原创 数据库优化方案汇总

一直以来，MySQL数据库优化都是每个程序员要面对的难题。网上有关SQL优化的教程也有很多，数据库优化主要从以下三方面：数据存储分区，表索引，语句优化。小编今天整理了比较全面的数据库优化方案，下面我们来一起看看吧。

1、数据存储分区

我们的理财系统,购买产品的用户来自不同的区,考虑到产品购买数量接近上亿条,单纯的为表建立索引不能满足性能的需要,因此交易记录按省份做了列表分区,使不同省的记录存储到不同的数据分区,当查询数据加上省份条件,只会检索对应分区的数据,大大缩小数据检索范围,从而提高查询性能,(备注:每个项目组按照自己项目的实际分区情况举例说明)

分区还有:范围分区(比如按照日期字段建立分区,一个季度或者一个月的数据划分为一个区)散列分区(不指定分区条件,oracle自动将数据平均分配)复合分区(比如:做了分区后,每个分区再分区)

2、表索引

为建立好分区以后,在分区里面添加索引,进一步提高数据的查询速度。

索引之所以会提高查询速度,索引有一套独立的系统表存储数据与rowid的对应关系(如书的目录),比如btree索引,他是一个树形结构图,有根节点,分支节点,叶子节点,根节点只有一个,分支节点数量不固定由oracle合理分配,分支节点存储数据的范围,最终的分支节点下面有叶子节点,叶子节点负责存储具体的值和rowid.比如:根节点存储大于50小于50两个范围,两个支节点,大于50的支节点存储50-60,60-70,大于100等区段,每个区段都有对应的叶子节点,比如大于100的区段下面的叶子节点存储100,101,102和对应的rowid等具体数据,100,101,102的值即为表中建立索引的列(比如num)的值,当编写sql语句where num=101时,就会通过检索索引表,查询到叶子节点上存储的rowid,然后根据rowid查询对应的数据，避免了对数据表的全表扫描。

当然,索引不是越多越好,索引适合建立在经常作为条件列(即为where之后作为条件的列,包括关联条件),以及order by的列.如果对于增删改查性能要求特别高而查询要求不高的表就不建议建立索引,因为索引降低增删改查效率.

3、sql语句优化

(1)尽量避免全表查询。

(2)避免where子句中字段进行null判断,否则导致全表扫描select id from t where num is null。

尽可能添加not null .备注,描述,评可以设置为null.null需要空间,比如:char(100),在字段建立时空间固定,不论插不插值都是占用100个字符,如果varchar,null不占空间.可以再num上设置默认值为0,确保表中num列没有null值。

Select id from t where num=0

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

(4)尽量避免where子句中使用or来连接条件,如果一个字段有索引,一个字段没有,将导致引擎放弃索引而进行全表扫描。

Select id from t where num=10 or name=’admin’错误

可以这样:

Select id from t where num =10

Union all

Select id from t where name=’admin’

(5)In 和not in 也要慎用,导致全表扫描如

Select id from t where num in(1,2,3)

对于连续的值能用between就不要用in

Select id from t where num between 1 and 3

大多数用exists代替in

Select num from a where num in(select num from b)

Select num from a where exists(select 1 from b where num=a.num)

(6)如果在where子句中使用参数,会导致全表扫描.因为sql只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;他必须在编译时进行选择.然而,如果在编译时建立访问计划,变量的值还是未知的,因为无法作为索引选择的输入项。如下将进行全表扫描：

Select id from t where num =@num

可以改为强制查询使用索引

Select id from t with(index(索引名)) where num=@num

应尽量避免在where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描，如：

Select id from t where num/2=100

应该为

Select id from t where num =100*2

(7)尽量避免where 子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描，如：

Select id from t where substring(name,1,3) = ‘abc’ -----name以abc开头的name

Select id from t where datediff(day,createdate,’2005-11-30’) = 0

改为

Select id from t where name like ‘abc%’

Select id from t where createdate >=’2005-11-30’ and createdate <’2005-12-01

(7)不要在where 子句中的’=’ 左边进行函数,算数运算或者其他表达式计算,否则系统将可能无法正确使用索引。

(8)Update语句,如果只更改1,2个字段,不要update全部字段,否则频繁调用会引起性能消耗,同时带来大量日志。

(9)对于多张大数据表的表join,先分页在join,否则逻辑读会很高,性能差。

(10)索引固然可以提高相应select 的效率,但同时也降低insert 和update效率,因为insert或update 可能会重建索引,所以一个表最好不要超过6个索引。

(11)尽量使用数字型字段。

(12)尽可能使用varchar/nvarchar代替char/nchar.因为节省空间,查询效率高。

(13)不要用select * 要使用select 字段。

(14)避免向客户端返回大数据量。

(15)避免向客户端返回大数据量

以上就是数据库优化方案的全部内容，希望可以帮助到有需要的小伙伴。