概述

索引优化的目的主要是让索引不失效，走正确的索引，续上次介绍的索引八大法则上篇，今天主要介绍下篇，下面一起来看看吧。。。

一、不等于（!=或<>）导致索引失效

1、不等于导致索引失效

mysql> explain select * from tb_emp where name != 'Jack';
mysql> explain select * from tb_emp where name = 'Jack';
mysql> explain select * from tb_emp where name <> 'Jack';

说明：使用!=或者<> 不等式会导致全表扫描（type=ALL），并且索引失效（key=Null）。

2、覆盖索引优化不等于（!=或<>）

从以下结果可以看到，当走覆盖索引的时候，本来的全表扫描变成了索引范围扫描，效率得到大大提升。

mysql> explain select name from tb_emp where name != 'Jack';
mysql> explain select name from tb_emp where name <> 'Jack';

二、is null 或 is not null导致索引失效

1、is null、is not null 走全扫

mysql> explain select * from tb_emp where name is null;
mysql> explain select * from tb_emp where name is not null;

说明：在使用is null的时候，索引完全失效，使用is not null的时候，type=ALL全表扫描，key=Null索引失效。

2、覆盖索引优化is not null

从以下结果可以发现is not null可以被覆盖索引所优化，但is null不能优化。

mysql> explain select name from tb_emp where name is  null;
mysql> explain select name from tb_emp where name is not null;
mysql> explain select name,age,gender from tb_emp where name is not null;

三、like通配符以%开头导致索引失效

1、对比like通配符位置是否会索引失效

mysql> explain select * from tb_emp where name like 'Jack%';
mysql> explain select * from tb_emp where name like '%Jack';
mysql> explain select * from tb_emp where name like '%Jack%';

说明：

1）like的%位置不同，所产生的效果不一样，当%出现在左边的时候，type=ALL，key=Null（全表扫描，索引失效），当%出现在右边的时候，type=range，索引未失效。

2）like查询为范围查询，%出现在左边，则索引失效。%出现在右边索引未失效。

但是在实际生产环境中，%仅出现在右边可能不能够解决我们的问题，所以解决%出现在左边索引失效的方法：使用覆盖索引。

2、覆盖索引优化%出现在左边问题

mysql> explain select name from tb_emp where name like '%Jack%';
mysql> explain select name from tb_emp where name like '%Jack';

说明：

前两个sql通过覆盖索引解决%出现在左边导致索引失效问题，使得type=index，并且使用了Using index，从全表扫描变成了全索引扫描。

第三个sql是因为主键自动创建唯一索引，其实也是覆盖索引，所以解决了%出现在左边导致索引失效问题。

后两个sql也是通过覆盖索引（查询列要被所使用的索引覆盖）解决%出现在左边导致索引失效问题。

四、or连接导致索引失效

1、or导致索引失效

生产环境尽量少用or,因为会使索引失效

mysql> explain select * from tb_emp where name ='Jack' or name='Mary';

说明：在使用or连接的时候type=ALL，key=Null，索引失效，并全表扫描

2、覆盖索引优化or导致索引失效问题

mysql> explain select name  from tb_emp where name ='Jack' or name='Mary';
mysql> explain select age  from tb_emp where name ='Jack' or name='Mary';
mysql> explain select gender  from tb_emp where name ='Jack' or name='Mary';
mysql> explain select id  from tb_emp where name ='Jack' or name='Mary';
mysql> explain select id,name,age,gender  from tb_emp where name ='Jack' or name='Mary';

可以看到用覆盖索引可以优化or导致索引失效的问题。

总结

1、最佳左前缀法则：查询从索引的最左前列开始且不能跳过索引中的列。

2、索引列上不计算和做隐式转换。

3、不等于、is null、is not null、or和like百分号在左边都会导致索引失效。

4、覆盖索引可以解决不等于、is not null、or和like百分号在左边导致索引失效问题。

到这里八大法则的内容就介绍完了，觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~