如何通过MySQL的optimizer_trace看清基于成本的执行计划选择过程？

MySQL的基于成本优化器（cost-based optimizer）在生成执行计划时，会对每一种可能的表访问路径、连接顺序和索引使用方式计算一个数值化的代价，然后挑选总代价最小的方案。但explain只给出结果，不展示中间比较过程。optimizer_trace正是官方提供的执行计划调试接口，它能把优化器内部决策路径完整输出为结构化JSON，让开发者直接看到每张表的候选计划及其cost组成。

一、optimizer_trace的基本开启与查看

optimizer_trace默认是关闭的，因为它会带来一定的内存与CPU开销。我们可以在会话级别单独打开，只影响当前连接，因此非常适合在测试环境或临时排查线上问题时使用。打开后执行目标SQL，再查询information_schema.optimizer_trace表即可拿到完整的trace内容。

下面是一段典型的开启与查看脚本。注意trace内容以JSON字符串存放在TRACE列中，长度可能很大，需要客户端支持长文本显示。

-- 开启当前会话的optimizer_trace，只记录本连接
SET optimizer_trace='enabled=on';

-- 执行需要分析的SQL
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' ORDER BY created_at DESC LIMIT 10;

-- 查看优化器追踪结果
SELECT TRACE FROM information_schema.optimizer_traceG

-- 关闭trace
SET optimizer_trace='enabled=off';

从输出中可以看到多个一级节点，例如join_preparation描述SQL重写，join_optimization包含代价计算与计划选择，join_execution则是执行阶段信息。我们重点关注join_optimization里的rows_estimated、cost_info以及considered_execution_plans。

二、cost-based优化器的代价模型

MySQL优化器估算代价时主要考虑两类成本：io_cost代表从存储引擎读取数据页的磁盘或缓冲池IO开销，cpu_cost代表在内存中处理记录（比如比较键值、做条件过滤）的CPU开销。两者相加构成某个访问路径的总cost。优化器会为同一张表生成多个候选路径，比如全表扫描、不同索引的范围扫描、索引覆盖扫描等。

在optimizer_trace的trace JSON中，每个候选计划都会带有一个cost_info对象，里面列出read_cost、eval_cost和prefix_cost等字段。read_cost近似对应IO代价，eval_cost对应CPU代价，prefix_cost则是到当前表为止的整体前缀代价，用于多表连接时的比较。理解这些字段，才能判断优化器为什么偏爱某个索引。

{
  "plan_prefix": [],
  "table": "`orders`",
  "best_access_path": {
    "considered_access_paths": [
      {
        "access_type": "ref",
        "index": "idx_user_status",
        "rows": 120,
        "cost_info": {
          "read_cost": 48.2,
          "eval_cost": 24.0,
          "prefix_cost": 72.2,
          "data_read_per_join": "19K"
        }
      },
      {
        "access_type": "scan",
        "rows": 98000,
        "cost_info": {
          "read_cost": 5200.0,
          "eval_cost": 19600.0,
          "prefix_cost": 24800.0,
          "data_read_per_join": "15M"
        }
      }
    ]
  }
}

上面这段简化的trace显示，优化器认为使用idx_user_status索引的ref访问只需72.2的代价，而全表扫描需要24800，因此显然会选择索引。但如果统计信息过期，rows估算偏差变大，就可能反过来选错。这也是为什么有时我们明明有索引，MySQL却走了全表扫描。

三、利用trace定位统计信息误判

当执行计划不符合预期时，第一步应看trace里considered_access_paths中每个路径的rows估算值。这个值来自表的统计信息（如innodb_index_stats）。如果实际数据分布倾斜，而统计采样不准，优化器会低估或高估某路径的行数，进而算错cost。

举个例子，某订单表user_id=100实际只有10行，但统计信息显示平均每个user_id有5000行，优化器就会觉得走索引不如扫描小表划算。通过trace发现rows被估高后，可以手动更新统计信息：

-- 重新采集表统计信息
ANALYZE TABLE orders;

-- 再次开启trace验证计划变化
SET optimizer_trace='enabled=on';
SELECT * FROM orders WHERE user_id = 100;
SELECT TRACE FROM information_schema.optimizer_traceG
SET optimizer_trace='enabled=off';

对比两次trace里的rows与cost_info，就能确认是否因统计信息导致优化器误判。在某些无法改统计信息的场景，也可以用FORCE INDEX提示，但根本解决还是保持统计准确。

四、多表连接中的prefix_cost分析

对于多表JOIN，优化器会枚举不同的连接顺序，每一步都计算prefix_cost，也就是把前面几张表连接完之后再接入当前表的总代价。trace中的considered_execution_plans数组会列出每一种连接顺序的评估结果，我们可以从中看出为什么优化器把小表放到了驱动位置。

以下SQL演示了一个两表关联的分析过程。通过trace能够确认优化器是否选择了它宣称的最优顺序，以及中间临时表的行数估算。

SET optimizer_trace='enabled=on';

SELECT u.name, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.city = 'Beijing' AND o.status = 'paid';

SELECT TRACE FROM information_schema.optimizer_traceG
SET optimizer_trace='enabled=off';

在输出的considered_execution_plans里，会看到先访问users还是orders的两种方案及其prefix_cost。如果某方案虽然后面cost低但中间结果集暴涨，优化器通常会避开。借助这些信息，我们可以针对性建联合索引或调整WHERE条件写法来引导优化器。

五、使用注意事项与性能影响

虽然optimizer_trace很有用，但它不是免费午餐。开启后优化器需要把决策过程序列化成JSON，会增加内存分配和CPU时间。因此生产环境务必使用会话级开关，执行完一条问题SQL就马上关闭，且不要在高并发账户上长期开启。

另外trace内容可能非常大，尤其是复杂SQL，查询information_schema.optimizer_trace时建议只取TRACE列，并在客户端关闭自动格式化以防卡死。如果只想看某部分，可以用JSON提取函数处理，但多数情况直接阅读原始trace更直观。

-- 仅对单个查询开启，用完即关，避免影响其他请求
SET SESSION optimizer_trace='enabled=on';
-- 你的慢SQL
SELECT COUNT(*) FROM large_table WHERE col_a > 100;
SELECT TRACE FROM information_schema.optimizer_trace;
SET SESSION optimizer_trace='enabled=off';

只要控制好作用范围，optimizer_trace就是排查“为什么MySQL不选我的索引”这类问题最权威的手段，比单纯靠经验猜要可靠得多。

optimizer_traceMySQLcost_based_optimizer修改时间：2026-08-09 01:12:35