SQL optimizer_trace 的 MySQL 执行计划调试详细输出解读

需先执行set optimizer_trace=”enabled=on,one_shot=1″，再运行目标sql，然后查询information_schema.optimizer_trace获取json格式的完整执行计划，最后设为off关闭。

怎么打开 optimizer_trace 并拿到完整执行计划

mysql 的 optimizer_trace 不是默认开启的，得手动开关，而且只对当前会话生效。开完还得查系统表才能看到结果，不是直接输出在命令行里。

• 先执行 SET optimizer_trace="enabled=on"，建议加上 one_shot=1 避免漏关（比如设成 "enabled=on,one_shot=1"）
• 紧接着运行你要分析的 SQL（必须是单条，不能是存储过程或批量语句）
• 再查 select * FROM information_schema.optimizer_trace，结果在 trace 字段里，是 JSON 格式
• 别忘了关掉：再执行一次 SET optimizer_trace="enabled=off"，否则后续查询也记录，影响性能且容易污染结果

常见错误：开了没查 information_schema.optimizer_trace，以为没生效；或者查了但没展开 trace 字段内容，只看 message 或 rows_examined_per_scan 这些摘要字段，漏掉关键决策路径。

trace JSON 里哪些字段真正影响执行计划判断

trace 字段里嵌套深、字段多，但真正决定“为什么走这个索引”“为什么没用上覆盖索引”的，就集中在几个节点里。别从头读，先定位到 steps → join_optimization → considered_execution_plans 这一层。

• condition_processing：看 WHERE 条件是否被重写，比如 id+1=5 被转成 id=4，这直接影响索引可用性
• ref_optimizer_key_uses：明确列出用了哪个索引、哪几列、是否为 NULL 安全比较——这里能解释“明明有索引却没走”的原因（比如类型隐式转换导致失效）
• rows_for_plan 和 cost_for_plan：优化器估算的行数和代价，如果多个 plan 的 cost_for_plan 差距极小（比如 100.2 vs 100.5），那实际选哪个可能受统计信息偏差或随机因素影响
• 注意 analyzing_range_alternatives 下的 analyzing_roworder_intersect：说明在考虑索引合并（index merge），但 MySQL 8.0 默认关了 range_optimizer_max_mem_size 限制，太大的候选集会被跳过

容易踩的坑：把 rows_estimated 当成真实扫描行数，其实它只是基于统计信息的估算；还有人盯着 best_covering_index 看，却忽略前面 usable_indexes 为空——说明根本没索引可用，覆盖索引自然无从谈起。

optimizer_trace 和 EXPLAIN 的结果不一致怎么办

EXPLAIN 显示的是优化器最终选定的执行计划，而 optimizer_trace 记录的是整个决策过程，包括被否决的备选方案。两者不一致不是 bug，而是视角不同。

• 如果 EXPLAIN 显示走了 idx_a_b，但 trace 里 considered_execution_plans 中 idx_a_b 的 cost_for_plan 比 idx_b 高，说明优化器权衡后仍选了前者——可能因为 idx_a_b 能避免回表，而 idx_b 虽然 cost 低但要读大量主键再回表，总 IO 更高
• 如果 EXPLAIN 显示 type=ALL（全表扫描），但 trace 里 ref_optimizer_key_uses 为空，说明优化器压根没考虑任何索引——大概率是 WHERE 条件没命中索引最左前缀，或存在函数/表达式包裹列（如 WHERE YEAR(create_time)=2023）
• MySQL 8.0.22+ 加了 skip_scan 优化，trace 里会出现 skip_scan_candidates，但 EXPLAIN 只显示 type=range，不会注明是 skip scan，容易误判

关键点：不要只比对“走不走索引”，要看 trace 里优化器「为什么放弃更好选项」——往往是统计信息不准、索引选择性差、或 optimizer_search_depth 太小导致没搜到最优解。

生产环境要不要长期开 optimizer_trace

不要。它带来可观的内存和 CPU 开销，尤其复杂 JOIN 查询，trace JSON 可能达数 MB，生成和序列化过程本身就会拖慢查询。

• 只在定位具体慢查询时临时开启，且务必加 one_shot=1
• 避免在高并发接口中使用，更不要写进通用 DAO 层作为日志开关
• MySQL 5.7 默认关闭 optimizer_trace_max_mem_size（上限 1MB），超限会截断 trace，导致看不到完整决策链；8.0 默认 16MB，但也建议按需调小，防止 OOM
• 如果需要持续观察执行计划变化，优先用 Performance Schema 的 events_statements_history_long + explain_for_connection，开销低得多

最常被忽略的一点：optimizer_trace 不记录锁信息、不反映 MVCC 版本可见性判断、也不体现 buffer pool 命中率——它只管“选哪个 plan”，不管“这个 plan 执行起来卡不卡”。真卡了，得配合 SHOW PROFILE 或 perf 分析。