Appearance
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短期止血”的顺序修复,并用压测和监控防回归。分层要点:
- 先定义“选错”,不要见全表就喊错:
type=ALL、key=NULL不一定是优化器错。小表、结果占比高、覆盖索引全扫描、全表顺序读都可能比走二级索引再大量回表更便宜。真正的“选错”是:存在一个可执行的更优计划,当前计划的EXPLAIN ANALYZE实际耗时/扫描行数明显更差,且差异不能用业务结果集大小解释。 - 根因分四类:统计信息(过期、采样少、数据倾斜、无直方图)、成本模型(回表随机 IO、
ORDER BY ... LIMIT、成本常数、引擎成本)、SQL 结构(列上函数、隐式转换、OR、子查询/派生表、大IN)、索引设计(缺联合索引、列序不对、冗余索引、覆盖不足)。不同根因的修复方式完全不同,先分类再动手。 - 诊断链要完整:慢日志找 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 对比 → 压测确认。 - 统计信息修复:
ANALYZE TABLE重新采样;innodb_stats_persistent=ON持久化统计;innodb_stats_persistent_sample_pages=20控制采样页数;innodb_stats_auto_recalc=ON在约 10% 行变化时自动重算;8.0 可对倾斜列建直方图。统计是采样估算,ANALYZE不是万能,数据分布极端时还要靠直方图或改写。 - 成本模型与
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 的估算变粗或退化。 - SQL/索引修复:去掉列上函数和隐式转换;把
OR拆成UNION ALL或补联合索引;调整联合索引列序(等值 → 范围 → 排序);高频查询做覆盖索引;避免SELECT *;深分页改延迟关联/游标;JOIN 给被驱动表补 join 列索引。加索引不是万能,索引变多会写放大、DDL 变慢、优化器选择空间更大。 - 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等。它们把“优化器的判断”换成“人的判断”,数据增长/分布变化后可能反噬,必须记录技术债、设监控和回滚方案。 - 治理与防回归:统计持久化 + 定期
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.18EXPLAIN ANALYZE、hash join;8.0.21prefer_ordering_index;8.0.22derived_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)。
- MySQL 5.7:
根因 → 信号 → 修复:
| 根因 | 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、扫描行数 | 先定位,不猜 |
EXPLAIN | type/key/key_len/rows/filtered/Extra | 只是估算 |
EXPLAIN ANALYZE | actual time/rows/loops | 8.0.18+;会执行 SQL |
optimizer_trace | 候选路径、成本、why_not | 会话级开启,注意内存 |
SHOW WARNINGS | 改写后的 SQL | 看隐式转换/函数 |
SHOW INDEX | 列序、Cardinality、Visible | Cardinality 是估算 |
mysql.innodb_index_stats | n_diff_pfx 等统计 | 判断采样质量 |
COLUMN_STATISTICS | 直方图 | 8.0 |
sys.schema_unused_indexes | 未使用索引 | 观察窗口要完整 |
sys.schema_redundant_indexes | 冗余索引 | 删除前评估 |
pt-index-usage | 索引使用情况 | Percona Toolkit |
- 统计信息与关键参数:
| 参数/命令 | 默认/用法 | 作用 | 注意 |
|---|---|---|---|
ANALYZE TABLE | 手动执行 | 重新采样统计 | 大表有 IO/锁开销 |
innodb_stats_persistent | ON | 统计持久化 | 避免重启后统计丢失 |
innodb_stats_persistent_sample_pages | 20 | 持久化统计采样页 | 越大越准、ANALYZE 越慢 |
innodb_stats_transient_sample_pages | 8 | 非持久化统计采样 | 仅相关场景 |
innodb_stats_auto_recalc | ON | 约 10% 行变化自动重算 | 大事务后仍建议手动 |
ANALYZE TABLE ... UPDATE HISTOGRAM | 8.0 | 列直方图 | 改善倾斜列估算,不是索引 |
eq_range_index_dive_limit | 200(8.0) | 大 IN 改用统计 | 过大估算粗 |
range_optimizer_max_mem_size | 8MB | range 优化内存上限 | 超限退化全表 |
optimizer_trace_max_mem_size | 1MB | trace 内存上限 | 太小会截断 |
- hint / 开关:
| 手段 | 语法/示例 | 适用 | 风险 |
|---|---|---|---|
USE INDEX | USE INDEX (idx) | 缩小候选 | 可能选不到最优 |
FORCE INDEX | FORCE INDEX (idx) | 短期强制倾向 | 数据变化后反噬 |
IGNORE INDEX | IGNORE INDEX (idx) | 排除坏索引 | 可能全表 |
8.0 INDEX hint | /*+ INDEX(t idx) */ | 语句级 | 版本/别名敏感 |
NO_INDEX hint | /*+ NO_INDEX(t idx) */ | 排除索引 | 同上 |
JOIN_ORDER hint | /*+ JOIN_ORDER(a,b) */ | 固定连接顺序 | 数据变化后失效 |
STRAIGHT_JOIN | SELECT STRAIGHT_JOIN ... | 强制左表驱动 | 可能更慢 |
SET_VAR hint | /*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */ | 单语句调参 | 需权限/版本 |
optimizer_switch | SET SESSION optimizer_switch='...' | 会话级调开关 | 全局改影响所有会话 |
- 修复优先级与取舍:
| 优先级 | 动作 | 收益 | 代价 |
|---|---|---|---|
| 1 | 更新统计/直方图 | 低成本、快速 | 可能只是暂时稳定 |
| 2 | 改写 SQL | 从根因消除 | 业务代码改动 |
| 3 | 调整/补充索引 | 提升读性能 | 写放大、DDL 成本 |
| 4 | 会话级 hint/开关 | 精准、可控 | 技术债、版本敏感 |
| 5 | FORCE INDEX | 立即止血 | 长期风险最高 |
深挖点(面试官常追的 6 个):
- “选错”要先证明,不要见全表就下结论:优化器可能算得对——小表、结果占比高、全表顺序读确实更便宜。用
EXPLAIN ANALYZE对比 actual rows/time,再用 hint 构造候选计划做 A/B,才能证明“有更优计划但没选”。 - 统计信息是采样,不是精确值:
rows、filtered、Cardinality 都来自采样;数据倾斜、大事务、批量删除后统计会失真。ANALYZE TABLE能缓解,但采样页数、直方图、数据分布决定上限;极端倾斜要靠直方图或改写。 ORDER BY ... LIMIT是选错高发区:优化器为了消除 filesort,可能选排序列索引,扫描大量不满足 WHERE 的行;LIMIT越小,这种“排序索引”看起来越便宜。8.0.21 的prefer_ordering_index可以关掉偏好,但全局关闭可能伤害其他查询,优先用会话级/单语句SET_VAR。- 回表成本决定二级索引的生死:二级索引命中行多时,每行回表都是随机 IO;优化器估算的回表成本不准,就会选错。覆盖索引把回表变成索引内读取,直接改变成本结构;但索引变宽会写放大,要权衡。
- hint 是“人的判断”替代“优化器判断”:
FORCE INDEX/USE INDEX/8.0 hint 能短期稳定计划,但数据增长、分布变化、版本升级后可能更慢。必须记录原因、负责人、失效条件,设监控和回滚,不能当长期方案。 - 版本和参数会改变计划:8.0 直方图、不可见索引、
prefer_ordering_index、hash join、derived_condition_pushdown都会影响选择;eq_range_index_dive_limit、range_optimizer_max_mem_size会改变估算和退化行为。升级前后要做计划回归,不能假设“SQL 没变,计划就没变”。
- “选错”要先证明,不要见全表就下结论:优化器可能算得对——小表、结果占比高、全表顺序读确实更便宜。用
追问链:
- 什么叫优化器选错索引? → 存在可执行的更优计划,当前计划实际耗时/扫描行数明显更差,且不是结果集本身大导致的。
- 怎么证明选错? →
EXPLAIN ANALYZE看 actual rows/loops/time,用 hint/FORCE INDEX构造候选计划做 A/B,压测对比。 EXPLAIN和EXPLAIN ANALYZE怎么配合? → 前者看估算计划,后者看实际执行;估算和实际差距大,通常是统计或成本模型问题。optimizer_trace看什么? →considered_access_paths的候选索引、rows、cost、chosen、why_not,判断是成本高还是结构不满足。- 选错的常见根因有哪些? → 统计过期/采样/倾斜、回表成本估算、
ORDER BY ... LIMIT、函数/隐式转换、大IN/range 内存、JOIN 顺序、索引列序/缺失。 - 统计信息为什么会错? → 采样估算、数据倾斜、批量增删、大事务、自动重算阈值未触发、持久化统计未更新。
ANALYZE TABLE够吗? → 不一定;采样页数少、倾斜严重时仍不准,需要直方图、调采样页数或改写 SQL。- 直方图怎么用? → 8.0
ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH N BUCKETS;改善倾斜列的基数/分布估算,不创建索引。 - 数据倾斜怎么办? → 直方图、改写查询、分桶/缓存热点、调整联合索引;极端热点不要只靠索引。
- 采样页数怎么调? →
innodb_stats_persistent_sample_pages(默认 20)增大更准但 ANALYZE 更慢;先确认统计是否持久化。 - 回表成本怎么估算? → 二级索引命中行数 × 回表随机 IO 成本;优化器用成本常数和统计估算,覆盖索引可消除回表。
- 覆盖索引为什么能改变计划? → 它把“索引扫描 + 回表”变成“只读索引”,成本结构变化,优化器更愿意选它。
- 为什么
ORDER BY ... LIMIT容易选错? → 优化器为消除 filesort 选排序索引,可能扫描大量不满足 WHERE 的行;LIMIT 小会让排序索引看起来更便宜。 prefer_ordering_index是什么? → 8.0.21 引入的优化器开关,控制是否优先为ORDER BY ... LIMIT选排序索引;可会话级关闭。- 8.0.21 之前怎么办? →
IGNORE INDEX/FORCE INDEX、改写 SQL、延迟关联/游标分页、补联合索引。 - 大
IN列表为什么估算粗? → 超过eq_range_index_dive_limit后改用索引统计而不是逐值 dive,估算变粗;分批/临时表更稳。 eq_range_index_dive_limit是什么? → 控制IN/等值范围做 index dive 的个数上限;8.0 默认 200,5.7 默认 10。range_optimizer_max_mem_size是什么? → range 优化可用的内存上限,超限可能放弃 range 退化成全表;默认 8MB。- 函数/隐式转换会导致选错吗? → 会让索引结构不可用或
ref=func,优化器只能全表;先改写 SQL 或用函数索引。 OR会导致选错吗? → 可能 index merge 或全表;拆UNION ALL、补联合索引,别只靠 index merge。- 子查询/派生表会导致选错吗? → 可能物化、临时表、无法下推;改写 JOIN/EXISTS,8.0.22 有
derived_condition_pushdown。 - JOIN 顺序选错怎么办? → 补被驱动表 join 列索引、更新统计、
JOIN_ORDER/STRAIGHT_JOIN兜底。 - 索引列序/联合索引怎么调? → 等值 → 范围 → 排序;看
key_len用到第几列、是否 filesort;用EXPLAIN对比候选。 - 加索引一定能解决吗? → 不一定;如果结果占比高、回表多、统计错,加索引可能没用甚至更慢,还会增加写放大。
FORCE INDEX能用吗? → 短期止血可以,长期不建议;数据增长/分布变化后会反噬。USE INDEX和FORCE INDEX区别? → 都是缩小候选;FORCE INDEX更强,表扫描被视为很贵;都不是绝对保证,且版本/数据敏感。- 8.0 optimizer hint 怎么写? →
/*+ INDEX(t idx) */、NO_INDEX、JOIN_ORDER、SET_VAR(...),放在 SELECT 后。 SET_VAR是什么? → 8.0 hint,可在单条语句内设置会话变量,如SET_VAR(optimizer_switch='prefer_ordering_index=off')。optimizer_switch怎么调? →SET SESSION optimizer_switch='prefer_ordering_index=off';全局改影响所有会话,慎用。- hint 的技术债怎么管? → 记录原因、负责人、失效条件、监控指标;数据/版本变化后复审,能删就删。
- 怎么防回归? → 统计持久化、定期 ANALYZE、慢日志/计划监控、上线前生产量级压测、版本升级计划回归。
- 统计信息持久化怎么配? →
innodb_stats_persistent=ON(5.7+ 默认),配合innodb_stats_persistent_sample_pages;避免重启后统计丢失。 - 主从/升级后计划会变吗? → 会;统计、版本、参数、数据分布不同都可能让计划不同,主从要分别观察。
- 怎么对比两个计划? → 用 hint/
FORCE INDEX构造候选,EXPLAIN ANALYZE对比 actual time/rows;压测看 RT/P99。 - 小表全表算选错吗? → 通常不算;全表少量页比二级索引回表更便宜。
- 结果占比高全表算选错吗? → 通常不算;优化器选全表可能正确,除非能覆盖索引/分区/改写。
- 优化器选错和索引失效的区别? → 索引失效强调“用不上/不该用”;选错强调“有可用索引但成本判断选了更差的计划”,两者有交集但处理不同。
- 处理顺序是什么? → 证明选错 → 统计/直方图 → 改写 SQL → 调/补索引 → 会话级 hint/开关 →
FORCE INDEX短期止血 → 压测/监控。 - 生产上怎么安全变更? → 先在从库/影子库复现,低峰灰度,准备回滚;DDL 用 Online DDL/gh-ost/pt-osc。
- 如何量化收益? → 对比
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/开关边界 → 治理与生产案例。