PostgreSQL / 系统课时
比较索引收益与维护代价
比较索引收益与维护代价
索引不是建得越多越好,而是要证明它长期带来的查询收益超过写入、空间和维护成本。本课用可观测指标建立评估方法,最终能判断一个索引该保留、优化还是删除。
学习目标
- 能从查询性能和扫描量判断索引是否产生实际收益
- 能识别索引带来的写入、空间与维护成本
- 能用系统视图和查询计划形成保留或删除索引的证据
先看索引是否真的被使用
判断索引价值不能只看它是否存在,应先看实际访问次数,再结合执行计划确认它解决了什么问题。pg_stat_user_indexes 可以提供索引扫描次数以及通过索引返回的元组统计。
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;预期结果
table_name | index_name | idx_scan | idx_tup_read | idx_tup_fetch | index_size
------------+-------------------+----------+--------------+---------------+-----------
orders | orders_pkey | 120000 | 120000 | 120000 | 24 MB
orders | idx_orders_status | 18 | 260 | 90 | 16 MBidx_scan 很低并不自动等于索引无用,因为统计信息可能只覆盖最近一段时间,而且某些查询是低频但高价值的关键路径。因此还要结合业务高峰、执行计划和查询出现频率判断。
把收益和成本放在一起比较
索引的收益通常体现为减少扫描行数、降低延迟或避免排序;成本则主要来自每次写入时维护索引条目、额外磁盘空间以及后续 VACUUM、备份和存储管理负担。
可以用以下思路做判断:
| 维度 | 观察信号 | 典型结论 |
|---|---|---|
| 查询收益 | idx_scan、执行时间、扫描行数下降 |
越稳定越值得保留 |
| 写入开销 | INSERT/UPDATE/DELETE 频率高、索引列经常变化 | 维护成本会上升 |
| 空间占用 | pg_relation_size |
大索引会增加存储与备份成本 |
| 维护成本 | VACUUM、重建、统计信息维护 | 高频变更表成本更明显 |
尤其要注意更新操作:如果被索引覆盖的列发生变化,数据库需要维护索引项;同一张高写入表上堆叠多个低收益索引,很容易让写放大和存储成本持续累积。
用执行计划确认索引解决了什么
统计视图只能告诉你“用过多少次”,不能证明“用得值不值”。对于关键 SQL,应直接查看执行计划,确认索引是否让访问路径更短。
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at
FROM orders
WHERE status = 'PAID'
AND created_at >= TIMESTAMP '<TIME>'
ORDER BY created_at DESC
LIMIT 50;预期结果
Limit
-> Index Scan using <INDEX> on orders
Index Cond: (created_at >= '<TIME>')
Filter: (status = 'PAID')
Planning Time: <TIME> ms
Execution Time: <TIME> ms实际执行计划会因数据分布、统计信息和索引定义不同而变化,因此不要把示例中的节点名称当作固定输出。重点是确认实际扫描量、缓冲区访问和执行时间是否得到改善。
什么时候应该考虑删除
删除索引前至少确认三个条件:长期没有稳定查询收益、存在更合适的重复或覆盖索引、删除后不会影响关键业务窗口。对生产系统不要仅凭一次 idx_scan = 0 就直接删除,先覆盖完整业务周期,并排除统计重置、冷数据查询和低频后台任务。
一个可操作的决策标准是:收益有证据、成本可量化、删除可回滚。当索引很少被使用,却持续占据大量空间并增加高频写入维护成本时,它才真正具备较强的清理价值;反之,低频但关键的索引应优先保留并持续验证。