SQL语句调优实例锦集
2016-07-23 timy 连接IT总监
怎样加快查询速度，优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在 where 及 order by 涉及的列上建立索引。
那一个表是不是索引越多越好，建立索引的方法论，也就是原则是什么呢？
第一：一个表的索引不是越多越好，没有一个具体数字，根据以往经，一
个表的索引最多不超过6个，因为索引越多，对update和insert操作也会有性能的影响，涉及到索引的新建和重建操作。
第二：建立索引的方法论为：
a：多数查询经常使用到列
b：很少进行修改操作的列
c：索引需要建立在数据差异化大的列上
好了，基于以上的基础，我下面具体讨论sql语句该怎么优化，以及优化的简单示例。往往很多理论和方法论都是建立在一个简单模型基础上，利用简单模型方法论，就能分析出实际环境中的更为复杂的模型。
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语句的优化
形如这样一条SQL语句： select id from t where name like '%abc%'
由于abc前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%，该语句可以优化为 select id from t where name like 'abc%'
这个sql会使用name的索引（前提name建立了索引）；而类似'%abc%'的语句就使用不了索引，因为‘%’代表任何字符，%xxx不知道怎么去索引的，所以使用不了索引。
4： where 子句中使用!=或<>操作符的优化
在 where 子句中使用!=或<>操作符，引擎将放弃使用，进行全表扫描。
形如这样一条SQL语句： SELECT ID FROM USER WHERE ID!=5;
可以优化为 SELECT ID FROM USER WHERE ID>5 OR ID<5;
5：where 子句中使用了IS NULL或IS NOT NULL的优化
where 子句中对字段进行 IS NULL或IS NOT NULL 值判断，将导致放弃使用索引而进行全表扫描。
只要列中包含有NULL值都将不会被包含在索引中（除非是唯一值的域，可以存在一个NULL），复合索引中只要有一列含有NULL值，那么这一列对于此复合索引是无效的。所以我们在数据库设计时不要让字段的默认值为NULL.
形如这样一条SQL语句：select id from t where num is null。可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0
避免使用NULL类型：NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默认值
6：where 子句中使用 or 的优化
当where子句中存在多个条件以“或”并存的时候，Mysql的优化器并没有很好的解决其执行计划优化问题，再加上mysql特有的sql与Storage分层架构方式，造成了其性能比较低下。
很多时候使用union all或者union(必要的时候)的方式代替“or”会得到更好的效果。where子句中使用了or，将导致引擎放弃使用索引而进行全表扫描。
形如这样一条SQL语句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
形如这样一条SQL语句：SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
可以优化为： SELECT * FROM orders WHERE customer_num=104 AND order_num>1001 UNION SELECT * FROM orders WHERE order_num=1008
这样就能利用索引路径处理查询。【数据结果集很多，但查询条件限定后结果集不大的情况下，后面的语句快】
7：where子句中使用IN 或者NOT IN 的优化
in 和 not in 也要慎用，否则会导致全表扫描
方案一：between替换in
形如这样一条SQL语句select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3
方案二：exists替换in
形如这样一条SQL语句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)
方案三：LEFT JOIN 替换 IN
形如这样一条SQL语句 SELECT * FROM customerinfo WHERE CustomerID IN (SELECT CustomerID FROM salesinfo );
优化为 SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID ;
8： where 子句中对字段进行表达式操作的优化
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
形如这样一条SQL语句 select id from t where num/2=100 应改为:select id from t where num=100*2
形如这样一条SQL语句 select id from t where substring(name,1,3)='abc' 应改为:select id from t where name like 'abc%'
形如这样一条SQL语句 select id from t where datediff(day,createdate,'2005-11-30')=0 应改为:select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
形如这样一条SQL语句 select * fromusers where YEAR(adddate)<2007;应改为 Select * from users where adddate<’2007-01-01’;
9：任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
原因：。。。。。。（此处略过一万字）
10：where 子句使用预编译语句（绑定变量 ）
形如这样一条SQL语句
select*from orderheader where changetime >'2010-10-20 00:00:01'
select*from orderheader where changetime >'2010-09-22 00:00:01'
以上两句语句，查询优化器认为是不同的SQL语句，需要解析两次。如果采用绑定变量
select*from orderheader where changetime >@chgtime
@chgtime变量可以传入任何值，这样大量的类似查询可以重用该执行计划了，这可以大大降低数据库解析SQL语句的负担。一次解析，多次重用，是提高数据库效率的原则。
但是 事物都存在两面性，绑定变量对大多数OLTP（On-Line Transaction Processing联机事务处理过程）处理是适用的，但是也有例外。比如在where条件中的字段是“倾斜字段”的时候。
“倾斜字段”指该列中的绝大多数的值都是相同的，比如一张人口调查表，其中“民族”这列，90%以上都是汉族。那么如果一个SQL语句要查询30岁的汉族人口有多少，那“民族”这列必然要被放在where条件中。这个时候如果采用绑定变量@nation会存在很大问题。
试想如果@nation传入的第一个值是“汉族”，那整个执行计划必然会选择表扫描。然后，如果再传入的是“布依族”，按理说“布依族”占的比例可能只有万分之一，应该采用索引查找。但是，由于重用了第一次解析的“汉族”的那个执行计划，那么第二次也将采用表扫描方式。这个问题就是著名的“绑定变量窥测”，建议对于“倾斜字段”不要采用绑定变量。
再如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。这就要求我们索引需要建立在数据差异化打的列上
11：强制查询优化器使用某个索引
SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45)
改成：SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)
则查询优化器将会强行利用索引IX_ProcessID 执行查询，前提是processid建立了索引。
12：使用“临时表”暂存中间结果
简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。
但是临时表的使用应该遵从一下几个原则
第一：尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引)。
第二：避免频繁创建和删除临时表，以减少系统表资源的消耗。
第三：临时表并不是不可使用，适当地使用它们可以使某些例程更有，例如，当需要重复引用大型表或常用表中的某个数据集时。
第四：尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
14：limit分页的优化
当偏移量特别大时，limit效率会非常低
select id,title from collect limit 1000,10; 很快；
再看下 select id,title from collect limit 90000,10; 就会很慢了
三种优化方法
方法一：select id from collect order by id limit 90000,10; 很快，0.04秒就OK。 因为用了id主键做索引当然快
方法二：select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;
方法三：select id from collect order by id between 10000000 and 10000010;
如果表非常大，这个查询最好写成下面的样子：
select film.film_id, film.description from sakila.film inner join(select film_id from sakila.film order by title liimit 50,5) as film usinig(film_id);
15：批量插入
insert into person(name,age) values(‘xboy’, 14);
insert into person(name,age) values(‘xgirl’, 15);
insert into person(name,age) values(‘nia’, 19);
把它优化
insert into person(name,age) values(‘xboy’, 14), (‘xgirl’, 15),(‘nia’, 19);
16：对多表关联的查询，建立视图
对多表的关联可能会有性能上的问题，我们可以对多表建立视图，这样操作简单话，增加数据安全性，通过视图，用户只能查询和修改指定的数据。且提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响
17：利用limit 1、top 1取得一行
有时要查询一张表时，你要知道需要看一行，你可能去查询一条独特的记录。你可以使用limit 1或者 top 1来终止数据库引擎继续扫描整个表或者索引
Select * from A where namelike ‘%xxx’ limit 1; 这样只要查询符合like ‘%xxx’的记录，那么引擎就不会继续扫描表或者索引了。
18：尽量不要使用BY RAND()命令
如果您真需要随机显示你的结果，有很多更好的途径实现。而这个函数可能会为表中每一个独立的行执行BY RAND()命令—这个会消耗处理器的处理能力，然后给你仅仅返回一行。
SELECT * FROM `表名` order by rand() LIMIT 10
可以优化为
SELECT * FROM 表名 WHERE id >= ((SELECT MAX(id) FROM 表名)-(SELECT MIN(id) FROM 表名)) * RAND() + (SELECT MIN(id) FROM 表名) LIMIT 10
19：排序的索引问题
Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作；
尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
20：尽量用union all 代替union
union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union
21：避免类型转换
这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上通过转换函数进行转换，直接导致mysql无法使用索引。如果非要转型，应该在传入参数上进行转换。
例如utime 是datetime类型，传入的参数是“2016-07-23”，在比较大小时通常是 date（utime）>"2016-07-23",可以优化为utime>"2016-07-23 00：00：00"
22： 尽可能使用更小的字段
MySQL从磁盘读取数据后是存储到内存中的，然后使用cpu周期和磁盘I/O读取它，这意味着越小的数据类型占用的空间越小，从磁盘读或打包到内存的效率都更好，但也不要太过执着减小数据类型，要是以后应用程序发生什么变化就没有空间了。修改表将需要重构，间接地可能引起代码的改变，这是很头疼的问题，因此需要找到一个平衡点。
23：Innerjoin和左连接，右连接，子查询
第一：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 (多个表查询效率低，容易锁表和阻塞)。如：
Select * from A left join B A.id=B.ref_id where A.id>10;可以优化为：select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;
24：exists 代替 in
select * from A where id in (select id from B);
select * from A where exists (select 1 from B where A.id=B.id);
对于以上两种情况，in是在内存里遍历比较，而exists需要查询数据库，所以当B表数据量较大时，exists效率优于in。
in()只执行一次，它查出B表中的所有id字段并缓存起来。之后，检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表的所有记录。
IN操作流程原理如同如下代码逻辑:
List resultSet={};
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i<A.length;i++) {
for(int j=0;j<B.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
return resultSet;
可以看出，当B表数据较大时不适合使用in()，因为它会B表数据全部遍历一次
如：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历10000*1000000次，效率很差。
再如：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000*100次，遍历次数大大减少，效率大大提升。
结论：in()适合B表比A表数据小的情况
select * from A where exists (select 1 from B where A.id=B.id);
exists()会执行A.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回false，非空则返回true。
它的查询过程类似于以下过程：代码如下:
List resultSet={};
Array A=(select * from A);
for(int i=0;i<A.length;i++) {
if(exists(A[i].id) { //执行select 1 from B where B.id=A.id是否有记录返回
resultSet.add(A[i]);
}
}
return resultSet;
当B表比A表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行。
如：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。
如：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。
再如：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，
我们都知道查询数据库所消耗的性能更高，而内存比较很快。
结论：exists()适合B表比A表数据大的情况
当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用。
在插入记录前，需要检查这条记录是否已经存在，只有当记录不存在时才执行插入操作，可以通过使用 EXISTS 条件句防止插入重复记录。
insert into A (name,age) select name,age from B where not exists (select 1 from A where A.id=B.id);（语句挺怪，作者也没有写过类似这样的语句）
EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引。但要看实际情况具体使用：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。