如何重建数据库表的索引？

完整指南与最佳实践

目录导读

1. 索引重建的核心概念与必要性
2. 何时需要重建索引？（性能预警信号）
3. 不同数据库的索引重建方法（SQL Server / MySQL / PostgreSQL）
4. 索引重建的三种操作模式对比（重建、重组、重新组织）
5. 重建索引的常见陷阱与避坑指南
6. 问答环节：解决你可能遇到的5个高频问题

索引重建的核心概念与必要性

索引碎片化 是数据库性能下降的隐形杀手，当你对表进行频繁的INSERT、UPDATE、DELETE操作后，索引页的逻辑顺序会与物理顺序脱节，形成碎片，就像一本被反复撕掉、插入新页的百科全书，页码虽然连续,但实际翻找时需要跳跃大量书页。

重建索引的本质 是删除原有索引并从头创建新索引，这能彻底整理碎片、更新统计信息，并释放被浪费的存储空间，在OLTP（在线事务处理）系统中，超过30%的碎片率就会显著增加I/O成本；而在OLAP（分析型）场景，甚至10%的碎片就可能导致查询计划失效。

何时需要重建索引？（性能预警信号）

1 通过碎片化程度判断

2 其他典型症状

• 查询执行时间突然增加50%以上，但数据量未大幅增长
• 某查询的IO统计显示逻辑读取次数远高于物理读取次数
• 碎片导致索引统计信息过时，造成SQL Server选择错误的执行计划

不同数据库的索引重建方法

1 SQL Server（适用于2016及以上版本）

-- 在线重建（不阻塞读写）
ALTER INDEX [IX_YourIndex] ON [dbo].[YourTable] REBUILD WITH (ONLINE = ON)
-- 离线重建（阻塞写入，更快完成）
ALTER INDEX [IX_YourIndex] ON [dbo].[YourTable] REBUILD
-- 批量重建库中所有索引（生产慎用）
EXEC sp_MSforeachtable @command1 = 'ALTER INDEX ALL ON ? REBUILD'

2 MySQL（InnoDB引擎）

InnoDB不支持直接重建单个索引,但可通过以下方式实现：

-- 方法1：使用OPTIMIZE TABLE（本质是重建表 + 索引）
OPTIMIZE TABLE your_table;
-- 方法2：删除并重新创建索引（适合大表）
ALTER TABLE your_table DROP INDEX idx_old;
ALTER TABLE your_table ADD INDEX idx_new (column1, column2);
-- 方法3：使用pt-online-schema-change（Percona Toolkit，零停机）
pt-online-schema-change --alter "ENGINE=InnoDB" D=db_name,t=table_name

3 PostgreSQL

-- 重建单个索引（支持并发重建）
REINDEX INDEX CONCURRENTLY idx_name;
-- 重建整个表的所有索引
REINDEX TABLE CONCURRENTLY table_name;
-- 检查索引碎片情况
SELECT schemaname, tablename, indexname, 
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
       avg_leaf_density
FROM pg_stat_user_indexes;

索引重建的三种操作模式对比

重建索引的常见陷阱与避坑指南

陷阱1：在业务高峰期重建索引
重建索引会消耗大量IO和CPU，可能导致系统响应超时,建议在维护窗口期内操作。

陷阱2：假设重建后性能必然提升
索引重建不是“万能银弹”，如果查询本身设计不佳（如缺少覆盖列、数据倾斜严重）,重建也无法解决问题。

陷阱3：忽略日志文件暴增风险
在SQL Server中，完整恢复模式下重建索引会写入大量事务日志,需提前预留足够日志空间或切换为简单恢复模式。

陷阱4：认为索引数量越多越好
每张表建议保持5-7个索引，过多索引会拖慢INSERT/UPDATE速度,重建前优先删除无用索引。

问答环节：解决你可能遇到的5个高频问题

问：重建索引时，表会完全锁死吗？
答：取决于你使用的数据库和选项，在SQL Server中，使用 ONLINE=ON 可允许DML操作；PostgreSQL使用 CONCURRENTLY 也不会阻塞写入；但MySQL的 OPTIMIZE TABLE 会锁表,需谨慎。

问：我应该每天重建索引吗？
答：不建议，过度重建会浪费资源和增加备份时间，更科学的方法是设置维护计划，根据碎片化阈值（如30%）自动触发重建（例如通过SQL Server Agent定期作业）。

问：如何在不中断服务的情况下重建大表的索引？
答：推荐使用在线重建工具：SQL Server 的 ONLINE模式、PostgreSQL 的 CONCURRENTLY 参数，或者 Percona Toolkit 中的 pt-online-schema-change，这些工具通过建立临时索引逐步替换,实现零停机。

问：重建后查询反而变慢，可能原因是什么？
答：可能是统计信息未及时更新，或重建后索引的填充因子（Fill Factor）设置不当，尝试手动 UPDATE STATISTICS，并检查填充因子是否过小（建议默认90%）。

问：如何监测索引碎片变化？
答：SQL Server可使用 sys.dm_db_index_physical_stats；MySQL通过 mysql.innodb_index_stats；PostgreSQL使用 pg_stat_user_indexes，建议每周记录碎片率，用图表工具（如Grafana）观察趋势。

延伸思考：除了重建索引，定期使用 INDEX_TRANSLATE 或 提示令（HINTS） 强制使用指定索引也是一种临时优化方案，最彻底的做法还是优化写入流程、合理设计索引（如复合索引、覆盖索引）,从根本上减少碎片产生。