读懂优化器Trace：看透执行计划的每一步隐藏决策

在前文中，我们详细解读了 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\G

OPTIMIZER_TRACE 表共有 4 个字段：

步骤四：关闭 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”走向“理解优化器”的必经之路。