MySQL索引失效的原因及问题排查_Mysql

1-引言：什么是mysql的索引失效？(what、why)

1-1 索引失效定义

在mysql中，索引是用来加快检索数据库记录的一种数据结构。
索引失效指的是在进行查询操作时，本应该使用索引来提升查询效率的场景下，数据库没有利用索引，而是采用了全表扫描的方式，这会大大增加查询时间和系统负担。

1-2 为什么排查索引失效

排查索引失效的原因是至关重要的，主要有以下方面：

1. 提高查询效率：索引的主要目的是加快数据检索速度。当索引失效时，数据库系统可能退回到更慢的查询方法，如全表扫描，这会显著增加查询时间和降低整体性能。
2. 降低服务器负载：使用索引可以显著减少数据库处理查询所需处理的数据量，从而减少cpu使用率和io读写。如果索引失效，数据库必须加载更多数据，这会增加服务器的负载和资源消耗。

2- 索引失效的原因及排查（how）

2-1 索引失效的情况

以以下的学生信息表举例

create table `student_info` (
  `student_id` int(11) not null auto_increment,
  `student_name` varchar(50) not null,
  `student_age` int(11) default null,
  `enrollment_date` datetime default null,
  primary key (`student_id`),
  unique key `student_name` (`student_name`),
  key `student_age` (`student_age`),
  key `enrollment_date` (`enrollment_date`)
) engine=innodb default charset=utf8mb4;

表的索引情况：
总结来说，表 student_info 有四个字段上定义了索引：
- 一个主键索引 student_id
- 一个唯一索引 student_name
- 以及两个普通索引 student_age 和 enrollment_date。

① 索引列参与计算

正常的通过 age 去做查询
- 走的是 student_age 的索引

explain select * from student_info where student_age=21;

如果索引列参与了计算进行查询
- 索引失效

explain select * from student_info where student_age+1 =21;

如果不是对列进行计算，而是对列等号右侧的值进行计算，结果还是走索引的。

② 对索引列进行函数操作

正常的查询——>走索引

explain select * from student_info where enrollment_date = '2022-09-04 08:00:00';

如果对查询的字段加上函数操作时，索引失效

explain select * from student_info where year(enrollment_date) = 2022;

③ 查询中使用了 or 两边有范围查询 > 或 <

正常情况查询，查询使用 student_name 索引

explain select * from student_info where student_name='helen' and student_age>15;

如果使用了 or 进行查询，两边包含范围查询 > 或 <
- 此时索引失效

explain select * from student_info where student_name='helen' or student_age>15;

如果没有范围查询下使用 or 还是正常走索引

explain select * from student_info where student_name='helen' or student_age=18;

④ like 操作：以 % 开头的 like 查询

以 % 开头的 like 查询比如 like ‘%abc’;;

⑤ 不等于比较 !=

在mysql中 != 比较有可能会导致不走索引，但如果对 id 进行 != 比较，是有可能走索引的。
!= 比较是否走索引，与索引的选择、数据分布情况有关，不单是由于查询包含 != 而引起的。

⑥ order by

如果使用 order by 时，表中的数据量很小，数据库会直接在内存中进行排序，而不使用索引

⑦ 使用 in

使用 in 的时候，有可能走索引，也有可能不走索引。当在 in 的取值范围比较大的时候有可能会导致索引失效，走全表扫描（not in 和 in的失效场景相同）。

2-2 索引失效的排查

使用 explain 排查

和 mysql 慢查询的排查类似，使用 explain 语句来进行排查。

需要关注的字段：type、key、extra

我们可以根据 key、type、extra 来判断一条语句是否走了索引。
一般走索引的情况：
- key 值不为 null
- type 值应该为 ref、eq_ref、range、const 这几个
- extra 的话如果是 null，或者 using indedx，using index condition 都是可以的

索引失效情况

如果一条语句出现了 type 值为 all、key 为 null，extra = using where 此时是索引失效了

此时就需要排查索引失效的原因

索引是否符合最左前缀匹配
查询语句出现以上 7 种情况

3- 总结：索引失效知识点小结

mysql中什么情况下会出现索引失效？如何排查索引失效？
回答
:::info
mysql中索引失效的情况有

1. 比如联合索引在查询的过程中不符合最左前缀原则，此时联合索引会失效
1. 查询的语句索引列进行计算，此时会使得索引失效
1. 查询的语句对索引列进行了函数操作，比如利用了 **year()** 函数
1. 查询语句中包含 **or** ，且 or 两侧有范围查询也就是 **>** 或 **<** 此时索引会失效
1. 查询语句中使用了 **like** 且 **like** 中存在以**%** 开头的匹配，此时索引会失效
1. 查询语句中使用了 **!=** 进行比较，但这种情况也和数据的分布情况有关系，
1. 当数据表中的数据较少，使用 **order by** 的时候，可能会不走索引直接在内存中进行排序
1. 当使用 **in**** **时候取值范围比较大的时候有可能会导致索引失效

索引失效的排查

① 使用 explain 对 sql 语句进行排查
需要关注的字段有 **type**、**key**、**extra**
如果一条语句出现了 type 值为 all、key 为 null，extra = using where 此时是索引失效了

此时就需要排查索引失效的原因，是否存在以上情况

以上就是mysql索引失效的原因及问题排查的详细内容，更多关于mysql索引失效的资料请关注代码网其它相关文章！

MySQL索引失效的原因及问题排查

2024年05月26日 • Mysql •我要评论