编程 EXPLAIN ANALYZE 的 actual time 是单 loop 平均:不乘 loops 会低估,树节点相加会重复计

2026-11-18 00:03:49

EXPLAIN ANALYZE 的 actual time 是单 loop 平均:不乘 loops 会低估,树节点相加会重复计

一条耗时 5 秒的查询,在 EXPLAIN ANALYZE 输出里,各节点的时间加起来却有 20 秒。原因在于:TREE 输出中每个节点的耗时既不是总耗时,也不能跨层相加。

一行输出里有四个数,单位各不相同

以 Oracle 官方博客里的 join 为例:

-> Nested loop inner join  (cost=49828 rows=76470)
                             (actual time=1.95..5182 rows=227103 loops=1)
    -> Filter: ((orders.quantity > 1000) and (orders.item_id is not null))
                             (cost=23063 rows=76470)
                             (actual time=1.82..1271 rows=227103 loops=1)
        -> Table scan on orders  (cost=23063 rows=229432)
                             (actual time=1.76..1135 rows=229376 loops=1)
    -> Single-row index lookup on i using PRIMARY (id = orders.item_id)
                             (cost=0.25 rows=1)
                             (actual time=0.0164..0.0164 rows=1 loops=227103)
  • cost:优化器自己的一套单位,只能用来比较两个计划的相对大小,换算成毫秒没有意义。
  • actual time=first..last:毫秒。first 表示 Init 加上读到第一行的时间,last 表示读到最后一行的累计时间。有多个 loop 时,这两个数是“每 loop 平均”,不是总和。
  • rows(估算行数):按单次 loop 估算。
  • actual rows:这条迭代器所有 loop 返回的总行数。
  • loops:迭代器的 Init() 被调用了多少次,也就是它被父节点复用了多少遍。

三种错读

1. 不乘 loops。 上面那个 index lookup 单看是 0.0164 ms,像是可以忽略;乘以 227103 次循环是 3.7 秒,占整条查询 5.07 秒的七成。这也是 EXPLAIN(不 ANALYZE)最容易误导人的地方:计划里一行“Single-row index lookup”看着很干净,代价全藏在次数里。

2. 把各节点耗时相加。 MySQL 的树形输出是累积口径:一个节点的耗时包含它所有子迭代器,不包含父迭代器。叶子节点 C 是 90 ms,B 是 185 ms,其中 180 ms 来自 C,B 自己的开销是 5 ms。三层相加会得出 565 ms,而实际查询只用了 200 ms。要判断哪个节点在消耗时间,用父节点的值减去子节点的值,看差值。

3. 拿估算 rows 直接和 actual rows 比。 两者统计口径不同:actual rows 是全部 loop 的合计,估算 rows 是单 loop 的。对 loops=227103 这种节点,直接比会得出“估算差了 22 万倍”的错误结论。正确做法是先算 actual_rows / loops,再和 estimated_rows 比。

这些数字从哪来,误差有多大

EXPLAIN ANALYZE 不是只读工具,它会真正执行一遍查询,再把结果集丢弃。实现上 MySQL 用 TimingIterator 包住每个真实迭代器,在 Init() 和 Read() 前后读时钟。因此:

  • 有明显的测量开销。Oracle 博客的例子里,一条约 281 ms 的查询,计划顶层报 actual time=380。
  • 实际耗时受缓冲池影响。热缓存下 I/O 慢的问题会被掩盖,冷热各跑一次再下结论。
  • 比较两个计划时用客户端计时,比较一个计划内部的各个节点时用 actual time。两套数不能混用。

版本与格式的互斥关系(8.0.18 → 8.4)

EXPLAIN ANALYZE 从 8.0.18 起提供,只支持 TREE:

EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM t3 WHERE pk > 17\G

写 FORMAT=TRADITIONAL 或 FORMAT=JSON 一律报错,不管 explain_format 设成什么:

ERROR 1235 (42000): This version of MySQL doesn't yet support
'EXPLAIN ANALYZE with JSON format'

8.4 新增了会话变量 explain_format(默认 TRADITIONAL,DEFAULT 是同义词),它只影响普通 EXPLAIN 的默认输出格式。但有一个问题:一旦执行 SET @@explain_format=JSON,后面所有不带 FORMAT=TREE 的 EXPLAIN ANALYZE 都会报 1235 错误。

想在程序里解析执行计划,需要另一个变量 explain_json_format_version(8.3 起):

SET @@explain_json_format_version = 2;   -- 默认 1
EXPLAIN ANALYZE FORMAT=JSON SELECT ... ;

版本 1 是 5.6 时代那套线性 JSON,结构对应不上迭代器树,所以不支持 ANALYZE;版本 2 按 access path 组织,每个对象对应 TREE 的一行,inputs 字段里挂子节点,并且带 actual_rows、actual_loops、actual_first_row_ms、actual_last_row_ms、estimated_rows、estimated_total_cost 这些字段。EXPLAIN FORMAT=JSON INTO @var 也依赖它。

用 JSON 做一次误估排查

把 ANALYZE 输出写成 JSON,按“实际单次行数 / 估算行数”的比值排序,就能定位优化器估错的节点:

SET SESSION explain_json_format_version = 2;
EXPLAIN ANALYZE FORMAT=JSON
SELECT i.name, o.quantity FROM orders o
JOIN items i ON o.item_id = i.id
WHERE o.quantity > 1000 INTO @plan;

SELECT JSON_EXTRACT(@plan, '$.inputs[1].estimated_rows') AS est,
       JSON_EXTRACT(@plan, '$.inputs[1].actual_rows')    AS act,
       JSON_EXTRACT(@plan, '$.inputs[1].actual_loops')   AS loops;

比值明显偏离 1 的节点才是统计信息的问题所在;比值正常但 loops 很大的节点,问题通常是索引缺失或连接顺序,不是统计信息。这两类问题要改的东西完全不同,先分清再动手。

最小复现

CREATE TABLE t1 (c1 INT DEFAULT NULL, c2 INT DEFAULT NULL);
CREATE TABLE t2 (c1 INT DEFAULT NULL, c2 INT DEFAULT NULL);
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
INSERT INTO t2 VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
ANALYZE TABLE t1, t2;

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c2\G

把两边行数放大到几万行,再跑一次,就能看到内外层节点的 loops 变成几万,而 actual time 仍然只有零点几毫秒——乘不乘 loops,结论完全不同。

检查清单

  1. 每个节点的 actual time × loops 才是它占用的总时间。
  2. 非叶子节点的耗时用“父减子”,不要跨层相加。
  3. 比估算行数前先 actual_rows / loops。
  4. cost 不和毫秒混用。
  5. explain_format=JSON 时,EXPLAIN ANALYZE 必须显式带 FORMAT=TREE。
  6. 要程序化解析,用 explain_json_format_version=2;默认的 1 不支持 ANALYZE。

官方口径:MySQL 8.4 Reference Manual 的 EXPLAIN Statement;新 JSON 格式的设计动机见 Oracle 博客 New JSON format for EXPLAIN;iterator 计时与 loops 的拆解见 hackmysql.com。

站内相关:hypergraph 优化器的对比实验也是用 EXPLAIN ANALYZE 逐条验证的,见 #8618;本文只讨论这套输出本身怎么读。

复制全文 生成海报 EXPLAIN MySQL 执行计划 ANALYZE 性能优化

推荐文章

程序员茄子在线接单