SQL EXPLAIN 输出不必看起来像外星文字

你执行了查询 EXPLAIN 在一条慢查询上。数据库返回的内容大致如下：

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000

你盯着它看了十秒钟。你随机添加一个索引。你重启应用程序并祈祷。这不是调试策略——这是带有额外步骤的迷信。

EXPLAIN 实际上在告诉你一些具体且可操作的信息。以下是阅读它的方法。

EXPLAIN 与 EXPLAIN ANALYZE：使用哪一个

清楚的 EXPLAIN 显示了查询规划器 的意图。 它不会执行查询——它只是显示带有估算成本的计划。速度快，但估算可能出错。

EXPLAIN ANALYZE 实际运行查询并添加真实时间。你获得计划和实际数据：

-- PostgreSQL: run the query and show real timings
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

-- MySQL equivalent
EXPLAIN ANALYZE SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

这 BUFFERS 选项是 PostgreSQL 专用的，会添加缓冲区命中/未命中计数——对诊断 I/O 问题有帮助，但现在可以忽略。

一个注意事项： EXPLAIN ANALYZE 实际运行查询。对于一个 DELETE 或 UPDATE，将其包裹在事务中并回滚：

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'pending';
ROLLBACK;

成本数字：它们的含义以及它们不包含的内容

计划中的每个节点都显示 (cost=X..Y rows=N width=W)。人们看到这些数字就认为是毫秒。它们不是。

• cost=X..Y ——X 是启动成本（在返回第一条记录之前完成的工作量），Y 是处理所有行的总成本。单位是任意的“页面成本”——大致与磁盘页面读取成比例，但由 PostgreSQL 的成本常数校准。1.0 的成本相当于一次顺序页面读取。
• rows=N ——规划器的估算行数。如果表统计信息过时，这个数值可能严重错误。
• width=W ——行的平均大小（字节）。行越宽，连接和排序操作越慢。

当你运行 EXPLAIN ANALYZE时，每个节点还会获得 (actual time=X..Y rows=N loops=L)。这些 是 毫秒。估算行数与实际行数之间存在巨大差距，是规划器决策错误最可靠的信号。

带有注释的 EXPLAIN ANALYZE 逐步解析

这里是介绍中的相同查询，这次使用了 ANALYZE ，并逐行解释：

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
           (actual time=2.451..12.879 rows=3168 loops=1)
-- Top-level node. Joins orders and customers via hashing.
-- cost estimate: 145..578 | actual: 2.4ms startup, 12.9ms total
-- rows estimate: 3200 | actual: 3168 — pretty close here

  Hash Cond: (o.customer_id = c.id)
  -- This is the join key. The planner built a hash table on customers.id
  -- then probed it with every row from orders.

  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
                          (actual time=0.019..3.125 rows=16000 loops=1)
  -- Full table scan on orders. 16,000 rows scanned.
  -- No filter here — we pull all orders and join them below.
  -- This is expected: we need all orders, so no index would help here.

  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
            (actual time=2.412..2.413 rows=1000 loops=1)
            Buckets: 1024  Batches: 1  Memory Usage: 64kB
  -- Builds the in-memory hash table from the customers result.
  -- 1 batch = fits in work_mem. Multiple batches = spilling to disk (bad).

        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
                                   (actual time=0.012..1.345 rows=1000 loops=1)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000
        -- Scanned all 10,000 customer rows. Kept 1,000 where country='US'.
        -- *** This is the expensive part. An index on customers.country
        --     would let PostgreSQL skip the 9,000 discarded rows entirely. ***

Planning Time: 0.187 ms
Execution Time: 13.451 ms

行动项立即显现： Rows Removed by Filter: 9000 对全表扫描是“你需要在这里添加索引”的最常见模式。

你实际上会遇到的节点类型

Seq Scan

按堆顺序读取表中的每一行。人们看到这个就立刻想要添加索引。但 Seq Scan 并不总是错误——如果过滤条件匹配超过 20% 的行，规划器会正确判断随机索引查找会更慢。在 500 行的表上进行 Seq Scan 是可以接受的。但在 1000 万行的表上，如果过滤条件高度选择性，则存在严重问题。

Index Scan

使用 B 树索引查找匹配的行，然后从堆中获取每行数据。堆获取是昂贵的部分——每行都是随机 I/O。当过滤条件具有选择性（即你只获取少量行）时，这种操作是高效的。

Index Only Scan

查询可以完全从索引中回答，无需访问堆。这要求你的 SELECT 且 WHERE 子句中的每一列都被索引覆盖。这是最快的扫描类型——如果你看到这个，说明索引设计得当。

Bitmap Heap Scan

一种折中方案。规划器通过 Bitmap Index Scan构建一个包含匹配行的堆页面的位图，然后按顺序只读取这些页面，从而减少随机 I/O。当索引扫描会产生过多随机读取，而全表扫描又会扫描太多不必要的行时，这种情况很常见。

Hash Join

从较小的输入构建哈希表，然后用较大的输入中的每一行去探测哈希表。适用于大型连接且没有有效排序顺序的情况。注意查看哈希节点中的 Batches: > 1 ——这意味着哈希表溢出到磁盘，因为超过了 work_mem。提高该会话的 work_mem 通常可以解决这个问题。

Nested Loop

对每一条外层行，探测内层（通常通过索引）。当一侧非常小的时候非常高效——复杂度为 O(n) 而不是 O(n log n)。当两侧都很大时，效率极差，因为它会为每一条外层行运行一次内层扫描。如果你看到一个 Nested Loop 节点带有 loops=50000，那么内层扫描将被运行 50,000 次。

Merge Join

两个输入都已按连接键排序；规划器并行地遍历它们。高效但需要提前排序。当你发现两个输入都已拥有连接键上的索引，或者规划器认为排序节点比哈希更便宜时，就会看到这种节点。

规划器为何选择 Seq Scan 而非 Index Scan

这一点最容易让人困惑。你有一个索引。查询很慢。EXPLAIN 显示了一个 Seq Scan。

规划器使用列统计信息——存储在 pg_statistic中，由 ANALYZE 更新——来估算过滤条件将返回多少行。如果它估算出 30% 的行将匹配，那么全表扫描实际上比 300,000 次随机索引查找更便宜。随机 I/O 是昂贵的。

规划器优先选择全表扫描的阈值大约是 10–20% 的选择性，具体取决于你的硬件和成本常数。你可以检查规划器的估算与实际值之间的差异：

-- Check the planner's selectivity estimate
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Compare with actual count
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders;

如果规划器估算出 8,000 行，但实际上只有 80 行，那就是统计信息过时的问题。运行 ANALYZE orders; 并重新检查计划。这种方法通常比添加索引更能修复糟糕的计划。

以 JSON 格式阅读 EXPLAIN 输出

PostgreSQL 可以以 JSON 格式输出计划，这更便于程序化解析或在树视图中查看：

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

JSON 输出非常密集。如果你将其粘贴到 IO Tools’ JSON 格式化器中，它会变成一个可导航的树结构——在处理包含多个子查询或 CTE 的复杂查询时，这种结构非常有用。

一个实用的调试工作流程

当查询变慢时，应遵循以下步骤而不是随意猜测：

1. 运行 EXPLAIN (ANALYZE, BUFFERS) 在确切的查询上运行，使用真实生产环境的参数（而不是 $1 占位符——使用实际值）。
2. 找到最昂贵的节点。 寻找最高的 actual time，而不是最高的成本估算。这两个数值可能不同。
3. 比较该节点的估算与实际行数 。如果差异超过 10 倍，说明规划器使用了错误的信息。先运行 ANALYZE <table>; 。
4. 寻找在大表上带有选择性过滤器的 Seq Scan。 Rows Removed by Filter: <large number> 紧接在 Seq Scan 之后的节点是你的索引候选。
5. 检查哈希节点中的 Batches > 1. 。如果存在，说明连接已溢出到磁盘。提高该会话的 work_mem 并重新测试。
6. 检查带有高循环次数的 Nested Loop。 循环次数在千级意味着内层扫描被频繁调用。在内层表的连接列上添加索引通常可以解决这个问题。

在添加任何新索引之前，也请将查询通过 IO Tools’ SQL 格式化器 处理——可读性更强的查询更容易分析，有时重写查询就能彻底消除问题。

理解计划是第一步，而不是添加索引。一旦你能读懂 EXPLAIN 输出，你将花费更少时间猜测，而更多时间进行真正有效的、有针对性的改进。

你可能也会喜欢