SQL语⾔--语句优化总结
概要
性能不理想的系统中除了⼀部分是因为应⽤程序的负载确实超过了服务器的实际处理能⼒外，更多的是因为系统存在⼤量的SQL语句需要优化。
为了获得稳定的执⾏性能，SQL语句越简单越好。对复杂的SQL语句，要设法对之进⾏简化。
常见的简化规则如下：
1.  不要有超过5个以上的表连接（JOIN）
2.  考虑使⽤临时表或表变量存放中间结果。
3.  少⽤⼦查询
4.  视图嵌套不要过深,⼀般视图嵌套不要超过2个为宜。
为了加快查询速度，优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在where及order by 涉及的列上建⽴索引。
建⽴索引不是建的越多越好，原则是：
利⽤以上的基础我们讨论⼀下如何优化sql：
1. 表的索引不是越多越好，也没有⼀个具体的数字，根据以往的经验，⼀个表的索引最多不能超过6个，因为索引越多，对update和
insert操作也会有性能的影响，涉及到索引的新建和重建操作。
2. 建⽴索引的⽅法论为：
语句优化
1、sql语句模型结构优化指导
a. ORDER BY + LIMIT组合的索引优化
如果⼀个SQL语句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];
这个SQL语句优化⽐较简单，在[sort]这个栏位上建⽴索引即可。
b. WHERE + ORDER BY + LIMIT组合的索引优化
如果⼀个SQL语句形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];
这个语句，如果你仍然采⽤第⼀个例⼦中建⽴索引的⽅法，虽然可以⽤到索引，但是效率不⾼。更⾼效的⽅法是建⽴⼀个联合索引(columnX,sort)
c. WHERE+ORDER BY多个栏位+LIMIT
如果⼀个SQL语句形如：
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
对于这个语句，⼤家可能是加⼀个这样的索引:(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由MySQL处理排序的机制造成的。
2、复合索引(形如(x,y,uid)索引的索引)
先看这样⼀条语句这样的：select* from users where area =’beijing’ and age=22;
如果我们是在area和age上分别创建索引的话，由于mysql查询每次只能使⽤⼀个索引，所以虽然这样已经相对不做索引时全表扫描提⾼了很多效率，但是如果area，age两列上创建复合索引的话将带来更⾼的效率。
在使⽤索引字段作为条件时，如果该索引是复合索引，那么必须使⽤到该索引中的第⼀个字段作为条件时才能保证系统使⽤该索引，否则该索引将不会被使⽤，并且应尽可能的让字段顺序与索引顺序相⼀致。
例如我们建⽴了⼀个这样的索引（area,age,salary），那么其实相当于创建了（area,age,salary）,(area,age),(area)三个索引，这样称为最佳左前缀特性。
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
sql语句优化方式优化成：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⼦句中的“=”左边进⾏函数、算数运算或其他表达式运算，否则系统将可能⽆法正确使⽤索引。
如SQL:SELECT id FROM A WHERE num/2 = 100
优化成：SELECT id FROM A WHERE num = 100*2
如SQL:SELECT id FROM A WHERE substring(name,1,3) = 'abc'
优化成：SELECT id FROM A WHERE LIKE 'abc%'
如SQL:SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0
优化成：SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'
如SQL:SELECT id FROM A WHERE year(addate) <2016
优化成：SELECT id FROM A where addate<'2016-01-01'
9、任何地⽅都不要⽤ select * from table ，⽤具体的字段列表替换"*"，不要返回⽤不到的字段 
10、使⽤“临时表”暂存中间结果
采⽤临时表暂存中间结果好处：
  （1）避免程序中多次扫描主表，减少程序执⾏“共享锁”阻塞“更新锁”，减少了阻塞，提⾼了并发性能。
  （2）尽量使⽤表变量来代替临时表。如果表变量包含⼤量数据，请注意索引⾮常有限（只有主键索引）。
  （3）避免频繁创建和删除临时表，以减少系统资源的浪费。
  （4）尽量避免向客户端返回⼤数据量，若数据量过⼤，应考虑相应需求是否合理。
11、limit分页优化
当偏移量特别时，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 all 替换 union
union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进⾏唯⼀性过滤操作，这就会涉及到排序，增加⼤量的cpu运算，加⼤资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使⽤union all⽽不是union
17、避免类型转换
这⾥所说的“类型转换”是指where⼦句中出现column字段的类型和传⼊的参数类型不⼀致的时候发⽣的类型转换。⼈为的上通过转换函数进⾏转换，直接导致mysql⽆法使⽤索引。如果⾮要转型，应该在传⼊参数上进⾏转换。
例如utime 是datetime类型，传⼊的参数是“2016-07-23”，在⽐较⼤⼩时通常是 date（utime）>"2016-07-23"，可以优化为utime>"2016-07-23 00：00：00"
18、尽可能使⽤更⼩的字段       
MySQL从磁盘读取数据后是存储到内存中的，然后使⽤cpu周期和磁盘I/O读取它，这意味着越⼩的数据类型占⽤的空间越⼩，从磁盘读或打包到内存的效率都更好，但也不要太过执着减⼩数据类型，要是以后应⽤程序发⽣什么变化就没有空间了。
修改表将需要重构，间接地可能引起代码的改变，这是很头疼的问题，因此需要到⼀个平衡点。
19、Inner join 和 left join、right join、⼦查询
第⼀：inner join内连接也叫等值连接是，left/rightjoin是外连接。
SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;
SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;
经过来之多⽅⾯的证实inner join性能⽐较快，因为inner join是等值连接，或许返回的⾏数⽐较少。但是我们要记得有些语句隐形的⽤到了等值连接，如：
　SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;
推荐：能⽤inner join连接尽量使⽤inner join连接
第⼆：⼦查询的性能⼜⽐外连接性能慢，尽量⽤外连接来替换⼦查询。
Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);
A表的数据为⼗万级表，B表为百万级表，在本机执⾏差不多⽤2秒左右，我们可以通过explain可以查看到⼦查询是⼀个相关⼦查询(DEPENDENCE SUBQUERY);Mysql是先对外表A执⾏全表查询，然后根据uuid逐次执⾏⼦查询，如果外层表是⼀个很⼤的表，我们可以想象查询性能会表现⽐这个更加糟糕。
⼀种简单的优化就是⽤innerjoin的⽅法来代替⼦查询，查询语句改为：
Select* from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000;  这个语句执⾏测试不到⼀秒；
第三：使⽤JOIN时候，应该⽤⼩的结果驱动打的结果
（left join 左边表结果尽量⼩，如果有条件应该放到左边先处理，right join同理反向），同时尽量把牵涉到多表联合的查询拆分多个query (多个表查询效率低，容易锁表和阻塞)。如：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。