如何通过PostgreSQL EXPLAIN BUFFERS分析缓存命中情况？

PostgreSQL 的 EXPLAIN 命令是分析查询性能的核心工具，而加上 BUFFERS 选项后，执行计划会额外展示共享缓冲区命中与磁盘读取的详细计数。对于排查 I/O 瓶颈、判断查询是否真正利用了内存缓存，这些页面级别的指标比单纯的执行时间更有价值。下面以 EXPLAIN (ANALYZE, BUFFERS) 为例，深入解读缓存命中分析的方法。

在实际使用中，BUFFERS 输出由若干个字段组成，每个字段都对应一类页面的流转。理解这些字段的准确含义，是进行缓存命中分析的前提。

一、EXPLAIN BUFFERS 输出的关键指标

当执行 EXPLAIN (ANALYZE, BUFFERS) SELECT ... 命令时，执行计划中每个节点下方会出现类似 Buffers: shared hit=125 read=30 dirtied=2 written=0 的信息。这里 shared hit 表示从共享缓冲区中直接命中的页面数，进程无需发起磁盘 I/O；read 表示从磁盘或操作系统页缓存中读取到共享缓冲区的页面数，即物理读或逻辑读中未命中共享缓冲区的部分。两者之和就是该节点总共访问的数据页数量。

dirtied 表示查询执行期间被标记为脏页的缓冲区数量，这些页面后续需要由后台进程或检查点写回磁盘。written 则是查询期间实际写回的页面数。如果查询涉及临时表，还会出现 local hit 和 local read 等字段，它们对应临时表缓冲区的访问情况。

当 SQL 涉及排序、哈希连接或大型聚合操作，且内存不足以容纳全部数据时，PostgreSQL 会把中间结果写入临时文件。此时输出中会出现 temp read 和 temp written，它们以页面为单位统计了临时文件的读写量。示例执行计划如下：

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, COUNT(*)
FROM orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id;

执行后可能得到类似下面的节点输出：

HashAggregate  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
  Buffers: shared hit=453 read=102 dirtied=12 written=0
  ->  Seq Scan on orders  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
        Filter: (order_date > (CURRENT_DATE - '30 days'::interval))
        Rows Removed by Filter: 7800
        Buffers: shared hit=453 read=102 dirtied=12 written=0

注意这个示例中的 read=102 意味着有 102 个页面没有在共享缓冲区中找到，需要从操作系统层或磁盘读取。如果该查询反复执行，而 read 始终很高，说明缓存命中率偏低。

二、缓存命中率的计算与常见误区

缓存命中率通常用 shared hit 除以 shared hit + read 来计算，结果越接近 1 表示缓存效果越好。例如上面的示例中，命中率为 453 / (453 + 102) ≈ 81.6%。对于在线事务处理系统，这个比例一般应保持在 95% 以上；对于分析型查询，首次运行时 read 偏高是正常现象，因为数据尚未进入缓冲区。

但缓存命中率并不是越高越好的绝对指标。一个常见的误区是单独看某一次执行计划就下结论。受 shared_buffers 大小、操作系统页缓存、并发查询等因素影响，同一条 SQL 首次执行和后续执行的 read 值可能相差巨大。尤其在生产环境中，如果数据库刚重启或者缓存被其他大查询挤占，read 会异常偏高。因此应当结合多次执行、不同时间段的统计信息来评估。

另一个误区是忽略 temp read 和 temp written。有些查询虽然 shared hit 很高，但存在大量的临时文件读写，这同样会造成性能问题。对这类查询而言，缓存命中率不能正确反映 I/O 压力。此时应该关注 work_mem 参数设置是否过小，导致排序或哈希操作频繁落盘。

可以借助系统视图来获取更宏观的缓存命中率。例如查询 pg_stat_database 中的 blks_hit 和 blks_read 字段，能够从全局角度计算数据库整体的缓存命中率：

SELECT datname,
       blks_hit,
       blks_read,
       round(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0), 4) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

这段 SQL 返回当前数据库累计的缓存命中比例。如果该值长期低于 0.95，说明共享缓冲区配置或查询模式存在问题。

三、定位高物理读的SQL与对象

要找到哪些 SQL 导致大量 read，可以借助 pg_stat_statements 扩展。该扩展会记录每条规范化 SQL 的总执行时间、调用次数以及共享缓冲区命中与读取量。开启扩展后，执行以下查询可以按磁盘读取量排序：

SELECT query,
       calls,
       shared_blks_read,
       shared_blks_hit,
       round(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0), 4) AS hit_ratio,
       mean_exec_time
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

这里 shared_blks_read 累计值越高，说明这条 SQL 对磁盘 I/O 的贡献越大。结合 mean_exec_time 和 calls 可以判断它是否属于高频且昂贵的查询。需要注意的是，pg_stat_statements 中的读取量是累计值，单次分析前可以先执行 SELECT pg_stat_statements_reset(); 清空统计，以便观察特定时间段内的变化。

如果已经定位到某张表或索引存在缓存未命中，可以使用 pg_statio_user_tables 和 pg_statio_user_indexes 视图查看表级的缓冲区统计。以下 SQL 列出缓存命中率最低的用户表：

SELECT relname,
       heap_blks_read,
       heap_blks_hit,
       round(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0), 4) AS heap_hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_hit_ratio ASC
LIMIT 10;

这类查询能够把问题从 SQL 层面进一步细化到物理对象，帮助 DBA 判断是否需要调整索引、增加内存或优化表结构。

四、优化缓存命中率的实践方法

提高缓存命中率的直接方式是增加 shared_buffers 参数的值。该参数决定 PostgreSQL 共享缓冲区的大小，通常设置为物理内存的 25% 到 40%。如果当前值过小，缓冲池无法容纳常用数据，就会频繁发生 read。修改参数后需要重启数据库服务生效。需要注意的是，shared_buffers 并不是越大越好，过大的缓冲池会增加检查点和恢复时间，并可能因为双缓存问题浪费内存。建议结合操作系统页缓存大小进行调整。

优化查询本身也能显著降低 read。例如为过滤条件中的列创建合适的索引，将顺序扫描改为索引扫描，可以大幅减少扫描的页面数。对于只需要少量列的场景，使用覆盖索引避免回表，也能有效降低物理读。此外，调整 work_mem 和 maintenance_work_mem 可以减少临时文件的读写，从而间接提升缓存效率。

对于关键表或经常访问的索引，可以使用 pg_prewarm 扩展在数据库启动或低峰期提前加载到共享缓冲区。例如：

SELECT pg_prewarm('orders', 'buffer');

这条语句把 orders 表的数据页加载到缓冲区，之后查询该表时 read 会大幅下降。不过预热操作本身会占用内存，应当只针对高频访问的核心对象使用。

五、案例分析：从高read到高hit的调整

假设某电商系统出现一条统计近 30 天订单的 SQL，执行计划显示大量 read。使用 EXPLAIN (ANALYZE, BUFFERS) 后发现，Seq Scan on orders 节点的 read=1450，而 shared hit=320。这说明优化器选择了全表扫描，导致大量未缓存页面从磁盘读取。

进一步检查表结构发现，orders 表在 order_date 列上没有索引。创建索引后，同样的查询改为索引扫描，只扫描近 30 天的数据页。重新执行计划显示 Index Scan using idx_orders_order_date 节点的 shared hit=210 read=35，命中率提升到 85% 以上，执行时间从 2.4 秒降到 0.3 秒。

这个案例说明，缓存命中率低通常不是单纯的内存问题，更多时候是访问路径不合理导致扫描了过多的冷数据。通过结合 EXPLAIN BUFFERS 的输出、表级统计信息和查询特征，可以找到真正的瓶颈并采取针对性优化。只要掌握了这些指标的含义和排查思路，缓存命中分析就会成为一项高效的性能调优手段。

PostgreSQL EXPLAIN缓存命中率BUFFERS修改时间：2026-08-23 01:19:34