数据库优化方案整理汇总

发布时间:2021-12-27 13:07:52

  MySQL数据库优化一直是每一个程序员都要面对的难题。关于SQL优化的在线教程也有很多,小编今天将数据库优化方案整理汇总。对数据库进行了三个方面的优化:数据存储划分、表索引、语句优化。

数据库

  1.数据存储分区。

  考虑到产品购入量近亿台,我们的资金管理体系是各地区用户购买产品,仅仅为一个表创建索引不能满足性能要求,所以交易记录按省进行列表划分,在查询数据加上省标准的情况下,将不同省的记录存储在不同的数据分区上,仅获取相应分区的数据。极大地减少数据检索的范围,从而提高查询性能,(注:每个项目组根据其项目的实际分区情况进行说明)还存在:范围分区(例如根据日期域创建分区,每个季度或每月数据分成一个区域)散列划分(没有指定分区条件,组合分区是由Oracle自动分配数据的(例如:完成分区之后,每个分区再划分)

  2.表索引

  为了创建分区,在分区内部添加了索引,从而进一步提高了数据查询速度,本项目中使用的索引如(CustomerInformationName建立btree索引,Identification建立唯一索引,Customernumber建立主键,主关键字自带唯一)(产品表产品类别建立位图索引)(常用的表关联条件建立索引)[备注:根据自身项目建立索引的实际情况示例]

  为了加快查询速度,索引使用一组独立的系统表来存储数据和rowid(例如书的目录),例如btree索引,他是一个具有根节点、分枝节点、叶子节点,只有一个根节点,oracle对分支节点的数目不固定,分枝节点存储数据的范围,在最后的分支节点下面有叶子节点,树叶节点负责存储特定值和rowid.例如:根节点存储大于50,小于50,两个支节点,比50大的支点可以存储50-60,60-70,大于100等的分区,每一片都有相应的叶子节点。例如,区段下的叶子节点存储了100,102个,以及诸如rowid、100、101,102的值是表中建立索引的列(如num)的值,在编写sql语句wherEnum=101时,通过检索索引表,查询保存在叶子节点上的rowid,接着,基于rowid查询相应数据,避免了对数据表进行全表扫描。

  索引并非越多越好,当然,索引也适用于创建一个条件列(即,where之后),包含相关条件)和orderby的列.如果对具有特殊性能要求的增删改查性能要求较低的表,建议不进行索引,由于指数降低了增删改查的效率。

  3.优化sql语句。

  (1)尽可能避免完整的表格查询。

  (2)避免对where子句中的字段进行空判断,另外,还会导致一个完整的表扫描selectidfromtwherenumisnull.Memo.添加notnull。说明:char(100)等将评设置为null.null需要空间,在字段建立时,无论插入或插入都需要100个字符,如果是varchar,null不占空间.num再次设置默认值为0,确保表中的num列没有空值。

  Selectidfromtwherenum=0。

  (3)避免在where子句中使用!=或<>操作符,否则引擎将在全表扫描中放弃使用索引。

  (4)避免在where子句中使用or连接条件,如果某个字段有索引,而一个字段不在,则会导致引擎在全表扫描时放弃索引。

  Selectidfromtwherenum=10orname='admin'错误。

  可以这样:

  Selectidfromtwherenum=10。

  Unionall。

  Selectidfromtwherename='admin'

  (5)还应谨慎In和notin,以形成全表扫描。

  Selectidfromtwherenumin(1,2,3)

  使用between时,不要使用连续值。

  Selectidfromtwherenumbetween1and3。

  大部分使用exists代替in。

  Selectnumfromawherenumin(selectnumfromb)

  Selectnumfromawhereexists(select1frombwherenum=a.num)

  (6)如果在where子句中使用参数,将导致全表扫描.因为sql只在运行时解析本地变量,但是,优化程序并不能延迟访问计划的选择,只有在编译时才能选择.但是,如果在编译时建立了访问计划,由于输入项不能作为索引选择.下面的全表扫描,该变量的值仍然未知。

  Selectidfromtwherenum=@num。

  您可以用索引代替查询。

  Selectidfromtwith(索引名))wherenum=@num。

  应该尽可能避免对where子句中的字段进行表达式操作,这会导致引擎放弃使用索引的全表扫描.例如。

  Selectidfromtwherenum/2=100。

  应该为

  Selectidfromtwherenum=100*2。

  (7)尽可能避免在where子句中执行功能操作,这会导致引擎在全表扫描时放弃使用索引.例如。

  Selectidfromtwheresubstring(name,1,3)='abc'-----name以abc开头。

  Selectidfromtwheredatediff(day,createdate,'2005-11-30')=0。

  改为

  Selectidfromtwherenamelike'abc%'

  Selectidfromtwherecreatedate>='2005-11-30'和createdate<'2005-12-01。

  (7)在where子句中不能执行功能、算数操作或其他表达式计算时,系统将不能正确使用索引。

  Update语句,如果只更改1、2个字段,不使用update所有字段,否则频繁调用将导致性能损失,并导致大量日志。

  (9)对于多大数据表的表join,先分页位于join中,否则逻辑读取将较高且性能较差。

  (10)虽然索引能够提高相应的select的效率,但同时也降低了insert和update的效率,因为insert或update可能重新建立索引,因此表最多不能超过6个索引。

  (11)尽可能使用数码字段。

  (12)尽可能用varchar/nvarchar取代char/nchar.因为可以节省空间和更高效的查询。

  (13)不要使用select*使用select域。

  (14)避免将大容量的数据返回给客户端。

  这些都是资料库优化计划的内容,希望能帮到有需要的小伙伴。

上一篇 Java的七种结构型设计模式
下一篇 你知道JavaWeb四大作用域