如何通过MySQL的Optimizer Trace工具深度分析索引选择的过程

必须会话级开启并设足够内存（如4MB）和end_markers_in_json=on，再执行目标SQL、查OPTIMIZER_TRACE表、立即关闭；关键看range_analysis下potential_range_indices中各索引的rows与cost对比。

怎么开启Optimizer Trace并确保能捕获到索引选择细节

必须在会话级开启，且要设置足够内存，否则关键字段（比如 potential_range_indices）可能被截断。默认的 optimizer_trace_max_mem_size=1048576（1MB）对复杂查询常不够用，遇到联合索引或多表JOIN时建议设为 4194304（4MB）。

开启命令必须包含 end_markers_in_json=on，否则 JSON 输出里没有明确的结构分隔符，读起来像一整块乱码。实际执行顺序不能错：先开 trace → 再跑目标 SQL → 立即查 information_schema.OPTIMIZER_TRACE → 关闭 trace。中间如果执行了其他语句，trace 会被覆盖。

• SET SESSION optimizer_trace = "enabled=on", end_markers_in_json=on;
• SET SESSION optimizer_trace_max_mem_size = 4194304;
• 执行你的查询（例如 SELECT * FROM orders WHERE create_time > '2025-01-01' AND status = 1;）
• SELECT * FROM information_schema.OPTIMIZER_TRACEG

怎么看Optimizer Trace里索引候选评估的关键字段

核心看 range_analysis 节点下的 potential_range_indices 数组——它列出所有被优化器认真考虑过的索引，每个元素包含 index 名、ranges（实际生成的索引范围条件）、rows（估算扫描行数）、cost（总代价）。注意：rows 不是真实扫描数，而是基于统计信息的估算值；cost 是优化器真正用来做决策的数字。

常见陷阱：你以为 idx_create_time_status 一定比全表扫描快，但 trace 显示它的 cost 是 120000，而 table_scan 的 cost 只有 98000。这时候不是索引写得不对，而是优化器认为“走索引+回表”比直接扫表更贵——往往因为 rows 估算过大（统计信息陈旧）或 row_evaluate_cost 参数偏高。

• 重点字段路径：steps → join_optimization → range_analysis → potential_range_indices
• 对比必须同时看 table_scan 和各索引的 cost，不能只盯 rows
• 如果某个索引没出现在 potential_range_indices 里，说明它根本没被纳入候选（可能是类型不匹配、函数包裹、隐式转换等）

为什么索引明明存在却没被选中？从Trace里找三类硬证据

Trace 不会直接说“这个索引被忽略了”，但会在不同节点留下线索。最常踩的坑是把问题归咎于“优化器 bug”，其实多数情况是以下三类原因在 JSON 里白纸黑字写着：

• 统计信息偏差：看 table_scan 下的 rows 和索引下 rows 是否严重偏离实际数据分布（比如表有 10 万行，但 table_scan.rows 显示 5000，而 idx_xxx.rows 显示 80000）——这时 ANALYZE TABLE 就是解药
• 索引无法覆盖WHERE条件：检查 ranges 字段是否为空或只含部分列。例如 WHERE 用到了 a=1 AND b>2 AND c=3，但索引是 (a,b)，那么 c=3 就不会进 ranges，只能回表后过滤，代价飙升
• ICP（索引下推）未生效：MySQL 5.6+ 才支持。如果 range_analysis 里出现 using_index_condition: true，说明 ICP 生效；若为 false，意味着本该下推的条件被拖到 server 层判断，回表次数暴增

如何验证你从Trace里读出的结论是否靠谱

别光靠 JSON 推理，必须用 FORCE INDEX 和 EXPLAIN FORMAT=JSON 交叉验证。Trace 告诉你“优化器觉得 A 更便宜”，那你就强制它走 B，再用 EXPLAIN FORMAT=JSON 看 B 路径的真实 cost 和 rows 是否真比 A 低——很多时候你会发现，强制走索引后 rows 比 trace 里估算的少一个数量级，说明统计信息早该更新了。

另一个容易被忽略的点：同一个 SQL 在不同时间跑，trace 结果可能完全不同。因为优化器会参考 innodb_stats_auto_recalc 和数据变更频率动态更新统计信息。所以分析前先确认 SHOW TABLE STATUS LIKE 'orders' 里的 Rows 和你心里的量级是否一致。