数据库基础体系 · 第 6/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQL 执行计划与查询优化：基数估计、Join 算法和慢查询诊断

SQL 查询慢，通常不是因为某一条语句“语法写得不够巧”，而是数据库在执行时选择了不合适的访问路径、连接算法、并行策略或执行时机。要系统地分析这类问题，需要把一条 SQL 拆成几个连续阶段：

解析 SQL 并绑定表、列、函数和类型；
生成等价的候选关系表达式；
根据统计信息估算每个中间结果的行数，即基数（cardinality）；
估算不同执行路径的成本；
选择扫描、索引、Join、排序、聚合和并行方案；
执行计划，并在运行过程中受到锁、I/O、内存、并发和缓存状态影响。

执行计划不是 SQL 的“解释文本”，而是数据库准备执行的物理操作树。查询优化的核心，也不是“看到全表扫描就改成索引”，而是判断：

优化器对数据规模和分布的估计是否可靠；
选择的访问路径是否适合实际选择性；
Join 的输入规模和算法是否匹配；
计划成本低但实际等待很久的原因是否来自执行器之外；
修复措施是否会在写入、并发、计划稳定性和运维复杂度上引入新的代价。

本文的示例明确区分 PostgreSQL 和 MySQL。SQL 的关系语义由数据库产品的实现和配置共同决定；执行计划格式、统计信息、锁行为和可用算法不能简单混用。

一、从 SQL 到执行计划：逻辑操作与物理操作

1. 逻辑计划和物理计划

下面的 SQL 描述了一个逻辑需求：

SELECT o.id, o.created_at, c.name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'PAID'
  AND o.created_at >= TIMESTAMP '2025-01-01';

逻辑上，它包含：

从 orders 中筛选状态和时间；
从 customers 中取出与订单匹配的客户；
按 customer_id = id 连接；
投影需要的列。

但数据库必须将它转换为物理操作。例如，PostgreSQL 可能选择：

Hash Join
├── Bitmap Heap Scan on orders
│   └── Bitmap Index Scan on orders_status_created_idx
└── Hash
    └── Seq Scan on customers

也可能选择：

Nested Loop
├── Index Scan on orders_status_created_idx
└── Index Scan on customers_pkey

这两棵树逻辑上可以返回同样的结果，但执行过程完全不同：

Hash Join 先建立哈希表，再扫描另一侧；
Nested Loop 对外层的每一行，执行一次内层查找；
如果外层有几十行，内层索引查找很适合；
如果外层有几百万行，重复查找可能远差于一次顺序扫描和哈希连接。

因此，执行计划应被理解为“关系运算的具体实现”，而不是 SQL 文本的另一种写法。

2. 计划节点中的关键数字

以 PostgreSQL 的计划为例：

Index Scan using orders_customer_id_idx on orders
  (cost=0.42..120.00 rows=100 width=48)
  (actual time=0.030..0.900 rows=980 loops=1)

这些字段含义不同：

cost=0.42..120.00：优化器估算的启动成本和总成本，不是毫秒；
rows=100：估计该节点输出 100 行；
width=48：估计每行平均宽度约为 48 字节；
actual time=0.030..0.900：执行时首行和最后一行的实际时间，单位通常是毫秒；
actual rows=980：该节点每次循环实际输出的行数；
loops=1：该节点被执行了一次。

若 loops=20、actual rows=100，则该节点累计输出约为：

100 × 20 = 2000 行

不要只拿 actual rows 与 rows 直接比较而忽略 loops。对于被 Nested Loop 反复调用的内层节点，循环次数本身就是性能线索。

MySQL 的 EXPLAIN 和 EXPLAIN ANALYZE 输出格式不同。传统 EXPLAIN 常见字段包括：

type：访问类型，例如 const、eq_ref、ref、range、index、ALL；
possible_keys：优化器认为可能使用的索引；
key：最终选择的索引；
rows：估算需要检查的行数；
filtered：经过条件过滤的估计百分比；
Extra：例如 Using index、Using temporary、Using filesort。

MySQL 8.0.18 及更高版本提供 EXPLAIN ANALYZE，用于展示实际执行过程；其具体输出格式应以对应版本为准。EXPLAIN 的 rows 是估计值，不能当作实际读取行数。

二、基数估计：优化器为何会选错计划

1. 基数、选择率和过滤条件

设关系 $R$ 的行数为 $|R|$ 。对谓词 $p$ ，优化器需要估计：

| \sigma_p(R) |

其中：

$\sigma_p(R)$ 表示对 $R$ 应用谓词 $p$ 后的结果；
$|R|$ 表示关系中的行数；
选择率（selectivity）定义为：

s(p) = \frac{|\sigma_p(R)|}{|R|}

于是：

|\sigma_p(R)| \approx |R| \times s(p)

例如，表中有 1,000,000 行，优化器估计 status = 'PAID' 的选择率为 0.1，那么估计结果就是：

1{,}000{,}000 \times 0.1 = 100{,}000

如果实际有 700,000 行，误差达到 7 倍。单个节点的误差可能被后续 Join 继续放大。

2. 统计信息如何支持估计

数据库不会每次执行查询都扫描整张表来计算真实分布，而是维护统计信息。常见信息包括：

总行数；
列中不同值的数量；
最常见值及其频率；
直方图；
NULL 比例；
多列统计或扩展统计；
索引的键分布和相关性估计。

以等值谓词为例，若某个值出现在最常见值列表中，优化器可以使用该值的真实频率；如果不在列表中，可能退回到对“普通值”的估计。

这解释了一个常见现象：同一列上的两个值，执行计划可能完全不同。一个值很稀疏，适合索引；另一个值占据大多数行，顺序扫描可能更便宜。

3. 多个条件不能总是简单相乘

对两个条件：

WHERE status = 'PAID'
  AND region = 'CN'

简单模型通常近似：

s(status = 'PAID' \land region = 'CN') \approx s(status = 'PAID') \times s(region = 'CN')

这个近似隐含了两个条件相互独立。

假设：

PAID 占 50%；
CN 占 20%；
独立模型估计交集为 $50\% \times 20\% = 10\%$ 。

但实际业务中，订单区域与支付状态可能有关：

中国区订单更容易成功；
某区域正在发生支付故障；
某个租户的订单全部使用特定状态。

如果真实交集是 30%，估计就会低估 3 倍。反过来，如果两者几乎互斥，也会严重高估。

PostgreSQL 可以通过扩展统计信息描述某些多列关系，例如依赖关系、组合最常见值和多列基数。示例：

CREATE STATISTICS orders_status_region_stat
    (dependencies, mcv, ndistinct)
    ON status, region
    FROM orders;

ANALYZE orders;

这不是“给查询加速的索引”。它改善的是优化器的估计，是否改变计划取决于估计误差和候选计划成本。统计对象也有维护成本，并不是任意列都应该创建。

MySQL 的统计信息、直方图和优化器行为应按照 MySQL 8.4 文档和实际版本验证；不能把 PostgreSQL 的 CREATE STATISTICS 语法迁移到 MySQL。

4. Join 基数的推导

考虑等值连接：

R JOIN S ON R.k = S.k

如果只知道：

$N_R = |R|$
$N_S = |S|$
$V(R.k)$ ：R.k 的不同值数量
$V(S.k)$ ：S.k 的不同值数量

常见的近似是：

|R \bowtie S| \approx \frac{N_R \times N_S} {\max(V(R.k), V(S.k))}

直觉是：两边的键值分布被简化为近似均匀，较大的一方不同值数量决定匹配密度。

例如：

orders 有 1,000,000 行；
customers 有 100,000 行；
orders.customer_id 有 100,000 个不同值；
customers.id 有 100,000 个不同值。

则：

|orders \bowtie customers| \approx \frac{1{,}000{,}000 \times 100{,}000}{100{,}000} = 1{,}000{,}000

这与“每个订单最多对应一个客户”的主键-外键关系相符。

但如果 orders.customer_id 实际上只有 10,000 个值，且大量订单集中在少数客户，均匀分布假设就不成立。某些热点客户可能产生大量匹配，导致：

Hash Join 的哈希桶分布不均；
Nested Loop 的内层调用次数或返回行数远超估计；
排序、聚合或上层 Join 的输入规模被放大。

5. Join 顺序为何重要

对三个表：

A JOIN B ON ...
  JOIN C ON ...

不同的结合顺序可能产生完全不同的中间结果：

(A JOIN B) JOIN C
A JOIN (B JOIN C)

如果 A JOIN B 先筛掉绝大多数行，通常有利；如果它产生巨大中间结果，再与 C 连接，就可能消耗大量 CPU、内存和临时磁盘空间。

优化器会在候选 Join 顺序中比较估计成本。表数量增加后，可能需要限制搜索空间或使用启发式策略，因此“理论上最优”的计划不一定被完整搜索到。复杂查询、视图展开、子查询改写和 CTE 物化行为也会影响候选空间。

三、成本模型：低成本不等于低延迟

优化器通常比较一个抽象成本，而不是直接预测用户看到的毫秒数。一个简化的总成本可以写成：

C = C_{\text{startup}} + C_{\text{cpu}} + C_{\text{random I/O}} + C_{\text{sequential I/O}} + C_{\text{memory}} + C_{\text{parallel}}

实际产品会使用更具体的参数和内部模型。例如：

顺序读取页的成本；
随机读取页的成本；
处理每行、每个表达式和每个索引元组的 CPU 成本；
并行启动和进程间通信成本；
排序、哈希表超出内存后的临时文件成本。

因此有几个重要边界：

cost=100 不是 100 毫秒；
成本参数通常是相对模型，不一定反映当前存储设备；
缓存命中率、操作系统缓存、数据冷热分布可能改变实际时间；
并发下的锁等待、CPU 排队和 I/O 队列等待通常不等价于计划成本；
计划选择只在候选计划中比较，若基数估计错误，成本比较也会失去基础。

一个全表扫描不是错误。若表只有几百页，或者谓词会命中表中大部分行，顺序扫描可能比随机访问索引后再回表更便宜。反例是：为了消除所有 Seq Scan 而建立索引，结果让写入和 VACUUM/统计维护成本上升，却没有降低查询延迟。

四、主要扫描方式与索引的真实作用

1. 顺序扫描

顺序扫描按物理页顺序读取表，并检查每一行是否满足条件。它适合：

表很小；
谓词选择性低；
需要读取大量列或大量行；
顺序 I/O 明显便宜于大量随机访问。

2. 索引扫描

索引扫描先定位索引条目，再访问表行。它适合返回少量行，尤其是：

WHERE id = ?

或高度选择性的范围查询。

但索引扫描不是“免费跳转”：

读取索引页；
找到一个或多个索引条目；
根据定位信息访问表数据页；
检查剩余谓词；
可能发生大量随机页访问。

当命中行很多时，访问大量分散的数据页可能比顺序扫描更慢。

3. 覆盖索引与索引条件下推

如果查询需要的列都能从索引中获得，数据库可能避免回表。不同产品和访问路径对“覆盖”的具体实现不同：

PostgreSQL 可能使用 Index Only Scan，但能否完全避免堆访问还受到可见性映射和 MVCC 状态影响；
MySQL 的 InnoDB 二级索引叶子节点包含主键值，若查询列不在二级索引中，通常还需要回到聚簇索引读取记录；EXPLAIN 中的 Using index 表示覆盖索引语义，具体是否需要额外过滤仍需看计划。

因此，索引设计必须结合：

查询谓词；
Join 键；
排序和分组；
返回列；
写入频率；
索引页大小和缓存；
更新索引列的代价。

联合索引的列顺序也不是“把所有条件都放进去”这么简单。等值条件、范围条件、排序需求和选择性共同决定可利用的索引前缀。更宽的覆盖索引可以减少回表，却会增加写放大、缓存压力和维护成本。

五、Join 算法：输入、状态和适用条件

1. Nested Loop Join

Nested Loop 的基本形式是：

for each row r in outer:
    for each row s in inner:
        if join_condition(r, s):
            emit(r, s)

如果内层有索引，实际常见形式是：

for each row r in outer:
    use inner index to find matching s

设外层输出 $N_o$ 行，每次内层查找成本为 $C_i$ ，则简化成本约为：

C \approx C_{\text{outer}} + N_o \times C_i

适合的情况

外层很小；
内层连接键有高效索引；
查询需要尽快返回前几行；
连接条件不是适合哈希的等值条件；
内层查找具有良好缓存局部性。

失败表现

优化器估计外层只有 100 行，实际却有 1,000,000 行：

Nested Loop
├── outer: estimated rows=100, actual rows=1000000
└── inner index lookup: loops=1000000

即使单次索引查找很快，百万次调用也会导致大量随机访问、CPU 消耗和锁/缓存竞争。

一个反例

SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'PAID';

若 status = 'PAID' 实际返回订单表的 80%，让每个订单逐次查客户主键，可能不如扫描客户表建立哈希表后一次性连接。此时“客户主键有索引”并不能证明 Nested Loop 是好计划。

2. Hash Join

Hash Join 主要用于等值连接：

build:
    读取较小输入
    用连接键建立哈希表

probe:
    读取另一输入
    用连接键查找哈希桶
    输出匹配行

若 Build 输入有 $B$ 行，Probe 输入有 $P$ 行，理想情况下成本近似：

C \approx O(B + P)

实际还包括哈希函数、冲突、内存分配和输出成本。

内存边界

哈希表需要占用工作内存。若不能放入内存，数据库可能将数据分区并写入临时文件，再逐分区处理。结果可能表现为：

临时文件大量增长；
I/O 突增；
计划显示的成本较低，但运行时间很长；
并发查询之间争用内存和磁盘。

PostgreSQL 中，work_mem 是每个操作、每个查询节点在特定执行路径中的工作内存上限语义，不应简单理解为“每个连接只使用这么多内存”。一个查询可能同时有多个排序或哈希节点，并且并行执行会增加总体消耗。MySQL 的相关内存和临时表行为使用不同参数体系，不能直接套用 PostgreSQL 参数。

适合的情况

等值连接；
两边输入规模较大；
Build 一侧可以接受哈希内存或分批落盘；
不需要依靠连接键顺序输出结果。

限制

对范围连接如 a.x < b.x 不适用普通等值 Hash Join；
需要处理 NULL 语义，NULL 不等于 NULL；
内存不足时可能发生批处理和临时 I/O；
Build 侧选择错误会使内存压力显著增大。

PostgreSQL 支持 Hash Join。MySQL 8.0.18 起支持 Hash Join，并在后续版本持续改进，具体是否选择以及执行计划显示方式取决于版本、统计信息和优化器成本判断。

3. Merge Join

Merge Join 要求两侧都按连接键有序，然后像合并两个有序数组一样推进：

left:  1, 2, 2, 5, 9
right: 2, 2, 3, 5, 8

比较当前键：
1 < 2，左侧前进
2 = 2，输出匹配组合
2 = 2，继续处理重复键
2 < 3，左侧前进
5 = 5，输出匹配
9 > 8，右侧前进

若两边已经有序，成本接近：

O(N + M)

若需要先排序，则总成本还要加上：

O(N\log N + M\log M)

适合的情况

连接键已有索引顺序；
输入本来就由上游排序产生；
连接结果需要按同一键顺序；
两边数据量较大，排序成本可接受；
查询条件包含范围连接或其他不适合哈希的情形。

重复键的代价

当两边同一连接键都有大量重复值时，匹配数本身可能是乘积。例如左侧某键有 10,000 行，右侧同键有 20,000 行，结果仅该键就有：

10{,}000 \times 20{,}000 = 200{,}000{,}000

此时无论 Join 算法多高效，输出本身就很大。把这种问题归因于“没有用对索引”是不准确的。

4. 算法不是可以任意互换的黑盒

Join 算法必须保持 SQL 语义：

Inner Join 可以在满足条件时重排连接顺序；
Outer Join 的重排受到保留行语义约束；
含有非确定性函数、易产生副作用的函数、窗口函数、聚合和 LIMIT 的查询块可能限制改写；
NULL 的三值逻辑会影响谓词等价性；
排序、重复行和集合运算会限制某些变换。

数据库优化器不是简单地“尝试所有代码写法”，而是在语义等价的范围内搜索物理实现。

六、一个可运行的 PostgreSQL 计划实验

以下示例针对 PostgreSQL，建议在测试数据库中执行。它会创建并填充一个小型订单表：

DROP TABLE IF EXISTS orders_demo;
DROP TABLE IF EXISTS customers_demo;

CREATE TABLE customers_demo (
    id   bigint PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE orders_demo (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    status      text NOT NULL,
    created_at  timestamptz NOT NULL
);

INSERT INTO customers_demo (id, name)
SELECT g, 'customer-' || g
FROM generate_series(1, 10000) AS g;

INSERT INTO orders_demo (id, customer_id, status, created_at)
SELECT
    g,
    ((g - 1) % 10000) + 1,
    CASE
        WHEN g % 10 < 8 THEN 'PAID'
        ELSE 'CANCELLED'
    END,
    TIMESTAMPTZ '2025-01-01 00:00:00+00'
        + (g % 365) * INTERVAL '1 day'
FROM generate_series(1, 1000000) AS g;

CREATE INDEX orders_demo_status_created_idx
    ON orders_demo (status, created_at);

CREATE INDEX orders_demo_customer_id_idx
    ON orders_demo (customer_id);

ANALYZE customers_demo;
ANALYZE orders_demo;

查询：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.id, o.created_at, c.name
FROM orders_demo AS o
JOIN customers_demo AS c
  ON c.id = o.customer_id
WHERE o.status = 'PAID'
  AND o.created_at >= TIMESTAMPTZ '2025-06-01 00:00:00+00';

1. 如何阅读这个计划

重点观察三组关系：

估计行数与实际行数

rows=...
actual ... rows=...

如果某节点估计 10,000 行、实际 400,000 行，先不要急着改 Join 算法。要继续问：

统计信息是否在数据加载后更新；
status 与 created_at 是否存在相关性；
时间条件是否命中了数据分布中的热点区间；
是否存在参数化查询导致通用计划；
上层节点是否把误差再次放大。

`loops`

如果一个内层索引节点：

actual rows=1 loops=500000

说明它被调用了 500,000 次。单次耗时很小，不代表总成本小。

`BUFFERS`

在 PostgreSQL 中，BUFFERS 可以帮助区分缓存命中和实际读盘，例如：

shared hit：从共享缓冲区命中；
shared read：需要读取数据页；
temp read / temp written：使用了临时文件。

它不能单独说明“磁盘慢”或“内存不够”，但能把计划节点与 I/O 行为联系起来。

2. `ANALYZE` 的边界

EXPLAIN ANALYZE 会真正执行查询。对于 SELECT，通常只读数据；但对 INSERT、UPDATE、DELETE，它会执行写入和触发器等副作用。

测试写操作时，应在明确的事务边界内：

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders_demo
SET status = 'PAID'
WHERE id <= 10;

ROLLBACK;

这只能回滚事务本身的数据库变化；外部副作用、发送通知、调用外部函数或触发器中的非事务性行为不一定能够回滚。因此不能把“执行完再回滚”当成对所有生产写操作都安全。

还要注意，EXPLAIN ANALYZE 的耗时包括计划节点实际运行时间，但在生产中执行它会真实消耗 CPU、I/O、锁和连接资源。对于极慢或高并发语句，应该优先在可复现的测试环境、只读副本或低风险窗口验证。

七、统计信息失真：慢计划最常见的根因之一

1. 数据变化后统计信息没有跟上

统计信息不是实时计数器。表经过大量插入、删除或更新后，优化器可能仍依据旧分布估计。

PostgreSQL 可以手动更新：

ANALYZE orders_demo;

MySQL 可以根据版本和表引擎使用相应的统计信息更新机制，例如：

ANALYZE TABLE orders_demo;

两者语义和副作用不同，不能把一条命令作为跨产品通用修复。更新统计信息后必须重新查看执行计划，确认：

估计行数是否更接近实际；
Join 顺序是否变化；
是否引入了更大的排序或哈希；
计划变化是否只在测试数据上成立。

2. 数据倾斜和最常见值

假设 tenant_id 有 10,000 个租户，但一个租户拥有 70% 的数据。按平均值估计某租户只占 0.01%，会使优化器偏向索引和 Nested Loop；实际返回大量数据时，顺序扫描或 Hash Join 可能更好。

这类问题不能仅靠增加一个普通索引解决。需要确认：

统计信息是否能表达热点值；
查询是否带有租户条件；
不同租户是否应该使用不同计划；
分区或物理隔离是否更符合数据分布；
是否存在参数敏感的计划选择。

3. 相关列

以下谓词具有明显业务相关性：

WHERE country = 'CN'
  AND timezone = 'Asia/Shanghai'

如果所有中国用户都使用该时区，独立性假设会低估或高估交集。多列统计有助于估计，但它不等同于执行时动态采样，也不保证覆盖所有组合。

4. 表达式、隐式类型转换和函数

下面的查询可能阻碍索引利用或统计信息使用：

WHERE DATE(created_at) = DATE '2025-06-01'

更适合改写为半开区间：

WHERE created_at >= TIMESTAMP '2025-06-01 00:00:00'
  AND created_at <  TIMESTAMP '2025-06-02 00:00:00'

这不仅可能使范围索引更容易使用，也明确表达了时间边界。使用半开区间的原因是避免“当天最后一秒”的精度假设，并正确处理时间戳的小数精度。

但改写必须考虑时区语义。timestamp、timestamptz 以及 MySQL 的 DATETIME、TIMESTAMP 存储和转换规则不同，不能只从索引角度改写而忽略业务时间含义。

隐式类型转换也可能造成问题：

WHERE customer_id = '123'

如果 customer_id 是数值列，数据库可能进行类型转换；转换发生在常量一侧通常较容易利用索引，但具体行为依赖产品、类型和表达式。应在实际版本中用执行计划验证，而不是凭 SQL 外观判断。

八、慢查询诊断：先区分执行慢和等待慢

“查询耗时 10 秒”只描述了用户感受到的时间，不说明这 10 秒花在哪里。通常至少要区分：

T_{\text{总}} = T_{\text{排队}} + T_{\text{锁等待}} + T_{\text{执行}} + T_{\text{结果传输}}

其中：

排队：连接池没有空闲连接，或者数据库线程/CPU/I/O 资源拥塞；
锁等待：等待其他事务释放锁；
执行：扫描、Join、排序、聚合、函数计算；
结果传输：结果集很大、客户端读取慢或网络拥塞。

1. 计划成本低但仍然很慢

常见原因：

等待行锁或表锁；
I/O 队列拥堵；
CPU 被其他查询占满；
连接池排队；
结果集传输过大；
数据库缓存冷启动；
执行计划使用了错误的参数化路径；
临时文件落盘；
并行 worker 或线程资源不足。

因此，看到一个不复杂的计划，不代表数据库内部没有等待。需要结合：

当前活动会话；
锁等待关系；
事务开始时间；
CPU、磁盘延迟和 I/O 队列；
数据库日志中的查询时间、锁等待时间；
应用连接池的获取连接耗时。

PostgreSQL 可以通过 pg_stat_activity、锁相关视图和日志配置观察活动与等待；MySQL 可以通过 performance_schema、sys 库、进程列表和 InnoDB 锁信息观察。视图字段和开关应按产品版本确认。

2. 计划实际执行慢

这时优先比较估计与实际：

估计  rows=100
实际  rows=500000

这是基数问题的强信号。

如果估计和实际都很大，则可能是：

查询确实需要处理大量数据；
返回列过多；
谓词选择性不足；
Join 产生了乘法膨胀；
排序或聚合需要大量内存；
计划虽然合理，但硬件或并发负载不足。

如果只有某个节点特别慢：

扫描节点慢：关注页读取、可见性、表膨胀、缓存和选择性；
Hash 节点慢：关注哈希表大小、批处理和临时 I/O；
Sort 节点慢：关注排序输入行数和是否落盘；
Nested Loop 内层慢：关注 loops 和每次查找成本；
Aggregate 慢：关注分组基数、输入规模和内存；
返回阶段慢：关注结果集大小和客户端消费速度。

3. 不要把应用测得的时间直接等同于计划时间

应用代码通常测量：

获取连接
+ 发送 SQL
+ 数据库排队
+ 执行
+ 网络传输
+ ORM 映射
+ 业务处理

而 EXPLAIN ANALYZE 主要帮助观察数据库执行节点。两者不一致是正常的。应分别记录：

从连接池申请连接的等待时间；
数据库服务端执行时间；
锁等待时间；
首字节返回时间；
完整结果读取时间；
应用端反序列化和映射时间。

如果连接池配置过小，数据库本身可能并不忙，但应用请求会在池中排队。反过来，盲目增加连接数可能让数据库进入 CPU、内存或锁竞争状态，导致所有查询变慢。

九、用 `EXPLAIN` 诊断时的完整步骤

第一步：固定复现条件

记录：

数据库产品和精确版本；
SQL 文本；
参数值；
当前事务隔离级别；
是否在读写事务中；
表大小和索引；
并发负载；
缓存冷热状态；
是否在主库、只读副本或分区节点上执行。

同一条参数化 SQL 在不同参数下可能返回完全不同的数据量，因此只记录 SQL 模板往往不够。

第二步：先看估计计划

PostgreSQL：

EXPLAIN
SELECT ...

MySQL：

EXPLAIN
SELECT ...;

先不执行查询的原因是：

生产环境风险较低；
可以观察优化器选择；
可以确认是否使用了预期索引；
不会因为诊断动作本身增加大量负载。

第三步：在安全边界内获取实际计划

PostgreSQL：

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT ...;

其中 SETTINGS 可帮助识别影响计划的非默认设置，但输出和可用选项以版本为准。

MySQL 8.0.18+：

EXPLAIN ANALYZE
SELECT ...;

对生产高负载查询，不应无条件直接执行。若语句可能返回大量行，EXPLAIN ANALYZE 也会承担完整执行代价。

第四步：定位第一个显著误差

从计划底部向上看，寻找估计与实际差距最大的节点。例如：

Index Scan
estimated rows=50
actual rows=200000

这个误差比上层 Hash Join 显示的总耗时更接近根因。上层只是承受了错误输入规模。

第五步：判断修复类别

不同现象对应不同修复方向：

现象	可能原因	先验证什么
估计行数远小于实际	统计过期、数据倾斜、列相关	更新统计并比较计划
估计行数远大于实际	选择率模型错误、谓词相关	检查分布与多列关系
Nested Loop 内层 loops 极大	外层估计过小或索引查找不划算	查看外层实际行数
Hash/Sort 临时 I/O 多	工作内存不足或输入膨胀	查看临时读写和中间结果
索引存在但未使用	选择性低、回表贵、表达式不可用	比较顺序扫描与索引路径成本
计划执行很快但请求很慢	锁、连接池、网络、客户端消费	分解端到端耗时
同 SQL 不同参数差异大	数据分布倾斜、参数计划不合适	分别查看参数下的实际计划

十、常见错误修复及其反例

1. 看到全表扫描就强制索引

错误逻辑：

全表扫描 = 慢
使用索引 = 快
所以必须强制索引

反例：表有 100 万行，查询需要返回 80 万行并读取多个非索引列。索引路径可能需要大量随机回表，而顺序扫描只需按页读取整表。

正确问题是：

实际需要返回多少行；
数据是否集中在少数页；
索引是否覆盖；
顺序扫描和索引回表的成本谁更低；
并发下缓存和 I/O 形态如何变化。

2. 看到缺索引就建立宽索引

宽索引可能减少回表，却增加：

插入和更新时的索引维护；
索引占用空间；
缓存压力；
统计信息和重建成本；
写放大；
复制或日志传输负担。

应先确认该查询是稳定高频路径，且索引确实改变了关键节点。索引不是对查询文本逐词覆盖，而是服务于访问路径。

3. 只看总耗时，不看中间结果

一个 Join 查询慢，可能不是最后的 Join，而是前面的过滤没有生效，导致：

扫描 10 万行
过滤后 9 万行
再与另一表连接
排序 9 万行

真正的问题可能是谓词选择性低、统计错误或数据模型不适合，而不是 Join 算法本身。

4. 用 `LIMIT` 掩盖大查询

SELECT ...
FROM ...
ORDER BY created_at DESC
LIMIT 20;

如果没有支持过滤和排序的访问路径，数据库可能仍需扫描和排序大量数据后才能得到前 20 行。LIMIT 只限制输出行数，不自动限制中间计算量。

当排序列和过滤列形成合适的索引顺序时，数据库才可能从索引前端快速获取结果。但这必须结合等值条件、范围条件和排序方向验证。

5. 把 Join 重复误认为数据库重复执行错误

如果一对多或多对多连接产生重复行，通常是关系结果的自然语义。例如：

一个客户有 100 个订单
一个客户有 20 个标签
订单 JOIN 标签

若连接条件只通过客户连接，结果可能产生：

100 \times 20 = 2000

条该客户相关的组合。此时使用 DISTINCT 可能掩盖建模或连接条件问题，并且还要额外排序或哈希去重。应先确认业务需要的是：

所有组合；
是否存在匹配；
每个客户一行；
每个客户的聚合结果。

例如仅判断是否存在订单，可使用半连接语义：

SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
      AND o.status = 'PAID'
);

优化器可能把 EXISTS 改写为半连接，但 SQL 语义首先表达了“存在性”，比先生成大量连接结果再去重更清晰。

十一、参数化查询与计划稳定性

应用通常使用参数化 SQL：

SELECT id, created_at
FROM orders
WHERE tenant_id = $1
  AND status = $2
  AND created_at >= $3;

参数化可以避免字符串拼接带来的 SQL 注入风险，并有利于计划复用。但“复用计划”与“每次根据参数重新估计”是不同策略。

当数据严重倾斜时：

tenant_id = 1 返回 70% 数据；
tenant_id = 9999 只返回 10 行。

一个适合大租户的顺序扫描计划，可能不适合小租户；反之亦然。若数据库选择通用计划或复用不适合当前参数的计划，就可能出现“同一条 SQL，有时极快、有时极慢”。

诊断时应：

记录实际参数，而不是只记录 SQL 模板；
分别对典型大值和小值获取执行计划；
检查数据库是否选择自定义计划或通用计划；
不要通过随意拼接 SQL 来规避问题；
评估数据分区、查询拆分、统计信息或应用层路由。

具体的计划缓存行为、阈值和配置项因 PostgreSQL、MySQL 版本及驱动协议不同而不同，不能用一个产品的参数解释另一个产品。

十二、事务、MVCC 与“扫描了不该看到的行”

执行计划展示的是操作路径，结果还受到事务可见性和隔离语义影响。

在 MVCC 数据库中，表页里可能存在对当前事务不可见的旧版本记录。执行器需要：

读取页或索引条目；
判断记录版本对当前事务是否可见；
对可见版本应用谓词；
输出满足条件的行。

这会带来几个边界：

物理上读取的记录数不等于逻辑上返回的行数；
更新和删除留下的旧版本可能增加扫描成本；
索引仅命中不代表最终一定返回；
PostgreSQL 的 Index Only Scan 仍可能访问堆页确认可见性，取决于可见性映射；
长事务可能阻碍清理旧版本，造成表膨胀或历史版本积累。

锁等待也与事务边界密切相关。一个“查询很慢”的请求可能只是等待另一个事务提交，而另一个事务又因为连接池泄漏、应用异常或未及时提交而长期持锁。

因此，慢查询诊断不能只保存 SQL 和执行计划，还应关联：

事务开始时间；
当前会话状态；
等待事件；
阻塞者；
应用请求和连接标识；
提交、回滚和超时路径。

十三、并行执行和资源边界

当扫描、Join、聚合或排序规模较大时，数据库可能选择并行计划。并行通常包含：

启动协调者；
启动若干 worker；
worker 分担扫描或计算；
汇总局部结果；
协调者返回最终结果。

并行并不保证更快，因为它增加：

worker 启动成本；
进程或线程调度；
内存消耗；
worker 间数据交换；
对共享存储和 CPU 的竞争。

如果查询本身只返回几行，但需要先处理很大的数据集，并行可能有收益；如果查询很小，启动开销可能超过计算收益。

生产验证需要观察：

实际使用了多少 worker；
是否因资源不足没有获得期望 worker；
并行节点是否出现数据倾斜；
多个并行查询叠加后总内存是否可接受；
连接池并发与并行度相乘后是否造成过载。

“把并行度调大”不是一般性优化。它必须建立在 CPU、内存、I/O 和并发预算之上。

十四、从计划到修复：一个可靠的闭环

一个完整的优化闭环应当保留修改前后的证据：

1. 记录基线

至少记录：

SQL 和参数；
p50、p95、p99 延迟；
执行次数；
返回行数；
计划文本；
实际行数；
缓存命中和临时 I/O；
锁等待、CPU 和磁盘指标；
事务隔离级别和部署节点。

只看一次执行时间容易被缓存、并发和数据冷热误导。

2. 先修复估计基础

可能的措施包括：

更新表统计信息；
调整统计目标或直方图粒度；
为明显相关的列建立适合的多列统计；
检查数据倾斜和热点值；
修正表达式、类型转换和时间边界；
检查分区裁剪是否生效。

3. 再评估访问路径和 Join

确认：

谓词是否真正减少了输入；
索引列顺序是否支持过滤和排序；
是否需要覆盖索引；
Join 键类型是否一致；
Nested Loop 的外层行数和内层 loops 是否合理；
Hash Join 是否溢出临时文件；
Merge Join 的排序成本是否值得。

4. 修改后验证语义和资源

性能变快不等于修改正确。需要验证：

返回行是否相同；
NULL 和重复行语义是否改变；
时区和时间边界是否正确；
事务锁范围是否改变；
写入成本和索引维护成本是否可接受；
其他查询是否因计划或缓存变化变慢；
在真实参数分布和并发下是否仍然成立。

5. 保留回滚路径

索引、统计配置、查询改写和参数设置都应能回退。尤其是：

强制索引或禁用某类 Join 可能掩盖统计问题；
增加内存参数可能在并发下放大 OOM 风险；
删除索引可能影响未被当前样本覆盖的查询；
更改事务或超时设置可能改变故障恢复行为。

十五、几个必须牢牢记住的判断原则

顺序扫描不是天然坏计划

它可能是处理大比例数据的最低成本方案。真正可疑的是：估计应该只返回少量行，但实际扫描和返回了大量行。

索引不是减少所有成本

索引主要减少定位数据的成本；它本身有维护、回表、缓存和随机 I/O 成本。覆盖索引减少回表，却通常增加索引宽度和写放大。

Join 算法由输入规模决定

外层小、内层可索引：Nested Loop 可能最好；
等值连接、输入较大：Hash Join 常有优势；
输入已有排序或需要排序输出：Merge Join 可能合适；
大量重复键造成的输出膨胀，不能仅靠更换算法消除。

基数错误通常比算法选择更基础

优化器如果把 500,000 行估成 50 行，后续选择哪一种 Join 算法都可能建立在错误前提上。先找估计与实际的第一个明显分叉点，再讨论是否需要改变索引或 Join 策略。

慢查询不一定是执行计划慢

锁等待、连接池排队、CPU 争用、临时 I/O、网络传输和客户端读取都可能占据主要时间。必须把端到端延迟拆开测量。

计划分析必须带着边界条件

计划依赖统计信息、参数、版本、配置、事务、缓存和并发。一个测试环境中更快的计划，不自动意味着生产环境中更好；一个节点显示索引，也不自动意味着整个查询高效。

当能够同时回答“优化器估计了多少”“实际处理了多少”“中间结果在哪里膨胀”“连接算法的状态和内存如何变化”“用户等待的时间到底花在哪里”时，执行计划才真正从一张树状图变成了可验证的性能证据。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。

SQL 执行计划与查询优化：基数估计、Join 算法和慢查询诊断

一、从 SQL 到执行计划：逻辑操作与物理操作

1. 逻辑计划和物理计划

2. 计划节点中的关键数字

二、基数估计：优化器为何会选错计划

1. 基数、选择率和过滤条件

2. 统计信息如何支持估计

3. 多个条件不能总是简单相乘

4. Join 基数的推导

5. Join 顺序为何重要

三、成本模型：低成本不等于低延迟

四、主要扫描方式与索引的真实作用

1. 顺序扫描

2. 索引扫描

3. 覆盖索引与索引条件下推

五、Join 算法：输入、状态和适用条件

1. Nested Loop Join

适合的情况

失败表现

一个反例

2. Hash Join

内存边界

适合的情况

限制

3. Merge Join

适合的情况

重复键的代价

4. 算法不是可以任意互换的黑盒

六、一个可运行的 PostgreSQL 计划实验

1. 如何阅读这个计划

估计行数与实际行数

loops

BUFFERS

2. ANALYZE 的边界

七、统计信息失真：慢计划最常见的根因之一

1. 数据变化后统计信息没有跟上

2. 数据倾斜和最常见值

3. 相关列

4. 表达式、隐式类型转换和函数

八、慢查询诊断：先区分执行慢和等待慢

1. 计划成本低但仍然很慢

2. 计划实际执行慢

3. 不要把应用测得的时间直接等同于计划时间

九、用 EXPLAIN 诊断时的完整步骤

第一步：固定复现条件

第二步：先看估计计划

第三步：在安全边界内获取实际计划

第四步：定位第一个显著误差

第五步：判断修复类别

十、常见错误修复及其反例

1. 看到全表扫描就强制索引

2. 看到缺索引就建立宽索引

3. 只看总耗时，不看中间结果

4. 用 LIMIT 掩盖大查询

5. 把 Join 重复误认为数据库重复执行错误

十一、参数化查询与计划稳定性

十二、事务、MVCC 与“扫描了不该看到的行”

十三、并行执行和资源边界

十四、从计划到修复：一个可靠的闭环

1. 记录基线

2. 先修复估计基础

3. 再评估访问路径和 Join

4. 修改后验证语义和资源

5. 保留回滚路径

十五、几个必须牢牢记住的判断原则

顺序扫描不是天然坏计划

索引不是减少所有成本

Join 算法由输入规模决定

基数错误通常比算法选择更基础

慢查询不一定是执行计划慢

计划分析必须带着边界条件

系列导航与关联阅读

官方资料

`loops`

`BUFFERS`

2. `ANALYZE` 的边界

九、用 `EXPLAIN` 诊断时的完整步骤

4. 用 `LIMIT` 掩盖大查询