前言

上一篇我們研究了如何利用索引在數據庫里面調優，簡要的介紹了索引的原理，更重要的分析了如何選擇索引以及索引的利弊項，有興趣的可以點擊查看。

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

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

技術準備

數據庫版本為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_detailssys.dm_db_missing_index_groupssys.dm_db_missing_index_group_statssys.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 INTBEGIN 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       ENDEND    COMMIT TRANGO

利用這個測試腳本，我們新建了一張測試表，并且插入了一些測試數據，運行一個查詢

SELECT [KEY],[DATA]FROM BigTableWHERE DATA<1000GO

在這個簡單的查詢腳本中，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結構的不準確，確切的說是不能正確的提供平衡查詢的性能，或者大量的數據分頁造成