索引优化、Sql查询语句优化

工作中我们经常会遇到系统查询慢的情况，一般我们会采取好多方法进行优化，如建立索引，优化查询Sql，分表，规范数据表结构设计，调整数据库参数（内存分配、缓存等），增加硬件配置，优化网络环境等。下面介绍两种常用的优化方法，遵循其中的一些原则，可以解决很多常见的问题。

一、索引设计

建立一个好的索引，对于查询效率，会有一个立竿见影的效果。但索引并不是多多益善，如果建立的不合适，提升的效果微乎其微。下面是一些我在工作中常用到的原则：
1、在经常作为查询条件的字段上创建索引
2、在经常进行Group by，Order by的字段上建立索引
3、在主键、外键字段上建立索引
4、查询频率高的表适合建立索引
5、避免在具有较少值的字段上建立索引，如性别等
6、避免在经常更新（写操作）的字段上建立索引
7、在经常存取的多个列上建立复合索引，字段的顺序应按照使用频率来确定，频率高的在前
8、唯一性差的字段避免使用索引
9、避免选择大型数据类型的列作为索引，如大的文本等。
10、一张表的索引不宜太多，一般不超过6个
11、含有NULL值的字段，避免使用索引，否则将放弃索引而进行全表扫描
12、避免在索引列上使用计算
13、定期分析执行效率，重建索引（rebuild）

二、查询优化

1、不要使用过多的表连接查询（join）
2、少用子查询，尽量使用外连接代替子查询
3、视图嵌套不要过深
4、使用临时表来存放中间结果
5、少用模糊查询
6、优化where子句中的!=或<>

--如SQL:
SELECT id FROM A WHERE ID != 5
--优化成：
SELECT id FROM A WHERE ID>5 OR ID<5

 

7、优化where子句中in或not in
第一种情况：exist代替in，数据量比较大时，exists效率优于in

--如SQL:
SELECT id FROM A WHERE num in(select num from b )
--优化成：
SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)

第二种情况：left join代替in

--如SQL:
SELECT id FROM A WHERE num in(select num from B)
--优化成：
SELECT id FROM A LEFT JOIN B ON A.num = B.num

第三种情况：between替换in

--如SQL:
SELECT id FROM A WHERE num in(1,2,3)
--优化成：
SELECT id FROM A WHERE num between 1 and 3

8、应尽量避免在 where 子句中对字段进行 null 值判断，否则将会进行全表扫描
9、不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算

--如SQL:
SELECT id FROM A WHERE num/2 = 100
--优化成：
SELECT id FROM A WHERE num = 100*2

10、不要使用select * from table，用具体的字段代替*
11、尽量避免类型转换
12、尽量用 union all 替换 union
13、能用inner join连接尽量使用inner join连接，因为inner join是等值连接，或许返回的行数比较少
14、使用外连接（left/right join）时候，应该用小的结果驱动打的结果。left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向
15、尽量避免使用游标
16、一些复杂的逻辑或者需要定期执行的语句，可以做成存储过程。存储过程可以减少编译时间，客户端与服务器的交互时间等

 

除了以上常用的优化方法，还有分表、调整数据库参数（内存分配、缓存等）、增加数据文件等方法。关于DBA运维，常用数据库分析工具的使用方法，以后再专门介绍。