PostgreSQL / 系统课时

清理冗余与重复索引

清理冗余与重复索引

本课解决索引越建越多、重复维护成本不断上升的问题。你将学会从定义相似度、约束依赖、实际使用和删除风险四个角度判断一个索引能否安全移除,并形成可回滚的清理流程。

学习目标

  • 区分重复索引、被覆盖索引与长期低使用率索引
  • 使用系统目录和统计信息收集删除前证据
  • 在确认依赖与风险后安全执行索引清理

先区分三类“多余”索引

真正危险的不是索引数量多,而是多个索引承担相同工作却长期没有必要。重复索引通常指键列、顺序、谓词等关键定义一致;被覆盖索引则是一个更宽或更合适的索引已经能够支持另一个索引承担的查询路径;低使用率索引只表示当前统计周期内调用次数很少,不能直接等同于“无用”。

例如,(customer_id, created_at) 与 (customer_id) 并不是简单的“二选一”。前者可以支持以 customer_id 为前导列的条件,但后者可能因为更小、维护成本更低而仍有价值。判断时必须同时看查询、写入和存储成本。

收集索引定义与使用证据

先查看目标表有哪些索引,再结合 pg_stat_user_indexes 判断实际使用情况。下面示例假设目标表为 orders,实际名称请替换为你的对象。

sql
SELECT
    i.indexrelname AS index_name,
    i.idx_scan,
    i.idx_tup_read,
    i.idx_tup_fetch,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM pg_stat_user_indexes AS i
WHERE i.relname = 'orders'
ORDER BY pg_relation_size(i.indexrelid) DESC;

预期结果

text
 index_name              | idx_scan | idx_tup_read | idx_tup_fetch | index_size
-------------------------+----------+--------------+---------------+-----------
 orders_customer_created |     8421 |      1854321 |       923441  | 64 MB
 orders_customer         |        0 |            0 |            0  | 24 MB
 orders_status           |        3 |          120 |           31  | 16 MB

idx_scan=0 是一个重要线索,但不是删除结论。统计信息可能刚刚重置,业务高峰尚未到来,也可能查询一直使用了其他等价路径。尤其要确认统计周期,以及索引是否服务于不频繁但关键的后台任务。

检查定义是否真的重复

仅比较索引名称没有意义,应查看实际定义。pg_indexes 可以快速拿到 CREATE INDEX 定义,再判断键列、表达式、谓词和排序等是否一致。

sql
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY indexname;

预期结果

text
 schemaname | tablename |        indexname        |                         indexdef
------------+-----------+------------------------+------------------------------------------------------------
 public     | orders    | orders_customer        | CREATE INDEX orders_customer ON public.orders USING btree (customer_id)
 public     | orders    | orders_customer_copy   | CREATE INDEX orders_customer_copy ON public.orders USING btree (customer_id)
 public     | orders    | orders_customer_created| CREATE INDEX orders_customer_created ON public.orders USING btree (customer_id, created_at)

前两项属于典型的结构重复;第三项与前两项则是“前导列重叠”,但不能仅凭定义删除。还要结合查询计划、唯一性要求、排序需求和维护成本判断。

删除前先验证依赖与回滚路径

普通索引通常直接服务查询性能,但唯一索引可能承担唯一性约束,或者被约束对象依赖。因此清理前应确认目标索引是否与约束、主键或其他数据库对象存在依赖关系。发现它承担约束语义时,应按约束治理流程处理,而不是直接把它当成普通索引删除。

正式删除前还应记录索引定义、大小、统计信息和依赖检查结果。线上环境优先使用 DROP INDEX CONCURRENTLY 降低阻塞风险,但它不能放在事务块中执行,并且失败后可能留下异常状态,需要单独复核。

sql
DROP INDEX CONCURRENTLY IF EXISTS public.orders_customer_copy;

预期结果

text
DROP INDEX

删除后不要立即结束观察。至少重新确认目标索引已经不存在,并检查相关核心查询的执行计划和延迟指标。对于关键业务,最好先在业务低峰执行,并保留可重建索引的原始定义。

什么时候不要删除

有四种信号出现时,应暂停清理:统计周期明显不足;索引承担唯一性或其他约束语义;低频查询属于关键任务;删除后无法快速重建或无法验证业务影响。索引治理的目标不是追求“越少越好”,而是在查询收益、写入开销、存储成本和故障风险之间取得平衡。

最终可以把每个候选索引按“证据充分性 → 依赖检查 → 查询验证 → 变更方式 → 删除后验收”逐项闭环。只有当这些证据都支持删除时,冗余索引清理才真正具备工程上的安全性。

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

目录与阅读设置

记录一个想法

清理冗余与重复索引

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

登录哪都通