MySQL索引的实现原理 一文看懂mysql数据库索引八大法则

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

mysql>explainselect*fromtb_empwherename!='Jack';\nmysql>explainselect*fromtb_empwherename='Jack';\nmysql>explainselect*fromtb_empwherename<>'Jack';

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

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

mysql>explainselectnamefromtb_empwherename!='Jack';\nmysql>explainselectnamefromtb_empwherename<>'Jack';二、isnull或isnotnull导致索引失效

1、isnull、isnotnull走全扫

mysql>explainselect*fromtb_empwherenameisnull;\nmysql>explainselect*fromtb_empwherenameisnotnull;

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

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

mysql>explainselectnamefromtb_empwherenameisnull;\nmysql>explainselectnamefromtb_empwherenameisnotnull;\nmysql>explainselectname,age,genderfromtb_empwherenameisnotnull;三、like通配符以%开头导致索引失效

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

mysql>explainselect*fromtb_empwherenamelike'Jack%';\nmysql>explainselect*fromtb_empwherenamelike'%Jack';\nmysql>explainselect*fromtb_empwherenamelike'%Jack%';说明：

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

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

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

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

mysql>explainselectnamefromtb_empwherenamelike'%Jack%';\nmysql>explainselectnamefromtb_empwherenamelike'%Jack';

说明：

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

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

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

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

?mysql>explainselect*fromtb_empwherename='Jack'orname='Mary';

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

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

mysql>explainselectnamefromtb_empwherename='Jack'orname='Mary';\nmysql>explainselectagefromtb_empwherename='Jack'orname='Mary';\nmysql>explainselectgenderfromtb_empwherename='Jack'orname='Mary';\nmysql>explainselectidfromtb_empwherename='Jack'orname='Mary';\nmysql>explainselectid,name,age,genderfromtb_empwherename='Jack'orname='Mary';

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

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

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

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

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

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