MySQL索引失效的场景总结

MySQL索引失效的场景总结

索引失效定义

索引失效是指在查询中，本应该使用索引来加速查询，但最终没有使用索引，最终需要进行全表扫描从而导致查询变慢或者执行计划不理想的情况。

索引失效的原因

1. 对索引使用左或者左右模糊匹配

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

// name 字段为二级索引
select * from t_user where name like '%Liu';

原因：

这是因为InnoDB索引B+树是按照**「索引值」有序排列存储**的，只能根据前缀进行比较，左模糊匹配无法有效进行前缀比较，造成索引失效。

2. 对索引使用函数

有时候我们需要使用 MySQL 自带的函数来得到我们想要的结果，这时候要注意了，如果查询条件中对索引字段使用函数，就会导致索引失效。

// name 为二级索引
select * from t_user where length(name)=6;

原因：

因为MySQL存储引擎中索引保存的是索引字段的原始值，而不是经过函数计算后的值。

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

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

3. 对索引进行表达式计算

在查询条件中对索引进行表达式计算如 + 、- 、NOT 、<> 等，也是无法走索引的，如以下语句：

select * from t_user where id + 1 = 10;

值得注意的是，如果将判断语句换成 id = 10 - 1,是可以走索引查询的。

select * from t_user where id = 10 - 1;

原因：

因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

4. 对索引隐式类型转换

如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。

// phone 为varchar类型
select * from t_user where phone = 1300000001;

而反过来则会走索引查询。

// id 为int类型
select * from t_user where id = '1';

原因：

MySQL的转换规则是 MySQL 会将自动「字符串」转换成「数字」，就相当于 select 10 > 9，这个就是数字比较，结果返回 1。

前面的例子一中的查询语句，

//例子一的查询语句
select * from t_user where phone = 1300000001;

这是因为 phone 字段为字符串，所以 MySQL 要会自动把字符串转为数字，所以这条语句相当于：

select * from t_user where CAST(phone AS signed int) = 1300000001;

可以看到，CAST 函数是作用在了 phone 字段，而 phone 字段是索引，也就是对索引使用了函数！而前面我们也说了，对索引使用函数是会导致索引失效的。

5. 联合索引非最左匹配

对主键字段建立的索引叫做聚簇索引，对普通字段建立的索引叫做二级索引。

那么多个普通字段组合在一起创建的索引就叫做联合索引，也叫组合索引。

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

• where a=1；
• where a=1 and b=2 and c=3；
• where a=1 and b=2；

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

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

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

原因：

在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

6. WHERE 子句中的 OR 语句

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

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

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

原因：

这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

参考

索引失效有哪些_小林coding