SQL Server 索引維護常用方法總結

索引維護是數據庫日常維護中一項重要的任務，SQL Server的索引維護其實主要圍繞下麵三個問題進行展開。

索引過多
索引不足
索引碎片率

本文同樣從這3個角度出發，介紹一些實用的日常維護方法和工具。

索引過多

索引過多是指每個表上麵的非聚集索引很多，並且有些非聚集索引很少用到。過多的索引，會導致增刪數據的效率降低，數據庫體積變大，索引以及統計信息的維護成本增加等負麵影響，建議定期檢查類似的索引，每個表上麵的索引最好不要超過10個。

通過下麵兩個DMV，定期檢查索引使用率，通過使用率決定是否需要該索引。sys.dm_db_index_operational_stats這個函數可以給出某個索引上麵的insert，update和delete的操作情況。sys.dm_db_index_usage_stats這個視圖可以給出訪問索引的所有方法的操作概覽。

--sys.dm_db_index_operational_stats
SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

--sys.dm_db_index_usage_stats
SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

上述結果中，可以看到，CountryRegionCurrency和AddressType表中，有兩個索引，沒有使用過。如果多次檢查，這兩個索引都還是沒有使用過的話，建議將其刪除。

索引不足

索引不足是指，要麼缺少索引，要麼有索引，但是沒有覆蓋所需的列，查詢效果不好。後者其實也可以歸納到索引不合適中。那麼我們來看下，如何才能找到缺失的索引。

SQL Server提供下麵4個DMV以供查詢missing index的情況。SQL Server重啟後，係統視圖中的內容就會更新，需要定期的將該信息保存下來。

sys.dm_db_missing_index_details 返回缺失的索引的詳細信息。
sys.dm_db_missing_index_group_stats 返回缺失索引組的概要信息。
sys.dm_db_missing_index_groups 返回缺失索引組中有哪些缺失的索引。
sys.dm_db_missing_index_columns 返回表中缺失索引的列。

如何通過檢測出來的缺失索引去新建索引，方法參考Using Missing Index Information to Write CREATE INDEX Statements 。

下麵語句，在每個庫上麵執行下麵的查詢，查看推薦建立的索引，包括創建語句。不過在創建索引前，需要綜合考量表中已有的索引，是否有可以合並的情況。

Use DB
SELECT 
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

創建index時，推薦按照下述順序進行。

將相等數據行列在最前
將不相等的數據行列在相等的數據行後
將include數據行列在create index語句的include子句中
若要決定相等數據行的順序，依據選擇性排列這些數據行，將選擇性最高的數據行排在最前

索引碎片率

新增、刪除和修改數據時，數據庫會自動維護索引。但時間長了之後，這些操作會造成數據不連續。這會對查找性能產生影響。

首先，觀察索引碎片的嚴重程度。

內部不連續（Internal Fragmentation）：數據頁中有很多空閑空間；

外部不連續（External Fragmentation）：

硬盤中擺放的分頁或區不連續，也就是數據表或索引散落在多個範圍中，以及存放數據表或者索引的頁不是按照實例連續存放的。
邏輯數據順序和實例在硬盤中的順序不同。

1. 用DBCC SHOWCONTIG觀察數據不連續

create index idCreditCard on CreditCard(CreditCardID)  with drop_existing
DBCC showcontig(CreditCard,idCreditCard)

紅框中的參數反應外部不連續狀況。索引idCreditCard總共用了43頁，6個區，光標掃描區時轉換了5次，每個區平均7.2頁，掃描密度100%，邏輯掃描片段為0，區掃描碎片率33.33% （=讀取時跳過的區數/總共使用的區數）。

最後兩個參數Avg. Bytes Free per Page和Avg. Page Density (full)則反應的是內部不連續的情況，平均每頁空閑字節數越大，說明內部不連續越嚴重。

可以通過定義一個臨時表來觀察數據不連續情況。

--BDCC Showcontig to show the fragmentation of table or index
create table #fraglist
(
objectName char (255),
objectID int,
IndexName char(255),
IndexID int,
Lvl int,
countPages int,
countRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecSize int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal
)
insert #fraglist exec('DBCC showcontig(CreditCard,idCreditCard) with tableresults')
select * from #fraglist

2. 通過sys.dm_db_index_physical_stats 觀察數據不連續情況

查看Department表的索引不連續情況：

select a.index_id,name,avg_fragmentation_in_percent from sys.dm_db_index_physical_stats
(DB_ID(),object_id(N'HumanResources.Department'),null,null,null)
as a join sys.indexes as b on a.object_id=b.object_id
and a.index_id=b.index_id;

查看數據庫中所有索引的碎片情況

use DB;
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 5
ORDER BY indexstats.avg_fragmentation_in_percent DESC

3. 根據數據片段狀況來判斷是否要重組或者重建索引。

當索引碎片大於5%，小於等於30%時，建議reorganize該索引；當索引碎片率大於30%時，建議rebuild該索引。Rebuild Index比較耗性能，建議在非工作時間進行，同時，建議使用online 的方式來rebuild index，以減少鎖的申請量。

重組索引：

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee
REORGANIZE ;

重建索引：

ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF )));

維護計劃重建索引

同時，對於自建的SQL Server數據庫，還可以通過創建維護計劃（maintenance plan）來重建索引。

最後更新：2017-06-13 11:31:39

SQL Server 索引維護常用方法總結

索引過多

索引不足

索引碎片率

上一篇： iOS11新特性之 Core ML [機器學習]

下一篇： ceph monitor paxos的實現(一)

相關內容

熱門內容

最新內容

SQL Server 索引維護常用方法總結

索引過多

索引不足

索引碎片率

上一篇： iOS11新特性 之 Core ML [機器學習]

下一篇： ceph monitor paxos的實現(一)

相關內容

熱門內容

最新內容

上一篇： iOS11新特性之 Core ML [機器學習]