SQL Server索引管理——索引建立建議和經驗

索引建立的建議

• 檢查WHERE語句和JOIN關聯列
• 使用窄索引
• 檢查列的唯一值（基數）
• 考慮列的順序
• 考慮索引類型（行索引 VS. 列索引；聚集索引 VS 非聚集索引）

如果一個表的資料較少，小于8KB，所有資料在一頁上，那麼表掃描可能比索引查找更适合

使用窄索引

你可以使用表中的多列組合建立索引。為擷取最好的性能，使索引中所包含的列盡可能的少。你也應該避免在索引中國使用寬資料類型列。擁有資料類型（CHAR，VARCHAR、NCHAR、NVARCHAR）的列，有時會很寬，就像二進制一樣；除非它們絕對需要，否則盡量減少在索引中使用大尺寸的寬資料類型列。

相比較于寬列索引，窄列索引在一個8KB的頁中，可以容納 更多的行，這有如下影響：

• 減少I/O（通過讀取較少8KB頁）
• 使資料庫緩存更有效，因為SQL Server可以緩存較少的索引頁，是以可以減少記憶體中索引頁所需要的邏輯讀
• 減少資料庫的存儲空間

為了解窄列索引如何影響邏輯讀，建立一個有20行資料，和一個索引，腳本如下：

IF(SELECT OBJECT_ID('t1')) IS NOT NULL
       DROP TABLE dbo.t1;
GO
CREATE TABLE dbo.t1(c1 INT, c2 INT);
WITH Nums AS
       (SELECT 1 AS n
        UNION ALL
        SELECT n+1
        FROM Nums
        WHERE n<20
        )
        INSERT INTO t1(c1,c2)
        SELECT n,2 FROM Nums;
CREATE INDEX i1 ON t1(c1);
           

因為索引列為窄列（INT資料類型為4位元組），所有索引行可以容納在一個8KB的索引頁中。可以使用如下動态視圖确認這個結論：

SELECT
       i.name
       ,i.type_desc
       ,s.page_count
       ,s.record_count
       ,s.index_level
       ,s.index_depth
FROM sys.indexes i
JOIN sys.dm_db_index_physical_stats(DB_ID(N'WideWorldImporters'),OBJECT_ID(N'dbo.t1'),NULL,NULL,'DETAILED') AS s
       ON i.index_id=s.index_id
WHERE i.[object_id]=OBJECT_ID(N'dbo.t1');
           

為了了解寬列索引的缺點，修改索引列c1的類型，将其由INT改為CHAR（500）：

DROP  INDEX t1.i1;
ALTER TABLE t1 ALTER COLUMN c1 CHAR(500);
CREATE INDEX i1 ON t1(c1);
           

INT類型資料的寬度是4位元組，CHAR（500）資料類型的寬度是500位元組。因為索引列的較大的寬度，需要兩個索引頁來容納20個索引行。你可以通過再次運作上面動态視圖腳本進行确認，結果如下：

大的索引鍵尺寸增加了索引頁數，是以增加了索引所需要的記憶體和硬碟。是以總是建議索引鍵列盡可能窄。

檢查列的唯一值

在小範圍可能值的列上建立索引（如性别），對性能沒有益處，因為查詢優化器将不能使用這種索引有效的減少傳回的行數。考慮性别列，僅僅隻有兩個唯一值：男和女。當你執行一個在WHERE中有性别列作為篩選條件的語句時，最終會從表中獲得大量的行（假定男女分布是平均的），導緻了代價昂貴的表掃描或者索引掃描。總是選擇WHERE語句中，有較多唯一行的列（high selectivity），來限制讀取的行數。你應該在那些列上建立索引，幫助優化器讀取較少的結果集。

進一步，當在多個列上建立索引時，通常被稱為組合索引列順序問題。在某些情況下，使用最多唯一值的列作為索引的第一列，将更有效的過濾資料。

注意：組合索引列順序的重要性将在後面的“考慮列順序”部分解釋。

從這點上，你可以看到，了解需要建立索引的列的唯一值的多少是很重要的，你可以通過執行類似如下腳本，獲得相關的資訊，隻需要調整表名和列名即可：

SELECT
       COUNT(DISTINCT Gender) AS DistinctColValuse
       ,COUNT(Gender) AS NumberOfRows
       ,COUNT(DISTINCT Gender)*1.0/COUNT(Gender)
FROM [HumanResources].[Employee];
           

WHERE語句或者JOIN關系中，擁有最多唯一值的列将是索引最好的候選者。

為了了解唯一值是如何影響索引的使用的，看一下HumanResources.Employee 表的Gender列，如果你運作前面的查詢，你将看到其有290行，隻包含2個唯一值，selectivity 為 0.006，一個僅僅查詢Gender值為F的查詢如下：

SELECT * FROM HumanResources.Employee with(index(IX_Employee_Test))
WHERE SickLeaveHours=59
       AND Gender='F'
       AND MaritalStatus='M';
           

執行計劃和IO消耗如下：

資料通過掃描聚集索引（資料存儲）獲得适合條件 Gender='F' 的值。如果你在Gender上建立索引如下：

CREATE INDEX IX_Employee_Test ON HumanResources.Employee(Gender)
           

并再次運作上面的查詢，其執行計劃不變。列中資料的selectivity不足夠支撐索引被使用，獨立使用。如果使用下面的組合索引

CREATE INDEX IX_Employee_Test ON HumanResources.Employee(Gender,SickLeaveHours,MaritalStatus)
with(drop_existing=on);
           

再次執行查詢，執行計劃及IO消耗如下：

現在效果要比聚集索引掃描好的多了。一個較為清晰索引查找操作将搜集資料的IO操作将近減半。其他的都花費在鍵查找上。

盡管問題中沒有一個單獨的列具有足夠的selectivity，作為有效的索引，但他們結合在一起，為優化器提供了足夠的selectivity，進而采用他們提供的索引。

有必要強制使用第一個建立的測試索引，如果你删除組合索引，再次建立最初的索引，并修改查詢，使用查詢提示，強制使用最初的索引，如下：

SELECT * FROM HumanResources.Employee with(index(IX_Employee_Test))
WHERE SickLeaveHours=59
       AND Gender='F'
       AND MaritalStatus='M';
           

你看到同樣是索引查找，但是邏輯讀次數多了近20倍。盡管強制優化器選擇索引是可能的，很清晰其不是最優的方法。

在SQL Server 2008以上另一個強制的不同行為是 FORCESEEK 查詢提示，FORCESEEK使得優化器僅選擇查找的操作。如果我們像下面重寫查詢：

SELECT * FROM HumanResources.Employee with(FORCESEEK)
WHERE SickLeaveHours=59
       AND Gender='F'
       AND MaritalStatus='M';
           

結果和強制使用索引一樣

限制優化器的選項，強制行為在某些情形可能有幫助，但是通常，如這裡的結果，其增加了讀，對整個查詢無益。

檢查資料類型

索引資料類型問題，如，整數鍵上的索引搜尋很快，因為其尺寸較小，并且容易在整數（INT）上進行算術運作。你也可以使用其他類型的整數類型，如BIGINT，SMALLINT和TINYINT作為索引列，然而字元類型，如CHAR、VARCHAR、NCHAR和NVARCHAR ，因為其需要字元比對，通常消耗要比整數要高。

假設你想要在一個列上建立索引，你有兩個候選列：一個是INT資料類型，另外一個是CHAR（4）資料類型。盡管在SQL Server中，兩個類型均占用4個位元組，你将仍然選擇整數列作為索引列。以算術運算操作為例。CHAR（4）資料類型中的1，實際上是以1開頭，後面跟三個空格的方式存儲的，是這樣四個位元組的結合：0x35、0x20、0x20、0x20. CPU不能了解這樣資料如何進行算術操作，是以，首先将其轉化為整數資料類型，1在整數類型下是這樣存儲的 0x00000001.CPU可以很容易的對這類資料執行算術操作。

當然，大多數時候，你沒有這種在資料類型尺寸相等的情況下進行簡單最優選擇。當設計或建立你的索引時，考慮這個情況。

考慮列順序

索引鍵先按照第一列進行排序，然後再按照下一列包含于前一列的值進行排序。組合索引中的第一列通常被稱為索引的leading edge。例如，考慮如下表：

如果在表上建立組合索引（c1，c2），那麼索引将排如下：

如上表展示的，資料首先按照組合索引中第一列c1列進行排序。第一列中的各值，資料在按照第二列（c2）進行排序。

是以，組合索引中列的順序是一個影響索引效率的重要因素。你可以這樣考慮：

• 列唯一值
• 列寬
• 列資料類型

例如，假設在表t1上的查詢均和下面的類似：

SELECT * FROM t1 WHERE c2=12;
SELECT * FROM t1 WHERE c2=12 AND c1=11;
           

一個如（c2，c1）的索引，将對兩個查詢都有益。但是索引（c1，c2）将不适合，因為它将按照c1列進行初始化排序，然而第一個查詢語句需要資料按照c2列進行排序。

為了了解索引列順序的重要性，考慮如下例子

SELECT COUNT(OrderQty) NumberOfOrderQtyRows
     ,COUNT(DISTINCT OrderQty) DistinctOrderQtyColValuse
     ,COUNT(DISTINCT OrderQty)*1.0/COUNT(OrderQty) OrderQtySelectivity
    ,COUNT(ProductID) NumberOfProductIDRows
     ,COUNT(DISTINCT ProductID) DistinctProductIDValuse
     ,COUNT(DISTINCT ProductID)*1.0/COUNT(ProductID) SalesProductIDSelectivity
FROM [Sales].[SalesOrderDetail];
           

WHERE語句或者JOIN關系中，擁有最多唯一值的列将是索引最好的候選者。

為了了解唯一值是如何影響索引的使用的，看一下[Sales].[SalesOrderDetail]表的QrderQty列，如果你運作上面的腳本，你将看到，它包含41個唯一值，121317行資料，其selectivity是0.0003.而ProductID列包含266個唯一值，其selectivity為0.002，一個包含QrderQty，ProductID列條件查詢如下：

SELECT  *
FROM [Sales].[SalesOrderDetail]
WHERE ProductID=714 AND OrderQty<=10 and OrderQty>5  ;
           

其查詢計劃如為：

在表上建立索引如下：

CREATE INDEX IX_SalesOrderDetail_Test ON [Sales].[SalesOrderDetail](OrderQty,ProductID)
           

再次執行查詢腳本，執行計劃及開銷如下：

CREATE INDEX IX_SalesOrderDetail_Test ON [Sales].[SalesOrderDetail](ProductID,OrderQty)
WITH(DROP_EXISTING=ON);
           

可以看到邏輯讀的次數有所降低。《SQL Server 索引優化—— 查詢條件中等于、大于或小于條件在索引中的順序對性能的影響》一文中闡述了索引順序對性能影響的另一種形式，有興趣者可以參考。

對于索引類型的選擇，将在後續文章中給出，敬請期待……

如果喜歡，可以搜尋關注 MSSQLServer 公衆号，将有更多精彩内容分享：