在前文中，我们详细解读了 Extra 列中的 8 个关键性能信号——Using temporary、Using filesort、Using index condition……把这些“黑话”都逐一拆解过。但还有一个更深层的难题悬而未决：
如果执行计划看起来不合理，如何知晓优化器“为什么”会做出这样的选择？
EXPLAIN 虽能展示最终选择的执行计划，却无法解释优化器做出该决策的原因。你看到 type=ALL，知道是全表扫描，但不知道为什么——明明存在索引，优化器为何不走？
此时，我们就需要借助 Optimizer Trace 来深入分析。
在 MySQL 5.6 之前，优化器就像一个黑盒子——你只能看到最终结果，却看不到决策过程。Optimizer Trace 正是打开这个黑盒子的钥匙。它可以完整追踪优化器做出的每一步决策——访问方式的选择、成本估算、各种转换规则的应用，全部被记录下来。从 MySQL 5.6 开始，设计 MySQL 的大叔贴心地提供了这一功能，让我们可以方便地查看优化器生成执行计划的完整过程。
一、Optimizer Trace 是什么？——优化器的“行车记录仪”
打个比方，EXPLAIN 是一张“行车路线图”——告诉你最终走了哪条路。而 Optimizer Trace 是“行车记录仪”——它录下了司机在路上做的每一个决策：为什么没走高速、为什么在这个路口右转、为什么绕了一段路。
Optimizer Trace 记录的正是优化器从接收 SQL 到最终选定执行计划的完整过程，包括：
- 生成了哪些候选执行计划
- 每个计划的成本估算
- 应用了哪些优化规则
- 最终选择了哪个计划，以及为什么
有了这些信息，你不仅能知道优化器“选了什么”，还能知道它“为什么这么选”。
二、怎么用 Optimizer Trace？——四步搞定
Optimizer Trace 默认是关闭的，因为开启会产生额外开销。好在它是轻量级工具，支持会话级别开启，不影响其他会话。
步骤一：开启 Optimizer Trace
SET SESSION optimizer_trace = "enabled=on";
SET end_markers_in_json = ON;end_markers_in_json=ON 会在 JSON 输出的每个阶段添加结束标记，方便阅读。
步骤二：执行需要分析的 SQL
SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2026-01-01';步骤三：查询跟踪结果
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\GOPTIMIZER_TRACE 表共有 4 个字段：
| 字段 | 含义 |
|---|---|
QUERY |
执行的 SQL 语句 |
TRACE |
优化过程的 JSON 格式文本（核心内容） |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE |
被截断的字节数 |
INSUFFICIENT_PRIVILEGES |
是否权限不足 |
步骤四：关闭 Optimizer Trace
SET SESSION optimizer_trace = "enabled=off";分析完记得关闭，避免产生不必要的性能开销。
三、Optimizer Trace 输出结构拆解——三个核心阶段
Optimizer Trace 的输出是一个 JSON 结构，主要包含三个阶段：
1. join_preparation（准备阶段）
这一阶段进行语法解析和预处理：将 * 扩展为具体列、外连接转内连接、视图合并、子查询转换等。
2. join_optimization（优化阶段）——核心
这是优化器的核心工作区，也是最值得关注的部分。包含以下关键子步骤：
- condition_processing：WHERE 和 JOIN 条件的优化处理
- table_dependencies：分析表之间的依赖关系
- ref_optimizer_key_uses：评估 ref 类型索引的使用可能性
- rows_estimation：行数估算和成本分析
在这个阶段，你可以看到优化器为每个可能的索引计算成本，对比不同访问路径的代价，最终做出选择。
3. join_execution（执行阶段）
记录执行器实际执行的信息，如排序操作、临时表使用等。
四、真实案例：用 Optimizer Trace 定位执行计划跑偏的根因
场景：一张订单表有 500 万行，customer_id 列有索引，order_date 列也有索引。执行以下查询：
SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2026-01-01';正常情况下，这个查询应该走 (customer_id, order_date) 复合索引。但实际执行计划显示 type=ALL，全表扫描。
从 EXPLAIN 看，只知道“走了全表扫描”，但不知道为什么——明明有索引，优化器却不走。
用 Optimizer Trace 查看详情：
SET SESSION optimizer_trace = "enabled=on";
SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2026-01-01';
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G在 rows_estimation 部分，可以看到优化器评估了每个索引的成本：
"rows_estimation": {
"table": "orders",
"range_analysis": {
"table_scan": {
"rows": 5000000,
"cost": 1000000
},
"potential_range_indices": [
{
"index": "idx_customer_id",
"usable": true,
"chosen": false,
"cost": 450000
},
{
"index": "idx_order_date",
"usable": true,
"chosen": false,
"cost": 420000
}
],
"chosen_range_access_summary": {
"range_scan_alternatives": [],
"chosen": "TABLE_SCAN",
"chosen_cost": 1000000
}
}
}关键发现：
- 全表扫描成本：约 100 万
idx_customer_id成本：约 45 万（但优化器认为回表成本过高）idx_order_date成本：约 42 万
优化器最终选择了全表扫描，因为两个单列索引的回表代价都太高了——customer_id = 12345 可能返回很多行，order_date > '2026-01-01' 也可能返回很多行。
解决方案：创建复合索引 (customer_id, order_date)，让索引同时覆盖两个条件，减少回表。
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);再跑一次 EXPLAIN，type=ref，key=idx_customer_date，查询从 5 秒降到 0.05 秒。
五、Optimizer Trace 的使用原则
1. 不要在生产环境长期开启
Optimizer Trace 会产生额外的开销，虽然影响很小，但仍不建议在生产环境长期开启。只在需要诊断疑难查询时临时开启，分析完立即关闭。
2. 结合 EXPLAIN 一起用
EXPLAIN 提供“结论”，Optimizer Trace 提供“过程”。先用 EXPLAIN 发现异常，再用 Optimizer Trace 追溯根因。
3. 关注成本估算部分
rows_estimation 是最有价值的部分——它直接展示了优化器是如何计算每个候选计划的代价的。如果优化器选错了计划，这里会给出线索。
六、总结
EXPLAIN 告诉你优化器“选了哪个计划”，Optimizer Trace 告诉你优化器“为什么选它”。
当你遇到一个看起来不合理的执行计划时，不要直接给优化器贴“笨”的标签。打开 Optimizer Trace，看看它到底是怎么算的——可能是统计信息过旧、可能是索引设计不够好、可能是某个参数影响了决策。
Optimizer Trace 把优化器的“思考过程”完整地呈现了出来。读懂它，你就能从“执行计划的阅读者”升级为“优化器的理解者”。读懂 Optimizer Trace 输出的 JSON，是 DBA 从“会用 EXPLAIN”走向“理解优化器”的必经之路。
