MySQL中索引失效的问题如何排查？

面试记录 2026-09-17

题目：MySQL 中索引失效的问题如何排查？

难度：medium

标签：db/mysql/index, mysql/index, difficulty/medium

评分：⭐⭐☆☆（2/5）

我的回答

索引失效，SQL，MySQL 索引失效。失效的话，首先你要加了索引，对吧？MySQL 里边这个执行的 SQL 语句，使用的查询记录中使用的索引的字段。然后我们看一下执行计划，并没有走这个索引字段。然后我们我们分析可能的情况。首先走索引需要它目标值匹配，也就是说你使用的查询方式跟你所走的索引能匹配的上。首先是字段类型，如果是类似于 char 跟 int 类型去匹配，这时候可能就会没有走你对应的设置的索引。另外就是是否加了特殊的处理，比如说字段上用了计算值，比如说成本，你比较的时候使用了加几，在成本的字段上加了多少，然后去算一个值。另外就是你做了一些特殊处理，比如截取，最终导致加了一个函数，导致你的对你的设置索引的字段出现了特殊处理，使得它不属于核心。另外就是可能说你使用了索引，但是底侧你使用了非索引字段去排序，最终致它觉得优化器觉得你这使用的索引不合理，或者是说成本太高了，反而它去直接使用全表而不走你的索引，这时候可能要做联合索引去处理相关的问题。

点评

优点：

• 有基本排查流程：先看执行计划确认未走索引，再分析原因
• 抓到几个高频场景：隐式类型转换、字段上施加函数/计算、ORDER BY 非索引列

不足：

• 未提关键排查工具：EXPLAIN FORMAT=JSON 的 missing_covering_index / attached_condition、sys.schema_index_statistics 的 rows_selected/rows_examined 比、performance_schema.table_io_waits_summary_by_index_usage
• 未列出高频失效清单：LIKE '%xx' 左模糊、OR 拼接非索引列、NOT IN、!=、< >、IS NOT NULL
• 未提排序/分组失效：ORDER BY 与 GROUP BY 走的是不同索引时的 filesort
• "优化器觉得不合理"过于模糊，未说明具体判断依据（cost、rows、key_len）
• 未提 ANALYZE TABLE 更新统计信息、FORCE INDEX 强制走索引、optimizer_switch 关闭 index_merge 等兜底手段
• 未提 MySQL 8.0 的 EXPLAIN ANALYZE 真实执行代价

补充：

• 排查三步走：EXPLAIN 看 key=NULL → EXPLAIN FORMAT=JSON 看 cost_info 与 missing_covering_index → SHOW INDEX 确认索引还在
• 常见失效清单：LIKE 左模糊 / OR 拼接 / 隐式转换 / 函数包装 / NOT IN / != / < > / IS NULL / ORDER BY 非索引列 / charset 不一致
• 强制手段：ANALYZE TABLE 刷新统计 → FORCE INDEX 硬推 → 关闭 index_merge 观察 → 拆分 SQL 到合适索引

完整讲解

第一部分：知识重点剖析

核心考点定位：掌握一套从"发现未走索引"到"定位失效原因"再到"给出修复方案"的完整排查链。

知识点拆解：

1. 确认索引确实存在：SHOW INDEX FROM t 看索引列、Cardinality 基数是否正常、Non_unique 是否唯一。索引不存在或列写错是最基础的可能。
2. 确认 SQL 真的没走索引：

• EXPLAIN 看 key 列是否为 NULL、rows 是否等于全表、Extra 是否有 Using filesort / Using temporary
• EXPLAIN FORMAT=JSON 看 missing_covering_index 建议、cost_info.query_cost
• sys.schema_index_statistics：rows_selected / rows_examined 长期低于 1% 说明索引扫描了大量无用行
• performance_schema.table_io_waits_summary_by_index_usage：索引长期 count_star=0 说明这个索引根本没人用
• MySQL 8.0：EXPLAIN ANALYZE 看真实执行代价

1. 常见失效场景清单：

• 隐式类型转换：字符串列 vs 数字条件（WHERE varchar_col = 123），MySQL 会强制把 varchar 转 number，索引失效
• 字段上做函数/运算：WHERE YEAR(create_time)=2024、WHERE UPPER(name)='A'、WHERE age+1=10
• 字符集/排序规则不一致：JOIN 时两表字段 charset 或 collation 不同
• 左模糊：WHERE name LIKE '%ab'，前缀不确定无法用 B+ 树有序性
• OR 拼接非索引列：WHERE a=1 OR non_index=2 退化为全表
• NOT IN、!=、< >、IS NOT NULL：范围或反向判断，优化器倾向全表
• ORDER BY / GROUP BY 非索引列：引发 filesort，若排序后需要大量回表，优化器可能放弃索引
• 多值匹配：WHERE FIND_IN_SET('x', col)、col LIKE '%a%' 全失效
• IN 元素过多：几百个值优化器倾向全表（受 eq_range_index_dive_limit 影响）

1. 修复方案：

• 改写 SQL：函数包装 → 反向范围条件（YEAR(t)=2024 → t >= '2024-01-01' AND t < '2025-01-01'）
• 加索引：新建覆盖索引 / 联合索引
• 强制手段：ANALYZE TABLE 刷新统计信息、FORCE INDEX 硬推、临时 optimizer_switch 关闭 index_merge
• 索引设计：把高频查询列放到联合索引前面，把选择性高的列放前面

第二部分：考察方向与延伸递进

考察方向：

• 深度：能否说清"为什么某个 SQL 没走索引"的优化器决策逻辑（cost 模型）
• 广度：能否覆盖 sys schema、performance_schema、INFORMATION_SCHEMA 三层视图
• 应用：能否在真实线上慢查询中快速定位并给出修复方案

延伸递进面试题：

1. 为什么 EXPLAIN 显示的 rows 可能比实际扫描行数大或小很多？

• 解答要点：rows 是基于统计信息的估算，ANALYZE TABLE 后会更准；8.0 用 EXPLAIN ANALYZE 看真实值。

1. IN (1,2,...,500) 里的 500 个值为什么不走索引？

• 解答要点：受 eq_range_index_dive_limit（默认 200）限制，超过阈值优化器用 statistics 估算而不是精确 index dive，估算偏大导致选全表。可以 SET eq_range_index_dive_limit=1000。

1. sys.statement_analysis 里怎么定位长期不走的索引？

• 解答要点：JOIN schema_index_statistics 看 count_star=0 且 rows_examined>0 的索引，是候选可删除索引；再结合 EXPLAIN 验证是否真的没人用。

第三部分：面试者参考回答（口语化）

面试官您好，索引失效排查我一般分三步走。

第一步是确认现象。跑一下 EXPLAIN，重点看三个字段：key 是不是 NULL，如果是就是没走索引；rows 是不是接近全表；Extra 里有没有 Using filesort 或 Using temporary。如果不确定，可以再跑 EXPLAIN FORMAT=JSON，里面会有 missing_covering_index 建议和 cost_info 的估算代价，MySQL 8.0 还有 EXPLAIN ANALYZE 能看真实执行耗时。

第二步是定位原因，按几个高频场景去排查。第一类是隐式转换，比如 varchar 列跟数字比较，WHERE code=123 而不是 WHERE code='123'，MySQL 会把 varchar 强制转 number，索引就废了。第二类是字段上加了函数或运算，比如 WHERE YEAR(create_time)=2024、WHERE UPPER(name)='A'、WHERE age+1=10，这些都会让索引失效，标准改法是反向写成范围条件 create_time >= '2024-01-01' AND create_time < '2025-01-01'。第三类是查询模式问题，LIKE 左模糊、OR 拼接非索引列、NOT IN、!=、< >、IS NOT NULL，这些都会让优化器倾向全表。第四类是排序或分组问题，ORDER BY 一个非索引列会触发 filesort，如果文件排序代价高，优化器会放弃索引走全表。第五类是统计信息过期，MySQL 是基于采样估算的，数据倾斜严重时会选错。

第三步是修复。SQL 能改就改，把函数包装改成范围条件。改不了的加索引，联合索引要按高频查询列的顺序放。如果怀疑是统计信息不准，先跑 ANALYZE TABLE 刷新一下。真要保底可以 FORCE INDEX 硬推，但要配合 EXPLAIN ANALYZE 验证真实代价，别引入更差的全索引扫描。

日常我也会盯着 sys.schema_index_statistics 和 performance_schema.table_io_waits_summary_by_index_usage，发现 count_star=0 或者 rows_selected/rows_examined 长期低于 1% 的索引，就是失效或者无效索引，该改该删。

https://chat.deepseek.com/share/jqfr120k19i2vcl6e3