无聚集索引时堆表非聚集索引的外部碎片问题及相关影响咨询

针对你的问题，核心结论和细节拆解如下：

堆上创建非聚集索引依然会产生外部碎片，并非完全不会出现：

• 非聚集索引本身是独立的B树结构，当索引页的可用空间耗尽时，同样会触发页拆分——这和聚集索引的B树拆分逻辑完全一致。堆的底层数据页不会因非聚集索引操作拆分，但非聚集索引自身的B树仍会因数据插入、索引键更新出现页拆分，进而产生外部碎片。
• 堆的底层数据页虽可随意存储，但删除数据后会留下空白空间（内部碎片），若后续插入的数据无法填充这些空白页，新数据会分配新的物理页，这也会导致外部碎片（物理页顺序与逻辑访问顺序不一致）。

对非聚集索引的影响 #

• 非聚集索引叶子节点存储的内容不同：堆结构下存储RID（行标识符），聚集索引下存储聚集索引键。如果聚集索引键是频繁更新的列，会导致非聚集索引叶子节点频繁更新，进而触发更多页拆分，提升碎片产生的概率；反之，若聚集索引键是稳定的自增ID，非聚集索引的碎片生成率会更低。
• 聚集索引的碎片会间接影响非聚集索引的查询性能：查询非聚集索引后需回表（书签查找），若聚集索引存在外部碎片，回表时的物理IO会更零散，查询效率下降。

对整体碎片的影响 #

• 聚集索引强制数据按索引键顺序物理存储：若插入数据与索引键顺序匹配（如自增主键），页拆分极少，外部碎片也很少；若插入数据是乱序的（如随机GUID），会频繁触发页拆分，产生大量外部碎片。
• 聚集索引的碎片修复更可控：可通过ALTER INDEX ... REORGANIZE（在线整理）或REBUILD（离线/在线重建）直接整理数据物理存储顺序；而堆的碎片修复仅能通过ALTER TABLE ... REBUILD或先建聚集索引再删除的方式实现，操作复杂度更高。
• 堆的碎片更易积累：堆无固定排序规则，删除数据后的空白页难以被有效复用，长期运行会积累更多外部碎片；聚集索引的有序性可更好地利用页空间，减少内部碎片的留存。

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