目录
- 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
可以得到的规律是：优先按a字段从小到大排序，a字段相等的按b字段从小到大排序；
分析以下情况，索引是否会失效以及失效的原因：
条件只包含b字段
select * from test where b=2;
索引失效：
显然，走的时候全文扫描，并没有使用索引。因为只看b字段的索引，是2,4,1,3,4,5，并不能利用索引的有序性快速定位。
对a字段范围查询：
select * from test where a>1 and b=2;
索引失效：
可以看到，索引并没有完全失效，而是先利用索引定位到a的位置。因为这里的key_len是4，而联合索引的key_len是8。
对a字段等值查询，b字段范围查询：
索引失效：
可以看到是using index并且key_len是8，也就是两个字段的索引都用到了，这也对应着联合索引排列的规律：a字段相同的情况下，b字段有序排列。
以上几种情况可以总结为：不符合最左前缀匹配原则导致索引失效。
最左匹配前缀保证可以利用到索引排序的有序性，而把等值查询放在前面，范围查询放在后面，是利用了[前缀字段相等的情况下，后面的索引字段有序]这个特性，是特殊意义下的最左前缀匹配原则。
2、再来看看哪些情况会破坏索引的有序性。
– 对索引字段做函数操作
对索引字段做函数操作，比如y=f(x),并不能保证得到的y的值依然是有序的，在这种弄个情况下，优化器会放弃树的搜索功能，但是不排除优化器在发现该索引树比主键索引小很多的情况下，选择扫描这个索引。
– 隐式类型转换
在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。隐式类型转换的本质是对索引字段使用了CAST()函数，原理同上。
– 隐式字符编码转换
字符串编码转换的本质是使用了CONVERT() 函数。
3、总结
索引失效的原因是优化器发现不能利用索引的有序性，因此在使用索引时，要尽量满足最左前缀匹配原则、范围查询放在最后、不使用%like、 %like%等模糊查询，就是在最大程度利用索引的有序性；但是在某些情况下，优化器只是放弃索引树的搜索功能，可能还是会选择扫描这个索引。
到此这篇关于MySQL索引失效原理的文章就介绍到这了,更多相关MySQL索引失效内容请搜索NICE源码以前的文章或继续浏览下面的相关文章希望大家以后多多支持NICE源码！
