heap table上频繁高碎片化nonclustered index优化方案咨询

根因分析 #

当前索引碎片化过高的核心诱因有两个：

1. 非聚集索引键为uniqueidentifier类型，若CSV导入的ID为随机无序值，插入操作会随机命中索引的不同数据页，频繁触发页拆分，直接导致碎片化快速上升。
2. 表为堆结构，非聚集索引叶子节点存储堆的RID，堆产生的转发记录也会额外提升索引维护开销，间接加剧碎片化问题。

可落地优化方案 #

• 替换为有序GUID逻辑
  若可控制CSV中ID的生成规则，将随机GUID替换为有序GUID。SQL Server内置NEWSEQUENTIALID()可生成自增有序GUID，应用侧也有对应语言的有序GUID生成实现。有序GUID的插入操作会集中在索引的尾部页，几乎不会触发中间页拆分，碎片化率可降低90%以上。
• 调整索引填充因子
  为该非聚集索引设置合理的填充因子，预留部分页空间减少插入时的页拆分概率。建议根据单次插入量级设置为80~90，示例语句：

  ALTER INDEX UQ_MyTable_ID ON dbo.MyTable REBUILD WITH (FILLFACTOR = 85);
  

  注意填充因子不可设置过低，否则会大幅提升索引存储空间占用，反而降低查询性能。
• 堆表改聚集索引表
  取消堆结构，选择合适的列作为聚集键：
  1. 若TimeStamp列随导入时间递增，优先选择TimeStamp作为聚集索引键，聚集索引有序，所有插入操作集中在尾部，无聚集索引碎片化问题，同时非聚集索引叶子节点存储聚集键，避免堆转发记录的额外开销。
  2. 若业务查询以ID列为核心，可将原非聚集唯一约束调整为聚集唯一约束，搭配有序GUID使用效果最佳。
• 优化数据导入逻辑
  每次导入CSV数据前，先对CSV内的ID列做升序排序，按排序后的顺序执行插入操作，插入操作会顺序写入非聚集索引，大幅减少随机插入带来的页拆分。
• 调整索引维护策略
  若索引碎片化率超过30%，直接执行索引重建的效率远高于重组，可设置定时任务在每日业务低峰期执行一次索引维护，无需频繁在业务高峰期执行重组操作。

结构调整示例 #

若选择将ID列设为聚集唯一键+默认有序GUID，调整后的表结构如下：

CREATE TABLE [dbo].[MyTable](
[ID] uniqueidentifier NOT NULL CONSTRAINT [DF_MyTable_ID] DEFAULT (NEWSEQUENTIALID()),
[EventID] uniqueidentifier NOT NULL,
[AssemblyID] uniqueidentifier NOT NULL,
[TimeStamp] [smalldatetime] NOT NULL,
[IsTrue] [bit] NOT NULL,
[IsExempt] [bit] NOT NULL CONSTRAINT [DF_IsExempt]  DEFAULT ((0)),
CONSTRAINT [UQ_MyTable_ID] UNIQUE CLUSTERED ([ID] )
)
GO

内容的提问来源于stack exchange，提问作者Bob Richards