SQL语句优化

1.前言:

优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在where及order by 涉及的列上建立索引。

2.引出问题:

然而建立索引并不是建的越多就会越好越好，原因是：

第一：一个表的索引不是越多越好，也没有一个具体的数字，根据以往的经验，一个表的索引最多不能超过6个，因为索引越多，对update和insert操作也会有性能的影响，涉及到索引的新建和重建操作。

第二：建立索引的使用场景：
多数查询经常使用的列；
很少进行修改操作的列；
索引需要建立在数据差异化大的列上

3.优化方案

1、sql语句模型结构优化指导

a. ORDER BY + LIMIT组合的索引优化
　　　　b. WHERE + ORDER BY + LIMIT组合的索引优化
　　　　c. WHERE+ORDER BY多个栏位+LIMIT

2、复合索引(形如(x,y,uid)索引的索引)

由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在列上创建复合索引的话将带来更高的效率。

注意: 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

3、like语句优化

SELECT id FROM A WHERE name like '%abc%'

由于abc前面用了“%”，因此该查询必然走全表查询，除非必要，否则不要在关键词前加%，优化成如下

SELECT id FROM A WHERE name like 'abc%'

4、where子句使用 ！= 或 <> 操作符优化

在where子句中使用 ！= 或 <>操作符，索引将被放弃使用，会进行全表查询。

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

5、where子句中使用 IS NULL 或 IS NOT NULL 的优化

在where子句中使用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会进行全表查询。

如SQL:    SELECT id FROM A WHERE num IS NULL 
优化成num上设置默认值0，确保表中num没有null值，
然后SQL为：SELECT id FROM A WHERE num=0

6、where子句使用or的优化

很多时候使用union all 或 nuin(必要的时候)的方式替换“or”会得到更好的效果。where子句中使用了or,索引将被放弃使用。

 如SQL:SELECT id FROM A WHERE num =10 or num = 20 
 优化成：SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20

7、where子句使用IN 或 NOT IN的优化

in和not in 也要慎用，否则也会导致全表扫描。

方案一：between替换in

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

方案二：exist替换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

8、where子句中对字段进行表达式操作的优化

不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。

9、任何地方都不要用 select * from table ，用具体的字段列表替换"*"，不要返回用不到的字段

10、使用“临时表”暂存中间结果

采用临时表暂存中间结果好处：
（1）避免程序中多次扫描主表，减少程序执行“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。
（2）尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
（3）避免频繁创建和删除临时表，以减少系统资源的浪费。
（4）尽量避免向客户端返回大数据量，若数据量过大，应考虑相应需求是否合理。

11、limit分页优化

SELECT id FROM A LIMIT 1000,10   很快
SELECT id FROM A LIMIT 90000,10  很慢

优化方法：

方法一：select id from A order by id limit 90000,10; 很快，0.04秒就OK。 因为用了id主键做索引当然快
　　　　方法二：select id,title from A where id>=(select id from collect order by id limit 90000,1) limit 10;
　　　　方法三：select id from A order by id between 10000000 and 10000010;

12、批量插入优化

INSERT into person(name,age) values('A',14)
INSERT into person(name,age) values('B',14)
INSERT into person(name,age) values('C',14)
可优化为：
INSERT into person(name,age) values('A',14),('B',14),('C',14),

13、利用limit 1 、top 1 取得一行

有时要查询一张表时，你知道只需要看一条记录，你可能去查询一条特殊的记录。可以使用limit 1 或者 top 1 来终止数据库索引继续扫描整个表或索引。

如SQL：SELECT id FROM A LIKE 'abc%' 
优化为：SELECT id FROM A LIKE 'abc%' limit 1

14、尽量不要使用 BY RAND()命令

BY RAND()是随机显示结果，这个函数可能会为表中每一个独立的行执行BY RAND()命令，这个会消耗处理器的处理能力。

如SQL：SELECT * FROM A order by rand() limit 10 优化为：SELECT * FROM A WHERE id >= ((SELECT MAX(id) FROM A)-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 10

15、排序的索引问题

Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作；

尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

16、尽量用 union add 替换 union

17、避免类型转换

18、尽可能使用更小的字段

19、Inner join 和 left join、right join、子查询

第一:能用inner join连接尽量使用inner join连接
第二：子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。

20、exist 代替 in

当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用。