MySQL（四）——SQL常见优化手段及如何避免索引失效

在MySQL（三）——SQL执行计划分析这篇文章中具体介绍了一下SQL执行计划中各个字段的含义，今天这篇文章就简单的说一说具体的优化手段吧。

一、SQL优化

在做具体的总结之前，为了便于理解，先拿一个具体的例子的简单的实际操作一下具体的SQL优化过程。

先创建一张book表（有图书编号、书名、作者、出版编号、类型编号四个字段），并随便插入几条数据：

create table book(
    bid int(4) primary key,
    bname varchar(20),
    authorid int(4),
    publishid int(4),
    typeid int(4)
);
insert into book values(1,'java',1,1,2);
insert into book values(2,'SQL',2,1,2);
insert into book values(3,'python',3,2,1);
insert into book values(4,'PHP',4,2,3);
commit;

有了数据后我们做一个查询：查询类型编号(typeid)为2或3，作者编号(authorid)为1的图书编号(bid)，并按类型标号bypeid降序排列。对应的SQL：select bid from book where typeid in(2,3) and authorid=1 order by typeid desc;我们看一下这个SQL的执行计划：

从上面可以看出来，这个SQL语句的性能还是很差的，没有用到任何索引（当然因为我们还没创建），type类型为all,Extral为using filesort。下面我们一步步来进行优化：

优化一：加索引

现在为我们上面用到的三个字段加上索引(复合索引)：alter table book add index bid_typeid_authorid_index(bid,typeid,authorid);加索引后看一下上面SQL的执行计划：可以看到using filesort依然存在，但type由原来的all降到了现在的index，整体比上面的好一些，但还不是很好。

优化二：根据SQL语句的解析顺序，调整索引顺序

我们在前面SQL执行计划里提到，MySQL对SQL语句的解析是有顺序的，它不会按着你编写的SQL顺序解析，而是按照如下顺序解析：

from->on->join->where->gruop by->having->select (dinstinct)->order by->limit

由上面的解析顺序可以知道，MySQL是先解析where后面的typeid字段和authorid字段，最后才解析select后面的bid字段，而我们在第一步的优化中建立的复合索引顺序却是bid在最前，authorid在最后(bid,typeid,authorid)，现在我们根据mysql的解析顺序调整复合索引的顺序，让他们二者保持一致：alter table book add index typeid_authorid_bid_index(typeid,authorid,bid);可以看到using filesort被消除了。

这里需要注意一点，我们在第二步优化的时候重现创建了索引，第一步中的索引还在。在实际开发中如果我们确定第一个索引不会用了，应当把它删除掉，因为有时候旧的索引会干扰我们优化。举个简单的例子，比如第一次创建了一个复合索引(a,b,c)觉得不合适，在没有删除它的前提下又创建了另一索引(a,b)，这个时候如果再进行优化的时候可能用到的还是之前的(a,b,c)索引，而新的(a,b)没有用到。

在此删除第一步中的索引：drop index bid_typeid_authorid_index on book;

优化三：调整原来的SQL语句顺序,并按调整后的顺序重新创建新的索引

原来SQL语句：select bid from book where typeid in(2,3) and authorid=1 order by typeid desc;

调整后SQL语句：select bid from book where authorid=1 and typeid in(2,3) order by typeid desc;

删除第二步中的索引：drop index typeid_authorid_bid_index on book;

根据调整后的SQL语句顺序重新创建新的索引：alter table book add index authorid_typeid_bid_index(authorid,typeid,bid);

可以看到上面的两个SQL语句仅仅是把where后面的typeid字段和authorid字段换了一下顺序，为什么要这么做？这个其实我们在MySQL（三）——SQL执行计划分析中也提到过，就是in的范围查询有时候会使索引失效。比如原来SQL中where条件是这样的：where typeid in(2,3) and authorid=1，这个时候因为in的存在可能会导致typeid这个索引失效，而在复合索引中如果一个索引失效，那么它后面的索引也会自动失效，这个时候原来的索引(typeid,authorid,bid)中，后面的authorid和bid也会自动失效。所以为了避免这种情况，我们把顺序调整一下，并根据调整后的顺序重新建立新的索引。下面看一下调整后的执行计划，可以看到type类型由原来的的index提升到了现在的ref。

优化小结:上面只是一个简单的优化案例，在实际开发中对优化也是这么一步步来的，不可一步到位。现在简单总结一下实际开发中常用到的一些优化手段

    1.最佳左前缀原则：即保持索引的定义顺序和使用的顺序（where后面的字段顺序）一致。比如定义了一个复合索引（a,b,c）那么在具体的SQL语句中最好按select...from A where a..b...c..这个顺序来，而不要按这种select...from A where b...c..a...无序的来，或者这种select...from A where a...c...跨列的方式来。

    2.将含有in的范围查询放到where条件的最后，防止让本身索引失效外，引起后面的索引也失效。这一点就是我们在上面案例中“优化三”中说道的情况。

    3.关于order by的优化：对于与order by处理不当经常会导致Extral中usring filesort的情况发生。其实在MySQL中关于排序它的底层有两种排序方法，mysql4.1之前一直是双路排序，4.1之后改为了单路排序，单双路排序的划分是按照mysql扫描磁盘的次数（I/O次数）进行的。那个具体的SQL语句说明，比如select name,age from A  order by id

        双路排序：会扫描两次磁盘，第一次从磁盘扫描排序字段id，第二次扫描其他字段 name和age

        单路排序：因为IO比较消耗性能能，所以MySQL为了减少IO次数，在4.1之后改为单路。只读取一次磁盘，它会一次性把name,age,id三个字段都扫描完。

不管是单路排序还是双路排序，MySQL在拿到字段后（对于双路排序只拿排序字段，对于单路排序拿全部查询的字段）都会把它们放到MySQL内部自带的的一个buffer缓存中，然后对他们进行排序。所以单路排序会比双路排序占用更多的buffer空间，单路排序如果在使用时数据量比较大，可以考虑调大buffer的容量（set max_length_for sort data=具体的字节数）。对于单路排序如果需要排序的列数大小超过了max_length_for sort data设定的大小，MySQL会自动从单路切换到双路。

根据以上情况，针对order by的优化手段主要有：

        3.1.根据实际情况，适当调整buffer的容量大小

        3.2.尽量保持排序的一致性（要么都是升序、要么都是降序）,避免order by  a desc,c asc这种按a降序的同时又按c升序

        3.3.对于符合索引，SQL中where和order by拼接起来的字段，不要跨列使用。比如复合索引为（a,b,c），那么像这种select...from...where a=..b=..order by c;甚至select...from...where a=..c=..order by b;都没什么问题,因为不管where后面是a,b还是a,c，最后和order by后面的字段拼接后都能组成有序的a,b,c序列（对于第二种情况mysql中的优化引擎会自动帮我们调整好顺序），保持了和复合索引（a,b,c）顺序的一致性。但如果是这种select...from...where a=.order by c;SQL语句，就属于跨列了（把b跨了过去），性能肯定会变差。

    4.避免*的使用，假如一个表有3列（id,name,age），我们尽量使用select id,name,age from.. 而不是select * from...

    5.关于exists和in的优化使用：如果主查询的数据量大，使用in；如果子查询的数据量大，则使用exists

        select..from table where exists (子查询)

        select..from table where 某个字段 in (子查询)

        可能有的同学对于exists和in不是太熟悉，这里简单说明一下：

        exists/in就是将主查询的结果放到子查询结果中进行条件校验，看子查询中是否有数据，如果符合校验则保留查询数据，比如下面的两条SQL,第一条表示先从A表中查到所有name信息，然后再和B表中的name信息对比，如果发现B表中也有这些name信息，则返回查询结果。

        select name from A where  exists (select name from B )

        select * from A where A.id in (select B.id from B);

    6.对于双表或者多表查询，准许“小表驱动大表”的原则。因为双表就涉及到两张表了，那么我们在where条件中一般把数据量小的表放到左边，数据量大的表放到右边，像这样：where 小表.id=大表.id，这就是所谓的小表驱动大表的意思。那么在加索引的时候，一般更多的也是选择给小表中的字段加。

    7.对于连接查询，一般如果是左连接就给左表字段加索引，右连接就给右表字段加索引。

以上只是一般的几个常见优化手段，对于特殊情况我们需要根据具体的业务适当变动，比如对于第6条，我们在给小表中字段加索引的同时，有时候也需要给大表中的某些字段加适当的索引；对于第7条，左连接有时候也需要给右表中的字段加索引，右连接的同时也需要给左表的某些字段加索引，这些需要根据业务灵活变动，尤其6、7两条。

 

二、索引失效原则

有时候以为SQL语句的书写不当会导致索引的失效，就比如对于符合索引中，如果其中一个失效了，那么它后面的几个索引也会失效。例如符合索引（a,b,c），如果a失效了那么b,c也会失效，b失效了,c就会失效。下面将几条避免索引失效的原则：

    1.复合索引，不要跨列或无序使用，否则索引失效。这个和上面讲优化手段的第1条和第3.3条一致。

    2.复合索引尽量使用全索引匹配，建几个用几个。比如建了（a,b）那么a,b字段最好都用上；建了（a,b,c）那么abc三个字段最好都用上

   3.不要在索引上进行任何操作（+、-、*、/等计算、函数、类型转换等），否则索引失效

        计算：比如id是索引字段，那么如下SQL就会使索引失效：select ...where a.id*3

        函数：比如id是索引字段，类似这样的SQL也会让索引失效：select count(id) from....

        类型转换：比如给那么字段设置了索引，并且那么字段的类型是char,那么下面第二句的SQL就会导致索引失效：

                          select * from teacher where name='abc'  //索引不失效

                          select * from teacher where name=123   //索引失效

    4.复合索引不要使用不等于（!=  <>）或 is null(is not null)否则自身及右侧所有索引全部失效 ，比如select...from A where id !=2...

    5.like尽量以“常量”开头，不要以“%”开头，否则索引失效,比如select * from xx where name like '%X%'；如果name字段是索引，则会失效。

    6.尽量不要使用or，否则索引失效。比如select * from teacher where name='abc' or id=3;id和name都会失效。

    7.尽量不要使用类型转换（显示、隐式），否则索引失效。比如在一张teacher表中由一个name字段是varchar类型的并且它是索引列，正常像select * from teacher where name ='123'；这种没有问题，如果查询语句是select * from teacher where name =123；那么就会导致索引失效。

上面说了那么多手段和原则，但有一点必须得说明一下，其实SQL优化是一种概率事件，也就是说你优化了不一定会百分之百的有效，主要原因就是MySQL的服务层中SQL优化器可能会对你优化后的SQL语句进行进一步优化更改，从而导致原有的优化失效。

但我们该优化还是需要优化，哈哈。