SQLSERVER聚集索引的整理(重建)的必要性测试
来源:互联网 发布:淘宝店虚假交易警告 编辑:程序博客网 时间:2024/06/09 17:17
http://www.soaspx.com/dotnet/sql/mssql/sql2000/sqlserver2000_20120331_8856.html
问题:聚集索引是否需要整理?在什么情况下需要整理?整理的效果如何?有没有负面作用?
测试环境: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效率会比较快
- SQLSERVER聚集索引的整理(重建)的必要性测试
- Oracle 重建索引的必要性
- Oracle 重建索引的必要性
- SQLServer的聚集索引和非聚集索引
- SQLServer的聚集索引和非聚集索引
- 重建 SQLServer 索引的重要性!
- 重建 SQLServer 索引的重要性!
- SQLServer批量重建索引(整理)
- SQLSERVER聚集索引与非聚集索引的再次研究(上)
- SQLSERVER聚集索引与非聚集索引的再次研究(下)
- SQLSERVER聚集索引与非聚集索引的再次研究(上)
- SQLSERVER聚集索引与非聚集索引的再次研究(下)
- SQLSERVER聚集索引与非聚集索引的再次研究(上)
- 索引重建的必要性与影响 (文档 ID 1525787.1)
- SQLSERVER聚集索引和主键的误区
- SQLSERVER的非聚集索引结构
- SqlServer查看聚集索引的创建时间
- sqlserver重建所有索引的存储过程
- 使用Git下载源码,使用mvn创建工程,将工程打包jar文件
- 美国国土安全部重点努力加强国家关键基础设施的网络安全
- AMSS - Advanced Mobile Subscriber Software
- PHP无限分类——左右值实现
- unresolved external symbol__imp__WSAStartup@8等错误原因
- SQLSERVER聚集索引的整理(重建)的必要性测试
- MSM平台上的AMSS
- MINA 框架简介
- python lambda 介绍
- 深入学习Oracle分区表及分区索引
- REX启动分析——基于Qualcomm平台
- 保留代码风格原格式复制到word文档
- ASP.NET中如何实现负载均衡
- JMS的两种模式 P2P,PUB/SUB