MSSQL清理索引碎片

October 11 2018

软件开发一般会有这么几个环境，本地、线上测试以及生产环境。每一套环境都有独立的数据库，那么问题来了，如果本地和生产环境数据库的表结构以及索引部分都一致，执行的程序也一样，为什么生产环境的查询速度和本地的查询速度完全在天壤之别呢？

最先想到的是，是不是索引有缺失，但是通过脚本比对下来，并没有。经过同事提点之后发觉，是不是索引碎片的问题呢？之前我从来没有遇到过，毕竟接触的数据量不多。

至于为什么会产生索引碎片，网上的说法是

索引能够加快对表的访问速度，然而任何事物都有两面性，索引在带给我们便利的同时也会占用额外的磁盘空间，并且我们在对表进行增删改的操作时也要消耗额外的时间来更新索引。而在我们对包含索引的表进行增删改时，也会造成索引碎片，久而久之，索引碎片程度越来越高，反而会降低我们对表的访问速度。因此作为数据库管理员，要定期维护索引，修复索引碎片。

虽然我说不出索引的原理，但是索引碎片却是实实在在的存在。查询索引碎片有两种方式

sys.dm_db_index_physical_stats

通过对sys.dm_db_index_physical_stats表的avg_fragmentation_in_percent字段的搜索，可以较为直观地了解到索引的碎片化程度。可以使用以下的脚本来查询索引碎片

1
2
3
4
5
6
SELECT object_name(a.object_id) [TableName] ,a.index_id ,name [IndexName] ,avg_fragmentation_in_percent 
From sys.dm_db_index_physical_stats ( DB_ID() , NULL , NULL, NULL, NULL ) As a 
JOIN sys.indexes AS b 
ON a.object_id = b.object_id AND a.index_id = b.index_id
Where avg_fragmentation_in_percent > 0
Order By avg_fragmentation_in_percent Desc

DBCC SHOWCONTIG TableId

另一种查询索引碎片的方法是

1
2
3
declare @table_id int
set @table_id=object_id('TableName')
dbcc showcontig(@table_id)

比如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DBCC SHOWCONTIG 正在扫描 'A' 表...  
 
表: 'A'（884198200）；索引 ID: 1，数据库 ID: 13  
 
已执行 TABLE 级别的扫描。  
 
- 扫描页数.....................................: 3127  
 
- 扫描扩展盘区数...............................: 403  
 
- 扩展盘区开关数...............................: 1615  
 
- 每个扩展盘区上的平均页数.....................: 7.8  
 
- 扫描密度［最佳值:实际值］....................: 24.20%［391:1616］  
 
- 逻辑扫描碎片.................................: 68.02%  
 
- 扩展盘区扫描碎片.............................: 38.46%  
 
- 每页上的平均可用字节数.......................: 2073.2  
 
- 平均页密度（完整）...........................: 74.39%  
 
DBCC 执行完毕。

扫描密度越低，逻辑扫描碎片以及扩展盘区扫描碎片越高，说明需要清理索引碎片了。

清理碎片的方法

第一种，使用ALTER INDEX命令，索引碎片化程度不同，使用的参数也不同，应该遵循如下规律：

1）当avg_fragmentation_in_percent >30%时，使用ALTER INDEX REBUILD命令修复索引碎片；

2）当5%<avg_fragmentation_in_percent <=30%时，使用ALTER INDEX REORGANIZE命令修复索引碎片。

当使用ALTER INDEX REBUILD命令的时候可以带上WITH(ONLINE=ON)，表示在修复索引碎片的同时其他用户仍然可以访问数据库。

第二种清理碎片的方式，DBCC DBREINDEX(表，索引名，填充因子）

第一个参数，可以是表名，也可以是表ID。

第二个参数，如果是’’，表示影响该表的所有索引。

第三个参数，填充因子，即索引页的数据填充程度。如果是100，表示每一个索引页都全部填满，此时select效率最高，但以后要插入索引时，就得移动后面的所有页，效率很低。如果是0，表示使用先前的填充因子值。

总结

即使加了索引也无法提升查询速度可能还有其它的原因，索引碎片是其中一种。有时候有可能是你数据库的服务器内存满了，我找了老半天原因，原来出在了这儿。

avatar

chilihotpot

You Are The JavaScript In My HTML