使用 EXPLAIN

EXPLAIN 用来查看数据库准备如何执行一条 SQL。它不会直接告诉你“应该建立哪个索引”，而是把优化器选择的扫描方式、连接顺序、预计数据量和成本展示出来，帮助你用证据回答下面的问题：

• 查询从哪些表或索引读取数据？
• 数据在什么时候被过滤、排序、连接或聚合？
• 优化器预计处理多少行，实际又处理了多少行？
• 时间主要花在哪个执行节点？
• 修改 SQL 或索引后，工作量是否真的减少？

本文先讲各数据库通用的阅读方法，再用 PostgreSQL 风格的输出解释细节。MySQL、SQLite、SQL Server 和 Oracle 的命令与字段名称不同，但“从执行计划定位多余工作”的思路相通。

EXPLAIN 与 EXPLAIN ANALYZE

只查看预计计划时，在查询前加上 EXPLAIN：

EXPLAIN
SELECT id, customer_id, total_amount
FROM orders
WHERE customer_id = 42;

普通 EXPLAIN 通常只生成计划，不真正执行查询，因此适合先观察未知 SQL，尤其是写操作。计划中的行数和成本来自统计信息，属于优化器的估算值。

要获得真实耗时、实际行数和循环次数，可以使用执行分析功能。在 PostgreSQL 中写作：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_amount
FROM orders
WHERE customer_id = 42;

ANALYZE 会真正执行语句。它得到的实际数据通常更有诊断价值，但也意味着查询会消耗 CPU、内存、磁盘 I/O，并可能持有锁。

:::warning 先确认语句是否可以安全执行

EXPLAIN ANALYZE UPDATE ...、DELETE ...、INSERT ... 等语句会真的修改数据。即使放入事务后回滚，也可能产生锁、触发器调用、序列值消耗或外部副作用。不要直接对来源不明的语句或生产环境中的重查询使用执行分析。

:::

MySQL 8.0.18 及以上版本提供 EXPLAIN ANALYZE，输出形式与 PostgreSQL 不同；其他数据库可能使用 EXPLAIN QUERY PLAN、图形执行计划或专用命令。具体选项应以目标数据库及版本的官方文档为准。

准备一个示例查询

假设有客户表和订单表：

CREATE TABLE customers (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  total_amount DECIMAL(12, 2) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id)
);

下面的查询查找某位客户最近完成的 20 个订单：

SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'completed'
ORDER BY created_at DESC
FETCH FIRST 20 ROWS ONLY;

若没有合适索引，PostgreSQL 风格的计划可能近似如下：

Limit  (cost=2450.30..2450.35 rows=20 width=32)
  ->  Sort  (cost=2450.30..2452.80 rows=1000 width=32)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0.00..2423.69 rows=1000 width=32)
              Filter: ((customer_id = 42) AND (status = 'completed'))

先不要逐个研究数字。把它当成一棵树，从缩进最深的节点开始向上读：

1. Seq Scan 扫描 orders 表并应用过滤条件；
2. Sort 将符合条件的订单按创建时间倒序排列；
3. Limit 只返回前 20 行。

这份计划揭示了两个潜在工作量：扫描整张表，以及对全部匹配行排序。它们是否真的慢，还要结合实际行数、耗时、缓存命中和查询频率判断。

看懂计划中的核心字段

以 PostgreSQL 的一个节点为例：

Index Scan using idx_orders_customer_created on orders
  (cost=0.43..18.77 rows=20 width=32)
  (actual time=0.041..0.090 rows=20 loops=1)

cost：优化器的相对成本

cost=0.43..18.77 包含两个值：

• 0.43 是产生第一行前的启动成本；
• 18.77 是返回该节点全部结果的总成本。

成本是优化器内部用于比较候选计划的相对单位，不是毫秒，也不能直接换算成耗时。它通常综合估算磁盘读取、CPU 计算、行数等因素。不同服务器、配置或数据库的成本数字不能直接横向比较。

rows：预计输出行数

rows=20 表示优化器预计该节点向父节点输出 20 行，而不是它需要检查的所有行。行数估算会影响扫描方式、连接算法和连接顺序，是阅读计划时最重要的指标之一。

width：预计平均行宽

width=32 表示每行预计占用约 32 字节。行越宽，排序、哈希、缓存和网络传输通常越昂贵。它是估算值，不等同于表中一行的固定物理大小。

actual time：真实执行时间

actual time=0.041..0.090 表示该节点第一次输出行和完成一次执行的大致时间，单位通常为毫秒。它包含子节点的时间，因此不能把所有节点的时间直接相加，否则会重复计算。

执行时间会受到缓存冷热、并发负载、参数、磁盘状态和客户端环境影响。一次很快不等于始终很快，应多次测试并结合监控数据。

loops：执行次数

loops=1 表示该节点执行了一次。若节点位于嵌套循环连接的内层，它可能执行许多次：

Index Scan ... (actual time=0.010..0.012 rows=3 loops=1000)

PostgreSQL 展示的 actual time 和 rows 通常是每次循环的平均值。粗略判断总工作量时，要把它们与 loops 一起看。单次只返回 3 行看似很少，但循环 1000 次就可能处理约 3000 行。

常见执行节点

节点名称因数据库而异，但大致可以分为扫描、连接和数据处理三类。

表扫描

Seq Scan、Table Scan 或 Full Table Scan 表示按表读取数据。看到全表扫描不等于发现问题：

• 表很小时，全表扫描往往最简单；
• 查询需要大部分行时，连续读取可能比大量随机索引访问更快；
• 没有可用索引、条件无法利用索引或估算认为索引不划算时，也会选择全表扫描。

真正需要关注的是：扫描了多少行、过滤掉多少行、执行频率多高，以及它是否位于一个大量循环的内层。

索引扫描

常见索引访问方式包括：

• Index Scan：通过索引定位，再按需读取表数据；
• Index Only Scan：所需数据可直接从索引获得，但某些数据库仍需检查数据可见性；
• Bitmap Index Scan 配合 Bitmap Heap Scan：先收集匹配位置，再批量读取表页，适合返回中等数量的数据；
• Index Seek 或 Range Scan：其他数据库中常见的精确查找或范围读取名称。

索引出现在计划中并不代表计划一定高效。低选择性条件可能读取大量索引项并频繁回表，反而不如顺序扫描。

嵌套循环连接

Nested Loop 的思路是：从外层取一行，再到内层查找匹配行。若外层行数较少，并且内层连接键有合适索引，它通常很高效。

风险在于外层实际行数很大，或内层每次都要扫描大量数据。此时应检查 loops、连接键索引和行数估算，而不是只因看到 Nested Loop 就判断有问题。

哈希连接

Hash Join 通常先读取一侧并建立哈希表，再扫描另一侧进行匹配，常用于较大的等值连接：

SELECT c.name, o.total_amount
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id;

如果哈希表超出可用内存，可能分批处理并使用临时磁盘。哈希连接通常不直接支持非等值条件，例如 <、> 或范围重叠。

合并连接

Merge Join 读取两侧已经按连接键排序的数据并同步合并，适合较大的有序输入。排序可能来自索引，也可能来自额外的 Sort 节点。判断它是否合理时，要把排序成本一起考虑。

排序、聚合与去重

常见节点包括：

• Sort：为 ORDER BY、合并连接、分组或去重排序；
• Aggregate：完成普通聚合；
• HashAggregate：用哈希表完成分组或去重；
• GroupAggregate：利用有序输入逐组聚合；
• Unique：移除重复行；
• WindowAgg：计算窗口函数。

这些节点本身很正常。需要关注的是输入行数、内存是否足够、是否写入临时磁盘，以及能否更早过滤不需要的数据。

Limit

Limit 只限制最终输出，不保证前面的节点只处理相同行数。下面的查询虽然只返回 20 行，但数据库可能先筛选并排序很多行：

SELECT *
FROM orders
WHERE status = 'completed'
ORDER BY created_at DESC
FETCH FIRST 20 ROWS ONLY;

如果索引顺序与筛选、排序相匹配，数据库才可能在得到前 20 行后尽早停止。

过滤条件要看在哪里发生

计划中的条件可能以不同形式出现：

• Index Cond：用于缩小索引访问范围；
• Filter：读取候选行后再判断；
• Join Filter：连接过程中应用的附加条件；
• Recheck Cond：某些位图或近似索引访问后再次核对。

例如：

Index Scan using idx_orders_customer on orders
  Index Cond: (customer_id = 42)
  Filter: (status = 'completed')
  Rows Removed by Filter: 980

这里索引只帮助定位 customer_id，status 在读取数据后才过滤。大量 Rows Removed by Filter 表明数据库做了许多最终无用的读取，但是否需要复合索引还要结合查询频率、数据分布和写入成本判断。

对于示例查询，可以评估下面的索引：

CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

它可能同时支持两个等值条件和时间排序，让数据库按索引顺序取得前 20 行。不过索引会占用空间并增加写入成本，最终仍应以修改前后的实际计划和业务负载为准。

重点比较预计行数与实际行数

执行分析最有价值的用途之一，是发现优化器估算偏差：

Seq Scan on orders
  (cost=0.00..2200.00 rows=10 width=32)
  (actual time=0.030..85.000 rows=50000 loops=1)

优化器预计 10 行，实际却有 50000 行。这个偏差可能使它选择不合适的连接顺序、嵌套循环或内存策略。

常见原因包括：

• 表统计信息陈旧或采样不足；
• 某个值特别常见，数据分布并不均匀；
• 多个列彼此相关，但优化器按独立条件估算；
• 表达式、函数或复杂条件缺少可用统计信息；
• 参数化查询针对某类参数生成的计划不适合另一类参数；
• 中间结果、临时表或新导入的数据尚未被准确统计。

此时应优先更新或改进统计信息，并检查数据分布。不要一看到估算错误就强制指定索引；错误估算可能还会影响计划中的其他节点。

使用 BUFFERS 判断数据读取

在 PostgreSQL 中，EXPLAIN (ANALYZE, BUFFERS) 可以显示缓冲区使用情况，例如：

Buffers: shared hit=120 read=8, temp read=64 written=64

可以这样理解：

• shared hit：数据页已在数据库共享缓冲区中；
• shared read：数据库需要把数据页读入共享缓冲区；
• temp read/written：排序、哈希等操作使用了临时文件。

缓冲区命中不代表“没有成本”，因为访问和处理内存中的大量数据页仍会消耗 CPU。read 也不一定等同于物理磁盘读取，操作系统缓存可能已经保存了数据。它的主要价值是比较查询处理了多少数据页，以及优化前后读取量是否下降。

若数据库支持类似的 I/O、内存或临时空间统计，也应结合对应指标分析。

一套高效的阅读顺序

面对很长的计划时，可以按以下顺序检查：

1. 确认结果和条件。 先保证 SQL 语义正确，参数具有代表性。
2. 查看总耗时与返回行数。 判断问题是否能稳定复现，不把偶发等待误认为计划问题。
3. 从最深层向上读。 理清数据从扫描到连接、聚合、排序和输出的过程。
4. 寻找高工作量节点。 关注耗时、实际行数、循环次数、读取页和临时文件，而非只找某个节点名称。
5. 比较预计值与实际值。 大幅偏差通常提示统计信息或数据分布问题。
6. 检查过滤发生的位置。 看是否读取很多行后才过滤，以及条件是否能成为索引访问条件。
7. 一次只改一个主要因素。 修改 SQL、索引或统计信息后重新执行，保留前后计划与指标。
8. 评估整体代价。 查询更快后，还要检查索引空间、写入性能、锁和其他查询是否受影响。

常见性能信号

下面的现象值得调查，但都不能脱离上下文直接判定为错误。

现象	可能的问题	下一步检查
大表全表扫描且只返回少量行	缺少合适索引，或条件无法利用索引	过滤条件、类型转换、函数、索引列顺序
Rows Removed by Filter 很大	读取许多最终无用的行	过滤能否提前，是否需要复合或表达式索引
预计行数与实际行数相差很大	统计信息或数据分布估算失真	更新统计信息，检查倾斜与列相关性
内层节点的 loops 很大	重复执行放大了少量单次成本	连接方式、连接顺序、内层索引
排序或哈希使用临时磁盘	输入过大或可用内存不足	先过滤、索引顺序、内存配置与并发量
返回少量行却读取大量数据页	访问路径选择性差或回表很多	覆盖索引、复合索引、返回列与条件
规划时间远大于执行时间	查询结构复杂或计划生成开销高	查询频率、分区数量、可否简化或复用计划

常见误区

看到全表扫描就建索引

小表或返回大部分数据时，全表扫描可能是正确选择。索引也不能免费获得：它会占用存储，并拖慢插入、更新和删除。

只看总耗时

耗时能说明“这次慢”，但不能单独说明“为什么慢”。同时查看行数、循环、过滤量和缓冲区，才能区分扫描过多、估算错误、临时磁盘或缓存差异。

把成本当成毫秒

cost 是用于计划选择的相对估算值。实际毫秒只会在真正执行后得到，而且受运行环境影响。

认为索引被使用就完成优化

一次索引扫描仍可能读取大量索引项、反复回表或执行很多循环。优化目标是减少整体工作量，不是让计划中出现 Index。

用不同参数只测一次

状态、租户、时间范围等参数可能对应完全不同的数据量。同一 SQL 对“热门值”和“冷门值”可能需要不同计划，应选择具有代表性的多组参数测试。

用开发环境的小数据推断生产表现

小表上的扫描、连接和排序选择可能与生产环境完全不同。测试数据应尽量接近真实规模和分布，同时避免复制不必要的敏感数据。

安全分析写操作

先用普通 EXPLAIN 查看预计计划：

EXPLAIN
UPDATE orders
SET status = 'archived'
WHERE created_at < TIMESTAMP '2024-01-01';

如果确实需要实际执行数据，应优先在隔离的测试环境中完成。事务加回滚可用于某些没有外部副作用的场景：

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'archived'
WHERE created_at < TIMESTAMP '2024-01-01';

ROLLBACK;

这并非绝对安全：语句仍会执行、加锁并消耗资源，触发器可能访问外部系统，序列值通常也不会因回滚而恢复。执行前必须了解目标数据库、表、触发器和事务行为。

不同数据库的常用入口

数据库	常用方式	说明
PostgreSQL	EXPLAIN、EXPLAIN (ANALYZE, BUFFERS)	文本、JSON 等格式可选；ANALYZE 会执行语句
MySQL	EXPLAIN、EXPLAIN FORMAT=JSON、EXPLAIN ANALYZE	EXPLAIN ANALYZE 需要支持该功能的版本并会执行语句
SQLite	EXPLAIN QUERY PLAN	输出较简洁，常用于查看扫描与索引选择
SQL Server	估计执行计划、实际执行计划	常通过管理工具查看图形计划，实际计划会执行查询
Oracle	EXPLAIN PLAN、运行时统计工具	需结合目标版本和工具读取计划及实际统计

不要机械比较不同数据库的节点名称或成本数字。先掌握目标数据库的输出字段，再应用本文的核心方法：沿数据流阅读、对比预计与实际、寻找被放大的工作量。

优化前后的验证清单

每次调整 SQL 或索引后，至少回答这些问题：

1. 查询结果是否与调整前一致？
2. 测试参数和数据分布是否具有代表性？
3. 实际总耗时是否稳定下降，而不是只快了一次？
4. 扫描行数、过滤行数、循环次数或读取页是否减少？
5. 预计行数是否更接近实际行数？
6. 是否减少了不必要的排序、回表或临时磁盘使用？
7. 新索引是否增加了不可接受的写入和存储成本？
8. 并发、锁等待和其他关键查询是否受到影响？

EXPLAIN 的核心不是记住所有节点，而是建立一条清晰的证据链：数据库如何取得数据，实际做了多少工作，最主要的工作为什么发生，以及调整后是否真正减少了它。