SQL Server調優系列進階篇（如何維護資料庫索引）

前言

上一篇我們研究瞭如何利用索引在資料庫裡面調優，簡要的介紹了索引的原理，更重要的分析瞭如何選擇索引以及索引的利弊項，有興趣的可以點選檢視。

本篇延續上一篇的內容，繼續分析索引這塊，側重索引項的日常維護以及一些注意事項等。

閒言少敘，進入本篇的主題。

技術準備

資料庫版本為SQL Server2012，前幾篇文章用的是SQL Server2008RT，內容區別不大，利用微軟的以前的案例庫（Northwind）進行分析，部分內容也會應用微軟的另一個案例庫AdventureWorks。

相信瞭解SQL Server的朋友，對這兩個庫都不會太陌生。

 

一、建立索引

當我們要開始對錶進行索引的建立的時候，首先明確的是，一張表內只能建立一個聚集索引，最多可以建立最多249個非聚集索引(SQL Server2005)，在SQL Server2008以後聚集索引數提升至999個，上一篇文章我們知道對於聚集索引項一般要建立上，而非聚集索引項要根據日常的T-SQL語句進行選擇。

關於索引的選擇是一個很考驗調優能力的事情，大部分的情況下優質的索引新建全靠經驗而論，有興趣的可以點選查閱我前面的一系列關於分析查詢計劃的文章，掌握住裡面的精髓才能有的放矢。

當然，小白級別的也可以參照如下方法嘗試進行建立：

由於SQL Server有著自己的一套調優技巧，所以在我們每次執行的T-SQL語句應該怎樣優化，SQL Server是瞭如指掌的，所以它會將缺失的索引項進行記錄，用於提示使用者，嘗試去建立這些索引。

主要記錄在以下幾個DMV中

sys.dm_db_missing_index_details

sys.dm_db_missing_index_groups

sys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_columns(index_handle)

sys.dm_db_missing_index_details

關於這些個DMV的使用，來舉一個例子：

--新建表，建立主鍵，形成聚集索引
CREATE TABLE BigTable
(
   [KEY] INT,
   DATA INT,
   PAD CHAR(200),
   CONSTRAINT [PK1] PRIMARY KEY ([KEY])
)
GO
--批量插入測試資料250000行
SET NOCOUNT ON 
DECLARE @i INT
BEGIN TRAN
    SET @i=0
    WHILE @i<250000
    BEGIN
       INSERT BigTable VALUES(@i,@i,NULL)
       SET @i=@i+1
       IF @i%1000=0
       BEGIN
          COMMIT TRAN
          BEGIN TRAN
       END
END    
COMMIT TRAN
GO

利用這個測試指令碼，我們新建了一張測試表，並且插入了一些測試資料，執行一個查詢

SELECT [KEY],[DATA]
FROM BigTable
WHERE DATA<1000
GO

在這個簡單的查詢指令碼中，SQL Server已經提示了我們需要建立的索引項。我們可以右鍵，直接生成建立指令碼

SQL Server已經提示我們要建立的索引項內容了，穿件一個非聚集索引在列DATA上，並且INCLUDE列KEY，並且經建立完這個索引後的提升值都給計算出來了。

以上這種方式，在我們調優的時候是經常使用的，在我們拿到需要優化的語句後，直接執行就可以看到一部分需要調整的資訊了。

但是，大部分的T-SQL語句不允許我們進行這樣的優化流程，甚至有時候是已經存在的系統。所以，我們下手的方式只能繞道了，幸好SQL Server為我們記錄下了這些缺失索引項的資訊，就存在我上面提到的幾個DMV中。我們來檢視下：

SELECT migs.group_handle, mid.* 
FROM sys.dm_db_missing_index_group_stats AS migs 
INNER JOIN sys.dm_db_missing_index_groups AS mig 
ON (migs.group_handle = mig.index_group_handle) 
INNER JOIN sys.dm_db_missing_index_details AS mid 
ON (mig.index_handle = mid.index_handle) 
WHERE migs.group_handle = 2

所以，大部分情況下，通過檢視以上語句基本能確認到需要建立的索引項有哪些。

提示：但是，這裡的DMV資訊只是記錄自上次SQL Server啟動以後的資訊項，也就是說每次重啟之後這部分資訊就丟失了，所以對於生產系統，建議確保執行了一段週期之後再進行檢視。

知道了應該建立什麼樣的索引，下一步就是建立索引了，來看建立索引的指令碼

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

建立指令碼很簡單，指定索引型別、索引名稱、所屬表、包含列、篩選項、所屬檔案組以及操作項就可以建立了。

我相信基本搞過SQL Server資料的這塊指令碼一般不會陌生。

當然，如果不熟悉指令碼的方式，SQL Server也預設給提供了圖形化操作介面，傻瓜式操作

這裡我們重點分析幾點注意事項。

• UNIQUE：

該關鍵字指定索引項為唯一值，也就是非重複值，在實際應用中非常的有用，應為唯一就意味著這個索引的高選擇性，也就意味著當前索引的可用性高低。

前面文章已經分析了SQL Server會預設的在主鍵列上建立聚集索引，也是利用了主鍵的非空和唯一性特點。

當然，這裡也提示下聚集索引要求的就是唯一性，如果當前列確實存在重複值，那在建立聚集索引的時候SQL Server會預設的在當前列上加上一個唯一識別符號（uniqueifiter）在內部來保證索引的唯一性。但這個時候就不需要顯式的指定UNIQUE了，否則會報如下錯誤：

• CLUSTERED|NONCLUSTERED：

這個就是指定建立的索引為聚集還是非聚集索引。

關於它，這裡有幾點需要注意，因為非聚集索引的葉子節點儲存的就是聚集索引鍵值，所以在建立順序上要保證優先建立聚集索引，而後再建立非聚集索引，保證有足夠的儲存空間來存放非聚集索引。

在我們重新建立聚集索引的時候，SQL Server會預設的重新生成全部非聚集索引，如果表資料量特別大，這個過程會很漫長，如果不指定ONLINE的話，這個過程會是鎖定索引B-Teee的，這就意味著是阻塞的，業務就要停下來等待完成操作，切記不要將此事發生在生產機上。

當然，以上問題是可以避免的。

• index_name：索引的名字。
• column ：

建立索引所選的列了，提示下：不能將大型物件 (LOB) 資料型別 ntext、text、varchar(max)、 nvarchar(max)、varbinary(max)、xml 或 image 的列指定為索引的鍵列。 另外，即使 CREATE INDEX 語句中並未引用 ntext、text 或 image 列。如果想用這些型別的列可以存放於INCLUDE裡面。

• INCLUDE：

索引包含列，這個關鍵字非常有用，尤其在應對T-SQL的隨機IO問題上，具體內容可參照我前面的一系列的文章介紹。

還有前面提到的那些大型物件（LOB）資料型別，也可以包含進去，不過這裡有一點需要提示下，如果包含了大型物件，則建立索引不支援線上（ONLINE）操作，這就意味著必須選擇非業務器進行操作。

• PAD_INDEX = { ON | OFF }|FILLFACTOR =fillfactor

這個兩個選項是為了設定填充因子使用的，也是我們在建立索引的時候最常用的。

關於填充因子的作用簡單點講就是為了減少分頁而在索引空間中提前先預留空間。我們知道對於聚集索引在葉級別就包含了資料，所以使用者在這裡可以指定每個葉子保留的空間的大小，通過預留空間，就可以避免使用者新的資料填充而產生分頁現象，產生索引碎片影響效能。

當然，關於填充因子的內容支撐，是需要一部分基礎知識的，有興趣的可以點選此參照聯機叢書的官方介紹。

索引預設的的選項是OFF，也就是說基本不會預留太多空間。

關於這裡填充因子設定的數值大小問題，其實沒有一個固定的值，純粹是一個經驗值，來自於系統的場景和長期執行的總結。當然，如果非要給出的話，可以參照如下進行設定：

1.當讀寫比例大於100：1時，不要設定填充因子，100%填充

2.當寫的次數大於讀的次數時，設定50%-70%填充

3.當讀寫比例位於兩者之間時80%-90%填充

但是，這個值並不是被SQL Server所維護的，也就是說在這部分預留空間填滿之後，後者改資料頁刪除部分資料之後，還是會產生索引碎片，所以在系統執行過一段週期之後，我們需要手動的去重新整理索引，來維護好索引的秩序，維護方式也就是：重新建立，重新組織等。文章後面的會介紹。

• SORT_IN_TEMPDB = { ON | OFF }

這個就是指定當前索引排序是否要藉助TempDB庫，預設值為OFF。如果想快速的生成索引請將此選項指定為ON，當然弊端就是會擴大TempDB的大小，如果原表資料量特別多的話，這可能會是一個很大的空間值。

• STATISTICS_NORECOMPUTE = { ON | OFF}

這個指定是否同時更新統計資訊。預設是開啟的。我知道統計資訊的重要性，所以在建立的時候不要更改此值。

• DROP_EXISTING = { ON | OFF }

刪除或重建的時候是否重新生成已經命名先前存在的聚集或非聚集索引。預設是OFF。

這個選項非常的有用。刪除或者重建索引的時候整個流程是作為一個事務來處理的。所以，通常情況下，如果打算重建一個聚集索引的時候，需要先刪除聚集索引，而後再新建立一個，但是這個流程中，在刪除的時候SQL Server必須重建每一個非聚集索引將每一個非聚集索引的葉子節點有聚集索引鍵改成RID,然後新建過程，在重複的將所有的每一個非聚集索引的葉子節點由RID鍵更改成新的聚集索引鍵值。

這就是需要重建非聚集索引兩次，如果表資料量特別大的話，這個時間消耗就會很長很長…而且是阻塞的….

但是如果指定DROP_EXISTING選項為ON的話，就可以在建立或者刪除的時候只需要一次更改所有非聚集索引就可以。當然此方式也可以通過ALTER INDEX做到，後面分析。

• ONLINE = { ON | OFF }

是否線上提供索引建立，此方式也是資料庫的在05版本以後新新增的一大亮點，提供了線上狀態下索引的建立，但是僅限於Enterprise版本。

如果在生產系統中，業務併發時期可以採用這個選項進行索引的建立及維護，但相對離線建立的時間週期要明顯長很多，但是不會造成業務停機。

如果深入研究此方式的底層原理，其實就是資料的快照隔離機制，簡單點將就是在建立索引的時候，將相應的資料行提供了版本控制，避免了和正常業務系統的鎖爭用從而避免了阻塞，屬於樂觀鎖機制原理。

• MAXDOP = max_degree_of_parallelism

設定並行計劃的數量值。這個選項也很有用，如果是非業務高發期，可以適當調高此值來並行進行索引的建立，加快索引的建立速度。

當然，也受限於物理的CPU核數。還有就是此功能也只有Enterprise版提供。

• ALLOW_ROW_LOCKS = { ON | OFF }|ALLOW_PAGE_LOCKS = { ON | OFF }

此方式指定是否行鎖或者頁鎖，當然，只所以索引的建立和修改大部分情況下需要離線操作，就是因為在索引建立的時候加鎖了。為了加快索引的生成就必須新增相應的鎖。

如果 ALLOW_ROW_LOCKS = ON 且 ALLOW_PAGE_LOCK = ON，則訪問索引時允許行級、頁級和表級鎖。資料庫引擎將選擇相應的鎖，並且可以將鎖從行鎖或頁鎖升級到表鎖。

如果 ALLOW_ROW_LOCKS = OFF 且 ALLOW_PAGE_LOCK = OFF，則訪問索引時僅允許使用表級鎖。

一個有用的索引的建立需要耐心的建立出來，切勿草率的魯莽進行，如果操作不當有可能還會產生更多意外的情況。所以要充分把握好資料的特性，合理的建立好每一個有用的索引。

二、索引管理

經過上面一步的索引的建立，其實在日常的大部分時間就需要維護好索引。關於索引的維護基本就集中在以下幾個方面

a、索引的重建

當我們發現索引索引覆蓋範圍不夠或者存在大量索引鎖片，影響效能的時候，我們就需要對索引進行重建。

索引範圍的問題其實大部分來源於對於T-SQL語句效能的把握，也就是我們前面幾篇文章中分析的需要調優的內容項。

而關於索引碎片的形成，也是源於資料庫長時間的執行，大量的增刪該查造成了B-Tree結構的不準確，確切的說是不能正確的提供平衡查詢的效能，或者大量的資料分頁造成索引碎片，進而增大了IO，影響了效能。

關於索引碎片的檢視，可以通過以下DMV語句進行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 
INTO #TempFragmentation 
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; 
INSERT INTO #TempFragmentation 
SELECT TOP 20 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE s.database_id = DB_ID() 
AND i.name IS NOT NULL 
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
ORDER BY [Fragmentation %] DESC' 
SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC 
DROP TABLE #TempFragmentation

看到了，這部分索引的碎片到大了99%…這就需要我們重建進行維護了，否則將嚴重拖垮資料的效能。

維護的方式也就主要集中在以下幾種：

1、重建索引

這種方式簡單高效也就是我們上面分析的CREATE INDEX 命令後面加上DROP_EXISTING方式。當然可以聯機操作，操作方式參考文章前面

2、修改索引

這種方式是05版本以後才提供的，簡單點將就是ALTER INDEX命令進行。其實底層的執行方式同索引重建，只不過這種方式更改的選項多一些。

3、索引重組

這種方式就是重新填充索引裡面的資料，對於解決索引碎片的方式不如前面兩種來的直接。不過也是一種推薦的方式，因為此方式在執行的時候，也是隨時停止。

不像前面兩種方式為原子性操作，並且業務阻塞。

b、索引的禁用

關於索引的禁用，這個功能也是SQL Server2005版本以後才出現的新功能，這個功能一般應用的不多。

因為大部分情況下將索引禁用了，還倒不如直接將索引刪除掉來的直接。

但是，記住了既然SQL Server設計了它就是有它的用武之地的。

很多情況下，資料庫在執行很長一段時間之後，會發生壞頁的情況。而如果通過命令查詢，發現損壞也處於索引項上，那麼你所做的操作就是禁用這個索引（記住只能是禁用）

然後重新建立一個新索引就可以了。

在這種情況下我們可選的最快處理方式就是禁用該索引，因為一旦發生壞頁的情況，該索引項是不允許刪除的。

很多朋友就好奇了，索引來了個禁用，那我什麼時候啟用呢？…….

.嘿嘿…一旦問出了此問題，就說明了你對資料庫的理解還很淺…基本上還算沒有入門了……一旦索引禁用就意味著這個所以不再維護更新了….不再維護更新了那它裡面的資料就是過時的或者說不準確的…那還啟用它幹嘛…與其啟用還不如重新維護一個呢…

關於資料庫壞頁的情況，可以參照我前面寫的一篇文章，點選此。

c、索引的刪除

關於索引的刪除，就不需要太多的介紹了，原因很簡單，索引的存在會影響資料插入資料的速度，並且在查詢的時候需要維護等多的鎖，進而影響併發。

所以，一旦索引存在著一點優化的作用沒有，我們就要及時的刪除掉，因為百害而無一利嘛。

檢視未使用的索引DMV指令碼如下：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.name AS IndexName 
INTO #TempNeverUsedIndexes 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; 
INSERT INTO #TempNeverUsedIndexes 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.NAME AS IndexName 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id 
AND I.index_id = S.index_id 
AND DATABASE_ID = DB_ID() 
WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0 
AND I.name IS NOT NULL 
AND S.object_id IS NULL' 
SELECT * FROM #TempNeverUsedIndexes 
ORDER BY DatbaseName, SchemaName, TableName, IndexName 
DROP TABLE #TempNeverUsedIndexes

當然，這些記錄都是自動SQL Server啟動以來未曾使用的索引，所以在生產系統中，一定要確保已經執行了一段週期了。

索引指令碼的刪除，很簡單和表刪除類似，直接drop掉就可以了。

當然，最後再贈送一個DMV，檢視那些經常被大量更新，但是卻基本不適用的索引項

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups 
AS [System usage] 
INTO #TempUnusedIndexes 
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; 
INSERT INTO #TempUnusedIndexes 
SELECT TOP 20 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups 
AS [System usage] 
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE s.database_id = DB_ID() 
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
AND s.user_seeks = 0 
AND s.user_scans = 0 
AND s.user_lookups = 0 
AND i.name IS NOT NULL 
ORDER BY s.user_updates DESC' 
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC 
DROP TABLE #TempUnusedIndexes

關於這些指令碼，就要自己酌情考慮是否刪除了，不能一概而論。

索引的知識實在是太廣泛..我通過兩篇文章進行此塊內容的分析，關於如何利用索引可以參照我上一篇文章….點選此….

..此篇先到此吧…文章寫的有點糙….

關於調優內容太廣泛，我們放在以後的篇幅中介紹，有興趣的可以提前關注。

結語

有問題可以留言或者私信，隨時恭候有興趣的童鞋加入SQL SERVER的深入研究。共同學習，一起進步。