MySQL optimizer_trace 还原连接顺序选择的分析方法

要还原 MySQL 为什么选择某个连接顺序，不能只盯着 EXPLAIN 的最终结果。更有效的方法是开启 optimizer_trace，在 join_optimization 下找到 considered_execution_plans，再把每个候选的 plan_prefix、当前 table、best_access_path 和 cost_for_plan 串起来。这样既能看到胜出的顺序，也能看到其他顺序在哪一步因为成本过高被剪枝。

MySQL 8.4 官方手册：https://dev.mysql.com/doc/refman/8.4/en/optimizer-tracing.html

optimizer_trace 适合解释“优化器为什么这样选”，EXPLAIN 适合确认“最后选了什么”。分析连接顺序时应把两者放在一起看。

先采集一份没有截断的 optimizer_trace

下面用订单、客户和支付三张表说明。目标查询先从订单中过滤状态和日期，再连接客户与支付记录。采集 trace 的关键是开启追踪、执行目标 SQL、立刻在同一会话读取 INFORMATION_SCHEMA.OPTIMIZER_TRACE。

-- 在当前诊断会话开启优化器追踪，并保留便于阅读的换行
SET SESSION optimizer_trace = 'enabled=on,one_line=off';

-- 提高单次追踪可使用的内存，避免复杂连接的 JSON 被截断
SET SESSION optimizer_trace_max_mem_size = 1048576;

-- 目标查询：观察 orders、customers、payments 的连接顺序选择
SELECT o.id, c.name, p.paid_at
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN payments AS p ON p.order_id = o.id
WHERE o.status = 'PAID'
  AND o.created_at >= '2026-10-01';

-- 必须在同一会话读取刚才语句的轨迹和截断标记
SELECT QUERY, TRACE,
       MISSING_BYTES_BEYOND_MAX_MEM_SIZE,
       INSUFFICIENT_PRIVILEGES
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

-- 诊断结束后关闭，避免后续语句继续产生追踪内容
SET SESSION optimizer_trace = 'enabled=off';

读取结果前先看两个状态列。MISSING_BYTES_BEYOND_MAX_MEM_SIZE 大于 0 说明轨迹不完整，这时不能据残缺 JSON 判断某条候选从未被考虑；INSUFFICIENT_PRIVILEGES 为 1 则表示涉及视图或存储程序的权限阻止了轨迹显示。官方文档还提醒，trace 的格式和内容可能随版本变化，所以脚本解析时应围绕语义字段做容错，不要依赖固定行号。

从 join_optimization 进入候选连接计划

trace 顶层通常按优化阶段组织。分析连接顺序时，重点不是 join_preparation 中的查询展开，而是 join_optimization 里的成本计算。继续向下寻找 considered_execution_plans，这里记录优化器在构造连接树时考虑的候选前缀。

若开启了 end_markers_in_json，大段 trace 的闭合位置更容易辨认，但官方说明这种重复键标记会让输出不再是合法 JSON。需要交给 JSON 解析器时应保持它关闭。

用 plan_prefix 还原候选连接顺序

还原顺序时使用一个简单规则：候选顺序 = plan_prefix 中的表 + 当前 table。例如某个节点的 plan_prefix 是 [o, c]，当前 table 是 p，那么这个节点表达的候选前缀就是 o → c → p。如果另一节点是 [c, o] + p，则它代表不同的连接顺序。

三表查询理论上可能出现多个排列，但实际 trace 不一定完整展开全部排列。依赖关系、外连接语义、常量表、已固定的提示以及成本剪枝都会缩小搜索空间。官方文档也指出，多表贪心搜索可能产生阶乘级候选，因此 trace 可以选择关闭 greedy_search 等特性的记录；做连接顺序分析时应确认没有把需要的搜索轨迹过滤掉。

-- 查看当前会话是否记录贪心搜索、范围优化等特性
SELECT @@SESSION.optimizer_trace_features;

-- 分析连接顺序时保留 greedy_search，其他开关按问题范围决定
SET SESSION optimizer_trace_features =
  'greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on';

不要看到一个 chosen: false 就断言整条顺序被淘汰。它可能只是说明当前表的某个访问路径输给了同表的另一访问路径。只有把层级、前缀和累计成本连起来，才能知道被淘汰的是索引方案、表追加位置，还是整个候选前缀。

分清访问路径成本和连接前缀累计成本

best_access_path 内部的 considered_access_paths 比较“在当前前缀下，怎样访问这张表”。常见候选包括全表扫描、ref、eq_ref 或范围访问。选出单表访问方式后，外层的 cost_for_plan 才表示把这张表接到已有前缀后形成的新累计成本。

因此，正确比较方式不是把所有 cost 数字放在一张表里横向比，而是逐层回答三个问题：

1. 当前 plan_prefix 已经包含哪些表？
2. 追加当前 table 时，哪种 access_type 被选中？
3. 新前缀的 rows_for_plan 和 cost_for_plan 是否比同层候选更小？

如果 trace 出现 pruned_by_cost 一类原因，表示该候选的估算成本已经不足以竞争，优化器不会继续扩展它。这个信息很重要：后面没有更多子节点，并不等于解析遗漏，而可能是搜索树主动剪枝。

把 trace 中的胜出路径与 EXPLAIN 对齐

完成候选树阅读后，再执行 EXPLAIN FORMAT=JSON 或普通 EXPLAIN。最终计划中的表顺序应能在 trace 的保留路径中找到。两者关注点不同：EXPLAIN 给出最终计划，trace 补充形成该计划时的候选、估算和取舍。

-- 查看最终计划，重点核对表顺序、访问类型、索引和估算行数
EXPLAIN FORMAT=JSON
SELECT o.id, c.name, p.paid_at
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN payments AS p ON p.order_id = o.id
WHERE o.status = 'PAID'
  AND o.created_at >= '2026-10-01';

如果胜出顺序看起来反常，先检查估算输入，而不是立刻加 STRAIGHT_JOIN。例如 orders.status 的分布高度倾斜，但统计信息仍认为“PAID”很少或很多，就会影响首表选择；连接列基数估算不准，也会让后续 rows_for_plan 逐层放大。

需要特别注意：trace 中的 rows 和 cost 是优化阶段的估算，不是 EXPLAIN ANALYZE 的实测时间和行数。把估算误当实测，是分析连接顺序时最常见的偏差之一。

用统计信息和受控提示完成回归检查

当 trace 指向明显过时的基数估算，可以先更新相关表统计信息，再重新采集同一条 SQL 的 trace。若顺序发生变化，应比较变化前后的 rows_for_plan 和 cost_for_plan，确认原因确实来自估算输入。

-- 更新参与连接的三张表统计信息，避免用过时基数做结论
ANALYZE TABLE orders, customers, payments;

-- 只在诊断阶段指定连接顺序，用于和优化器原计划做受控对比
EXPLAIN FORMAT=JSON
SELECT /*+ JOIN_ORDER(o, c, p) */
       o.id, c.name, p.paid_at
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN payments AS p ON p.order_id = o.id
WHERE o.status = 'PAID'
  AND o.created_at >= '2026-10-01';

JOIN_ORDER 提示应使用查询里的别名。它适合验证“如果按另一顺序连接，计划会怎样”，但不应在没有回归数据时直接固化到业务 SQL。连接提示还会受到依赖关系、外连接语义和常量表位置等约束；与语义冲突的提示可能被忽略。

迁移与版本变化时的检查清单

optimizer_trace 的内容可能随 MySQL 版本变化。升级前后做计划回归时，不要拿整段 JSON 做字符串比较，而应抽取稳定的分析维度：最终表顺序、候选前缀、访问类型、索引、估算行数、累计成本和剪枝原因。

• 确认 trace 未被内存上限截断，且权限足以显示内容。
• 确认采集与读取发生在同一会话，并且读到的是目标 SQL。
• 按 plan_prefix + table 组合候选，不把单个节点当完整顺序。
• 先比较同层候选，再解释 chosen 与成本剪枝。
• 用 EXPLAIN 对齐最终计划，用统计信息解释估算变化。
• 把提示语句限制在诊断和回归测试中，避免掩盖根因。

常见问题

为什么 considered_execution_plans 里没有所有表排列？

优化器不会无条件展开所有排列。连接依赖、外连接语义、常量表、提示和成本剪枝都会减少候选；如果关闭了 greedy_search 的追踪，也会看不到相应搜索细节。

chosen: false 是否表示这张表不会出现在最终计划？

不一定。它可能只表示这张表在某个前缀下的一种访问路径未被选择。需要结合所在层级、plan_prefix 和同层其他候选一起判断。

optimizer_trace 能代替 EXPLAIN ANALYZE 吗？

不能。trace 解释优化器的候选与估算，EXPLAIN ANALYZE 关注实际执行中的时间和行数。前者回答“为什么选”，后者帮助判断“估算与实际相差多少”。

为什么导出的 trace 不能被 JSON 工具解析？

先检查 end_markers_in_json。该变量开启后会重复结构键以帮助人工阅读，但官方说明输出不再是合法 JSON；交给解析器时应关闭它。