KernelRanch
返回文章列表

读放大：一条返回 21 行的查询扫了 200 万行，代价是 351 倍

2026-11-10 #数据库

"慢查询"这个词容易误导人，因为它听起来像"某条 SQL 写得很差"。更准确的说法是"这条 SQL 读得太多了"。同一个逻辑操作，读 4 个缓冲区块和读 14720 个，延迟能差两个数量级——这就是读放大（read amplification）。

0. 定义：三个口径

读放大可以按三个层次量化，越靠下越接近硬件：

口径含义从哪看
行放大扫描的行数 ÷ 返回的行数pg_stat_user_tables.seq_tup_read 与查询返回行数
块放大读了多少个 8kB 缓冲区块EXPLAIN (ANALYZE, BUFFERS) 的 Buffers: 行
延迟放大优化前后执行时间之比EXPLAIN ANALYZE 的 Execution Time

三个口径的数值不一定同比例，但它们指向同一个东西：你为了拿到 21 行，付出了多少代价。

1. 实验设置

项值
版本PostgreSQL 18.6（Docker，shared_buffers=256MB，其余默认）
数据200 万行，user_id 100,000 个不同值（平均每值 20 行）
查询select count(*) from events where user_id = 12345（实际返回 21 行）
计时100 次连续执行的总墙钟时间 + 单次 EXPLAIN (ANALYZE, BUFFERS)

2. 加索引前后的三个口径

先看 100 次连续执行的批量结果：

指标无索引有索引（user_id）
100 次总耗时2.5 s0.2 s
seq_scan3001
seq_tup_read200,000,0001,059,984（来自 ANALYZE 采样）
idx_scan0100
idx_tup_fetch00

再单看一次查询：

指标无索引有索引
计划Parallel Seq ScanIndex Only Scan
Execution Time24.611 ms0.070 ms
Buffersshared hit=14720shared hit=1 read=3

换算一下：

  • 行放大：2,000,000 ÷ 21 ≈ 95,238 倍
  • 块放大：14720 ÷ 4 = 3,680 倍（14720 × 8kB ≈ 115MB，对比 4 × 8kB = 32kB）
  • 延迟放大：24.611 ÷ 0.070 ≈ 351 倍

seq_tup_read 那一行值得单独看：100 次查询累计读了 2 亿行，正好等于 100 × 2,000,000。这说明每次查询都在完整扫描全表，一行没省。而它只需要 21 行。

顺带解释 idx_tup_fetch = 0：这是 index-only scan，只需要读索引、不需要回表取堆元组。idx_tup_fetch 统计的是堆访问次数，所以是 0。很多人看到 0 会以为索引没被用，其实恰恰相反——它是被用得很彻底。

3. 一个不成立的经验法则

流传很广的说法是"选择性差（返回行占比高）时，优化器会放弃索引改走全表扫描"。我按这个说法设计了对照组：status 字段只有 5 个值，status='ok' 命中 401,087 行，占全表 20%。

结果优化器并没有走全表扫描：

->  Parallel Index Only Scan using idx_events_status on events
      (actual time=0.018..5.128 rows=133695.67 loops=3)
Execution Time: 11.916 ms

原因是：count(*) 不需要 amount 列，索引里就有 status，走 index-only scan 扫索引比扫全表便宜得多。

但如果查询需要回表，结论立刻反转。同一条件改成 avg(amount)：

->  Parallel Bitmap Heap Scan on events  (actual time=7.597..25.257 rows=133695.67 loops=3)
      ->  Bitmap Index Scan on idx_events_status  (rows=401087.00)
Execution Time: 38.584 ms

同样是 401,087 行、同一个索引、同一个谓词：

查询计划执行时间
count(*) ... where status='ok'Parallel Index Only Scan11.916 ms
avg(amount) ... where status='ok'Parallel Bitmap Heap Scan38.584 ms

差 3.2 倍，全部来自"要不要回表"。所以"选择性差就不走索引"这条法则至少要说完整：它只适用于需要取堆元组的查询；覆盖索引（index-only scan）不受这条限制。

4. 三条 SQL 定位读放大

-- 1) 谁在全表扫描：seq_tup_read 远大于返回行数
SELECT relname,
       seq_scan,
       seq_tup_read,
       coalesce(idx_scan, 0)  AS idx_scan,
       n_live_tup
FROM pg_stat_user_tables
WHERE seq_tup_read > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

判读：seq_tup_read ÷ n_live_tup 就是"平均每次全表扫描扫了几倍的表"。如果 seq_scan 很高而 n_live_tup 只有几万，这条表上的查询几乎都在做无意义的全扫。

-- 2) 块级：堆和索引各读了多少块
SELECT relname,
       heap_blks_read,
       coalesce(idx_blks_read, 0) AS idx_blks_read,
       heap_blks_hit,
       coalesce(idx_blks_hit, 0)  AS idx_blks_hit
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;

判读：heap_blks_read 高说明真的在读表数据；idx_blks_read 高但 heap_blks_read 低，说明是索引扫描（正常）。

-- 3) 单条查询：Buffers 是最终裁决
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events WHERE user_id = 12345;

Buffers: shared hit=... read=... 里的数字是 8kB 页数，乘以 8 就是实际读的字节量。文档对此的说明是：这些数字统计的是"命中的、读取的、弄脏的、写出的非去重缓冲区数量"，并包含所有子节点[1]。

5. 什么时候读放大是正常的

不是所有全表扫描都要优化：

  • 全表聚合（select count(*) from events 无谓词、select sum(amount) from events）：本来就要读全表，读放大等于 1，没问题。
  • 小表：几千行的表做全扫比走索引还快，优化器也知道。文档明确提到，只占一个磁盘页的表几乎总是走顺序扫描[1]。
  • 列存引擎：DuckDB 这类引擎按列读取，扫两列就只读两列，行存里的"读整行"问题在它那里不存在。同一份 200 万行数据，DuckDB 的聚合查询是毫秒级（见同目录另一篇的实测）。

真正需要干预的是：返回行数很少、但扫描行数极大的查询。判据可以写得很简单——seq_tup_read ÷ (返回行数 × 调用次数) 大于 1000 就值得看一眼计划。

6. 在 Windows 上复现

docker run -d --name pgcmp --memory 1g -e POSTGRES_PASSWORD=postgres postgres:18 -c shared_buffers=256MB
docker exec -u postgres pgcmp psql -c "create table events(id int, user_id int, ts timestamp, amount numeric(10,2), status text);"
# 灌入 200 万行后：
docker exec -u postgres pgcmp psql -c "select pg_stat_reset();"
docker exec -u postgres pgcmp psql -c "explain (analyze, buffers) select count(*) from events where user_id=12345"
docker exec -u postgres pgcmp psql -Atc "select seq_scan, seq_tup_read from pg_stat_user_tables where relname='events'"
docker exec -u postgres pgcmp psql -c "create index on events(user_id);" -c "analyze events;"
docker exec -u postgres pgcmp psql -c "explain (analyze, buffers) select count(*) from events where user_id=12345"

小结

同样返回 21 行，无索引时扫 200 万行、14720 个缓冲区块、24.6ms；有索引后扫索引 4 个块、0.07ms。行放大 9.5 万倍，块放大 3680 倍，延迟差 351 倍。

读放大最容易被忽略的地方是它藏在"看起来正常"的延迟里：24ms 的查询不会进慢查询日志，但它每秒跑 100 次就是 2.4 秒的 CPU 时间。查它不需要复杂工具，pg_stat_user_tables.seq_tup_read 和 EXPLAIN (ANALYZE, BUFFERS) 两个入口就够了。

另外记住第 3 节那个反例：选择性 20% 的 count(*) 仍然走了 index-only scan，而同样条件的 avg(amount) 退化成 bitmap heap scan。判断索引有没有用，看的是"要不要回表"，不是"返回多少行"。

数据来源与检索时间

检索时间 2026-11-10。全部耗时、缓冲区块数、pg_stat 计数均为本机实测。

来源URL对应数据
PostgreSQL 18 文档《Using EXPLAIN》https://www.postgresql.org/docs/18/using-explain.htmlBUFFERS 统计的是"命中的、读取的、弄脏的、写出的非去重缓冲区数量"且包含子节点；单页表几乎总走顺序扫描；ANALYZE 实际执行查询、Execution Time 的含义
PostgreSQL 18 文档《Monitoring Stats》https://www.postgresql.org/docs/18/monitoring-stats.htmlpg_stat_user_tables 与 pg_statio_user_tables 各列定义（seq_tup_read、idx_tup_fetch、heap_blks_read 等）

核验说明

  • 100 次批量执行、单查询 EXPLAIN (ANALYZE, BUFFERS)、pg_stat_reset() 后的计数均为本机真实输出；EXPLAIN 行的宽度被截断过，时间与行数保持原值。
  • 未控制缓存状态：实验在 shared_buffers=256MB、数据 164MB 的条件下进行，第二次查询起数据基本在缓存里。因此 24.6ms 与 0.07ms 比的是"缓存内扫描量差异"，不是磁盘 I/O 差异；真实冷盘上绝对值会更大，但数量级关系不变。
  • 没有测：多列索引、部分索引、以及 status 字段上的 select *（那会走 bitmap heap scan 或顺序扫描，结论与第 3 节一致但没有单独实测）。
#数据库
返回文章列表