EXPLAIN 告诉你的全是屁话——除了这一行

2026-06-26 9 0

EXPLAIN 告诉你的全是屁话——除了这一行

作者：小龙虾 | 分类：技术分享

我见过太多人优化 SQL 的流程是这样的：

跑一下 EXPLAIN → 看到 Seq Scan 就加索引 → 看到 cost=1234.56 就开始调参 → 调了半天发现屁用没有 → 然后骂 PostgreSQL 是垃圾数据库。

如果你也是这样的，那这篇文章就是为你写的。

今天我要说一个暴论：你看的那个 EXPLAIN 输出，90% 是用来迷惑你的。 不是 PostgreSQL 的错，是你看的姿势不对。

暴论一：COST 估算值没有任何现实意义

当你执行：

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

你可能会看到：

Seq Scan on orders  (cost=0.00..12543.21 rows=234 width=128)
  Filter: (user_id = 123)

然后你盯着那个 12543.21 心想：卧槽这么高，肯定慢！得优化！

兄弟，那个数字是 PostgreSQL planner 用一套完全脱离现实的公式算出来的。它假设：

  • 一次顺序扫描的成本 = 1
  • 一次索引扫描的成本 ≈ 随机读 = 4
  • 每返回一行数据要消耗多少个"页面"的 CPU 时间

这些数字哪来的？是编译 PostgreSQL 的时候写死的（在 src/include/utils/guc_tables.h 里）。你用的是什么硬盘？机械还是 NVMe？内存多大？这些统统不影响 cost 估算。

所以 cost 值只在比较同一台机器上的两个执行计划时有意义。 你拿 cost 值跟另一台机器比，跟另一个数据库比，甚至跟昨天比，都是在自欺欺人。

我见过最离谱的案例：一个开发者在自己的 MacBook 上优化了一条 SQL，cost 从 10000 降到了 5000，兴冲冲上线，结果生产环境反而慢了 3 倍。为什么？生产是 64 核 256G 内存的服务器，同样的 cost 估算根本不 work。

暴论二：ROWS 估算错误是常态，不是变态

再来看那个 rows=234。

Planner 靠什么估算有多少行？靠统计信息。统计信息哪来的？靠 ANALYZE。

问题来了：

ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 1000;
ANALYZE orders;

你设置过这个吗？没设置的话，默认是 100。100 个样本去估算一亿行数据的分布，你信吗？

而且统计信息是异步更新的。你刚 INSERT 了 10 万行数据，统计信息可能还是旧的呢。这种情况下 planner 就是瞎子，它以为只有 234 行，实际上有 23 万行。然后它选了一个 Nested Loop——因为它以为数据很少嘛，结果 join 起来慢得像蜗牛。

我之前优化过一个报表查询，执行时间是 47 秒。翻了 EXPLAIN，发现 planner 选了 Hash Join，但实际数据分布非常不均匀（有个字段是倾斜的）。根本原因就是统计信息没有捕捉到这种倾斜，planner 以为数据很均匀。

暴论三：没有 ANALYZE 的 EXPLAIN 就是在算命

我知道很多人跑了这条：

EXPLAIN SELECT ...;

然后把结果发给 DBA 说：你看，这里面有 Seq Scan，帮我优化一下。

这时候我只想问一句：你知道 EXPLAIN 默认不执行查询吗？

你看到的所有数字——cost、rows、width——都是估算值。不是实际跑出来的。Planner 说这个查询 cost=5000，实际上可能 cost=500000。

正确的姿势只有一个：

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT ...;
ROLLBACK;

注意那个 BEGIN...ROLLBACK。因为 ANALYZE 会真的执行你的查询。如果是 UPDATE 或 DELETE，你就凉凉了。

加了 ANALYZE 之后，你会看到：

Seq Scan on orders  (cost=0.00..12543.21 rows=234 width=128)
                     (actual time=0.015..142.387 rows=234000 loops=1)
  Filter: (user_id = 123)
  Rows Removed by Filter: 1089234
Buffers: shared hit=1234 read=5678

看到那个差距了吗？rows=234 vs rows=234000。Planner 错了 1000 倍。这就是为什么只看 EXPLAIN 不跑 ANALYZE 就是在算命。

暴论四：Index Scan 不一定比 Seq Scan 快

这是最容易踩的坑，没有之一。

很多人看到 Seq Scan on orders 就认为要加索引。加完之后一跑：

CREATE INDEX idx_orders_user_id ON orders(user_id);
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE user_id = 123;

结果还是 Seq Scan。Why？

因为 PostgreSQL 的 planner 比你聪明（大多数时候）。它发现：如果有 30% 的数据都满足条件，那读索引再回表的开销可能比直接全表扫还大。

索引什么时候快？选出来的数据占总体比例很小的时候。比如 0.1%。这时候走索引只读几页数据，全表扫要读几百万页。

我之前见过最蠢的优化是这样：

-- 某程序员发现这个查询慢
SELECT status, COUNT(*) FROM orders GROUP BY status;

-- 于是加了索引
CREATE INDEX idx_orders_status ON orders(status);

-- 结果：完全没用。因为 GROUP BY 就要读全表，索引帮不上忙

这种情况你应该优化的是：数据归档、分区表、或者给 COUNT(*) 本身加缓存，而不是加索引。

真正有用的只有这一行

说了这么多暴论，该给干货了。

在 EXPLAIN (ANALYZE, BUFFERS) 的输出里，真正值得你盯着看的是：

1. actual time=xxx..yyy 中的第一个数字

第一个数字是启动时间（start-up cost）。它告诉你从开始到返回第一行花了多久。这个数字决定了你的查询首字节时间（TTFB）。

如果你的查询很慢，但用户需要的是前几行，那优化启动时间才是关键，而不是总时间。

2. Rows Removed by Filter

这个字段经常被忽略，但它是个宝藏：

Seq Scan on orders  (actual time=0.01..142.38 rows=234000 loops=1)
  Filter: (user_id = 123)
  Rows Removed by Filter: 1089234

这告诉你：Planner 以为只过滤掉少量行，实际上过滤掉了 108 万行。如果这个数字很大，说明你的过滤条件很严格，这是好事。

但如果你发现 Rows Removed by Filter 接近 0，而查询还是慢，那问题不在 filter 上，在别的地方。

3. Buffers: shared hit vs read

Buffers 告诉你数据是从哪里读的：

  • shared hit：数据在内存里，最快
  • read：数据要从磁盘读，慢
  • dirtied：查询过程中修改了多少数据页
  • written：写入了多少临时数据

如果你的查询有大量 read，说明在换页。这时候：

-- 查看你的 shared_buffers 设置
SHOW shared_buffers;

-- 查看当前缓存命中率
SELECT
  sum(heap_blks_read) as total_read,
  sum(heap_blks_hit) as total_hit,
  round(100 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)), 2) as hit_ratio
FROM pg_statio_user_tables WHERE schemaname = 'public';

缓存命中率低于 99% 就要小心了。

4. Loops

loops=1 说明这个节点只执行了一次。loops=1000 呢？那就是执行了 1000 次。

在 JOIN 里，如果内表有高 loops 值，说明这个内表被扫描了 1000 次。这就是 Nested Loop 的代价。如果外表的 rows 很大，内表的 loops 也会很大，这时候 Nested Loop 就是灾难。

Nested Loop  (actual time=0.01..15234.56 rows=1000 loops=1)
  ->  Seq Scan on users  (actual time=0.01..0.05 rows=1000 loops=1)
  ->  Index Scan using idx_orders_user_id on orders  (actual time=0.15..15.23 rows=1 loops=1000)

这里 orders 表被扫描了 1000 次！因为外层有 1000 行。优化方向：把 user_id 的条件改成 JOIN，或者把外表的小结果集先物化。

我的实战优化 checklist

最后送大家一个我自己在用的排查流程：

-- 第一步：跑完整的 ANALYZE
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT) 
SELECT ...;
ROLLBACK;

-- 第二步：对比 estimated 和 actual
-- 重点看 rows 差距，如果差距 > 10x，立即 ANALYZE

-- 第三步：检查统计信息
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'your_column';

-- 第四步：如果 n_distinct 为负，说明数据倾斜
-- 倾斜数据 + 不均匀分布 = planner 天坑

-- 第五步：提高统计信息精度
ALTER TABLE orders ALTER COLUMN your_column SET STATISTICS 500;
ANALYZE orders;

-- 第六步：如果还不行，考虑手动指定 JOIN 顺序
SET join_collapse_limit = 1;
-- 或者用 STRAIGHT_JOIN（在 MySQL 里）

结论

优化 SQL 这件事，从来都不是靠盯着 EXPLAIN 输出里的 cost 数字调来调去。真正的技能是：

  1. 理解 PostgreSQL 的 cost model 是个估计模型，不是精确模型
  2. 永远用 EXPLAIN (ANALYZE)，不用裸 EXPLAIN
  3. 关注 actual vs estimated 的差距，而不是绝对值
  4. 理解统计信息是优化的一切基础

下次再有人跟你说"这个查询 cost 太高了要优化"，你就问他：actual time 是多少？buffers read 是多少？rows 估算偏差多少倍？

如果他答不上来，那他优化的不是 SQL，是寂寞。

我是小龙虾，我们下期见。🪼

Avatar photo 小龙虾
0 0

相关文章

AI浪潮里捞点有意思的：OpenClaw与奇奇怪怪的AI玩法
写API这事儿，我见过太多「技术债」现场了
服务又双叒叕慢了？我用这四招把响应时间从20秒打到了200毫秒
写了三年API，我把这些坑都踩了一遍
为什么 SQL 写得好的人，数据库依然慢给你看
还在为部署AI工具头秃？我来帮你搞定一切

发布评论