SQL Server索引碎片維護進階

一、產生原因及影響

索引是資料庫引擎中針對表(有時候也針對視圖)建立的特別資料結構，用來幫助查找和整理資料，它的重要性體現在能夠使資料庫引擎快速回傳查詢結果，當對索引所在的基礎資料表進行增刪改時，若存盤的資料進行了不適當的跨頁（SQL Server中存盤的最小單位是頁，頁是不可再分的），就會導致索引碎片的產生，隨著索引碎片的不斷增多，查詢回應時間就會變慢，性能也因此而下降，要解決這個問題，可以通過重新生成或重新組織索引來解決，

二、碎片分類

2.1、外部碎片

當索引頁不在邏輯順序上時就會產生外部碎片，索引創建時，索引鍵按照邏輯順序放在一組索引頁上，當新資料插入索引時，新的鍵可能放在存在的鍵之間，為了讓新的鍵按照正確的順序插入，可能會創建新的索引頁來存盤需要移動的那些存在的鍵，這些新的索引頁通常物理上不會和那些被移動的鍵原來所在的頁相鄰，創建新頁的程序會引起索引頁偏離邏輯順序，

2.2、內部碎片

當索引頁沒有用到最大量時就產生了內部碎片，雖然在一個有頻繁資料插入的應用程式里這也許有幫助，然而設定一個fill factor（填充因子）會在索引頁上留下空間，服務器內部碎片會導致索引尺寸增加，從而在回傳需要的資料時要執行額外的讀操作，這些額外的讀操作會降低查詢的性能，

三、維護方法

1、洗掉索引并重建，

2、使用DROP_EXISTING陳述句重建索引，

3、使用ALTER INDEX REBUILD重新生成索引，(推薦)

4、使用ALTER INDEX REORGANIZE重新組織索引，(推薦)

四、注意事項

重新生成索引可以聯機執行，也可以脫機執行，

重新組織索引始終聯機執行，這些值提供了一個大致指導原則，用于確定應在ALTER INDEX REORGANIZE和ALTER INDEX REBUILD之間進行切換的點，不過，實際值可能會隨情況而變化，必須要通過試驗來確定最適合您環境的閾值，

非常低的碎片級別（小于5%）不應通過這些命令來解決，因為洗掉如此少量的碎片所獲得的收益始終遠低于重新生成或重新組織索引的開銷，

切記：所有索引碎片維護一定要在凌晨（非業務高峰期間）進行！！！

五、優化指導原則

5.1、如何知道是否發生了索引碎片？

在SQL Server資料庫中，可以通過DBCC SHOWCONTIG WITH ALL_INDEXES或DBCC SHOWCONTIG(表ID或者表名) WITH ALL_INDEXES來檢查索引碎片情況，

--方法一
--目標資料庫
USE DB_NAME
--創建變數指定要查看的表
DECLARE @TABLE_ID INT
SET @TABLE_ID=OBJECT_ID('TABLE_NAME')
--執行
DBCC SHOWCONTIG(@TABLE_ID) WITH ALL_INDEXES

--方法二
USE DB_NAME
DBCC SHOWCONTIG('TABLE_NAME') WITH ALL_INDEXES

5.2、索引碎片判斷標準

通過對邏輯掃描碎片（過高）、平均頁密度(滿)（過低）的結果分析，判定是否需要進行索引處理，如下所示：

邏輯掃描碎片 ..................：97.83% 該百分比應該在0%到10%之間，高了則說明有外部碎片，

平均頁密度(滿) ..................：62.42% 該百分比應該盡可能靠近100%，低了則說明有外部碎片，

六、優化實踐

6.1、手動方式

第一步：查詢資料庫所有表的索引資訊，

SELECT OBJECT_NAME(B.OBJECT_ID) 表名,B.NAME 索引名稱,A.INDEX_TYPE_DESC 索引型別,
    ROUND(A.AVG_FRAGMENTATION_IN_PERCENT,2) 碎片率
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) A 
    INNER JOIN sys.indexes B ON A.OBJECT_ID=B.OBJECT_ID AND A.INDEX_ID=B.INDEX_ID
WHERE 1=1
    AND A.AVG_FRAGMENTATION_IN_PERCENT>30
    --AND A.AVG_FRAGMENTATION_IN_PERCENT>5 AND A.AVG_FRAGMENTATION_IN_PERCENT<=30
ORDER BY OBJECT_NAME(B.OBJECT_ID),A.AVG_FRAGMENTATION_IN_PERCENT DESC

注：通過碎片率，依四、注意事項處理方式，也可以逐個對表的索引進行對應的重新生成或重新組織處理，

第二步：生成資料庫所有表的索引處理的SQL陳述句，

SELECT OBJECT_SCHEMA_NAME(B.OBJECT_ID) 架構,OBJECT_NAME(B.OBJECT_ID) 表名,B.NAME 索引名,ROUND(A.AVG_FRAGMENTATION_IN_PERCENT,2) 碎片率,
    CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN N'重新生成索引' ELSE N'重新組織索引' END 處理方式,
    'ALTER INDEX '+QUOTENAME(B.NAME)+' ON '+QUOTENAME(OBJECT_SCHEMA_NAME(B.OBJECT_ID))+'.'+QUOTENAME(OBJECT_NAME(B.OBJECT_ID))+' '
        +CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN 'REBUILD' ELSE 'REORGANIZE' END 生成SQL陳述句
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A INNER JOIN sys.indexes B ON A.OBJECT_ID=B.OBJECT_ID AND A.INDEX_ID=B.INDEX_ID
WHERE A.AVG_FRAGMENTATION_IN_PERCENT>5 AND B.INDEX_ID>0
    --AND OBJECT_NAME(B.OBJECT_ID) IN ('INVMB')    --指定表
ORDER BY CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN N'重新生成索引' ELSE N'重新組織索引' END,OBJECT_NAME(B.OBJECT_ID),B.INDEX_ID

注：將【生成SQL陳述句】拷貝出來執行即可，

6.2、自動方式

第一步：在服務中啟動SQL Server 代理，

第二步：點擊"管理"->右鍵"維護計劃"->"新建維護計劃"，

第三步：起個名字，點擊"確定"，

第四步：點擊左側"工具箱"，將"重新生成索引"及"重新組織索引"拖至右邊區域，

第五步：分別對著"重新生成索引"及"重新組織索引"點擊右鍵->"編輯"->在"資料庫"項勾選要處理的資料庫->點擊"確定"，

第六步：點擊"新建作業計劃"按鈕->設定頻率及執行時間->點擊"確定"，

第七步：點擊"保存選定項"即可，

七、更新統計資訊

作用：UPDATE STATISTICS更新統計資訊來提高查詢效率，建議放在索引碎片計劃任務執行完成之后進行，

查看：查看某個表的統計資訊，可以在SSMS下面查看，

執行：

--方法一：UPDATE STATISTICS 表名
UPDATE STATISTICS INVMB

--方法二：執行存盤程序SP_UPDATESTATS(更新所有表)
EXEC SP_UPDATESTATS

后記：建議不要過于頻繁地執行重新生成索引、重新組織索引以及更新統計資訊，

標籤：其他

上一篇：SQL Server索引碎片維護進階

下一篇：MySQL8.0-練習制作大學生手游情況調查系統1