11.4 OPTIMIZER_TRACE 追踪优化器决策过程

EXPLAIN 能告诉你优化器最终选择了什么执行计划，但它不告诉你为什么选这个而不选那个。当你发现优化器“选错了索引”或者“莫名其妙走了全表扫描”时，就需要 OPTIMIZER_TRACE 出马了。它像是一个记录优化器思考过程的黑匣子，把每一步的判断依据、成本计算、候选方案全都摊在你面前。

11.4.1 什么是 OPTIMIZER_TRACE

OPTIMIZER_TRACE 是 MySQL 提供的一个诊断功能，能以 JSON 格式输出优化器从解析 SQL 到生成最终执行计划的完整决策链路。这些信息包括但不限于：

表关联顺序的选择与成本估算
每个可选索引的扫描范围、行数、成本
为什么某个索引被选中，另一个被放弃
子查询改写、条件推导等优化动作
内存或排序等操作的成本评估

它对数据库的侵入性很低，只在开启了跟踪的会话内生效，而且仅影响该会话的查询，不用全局开启。生产环境调试慢 SQL 时，也可以临时对某个连接开启，用完即关。

11.4.2 如何开启和使用

使用 OPTIMIZER_TRACE 需要三步：开启跟踪、执行目标查询、查看跟踪结果。

开启跟踪

-- 1. 打开当前会话的优化器跟踪
SET optimizer_trace = 'enabled=on';

-- 2. 可选：设置跟踪内存上限（默认 1MB），防止超大 SQL 的跟踪信息爆内存
SET optimizer_trace_max_mem_size = 1000000;

执行目标查询

-- 就执行你想要分析的 SQL，比如：
SELECT * FROM orders WHERE user_id = 1024 AND status = 'paid' ORDER BY create_time DESC;

获取跟踪结果

优化器跟踪的输出放在 information_schema.OPTIMIZER_TRACE 表里，每次开启跟踪后执行的第一个查询会被记录下来。

SELECT * FROM information_schema.OPTIMIZER_TRACE \G

结果是一个 JSON 文本，可以用 \G 垂直显示方便阅读，也可以复制出来用 JSON 工具格式化。

关闭跟踪

SET optimizer_trace = 'enabled=off';

如果不主动关闭，该值只在当前会话有效，断开连接后自动恢复默认。线上使用建议执行完立刻关闭，避免产生不必要的性能开销。

11.4.3 跟踪结果解读：JSON 结构拆解

一个完整的 OPTIMIZER_TRACE 输出通常包含以下几个关键阶段（对应 JSON 里的 steps 数组）：

join_preparation：查询的初始准备阶段，包括字段展开、通配符展开等。
join_optimization：核心阶段，表的关联优化、索引选择、条件过滤、子查询改写等都发生在这里。这也是我们最需要关注的部分。
join_execution：描述最终执行时的某些运行时决策，例如是否用临时表。

join_optimization 内部又细分为：

condition_processing：WHERE 条件的提取与简化。
table_dependencies：表之间的依赖关系。
ref_optimizer_key_uses：列出可用于 ref/eq_ref 访问的索引及其使用方式。
rows_estimation：每张表用不同访问方式（全表扫描、各个索引）的预估行数和成本。
considered_execution_plans：优化器考虑过的所有执行计划及其代价，可以看到被淘汰的方案及其被淘汰的原因。

解读时，重点找 “chosen” 和 “cause” 关键字。

11.4.4 实用案例：为什么优化器没用上我的索引

假设你有一个订单表，建了联合索引 idx_user_status_time (user_id, status, create_time)，你期待 WHERE user_id = 1024 AND status = 'paid' ORDER BY create_time DESC 能走这个索引直接排序。但 EXPLAIN 显示 Using filesort，优化器居然没用上你精心设计的索引。

这时可以通过 OPTIMIZER_TRACE 看到真相。

步骤：

SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE user_id = 1024 AND status = 'paid' ORDER BY create_time DESC;
SELECT * FROM information_schema.OPTIMIZER_TRACE \G
SET optimizer_trace = 'enabled=off';

在输出的 rows_estimation 部分，你可能会看到类似：

{
  "index": "idx_user_status_time",
  "ranges": ["(1024, 'paid') <= (user_id, status) <= (1024, 'paid')"],
  "rows": 5000,
  "cost": 5200,
  "chosen": false,
  "cause": "cost"
}

说明优化器计算了这个索引的扫描成本（约 5200 个成本单位），但因为发现使用这个索引需要回表 5000 次、或者数据文件太大导致 I/O 成本偏高，最终没有选择它。而全表扫描 + filesort 的成本可能是 4000，于是它选了后者。

或者你可能会看到：

"chosen": false,
"cause": "not_applicable",
"reason": "The order of the index cannot satisfy the ORDER BY clause"

这是因为联合索引 (user_id, status, create_time) 对于 ORDER BY create_time 来说，只有在 user_id 和 status 都是等值查询时才能利用索引排序。如果 status 是范围查询（例如 status > 'paid'），那就破坏了索引的排序顺序，导致排序不可用。跟踪信息也会明确告诉你这个原因。

11.4.5 常用诊断场景

索引选择异常：明明有更好的索引，优化器却走了全表扫描或选择了低效索引。通过 rows_estimation 对比各索引的成本和行数，确认是否因为统计信息不准或者成本算法偏差导致。

关联顺序与算法问题：多表 JOIN 时，优化器会考虑不同的表顺序和连接算法（Nested Loop、Hash Join）。跟踪信息里的 considered_execution_plans 会列出每种组合的代价，帮助你理解为何驱动表被选为 A 而不是 B。

子查询改写失效：开发时可能会把子查询写成某种形式，期望优化器自动改写为 EXISTS 或反半连接，但实际却没有发生。跟踪信息中可以精确看到优化器做了哪些查询改写动作，以及为什么没有进一步优化。

filesort 与临时表使用诊断：当 Extra 出现 Using filesort 或 Using temporary，但你认为可以利用索引消除时，跟踪结果会显式地告诉你优化器是否考虑过用索引来避免排序、为什么条件不满足。

11.4.6 注意事项与避坑

生产环境慎用：虽然只影响开启会话，但跟踪本身会额外消耗 CPU 和内存，对于超高 QPS 的会话不建议长时间开启。用完记得关掉。
结果可能很长：一个复杂查询的跟踪 JSON 可能有几万行，直接阅读很费眼。可以先搜索 "chosen": true 或 "cause" 定位关键决策点，再反向查看原因。
版本差异：不同 MySQL 版本的跟踪字段和细节有差异。MySQL 8.0 比 5.7 多了更多决策细节，比如 Hash Join 的成本考量。检查 MySQL 版本，避免被旧版文档误导。
并非万能：优化器在某些情况下会放弃穷举搜索或过早 cutoff（比如表关联数超过 optimizer_search_depth 限制）。跟踪信息里通常会有 "pruned_by_heuristic" 之类的标记，此时意味着优化器并没有评估所有可能的计划，你可以通过调整 optimizer_search_depth 来观察变化。
结合统计信息：很多索引选择错误都源于 InnoDB 的统计信息不准确。此时应先执行 ANALYZE TABLE 更新统计信息，再重新 trace，看看决策是否改变。

总之，OPTIMIZER_TRACE 是调试慢查询的一块“显微镜”，让你从执行计划的表象深入到优化器的内部逻辑。当 EXPLAIN 看不明白的时候，打开它，你会有豁然开朗的感觉。