MySQL为什么会选错索引，如何解决？

面试记录 2026-09-17

题目：MySQL 为什么会选错索引，如何解决？

难度：medium

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

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

我的回答

MySQL 走索引，可能是预期不符，它计算的执行计划里边，它看到的成本可能与我们预估的不一样，或者是存在计算的时候，它统计的数据和实际的数据存在误差。这时候就会导致它计算的成本走它其他的索引，而不选择我们预期中的索引。这时候我们可以其实可以做强走索引，或者是重新生成索引，把数据索引数据刷新一下。不是重新生成索引，是刷新索引。这时候的话可以让 MySQL 去重新计算这个索引的成本，然后可以看一下是否走了我们预期索引。然后就是实在不行就强加索引，走强制索引，我们指定强制我们那个索引，但加强制索引有风险。因为会导致一些死硬的编程，这因为加强制索引会写到 SQL 里边。之前碰见过类似的情况，就是加强制索引后，对应的这个字段的索引后续觉得没有用了被删除了，但是业务里边有加强索引的地方，直接导致对应业务报错生产事故。

点评

优点：

• 抓到本质：优化器基于成本估算决策，采样统计与实际分布有偏差
• 提到 ANALYZE TABLE 刷新统计信息
• 有真实生产事故案例（FORCE INDEX 硬编码导致后续索引被删报错），有实战感

不足：

• 未具体讲成本模型：cost = rows × cpu_cost + pages × io_cost
• 未提采样基数：innodb_stats_persistent_sample_pages（默认 20 页），倾斜数据下必翻车
• 未提 MySQL 8.0 的直方图：ANALYZE TABLE t UPDATE HISTOGRAM ON col，专门解决长尾分布
• 未提优化器开关：optimizer_switch、eq_range_index_dive_limit、prefer_ordering_index、index_merge
• 未提 EXPLAIN FORMAT=JSON 里 cost_info / attached_condition 对比
• 未提 IGNORE INDEX 与 FORCE INDEX 的区别
• 未提索引治理：performance_schema.table_io_waits_summary_by_index_usage.count_star=0 找无索引

补充：

• 选错索引的三大根因：统计信息过期 / 数据倾斜（采样失真）/ IN (...) 元素过多越过 eq_range_index_dive_limit
• 修复优先级：改写 SQL > 补索引 > 刷新统计 > FORCE INDEX
• FORCE INDEX 上线前必须 EXPLAIN ANALYZE 验证真实代价
• 8.0 更推荐 hints：/+ IGNORE_INDEX(t idx) / 或 /+ FORCE_INDEX(t idx) /

完整讲解

第一部分：知识重点剖析

核心考点定位：理解 MySQL 优化器基于成本模型的决策机制，掌握选错索引的诊断与修复手段。

知识点拆解：

1. 优化器成本模型：MySQL 使用基于成本的优化器（CBO），核心公式 cost = cpu_cost × rows + io_cost × pages_read。它会为每种可能计划算 cost，选最小的。cost 是基于统计信息估算出来的，不是真实扫描值，这就是选错索引的根源。
2. 统计信息从哪来：InnoDB 持久化统计（innodb_stats_persistent=ON），用 innodb_stats_persistent_sample_pages（默认 20 页）随机采样估算基数。采样只有 20 页，数据倾斜、热点集中在少数页时估算必失准。
3. 选错索引的典型场景：

• 统计信息过期：数据大量增删后没 ANALYZE，优化器用旧基数算
• 数据倾斜：某个值占比 90%，采样页没扫到，优化器以为"很分散"
• IN 元素过多：超过 eq_range_index_dive_limit（默认 200），从精确 index dive 切换成基于统计估算
• 多索引可选：(a)、(a,b)、(a,c) 都存在，优化器挑了错误的
• ORDER BY 排序列：优化器偏好 prefer_ordering_index=on，即使成本更高也倾向避免 filesort
• index_merge 陷阱：优化器认为 index merge 更快但实际更慢

1. 诊断手段：

• EXPLAIN 看 key、rows、Extra、key_len
• EXPLAIN FORMAT=JSON：看 cost_info.query_cost、attached_condition、missing_covering_index
• EXPLAIN ANALYZE（8.0）：看真实执行耗时与估算的差距
• SHOW INDEX FROM t：看 Cardinality 是否合理
• sys.statement_analysis：找慢查询关联的 index 使用

1. 修复手段（按优先级）：
2. 改写 SQL：把无法用索引的写法改掉（去掉函数包装、拆 OR 为 UNION）
3. 补索引/调索引：加更贴合查询的索引
4. 刷新统计：ANALYZE TABLE 或 8.0 建直方图 ANALYZE TABLE t UPDATE HISTOGRAM ON col
5. 优化器开关：调 optimizer_switch、eq_range_index_dive_limit
6. FORCE INDEX / IGNORE INDEX：硬指定，最后兜底
7. 8.0 Optimizer Hints：/+ FORCE_INDEX(t idx) /，写在 SQL 里同样有硬编码风险
8. 工程治理：

• 定期扫描 performance_schema.table_io_waits_summary_by_index_usage，count_star=0 的索引是候选删除
• 建索引、删索引要有 CR 流程，避免"索引删了但 SQL 还在 FORCE INDEX"
• 生产环境变更前跑 EXPLAIN ANALYZE 看真实代价

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

考察方向：

• 深度：能否讲清成本模型的估算逻辑，采样为什么会导致失真
• 广度：能否覆盖统计信息、优化器开关、直方图、Hints 全链条
• 应用：能否在真实线上事故中给出一套排查与兜底方案

延伸递进面试题：

1. eq_range_index_dive_limit 是什么？为什么超过它反而选错？

• 解答要点：控制 IN 或范围条件里 index dive 的最大次数。超过后优化器退回用 stats.records 均分估算，倾斜数据下偏差巨大。

1. prefer_ordering_index 是干什么的？为什么会导致"更贵的索引被选中"？

• 解答要点：默认 on，优化器偏好能避免 filesort 的索引。即使该索引要扫描更多行，只要省了排序就选它。

1. MySQL 8.0 直方图是什么？什么时候该建？

• 解答要点：ANALYZE TABLE t UPDATE HISTOGRAM ON col 会把列的值分布采样存下来，帮助优化器更准地估算基数。适合长尾分布（如 status 字段 90% 都是 1）场景。

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

面试官您好，MySQL 选错索引本质上是基于成本的优化器（CBO）算错了成本。它做决策的核心是 cost = rows × cpu_cost + pages × io_cost，rows 和 pages 都来自统计信息，而统计信息是 InnoDB 通过 innodb_stats_persistent_sample_pages 采样估算出来的，默认只采样 20 页。所以只要数据有倾斜、采样页没覆盖到热点分布，优化器就会算错行数，选到错误的索引。

典型场景有几个：一是统计信息过期，数据大量增删后没跑 ANALYZE，优化器用旧数据算；二是数据倾斜，某个值占比 90% 但采样没扫到；三是 IN 元素超过 eq_range_index_dive_limit（默认 200），优化器从精确 index dive 退回均分估算；四是排序偏好，prefer_ordering_index=on 让优化器倾向选能避免 filesort 的索引，即使要扫更多行。

排查上我会先跑 EXPLAIN 看 key、rows、Extra，再用 EXPLAIN FORMAT=JSON 看 cost_info 和 attached_condition，MySQL 8.0 最好用 EXPLAIN ANALYZE 直接看真实耗时和估算的差异，差距越大说明统计信息越不准。

修复我按优先级来：先改 SQL，把走不到索引的写法改掉（比如函数包装改范围条件，OR 拆 UNION）；再考虑补索引或者调整联合索引顺序；然后跑 ANALYZE TABLE 刷新统计，如果是长尾分布的场景，MySQL 8.0 还可以建直方图 ANALYZE TABLE t UPDATE HISTOGRAM ON col；实在不行再动 optimizer_switch 或者硬指定。

FORCE INDEX 我一般放最后手段。它写死在 SQL 里有两个风险：一是数据变化后强走可能变成全索引扫描反而更慢；二是我之前踩过坑——代码里 FORCE INDEX 硬编码，后来团队把这个索引当冗余删了，业务直接生产报错。所以我的原则是 FORCE INDEX 上线前必须 EXPLAIN ANALYZE 验证真实代价，而且要有索引治理流程，通过 performance_schema.table_io_waits_summary_by_index_usage 定期扫 count_star=0 的索引，删之前先 grep 代码库确认没人引用。

https://chat.deepseek.com/share/9fe4n94b1weytntp9b