背景
工作中经常遇到查询大表数据,为了提高用户体验,优化查询速度成为了必修课,而索引成为了我们提高查询速度的利器,但是我们在工作中经常遇到查询的索引失效的情况,本篇文章根据作者工作中遇到的索引失效情况,由于作者最近使用的数据库为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并没有命中索引,进行的全部扫描,哪位同学内回答一下这个问题吗?不胜感激