SQLServer索引維護常用方法總結

傾歡發表於2017-06-13
SQLServer索引
索引維護是資料庫日常維護中一項重要的任務，SQL Server的索引維護其實主要圍繞下面三個問題進行展開。
  1. 索引過多
  2. 索引不足
  3. 索引碎片率

本文同樣從這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

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

 2

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

索引不足

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

SQL Server提供下面4個DMV以供查詢missing index的情況。SQL Server重啟後，系統檢視中的內容就會更新，需要定期的將該資訊儲存下來。

  1. sys.dm_db_missing_index_details 返回缺失的索引的詳細資訊。
  2. sys.dm_db_missing_index_group_stats 返回缺失索引組的概要資訊。 
  3. sys.dm_db_missing_index_groups 返回缺失索引組中有哪些缺失的索引。
  4. 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

 3

建立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)

4

紅框中的引數反應外部不連續狀況。索引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

6

 

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）來重建索引。


相關文章