包含列的索引：SQL Server索引進階 Level 5

幾何魔君阿基米德發表於2017-11-29
索引SQLServer

作者David Durant，2011/07/13

關於系列

本文屬於Stairway系列：Stairway to SQL Server Indexes

索引是資料庫設計的基礎，並告訴開發人員使用資料庫關於設計者的意圖。 不幸的是，當效能問題出現時，索引往往被新增為事後考慮。 這裡最後是一個簡單的系列文章，應該使他們快速地使任何資料庫專業人員“快速”。

前面的級別引入了聚簇和非聚簇索引，突出了以下各個方面：

  • 表中每一行的索引總是有一個條目（我們注意到這個規則的一個例外將在後面的級別中進行討論）。 這些條目始終處於索引鍵序列中。
  • 在聚集索引中，索引條目是表的實際行。
  • 在非聚集索引中，條目與資料行分開; 由索引鍵列和書籤值組成，以將索引鍵列對映到表的實際行。

前面句子的後半部分是正確的，但不完整。 在這個級別中，我們檢查選項以將其他列新增到非聚集索引（稱為包含列）。 在檢查書籤操作的級別6中，我們將看到SQL Server可能會單方面向您的索引新增一些列。

包括列

在非聚集索引中但不屬於索引鍵的列稱為包含列。 這些列不是鍵的一部分，因此不影響索引中條目的順序。 而且，正如我們將會看到的那樣，它們比鍵列造成的開銷更少。

建立非聚集索引時，我們指定了與鍵列分開的包含列; 如清單5.1所示。

CREATE NONCLUSTERED INDEX FK_ProductID_ ModifiedDate
       ON Sales.SalesOrderDetail (ProductID, ModifiedDate)
       INCLUDE (OrderQty, UnitPrice, LineTotal)

清單5.1：建立包含列的非聚集索引

在本例中，ProductID和ModifiedDate是索引鍵列，OrderQty，UnitPrice和LineTotal是包含的列。

如果我們沒有在上面的SQL語句中指定INCLUDE子句，那麼結果索引看起來應該是這樣的：

ProductID   ModifiedDate   Bookmark

Page n:

707         2004/07/25        =>  
707         2004/07/26        =>  
707         2004/07/26        =>  
707         2004/07/26        =>  
707         2004/07/27        =>  
707         2004/07/27        =>  
707         2004/07/27        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  

Page n+1:

707         2004/07/29        =>  
707         2004/07/31        =>  
707         2004/07/31        =>  
707         2004/07/31        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>

但是，告訴SQL Server包含OrderQty，UnitPrice和LineTotal列時，索引如下所示：

:- Search Key Columns -:      :---  Included Columns  ---:     : Bookmark :

ProductID   ModifiedDate      OrderQty    UnitPrice   LineTotal       

Page n-1:

707         2004/07/29        1           34.99       34.99       =>  
707         2004/07/31        1           34.99       34.99       =>  
707         2004/07/31        3           34.99      104.97       =>  
707         2004/07/31        1           34.99       34.99       =>  
708         2001/07/01        5           20.19      100.95       =>  

Page n:

708         2001/07/01        1           20.19       20.19       =>  
708         2001/07/01        1           20.19       20.19       =>  
708         2001/07/01        2           20.19       40.38       =>  
708         2001/07/01        1           20.19       20.19       =>  
708         2001/07/01        2           20.19       40.38       =>  

708         2001/12/01        7           20.19      141.33       =>  
708         2001/12/01        1           20.19       20.19       =>  
708         2002/01/01        1           20.19       20.19       =>  
708         2002/01/01        1           20.19       20.19       =>  
708         2002/01/01        1           20.19       20.19       =>  

Page n+1:

708         2002/01/01        2           20.19       40.38       =>  
708         2002/01/01        5           20.19      100.95       =>  
708         2002/02/01        1           20.19       20.19       =>  
708         2002/02/01        1           20.19       20.19       =>  
708         2002/02/01        2           20.19       40.38       =>

檢查顯示的這個索引的內容，顯然這些行按索引鍵列排序。例如，修改日期為2002年1月1日（以粗體突出顯示）的產品708的五行在索引中是連續的，每隔一個ProductID / ModifiedDate組合的行也是如此。

你可能會問“為什麼甚至包括列？為什麼不簡單地將OrderQty，UnitPrice和LineTotal新增到索引鍵？“索引中有這些列但索引鍵中沒有這些列有幾個優點，例如：

  • 不屬於索引鍵的列不會影響索引內條目的位置。這反過來又減少了讓他們在索引中的開銷。例如，如果行中的ProductID或ModifiedDate值被修改，那麼該行的條目必須在索引內重新定位。但是，如果行中的UnitPricevalue被修改，索引條目仍然需要更新，但不需要移動。
  • 在索引中查詢條目所需的努力較少。
  • 指數的大小會略小。
  • 索引的資料分佈統計將更容易維護。

當我們檢視索引的內部結構以及由SQL Server維護的用於優化查詢效能的一些附加資訊時，大多數這些優勢在以後的級別中將更有意義。

確定索引列是否是索引鍵的一部分，或只是包含的列，不是您將要做的最重要的索引決定。也就是說，頻繁出現在SELECT列表中但不在查詢的WHERE子句中的列最好放在索引的包含列部分。

成為覆蓋指標

在級別4中，我們表示與AdventureWorks資料庫的設計者達成協議，決定將SalesOrderID / SalesOrderDetailID作為SalesOrderDetail表的聚集索引。針對此表的大多數查詢都將請求按銷售訂單編號排序或分組的資料。然而，可能來自倉庫人員的一些查詢將需要產品序列中的資訊。這些查詢將受益於清單5.1所示的索引。

為了說明在索引中包含列的潛在好處，我們將檢視兩個針對SalesOrderDetailtable的查詢，每個查詢我們將執行三次，如下所示：

  • 執行1：沒有非聚集索引
  • 執行2：使用不包含列的非聚簇索引（只有兩個關鍵列）
  • 執行3：使用清單5.1中定義的非聚集索引

正如我們在前面的級別所做的那樣，我們再次使用讀取次數作為主要度量標準，但是我們也使用SQL Server Management Studio的“顯示實際執行計劃”選項來檢視每個執行的計劃。這會給我們一個額外的指標：在非讀取活動上花費的工作量的百分比，例如在將相關資料讀入記憶體之後進行匹配。這使我們更好地瞭解查詢的總成本。

測試第一個查詢：產品的活動總數

清單5.2中顯示的第一個查詢是按特定產品的日期提供活動總計的查詢。

SELECT  ProductID ,
        ModifiedDate ,
        SUM(OrderQty) AS `No of Items` ,
        AVG(UnitPrice) `Avg Price` ,
        SUM(LineTotal) `Total Value`
FROM    Sales.SalesOrderDetail
WHERE   ProductID = 888
GROUP BY ProductID ,
        ModifiedDate ;

清單5.2：“按產品的活動總計”查詢

由於索引可以影響查詢的效能，但不影響結果; 對這三個不同的索引方案執行這個查詢總是產生下面的行集合：

ProductID   ModifiedDate    No of Rows  Avg Price         Total Value

----------- ------------    ----------- -----------------------------
888         2003-07-01      16          602.346           9637.536000
888         2003-08-01      13          602.346           7830.498000
888         2003-09-01      19          602.346           11444.574000
888        2003-10-01       2           602.346           1204.692000
888         2003-11-01      17          602.346           10239.882000
888         2003-12-01      4           602.346           2409.384000
888         2004-05-01      10          602.346           6023.460000
888         2004-06-01      2           602.346           1204.692000

這八行輸出從表中的三十九個“ProductID = 888”行聚合而成，每個日期有一個或多個“ProductID = 888”銷售的輸出行。進行測試的基本方案是 如程式碼5.3所示。 在執行任何查詢之前，請確保您執行SET STATISTICS IO ON。

IF EXISTS ( SELECT  1
            FROM    sys.indexes
            WHERE   name = `FK_ProductID_ModifiedDate`
                    AND OBJECT_ID = OBJECT_ID(`Sales.SalesOrderDetail`) ) 
    DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate ;
GO

--RUN 1: Execute Listing 5.2 here (no non-clustered index)

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate
ON Sales.SalesOrderDetail (ProductID, ModifiedDate) ;

--RUN 2: Re-execute Listing 5.2 here (non-clustered index with no include)

IF EXISTS ( SELECT  1
            FROM    sys.indexes
            WHERE   name = `FK_ProductID_ModifiedDate`
                    AND OBJECT_ID = OBJECT_ID(`Sales.SalesOrderDetail`) ) 
    DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate ;
GO

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate
ON Sales.SalesOrderDetail (ProductID, ModifiedDate)
INCLUDE (OrderQty, UnitPrice, LineTotal) ;

--RUN 3: Re-execute Listing 5.2 here (non-clustered index with include)

清單5.3：測試“按產品的活動總計”查詢

表5.1顯示了對每個索引方案執行查詢所需的相對工作量。

Run 1: No Nonclustered Index Table `SalesOrderDetail`. Scan count 1, logical reads 1238. Non read activity: 8%.
Run 2: Index – No Included Columns Table `SalesOrderDetail`. Scan count 1, logical reads 131. Non read activity: 0%.
Run 3: With Included Columns Table `SalesOrderDetail`. Scan count 1, logical reads 3. Non read activity: 1%.

表5.1：使用不同的非聚集索引可執行第一次查詢三次的結果

正如你可以從這些結果看到的：

  • 執行1需要完整掃描SalesOrderDetail表; 每一行都必須閱讀和檢查，以確定是否應該參與結果。
  • 執行2使用非聚集索引為39個請求的行快速查詢書籤，但它必須從表中單獨檢索每個行。
  • 執行3在非聚集索引中找到了所需的所有內容，並以最有利的順序 – 產品ID中的ModifiedDate。 它迅速跳到第一個要求的條目，閱讀了39個連續的條目，對每個條目進行了總計算，讀取完成。

測試第二個查詢：基於日期的活動總數

我們的第二個查詢與第一個查詢是相同的，除了WHERE子句的更改。 這次倉庫正在根據日期而不是產品請求資訊。 我們必須過濾最右邊的搜尋鍵列ModifiedDate; 而不是最左邊的一列ProductID。 新的查詢如清單5.4所示。

SELECT  ModifiedDate ,
        ProductID ,
        SUM(OrderQty) `No of Items` ,
        AVG(UnitPrice) `Avg Price` ,
        SUM(LineTotal) `Total Value`
FROM    Sales.SalesOrderDetail
WHERE   ModifiedDate = `2003-10-01`
GROUP BY ModifiedDate ,
        ProductID ;

清單5.4：“按日期的活動總計”查詢

生成的行集部分是：

ProductID   ModifiedDate    No of Items Avg Price             Total Value
----------- ------------    ----------- --------------------- ----------------
                                   :
                                   :
782         2003-10-01      62          1430.9937             86291.624000
783         2003-10-01      72          1427.9937             100061.564000
784         2003-10-01      52          1376.994              71603.688000
792         2003-10-01      12          1466.01               17592.120000
793         2003-10-01      46          1466.01               67436.460000
794         2003-10-01      37          1466.01               54242.370000
795         2003-10-01      22          1466.01               32252.220000
                                   :
                                   :
(164 row(s) affected)

WHERE子句將表格過濾為1492個符合條件的行; 其中，分組時，產生了164行的產出。

要執行測試，請按照程式碼5.3中所述的相同方案，但使用程式碼清單5.4中的新查詢。 結果是表5.2顯示了對每個索引方案執行查詢所需的相對工作量。

Run 1: No Nonclustered Index Table `SalesOrderDetail`. Scan count 1, logical reads 1238. Non read activity: 10%.
Run 2: With Index – No Included Columns Table `SalesOrderDetail`. Scan count 1, logical reads 1238. Non read activity: 10%.
Run 3: With Included Columns Table `SalesOrderDetail`. Scan count 1, logical reads 761. Non read activity: 8%.

表2：使用可用的不同非聚簇索引三次執行第二個查詢的結果

第一次和第二次測試都是相同的計劃。對SaleOrderDetail表的完整掃描。由於第4級中詳細說明的原因，WHERE子句沒有足夠的選擇性從非覆蓋索引中受益。而且，包含任何一個組的行都散佈在整個表格中。正在讀表時，每一行都必須與其組相匹配。以及消耗處理器時間和記憶體的操作。

第三個測試發現了它在非聚集索引中需要的一切;但與前面的查詢不同，它沒有找到索引內連續的行。構成每個單獨組的行在索引內是連續的;但是這些群體本身分散在指數的長度上。因此，SQL Server掃描索引。

掃描索引而不是表格有兩個好處：

  • 索引小於表，需要更少的讀取。
  • 行已經分組，需要較少的非閱讀活動。

結論

包含的列使非聚集索引能夠覆蓋各種查詢的索引，從而提高這些查詢的效能; 有時相當戲劇性。 包含的列增加了索引的大小，但在開銷方面增加了很少的內容。 每當你建立一個非聚集索引，特別是在一個外來鍵列上時，問問自己 – “我應該在這個索引中包含哪些額外的列？

相關文章