面试官：什么情况会导致 MySQL 索引失效？

古城微笑少年丶 2024-04-06 09:24 105阅读 0赞

为了验证 MySQL 中哪些情况下会导致索引失效，我们可以借助 explain 执行计划来分析索引失效的具体场景。

explain 使用如下，只需要在查询的 SQL 前面添加上 explain 关键字即可，如下图所示：

![8f0f4ad22eac40cd9a325c596b1ca448.png][]  
而以上查询结果的列中，我们最主要观察 key 这一列，key 这一列表示实际使用的索引，如果为 NULL 则表示未使用索引，反之则使用了索引。

以上所有结果列说明如下：

id — 选择标识符，id 越大优先级越高，越先被执行；  
select\_type — 表示查询的类型；  
table — 输出结果集的表；  
partitions — 匹配的分区；  
type — 表示表的连接类型；  
possible\_keys — 表示查询时，可能使用的索引；  
key — 表示实际使用的索引；  
key\_len — 索引字段的长度；  
ref— 列与索引的比较；  
rows — 大概估算的行数；  
filtered — 按表条件过滤的行百分比；  
Extra — 执行情况的描述和说明。  
其中最重要的就是 type 字段，type 值类型如下：

all — 扫描全表数据；  
index — 遍历索引；  
range — 索引范围查找；  
index\_subquery — 在子查询中使用 ref；  
unique\_subquery — 在子查询中使用 eq\_ref；  
ref\_or\_null — 对 null 进行索引的优化的 ref；  
fulltext — 使用全文索引；  
ref — 使用非唯一索引查找数据；  
eq\_ref — 在 join 查询中使用主键或唯一索引关联；  
const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq\_ref 效率高一点。  
创建测试表和数据

为了演示和测试那种情况下会导致索引失效，我们先创建一个测试表和相应的数据：

![ddd413664dc04e2f87d3690a29978bba.png][]  
当前表中总共有 3 个索引，如下图所示：

![40642977369047769d28479d1e3cf909.png][]

PS：本文以下内容基于 MySQL 5.7 InnoDB 数据引擎下。  
索引失效情况1：非最左匹配

最左匹配原则指的是，以最左边的为起点字段查询可以使用联合索引，否则将不能使用联合索引。 我们本文的联合索引的字段顺序是 sn + name + age，我们假设它们的顺序是 A + B + C，以下联合索引的使用情况如下：

![8de6f338b28f400d811fc5157f0bc33b.png][]  
从上述结果可以看出，如果是以最左边开始匹配的字段都可以使用上联合索引，比如：

A+B+C  
A+B  
A+C 其中：A 等于字段 sn，B 等于字段 name，C 等于字段 age。  
而 B+C 却不能使用到联合索引，这就是最左匹配原则。

索引失效情况2：错误模糊查询

模糊查询 like 的常见用法有 3 种：

模糊匹配后面任意字符：like '张%'  
模糊匹配前面任意字符：like '%张'  
模糊匹配前后任意字符：like '%张%'  
而这 3 种模糊查询中只有第 1 种查询方式可以使用到索引，具体执行结果如下：

![4da66aadc456417a99fded1cffdf4da5.png][]

索引失效情况3：列运算

如果索引列使用了运算，那么索引也会失效，如下图所示：

![f6a39ca0d00b4ced94c5558f6a132765.png][]

索引失效情况4：使用函数

查询列如果使用任意 MySQL 提供的函数就会导致索引失效，比如以下列使用了 ifnull 函数之后的执行计划如下：

![3ff9a640e81048f180ac03270d341d00.png][]

索引失效情况5：类型转换

如果索引列存在类型转换，那么也不会走索引，比如 address 为字符串类型，而查询的时候设置了 int 类型的值就会导致索引失效，如下图所示：

![2893fa7035884bff9fd07398872c80ae.png][]

索引失效情况6：使用 is not null

当在查询中使用了 is not null 也会导致索引失效，而 is null 则会正常触发索引的，如下图所示：

![090c62df369846ac9051ed2b5d112ed8.png][]

总结

导致 MySQL 索引失效的常见场景有以下 6 种：

联合索引不满足最左匹配原则。  
模糊查询最前面的为不确定匹配字符。  
索引列参与了运算。  
索引列使用了函数。  
索引列存在类型转换。  
索引列使用 is not null 查询。

[8f0f4ad22eac40cd9a325c596b1ca448.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/a17daea641f94415a027123fc67d2566.png
[ddd413664dc04e2f87d3690a29978bba.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/27336b3ce67843fea53489780811d939.png
[40642977369047769d28479d1e3cf909.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/85ce7094718b442f908cd01172807925.png
[8de6f338b28f400d811fc5157f0bc33b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/40808ee13c5746d0a4f13d77034df9f1.png
[4da66aadc456417a99fded1cffdf4da5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/6e375c8173174b9581308d922f4f0fbf.png
[f6a39ca0d00b4ced94c5558f6a132765.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/529d28381f444065a8cf519cd328a9d7.png
[3ff9a640e81048f180ac03270d341d00.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/14ca1c5f802b474bbfe4fc880ea02425.png
[2893fa7035884bff9fd07398872c80ae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/26e13743136c4c1298203d9dc7b8f491.png
[090c62df369846ac9051ed2b5d112ed8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/c377c50e624643a09f9c4a5acf36df29.png