SQL Server 效能調優2 之索引（Index）的建立

前言

索引是關聯式資料庫中最重要的對象之一，他能顯著減少磁碟I/O及邏輯讀取的消耗，並以此來提升 SELECT 語句的尋找效能。但它是一把雙刃劍，使用不當反而會影響效能：他需要額外的空間來存放這些索引資訊，並且當資料更新時需要一些額外開銷來保持索引的同步。

形象的來說索引就像字典裡的目錄，你要尋找某一個字的時候可以根據它的比劃/拼音先在目錄中找到對應的頁碼範圍，然後在該範圍中找到這個字。如果沒有這個目錄（索引），你可能需要翻遍整本字典來找到要找的字。

SQL Server 中的索引以 B-Tree 的形式儲存，如：

建立叢集索引（clustered index）來改進效能

RDBMS 隨著資料的增長都會面臨查詢效能的下降，索引就是專門設計來解決這個問題的。叢集索引是所有索引的基礎，沒有它資料表就是一個堆（heap）。叢集索引決定了資料的實體儲存體形態，所以一張表上只能有一個叢集索引。SQL Server 的 sys.partitions 系統檢視表中記錄著所有叢集索引的資訊（它們的 Index_ID為1）。

叢集索引可以包含多個欄位（列），通常應挑選絕大多數查詢語句中經常涉及到的篩選欄位，並且事先瞭解以下幾點：

• 欄位應當包含大量的非重複的值。例如：社會安全號碼
• 預設情況下主鍵欄位將自動建立叢集索引，但這不是必須的，你可以手工修改為非叢集索引（non-clustered index）
• 欄位經常參與篩選，即：經常在 WHERE, JOIN, ORDER BY, GROUP BY 語句中使用
• 欄位經常參與比較，即：經常參與 >, <, >=, <=, BETWEEN, IN 運算
• 欄位長度越短越好

另外在可能的情況下建議對叢集索引實施以下規則：

• 包含的欄位都設為唯一(unique)且非空(NOT NULL)
• 包含欄位的長度越短越好，包含的欄位越少越好
• 每張表都有叢集索引，並且把 WHERE 中經常使用到的欄位作為該叢集索引的欄位
• 盡量避免在 varchar 列上建立叢集索引

我們來做一次10w條資料的效能比較（測試資料的產生SQL請參照附錄）：

SELECT OrderDate,Amount,Refno FROM ordDemo WHERE Refno<3

索引建立前的執行計畫：

CREATE CLUSTERED INDEX idx_refno ON ordDemo(refno)GO--再次執行相同的查詢語句SELECT OrderDate,Amount,Refno FROM ordDemo WHERE Refno<3GO

建立索引後的執行計畫：

通過對比我們可發現I/O 消耗從 0.379421 降低為 0.0571991，並且從 Table Scan 處理轉變為 Index Seek。

建立非叢集索引（non-clustered index）來改善效能

上面提到了索引能有效改善查詢效能，但由於一張表只能有一個叢集索引，而一個叢集索引通常無法包含所有必要的列，所以 SQL Server 允許我們建立非叢集索引來實現這個需求。

【 SQL Server 2005 及之前的版本允許建立249 個非叢集索引；SQL Server 2008 及 SQL Server 2012 允許999個非叢集索引】

通常當你在某一個欄位上建立一個唯一鍵（unique key）的時候，SQL Server 會自動在該列上建立一個非叢集索引。sys.partitions 系統資料表中存放著非叢集索引的相關資訊（Index_ID>1）。

在為某張表建立非叢集索引之前請先確認兩點：該表是否真的需要非叢集索引？該表是否有合適的欄位來建立非叢集索引？

這是因為索引建得不好不但不能帶來效能的提高，還會花費額外的空間來存放索引併產生額外的 I/O 操作！

建立非叢集索引選擇欄位時應遵循以下規則：

• 欄位應當包含大量的非重複的值。
• 欄位經常參與等值（=）運算
• 欄位經常參與篩選，即：經常在 JOIN, ORDER BY, GROUP BY 語句中使用

我們繼續之前的測試，來看看非叢集索引帶來的速度提升：

SELECT OrderDate FROM ordDemoWHERE OrderDate='2011-11-28 20:29:00.000'GO

執行計畫如：

建立非叢集索引，並再次執行查詢：

CREATE NONCLUSTERED INDEX idx_orderdateon ordDemo(orderdate)GOSELECT OrderDate FROM ordDemoWHERE OrderDate='2011-11-28 20:29:00.000'GO

比較結果非常明顯，非叢集索引建立之後 I/O Cost, CPU Cost, Operator Cost 等消耗大幅下降。

在我們的例子中由於OrderDate 欄位並不在叢集索引中，所以前一次的查詢被解釋成一個index scan。當我們在OrderDate 上建立一個非叢集索引後，查詢將利用起該索引並解釋成 index seek。

隨著表的資料越來越多，用來存放非叢集索引的空間也會越來越大，並逐漸對效能造成影響。遇到這種情況可以把非叢集索引建立在獨立的資料庫檔案或檔案組(filegroup)中，從而減少對同一個檔案的 I/O 操作壓力。

合理的索引覆蓋來改善效能

執行下面的測試 SQL

SELECT OrderDate,OrderID FROM ordDemoWHERE OrderDate='2011-11-28 20:29:00.000'GO

觀察執行計畫後你會發現查詢被解析為 index scan，而不是先前的 index seek？這是因為我們已建立的兩個索引都沒有包含 OrderId 欄位。

把 non-clustered Index 刪掉了，重建立一下（把OrderId 欄位也作為索引的欄位）

CREATE NONCLUSTERED INDEX idx_orderdate_orderIdon ordDemo(orderdate DESC,OrderId ASC)GO

再次執行查詢，執行計畫如

查詢不出意料的再次被解析為 index seek。

注意：

一個索引中最多包含16個欄位，並且這些欄位的長度必須小於 900 byte。

以下類型不能作為索引的關鍵字段（text, ntext, image, nvarchar(max), varchar(max), varbinary(max)）

調整索引的包含欄位（including columns）來提高效能

索引的包含欄位的概念起源自 SQL Server 2005，SQL Server 2008 及 2012 也具備該功能。它允許你在非叢集索引中包含非鍵值(non-key)欄位，這些欄位不會記入索引的大小（這樣我們也就不太會促發上文提到的索引欄位上限）。另外這些欄位的類型可以是除 text, ntext, image 之外的任何類型。

在前文的測試案例中 OrderId 並不是一個關鍵字段，因為他並沒有在 WHERE 子句中進行篩選，所以把他作為索引的關鍵字段並不合適，現在我們用 INCLUDE 來把它建立為包含欄位：

--刪除前文的索引DROP INDEX idx_orderdate_orderId ON ordDemoGO--重建索引CREATE NONCLUSTERED INDEX idx_orderdate_Includedon ordDemo(orderdate DESC)INCLUDE(OrderID)GO--重新查詢SELECT OrderDate,OrderID FROM ordDemoWHERE OrderDate='2011-11-28 20:29:00.000'GO

執行計畫如：

從效能上來說本節的最佳化結果與上一節的幾乎一致，但採用了包含欄位索引（include column index） 後，你受到的限制更小，並伴隨著索引關鍵字段的減少，索引的佔用也變小查詢起來更高效。

總結下區分索引關鍵字段及包含欄位的基本原則：

• WHERE, ORDER BY, GROUP BY, JOIN-ON 中的使用到的欄位適用於關鍵字段
• SELECT, HAVING 中的使用到的欄位適用於包含欄位

使用過濾索引（filtered index）來提高效能

過濾索引起源自 SQL Server 2008 ，SQL Server 2012 也具備該功能，你可以把它看成一個帶著 WHERE 子句的非叢集索引。適當地使用能減少索引的儲存尺寸及維護消耗，同時提高查詢效能。

常規的索引都是對整張表的每條資料進行索引，而過濾索引僅僅對滿足特定條件的記錄進行索引，這個特定條件在建立過濾索引時通過 WHERE 子句來定義。

類似以下的情境你可以考慮採用過濾索引：

一張包含多年資料的巨型表，實際使用中僅查詢當年資料。

一張記錄產品類別的表，包含許多到期不再使用的類別。

一個訂單表，包含OrderStartDate 及 OrderEndDate 欄位。當訂單完成時更新OrderEndDate，其他情況為 null。你可以在 OrderEndDate 上建立過濾索引，這樣當你需要查詢哪些訂單未完成時可以利用。

在建立過濾索引時需要進行一些設定：

• ARITHABORT = ON
• CONCAT_NULL_YIELDS_NULL = ON
• QUOTED_IDENTIFIER = ON
• ANSI_WARNINGS = ON
• ANSI_NULLS = ON
• ANSI_PADDING = ON
• NUMERIC_ROUNDABORT = OFF

來看一下樣本：

SET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONSET ARITHABORT ONSET CONCAT_NULL_YIELDS_NULL ONSET QUOTED_IDENTIFIER ONSET NUMERIC_ROUNDABORT OFFGOCREATE NONCLUSTERED INDEX idx_orderdate_Filteredon ordDemo(orderdate DESC)INCLUDE(OrderId)WHERE OrderDate = '2011-11-28 20:29:00.000'GOSELECT OrderDate,OrderID FROM ordDemo WHERE OrderDate='2011-11-28 20:29:00.000'GO

I/O 消耗從上一節的0.0078751 減少為 0.003125，最佳化效果非常顯著。

使用資料行存放區索引（columnstore index）來提高效能

目前為止我們討論的都是行儲存索引（rowstore index），SQL Server 2012 開始支援資料行存放區索引。

行儲存索引在資料頁（data page）中儲存資料行，資料行存放區索引在資料頁中儲存資料列。假設我們有一張表（tblEmployee），包括 empId, FirstName, LastName 三列。行儲存索引/資料行存放區索引表現為以下儲存形式：

顯然當你需要對某幾列值進行尋找篩選的時候，資料行存放區索引需要訪問的資料頁更少，從而降低了I/O開銷，並因此提高了執行效率。在你決定採用資料行存放區索引之前建議你確認一下3點：

• 你的資料表是否可以設定為唯讀(read-only)
• 你的資料表是否非常巨大（百萬級以上）
• 如果你的資料庫是個OLTP，是否能允許你切換（開/關）資料行存放區索引

如果以上3點的答案都是OK的，那麼你可以開始使用資料行存放區索引了，不過你還會受到以下限制：

• 你不能包含1024個以上欄位
• 欄位類型只能是以下幾種：

int

big int

small int

tiny int

money

smallmoney

bit

float

real

char(n)

varchar(n)

nchar(n)

nvarchar(n)

date

datetime

datetime2

small datetime

time

datetimeoffset (precision <=2)

decimal 或 numeric (precision <=18)

好，我們來實驗一下資料行存放區索引：

執行以下的代碼，根據輸出的執行計畫可以發現它已經利用了我們先前建立的叢集索引（行儲存索引）。

SELECT  Refno  ,sum(Amount) as SumAmt  ,avg(Amount) as AvgAmtFROM  ordDemoWHERE  Refno>3Group By  RefnoOrder By  RefnoGO

接著我們把已經存在的行儲存索引刪除，建立資料行存放區索引：

DROP INDEX idx_refno ON ordDemoCREATE NONCLUSTERED COLUMNSTORE INDEXidx_columnstore_refnoON ordDemo (Amount,refno)

再次執行相同的查詢語句，執行計畫如：

通過比較，我們可以發現I/O消耗顯著下降:) 

注意：由於建立了資料行存放區索引，此時該表是唯讀，如果你要恢複成可寫的狀態必須刪除這個資料行存放區索引！

附錄

產生測試資料的SQL代碼：

--建表CREATE TABLE ordDemo (OrderID INT IDENTITY, OrderDate DATETIME,Amount MONEY, Refno INT)GO--插入 100000 條測試資料INSERT INTO ordDemo (OrderDate, Amount, Refno)  SELECT TOP 100000    DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2011-11-04' AS DATETIME)), ABS(a.object_id % 10), CAST(ABS(a.object_id % 13) AS VARCHAR)  FROM sys.all_objects aCROSS JOIN sys.all_objects bGO