MySQL optimizer_trace 观察成本估算分支

排查 MySQL 查询为什么选择了某条访问路径时，EXPLAIN 能告诉你结果计划，却不一定能说明优化器比较过哪些候选、估算了多少行、给每条路径算出了什么成本。optimizer_trace 可以把这段优化过程写入 JSON，适合回答“为什么这个分支被选中”“为什么范围访问没有被采用”这类问题。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/optimizer-tracing.html

本文只聚焦成本估算分支：在同一会话中打开跟踪，执行一条目标 SQL，再从 INFORMATION_SCHEMA.OPTIMIZER_TRACE 里顺着 rows_estimation、range_analysis 和 considered_execution_plans 阅读。示例中的数值是为了说明字段关系，不代表你的业务库会得到相同结果。

为什么 EXPLAIN 还不够看懂成本选择

EXPLAIN 更像最终计划的摘要：你可以看到访问类型、候选索引以及估算行数，但当多个路径都可用时，摘要通常不会把“候选 A 为什么输给候选 B”的过程完整展开。优化器跟踪则保留了更多中间决策，例如表扫描和索引访问的估算行数、成本、是否被选中，以及计划累计成本。

因此，optimizer_trace 适合短时间定位一个具体问题，而不是长期打开当作普通监控。官方文档给出的基本步骤也是开启跟踪、执行待分析语句、读取 OPTIMIZER_TRACE，完成后关闭跟踪；跟踪只对当前会话可见。

先在同一会话打开并读取跟踪

最容易犯的错误是：在连接 A 打开跟踪，却在连接 B 执行查询或读取结果。优化器跟踪按会话保存，换连接后看不到刚才的 trace。下面的 SQL 要在同一个 MySQL 客户端连接中依次执行。

-- 只在当前会话打开优化器跟踪，并让 JSON 更容易人工阅读
SET optimizer_trace = 'enabled=on,one_line=off';

-- 执行需要分析的查询；这里的表名和条件请替换为你的目标 SQL
SELECT o.order_id, o.customer_id
FROM orders AS o
WHERE o.customer_id = 10086
ORDER BY o.order_id DESC
LIMIT 20;

-- 读取当前会话最近保留的 trace，\G 只负责纵向展示结果
SELECT QUERY, TRACE, MISSING_BYTES_BEYOND_MAX_MEM_SIZE,
       INSUFFICIENT_PRIVILEGES
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

-- 分析完成后关闭跟踪，避免继续记录后续语句
SET optimizer_trace = 'enabled=off';

one_line=off 只影响 JSON 的空白格式，便于人工阅读；如果结果要交给 JSON 解析器，空白并不影响解析。重点是执行 SQL 和读取表必须保持在同一连接中。读取结果时优先看 QUERY，先确认这条 trace 对应的确实是目标语句。

从 OPTIMIZER_TRACE 找到成本估算分支

TRACE 是 JSON 文本，字段会随着语句类型、表数量和优化过程变化，不建议只按固定层级下标读取。人工排查时可以先搜索这些语义节点：

• rows_estimation 或 records_estimation：查看扫描、范围分析等行数与成本估算。
• range_analysis：查看表扫描、潜在范围索引和范围访问相关判断。
• considered_execution_plans：查看优化器比较过的执行计划。
• best_access_path 与 considered_access_paths：查看某张表的候选访问路径。

官方示例在记录估算中展示了 table_scan、records 和 cost，在候选计划中展示了 access_type、index、records、cost 和 chosen。这就是标题所说的“成本估算分支”：不是只看最终索引名，而是沿着估算节点看每个候选如何进入比较。

-- 只抽取原始 JSON 文本；不要假设每种 SQL 都有相同字段
SELECT TRACE
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE QUERY LIKE 'SELECT o.order_id%';

用 cost、records 和 chosen 对上决策

阅读一段候选访问路径时，可以按“候选是什么—预计读多少—代价是多少—是否采用”的顺序看：

例如，同一个 best_access_path 里可能同时出现 scan 和 ref。先比较它们的估算行数和成本，再看 chosen 是否为 true。如果某条路径的成本更高但仍被选中，不要立刻断言跟踪错误，还要继续确认它是否属于不同的计划前缀、连接顺序或后续代价累计。

同时要把“估算原因”和“运行结果”分开：optimizer_trace 解释的是优化器在生成计划时的判断；它不是执行耗时剖析，也不能替代真实运行指标。

trace 太大时先控制分支和内存

复杂连接、范围分析或重复执行的子查询会让 trace 很快变大。MySQL 8.4 提供了几组会话级控制项：

-- 提高本次分析允许保存的 trace 总内存；数值按语句复杂度调整
SET optimizer_trace_max_mem_size = 1000000;

-- 只查看保留结果中的一部分；limit 和 offset 用于控制展示窗口
SET optimizer_trace_limit = 1;
SET optimizer_trace_offset = 0;

-- 对容易重复产生噪声的优化器分支关闭跟踪
SET optimizer_trace_features =
    'greedy_search=off,range_optimizer=on,dynamic_range=on,repeated_subselect=off';

optimizer_trace_features 的四个开关分别影响贪心搜索、范围优化器、动态范围优化和重复子查询跟踪。关闭某个开关的含义是“不记录该类分支”，不是关闭查询优化本身。调试成本估算时，不要为了让 JSON 变短而随意关闭你正在研究的分支；先关闭与当前问题无关的重复噪声。

看到截断或空 trace 时怎么判断

先检查 MISSING_BYTES_BEYOND_MAX_MEM_SIZE。如果它大于 0，说明 trace 达到 optimizer_trace_max_mem_size 后没有继续扩展，当前结果是不完整的，应提高会话级内存上限后重新执行目标语句，而不是把缺失部分当成“优化器没有走这条分支”。

再看 INSUFFICIENT_PRIVILEGES。官方文档指出，涉及以 SQL SECURITY DEFINER 定义的视图或存储程序时，当前用户可能无权看到完整 trace；这时 trace 可能为空，并且该列值为 1。它是权限边界，不等同于目标 SQL 没有优化过程。

还要确认语句属于可跟踪范围，并且没有跨连接读取。最稳妥的排查顺序是：确认 QUERY、确认当前连接、确认内存缺失字节、确认权限列，最后再调整跟踪开关。

一次分析结束后恢复会话状态

optimizer trace 是定位工具，文章示例完成后应关闭。若你在共享客户端里继续执行其他 SQL，保留的 trace 还会影响后续阅读，因此建议把“打开—执行—读取—关闭”放在一个短流程内。

-- 恢复为关闭状态，避免把后续无关语句继续写入 trace
SET optimizer_trace = 'enabled=off';

-- 需要重新分析时，从清晰的会话状态开始检查当前参数
SELECT @@optimizer_trace,
       @@optimizer_trace_features,
       @@optimizer_trace_max_mem_size,
       @@optimizer_trace_limit,
       @@optimizer_trace_offset;

如果本次目的是解释一个索引或连接顺序选择，最终应把 trace 中的成本判断与 EXPLAIN 的最终计划放在一起阅读：前者说明候选和估算过程，后者说明计划摘要。两者回答的问题不同，不能用其中一个替代另一个。

常见问题

optimizer_trace 会记录其他连接的 SQL 吗？

不会。官方说明是只能跟踪当前会话中执行的语句，不能从另一个会话查看。

cost 是真实耗时吗？

不是。它是优化器用于比较候选计划的估算值，不能直接当作毫秒、CPU 时间或磁盘耗时。

为什么 TRACE 里找不到 range_analysis？

字段是否出现取决于语句结构和优化阶段。不要把某个示例的 JSON 路径当成固定 schema，先确认目标 SQL 是否确实产生范围分析，再沿着 rows_estimation 或 considered_execution_plans 查找实际存在的节点。

增大 optimizer_trace_max_mem_size 就一定能解决空结果吗？

不一定。它只针对因跟踪内存上限造成的不完整 trace；如果 INSUFFICIENT_PRIVILEGES=1，应先处理视图或存储程序的权限边界；如果是跨会话读取，也应回到执行目标 SQL 的连接。

总结

观察 MySQL 成本估算分支的关键，不是盯着一行最终索引，而是让 optimizer_trace 暂时记录优化过程，再从 OPTIMIZER_TRACE 里把候选路径、估算行数、成本和 chosen 对应起来。记住三点：同一会话执行和读取、把 cost 当估算值而不是耗时、遇到截断或空 trace 先检查内存和权限。