上篇文章介绍了MySQL的性能分析工具，能够使得我们的SQL在被MySQL优化过后变得透明化，我们可以知道SQL有没有被改写，索引是否使用，是否失效，选择使用了哪个索引，我们也可以根据SQL执行成本选择使用那哪条SQL，在执行结果相同的情况下，但是还缺少SQL优化，本篇文章我来讲述，工作中经常见到的SQL优化手段，进而加快SQL的执行，笔者站在学习过程中也遇到了许多困难，就比如今天本来十点钟就打算开始写的，但是因为一些bug耽误了一个小时，真的很烦躁，马上快12点了，明天还要上班，但是我还是尽量在这里多写一点。

索引失效的场景

MySQL版本5.7：select version();如下图：

数据库有两张表class有10w数据,student500w数据,表结构如下：

1. 未遵循最左前缀匹配导致索引失效

新加索引idx_class_name;alter table class add index idx_class_name(className);

执行如下SQL语句，导致索引失效explain select * from class where className like '%abc';

如下图：

解决方案：将abc放到前面:

explain select * from class where className like 'abc%';

这里可能有小伙伴会说，你这样子改虽然索引生效了，但是SQL的含义都不一样了，返回的结果和业务需求不一致，对于这种我想说，要么你抛弃className字段的匹配，全量的将数据放到业务层面使用代码去做筛选，要么换另外的实现方式（比如es），除非你当前mysql数据表下数据量未达到一定的级别，否则后面一旦做inner连接查询会导致慢SQL的产生，也不好优化，长痛不如短痛，将其扼杀在摇篮！

2. 使用函数导致索引失效

指向如下SQL，当前字段className是索引字段（普通索引），

我们发现，使用了函数索引还可以生效啊，那是因为我们不是在索引字段上使用的，对于参数使用函数可处理的时候，MySQL优化器都会将其优化，上面的SQL实际上被优化器变成了如下:

3. 计算导致索引失效

如下SQL执行：explain select * from class where id + 1 = 666; 解决方案就是不要在索引字段进行计算，应当在参数那边计算完，在做匹配，这样子其实也是可以生效的，如下图：

4. 类型转换导致索引失效

如下SQL对索引字段进行了显示的类型转换：

explain select * from class where convert(id,char) = '666';

如下SQL进行了隐式类型转换将数值类型123转为字符类型123：

explain select * from class where className = 666;

解决方案:不要在索引字段进行类型转换（显示或隐式）。

5. 联合索引不完全使用及失效

查看当前表student的索引结构：show index from student,只有主键，如下图：

新增联合索引idx_id_classId_name由字段id,classId,name组成

alter table student add index idx_id_classId_name(id,classId,name);

查看索引：

失效场景1：未遵循最左匹配原则，也就是说使用联合索引必须按照索引字段id,classId,name的顺序来，可以不完全使用索引比如我只用字段id或者id,classId；但是不能打乱顺序使用，比如先使用classId在用id。

如下SQL

explain select * from student where classId =1 and id between 5000001 and 5000010;

这里使用到了主键索引，并没有使用到联合索引，因为不符合最左前缀准则。

失效场景二：范围索引字段后的索引字段失效，导致联合索引使用不完全，如下sql:

explain select * from student where id < 5000010 and classId = 1 and name like 'x%';

明明我们的索引字段是符合联合索引的最左匹配原则的，按道理来说使用联合索引效率更高，但是为什么还是使用了主键呢？原因就是因为id使用了范围查找导致索引字段classId,name失效了，所以MySQL优化器选择了主键索引，但是这个时候有人会说虽然id字段使用范围导致后面的索引字段失效，但是联合索引的id字段还是可以索引的，为什么不用呢？这个时候注意看我们的select查询的是全部的字段而主键索引是聚集索引，保存了主键索引和全部的信息，而联合索引是非聚集索引，只保留索引字段和主键索引，其他不存在的字段还需要二次回表去走一遍聚集索引，所以这里MySQL优化器当然会选择走主键索引咯。

解决方案：将范围索引字段放到最后，我们可以将索引idx_id_classId_name删除，新建一个idx_classId_name_id的联合索引，索引字段的先后顺序是classId,name,id，如下图：

在执行如下SQL：

explain select * from student where classId = 1 and name like 'x%' and id < 5000010;

可以看到使用到了联合索引，并且key_len的值为72,字段id,classId都是int类型占4个字节，但是classId可以为null所以占5个字节，name字段的类型是varchar(20),可为null，varchar可变长度需要2个字节记录长度，占63字节，4+5+63 == 72，因此可以证明联合索引完全使用。

6. 不等于(!= 或者<>)索引失效

给表class字段className新增索引，alter table class add index idx_className(className);

执行如下SQL：

7. like模糊匹配以通配符开头导致索引失效

8. 索引字段使用is not null导致失效

explain select * from class where className is null;

9. OR 前后存在非索引的列，索引失效