大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
前面我们讲了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个字段：
| 字段 | 含义 |
|---|---|
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”走向“理解优化器”的必经之路。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
