作者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掃描索引。
掃描索引而不是表格有兩個好處:
- 索引小於表,需要更少的讀取。
- 行已經分組,需要較少的非閱讀活動。
結論
包含的列使非聚集索引能夠覆蓋各種查詢的索引,從而提高這些查詢的效能; 有時相當戲劇性。 包含的列增加了索引的大小,但在開銷方面增加了很少的內容。 每當你建立一個非聚集索引,特別是在一個外來鍵列上時,問問自己 – “我應該在這個索引中包含哪些額外的列?