记录工作中遇到的mysql索引失效的一些情况

背景

工作中经常遇到查询大表数据，为了提高用户体验，优化查询速度成为了必修课，而索引成为了我们提高查询速度的利器，但是我们在工作中经常遇到查询的索引失效的情况，本篇文章根据作者工作中遇到的索引失效情况，由于作者最近使用的数据库为mysql，所以本篇中使用的栗子也是来自mysql

EXPLAIN工具

工欲善其事必先利其器，没错，我们的利器就是mysql自带的excplain工具，它可以帮助我们快速定位查询语句的执行状态，优化查询语句，当你需要执行非常复杂或者查询大表数据时候，如果不能确定该语句的执行时长或者是否命中索引，可以使用explain工具进行检查，比如：

以上是作者随手截取的一张explain结果图，关于上面的每个字段的解释，大家可以参考Mysql官网给出的解释mysql官网

索引节点数据结构

在理解索引失效之前，需要理解一下索引节点的数据结构，这样才能有助于看懂作者接下来的内容，这位兄弟写的这篇内容就比较不错，直接附链接吧MySQL索引背后的数据结构及算法原理

索引失效情况

1、like失效， 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

原因：想一想mysql底层关于索引的数据结构就了然了，如果有童鞋不懂，自己百度一下吧，因为使用%开头，就意味着索引节点里的内容是没办法完全匹配的，比如一堆数据分别是’abcd’，‘acbcd’，‘bdcc’，那么由他们构成的b+树，在使用’%‘开头的时候需要扫描哪些节点了？是的，只能全部扫描，因为没有办法判断对应节点中是否包含你需要扫描的内容，那么为什么使用’%'后缀就没有问题了，也是一样的道理，因为由他们构成的索引节点是有序的，这个排序方式就是从内容从左往右排序，那么mysql在执行查询的时候，直接进行匹配，当匹配到某个节点后续无法匹配的时候，就可以明确知道后续内容完全无效，放弃继续查询，返回结果

2、or语句失效，or语句只有 A or B，A、B均有索引才会命中，如果其中一个不存在索引，那么将进行全部扫描
原因：mysql会在执行语句的时候进行解析判断是否走索引，如果A或者B不存在索引，那么就会出现一种情况，根据某一个查询条件命中了索引，但是根据另一个条件却需要全表扫描，那么这种情况下，走索引的意义不大，所以mysql会放弃使用索引

3、组合索引，最左匹配原则，如果没有按照该原则那么将不能命中该索引
原因：mysql在简历组合索引的时候存在排序情况，比如：

`ALTER TABLE `test`
ADD INDEX `date_m2` (`create_time`, `user_id`) USING BTREE ;

mysql会根据create_time和user_id建立索引，但是索引的节点数据结构会存在排序情况，是的排序和你建立索引的语句是一致的，索引先根据create_time进行排序，然后在根据user_id进行排序，而如果你使用查询语句：

select * from test where user_id = '123'`

如果mysql根据date_m2索引进行查询，那么查询出来的内容将不是完整内容，所以mysql只会使用全表扫描

4、数据类型出现隐式转化
原因：mysql不像java那样可以进行隐式转化，如果你需要查询的字段和你的查询语句中的条件类型不匹配，也将不会命中索引，

5、使用函数

select * from test where date_format(create_time,'%y%m') = '2005';

原因：mysql在查询的时候，需要将内容转行为字符串，无法使用索引，所以建议同学们别这样写

6、is null 、is not null
原因：mysql索引是不索引空值的，也就是说，如果内容为空，将无法针对这条记录建立索引，所以也无法命中

7、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
比如：test表中存在1000w的数据，均匀分布在2020年全年那么语句

select * from test where create_time > '2020-06-01'

将不会命中索引，因为这条语句几乎会命中一半的记录，而mysql在判断如果命中的记录大约占总记录的35%以上(这是我一位同事分享的，作者并没有考证)，将不会命中索引

后记

疑问：作者在一次查询的时候，使用了IN查询，情况如下：
表中存在3000w条记录，作者使用主键IN查询，但是查询的内容有点多，大约有5w条，explain并没有命中索引，进行的全部扫描，哪位同学内回答一下这个问题吗？不胜感激