6.2.6 索引碎片

 

一个聚集索引创建后，数据按顺序存储在叶子级。在非聚集索引中，索引键按顺序存储在叶子级上。为了在叶子级上获得最大的性能，索引的页会在逻辑和物理上进行排序。SQL Server依据恰当的物理顺序来存储这些索引页以在磁盘上获得尽可能快的读取速度。从磁盘上读取数据的过程中，开销最大的过程是移动磁盘臂。如果需要读的数据页顺序存储，那么磁盘臂移动的次数就会降到最小，读取性能因而得到优化。

数据插入表中时，会被存储在聚集索引叶子级页的特定页上。非聚集索引的索引键也需要插入非聚集索引叶子级页的正确页。如果这个页满了，SQL Server会执行一次页拆分，意味着SQL Server会分派一个新页并将这个新页与正确的索引相连接。这种情况将导致索引碎片，也就是说数据页的逻辑顺序将不再与物理顺序相匹配。UPDATE和DELETE语句同样会导致索引碎片。

为了减少索引碎片，在索引创建时可以使用FILLFACTOR选项。这个选项定义了在索引创建时索引的叶子级页应该被填充的比例。FILLFACTOR越低，叶子级页在不被拆分的情况下容纳的条目更多，碎片发生的可能性因而越低。然而，较低的FILLFACTOR会使每一个叶子级页的初始存储数据变少，因而会产生较大的索引。

如果被索引的表不是只读表，它的索引早晚会产生碎片。具有碎片的索引可以使用语句ALTER INDEX进行碎片整理以提高数据访问速度。碎片整理有两个选项：

l          REORGANIZE  重新组织索引意味着使用冒泡排序操作排序叶子级页。REORGANIZE只排序数据页，并不是页中的所有条目，这意味着FILLFACTOR不能与重新组织同用。

l          REBUILD  重建索引意味着这个索引将被重新构建。这个操作比重组织操作所花的时间更长，但效果更好。FILLFACTOR选项可以与其共同使用来以期望的百分比再次填充页。如果没有FILLFACTOR选项，叶子级页将被完全填充。ONLINE选项同样可以在重建索引时使用。如果不使用ONLINE选项，创建将在脱机状态下执行，这意味着表在创建期间会被锁定。脱机创建比联机重建快，但由于它会锁住表，因此在需要访问表中数据的时候不能使用脱机重建。

Ø       维护索引

1.    打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2.    使用行集函数sys.dm_db_physical_stats来获取碎片信息。键入并执行以下语句来获取碎片大于50%的索引列表。以下代码包含在示例文件IndexFragmentation.sql中。

 

SELECT object_name(i.object_id) as object_name ,i.name as IndexName

    ,ps.avg_fragmentation_in_percent

    ,avg_page_space_used_in_percent

FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL , 'DETAILED') as ps

INNER JOIN sys.indexes as i

ON i.object_id = ps.object_id

     AND i.index_id = ps.index_id

WHERE ps.avg_fragmentation_in_percent > 50

AND ps.index_id > 0

ORDER BY 1

 

3.    键入并执行以下语句来对索引PK_Employee_EmployeeID执行一次脱机重建。

 

ALTER INDEX PK_Employee_EmployeeID

ON HumanResources.Employee

REBUILD

WITH (ONLINE = ON)

 

4.    关闭SQL Server Management Studio。

 注意     对索引进行碎片整理的过程一般需要自动运行。具体做法是使用维护计划或使用SQL Server代理来计划运行自己写的脚本(参见SQL Server Books Online的“How to: Create a Maintenance Plan”的主题)。