mysql索引失效的几种情况,为什么mysql 使用函数 索引失效

mysql索引失效的几种情况,为什么mysql 使用函数 索引失效,MySQL索引失效原理

索引可以加快搜索速度是因为,在每一层的兄弟节点中,索引是有序的,所以可以通过二分法快速定位到相应的位置。如果某些操作破坏了索引排列的顺序或者不能利用索引的顺序,这个索引自然就失效了。这个在下面有比较详细的解释,有需要的朋友可以参考一下。

: 目录

1.索引失败的原因2。我们来看看什么会破坏索引的顺序。

-对索引字段执行函数操作。

-隐式类型转换

-隐式字符编码转换

3.摘要

1、索引失效原因

首先,我们来看看会导致搜索无法利用索引排序的情况。

假设一个表测试有A、B、C、D四个字段,C是主键。

在a,b字段上建立联合索引(a,b):

在test(a,b)上创建索引idx _ a _ b;树联合索引。使用jpeg文件交换格式存储的编码图像文件扩展名

可以得到的规律是:

优先按A域从小到大排序,A域从小到大等于B域;

分析以下情况,指数是否会失效,原因是什么:

条件只包含b字段

select * from test其中b=2;

索引失效:

很明显,当你离开的时候,你扫描了整个文本而没有使用索引。因为只看字段B的索引,也就是2,4,1,3,4,5,无法利用索引的排序快速定位。

对a字段范围查询:

select * from test其中a1和b=2;

索引失效:

可以看到,

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

,而联合索引的key_len是8。

查询字段A的等效值和字段B的范围:

索引失效:

可以看出,它在使用index,key_len为8,即两个字段的索引都被使用,这也对应了联合索引排列的规律:当字段A相同时,字段B排序。

以上几种情况可以总结为:

不符合最左前缀匹配原则,导致索引失效。

最左边的匹配前缀保证了索引排序的排序可以被利用,而把等价查询放在前面,范围查询放在后面,就是利用了这个特性[当前缀字段相等时,后面的索引字段排序],这是一个特殊意义上的最左边的前缀匹配原理。

2.我们来看看什么会破坏索引的顺序。

-对索引字段执行函数操作。

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

-隐式类型转换

在MySQL中,当字符串与数字进行比较时,它们被转换成数字。隐式类型转换的本质是对索引字段使用CAST()函数,原理同上。

-隐式字符编码转换

字符串转换的本质是CONVERT()函数的使用。

3.摘要

索引失败的原因是优化器发现它不能利用索引的有序性。所以在使用索引的时候,要尽量满足最左边的前缀匹配原则,把范围查询放在最后,不要使用%like、%like%之类的模糊查询,也就是充分利用索引的有序性。但是,在某些情况下,优化器只是放弃了索引树的搜索功能,可能仍然会选择扫描这个索引。

关于MySQL索引失效原理的这篇文章到此为止。关于MySQL索引失效的更多信息,请搜索我们之前的文章或者继续浏览下面的相关文章。希望你以后能支持我们!

mysql索引失效的几种情况,为什么mysql 使用函数 索引失效