MySQL索引失效原理

索引可以加快查找速度是因为,在每一层的兄弟节点之间,索引是有序的,因此可以通过二分法快速定位到相应位置。如果一些操作破坏了索引排列的有序性或者不能利用索引的有序性,这个索引自然就失效了,下文更详细说明,需

目录

  • 1、索引失效原因
  • 2、再来看看哪些情况会破坏索引的有序性。

    • – 对索引字段做函数操作
    • – 隐式类型转换
    • – 隐式字符编码转换
  • 3、总结

1、索引失效原因

首先看看哪些情况下,将会导致查找不能利用索引的有序性。

假设一个表test中有a,b,c,d四个字段,c是主键。

在a,b字段上建立联合索引(a,b):create index idx_a_b on test(a,b); b+树联合索引.jpg

MySQL索引失效原理

可以得到的规律是:优先按a字段从小到大排序,a字段相等的按b字段从小到大排序;

分析以下情况,索引是否会失效以及失效的原因:

条件只包含b字段

?

1

select * from test where b=2;

索引失效:

MySQL索引失效原理

显然,走的时候全文扫描,并没有使用索引。因为只看b字段的索引,是2,4,1,3,4,5,并不能利用索引的有序性快速定位。

对a字段范围查询:

?

1

select * from test where a>1 and b=2;

索引失效:

MySQL索引失效原理

可以看到,索引并没有完全失效,而是先利用索引定位到a的位置。因为这里的key_len是4,而联合索引的key_len是8。

对a字段等值查询,b字段范围查询:

索引失效:

MySQL索引失效原理

可以看到是using index并且key_len是8,也就是两个字段的索引都用到了,这也对应着联合索引排列的规律:a字段相同的情况下,b字段有序排列。

以上几种情况可以总结为:不符合最左前缀匹配原则导致索引失效。

最左匹配前缀保证可以利用到索引排序的有序性,而把等值查询放在前面,范围查询放在后面,是利用了[前缀字段相等的情况下,后面的索引字段有序]这个特性,是特殊意义下的最左前缀匹配原则。

2、再来看看哪些情况会破坏索引的有序性。

– 对索引字段做函数操作

对索引字段做函数操作,比如y=f(x),并不能保证得到的y的值依然是有序的,在这种弄个情况下,优化器会放弃树的搜索功能,但是不排除优化器在发现该索引树比主键索引小很多的情况下,选择扫描这个索引。

– 隐式类型转换

在 mysql 中,字符串和数字做比较的话,是将字符串转换成数字。隐式类型转换的本质是对索引字段使用了cast()函数,原理同上。

– 隐式字符编码转换

字符串编码转换的本质是使用了convert() 函数。

3、总结

索引失效的原因是优化器发现不能利用索引的有序性,因此在使用索引时,要尽量满足最左前缀匹配原则、范围查询放在最后、不使用%like、 %like%等模糊查询,就是在最大程度利用索引的有序性;但是在某些情况下,优化器只是放弃索引树的搜索功能,可能还是会选择扫描这个索引。

到此这篇关于mysql索引失效原理的文章就介绍到这了,更多相关mysql索引失效内容请搜索钦钦技术栈以前的文章或继续浏览下面的相关文章希望大家以后多多支持钦钦技术栈!

原文链接:https://juejin.cn/post/7038582872650809381

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/3673/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年 7月 20日 10:11:22
下一篇 2022年 7月 20日 10:11:29

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!