【笔记】数据库性能优化

理解性能的瓶颈

CPU处理能力

磁盘读写能力

网络传输能力

 

漏斗原则

1.减少数据访问

创建并正确使用索引（索引能增加DML的开销）

索引就好比字典的目录

1、一般主键和外键都可以设置为索引。

2、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。

3、根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。

4、把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。

5、一个表不要加太多索引，因为索引影响插入和更新的速度

 

2.返回更少数据

数据分页处理（存储过程分页）

只返回需要的字段

 

3.减少交互次数

批处理

使用存储过程

 

4.减少服务器CPU开销

使用变量参数，软解析，提高解析性能 合理使用排序

减少比较操作：

（1）尽量避免使用like模糊查询

（2）尽量避免使用in list 大量复杂运算在客户端处理

 

5.利用更多资源

客户端多进程并行访问

数据库并行处理

 

数据库查询优化原则

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。　　

 

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： 　　select id from t where num is 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 num=20 　　可以这样查询： 　　select id from t where num=10 　　union all 　　select id from t where num=20

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

6.下面的查询也将导致全表扫描： 　　select id from t where name like '%abc%' 　　若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： 　　select id from t wherenum=@num 　　可以改为强制查询使用索引： 　　select id from t with(index(索引名)) wherenum= @num　　

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： 　　select id from t where num/2=100 　　应改为： 　　select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如： 　　select id from t where substring(name，1，3)='abc'--name以abc开头的id 　　select id from t where datediff(day，createdate，'2005-11-30')=0--‘2005-11-30’生成的id