SQLSERVER聚集索引的整理（重建）的必要性测试

作者：[51ak ]

SQLSERVER 在日常DBA工作中有一项叫索引整理

一般整理的多为非聚集索引

问题：聚集索引是否需要整理？在什么情况下需要整理？整理的效果如何？有没有负面作用？

测试环境:WIN2003+SQL2008R2
测试表:wkf_test 存放12767550条记录,wkf_test_all表是该表的备份

1.首先来次DBCC结果如下:
  DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
  表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
  已执行 TABLE 级别的扫描。
  - 扫描页数................................: 64860
  - 扫描区数..............................: 8142
  - 区切换次数..............................: 8145
  - 每个区的平均页数........................: 8.0
  - 扫描密度 [最佳计数:实际计数].......: 99.53% [8108:8146]
  - 逻辑扫描碎片 ..................: 0.38%
  - 区扫描碎片 ..................: 0.07%
  - 每页的平均可用字节数........................: 20.6
  - 平均页密度(满).....................: 99.75%

2.删除1部分连续数据，注意看页数相应减少，页密度保持不动
delete from dbo.wkf_test where id between 50 and 2767550
  表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
  已执行 TABLE 级别的扫描。
  - 扫描页数................................: 50803
  - 扫描区数..............................: 6377
  - 区切换次数..............................: 6377
  - 每个区的平均页数........................: 8.0
  - 扫描密度 [最佳计数:实际计数].......: 99.58% [6351:6378]
  - 逻辑扫描碎片 ..................: 0.38%
  - 区扫描碎片 ..................: 0.02%
  - 每页的平均可用字节数........................: 20.8
  - 平均页密度(满).....................: 99.74%

3.删除奇数数据，亮点来了，页数保持不变的同时，页密度大幅下降 '（此时数据表性能很差）'
  delete from dbo.wkf_test where id%2=0
  DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
  表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
  已执行 TABLE 级别的扫描。
  - 扫描页数................................: 50803
  - 扫描区数..............................: 6377
  - 区切换次数..............................: 6377
  - 每个区的平均页数........................: 8.0
  - 扫描密度 [最佳计数:实际计数].......: 99.58% [6351:6378]
  - 逻辑扫描碎片 ..................: 0.38%
  - 区扫描碎片 ..................: 0.02%
  - 每页的平均可用字节数........................: 4060.2
  - 平均页密度(满).....................: 49.84%

4.REBUILD一下聚集索引,以90的填充率后,页数大量下降(约50%)，密度90
  ALTER INDEX ALL ON wkf_test REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON,ONLINE = ON);
  DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
  表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
  已执行 TABLE 级别的扫描。
  - 扫描页数................................: 28064
  - 扫描区数..............................: 3546
  - 区切换次数..............................: 3569
  - 每个区的平均页数........................: 7.9
  - 扫描密度 [最佳计数:实际计数].......: 98.26% [3508:3570]
  - 逻辑扫描碎片 ..................: 0.38%
  - 区扫描碎片 ..................: 1.83%
  - 每页的平均可用字节数........................: 793.8
  - 平均页密度(满).....................: 90.19%

5.再把奇数数据补回来。--运行时间 01:36
  set identity_insert [dbo].[wkf_test] on
  INSERT INTO [dbo].[wkf_test](id,[usernmae],[tablename])
  SELECT * from wkf_test_all where id%2=0
  set identity_insert [dbo].[wkf_test] off
6.此时的DBCC结果显示数据表页数再次暴涨.很显然数据是追上去的而不是塞上去的,古怪的现象是数据页变得稀了。注意这个细节，马上的步骤会有怪异的现象发生
  DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
  表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
  已执行 TABLE 级别的扫描。
  - 扫描页数................................: 82431
  - 扫描区数..............................: 10338
  - 区切换次数..............................: 59658
  - 每个区的平均页数........................: 8.0
  - 扫描密度 [最佳计数:实际计数].......: 17.27% [10304:59659]
  - 逻辑扫描碎片 ..................: 68.19%
  - 区扫描碎片 ..................: 0.29%
  - 每页的平均可用字节数........................: 2429.9
  - 平均页密度(满).....................: 69.98%

7.好吧，现在再把刚才"追"上去的数据再删了，理论上应该回退到4的结果。但是现实情况是页数不变。继续变稀。理解一下：后被的数据不被SQLSERVER理解为连续的方式"追"上去的
  delete from dbo.wkf_test where id%2=0
  表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
  已执行 TABLE 级别的扫描。
  - 扫描页数................................: 75403
  - 扫描区数..............................: 9485
  - 区切换次数..............................: 58521
  - 每个区的平均页数........................: 7.9
  - 扫描密度 [最佳计数:实际计数].......: 16.11% [9426:58522]
  - 逻辑扫描碎片 ..................: 73.90%
  - 区扫描碎片 ..................: 0.69%
  - 每页的平均可用字节数........................: 5377.4
  - 平均页密度(满).....................: 33.56%

8.那么我们再把数据补回去一次，很好，SQLSERVER发现了上次的“空”，把数据原生态补回去了。而且INSERT性能明显强过整理过的那次（时间是第一次耗时的1/3）
  set identity_insert [dbo].[wkf_test] on
   INSERT INTO [dbo].[wkf_test]
       (id,
      [usernmae]
       ,[tablename])
  SELECT * from wkf_test_all where id%2=0
  set identity_insert [dbo].[wkf_test] off
  --00:33
  DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
  表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
  已执行 TABLE 级别的扫描。
  - 扫描页数................................: 82431
  - 扫描区数..............................: 10337
  - 区切换次数..............................: 59660
  - 每个区的平均页数........................: 8.0
  - 扫描密度 [最佳计数:实际计数].......: 17.27% [10304:59661]
  - 逻辑扫描碎片 ..................: 68.22%
  - 区扫描碎片 ..................: 0.30%
  - 每页的平均可用字节数........................: 2429.9
  - 平均页密度(满).....................: 69.98%

9.再REBULID一下.这个没什么说的了，顺利到指定的90
  ALTER INDEX ALL ON wkf_test
  REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
       STATISTICS_NORECOMPUTE = ON,ONLINE = ON);
  DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
  表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
  已执行 TABLE 级别的扫描。
  - 扫描页数................................: 63932
  - 扫描区数..............................: 8078
  - 区切换次数..............................: 8097
  - 每个区的平均页数........................: 7.9
  - 扫描密度 [最佳计数:实际计数].......: 98.69% [7992:8098]
  - 逻辑扫描碎片 ..................: 0.25%
  - 区扫描碎片 ..................: 4.51%
  - 每页的平均可用字节数........................: 790.4
  - 平均页密度(满).....................: 90.23%

10.delete from dbo.wkf_test where id between 50 and 10767550
  DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
  表: 'wkf_test' (725577623)；索引 ID: 1，数据库 ID: 6
  已执行 TABLE 级别的扫描。
  - 扫描页数................................: 11234
  - 扫描区数..............................: 1420
  - 区切换次数..............................: 1421
  - 每个区的平均页数........................: 7.9
  - 扫描密度 [最佳计数:实际计数].......: 98.80% [1405:1422]
  - 逻辑扫描碎片 ..................: 0.27%
  - 区扫描碎片 ..................: 14.37%
  - 每页的平均可用字节数........................: 791.6
  - 平均页密度(满).....................: 90.22%

总结如下：
以IDENTITY(1,1)为聚集索引时。
如果连续的删除大量索引在这些索引以有序的方式进行排列时(是不是有点绕口？)会被数据库引擎识别，将这一串页从页链中删除。无需做聚集索引的整理工作
如果不连续的删除，则数据页被保存，页密度被稀释
如果整理后填充了原有的页空间，则数据会追加到新页中，而不是分裂旧页，此时INSERT效率会下降，因为要新开页
反之如果删除后没有整理释放页空间，则补回来的数据会加到原有的位置（可能会引起页分裂），在补回去的数据不超过旧有数据的情况下。INSERT效率会比较快