SQL如何用EXPLAIN (ANALYZE, BUFFERS)分析IO与缓存命中

在数据库查询性能优化场景中，IO消耗和缓存命中率是影响查询速度的核心因素，PostgreSQL提供的EXPLAIN (ANALYZE, BUFFERS)命令可以在执行实际查询的同时，输出包含IO和缓存相关的详细统计信息，帮助开发者精准定位性能问题。

EXPLAIN (ANALYZE, BUFFERS)基础用法

该命令的基本语法是在普通EXPLAIN语句基础上，添加ANALYZE和BUFFERS两个选项，ANALYZE会让数据库实际执行查询并返回真实的运行耗时，BUFFERS则会输出缓存相关的统计信息。示例如下：

-- 分析查询用户表中年龄大于30的用户信息的IO和缓存情况
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM user_info WHERE age > 30;

核心输出字段解读

执行上述命令后，除了常规的查询计划、预估行数等信息外，还会在输出中包含和IO、缓存相关的关键指标，主要包含以下几类：

1. 缓存命中相关指标

• shared hit：表示从共享缓冲区（shared buffer）中直接读取的数据块数量，这部分不需要访问磁盘，属于缓存命中
• shared read：表示从磁盘读取的数据块数量，属于实际的磁盘IO消耗
• shared dirtied：表示查询过程中被标记为脏块的数据块数量，这类块后续需要被刷入磁盘
• shared written：表示查询过程中被写入磁盘的数据块数量

2. IO耗时相关指标

• I/O Read Time：读取数据块的总磁盘IO耗时，单位是毫秒
• I/O Write Time：写入数据块的总磁盘IO耗时，单位是毫秒

实际案例分析

我们以一个没有索引的用户表查询为例，查看具体的输出信息并分析IO和缓存情况：

-- 创建测试表并插入10万条测试数据
CREATE TABLE test_user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);
INSERT INTO test_user (id, name, age)
SELECT generate_series(1,100000), 'user_' || generate_series(1,100000), (random()*100)::INT;

-- 执行分析命令
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM test_user WHERE age > 80;

上述查询的输出中，假设看到如下缓存相关统计：

Buffers: shared hit=10 read=442 I/O Read Time=12.345

这里shared hit为10，说明只有10个数据块是从缓存中读取的，shared read为442，说明需要从磁盘读取442个数据块，I/O Read Time为12.345毫秒，说明磁盘IO耗时占比较高。如果此时缓存命中率（shared hit/(shared hit+shared read)）只有2%左右，说明大部分数据都没有命中缓存，查询性能会受到磁盘IO的限制。

优化方向参考

根据上述分析结果，我们可以采取对应的优化措施：

• 如果shared read数值过高，且查询条件字段没有索引，可以为对应字段添加索引，减少需要扫描的数据块数量，比如上述案例可以为age字段添加索引：CREATE INDEX idx_test_user_age ON test_user(age);
• 如果缓存命中率整体偏低，可以检查数据库的shared_buffers配置，适当调大共享缓冲区的大小，让更多热数据留在内存中
• 如果I/O Read Time数值过高，可以检查磁盘性能，或者考虑将频繁查询的热数据迁移到性能更好的存储设备

注意事项

使用EXPLAIN (ANALYZE, BUFFERS)时需要注意，ANALYZE选项会实际执行查询，所以如果查询是UPDATE、DELETE等写操作，会对数据产生实际修改，测试时建议使用专门的测试库，或者在事务中执行后回滚。另外该命令是PostgreSQL的原生功能，其他数据库如MySQL有类似的EXPLAIN ANALYZE语法，但缓存相关的输出字段会有差异，需要根据对应数据库的文档调整解读方式。

SQLEXPLAIN_ANALYZE_BUFFERSIO分析缓存命中数据库优化修改时间：2026-06-26 12:51:25