PostgreSQL / 系统课时

比较索引收益与维护代价

比较索引收益与维护代价

索引不是建得越多越好,而是要证明它长期带来的查询收益超过写入、空间和维护成本。本课用可观测指标建立评估方法,最终能判断一个索引该保留、优化还是删除。

学习目标

  • 能从查询性能和扫描量判断索引是否产生实际收益
  • 能识别索引带来的写入、空间与维护成本
  • 能用系统视图和查询计划形成保留或删除索引的证据

先看索引是否真的被使用

判断索引价值不能只看它是否存在,应先看实际访问次数,再结合执行计划确认它解决了什么问题。pg_stat_user_indexes 可以提供索引扫描次数以及通过索引返回的元组统计。

sql
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;

预期结果

text
 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 MB

idx_scan 很低并不自动等于索引无用,因为统计信息可能只覆盖最近一段时间,而且某些查询是低频但高价值的关键路径。因此还要结合业务高峰、执行计划和查询出现频率判断。

把收益和成本放在一起比较

索引的收益通常体现为减少扫描行数、降低延迟或避免排序;成本则主要来自每次写入时维护索引条目、额外磁盘空间以及后续 VACUUM、备份和存储管理负担。

可以用以下思路做判断:

维度 观察信号 典型结论
查询收益 idx_scan、执行时间、扫描行数下降 越稳定越值得保留
写入开销 INSERT/UPDATE/DELETE 频率高、索引列经常变化 维护成本会上升
空间占用 pg_relation_size 大索引会增加存储与备份成本
维护成本 VACUUM、重建、统计信息维护 高频变更表成本更明显

尤其要注意更新操作:如果被索引覆盖的列发生变化,数据库需要维护索引项;同一张高写入表上堆叠多个低收益索引,很容易让写放大和存储成本持续累积。

用执行计划确认索引解决了什么

统计视图只能告诉你“用过多少次”,不能证明“用得值不值”。对于关键 SQL,应直接查看执行计划,确认索引是否让访问路径更短。

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;

预期结果

text
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 就直接删除,先覆盖完整业务周期,并排除统计重置、冷数据查询和低频后台任务。

一个可操作的决策标准是:收益有证据、成本可量化、删除可回滚。当索引很少被使用,却持续占据大量空间并增加高频写入维护成本时,它才真正具备较强的清理价值;反之,低频但关键的索引应优先保留并持续验证。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

比较索引收益与维护代价

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通