閱讀查詢計劃：SQL Server索引層級9

標籤：並且   訊息   不同   平行處理   ddl   soft   入行   www.   group by   

David Durant，2011/10/05

原文連結：http://www.sqlservercentral.com/articles/Stairway+Series/72441/ 該系列 本文是“Stairway系列：SQL Server索引的階梯”的一部分 索引是資料庫設計的基礎，並告訴開發人員使用資料庫關於設計者的意圖。不幸的是，當效能問題出現時，索引往往被添加為事後考慮。這裡最後是一個簡單的系列文章，應該使他們快速地使任何資料庫專業人員“快速” 在整個階段，我們經常說某個查詢以某種方式執行，我們引用產生的查詢計劃來支援我們的陳述。 Management Studio顯示的估計和實際查詢計劃可以協助您確定索引的收益或缺乏。因此，這個層級的目的是讓您充分瞭解查詢計劃，您可以： ?當你閱讀這個階梯時，驗證我們的斷言。 ?確定您的索引是否有益於您的查詢。 有許多關於閱讀查詢計劃的文章，其中包括MSDN庫中的一些文章。這裡我們不打算擴大或取代它們。事實上，我們會在這個層面提供其中的許多連結/參考。顯示圖形執行計畫（http://msdn.microsoft.com/zh-cn/library/ms178071.aspx）是一個很好的開始。其他有用的資源套件括Grant Fritchey的書，SQL Server執行計畫（以電子書形式免費提供）和Fabiano Amorim關於在查詢計劃輸出中找到的各種運算子的Simple-Talk文章系列（http：//www.simple-talk .COM /作家/法比亞諾 - 阿莫林/）。 圖形查詢計劃 查詢計劃是SQL Server執行查詢的一組指令。 SQL Server Management Studio將以文本，圖形或XML格式顯示查詢計劃。例如，考慮以下簡單的查詢：

SELECT LastName, FirstName, MiddleName, Title 

FROM Person.Contact

WHERE Suffix = ‘Jr.‘

ORDER BY Title

這個查詢的計劃可以看成1所示。

圖1 - 圖形格式的實際查詢計劃 或者，它可以被視為文本：

|--Sort(ORDER BY:([AdventureWorks].[Person].[Contact].[Title] ASC)) |--Clustered Index Scan(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID]), WHERE:([AdventureWorks].[Person].[Contact].[Suffix]=N‘Jr.‘))

或者作為一個XML文檔，像這樣開始：

查詢計劃的顯示可以請求如下： ?要請求圖形查詢計劃，請使用Management Studio的SQL編輯器工具列，它具有“顯示估計執行計畫”和“包括實際執行計畫”按鈕。 “顯示估計執行計畫”選項立即顯示所選TSQL代碼的查詢計劃圖，而不執行查詢。 “包括實際執行計畫”按鈕是一個開關，一旦您選擇了此選項，您執行的每個查詢批次都將顯示新查詢計劃圖表以及結果和訊息。這個選項可以在圖1中看到。 ?要請求文字查詢計劃，請使用SET SHOWPLAN_TEXT ON語句。開啟文本版本將關閉圖形版本，不會執行任何查詢。 ?要查看XML版本，請按右鍵圖形版本，然後從操作功能表中選擇“顯示執行計畫XML”。 對於這個層級的其餘部分，我們將重點放在圖形視圖上，因為它通常提供對計劃的最快理解。對於查詢計劃，一張圖片通常勝過千言萬語。 閱讀圖形查詢計劃 圖形查詢計劃通常從右至左讀取;最右邊的表徵圖表示資料收集流中的第一步。這通常是訪問堆或索引。你不會看到這裡使用的單詞表;相反，您將看到聚簇索引掃描或堆掃描。這是首先看看哪些索引，如果有的話，正在使用。 圖形查詢計劃中的每個表徵圖代表一個操作。有關可能的表徵圖的其他資訊，請參閱http://msdn.microsoft.com/zh-cn/library/ms175913.aspx上的圖形執行計畫表徵圖 串連操作的箭頭表示行，從一個操作流出並進入下一個操作。 將滑鼠放在表徵圖或箭頭上會導致顯示其他資訊。 不要把操作當作一個步驟，因為這意味著一個操作必須在下一個操作開始之前完成。這不一定是真的。例如，當WHERE子句被評估時，也就是說，當一個Filter操作被執行時，行被一次評估一個;不是一次全部。在下一行到達過濾器操作之前，行可以移動到下一個操作。另一方面，排序操作必須在第一行移動到下一個操作之前全部完成。 使用一些額外的資訊 圖形查詢計劃顯示兩個不屬於計劃本身的可能有用的資訊;建議的指標和每個操作的相對成本。 在上面的樣本中，建議的索引（以綠色顯示並按空間要求截斷）建議在連絡人表的尾碼列上使用非聚簇索引;包括標題，名字，中間名和姓氏的列。 這個計劃的每個操作的相對成本告訴我們，排序操作是總成本的5％，而表掃描是95％的工作。因此，如果我們想提高這個查詢的效能，我們應該解決表掃描，而不是排序;這就是為什麼建議索引。如果我們建立推薦的索引，像這樣：

CREATE NONCLUSTERED INDEX IX_Suffix ON Person.Contact

(

Suffix

)

INCLUDE ( Title, FirstName, MiddleName, LastName )

然後重新執行查詢，我們的讀數從569降到3; 而下面顯示的新查詢計劃顯示了原因。

新的非叢集索引（索引鍵為Suffix）具有“WHERE Suffix =‘Jr.”條目聚集在一起; 因此，檢索資料所需IO的減少。 因此，與之前計劃中的排序操作相同的排序操作現在佔查詢總成本的75％以上，而不是僅僅是原來成本的5％。 因此，最初的計劃需要75/5 = 15倍的工作量來收集與當前計劃相同的資訊。 由於我們的WHERE子句只包含一個等號運算子，所以我們可以通過將Title列移入索引鍵來改進我們的索引，如下所示：

IF  EXISTS (SELECT * FROM sys.indexes

WHERE OBJECT_ID = OBJECT_ID(N‘Person.Contact‘)

 

AND name = N‘IX_Suffix‘)

DROP INDEX IX_Suffix ON Person.Contact

CREATE NONCLUSTERED INDEX IX_Suffix ON Person.Contact

(

Suffix, Title

)

INCLUDE ( FirstName, MiddleName, LastName )

現在，所需的條目仍然聚集在索引內，並且在每個叢集內，它們都是按照請求的順序; 如新查詢計劃所示，2所示。

 

圖2-重建非叢集索引後的查詢計劃

 

該計劃現在顯示，排序操作不再需要。 在這一點上，我們可以放棄我們非常有利的覆蓋指數。 這將恢複連絡人表格的方式，當我們開始時， 當我們進入我們的下一個主題時，這是我們希望的狀態。

 

查看並行流

 

如果兩行可以平行處理，它們將在圖形顯示中上下顯示。 箭頭的相對寬度表示在每個流中正在處理的行數。 例如，以下加入，擴充了以前的查詢以包含銷售資訊：

 

SELECT C.LastName, C.FirstName, C.MiddleName, C.Title

 

, H.SalesOrderID, H.OrderDate

 

FROM Person.Contact C

 

JOIN Sales.SalesOrderHeader H ON H.ContactID = C.ContactID

 

WHERE Suffix = ‘Jr.‘

 

ORDER BY Title

 

查詢計劃3所示。

 

圖3 - JOIN的查詢計劃 快速查看計劃告訴我們一些事情： ?兩張桌子同時被掃描。 ?大部分工作用於掃描表格。 ?出來的更多行或SalesOrderHeader表比出Contact表更多。 ?兩個表格沒有聚整合相同的序列; 因此將每個SalesOrderHeader行與其連絡人行進行匹配將需要額外的努力。 在這種情況下，使用雜湊匹配操作。 （關於雜湊的更多資訊。） ?排序所選行所需的工作量可以忽略不計。 即使是單獨的行流也可以分解成單獨的較少行的流，以利用平行處理。 例如，如果我們將上述查詢中的WHERE子句更改為WHERE Suffix為NULL。 更多的行將被返回，95％的Contact行有NULL尾碼。 新的查詢計劃反映了這一點，4所示。

 

圖4 - 一個並行查詢計劃 新的計劃也向我們展示了連絡人行數的增加，導致匹配和排序操作成為此查詢的關鍵路徑。如果要提高績效，就要先攻擊這兩個行動。再次，內含資料行索引將有所協助。 像大多數串連一樣，我們的例子通過外鍵/主鍵關係串連兩個表。其中的一個表Contact（連絡人）按ContactID進行排序，ContactID也恰好是其主鍵。在另一個表中，SaleOrderHeader，ContactID是一個外鍵。由於ContactID是一個外鍵，因此ContactID訪問的SaleOrderHeader資料請求（例如我們的聯結樣本）可能是常見的業務需求。這些請求將受益於ContactID上的索引。 無論何時索引一個外鍵列，總是問自己，如果有的話，列應該作為包含列添加到索引中。在我們的例子中，我們只有一個查詢，而不是一系列的查詢來支援。因此，我們唯一包含的列將是OrderDate。為了支援針對SaleOrderHeader表的一系列面向ContactID的查詢，我們會根據需要在索引中包含更多的SaleOrderHeader列以支援這些附加查詢。 我們的CREATE INDEX語句是：

CREATE NONCLUSTERED INDEX IX_ContactID ON Sales.SalesOrderHeader

(

ContactID

)

INCLUDE ( OrderDate )

而執行我們的SalesOrderHeader和Contact資訊串連的新計劃5所示。

 

圖5 - 計劃在每個表上使用支援索引的JOIN查詢 因為兩個輸入資料流現在都由串連謂詞列ContactID進行排序;查詢的JOIN部分可以在不分割流的情況下完成，也不需要散列;從而將工作負載的26 + 5 + 3 = 34％減少到工作負載的4％。 排序，推送和散列 許多查詢操作要求在執行操作之前將資料分組。這些包括DISTINCT，UNION（意味著不同），GROUP BY（及其各種彙總函式）和JOIN。通常，SQL Server將使用以下三種方法之一來實現這個分組，第一個方法需要您的協助： ?愉快地探索資料已經預先分類到分組序列中。 ?通過執行散列操作對資料進行分組。 ?將資料分類到分組順序中。 預分類 索引是您預測資料的方式;即以經常需要的順序向SQL Server提供資料。這就是為什麼建立非聚簇索引（每個都包含列）都使我們以前的例子受益。實際上，如果將滑鼠放在最近查詢中的“合并串連”表徵圖上，則會使用兩個適當排序的輸入資料流匹配行，並利用它們的排序次序。會出現。這會通知您兩個表/索引的行使用記憶體和處理器時間的絕對最小值進行串連。適當的排序輸入是一個很棒的短語，當滑鼠移至上方在查詢計劃表徵圖上時，它會驗證您選擇的索引。 雜湊 如果傳入資料的順序不合適，SQL Server可能會使用散列操作對資料進行分組。雜湊是一種可以使用大量記憶體的技術，但通常比分類更有效。在執行DISTINCT，UNION和JOIN操作時，散列與排序相比有一個優勢，即單個行可以傳遞到下一個操作，而不必等待所有傳入行被散列。但是，在計算分組彙總時，必須先讀取所有輸入行，然後才能將任何彙總值傳遞給下一個操作。 散列資訊所需的記憶體量與所需組的數量直接相關。因此，需要散列來解決：

SELECT Gender, COUNT(*)

FROM NewYorkCityCensus

GROUP BY Gender

只需要很少的記憶，因為只會有兩組; 女性和男性，無論輸入行的數量。 另一方面：

SELECT LastName, FirstName, COUNT(*)

FROM NewYorkCityCensus

GROUP BY LastName, FirstName

會導致大量的群體，每個群體都需要自己的記憶空間;可能消耗太多記憶體，雜湊成為解決查詢的不良技術。 有關查詢計劃散列的更多資訊，請訪問http://msdn.microsoft.com/en-us/library/ms189582.aspx。 排序 如果資料沒有被預分類（索引），並且如果SQL Server認為雜湊不能有效地完成，SQL Server將對資料進行排序。這通常是最不可取的選擇。因此，如果在計劃的早期出現“排序”表徵圖，請檢查是否可以改進索引。如果Sorticon出現在計劃末尾附近，這可能意味著SQL Server將最終輸出按ORDER BY子句所請求的順序排序;並且該序列與用於解析查詢的JOIN，GROUP BY和UNION的序列不同。通常情況下，你可以做些什麼來避免這種情況。 結論 查詢計劃顯示SQL Server打算使用或已經使用的方法來執行查詢。它通過詳細描述將要使用的操作，從操作到操作的行的流程以及涉及的並行性來實現。您將此資訊視為文本，圖形或XML顯示。 ?圖形計劃顯示每個操作的相對工作量。 ?圖形計劃可能會建議一個索引，以提高查詢的效能。 ?瞭解查詢計劃將協助您評估和最佳化索引設計

 

閱讀查詢計劃：SQL Server索引層級9