SQL Academy · 课时

索引维护与膨胀

Q: 「索引维护与膨胀」这节课中我会学到什么？

诊断索引膨胀，使用 REINDEX CONCURRENTLY 重建索引，并安全地删除未使用的索引 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

诊断索引膨胀，使用 REINDEX CONCURRENTLY 重建索引，并安全地删除未使用的索引

索引维护与膨胀 是 CoddyKit 上的免费 SQL Academy 课时。这是第 4 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

索引为什么会膨胀

PostgreSQL 使用 MVCC：UPDATE 会写入一个新的行版本，同时保留对现有事务可见的旧版本。两个版本的索引条目都会存在。随着时间推移：

频繁更新的表会积累无效索引条目
索引会变得比必要大小更大
随着 B 树变得更深，查找会变慢

诊断索引膨胀

检查膨胀的查询并不简单。常用工具包括：

pgstattuple 扩展
pg_repack 报告
来自 check_postgres 或监控工具的膨胀查询

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');

REINDEX

重建索引。经典形式会获取 ACCESS EXCLUSIVE 锁——在生产环境中不理想：

REINDEX INDEX orders_user_id_idx;       -- blocks writes!

REINDEX CONCURRENTLY（PG 12+）

非阻塞版本——重建期间读写操作可以继续：

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

未使用的索引

未使用的索引会拖慢每次写入，却不会加快任何读取。查找它们：

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

删除未使用的索引

删除它们——但请先在所有环境和时间范围内进行确认。偶尔用于报告的索引大部分时间看起来都未被使用。

DROP INDEX CONCURRENTLY old_unused_idx;

重复索引

有时同一个索引既由约束创建，又由手动的 CREATE INDEX 创建。请检查 pg_indexes 中是否存在重复项，并删除多余的索引：

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

索引写放大

每次 INSERT/UPDATE/DELETE 都会更新所有相关索引。在一个高频使用的表上建立三个索引 = 3 倍的写入成本。只添加真正有收益的索引。

GIN 待处理列表

GIN 索引会将更新批量放入待处理列表。您可以手动刷新，也可以依赖自动清理：

SELECT gin_clean_pending_list('events_data_gin');

VACUUM 清理索引条目

VACUUM（在 MVCC 课程中介绍）会从堆页面中删除无效索引条目。如果没有自动清理，索引会无限增长。

监控索引大小

随时间跟踪索引大小：

SELECT pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

pg_repack：在线重写表

对于严重的膨胀，pg_repack 可以在线重写表和索引——不会获取整张表的锁。请将其作为 OS 软件包和 PostgreSQL 扩展安装。

总结

索引需要维护。

MVCC 导致的膨胀是正常现象——使用 VACUUM 和 REINDEX CONCURRENTLY 进行管理
删除未使用的索引
避免重复索引
每新增一个索引都会拖慢写入——请谨慎设计

快速检查

哪个 PostgreSQL 命令可以在不阻塞写入的情况下重建索引？

常见问题解答

「索引维护与膨胀」课时是免费的吗？

是的 — 「索引维护与膨胀」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「索引维护与膨胀」这节课中我会学到什么？

诊断索引膨胀，使用 REINDEX CONCURRENTLY 重建索引，并安全地删除未使用的索引你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 4 节课，共 4 节。

「索引维护与膨胀」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy