PostgreSQL / 系统课时
判断索引是否有效
判断索引是否有效
本课解决“索引已经创建,但到底有没有加速查询”的问题。你将结合执行计划、数据选择性和缓冲区读取量,判断索引是否被使用以及是否值得保留。
学习目标
- 使用
EXPLAIN ANALYZE查看真实执行路径 - 根据选择性判断顺序扫描是否合理
- 比较索引前后的扫描节点和缓冲区开销
先看执行计划,不凭感觉判断
EXPLAIN 展示优化器预计采用的计划,EXPLAIN ANALYZE 会真实执行 SQL,并补充实际行数和耗时。排查索引时,应重点观察扫描类型、实际返回行数、过滤掉的行数以及缓冲区访问量。常见扫描节点包括:
Seq Scan:顺序读取表中的数据页Index Scan:沿索引定位并回表读取Index Only Scan:主要从索引获取结果,是否回表取决于可见性映射Bitmap Index Scan与Bitmap Heap Scan:先收集多个位置,再批量访问数据页
比较创建索引前后的计划
下面构造 10 万行数据,并查询一个选择性较高的分类值。示例输出中的缓冲区数量会因版本和环境不同而变化,因此以核心节点变化为判断标准。
-- 重建演示表,保证两次计划可以直接比较
DROP TABLE IF EXISTS demo_events;
CREATE TABLE demo_events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
category_id integer NOT NULL,
payload text NOT NULL
);
-- 1000 个分类平均分布,每个分类约 100 行
INSERT INTO demo_events (category_id, payload)
SELECT
g % 1000,
repeat('x', 50)
FROM generate_series(1, 100000) AS g;
ANALYZE demo_events;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, payload
FROM demo_events
WHERE category_id = 42;
-- 建索引后再次收集统计信息并执行同一查询
CREATE INDEX idx_demo_events_category
ON demo_events (category_id);
ANALYZE demo_events;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, payload
FROM demo_events
WHERE category_id = 42;预期结果
创建索引前:
核心节点为 Seq Scan on demo_events。
实际返回约 100 行,并过滤其余大部分行。
创建索引后:
核心节点通常为 Bitmap Heap Scan 或 Index Scan。
下层出现 idx_demo_events_category,并且访问的数据页明显减少。具体扫描类型可能因 PostgreSQL 版本、缓存状态和成本参数不同而变化,但索引后不应继续扫描绝大多数无关数据页。
选择性决定索引收益
选择性表示条件能排除多少数据。查询 category_id = 42 只返回约千分之一的数据,通常适合索引。
如果条件返回表中大部分行,例如布尔列中 90% 都是 true,顺序扫描可能更快。因为随机回表读取大量数据页的成本,可能高于连续扫描整张表。
因此,“出现 Seq Scan”不等于索引无效。要结合返回比例、表大小和实际耗时判断。
检查估算是否准确
执行计划中的预计行数与实际行数差距很大时,优化器可能做出错误选择。先执行 ANALYZE table_name 更新统计信息,再重新查看计划。
对于数据分布高度倾斜或多列强相关的场景,可以提高统计目标或创建扩展统计信息,但应先确认问题确实来自估算偏差。
识别无效或重复索引
索引长期未被扫描、与其他索引完全重复,或只服务极低频查询时,可能不值得保留。可以查看 pg_stat_user_indexes 中的 idx_scan,但该计数会在统计重置或实例重启后变化,不能单独作为删除依据。
删除索引前应覆盖业务高峰周期,并确认它没有用于唯一性约束、外键关联查询、定时任务或低频关键报表。