数据库索引维护：膨胀、重建与无用索引清理

很多团队对索引的态度是：上线前找 DBA 加几个索引，上线后就把这事忘了。直到某天监控告警——磁盘涨了 40%、慢查询暴增、写入吞吐掉了一半——才想起来排查。复盘时往往发现：那个 80GB 的索引实际有用数据只有 20GB，那三个"为了保险"加的索引一次都没被命中过，而真正该建的复合索引却没有。

索引不是一次性投资，而是一个会膨胀、会失效、会持续吃资源的活物。这篇文章把索引维护拆成三件你必须定期做的事：检测膨胀、清理无用/冗余索引、在线重建。所有示例基于 PostgreSQL（PG 的索引维护工具链最完整，也是这套方法论的原产地），但思路对 MySQL、SQL Server 同样适用。

一、先建立正确的心智模型：索引是一种"负债"

很多开发者下意识觉得"索引越多越好"，因为读快。这是个昂贵的误解。正确的模型是：

每一个索引都是一笔负债，不是资产。它用写入速度和存储空间，换取查询速度。

具体来说，表上每多一个索引：

• 每次 INSERT：除了写堆数据，还要写一遍这个索引的 B-tree 页。N 个索引 = N+1 次写入。
• 每次 UPDATE 被索引的列：PG 的 MVCC 机制下，UPDATE = 标记旧行 + 写新行，所有索引都会新增一条指向新行的指针（哪怕你只更新了一列）。这是 PG 膨胀的头号元凶。
• 每次 DELETE：索引项不会立即回收，留下死元组，直到 VACUUM 清理。
• 存储：索引要占磁盘，备份要变大，复制流量要增加，缓存（buffer cache）要被挤占。

所以索引不是"建了再说"。每加一个索引，你都要回答：它换来什么样的查询加速，值不值这个写代价。 更重要的是——这个"值不值"是会随时间变化的。今天用得多的索引，下个版本可能就没人查了。这就是为什么索引需要定期体检，而不是一劳永逸。

二、索引膨胀：你的索引比你以为的大得多

2.1 膨胀是怎么产生的

PG 的索引（B-tree）在频繁 UPDATE/DELETE 后会产生碎片和死元组：

• DELETE 留下空洞；
• UPDATE（PG 的 MVCC）会先删后插，索引里留下指向旧行版本（dead tuple）的指针，VACUUM 清理后页面出现空闲空间；
• B-tree 的页面分裂（page split）会让索引结构稀疏；
• 长事务、过低的 autovacuum 频率会让死元组堆积得更久。

结果是：索引逻辑大小正常，但物理占用远超应有体积。一个典型症状是 pg_relation_size 远大于"理论上需要的体积"，索引深度（level）变高，IO 增加，缓存命中率下降。

膨胀不是病，但它是性能和成本的慢性毒药。我见过最夸张的案例：一个 120GB 的订单表索引，REINDEX 后只有 18GB。也就是说 85% 的空间是碎片和死元组——这些空间每秒都在被读进内存、被 WAL 记录、被复制到从库、被备份进快照。

2.2 用 pgstattuple 精确检测

PG 的扩展 pgstattuple（以及配套的 pgstatindex）能直接采样统计索引/表的膨胀情况，给出真实的"空闲比例"。

先装扩展（每个库一次）：

CREATE EXTENSION IF NOT EXISTS pgstattuple;

检查某个索引的膨胀（返回 free_space 占比等指标）：

SELECT * FROM pgstattuple('orders_idx_user_id_created_at');

pgstatindex 专门给 B-tree 用，能看到 version、tree_level、root_pages、internal_pages、leaf_pages、deleted_pages：

SELECT * FROM pgstatindex('orders_idx_user_id_created_at');

deleted_pages 高 = 大量被删除但未复用的页面；tree_level 持续升高 = 索引深度在涨，该重建了。

2.3 一次扫遍全库的膨胀报告

逐个索引查不现实。下面这段 SQL 用 pgstattuple_approx（更快，采样版）扫一遍库里的表，输出膨胀 Top 10，适合定期巡检：

SELECT
    schemaname || '.' || relname       AS relation,
    pg_size_pretty(table_len)          AS total_size,
    ROUND(dead_tuple_percent::numeric, 2) AS dead_pct,
    ROUND(free_percent::numeric, 2)    AS free_pct,
    pg_size_pretty(dead_tuple_len)     AS dead_bytes,
    pg_size_pretty(free_space)         AS free_bytes
FROM pgstattuple_approx('public'::regclass)  -- 替换为目标表，或写循环
ORDER BY (dead_tuple_len + free_space) DESC
LIMIT 10;

注意：pgstattuple 全表扫描代价不低，生产库建议在从库上跑，或选择低峰期，或用 pgstattuple_approx（近似但快）。pgstattuple_approx 只支持表不支持索引，要查索引本身得用 pgstatindex。

经验阈值（我团队的内部红线，仅供参考）：

• free_percent > 30% 或 死元组 + 空闲空间占物理体积 > 40%：列入重建候选清单。
• tree_level 比基线涨了 1 级以上：重建。
• 不必追求 0 膨胀——B-tree 本来就需要一定空闲率来避免频繁分裂。目标是控住"异常膨胀"，而不是洁癖。

三、重建索引：REINDEX CONCURRENTLY 是生产唯一选项

检测出膨胀之后，重建就是把它压实。但怎么重建比建不建更关键——在生产库上千万别用裸 REINDEX。

3.1 为什么不能用普通 REINDEX

-- 生产库禁止这样做：
REINDEX INDEX orders_idx_user_id_created_at;

普通 REINDEX 会拿排他锁（AccessExclusiveLock），锁表期间连 SELECT 都进不来，等于把这个表从服务里摘掉几秒到几十分钟。在 7×24 在线业务里，这是事故级操作。

3.2 REINDEX CONCURRENTLY：在线重建

PG 12+ 提供了 REINDEX CONCURRENTLY，原理是：

1. 用新定义建一个临时索引（_ccnew 后缀），和原索引并行存在；
2. 边读边写——把表里所有现有数据索引进新索引，同时所有新的 INSERT/UPDATE/DELETE 同时维护新旧两个索引；
3. 建完做一次"catch-up"，把并发产生的增量补齐；
4. 原子替换：rename 新索引为原索引名，drop 旧索引。

整个过程不阻塞读写，是生产环境唯一应该用的方式：

-- 生产正确姿势
REINDEX INDEX CONCURRENTLY orders_idx_user_id_created_at;

-- 也可以重建某个表的所有索引
REINDEX TABLE CONCURRENTLY orders;

-- 或者整个库（慎用，耗时长，建议分表分批）
REINDEX DATABASE CONCURRENTLY your_db;

代价和注意点：

• 存储翻倍：重建期间新旧两个索引同时存在，需要和原索引等量的空闲空间。磁盘水位高的库要提前评估。
• 写入有放大：重建期间每次写要维护两份索引，写入吞吐会下降。错峰执行。
• 可能失败：如果重建期间遇到唯一约束冲突或死锁，CONCURRENTLY 会留下一个 INVALID 的 _ccnew 索引。重建完一定要检查并清理：

-- 找重建失败的残留索引
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;
-- 确认无用后清理（CONCURRENTLY 重建无效索引必须用 CONCURRENTLY drop）
DROP INDEX CONCURRENTLY IF EXISTS orders_idx_user_id_created_at_ccnew;

经验：大表（百 GB 起）的 CONCURRENTLY 重建可能跑几小时，建议先在从库上验证耗时，再在主库低峰执行；同时盯 IO、长事务和复制延迟。如果库太大、停不起写入放大，用 pg_repack / pg_squeeze 这类第三方工具，它们能更精细地控制重建节奏。

3.3 什么时候重建，什么时候不重建

不是所有膨胀都要重建——重建是有成本的动作，不是日常 chore。

• ✅ 膨胀 > 阈值 且 该索引是热点路径（高频查询命中、或缓存命中率明显下降）
• ✅ 索引深度（tree_level）异常升高
• ✅ 准备做一次大表压缩/迁移前的例行清理
• ❌ 刚 VACUUM 完死元组就少了，等两轮 autovacuum 再看
• ❌ 该索引本身就要删（见第四节）——删比重建省事一百倍

调高 autovacuum 的频率（对大表单独设置 autovacuum_vacuum_scale_factor）可以从源头减少死元组堆积，降低重建频率。这是比"定期 REINDEX"更治本的做法。

四、无用索引：idx_scan=0 的沉默成本

膨胀要重建，但有一类索引连重建都不值得——压根没人用的索引。它们默默占着空间、拖慢每次写入，却从不被查询命中。

4.1 用 pg_stat_user_indexes 找零使用索引

PG 的累计统计视图 pg_stat_user_indexes 记录每个索引被扫描的次数：

SELECT
    schemaname,
    relname        AS table_name,
    indexrelname   AS index_name,
    idx_scan       AS scans,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  AND idx_scan = 0          -- 自上次统计重置以来零扫描
  AND indexrelname NOT LIKE '%_pkey'  -- 排除主键（约束/外键依赖，别乱删）
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 的索引就是"建了从没人用"的嫌疑犯。按体积排序，优先看大的——删一个 20GB 的无用索引比删十个 1MB 的有意义得多。

4.2 三个务必注意的坑

idx_scan = 0 不等于"可以直接删"。在动手前要排除三种误杀：

1. 统计被重置过。pg_stat_user_indexes 的计数从 stats_reset 时间点开始累计。刚重启过库、或手动 pg_stat_reset() 过，计数会很短。先看一眼 pg_stat_database.stats_reset，确认统计窗口够长（至少一个完整的业务周期，建议 30 天以上）：

   SELECT stats_reset, now() - stats_reset AS window
   FROM pg_stat_database WHERE datname = current_database();
   

2. 周期性查询的索引。月底报表、季度对账、年度归档用到的索引，平时 idx_scan=0，删了下个月就炸。结合业务周期判断——至少观察一个完整的业务高频周期。

3. 被约束/唯一性隐式使用的索引。唯一索引、主键索引即使没有被查询显式命中，也在每次 INSERT/UPDATE 时承担唯一性校验。永远不要碰 _pkey 和带唯一约束的索引。删之前确认：

   -- 确认这个索引不被任何约束、外键、CLUSTER 依赖
   SELECT conname, contype
   FROM pg_constraint
   WHERE conindid = 'orders_idx_user_id_created_at'::regclass;
   

   有结果就别删——它是某个约束的物理载体。

4.3 删除也要 CONCURRENTLY

删索引同样会拿锁，生产库用：

DROP INDEX CONCURRENTLY IF EXISTS orders_idx_user_id_created_at;

CONCURRENTLY 的 DROP 不阻塞读写。删除后建议观察一两个业务周期，确认没有隐藏的查询路径退化（比如某个 ORM 生成的 SQL 之前依赖这个索引），再正式从迁移脚本和文档里清掉。

五、冗余索引：(a,b,c) 已经覆盖了 (a,b)

比"无用索引"更隐蔽的是冗余索引——它可能被用到，但它的功能已经被另一个更大的索引完全覆盖，留着纯属浪费。

5.1 最左前缀原则下的冗余

B-tree 复合索引遵循最左前缀匹配。如果你有 (user_id, created_at, status)，那么以 user_id 开头的查询都能用它，包括：

WHERE user_id = ?                       -- 命中 (user_id, ...)
WHERE user_id = ? AND created_at > ?    -- 命中 (user_id, created_at, ...)
WHERE user_id = ? AND created_at > ? AND status = 1  -- 完整命中

这意味着，如果你同时还有 (user_id, created_at) 或 (user_id) 的独立索引，它们大概率是冗余的——SELECT 优化器会优先选更"窄"的索引，但写代价却是双份。

5.2 找冗余索引的查询

下面这段 SQL 找出"前缀被另一个索引完全包含"的索引：

SELECT
    a.relname        AS table_name,
    a.indexrelname   AS redundant_index,
    b.indexrelname   AS covering_index,
    pg_size_pretty(pg_relation_size(a.indexrelid)) AS redundant_size
FROM pg_index a
JOIN pg_index b
  ON a.indrelid = b.indrelid
 AND a.indexrelid <> b.indexrelid
 AND a.indkey <@ b.indkey          -- a 的列是 b 的前缀子集
 AND b.indkey @> a.indkey
 AND a.indisunique = false         -- 非唯一（唯一的不能轻易删）
 AND b.indpred IS NULL             -- b 不是部分索引
 AND a.indpred IS NULL             -- a 也不是部分索引
JOIN pg_class c ON c.oid = a.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(a.indexrelid) DESC;

逻辑：a 的列集合是 b 的子集（用 indkey 数组的 @> <@），且 b 不是部分索引（否则覆盖关系不一定成立），且 a 非唯一。命中即冗余候选。

坑提示：部分索引（partial index）和包含列（INCLUDE）的索引不能用这套简单判断。 (a) WHERE active = 1 和 (a) 不是一回事；INCLUDE 列不参与 B-tree 排序也不算进 indkey 匹配。遇到这类索引要人工核一眼。

5.3 另一类冗余：完全重复

两个索引定义完全一样（开发者用不同名字建了两次）。直接查：

SELECT indexrelname, pg_get_indexdef(indexrelid)
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_get_indexdef(indexrelid);

肉眼扫一遍定义列，相同的就留一个删一个。这类冗余在小团队/多分支并行开发的项目里很常见，是低垂果实。

六、写代价的账，要算在加索引的那一刻

回到第一节那个心智模型：每个索引都是写入的负债。 这一节给个具体数字感。

假设你有一张表，10 个索引，写入 QPS 5000：

• 每次 INSERT 要写 1 份堆 + 10 份索引 = 11 次写入；
• 每次 UPDATE（无论改哪个列，因为 MVCC）也是 11 次写入；
• 50% 的索引如果是无用/冗余的，意味着 每秒 2.5 万次写入是纯浪费。

更隐蔽的成本：

• WAL 膨胀：所有写入都要记 WAL，索引越多 WAL 越大，主从复制带宽、归档存储成本随之上涨。
• 缓存挤占：无用索引同样占 buffer cache，把热点数据挤出内存，间接拉低整体缓存命中率。
• autovacuum 变慢：VACUUM 要清理所有索引的死元组，索引越多越慢，反过来又加剧膨胀——恶性循环。

所以加索引时的正确动作：

1. 先 explain 分析查询，确认没有现成索引可用（避免重复建设）；
2. 评估该索引的查询收益是否能覆盖写入成本（高频读、低频写的表值得；高频写表要慎之又慎）；
3. 上线后在 pg_stat_user_indexes 里盯它两周——idx_scan 不如预期就撤；
4. 把它写进索引文档/迁移注释，标明用途，方便后人判断是否冗余。

不加索引的成本是读变慢，加索引的成本是写变慢 + 存储变大。永远在做权衡，没有免费的午餐。

七、一套可落地的定期索引体检方案

把前面散落的方法整合成一个每月一次的索引体检流程。我团队内部跑了两年，平均每次能清出 5%~15% 的存储、堵掉若干潜在慢查询。

7.1 体检脚本（可在从库上跑）

-- ============ 索引体检报告 ============
-- 前置：CREATE EXTENSION IF NOT EXISTS pgstattuple;

\echo '\n=== [1] 统计窗口检查（必须 >= 30 天）==='
SELECT stats_reset,
       now() - stats_reset AS window,
       now() - stats_reset >= interval '30 days' AS ok
FROM pg_stat_database WHERE datname = current_database();

\echo '\n=== [2] 大体积索引 Top 20（重点关注对象）==='
SELECT schemaname, relname, indexrelname,
       idx_scan, idx_tup_read,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

\echo '\n=== [3] 无用索引（idx_scan=0，排除主键/约束）==='
SELECT s.schemaname, s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       pg_get_indexdef(s.indexrelid) AS def
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND i.indisunique = false          -- 非唯一
  AND s.indexrelname NOT LIKE '%_pkey'
  AND NOT EXISTS (                   -- 不被任何约束依赖
      SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid)
  AND s.schemaname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_relation_size(s.indexrelid) DESC;

\echo '\n=== [4] 冗余索引（前缀被覆盖）==='
-- （用第五节的查询，这里略）

\echo '\n=== [5] 膨胀检测（对 Top 10 大索引逐一 pgstatindex）==='
-- 对每个大索引手动跑：
-- SELECT * FROM pgstatindex('schema.idx_name');
-- 关注 free_percent / deleted_pages / tree_level

7.2 处理优先级（我团队的红黄绿）

按"投入产出比"排序，别一上来就想着重建所有膨胀索引：

7.3 把它变成制度

• 每月一次跑体检脚本，输出 Markdown 报告，存档对比趋势（索引体积、idx_scan 分布的变化比绝对值更有意义）。
• 每次大版本/大重构上线后额外跑一次——查询模式变了，索引的有效性也跟着变。
• DBA + 后端 owner 一起 review——只有写代码的人知道某个索引对应的业务是否还在，只有 DBA 知道它的物理代价。两边对齐才能下决策。
• 建立索引台账：每个索引在迁移注释里标明"用途 / 对应查询 / 建立时间"，体检时按台账核对。

小结

索引维护不是"出问题再救火"，而是和监控告警一样该常态化的事。记住几条：

1. 索引是负债不是资产——每加一个，都在用写入速度和存储换查询速度，权衡要算清。
2. 膨胀用 pgstattuple 检测，关注 free_percent 和 tree_level；别追求 0 膨胀，控住异常膨胀即可。
3. 重建永远用 REINDEX CONCURRENTLY——普通 REINDEX 在生产等于自残，会锁表。
4. 无用索引看 idx_scan=0，但先确认统计窗口够长、排除周期性查询和约束依赖，再用 DROP INDEX CONCURRENTLY 清理。
5. 冗余索引看最左前缀，(a,b,c) 覆盖 (a,b)，后者大概率可删；部分索引和 INCLUDE 列要人工核对。
6. 写一套月度体检脚本，按"删无用 → 删冗余 → 重建热点"的优先级处理，把索引维护变成制度而不是灵感。

金句：索引不是建完就结束的工程动作，而是一笔需要持续审计的负债——你以为它在帮你，它可能正在默默吃掉你的写入吞吐和存储预算。定期体检，该删的删，该压的压，索引才能一直为你工作，而不是你为它打工。