EXPLAIN ANALYZE 阅读法 — 从执行计划中找出真正瓶颈的顺序

引言 — 看了执行计划却仍然不知道哪里慢
EXPLAIN 与 EXPLAIN ANALYZE — 一个是预测,一个是执行
读输出的顺序 — 从内层节点开始,自下而上
cost 不是时间,rows 的偏差才是真正的信号
loops 会被相乘 — actual time 是单次平均值
Seq Scan 无罪,以及三种连接的分水岭
BUFFERS 与 Rows Removed by Filter — 剩下的两条线索
结语 — 看偏差,而不是看节点名称

引言 — 看了执行计划却仍然不知道哪里慢

遇到慢查询,大多数人都会先加上 EXPLAIN ANALYZE。然后盯着满屏的缩进和括号里的数字看半天,最后跳到一个结论:「有 Seq Scan,那就建索引吧。」这个结论有时确实是对的。但执行计划想告诉你的,往往是另一件事。

读执行计划,不是扫一眼节点名称,而是寻找优化器的预测与实际结果之间的偏差。如果没有偏差,说明优化器在自己已知的信息范围内选了最优解,剩下的瓶颈是物理问题。如果偏差很大,说明优化器是在错误的前提上做了精确计算,该修的不是查询而是统计信息。

本文逐行解剖真实输出,梳理应该按什么顺序看哪些数字。以 PostgreSQL 为基准,MySQL 有差异的地方会随时指出。

EXPLAIN 与 EXPLAIN ANALYZE — 一个是预测,一个是执行

这两条命令只是名字相似,做的事情完全不同。

-- 只生成计划就结束。查询不会被执行。
EXPLAIN
SELECT * FROM orders WHERE user_id = 42;

-- 实际执行之后,把实测值叠加到计划上展示。
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42;

只加 EXPLAIN 时,输出的是优化器基于统计信息生成的计划和预估值。快而安全,但无法知道这些预估是否准确。加上 ANALYZE 选项后会真正执行查询,并一并输出每个节点实际返回了多少行、耗时多久。

这里经常出事故。EXPLAIN ANALYZE UPDATE ... 是真的会执行 UPDATE 的。分析写入类查询时,必须用事务包起来并回滚。

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'shipped' WHERE id = 1001;
ROLLBACK;

选项还有几个,而实务中有用的组合基本上是固定的。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT TEXT)
SELECT ...;

BUFFERS — 各节点的块读取统计。没有它就看不到缓存问题。
VERBOSE — 输出列清单和带模式限定的名称。连接复杂时很有用。
SETTINGS — 显示偏离默认值的规划器参数。调查别人搭建的环境时具有决定性作用。
FORMAT JSON — 只在交给工具解析时使用,人来读的话 TEXT 更好。

MySQL 从 8.0.18 开始才有 EXPLAIN ANALYZE,在此之前是用 EXPLAIN FORMAT=TREE 或 SHOW WARNINGS 来确认优化器改写后的查询。输出形式不同,但阅读原则是一样的。

读输出的顺序 — 从内层节点开始,自下而上

执行计划是一棵树。以文本输出时,缩进越深就越靠近树的内侧,也就是越先执行的节点。从上往下读,等于是反着读。

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, o.id, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'KR'
  AND o.created_at >= '2026-06-01';

Hash Join  (cost=1842.00..24310.55 rows=18420 width=44)
           (actual time=8.412..131.207 rows=17903 loops=1)
  Hash Cond: (o.user_id = u.id)
  Buffers: shared hit=4021 read=9877
  ->  Seq Scan on orders o  (cost=0.00..20117.00 rows=92300 width=20)
                            (actual time=0.019..92.441 rows=91188 loops=1)
        Filter: (created_at >= '2026-06-01 00:00:00'::timestamp)
        Rows Removed by Filter: 708812
        Buffers: shared hit=3110 read=9004
  ->  Hash  (cost=1610.00..1610.00 rows=18560 width=28)
            (actual time=8.287..8.288 rows=18402 loops=1)
        Buckets: 32768  Batches: 1  Memory Usage: 1408kB
        Buffers: shared hit=911 read=873
        ->  Seq Scan on users u  (cost=0.00..1610.00 rows=18560 width=28)
                                 (actual time=0.011..5.902 rows=18402 loops=1)
              Filter: (country = 'KR'::text)
              Rows Removed by Filter: 61598
              Buffers: shared hit=911 read=873
Planning Time: 0.184 ms
Execution Time: 133.902 ms

阅读顺序是这样的。

缩进最深的 Seq Scan on users u 先执行,产出 18402 行。
该结果向上进入 Hash 节点,在内存中构建成哈希表。
接着 Seq Scan on orders o 流出 91188 行。
最上层的 Hash Join 把两者结合,输出 17903 行。

也就是说,兄弟节点有多个时上面的先执行,父节点要等子节点结束后才完成。习惯了树结构之后,一看到计划就能立刻看出「数据在哪里产生、又流向哪里」。

有一点需要注意。父节点的 actual time 是包含子节点时间的累计值。上面输出中 Hash Join 的 131ms 里,有 92ms 是 orders 扫描花掉的。想知道某个节点纯粹自身耗费的时间,就要减去子节点的时间。不知道这一点,就会得出「连接竟然要 131ms」这种错误结论。

cost 不是时间,rows 的偏差才是真正的信号

cost=1842.00..24310.55 中,前一个值是产出第一行为止的成本,后一个值是到最后一行的总成本。问题在于这些数字的单位。它不是毫秒,也不是秒,而是把顺序读取一个页面的成本定为 1.0 的任意单位。

-- 成本单位的基准值
SHOW seq_page_cost;      -- 1.0  (基准)
SHOW random_page_cost;   -- 4.0  (默认值,如果是 SSD 则 1.1 附近更现实)
SHOW cpu_tuple_cost;     -- 0.01
SHOW cpu_operator_cost;  -- 0.0025

所以 cost 24310 并不表示 24 秒。这个值只是用来在同一条查询的多个候选计划之间互相比较的分数,而拿它和另一条查询的 cost 相比,基本上没有意义。经常能看到有人建议设一条「cost 超过 10000 就危险」的标准,但这没有任何依据。

真正该看的,是同一个节点内的那两个 rows=。

->  Seq Scan on orders o  (cost=... rows=92300 ...) (actual ... rows=91188 loops=1)

预估 92300,实际 91188。误差 1.2 个百分点,说明统计信息是健康的。反过来,遇到下面这样的输出,情况就不同了。

->  Index Scan using idx_orders_status on orders o
      (cost=0.42..8.44 rows=1 width=20)
      (actual time=0.031..214.882 rows=482913 loops=1)

预估 1 行,实际 482913 行,偏差超过 4 万倍。优化器多半是判断「只会出 1 行,那用 Nested Loop 连起来就行」,而当这个前提崩塌时,内层节点就被重复了 48 万次。此时该修的不是连接提示,而是统计信息。

-- 首选处方: 刷新统计信息
ANALYZE orders;

-- 仍然偏差很大时,提高特定列的直方图精度
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- 因为列之间存在相关性而出错的情况 (例如城市与邮政编码)
CREATE STATISTICS stat_orders_geo (dependencies, ndistinct)
  ON city, postal_code FROM orders;
ANALYZE orders;

最后这种扩展统计信息,需要用到的场合比想象中多。优化器默认假设各个条件彼此独立,并把它们的选择性相乘。而现实中相关的列对很多,这时预估值就会远小于实际值。

loops 会被相乘 — actual time 是单次平均值

最常见的误读就出在这里。

->  Index Scan using idx_order_items_order_id on order_items i
      (cost=0.43..3.21 rows=4 width=16)
      (actual time=0.008..0.011 rows=4 loops=52310)

看到 actual time=0.008..0.011 就想着「0.011 毫秒,可以忽略」直接略过,这是不行的。括号里的时间和行数都是以单次执行为基准的平均值。实际总量要乘上 loops 才能得出。

总耗时: 0.011ms 乘以 52310 次 = 约 575ms
总返回行数: 4 行乘以 52310 次 = 约 209,240 行

如果整条查询是 700ms,那么这一个节点就占了 80 个百分点。计划里任何地方都没有写着「575」这个数字,所以不亲手做一次乘法,就会与瓶颈擦肩而过。

出于同样的原因,Nested Loop 内层的节点必须先确认 loops。如果 loops 超过几万次,外层的行数预估很可能是错的。至于是改连接顺序还是加索引,那是下一步的问题。

并行查询还多一层。Gather 下方节点的 loops 反映的是工作进程数,而 Workers Launched 可能比请求的数量少。

Gather  (cost=1000.00..38210.13 rows=210 width=8)
        (actual time=0.412..312.884 rows=198 loops=1)
  Workers Planned: 4
  Workers Launched: 2

计划了 4 个但只启动了 2 个,说明 max_parallel_workers 已经耗尽,速度也就相应地比预期慢。如果基准测试结果无法复现,请先看这一行。

Seq Scan 无罪,以及三种连接的分水岭

「看到 Seq Scan 就建索引」这条建议只对了一半。顺序扫描比索引扫描更快的场景确实存在。

第一,表很小的时候。几百行的代码表整张读完也不过几个页面。走索引则要读索引页,再随机回到堆中访问,反而更亏。

第二,选择性低的时候。如果条件放行了表中相当大的比例,索引就处于劣势。索引扫描要为每个匹配行随机访问堆页面,而当要访问的行很多时,最终等于是以随机顺序读完整张表。顺序读取对磁盘和预取要友好得多。大致超过整体的 5 到 10 个百分点,顺序扫描就开始占优,而准确的分界线由 random_page_cost 和物理有序度决定。

-- 确认物理有序度: 越接近 1.0,索引顺序与堆顺序越一致
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('id', 'created_at', 'user_id');

  attname    | correlation
-------------+-------------
 id          |    0.999812
 created_at  |    0.998304
 user_id     |    0.003911

user_id 的相关度接近 0,意味着同一个用户的订单散落在整张表中。这类列即便选择性相同,索引带来的收益也小得多。优化器早就知道这个值,并把它计入了计算。

连接算法同样不是「哪个更好」的问题,而是「什么时候哪个才对」的问题。

连接方式	被选中的条件	成本特征	计划中值得怀疑的信号
Nested Loop	外层行数少且内层有连接键索引时	外层行数乘以内层查找成本	loops 达数万次以上且内层是 Seq Scan
Hash Join	等值连接且能把较小一侧放进内存哈希表时	把较小一侧整体装入内存	Batches 大于 1 且显示了 Disk 使用量
Merge Join	两侧已按连接键排序,或排序代价很低时	排序成本加上每侧输入各扫一遍	前置的 Sort 退化为 external merge

Hash Join 中如果不是 Batches: 1,就说明哈希表没能全部装进 work_mem,被拆分到磁盘上了。

->  Hash  (actual time=412.331..412.332 rows=1840221 loops=1)
      Buckets: 65536  Batches: 32  Memory Usage: 4097kB

Batches 32 表示分 32 批处理,其间会产生临时文件的读写。这种情况下,把该会话的 work_mem 调高,要比建索引有效得多。

SET LOCAL work_mem = '256MB';

调高全局设置很危险。work_mem 不是按连接分配,而是按查询内的每一次排序或哈希运算分配的,所以并发查询一多,内存瞬间就会翻好几倍。只在重量级批处理查询之前按会话级别调高,才是安全的做法。

BUFFERS 与 Rows Removed by Filter — 剩下的两条线索

没有 BUFFERS 的 EXPLAIN ANALYZE 只是半成品。同一条查询昨天 20ms、今天 900ms,原因通常不在计划而在缓存。

->  Bitmap Heap Scan on events e  (actual time=44.201..811.339 rows=214402 loops=1)
      Recheck Cond: (tenant_id = 77)
      Heap Blocks: exact=41883
      Buffers: shared hit=1204 read=40801

shared hit 是直接在缓冲区缓存中找到的块,shared read 是缓存中没有、只能下沉到操作系统或磁盘去取的块。上面的输出意味着有 4 万个块、约 320MB 是从缓存之外读来的。如果第二次执行时 read 骤减、耗时缩短,那么计划没有问题,问题在于工作集比 shared_buffers 更大。这时再多建索引并不是答案。

dirtied 和 written 也值得留意。如果是 SELECT 却出现很大的 dirtied,那是提示位更新或事后清理正在发生的信号,在大批量导入之后很常见。

第二条线索是 Rows Removed by Filter。我们回到前面的例子。

->  Seq Scan on orders o  (actual time=0.019..92.441 rows=91188 loops=1)
      Filter: (created_at >= '2026-06-01 00:00:00'::timestamp)
      Rows Removed by Filter: 708812

读了 80 万行,扔掉了 71 万行。需要的行只占整体的 11 个百分点。这种程度的选择性,索引完全有取胜的空间。

CREATE INDEX CONCURRENTLY idx_orders_created_at
  ON orders (created_at);

ANALYZE orders;

Hash Join  (cost=1842.00..9714.22 rows=18420 width=44)
           (actual time=7.902..28.113 rows=17903 loops=1)
  Hash Cond: (o.user_id = u.id)
  Buffers: shared hit=6188 read=1204
  ->  Index Scan using idx_orders_created_at on orders o
        (cost=0.43..5901.10 rows=92300 width=20)
        (actual time=0.028..14.882 rows=91188 loops=1)
        Index Cond: (created_at >= '2026-06-01 00:00:00'::timestamp)
  ...
Execution Time: 29.440 ms

从 133ms 降到了 29ms,Rows Removed by Filter 那一行消失了,并变成了 Index Cond。这个差别很重要。Filter 是读进来之后再扔掉,Index Cond 是根本就不去读。如果在计划里发现 Filter 下面挂着很大的数字,请先考察能否把那个条件提升到索引上。

反过来也有这样的情况。

->  Index Scan using idx_orders_user_id on orders o
      (actual time=0.041..38.221 rows=112 loops=1)
      Index Cond: (user_id = 42)
      Filter: (status = 'pending')
      Rows Removed by Filter: 9884

索引把范围收窄到了 1 万行,但其中只剩下 112 行。这种情况的答案是复合索引或部分索引。

CREATE INDEX CONCURRENTLY idx_orders_user_pending
  ON orders (user_id)
  WHERE status = 'pending';

结语 — 看偏差,而不是看节点名称

看执行计划时,只需要记住一个顺序。

先看 Execution Time 和 Planning Time。如果规划时间超过了执行时间,问题在别处。
在每个节点比较预估 rows 和实际 rows。偏差达到一个数量级以上的最内层节点就是元凶。
对 loops 不为 1 的节点做乘法,算出它实际贡献的时间。
找出 Rows Removed by Filter 很大的节点,确认有没有建索引的机会。
用 Buffers 中 read 的比例,判断这究竟是计划问题还是缓存问题。

Seq Scan 或 Nested Loop 这类节点名称本身,并不能告诉你好还是坏。在自己掌握的统计信息范围内,优化器几乎总是做出合理判断。所以当计划看起来奇怪时,该问的不是优化器为什么这么笨,而是我究竟把什么信息错误地告诉了优化器。