MySQL Optimizer Trace 怎么查看索引选择原因

MySQL 明明有索引，优化器却选择了全表扫描或另一条索引，单看 EXPLAIN 往往只能看到“选了什么”，看不到“为什么这样选”。这时可以在同一个连接里开启 Optimizer Trace：执行原查询，再读取 INFORMATION_SCHEMA.OPTIMIZER_TRACE，从候选访问路径、估算行数和成本判断索引落选原因。

官方地址：https://dev.mysql.com/

先用 EXPLAIN 固定要分析的查询与索引候选

建议先把线上慢 SQL 脱敏后放到测试或只读环境，用 EXPLAIN 看当前计划。下面的例子假设订单表上有 idx_status_created_at，查询希望按状态和创建时间过滤：

-- 先记录当前计划，确认实际查询与索引候选
EXPLAIN
SELECT id, user_id, created_at
FROM orders
WHERE status = 'paid'
  AND created_at >= '2026-09-01'
ORDER BY created_at DESC
LIMIT 50;

如果 key 为空，或者 key 不是预期索引，先记下 possible_keys、rows、filtered 和 Extra。Trace 的任务不是替代 EXPLAIN，而是把优化器在计划生成阶段比较过的候选展开。

在当前会话开启 optimizer_trace 并执行原语句

不要只执行开启语句后换到另一个连接。官方流程要求在当前会话执行被追踪的语句；连接池场景尤其容易因为连接切换而读不到预期结果。

-- 只在当前诊断连接打开追踪，并给较大的 Trace 留出空间
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1000000;

-- 在同一连接执行要分析的原查询
SELECT id, user_id, created_at
FROM orders
WHERE status = 'paid'
  AND created_at >= '2026-09-01'
ORDER BY created_at DESC
LIMIT 50;

-- 读取当前会话最近的优化器记录
SELECT QUERY, TRACE, MISSING_BYTES_BEYOND_MAX_MEM_SIZE,
       INSUFFICIENT_PRIVILEGES
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

optimizer_trace_max_mem_size 过小，Trace 可能被截断；出现非零的 MISSING_BYTES_BEYOND_MAX_MEM_SIZE 时，先扩大诊断会话的上限再重跑。追踪结束后要执行：

-- 诊断完成立即关闭，避免这个连接继续积累 Trace
SET optimizer_trace = 'enabled=off';

从 OPTIMIZER_TRACE 读取候选路径与成本

TRACE 是 JSON 文本，不要从第一行直接跳到结论。先按下面的顺序搜索关键对象；不同 SQL 的节点组合会变化，但“可用性—估算—成本—选择”这条阅读路径比较稳定。

例如，看到某索引 usable: false，方向通常是谓词无法形成有效范围、类型或排序条件不匹配；看到多个索引都可用但成本不同，就要继续对照估算行数、回表代价和排序代价。不要把“用了索引”当成“更快”的证明，Trace 记录的是优化器当时掌握的统计与成本判断。

根据 cause 与 chosen 判断索引为何落选

排查时把 Trace 当成“决策链”，而不是一串必须全部读懂的 JSON。可以先定位目标索引名称，再向上找它所在的分析对象：

1. 先查可用性：如果候选索引没有进入范围分析，检查列类型、隐式转换、函数包裹、联合索引最左列和排序方向。
2. 再查估算：索引可用但预计行数很大，优先核对统计信息和数据分布，不要立即用 FORCE INDEX 掩盖问题。
3. 最后看成本：如果候选被 pruned_by_cost 或类似原因淘汰，说明它参与过比较，只是模型认为另一条计划更便宜。

Trace 只能解释优化器的选择依据，不能直接证明真实执行耗时。结论最好与 EXPLAIN ANALYZE、表统计信息和实际数据分布交叉核对；生产环境先在低风险窗口或副本上复现。

关闭追踪并把结论落到统计信息与索引设计

如果确认是统计信息偏旧，更新统计信息后重新执行同一组 EXPLAIN 与 Trace；如果是索引列顺序或排序需求不匹配，再调整索引设计。若只是单条特殊查询，不要把 Trace 里的某个候选直接固化成全局规则。

一个可复用的检查清单是：同一连接、同一 SQL、Trace 未截断、候选索引确实可用、估算值与数据分布没有明显偏差、成本结论与实际执行计划相互印证。这样才能回答“为什么没选这条索引”，而不是只得到“加一个索引再试试”。

常见问题

Optimizer Trace 能读取其他连接执行过的 SQL 吗？

不能。官方说明它只追踪当前会话执行的语句，必须在同一个连接里开启、执行和读取。

Trace 为空是不是索引没有生效？

不一定。先确认是否换了连接、是否真的执行了目标语句，再检查追踪开关和读取权限。

为什么 Trace 里有索引但最终仍然全表扫描？

索引“可用”只表示进入了比较过程；如果估算行数或综合成本更高，它仍可能在最终决策中落选。

需要一直打开 optimizer_trace 吗？

不需要。它适合短时间诊断，读完结果就关闭，并保留 SQL、EXPLAIN 和关键 Trace 片段供复盘。