optimizer trace 是什么
optimizer_trace 记录优化器为一条语句做决策的完整过程：考虑过哪些候选索引、每种方案的估算行数与代价、最终为什么选了这一个、其他方案被否决的原因。当 EXPLAIN 只告诉你「用了哪个索引」而你更想知道「为什么不用另一个」时，它就是唯一的观察窗口。开启后只影响当前会话的语句，用完必须关闭。
基本信息
| 适用版本 | MySQL 5.6+ / 5.7 / 8.0 |
|---|---|
| 开启方式 | SET optimizer_trace = 'enabled=on'（会话级） |
| 输出位置 | information_schema.OPTIMIZER_TRACE 的 TRACE 字段 |
| 常用开关 | enabled、one_line、max_mem_size |
| 关键段落 | rows_estimation、considered_execution_plans、best_plan_so_far、rejected |
| 开销 | 只针对当前会话语句，用后关闭避免累积 |
典型使用场景
- 索引明明存在却不被选中：查看被否决的具体原因与代价差
- 执行计划在数据变化后翻转：对比两次 trace 的估算差异
- 联合索引列序设计验证：看优化器如何比较不同前缀的代价
- 排查统计信息问题：rows_estimation 段直接暴露估算与实际分布偏离
- 升级后计划变化：用 trace 定位是否是新版本优化器行为改变
启用与分析
启用与分析
-- 打开追踪，只影响当前会话
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1048576; # 输出较大时适当放宽
-- 执行待分析语句
SELECT * FROM appdb.orders WHERE user_id = 1 AND status = 2 LIMIT 10;
-- 读取决策过程
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
-- 用完立即关闭，避免长连接持续累积
SET optimizer_trace = 'enabled=off';抓取关键段落
抓取关键段落
# 把 trace 输出落文件后用工具筛选关键段落
mysql -uroot -p -e "SELECT TRACE FROM information_schema.OPTIMIZER_TRACE" > trace.json
# 看有哪些候选方案与各自代价
grep -o '"considered_execution_plans".*' trace.json | head -c 2000
# 看最终选择与被否决的方案
grep -o '"best_plan_so_far".*' trace.json | head -c 500
python3 -c "import sys,json; d=open('trace.json').read(); print(len(d))" # 体积参考TRACE 关键段落
| 段落 | 内容 | 怎么用 |
|---|---|---|
| rows_estimation | 各候选索引的估算行数 | 估算与真实分布差距大说明统计不准 |
| considered_execution_plans | 被考虑的执行方案与代价 | 对比不同方案的成本差异 |
| best_plan_so_far | 当前最优方案 | 看优化器中途的取舍过程 |
| rejected | 被否决的方案与原因 | 定位「为什么不用这个索引」的直接证据 |
| chosen | 最终选定方案 | 与 EXPLAIN 输出相互印证 |
trace 输出动辄几百 KB，用 \G 或导出到文件后再用 grep / jq 筛段落比逐屏翻看高效得多；把 max_mem_size 调大可以避免超限导致 trace 被截断。
忘记关闭 trace 会让长连接持续收集信息，内存与开销逐条累积；调试完成应立即关闭，并把这一步写进排查脚本的收尾动作。