MySQL optimizer_trace怎样读取一次查询的优化决策

读取一次查询的优化决策，可以在同一个 MySQL 会话启用 optimizer_trace，紧接着对目标 SQL 做 EXPLAIN，再读取 information_schema.OPTIMIZER_TRACE。先核对 QUERY 是否属于目标语句、跟踪是否截断以及权限是否足够，然后在相同查询块和计划前缀下比较候选路径、估算行数、代价与选择原因。

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

本文以 MySQL 8.4 为范围，将这套方法理解为“会话内的优化决策记录”：EXPLAIN 帮助看选中了什么，trace 补充选择时考虑了什么。这适合解释索引、连接顺序或排序方案的取舍；实际慢在哪里还需要运行期数据。以下 SQL 为原创示例，本文未连接数据库执行，不提供虚构的 trace 输出。

先用同会话捕获一条语句

-- 使用独立诊断连接，先保存会话设置，结束时原样恢复。
SET @diag_old_trace = @@SESSION.optimizer_trace;
SET @diag_old_mem = @@SESSION.optimizer_trace_max_mem_size;
SET @diag_old_offset = @@SESSION.optimizer_trace_offset;
SET @diag_old_limit = @@SESSION.optimizer_trace_limit;

-- 示例上限为1 MiB，仅保留最近一条；实际按查询复杂度调整。
SET SESSION optimizer_trace_max_mem_size = 1048576;
SET SESSION optimizer_trace_offset = -1;
SET SESSION optimizer_trace_limit = 1;
SET SESSION optimizer_trace = 'enabled=on';

-- 假定已有shop.shipments表；EXPLAIN只观察优化，不执行目标SELECT。
-- 该查询是本次原创示例，替换成实际SQL时保留原来的条件和参数。
EXPLAIN FORMAT=JSON
SELECT shipment_id, promised_at
FROM shop.shipments
WHERE warehouse_id = 17
  AND delivery_state = 'queued'
ORDER BY promised_at
LIMIT 30;

-- 紧接着读取，不在中间运行其他待跟踪查询。
SELECT QUERY, TRACE,
       MISSING_BYTES_BEYOND_MAX_MEM_SIZE,
       INSUFFICIENT_PRIVILEGES
FROM information_schema.OPTIMIZER_TRACE;

-- 导出上面结果后恢复设置；读取窗口设置会清除已有跟踪。
SET SESSION optimizer_trace = @diag_old_trace;
SET SESSION optimizer_trace_max_mem_size = @diag_old_mem;
SET SESSION optimizer_trace_offset = @diag_old_offset;
SET SESSION optimizer_trace_limit = @diag_old_limit;

独立诊断连接能避免连接池换连接以及客户端后台语句干扰。如果在一个窗口启用，另一个窗口执行查询，即使账号一样也不是同一会话。上述写法捕获的是 EXPLAIN 所触发的优化记录，QUERY 会显示对应的 EXPLAIN 文本；不要把它描述成原 SELECT 已执行完成。若要跟踪真实 SELECT，需要评估执行成本，并把 EXPLAIN 那段替换为实际语句。

示例把采集窗口限定为最近一条，开始前设置 offset 和 limit，结束后恢复。窗口设置的变更会清除先前记录，所以必须先保存输出再恢复；采集中途随意改窗口可能丢掉目标证据。脚本在正常执行路径恢复设置，若客户端因错误中断，仍须手动完成末尾恢复，或关闭这个独立连接。

把跟踪看作优化决策记录

面对长 JSON，先确定读者真正要回答的问题。例如示例只关心：warehouse_id 与 delivery_state 过滤后，优化器为什么采用某个访问路径，以及 ORDER BY promised_at 和 LIMIT 是否改变了取舍。先定位相应查询块与表，再阅读相关估算和候选方案，通常比从第一行开始逐字读完更有效。

跟踪按优化活动组织，包含数组和对象的嵌套关系。对象成员不应被当成严格执行时间线；同名字段可能出现在不同层级。实际可见内容受语句结构、优化器功能和版本影响，不能写一个固定路径并假定所有查询都有完全相同的节点。遇到子查询、派生表或多个连接表，先确认当前节点属于哪个查询块、表别名和计划前缀。

结构说明：https://dev.mysql.com/doc/refman/8.4/en/trace-general-structure.html

围绕同一查询块比较候选路径

阅读时保留“候选对象—估算依据—取舍原因—上下文”四项。潜在范围索引的可用性只能说明某项访问能力是否适合该处条件，不能单独证明最终计划必须选择它。考虑过的访问路径可能包含扫描、索引查找等方式，比较代价时要在同一表、同一连接前缀和同一优化分支下进行。

chosen 和 cause 应放回它所在的父节点解释。某个局部分支 chosen 为 true，不代表该索引一定成为整条查询的最终访问路径；某一优化方式出现 chosen 为 false，也不代表这个索引在所有位置都被禁止。cause 若给出的是规则适用性，就先检查条件结构；若体现成本取舍，则继续看估算行数、过滤效果及后续操作，不能一律归因于统计信息陈旧。

在示例的订单配送场景里，即使存在某个过滤索引，也应同时考虑返回列是否需要回表、排序是否另有成本、LIMIT 能否影响预期读取量。这里没有指定或声称任何索引一定获胜；需要由实际表结构和采集结果回答。cost 是优化器模型的估算值，不是毫秒，也不是“索引快几倍”的直接证据。

官方阅读示例：https://dev.mysql.com/doc/refman/8.4/en/tracing-example.html。示例用于理解记录组织方式，不要复制其中的行数和代价作为自己的运行结果。

排除记录不完整与错误比较

MISSING_BYTES_BEYOND_MAX_MEM_SIZE 非零表示受内存上限影响有内容未保存，此时“没看到某候选”不能直接推出“优化器没有考虑”。需要在可承受范围增加会话上限并重新捕获，单纯把客户端显示宽度改大不能补回服务器已经没有记录的字节。反过来，服务器字段显示未截断，也不代表客户端导出没有被自身长度限制截短。

INSUFFICIENT_PRIVILEGES 为 1 时，可能因为涉及 SQL SECURITY DEFINER 的视图或存储例程而看不到完整信息。应按授权范围诊断，不通过放大内存解决权限问题。普通查表权限和能否看到内部跟踪不是完全相同的判断。

另一种常见反例是先执行目标 SQL，再运行几条排查查询，最后才读取 trace。默认窗口很小，留下的可能已经是别的语句。必须核对 QUERY，并在记录中保存会话上下文。存储例程含多个子语句时尤其如此，要调整捕获窗口与读取目标，不能拿最后一个子语句解释整个调用。

字段说明：https://dev.mysql.com/doc/refman/8.4/en/information-schema-optimizer-trace-table.html

保存上下文并结束跟踪

有用的诊断记录还包括 MySQL 版本、完整 SQL 与参数类型、表结构和索引定义、相关会话设置、统计信息状态及对应 EXPLAIN。改变一个因素后重新捕获，比较相同上下文里的候选取舍；若同时换参数、刷新统计和加索引，就很难解释结果由谁造成。

trace 会增加记录开销，不适合长期给所有业务查询开启。保存目标结果后恢复原设置，避免诊断状态随连接池流转。导出内容可能带查询常量和业务信息，分享时只保留解释问题所需内容。对性能结论，在关闭跟踪后用可比较负载观察耗时与运行期行数；优化器选择理由清楚，并不保证运行中没有锁等待或缓存差异。

必须运行原 SELECT 才能得到 trace 吗？

可以先通过 EXPLAIN 观察其优化记录，尤其适合避免执行昂贵的读取；但要明确采集对象，并对实际执行表现另做比较。

为什么换个窗口读不到？

跟踪只属于当前会话。固定同一物理连接完成设置、目标语句和结果读取，再保存输出。

为什么 JSON 路径和教程不同？

版本、语句结构和启用功能都可能改变记录。先根据父节点和查询块定位，不把某次示例的数组下标做成通用解析契约。