索引碎片的檢測和整理_ZenDei技術網路在線

存儲數據是為了查找數據，存儲結構影響數據查找的性能。對無序數據進行查找，最快的查找演算法是哈希查找；對有序數據進行查找，最快的查找演算法是平衡樹查找。在傳統的關係型資料庫中，聚集索引和非聚集索引都是平衡樹（B-Tree）類型的存儲結構，用於順序存儲數據，便於實現數據的快速查找。除了提升數據查找的性能之外 ...

存儲數據是為了查找數據，存儲結構影響數據查找的性能。對無序數據進行查找，最快的查找演算法是哈希查找；對有序數據進行查找，最快的查找演算法是平衡樹查找。在傳統的關係型資料庫中，聚集索引和非聚集索引都是平衡樹（B-Tree）類型的存儲結構，用於順序存儲數據，便於實現數據的快速查找。除了提升數據查找的性能之外，索引還能減少硬碟IO和記憶體消耗。通常情況下，硬碟IO是查找性能的瓶頸，由於索引是數據表的列的子集，這意味著，索引只存儲部分列的數據，占用的硬碟空間比全部列少了很多，因此，資料庫引擎只需要消耗相對較少的硬碟IO和記憶體buffer，就能把索引數據載入到記憶體中。

索引以B-Tree結構存儲在數據文件中，分為葉子節點和非葉子節點，葉子節點用於存儲數據，而非葉子節點（中間節點和根節點）用於存儲索引鍵，節點數據按照索引鍵排序。理論上，一旦數據集確定下來，索引查找的時間消耗就只跟索引結構的層次有關係，層次越多，查找數據所消耗的時間越多。碎片會影響索引的層次結構，但是，碎片並不總是破壞者，碎片有利於數據的更新。

在數據的物理存儲上，索引和數據存儲在硬碟上的數據文件中，數據文件以頁（Page）為最小單位分割，每一個Page是8KB，物理位置上連續的8個Page叫做一個區（Extent），每一個區是64KB。區是空間分配的基本單位，而頁是數據存儲的基本單位。

從物理存儲上來看，索引是由一系列的分段（Fragment）構成的，每個分段是由連續的數據頁（Page）構成的。理想情況下，數據存儲的物理順序和索引鍵定義的邏輯順序保持一致，這有利於數據的範圍查詢，因為機械硬碟不需要移動磁頭就可以獲取到所需數據。數據的更新（Insert，Update或Delete）有時會更新索引鍵，組成索引鍵的欄位的Size增加，以至於原來的Page不能容納該行數據，導致頁拆分，致使數據的物理順序和邏輯順序不再匹配，產生索引外部碎片。因此，預留少量的頁內碎片能夠容納數據行Size的有限增加，減少頁拆分（page split）發生的次數，提高數據更新的性能。通常情況下，大量的索引碎片總是十分有害的，應該把索引碎片控制在一定百分比以下，微軟推薦，30%。

數據更新和數據查找是此消彼長的關係，在索引頁中預留空閑空間會增加索引的Size，然而，額外占用的硬碟空間需要額外的硬碟IO載入到記憶體中，這不利於數據的查找，然而，當發生數據更新時，預留的空間能夠容納數據行Size的增加，減少頁拆分發生的次數，這有利於數據的更新，因此，在頻繁更新的資料庫系統中，為了減少頁拆分的次數，需要人為增加索引的內部碎片：

FILLFACTOR = fillfactor
PAD_INDEX = { ON | OFF }

在創建索引時，需要權衡數據更新和數據查找對系統的影響，在實際產品環境中，需要設置合適的填充因數，預留索引內部碎片；及時整理索引碎片，消除索引外部碎片，以使資料庫達到最優狀態。

一，索引碎片

索引碎片分為內部碎片（Internal Fragmentation）和外部碎片（External Fragmentation），內部碎片是指索引頁內部的碎片，在索引頁內部存在沒有使用的空間，部分空間被閑置，這意味索引頁存在空間的浪費，數據實際占用的空間多於需要的空間，因此，當存儲相同的數據集時，如果索引的碎片越多，索引結構占用的硬碟空間越多；在處理數據時，資料庫引擎需要讀取的索引頁越多，載入到記憶體消耗的緩存頁（Buffer）越多。內部碎片會出現在索引結構的葉子節點或中間節點，葉子節點中的碎片會導致數據密度降低，而中間節點中的碎片會導致索引鍵的密度降低。

外部碎片是指存儲數據的頁或區（Extent）的邏輯順序和物理順序不一致，邏輯順序（Logical Order）是由索引鍵定義的，物理順序（Physical Order）是在硬碟文件中，用於存儲數據的頁或區的順序，也就是索引的葉子節點占用的頁或區在硬碟上的物理存儲的順序。如果在邏輯上連續的Page或Extent在物理上也是連續的，那麼就不存在外部碎片。最有效的順序是：邏輯順序上相鄰的數據頁，在物理順序上也相鄰。

The most efficient order is where the logical order of the pages and extents(as defined by the index keys, following the next-page pointers from the page headers) is the same as the physical order of the pages and extents with the data files. In other words, the index leaf-lelvel page that has the row with the next index key is also the next physical contiguous page int the data file.

二，檢測索引碎片

可以通過內置函數: sys.dm_db_index_physical_stats，查看索引的外部碎片，欄位 avg_fragmentation_in_percent 用於表示外部碎片的程度，對於索引，以Page為單位統計碎片；對於堆（Heap），以Extent為單位統計碎片，這是因為Heap結構的頁（Page）是沒有順序的。在堆（Heap）的 Page Header中，欄位 next_page 和 Pre_page pointer是null。欄位 avg_page_space_used_in_percent 用於表示內部碎片的程度，百分比越高，說明單個Page的空間利用率越高。

1，掃描模式

檢測索引的碎片，需要對索引進行掃描，參數mode指定為了獲取碎片數據，資料庫引擎必須執行的掃描模式，共有三種模式：LIMITED, SAMPLED, or DETAILED，預設值是LIMITED。

Limited 模式是最快的，只掃描最小數據量的Page，Limited模式不會掃描數據頁（Data Page），對於索引，掃描葉子節點的直接父節點；對於Heap，掃描堆表對應的IAM 和 PFS系統頁。
在Sampled模式下，資料庫引擎從索引或堆表中抽取1%的Page作為樣本數據，根據樣本數據來估計碎片的程度。
Detailed 模式掃描所有的數據頁，耗時最久，返回的信息最詳細。

2，分段和碎片

分段（Fragment），也叫片段，是指在硬碟文件中，數據的物理存儲的集中/分散程度。一個片段是由在物理位置上連續的索引頁組成的，Fragment的Size 越大，說明頁的物理位置越集中，讀取相同數量的Page所需的IO越少，範圍讀取性能越好。

碎片（Fragmentation）用於描述數據更新對索引結構產生的副作用。頁內碎片是指Page 內部存在空閑空間，外部碎片是指Page 或 extent 的物理順序和所以鍵定義的邏輯順序不一致。

avg_fragmentation_in_percent：碎片百分比，合理的比例是在10左右，比例越大，索引碎片越多，讀取性能越差；
fragment_count：分段的數量，理論上，分段（Fragment）數量越少越好，間接說明索引的物理順序和邏輯順序越匹配；
avg_fragment_size_in_pages：每個分段平均包含的Page數量，Fragment的Size 越大，讀取相同數量的Pages所需的IO越少，讀取性能越好；
avg_page_space_used_in_percent：Page空間的平均利用率，值越大，頁內碎片越小；

3，檢測碎片的腳本

通過執行函數，檢測索引的碎片：

select ps.database_id,
    ps.object_id,
    ps.index_id,
    ps.partition_number,
    ps.index_type_desc,
    ps.alloc_unit_type_desc,
    ps.index_depth,
    ps.index_level,
    ps.avg_fragmentation_in_percent,
    ps.fragment_count,
    ps.avg_fragment_size_in_pages,
    ps.page_count,
    ps.avg_page_space_used_in_percent,
    ps.record_count,
    ps.ghost_record_count,
    ps.version_ghost_record_count,
    ps.min_record_size_in_bytes,
    ps.max_record_size_in_bytes,
    ps.avg_record_size_in_bytes,
    ps.forwarded_record_count,
    ps.compressed_page_count
from sys.dm_db_index_physical_stats(database_id,object_id,index_id,partition_number,'detailed') as ps
order by ps.index_level

View Code

欄位avg_fragmentation_in_percent 表示索引碎片的密度，可以接受的百分比是從0到10%，根據碎片的百分比，選擇重新組織索引或重新創建索引，以整理碎片。

返回的欄位分析：

Index_level=0，表示是索引結構的深度，0表示葉子級別；
avg_fragmentation_in_percent：碎片的百分比，表示物理順序不連續的pages所占的百分比；如果基礎表是BTree，碎片的計量單位是Page，avg_fragmentation_in_percent和page_count 的乘積就是物理順序和邏輯順序不一致的pages的總數量。
fragment_count：片段的數量
page_count：page 的數量
avg_fragment_size_in_pages：每個Index 片段平均使用的Pages，是Page_Count和Fragment_Count的比值。
avg_page_space_used_in_percent：每個Page內空間的平均使用程度

三，碎片整理

碎片整理有兩種方式：重新組織索引和重新創建索引，重建索引是指在一個事務中，刪除舊的索引，並重建新的索引，這種方式會回收原有索引的硬碟空間，並分配新的存儲空間，以創建索引結構。重組索引是指不分配新的存儲空間，在原有的空間基礎上，重新組織索引結構的葉子節點，使數據頁的邏輯順序和物理順序保持一致，並釋放索引中多餘的空間，這就是說，重組索引是為了減少葉子節點的外部碎片。

使用函數 sys.dm_db_index_physical_stats 檢測碎片的程度，欄位 avg_fragmentation_in_percent 返回的邏輯碎片的百分比，一般情況下，微軟推薦以30%為閾值：

avg_fragmentation_in_percent >5% and <=30%：重組索引（ALTER INDEX REORGANIZE）；
avg_fragmentation_in_percent >30%：重建索引（ALTER INDEX REBUILD）；

以下腳本使用游標（Cusor）逐個整理索引碎片，在重建索引（Rebuild Index）時，使用的索引選項是：FILLFACTOR = 95, ONLINE = OFF, DATA_COMPRESSION = PAGE

DECLARE @SchemeName NVARCHAR(MAX)=N'';
DECLARE @TableName NVARCHAR(MAX)=N'';
DECLARE @IndexName NVARCHAR(MAX)=N'';
DECLARE @avg_fragmentation_in_percent FLOAT=0;
DECLARE @SQL NVARCHAR(MAX)=N'';

DECLARE cur_index CURSOR
LOCAL
FORWARD_ONLY
FAST_FORWARD
READ_ONLY
FOR
SELECT
    '['+s.name+']' AS SchemeName,
    '['+o.name+']' AS TableName,
    '['+i.name+']' AS IndexName,
    MAX(ps.avg_fragmentation_in_percent) AS avg_fragmentation_in_percent
FROM sys.indexes i
INNER JOIN sys.objects o
    ON i.object_id = o.object_id
INNER JOIN sys.schemas s
    ON o.schema_id = s.schema_id
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'DETAILED') AS ps
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent >= 10
AND i.type IN (1, 2)    --1: CLUSTERED, 2: NONCLUSTERED
AND o.type = N'U'        --U: USER_TABLE
AND ps.index_level = 0    --Index leaf-level 
GROUP BY    s.name,
            o.name,
            i.name
ORDER BY avg_fragmentation_in_percent DESC;

OPEN cur_index;

FETCH NEXT FROM cur_index
INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent;

WHILE(@@FETCH_STATUS=0)
BEGIN
    IF (@avg_fragmentation_in_percent>30)
    BEGIN
        SELECT @SQL = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemeName + N'.' + @TableName 
                        + N' REBUILD PARTITION=ALL WITH (FILLFACTOR = 95, ONLINE = OFF, DATA_COMPRESSION = PAGE );'
    END 
    ELSE --@avg_fragmentation_in_percent between 10 and 30
    BEGIN
        SELECT @SQL = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemeName + N'.' + @TableName 
                        + N' REORGANIZE PARTITION=ALL;'
    END

    EXEC (@SQL)

    FETCH NEXT FROM cur_index
    INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent;
END

CLOSE cur_index;
DEALLOCATE cur_index;

View Code

這個閾值，可以根據產品環境數據更新和查找的實際情況，適度調整。

參考文檔：

Reorganize and Rebuild Indexes

sys.dm_db_index_physical_stats (Transact-SQL)