Skip to content

4.1-9 优化器选错索引的处理 ​

所属:04 数据存储 / 4.1 MySQL 分级:B(按 A 类模板展开) 状态:◐ 初稿 关联:4.1-6、4.1-7、4.1-8、4.1-10、4.1-11、4.1-12

  • 题型:排查 / 调优 / 场景

  • 一句话结论:优化器选错索引,本质是“成本估算”和“真实执行”出现偏差:统计信息过期/采样误差/数据倾斜、回表成本估算不准、ORDER BY ... LIMIT 偏好排序索引、函数/隐式转换、大 IN/range 内存超限、成本常数和版本差异都会让优化器选了更贵的计划。处理顺序是先用 EXPLAIN + EXPLAIN ANALYZE + optimizer_trace 证明“确实有更优计划且当前计划明显更差”,再判断根因是统计、成本模型、SQL 结构还是索引设计,最后按“更新统计/直方图 → 改写 SQL/调索引 → 会话级 hint/开关 → FORCE INDEX 短期止血”的顺序修复,并用压测和监控防回归。

  • 分层要点:

    1. 先定义“选错”,不要见全表就喊错:type=ALL、key=NULL 不一定是优化器错。小表、结果占比高、覆盖索引全扫描、全表顺序读都可能比走二级索引再大量回表更便宜。真正的“选错”是:存在一个可执行的更优计划,当前计划的 EXPLAIN ANALYZE 实际耗时/扫描行数明显更差,且差异不能用业务结果集大小解释。
    2. 根因分四类:统计信息(过期、采样少、数据倾斜、无直方图)、成本模型(回表随机 IO、ORDER BY ... LIMIT、成本常数、引擎成本)、SQL 结构(列上函数、隐式转换、OR、子查询/派生表、大 IN)、索引设计(缺联合索引、列序不对、冗余索引、覆盖不足)。不同根因的修复方式完全不同,先分类再动手。
    3. 诊断链要完整:慢日志找 Top SQL → EXPLAIN 看 type/key/key_len/rows/filtered/Extra → EXPLAIN ANALYZE 看 actual rows/loops/time → optimizer_trace 看 considered_access_paths/chosen/cost/why_not → SHOW WARNINGS 看改写 → SHOW INDEX/mysql.innodb_index_stats/直方图看统计 → 用 hint 或 FORCE INDEX 构造候选计划做 A/B 对比 → 压测确认。
    4. 统计信息修复:ANALYZE TABLE 重新采样;innodb_stats_persistent=ON 持久化统计;innodb_stats_persistent_sample_pages=20 控制采样页数;innodb_stats_auto_recalc=ON 在约 10% 行变化时自动重算;8.0 可对倾斜列建直方图。统计是采样估算,ANALYZE 不是万能,数据分布极端时还要靠直方图或改写。
    5. 成本模型与 ORDER BY ... LIMIT:优化器可能为了消除 filesort 选择排序列索引,结果扫描大量不满足 WHERE 的行;prefer_ordering_index(8.0.21)可以关闭这种偏好,8.0.21 之前用 IGNORE INDEX/FORCE INDEX/改写。回表成本估算不准时,覆盖索引能直接改变成本结构;eq_range_index_dive_limit(8.0 默认 200)和 range_optimizer_max_mem_size(默认 8MB)会让大 IN/复杂 range 的估算变粗或退化。
    6. SQL/索引修复:去掉列上函数和隐式转换;把 OR 拆成 UNION ALL 或补联合索引;调整联合索引列序(等值 → 范围 → 排序);高频查询做覆盖索引;避免 SELECT *;深分页改延迟关联/游标;JOIN 给被驱动表补 join 列索引。加索引不是万能,索引变多会写放大、DDL 变慢、优化器选择空间更大。
    7. hint/开关是兜底,不是正解:USE INDEX/FORCE INDEX/IGNORE INDEX、8.0 /*+ INDEX(t idx) */、NO_INDEX、JOIN_ORDER、SET_VAR(...) 都能短期稳定计划;optimizer_switch 可会话级调 prefer_ordering_index、index_merge、skip_scan 等。它们把“优化器的判断”换成“人的判断”,数据增长/分布变化后可能反噬,必须记录技术债、设监控和回滚方案。
    8. 治理与防回归:统计持久化 + 定期 ANALYZE;慢日志 + performance_schema 监控计划变化;上线前用生产数据量级压测;版本升级做计划回归;用 sys.schema_unused_indexes/sys.schema_redundant_indexes 治理索引;hint 和 FORCE INDEX 要标注原因、负责人、失效条件,避免“祖传 hint”。
  • 图/流程:

text
慢 SQL / 计划突变
  → EXPLAIN:type/key/key_len/rows/filtered/Extra
  → EXPLAIN ANALYZE:actual rows/loops/time
  → 是否存在更优候选计划?
      ├─ 否 → 当前计划可能已是最优,接受或改业务
      └─ 是 → optimizer_trace 看为什么没选
            ├─ 统计问题 → ANALYZE / 直方图 / 采样页数
            ├─ 成本问题 → 覆盖索引 / 调列序 / prefer_ordering_index
            ├─ SQL 问题 → 去函数/转换/拆 OR/改写 JOIN
            └─ 索引问题 → 补联合/覆盖索引,治理冗余
  → hint/FORCE INDEX 只在短期止血
  → 压测 + 监控 + 回滚方案
text
选错根因分类:
  统计:过期 / 采样少 / 倾斜 / 无直方图
  成本:回表估算 / ORDER BY LIMIT / 成本常数 / 引擎成本
  SQL:函数 / 隐式转换 / OR / 子查询 / 大 IN
  索引:缺联合 / 列序错 / 冗余 / 覆盖不足
text
ORDER BY ... LIMIT 的典型陷阱:
  WHERE user_id=? AND status=? ORDER BY create_time DESC LIMIT 20
    ├─ 优化器选 (create_time) 索引:避免 filesort,但扫大量行再过滤
    └─ 更优:(user_id,status,create_time) 联合索引:定位+排序一次完成
  8.0.21+:optimizer_switch='prefer_ordering_index=off'
  8.0.21-:IGNORE INDEX / FORCE INDEX / 改写
text
optimizer_trace 关注点:
  considered_access_paths
    ├─ access_type / index / rows / cost
    ├─ chosen: true/false
    └─ why_not: 成本更高 / 不满足最左前缀 / 统计估算
  best_access_path → 最终选择
text
修复优先级:
  1. 更新统计 / 直方图
  2. 改写 SQL(去函数、隐式转换、拆 OR、延迟关联)
  3. 调整/补充索引(联合、覆盖、列序)
  4. 会话级 optimizer_switch / hint
  5. FORCE INDEX 短期止血(记录技术债)
  • 版本/源码锚点:

    • MySQL 5.7:innodb_stats_persistent 默认 ON;optimizer cost tables;EXPLAIN FOR CONNECTION。
    • MySQL 8.0:直方图;不可见索引;降序索引;8.0.13 skip scan/函数索引;8.0.16 FORMAT=TREE;8.0.18 EXPLAIN ANALYZE、hash join;8.0.21 prefer_ordering_index;8.0.22 derived_condition_pushdown。
    • 关键参数:innodb_stats_persistent_sample_pages=20、innodb_stats_transient_sample_pages=8、innodb_stats_auto_recalc=ON、eq_range_index_dive_limit=200(8.0,5.7 为 10)、range_optimizer_max_mem_size=8MB、optimizer_trace_max_mem_size=1MB。
    • 成本常数:io_block_read_cost=1.0、memory_block_read_cost=0.25、row_evaluate_cost=0.1、key_compare_cost=0.05(8.0 默认,见 mysql.server_cost/mysql.engine_cost)。
    • 源码/实现:sql/sql_optimizer.cc(计划生成)、sql/opt_costmodel.cc(成本模型)、sql/opt_range.cc(range/索引选择)、sql/opt_explain.cc(EXPLAIN)、sql/sql_select.cc(JOIN/子查询)、dict/dict0stats.cc(InnoDB 统计)、handler/ha_innodb.cc(index_read_map)、storage/innobase/row/row0sel.cc(ICP/MRR)。
  • 根因 → 信号 → 修复:

根因EXPLAIN/ANALYZE 信号修复
统计过期rows 与实际差几个数量级;计划突变ANALYZE TABLE;检查自动重算
采样少估算粗糙、计划抖动调大 innodb_stats_persistent_sample_pages
数据倾斜平均值估算骗人,热点值计划差8.0 直方图;改写/分桶/缓存
回表成本高key 有值但 rows 大、Extra 无覆盖覆盖索引/联合索引;接受全表
ORDER BY ... LIMIT选了排序列索引,扫描行远大于返回行prefer_ordering_index=off;改联合索引;延迟关联
大 IN估算变粗、range 退化分批/临时表;调整 eq_range_index_dive_limit
range 内存超限复杂 OR/IN 退化成全表简化 SQL;调大 range_optimizer_max_mem_size
函数/隐式转换possible_keys 为空或 ref=func改写 SQL;8.0 函数索引
OR 非索引列type=ALL,possible_keys 为空拆 UNION ALL;补联合索引
子查询/派生表<derivedN>、物化、临时表改写 JOIN/EXISTS;8.0.22 条件下推
JOIN 顺序错驱动表选择差、Using join buffer补被驱动表索引;JOIN_ORDER/STRAIGHT_JOIN
索引列序/缺失key_len 只用前几列;filesort调整联合索引;覆盖索引
  • 诊断工具链:
工具/命令看什么注意
慢日志Top SQL、RT、扫描行数先定位,不猜
EXPLAINtype/key/key_len/rows/filtered/Extra只是估算
EXPLAIN ANALYZEactual time/rows/loops8.0.18+;会执行 SQL
optimizer_trace候选路径、成本、why_not会话级开启,注意内存
SHOW WARNINGS改写后的 SQL看隐式转换/函数
SHOW INDEX列序、Cardinality、VisibleCardinality 是估算
mysql.innodb_index_statsn_diff_pfx 等统计判断采样质量
COLUMN_STATISTICS直方图8.0
sys.schema_unused_indexes未使用索引观察窗口要完整
sys.schema_redundant_indexes冗余索引删除前评估
pt-index-usage索引使用情况Percona Toolkit
  • 统计信息与关键参数:
参数/命令默认/用法作用注意
ANALYZE TABLE手动执行重新采样统计大表有 IO/锁开销
innodb_stats_persistentON统计持久化避免重启后统计丢失
innodb_stats_persistent_sample_pages20持久化统计采样页越大越准、ANALYZE 越慢
innodb_stats_transient_sample_pages8非持久化统计采样仅相关场景
innodb_stats_auto_recalcON约 10% 行变化自动重算大事务后仍建议手动
ANALYZE TABLE ... UPDATE HISTOGRAM8.0列直方图改善倾斜列估算,不是索引
eq_range_index_dive_limit200(8.0)大 IN 改用统计过大估算粗
range_optimizer_max_mem_size8MBrange 优化内存上限超限退化全表
optimizer_trace_max_mem_size1MBtrace 内存上限太小会截断
  • hint / 开关:
手段语法/示例适用风险
USE INDEXUSE INDEX (idx)缩小候选可能选不到最优
FORCE INDEXFORCE INDEX (idx)短期强制倾向数据变化后反噬
IGNORE INDEXIGNORE INDEX (idx)排除坏索引可能全表
8.0 INDEX hint/*+ INDEX(t idx) */语句级版本/别名敏感
NO_INDEX hint/*+ NO_INDEX(t idx) */排除索引同上
JOIN_ORDER hint/*+ JOIN_ORDER(a,b) */固定连接顺序数据变化后失效
STRAIGHT_JOINSELECT STRAIGHT_JOIN ...强制左表驱动可能更慢
SET_VAR hint/*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */单语句调参需权限/版本
optimizer_switchSET SESSION optimizer_switch='...'会话级调开关全局改影响所有会话
  • 修复优先级与取舍:
优先级动作收益代价
1更新统计/直方图低成本、快速可能只是暂时稳定
2改写 SQL从根因消除业务代码改动
3调整/补充索引提升读性能写放大、DDL 成本
4会话级 hint/开关精准、可控技术债、版本敏感
5FORCE INDEX立即止血长期风险最高
  • 深挖点(面试官常追的 6 个):

    1. “选错”要先证明,不要见全表就下结论:优化器可能算得对——小表、结果占比高、全表顺序读确实更便宜。用 EXPLAIN ANALYZE 对比 actual rows/time,再用 hint 构造候选计划做 A/B,才能证明“有更优计划但没选”。
    2. 统计信息是采样,不是精确值:rows、filtered、Cardinality 都来自采样;数据倾斜、大事务、批量删除后统计会失真。ANALYZE TABLE 能缓解,但采样页数、直方图、数据分布决定上限;极端倾斜要靠直方图或改写。
    3. ORDER BY ... LIMIT 是选错高发区:优化器为了消除 filesort,可能选排序列索引,扫描大量不满足 WHERE 的行;LIMIT 越小,这种“排序索引”看起来越便宜。8.0.21 的 prefer_ordering_index 可以关掉偏好,但全局关闭可能伤害其他查询,优先用会话级/单语句 SET_VAR。
    4. 回表成本决定二级索引的生死:二级索引命中行多时,每行回表都是随机 IO;优化器估算的回表成本不准,就会选错。覆盖索引把回表变成索引内读取,直接改变成本结构;但索引变宽会写放大,要权衡。
    5. hint 是“人的判断”替代“优化器判断”:FORCE INDEX/USE INDEX/8.0 hint 能短期稳定计划,但数据增长、分布变化、版本升级后可能更慢。必须记录原因、负责人、失效条件,设监控和回滚,不能当长期方案。
    6. 版本和参数会改变计划:8.0 直方图、不可见索引、prefer_ordering_index、hash join、derived_condition_pushdown 都会影响选择;eq_range_index_dive_limit、range_optimizer_max_mem_size 会改变估算和退化行为。升级前后要做计划回归,不能假设“SQL 没变,计划就没变”。
  • 追问链:

    1. 什么叫优化器选错索引? → 存在可执行的更优计划,当前计划实际耗时/扫描行数明显更差,且不是结果集本身大导致的。
    2. 怎么证明选错? → EXPLAIN ANALYZE 看 actual rows/loops/time,用 hint/FORCE INDEX 构造候选计划做 A/B,压测对比。
    3. EXPLAIN 和 EXPLAIN ANALYZE 怎么配合? → 前者看估算计划,后者看实际执行;估算和实际差距大,通常是统计或成本模型问题。
    4. optimizer_trace 看什么? → considered_access_paths 的候选索引、rows、cost、chosen、why_not,判断是成本高还是结构不满足。
    5. 选错的常见根因有哪些? → 统计过期/采样/倾斜、回表成本估算、ORDER BY ... LIMIT、函数/隐式转换、大 IN/range 内存、JOIN 顺序、索引列序/缺失。
    6. 统计信息为什么会错? → 采样估算、数据倾斜、批量增删、大事务、自动重算阈值未触发、持久化统计未更新。
    7. ANALYZE TABLE 够吗? → 不一定;采样页数少、倾斜严重时仍不准,需要直方图、调采样页数或改写 SQL。
    8. 直方图怎么用? → 8.0 ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH N BUCKETS;改善倾斜列的基数/分布估算,不创建索引。
    9. 数据倾斜怎么办? → 直方图、改写查询、分桶/缓存热点、调整联合索引;极端热点不要只靠索引。
    10. 采样页数怎么调? → innodb_stats_persistent_sample_pages(默认 20)增大更准但 ANALYZE 更慢;先确认统计是否持久化。
    11. 回表成本怎么估算? → 二级索引命中行数 × 回表随机 IO 成本;优化器用成本常数和统计估算,覆盖索引可消除回表。
    12. 覆盖索引为什么能改变计划? → 它把“索引扫描 + 回表”变成“只读索引”,成本结构变化,优化器更愿意选它。
    13. 为什么 ORDER BY ... LIMIT 容易选错? → 优化器为消除 filesort 选排序索引,可能扫描大量不满足 WHERE 的行;LIMIT 小会让排序索引看起来更便宜。
    14. prefer_ordering_index 是什么? → 8.0.21 引入的优化器开关,控制是否优先为 ORDER BY ... LIMIT 选排序索引;可会话级关闭。
    15. 8.0.21 之前怎么办? → IGNORE INDEX/FORCE INDEX、改写 SQL、延迟关联/游标分页、补联合索引。
    16. 大 IN 列表为什么估算粗? → 超过 eq_range_index_dive_limit 后改用索引统计而不是逐值 dive,估算变粗;分批/临时表更稳。
    17. eq_range_index_dive_limit 是什么? → 控制 IN/等值范围做 index dive 的个数上限;8.0 默认 200,5.7 默认 10。
    18. range_optimizer_max_mem_size 是什么? → range 优化可用的内存上限,超限可能放弃 range 退化成全表;默认 8MB。
    19. 函数/隐式转换会导致选错吗? → 会让索引结构不可用或 ref=func,优化器只能全表;先改写 SQL 或用函数索引。
    20. OR 会导致选错吗? → 可能 index merge 或全表;拆 UNION ALL、补联合索引,别只靠 index merge。
    21. 子查询/派生表会导致选错吗? → 可能物化、临时表、无法下推;改写 JOIN/EXISTS,8.0.22 有 derived_condition_pushdown。
    22. JOIN 顺序选错怎么办? → 补被驱动表 join 列索引、更新统计、JOIN_ORDER/STRAIGHT_JOIN 兜底。
    23. 索引列序/联合索引怎么调? → 等值 → 范围 → 排序;看 key_len 用到第几列、是否 filesort;用 EXPLAIN 对比候选。
    24. 加索引一定能解决吗? → 不一定;如果结果占比高、回表多、统计错,加索引可能没用甚至更慢,还会增加写放大。
    25. FORCE INDEX 能用吗? → 短期止血可以,长期不建议;数据增长/分布变化后会反噬。
    26. USE INDEX 和 FORCE INDEX 区别? → 都是缩小候选;FORCE INDEX 更强,表扫描被视为很贵;都不是绝对保证,且版本/数据敏感。
    27. 8.0 optimizer hint 怎么写? → /*+ INDEX(t idx) */、NO_INDEX、JOIN_ORDER、SET_VAR(...),放在 SELECT 后。
    28. SET_VAR 是什么? → 8.0 hint,可在单条语句内设置会话变量,如 SET_VAR(optimizer_switch='prefer_ordering_index=off')。
    29. optimizer_switch 怎么调? → SET SESSION optimizer_switch='prefer_ordering_index=off';全局改影响所有会话,慎用。
    30. hint 的技术债怎么管? → 记录原因、负责人、失效条件、监控指标;数据/版本变化后复审,能删就删。
    31. 怎么防回归? → 统计持久化、定期 ANALYZE、慢日志/计划监控、上线前生产量级压测、版本升级计划回归。
    32. 统计信息持久化怎么配? → innodb_stats_persistent=ON(5.7+ 默认),配合 innodb_stats_persistent_sample_pages;避免重启后统计丢失。
    33. 主从/升级后计划会变吗? → 会;统计、版本、参数、数据分布不同都可能让计划不同,主从要分别观察。
    34. 怎么对比两个计划? → 用 hint/FORCE INDEX 构造候选,EXPLAIN ANALYZE 对比 actual time/rows;压测看 RT/P99。
    35. 小表全表算选错吗? → 通常不算;全表少量页比二级索引回表更便宜。
    36. 结果占比高全表算选错吗? → 通常不算;优化器选全表可能正确,除非能覆盖索引/分区/改写。
    37. 优化器选错和索引失效的区别? → 索引失效强调“用不上/不该用”;选错强调“有可用索引但成本判断选了更差的计划”,两者有交集但处理不同。
    38. 处理顺序是什么? → 证明选错 → 统计/直方图 → 改写 SQL → 调/补索引 → 会话级 hint/开关 → FORCE INDEX 短期止血 → 压测/监控。
    39. 生产上怎么安全变更? → 先在从库/影子库复现,低峰灰度,准备回滚;DDL 用 Online DDL/gh-ost/pt-osc。
    40. 如何量化收益? → 对比 EXPLAIN ANALYZE 的扫描行数/耗时、慢日志 RT/P99、QPS、写 TPS、BP 命中、索引体积。
  • 生产案例/取舍(示例,替换成你的项目):

    • 订单列表 WHERE user_id=? AND status=? ORDER BY create_time DESC LIMIT 20,优化器选了 (create_time) 索引,扫描几十万行;补 (user_id,status,create_time) 后稳定。
    • 批量删除后统计过期,计划从 range 变 ALL;ANALYZE TABLE 恢复,并开启持久化统计。
    • 城市字段严重倾斜,平均值估算失真;8.0 直方图后计划稳定。
    • WHERE status=1 ORDER BY id LIMIT 20,status=1 占 90%,优化器走 PK 顺序扫描;确认业务可接受,不强行加索引。
    • 手机号 varchar 写成数字,隐式转换导致全表;加引号后恢复,属于 SQL 问题不是优化器错。
    • 大 IN 列表超过 eq_range_index_dive_limit,估算粗、计划抖动;分批查询/临时表后稳定。
    • JOIN 驱动表选错,出现 Using join buffer (hash join);给被驱动表 join 列加索引后改回 eq_ref/ref。
    • 升级 8.0 后计划变化,部分 SQL 变慢;做计划回归、直方图/统计更新、必要时会话级 hint。
    • FORCE INDEX 短期止血,半年后数据增长导致更慢;移除并补联合索引。
    • 分区表 WHERE 没带分区键,partitions 命中全部分区;补分区键/检查分区设计。
    • 取舍:更新统计成本低但可能只是暂时稳定;改写 SQL 从根因解决但要改代码;加索引提升读但写放大;hint 精准但技术债高;FORCE INDEX 止血快但长期风险最大。
  • 易错点:

    • 把 type=ALL 一律当成优化器选错。
    • 只看 EXPLAIN 估算,不看 EXPLAIN ANALYZE 实际。
    • 认为 ANALYZE TABLE 一定能解决。
    • 认为直方图是索引。
    • 认为采样统计是精确值。
    • 认为加索引一定能让优化器选对。
    • 认为 FORCE INDEX 可以长期用。
    • 认为 hint 没有维护成本。
    • 忽略 ORDER BY ... LIMIT 的排序索引偏好。
    • 忽略回表成本对计划的影响。
    • 忽略 eq_range_index_dive_limit 对大 IN 的影响。
    • 忽略 range_optimizer_max_mem_size 导致 range 退化。
    • 忽略字符集/隐式转换导致的 possible_keys=NULL。
    • 忽略版本差异(8.0.21 前后、hash join、直方图)。
    • 认为 optimizer_trace 可以长期全局开启。
    • 认为执行计划一旦稳定就永远稳定。
    • 认为主从/不同实例计划一定一致。
    • 认为小表全表扫描是故障。
    • 认为结果占比高时加索引一定有用。
    • 认为 hint 可以替代统计信息和索引设计。
    • 认为 prefer_ordering_index=off 全局关闭没有副作用。
  • 30 秒口述稿:

优化器选错索引,本质是成本估算和真实执行偏差。先用 EXPLAIN ANALYZE 证明当前计划确实更差,再用 optimizer_trace 看候选路径和成本。常见根因是统计过期/倾斜、回表成本、ORDER BY ... LIMIT、函数/隐式转换、大 IN 或索引列序不对。修复顺序是更新统计/直方图、改写 SQL、调整/补索引,最后才用会话级 hint 或 FORCE INDEX 短期止血;FORCE INDEX 要记录技术债,用压测和监控防回归。

  • 3 分钟展开顺序:结论(成本估算偏差 + 证明选错)→ 四类根因 → 诊断链(慢日志/EXPLAIN/ANALYZE/trace)→ 统计与直方图 → 回表成本与覆盖索引 → ORDER BY ... LIMIT/prefer_ordering_index → 大 IN/range 参数 → SQL/索引修复 → hint/开关边界 → 治理与生产案例。