SQL Server误区：通过创建再删除聚集索引来整理堆碎片的方法不可取

核心结论

严禁通过在堆表上创建聚集索引随后立即删除的方式来整理堆的碎片。这种做法不仅效率低下，更会导致系统中所有依赖该表的非聚集索引发生灾难性的双重重建，严重影响数据库性能。

原理解析：为什么堆不能这样“整理”？

要理解这个误区，首先必须明确SQL Server中不同表结构下非聚集索引存储的键值差异：

• 如果表是堆（Heap）： 非聚集索引中的每行数据通过 RID（Row Identifier，即文件号:页号:记录号）来链接到基表数据。
  如果你希望将堆变为聚集索引，那么原来的 RID 信息就全部失效了。因此，所有的非聚集索引都必须被重新扫描和构建，以指向新的数据位置。
  反之，如果删除了聚集索引（使表变回堆），原先存储在非聚集索引中的聚集索引键也失效了，系统需要重新计算或重建这些索引以恢复为基于 RID 的结构。
• 如果表是聚集索引表： 非聚集索引链接的是聚集索引键。逻辑相对清晰，但依然涉及大量的键值重算。

第1步：执行“建-删”操作的后果分析

假设你有一个包含大量数据的堆表，且上面建立了多个非聚集索引。当你执行以下逻辑时：

1. 创建聚集索引： 数据库必须重写整个堆的数据以按新键排序，同时重建所有非聚集索引（因为它们需要从RID变为聚集索引键）。
2. 删除聚集索引： 数据再次被打散或重组为无序状态，此时所有非聚集索引再次失效，系统不得不第二次重建所有非聚集索引，将其键值从聚集索引键改回RID。

结果： 你仅仅为了整理堆的碎片，却迫使非聚集索引经历了两次完全重建。对于大表而言，这通常意味着数小时甚至数天的I/O等待和资源锁定。

第2步：SQL Server 2008+ ALTER TABLE REBUILD 的行为

部分用户可能听说可以使用 ALTER TABLE ... REBUILD 命令直接重建堆来整理碎片。虽然这确实可以整理堆的物理碎片，但其效果与上述“建-删聚集索引”类似：

当你对堆执行 ALTER TABLE TableName REBUILD 时，SQL Server 会将所有数据移动到新的物理页面中。由于数据位置变了，原来存储在所有非聚集索引中的 RID 全部失效。因此，SQL Server 必须同步重建所有非聚集索引。

正确做法与建议

面对堆表的碎片问题，盲目重堆不是唯一解，也不是最优解。请根据实际场景判断：

1. 评估碎片程度： 使用 sys.dm_db_index_physical_stats 查询 avg_fragmentation_in_percent。只有当碎片率非常高且影响扫描性能时才考虑重组织。
2. 转为聚集索引： 如果业务允许，最彻底的方法是为主键或常用查询列创建一个聚集索引。这样不仅消除了堆碎片问题，还提升了大多数查询的性能。注意：创建后无需删除，保持其为聚集索引即可。
3. 在线/离线维护： 如果必须整理堆，请确保在非业务高峰期进行，并做好监控。如果使用 SQL Server Enterprise Edition，可以考虑使用在线索引操作（尽管对堆的重建本身仍会阻塞写入）。
4. 关注非聚集索引：** 永远不要单独看待堆碎片，务必同步评估其关联的非聚集索引大小和维护成本。

总结

切勿尝试通过“先建聚集索引再删除”这种迂回手段来整理堆碎片。这不仅无法达到预期的高效整理目的，反而会因为 RID 的改变导致所有非聚集索引遭受双倍重建的痛苦。正确的思路是直接维护现有非聚集索引，或者在条件允许时将堆转化为具有良好访问模式的聚集索引。

以上就是SQL Server堆碎片整理误区的详细内容，更多关于SQL Server数据库优化的资料请关注本站其它相关文章！