MySQL索引失效的原因

最新推荐文章于 2024-05-04 13:51:18 发布

鱼在树上飞

最新推荐文章于 2024-05-04 13:51:18 发布

阅读量533

点赞数 1

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/mrqiuwen/article/details/132421828

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

索引失效的6个原因

首先并不意味着查询条件用上了索引列，查询过程就一定都能用上索引。下面列举了一些索引失效的情况，整理自小林coding的笔记

一、对索引使用左或者左右模糊匹配

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

比如下面的 like 语句，查询 name 后缀为「林」的用户，执行计划中的 type=ALL 就代表了全表扫描，而没有走索引。

// 前提：name 字段为二级索引
select * from t_user where name like '%林';

在这里插入图片描述

很正常，因为name为索引的话，那也是按照首字母顺序排序的，%林，可能是王林、周林、陈林…

而name为索引是按照姓氏来排列的：
在这里插入图片描述

而前缀指定，右模糊匹配，如like 林%就能用上索引

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yuG6tGEt-1692669612732)(C:\Users\邱文\AppData\Roaming\Typora\typora-user-images\image-20230707162403252.png)]$

二、对索引使用函数

如果查询条件中对索引字段使用函数，就会导致索引失效。

比如下面这条语句查询条件中对 name 字段使用了 length() 函数，执行计划中的 type=ALL，代表了全表扫描：

在这里插入图片描述

很正常，因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

但是如果就是想走索引的话，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

比如先为length(name)的结果创建一个函数索引

alter table t_user add key idx_name_length ((length(name)));

再执行上面的语句时，就能走索引了：
在这里插入图片描述

三、对索引进行表达式计算

用表达式无法走索引的原因，这个和使用函数一样的，表达式不就是一个函数吗，如下：
在这里插入图片描述

但是，如果把查询语句的条件改成 where id = 10 - 1，这样就不是在索引字段进行表达式计算了，于是就可以走索引查询了，因为先把等号左边算好了，就是where id = 9;

在这里插入图片描述

不过，MySQL为什么不做一下查询优化，直接转换表达式呢？比方将 id + 1 = 10 变成 id = 10 - 1，这样不就能走索引了。

但是 MySQL 还是偷了这个懒，没有实现。

我的想法是，可能也是因为，表达式计算的情况多种多样，每种都要考虑的话，代码可能会很臃肿，所以干脆将这种索引失效的场景告诉程序员，让程序员自己保证在查询条件中不要对索引进行表达式计算

四、对索引进行隐式类型转换

如果索引字段是字符串类型，查询的时候输入的是整型，那就整表扫描不走索引；

但反过来，如果索引是整型，查询的时候输入字符串，却可以走索引。

如，phone字段建立索引，类型为varchar，查的时候用整型去查，不走索引：

在这里插入图片描述

但是，对于id这个整型的索引字段，我们查的时候加了引号即用字符串去查，就能走索引：
在这里插入图片描述

因为，在遇到需要对字符串和整型进行比较时，MySQL是把字符串转成整型，而不是整型转字符串。

（C/C++默认也是这样）

那既然要先转成整型，

对于情况一：

索引字段是字符串，要将索引字段转成整型，那不就相当于调用了一个函数，这样不能走索引的理由和前面一样了，等效于如下SQL：
在这里插入图片描述

对于情况二：

索引字段不需要转类型，因为它是整型数字，而查询的参数是，那就转换成数字，依然能走索引：

在这里插入图片描述

五、联合索引非最左匹配

一般都是设计联合索引，很少用单个字段做索引，因为还是要尽可能让索引数量少，避免磁盘占用太多，影响增删改性能。

有个表存储学生成绩，id是自增主键，包含学生班级、学生姓名、科目名称、成绩分数四个字段，平时查询，可能比较多的就是查找某个班的某个学生的某个科目的成绩。

所以，我们可以针对**【学生班级，学生姓名，科目名称】**建立一个联合索引（二级索引，id是主键索引，所以也涉及回表）

假设搜索：1班+张小强+数学的成绩，你可能写

select * from student_score where class_name='1班' and student_name='张小强' and subject_name='数学'

where条件里的几个字段都是等值查询，且where条件里的几个字段名称和顺序也跟你的联合索引一样！此时就是等值匹配规则，上面的SQL百分百可以用联合索引查询。

联合索引的图像如下，和单个值的索引一样，就是一棵B+树，叶子结点的值是主键id（因为这是一棵二级索引树）：
在这里插入图片描述

找到主键id后再回表去聚簇索引那找对应的数据行。

5.1 最左匹配原则

联合索引和单个索引一样，也是建立索引树，联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。很容易理解，就像上面先按班级排序，再按名字。

所以只要用到了最左侧的字段就可以用到索引：比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；——同 where b =2 and a=1 and c=3
where a=1 and b=2；
where a=1 and c=3; // 这个a用到了，c有没有得看MySQL版本，后面讲了

而且，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

所以where b=2 and a=1 and c=3；和上面依次a,b,c是等效的。

但是如果where没有最左字段a去查询，那就用不到索引，下面这些用不到索引：

where b=2；
where c=3；
where b=2 and c=3；

5.2 索引下推

就是对上面一个特殊的条件语句，where a=1 and c=3;

注意，下面无论哪个MySQL版本，a肯定会用到索引的。

——MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，Server 层从存储引擎层获取到数据行后，然后在 Server 层再比对 c 字段的值。

索引是存储引擎层的，而c是等a回表拿到整行数据返回到server层才开始做数据比对，意思就是c没用到索引

这里的开销：回表（这个占大头，需要读磁盘）、字段比对

——从 MySQL 5.6 之后，有一个索引下推功能，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段（如c）先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。

索引下推的大概原理是：截断的字段像c不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

在存储引擎层就对c字段进行了过滤，虽然c和a之间断开了不连续，但是也接近于用到了c这个索引（但依旧是先按a字段查索引树，只是不着急去回表然后把整行数据返回给server，因此此时会有多条符合a=1的数据行；而是在存储引擎层先把c=3的条件拿去过滤，这样就回表次数就少了）

索引下推为什么能减少回表次数？

因为没有索引下推的时候，找到一个a=1就认为符合条件，然后根据其id去回表，很多a=1的那就回表多次。

而有索引下推时，多一个c=3的过滤，那回表次数自然就更少了，但肯定还要回表，才能获取整行数据。

比如下面这条 where a = 1 and c = 0 语句，我们可以从执行计划中的 Extra=Using index condition 知道使用了索引下推功能

在这里插入图片描述

六、WHERE 子句中的 OR

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

举个例子，比如下面的查询语句，id 是主键，age 是普通列，从执行计划的结果看，是走了全表扫描

select * from t_user where id = 1 or age = 18;

因为age没有索引，而这两个又满足其中一个就行，所以age必然是全表扫描。

那么会是id先按索引找到，再age去全表扫吗——不大可能，后者已经全表扫描了，那一次性全表扫描找出符合两个条件的不是更好，没必要前面多走一趟索引；这个应该是看优化器怎么优化，而且最后生成的执行计划是什么样的。

查看执行计划也发现一个索引都没用到，走的全表扫描：

在这里插入图片描述

也就是说：or语句中，只要有一个条件列不是索引列，就会进行全表扫描

解决的办法就是将age字段设置为索引，下面 type=index merge， index merge 的意思就是对 id 和 age 分别进行了扫描（分别走一次索引），然后将这两个结果集进行了合并，这样做的好处就是避免了全表扫描

在这里插入图片描述

总结

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列使用函数，就会导致索引失效。
当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。