想知道数据库怎么给查询“算成本选路线”？EXPLAIN能帮你看明白？

kknone
104 阅读18分钟

EXPLAIN工具的基础：作用与基本用法

什么是EXPLAIN？

在PostgreSQL中，查询优化器会为每个SQL查询生成多种可能的执行方案（比如全表扫描 vs 索引扫描、嵌套循环连接 vs 哈希连接），并选择“成本最低”的方案——这就是查询计划。EXPLAIN工具的核心作用，是将这个“隐藏的决策过程”转化为可读的文本，帮你理解数据库“为什么这么执行查询”，以及“如何优化”。

简单来说，EXPLAIN就像查询的“烹饪说明书”：它不会帮你做“菜”（执行查询），但会告诉你“需要哪些步骤”“每步的成本”“用多少数据”。

EXPLAIN vs EXPLAIN ANALYZE：关键区别

很多初学者会混淆这两个命令，它们的核心差异在于是否实际执行查询：

  • EXPLAIN：仅生成估计的查询计划，不执行查询。适合快速查看优化器的决策，但无法验证“估计是否准确”。
  • EXPLAIN ANALYZE：执行查询，并在计划中添加实际执行数据（比如实际时间、实际行数）。适合分析慢查询，但注意：它会真正执行查询（比如UPDATE或DELETE会修改数据），因此不要在生产环境随意使用！

举个例子：

-- 仅看估计计划（不执行）
EXPLAIN SELECT * FROM users WHERE age > 30;

-- 执行查询并看实际数据
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

基本语法示例

EXPLAIN的语法非常简单：在要分析的查询前添加EXPLAIN（或EXPLAIN ANALYZE）即可。以下是常见场景的示例：

-- 分析简单SELECT查询
EXPLAIN SELECT name, email FROM users WHERE created_at > '2023-01-01';

-- 分析JOIN查询
EXPLAIN ANALYZE SELECT u.name, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.country = 'China';

-- 分析带排序的查询
EXPLAIN SELECT * FROM users ORDER BY age DESC LIMIT 10;

执行后，PostgreSQL会返回一个树状结构的文本——这就是查询计划。

查询计划的核心结构：节点与输出列

查询计划的树状结构

PostgreSQL的查询计划是自底向上执行的树状结构：每个“节点”代表一个操作（比如扫描表、连接表、排序），父节点依赖子节点的输出。例如，一个JOIN节点的子节点是两个Scan节点（分别扫描两张表）。

举个简单的例子，查询SELECT * FROM users WHERE age > 30的计划可能是这样：

Seq Scan on users  (cost=0.00..100.00 rows=2000 width=100)
  Filter: (age > 30)

这里的Seq Scan on users是根节点（唯一节点），Filter是它的筛选条件。

常见节点类型解析

查询计划中的节点类型直接反映了数据库的执行方式，以下是最常见的几种（重点掌握）：

  1. Seq Scan（顺序扫描）：全表扫描，逐个读取表中的每一行。适合小表或筛选条件无索引的情况（比如age > 30但age无索引）。
  2. Index Scan（索引扫描）：先扫描索引找到符合条件的行的位置，再回表读取完整数据。适合筛选条件选择性高（比如age = 30，返回少数行）的情况。
  3. Index Only Scan（仅索引扫描）：不需要回表！如果查询的列都在索引中（比如SELECT age FROM users WHERE age > 30，且索引包含age），数据库直接从索引获取数据，速度更快。
  4. Hash Join：将其中一张表的数据做成哈希表，再用另一张表的数据匹配。适合两张表都较大的情况（比如users10万行、orders100万行）。
  5. Nested Loop Join：用一张表的每一行去遍历另一张表。适合一张表很小（驱动表）、另一张表有索引的情况（比如users1万行、orders100万行且user_id有索引）。
  6. Sort：对数据排序。如果ORDER BY的列没有索引，就会出现这个节点——排序的成本很高，尽量避免。

输出列的解读

每个节点的输出会包含几个关键列，理解它们是解读计划的核心：

列名含义
Cost优化器估计的“执行成本”（相对值，不是实际时间），格式为启动成本..总成本。比如0.00..100.00。
Rows优化器估计的“该节点输出的行数”（基于统计信息）。
Width优化器估计的“每行的字节数”（所有列的字节总和）。
Actual Time仅EXPLAIN ANALYZE有，实际执行时间（格式为开始时间..结束时间）。
Actual Rows仅EXPLAIN ANALYZE有，实际输出的行数。

举个EXPLAIN ANALYZE的示例：

Seq Scan on users  (cost=0.00..100.00 rows=2000 width=100) (actual time=0.012..0.500 rows=2000 loops=1)
  Filter: (age > 30)
  Rows Removed by Filter: 8000

解读：

  • 估计值：启动成本0（不需要准备数据），总成本100；估计输出2000行，每行100字节。
  • 实际值：总执行时间0.5秒，实际输出2000行，过滤掉8000行（原表共10000行）。

深入理解Cost模型：如何计算与解读

Cost的组成：启动成本与总成本

Cost是PostgreSQL优化器的“决策依据”，它是相对值（不是毫秒或秒），基于两个核心因素：

  1. I/O成本：读取数据页的成本（比如全表扫描需要读多少个数据页）。
  2. CPU成本：处理每行数据的成本（比如过滤、计算）。

具体来说，Cost的格式是启动成本..总成本：

  • 启动成本：该节点开始输出第一行数据前的成本（比如排序节点需要先收集所有数据，启动成本很高）。
  • 总成本：该节点完成所有输出的总成本（启动成本 + 后续处理成本）。

例如，一个Sort节点的Cost可能是50.00..60.00：启动成本50（排序前需收集所有数据），总成本60（排序+输出）。

为什么Cost是相对值？

PostgreSQL的Cost不是实际时间，而是优化器内部的评分标准。它的计算基于几个配置参数（可通过show all;查看）：

  • seq_page_cost：全表扫描时读取一个数据页的成本（默认1.0）。
  • idx_page_cost：索引扫描时读取一个索引页的成本（默认0.25，因为索引通常更紧凑）。
  • cpu_tuple_cost：处理一行数据的CPU成本（默认0.01）。
  • cpu_operator_cost：执行一个运算符（比如>、=）的CPU成本（默认0.0025）。

例如，全表扫描的成本计算公式是： Cost = (数据页数 × seq_page_cost) + (行数 × cpu_tuple_cost)

优化器会比较所有可能计划的总成本，选最小的那个——这就是“最优计划”。

Rows与实际行数的差异：统计信息的重要性

Rows是优化器根据统计信息（比如表的行数、列的分布）估计的输出行数。如果Rows和Actual Rows差异很大（比如估计100行，实际10000行），说明统计信息过时了，会导致优化器选“错误的计划”。

示例：假设users表原本有1000行，后来插入到100万行，但没更新统计信息。优化器可能还认为“全表扫描很快”，但实际全表扫描需要10秒——这时候你需要执行ANALYZE users;更新统计信息。

应用：用EXPLAIN优化查询的步骤

案例1：从Seq Scan到Index Scan——索引的作用

场景：users表有100万行，age列无索引，执行查询SELECT * FROM users WHERE age = 30;。

原始计划（EXPLAIN ANALYZE输出）：

Seq Scan on users  (cost=0.00..2000.00 rows=10000 width=100) (actual time=0.015..5.000 rows=10000 loops=1)
  Filter: (age = 30)
  Rows Removed by Filter: 990000

解读：全表扫描（Seq Scan），实际用了5秒，过滤掉99万行——效率极低。

优化方法：给age列加索引：

CREATE INDEX idx_users_age ON users(age);

优化后计划：

Index Scan using idx_users_age on users  (cost=0.29..80.00 rows=10000 width=100) (actual time=0.008..0.100 rows=10000 loops=1)
  Index Cond: (age = 30)

解读：索引扫描（Index Scan），实际时间从5秒降到0.1秒——成本从2000降到80，效果显著！

案例2：Join类型的选择——Nested Loop vs Hash Join

场景：有两张表：

  • users：1万行（小表），id是主键。
  • orders：100万行（大表），user_id是外键（无索引）。

执行查询SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;。

原始计划：

Nested Loop  (cost=0.00..15000.00 rows=1000000 width=20) (actual time=0.010..10.000 rows=1000000 loops=1)
  ->  Seq Scan on users u  (cost=0.00..20.00 rows=10000 width=10) (actual time=0.005..0.100 rows=10000 loops=1)
  ->  Seq Scan on orders o  (cost=0.00..1.00 rows=100 width=10) (actual time=0.000..0.000 rows=100 loops=10000)
        Filter: (user_id = u.id)

解读：Nested Loop连接（用users的每一行去遍历orders），实际用了10秒——因为orders没有user_id的索引，每次遍历都要全表扫描。

优化方法：给orders.user_id加索引：

CREATE INDEX idx_orders_user_id ON orders(user_id);

优化后计划：

Hash Join  (cost=200.00..5000.00 rows=1000000 width=20) (actual time=1.000..3.000 rows=1000000 loops=1)
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o  (cost=0.00..2000.00 rows=1000000 width=10) (actual time=0.005..0.500 rows=1000000 loops=1)
  ->  Hash  (cost=20.00..20.00 rows=10000 width=10) (actual time=0.900..0.900 rows=10000 loops=1)
        ->  Seq Scan on users u  (cost=0.00..20.00 rows=10000 width=10) (actual time=0.003..0.100 rows=10000 loops=1)

解读：优化器切换到Hash Join（把users做成哈希表，再匹配orders），实际时间从10秒降到3秒——因为索引让orders的查找更快，优化器选了更适合大数据集的连接方式。

案例3：避免不必要的排序——ORDER BY的优化

场景：执行查询SELECT * FROM users WHERE age > 30 ORDER BY created_at;，created_at无索引。

原始计划：

Sort  (cost=150.00..160.00 rows=2000 width=100) (actual time=1.000..1.500 rows=2000 loops=1)
  Sort Key: created_at
  Sort Method: quicksort  Memory: 200kB
  ->  Seq Scan on users  (cost=0.00..100.00 rows=2000 width=100) (actual time=0.010..0.500 rows=2000 loops=1)
        Filter: (age > 30)

解读：Sort节点的成本是150..160，实际用了1.5秒——因为ORDER BY created_at需要排序，而created_at无索引。

优化方法：创建复合索引（覆盖筛选条件和排序字段）：

CREATE INDEX idx_users_age_created_at ON users(age, created_at);

优化后计划：

Index Scan using idx_users_age_created_at on users  (cost=0.29..80.00 rows=2000 width=100) (actual time=0.008..0.200 rows=2000 loops=1)
  Index Cond: (age > 30)

解读：Sort节点消失了！因为复合索引的顺序是age→created_at，数据库可以直接从索引中读取“按created_at排序”的行，不需要额外排序——实际时间从1.5秒降到0.2秒。

课后Quiz：巩固EXPLAIN知识

问题1：EXPLAIN和EXPLAIN ANALYZE的主要区别是什么？

答案：EXPLAIN仅生成估计的查询计划，不执行查询；EXPLAIN ANALYZE会执行查询，并返回实际执行时间、实际行数等数据。
解析：EXPLAIN适合快速查看优化器的决策，EXPLAIN ANALYZE适合验证“估计是否准确”（比如Rows是否接近Actual Rows）。但注意，EXPLAIN ANALYZE会执行写操作（比如UPDATE），所以不要在生产环境随意使用。参考链接：www.postgresql.org/docs/17/sql…

问题2：当EXPLAIN输出中出现Seq Scan时，是否一定需要添加索引？为什么？

答案：不一定。
解析：Seq Scan的成本并不总是更高——如果表很小（比如只有100行），全表扫描比索引扫描更快（因为索引需要额外的I/O去读取索引文件）。只有当表很大（比如100万行）且筛选条件选择性高（比如返回行数占总表行数的5%以下）时，添加索引才有意义。参考链接：www.postgresql.org/docs/17/usi…

问题3：为什么Rows和Actual Rows差异大会导致查询变慢？

答案：因为Rows是优化器选择查询计划的关键依据。如果Rows估计过小（比如实际10000行，估计100行），优化器可能会选不适合大数据集的计划（比如Nested Loop Join），导致实际执行时间变长。
解决方法：执行ANALYZE命令更新表的统计信息（比如ANALYZE users;）。参考链接：www.postgresql.org/docs/17/pla…

常见报错与解决方案

报错1：ERROR: syntax error at or near \"EXPLAIN\"

原因：EXPLAIN语法错误（比如缺少查询语句）。
示例：EXPLAIN;（缺少查询）或SELECT * FROM users EXPLAIN;（位置错误）。
解决：确保EXPLAIN后紧跟完整查询，比如EXPLAIN SELECT * FROM users;。

报错2：ERROR: could not open relation with OID 12345

原因：表不存在，或用户无SELECT权限。
解决：

  1. 检查表名拼写（比如users写成user）；
  2. 授予权限：GRANT SELECT ON users TO your_user;。

报错3：估算行数与实际相差大

现象：EXPLAIN ANALYZE显示actual rows=1000但rows=100。
原因：统计信息过时。
解决：运行ANALYZE users;更新统计信息。

报错4：EXPLAIN ANALYZE执行慢

原因：查询本身耗时（比如全表扫描1000万行）。
解决：加LIMIT限制行数（比如EXPLAIN ANALYZE SELECT * FROM orders LIMIT 100;），但注意这会改变执行计划。

参考链接

余下文章内容请点击跳转至 个人博客页面 或者 扫码关注或者微信搜一搜：编程智域 前端至全栈交流与成长，阅读完整的文章：想知道数据库怎么给查询“算成本选路线”？EXPLAIN能帮你看明白？

往期文章归档
免费好用的热门在线工具
avatar
文章
阅读
粉丝