理解SQL Server的SQL查詢計劃)

入門指南

讓我們以一個簡單的例子協助你理解如何閱讀查詢計劃，可以通過發出SET SHOWPLAN_TEXT On命令，或者在SQL Query Analyzer 的配置屬性中設定同樣的選項等方式得到查詢計劃。

注意：這個例子使用了表pubs.big_sales，該表與pubs..sales表完全相同，除了多了80000行的記錄，以當作簡單explain plan例子的主要資料。

如下所示，這個最簡單的查詢將掃描整個叢集索引，如果該索引存在。注意聚集索引值是物理次序，資料按該次序存放。所以，如果聚集索引值存在，你將可能避免對整個表進行掃描。即使你所選的列不在聚集索引值中，例如ord_date，這個查詢引擎將用索引掃描並返回結果集。

SELECT *FROM big_salesSELECT ord_dateFROM big_salesStmtText-------------------------------------------------------------------------|--ClusteredIndexScan(OBJECT:([pubs].[dbo].[big_sales].[UPKCL_big_sales]))

上面的查詢展示返回的資料量非常不同，所以小結果集(ord_date)的查詢比其它查詢運行更快，這隻是因為存在大量底層的I/O。然而，這兩個查詢計劃實際上是一樣的。你可以通過使用其它索引提高效能。例如，在title_id列上有一個非叢集索引存在：

SELECT title_idFROM big_salesStmtText------------------------------------------------------------------|--Index Scan(OBJECT:([pubs].[dbo].[big_sales].[ndx_sales_ttlID]))

上面的查詢的執行時間與SELECT *查詢相比非常小，這是因為可以從非叢集索引即可得到所有結果。該類查詢被稱為covering query（覆蓋查詢），因為全部結果集被一個非叢集索引所覆蓋。

SEEK與SCAN

第一件事是你需要在查詢計劃中區別SEEK和SCAN操作的不同。

注意：一個簡單但非常有用的規則是SEEK操作是有效率的，而SCAN操作即使不是非常差，其效率也不是很好。 SEEK操作是直接的，或者至少是快速的，而SCAN操作需要對整個對象進行讀取（表，叢集索引或非叢集索引）。因此，SCAN操作通常比SEEK要消耗 更多的資源。如果你的查詢計劃僅是掃描操作，你就應該考慮調整你的查詢了。

where子句在查詢效能中能產生巨大的差異，如下面展示的：

Select *From big_salesWhere stor_id=’6380’StmtText-----------------------------------------------------------------------------|--ClusteredIndex Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales])),SEEK: ([big_sales].[stor_id]={@1} ORDERED FORWARD)

上面的查詢是在叢集索引上執行SEEK而不是SCAN操作。這個SHOWPLAN確切的描述SEEK操作是基於stor_id並且結果是按照在索引 中儲存的順序排序的。因為SQL Server支援索引的向前和向後滾動的效能是相同的，所以你可以在查詢計劃中看到ORDERED FORWARD 或ORDERED BACKWARD。這隻是告訴你表或索引讀取的方向。你甚至可以在ORDER BY子句中通過用ASC和DESC關鍵字操作這些行為。範圍查詢返回的查詢計劃，與前面的直接查詢的查詢計劃很相似。下面兩個範圍查詢可提供一些資訊：

Select *From big_salesWhere stor_id>=’7131’StmtText------------------------------------------------------------------------------|-ClusteredIndex Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ),SEEK: ([big_sales].[stor_id]>=’7131’) ORDER FORWARD 

上面的查詢看起來很象以前的例子，除了SEEK謂詞有點不同。

Select *From big_salesWhere stor_id between ‘7066’ and ‘7131’StmtText------------------------------------------------------------------------------|-ClusteredIndex Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ),SEEK:([big_sales].[stor_id]>=’7066’ and ([big_sales].[stor_id]<=’7131’) ORDER FORWARD)

這個看起來也一樣。只是尋找謂詞改變了。因為尋找是非常快的，所以這個查詢是相當好的。

SEEK和SCAN也可包含Where謂詞。在這種情況下，這個謂詞告訴你Where子句從結果集中過濾出哪些記錄。因為它是作為SEEK或 SCAN的一個組件執行的， Where子句通常既不損害也不提高這個操作本身的效能。Where子句會協助查詢最佳化工具找到可能有最佳效能的索引。

查詢最佳化的一個重要部分是要確定是否在某個索引上執行SEEK操作，如果是這樣，就找到了具有最佳效能的索引。大部分情況下，查詢引擎能出色地尋找到存在的索引。但是，目前有三種涉及到索引的常見問題：

◆資料庫設計師，通常是應用開發人員，在表中沒有建立任何索引。
◆資料庫設計師通常猜測不到常用的查詢或事務類型，所以建立在表上的索引或主鍵往往效率不高。
◆當索引表被建立時，即使資料庫設計師猜測較准，但事務負載隨著時間將發生改變，使得這些索引效率變差。

如果你在你的查詢計劃中看到大量的SCAN而不是SEEK，你應該從新評估你的索引。例如，看看下面的查詢：

Select ord_numFrom salesWhere ord_date IS NOT NULAnd ord_date>’Jan 01,2002 12:00:00 AM’StemtText----------------------------------------------------------------------------------|--Clustered Index Scan(OBJECT: ([pubs].[dbo].[sales].[UPKCL_sales] ),WHERE : ([sales].[ord_date]>’Jan 1,2002 12:00:00 AM ’))

現在這個查詢在我們剛建立的sales_ord_date索引上執行SEEK INDEX操作。

通過比較串連和子查詢說明分支步驟

一條正確的老規則是：在結果集相同的情況下，串連比子查詢具有更好的效能。

SELECT au_fname,au_lnameFROM authorsWHERE au_id IN (select au_id from titleauther ) StmtText-------------------------------------------------------------------------------------------|---Nested Loops(Inner Join, OUTER ReFERENCES:([titleauthor].[au_id])|--Stream Aggregate(GROUP BY:([titleauthor].[au_id]))| |--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[auidind]),ORDERED FORWARD)|--ClusteredIndex Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),SEEK:([authors].[au_id]=[titleauthor].[au_id]) ORDERED FORWARD)Table ‘authors’. Scan count 38,logical reads 76,physical reads 0,read-ahead reads 0.Table  ‘titleauthor’. Scan count 2, logical reads 2, physical reads 1,read-ahead reads 0.

在這種情況下，查詢引擎選擇一個嵌套迴圈操作。這個查詢被迫用叢集索引讀取整個authors表，在處理中執行大量的邏輯頁讀。

注意：在帶分支步驟的查詢中，縮排行給你展示那些步驟是其它步驟的分支。

Select distinct au_fname,au_lnameFrom authors as aJoin titleauthor as t ON a.au_id=t.au_idStmtText---------------------------------------------------------------------------------|--stream Aggregate(group by: ([a].[au_lname].[a].[au_fname]))|-Nested loops(Inner Join,OUTER REFERENCES: ([a].[au_id]))|-Index scan(OBJECT:([pubs].[dbo].[authors].[authord ]as[a]),ordered forward)|-Index Seek (OBJECT: [pubs].[dbo].[titleauthor].[authord ]as [t]),SEEK: ([t].[au_id]=[a].[au_id]) ORDER FORWARD)Table ‘titleauthors’ .Scan count 23,logical reads 23,physical reads 0,read ahead reads 0.Table ‘authors’ .Scan count 1,logical reads 1,physical reads 0,read-ahead read 0.

上面的這個查詢中，titleauthors表邏輯讀的數字上升而authors表下降。注意到，stream aggregation在查詢計劃中位置更高，即發生的更晚。

比較查詢計劃

可以使用查詢計劃比較兩個不同查詢的相對效果。如，你可能想知道是否一個查詢比另一個查詢增加了額外的開銷或者選擇了不同的索引策略。

在這個例子中，我們比較兩個查詢。第一個使用SUBSTRING第二個使用LIKE。

Select *From authorsWhere substring(au_lname,1,2)=’Wh’StmtText--------------------------------------------------------------|--ClusteredIndex  Scan(OBJECT: ([pubs].[dbo].[authors].[UPKCL_auidind])Where: (substring([authors].[au_lname],1,2)=’Wh’)) 

與使用LIKE的相似查詢相比較：

Select * From authorsWhere au_lname like ‘Wh%’StmtText-------------------------------------------------------------------------|-Bookmark Lookup(BOOKMARK: ([Bmk1000]),OBJECT: ([pubs].[dbo].[authors]))|-Index Seek(OBJECT:([pubs].[dbo].[authors].[sunmind]), SEEk:([authors].[au_lname]>=’WGp’ AND [authors].[au_lname]<’WI’),WHERE:(like([authors].[au_lname],’Wh%’,NULL)) ORDERED FORWARD) 

很明顯，第二個查詢使用INDEX SEEK操作，第一個查詢使用了CLUSTERED INDEX SCAN操作，第二個查詢計劃比第一個更簡單。

當我們比較查詢計劃中那一個最好時，事實上我們更願意用SET STATISTICS PROFILE ON或SQL Query Analyzer的Graphic Execution Plan(圖形執行計畫)特性而不是SET SHOWPLAN_TEXT ON。這些工具將以百分率的方式明確給你展示每一個查詢計劃步驟的處理消耗。這可以告訴你每種選擇的或多或少的查詢消耗情況。你還可以同時運行兩個或更多 查詢來看哪個執行的最好。

為了獲得最全面的效能測量資訊，使用SET STATISTICS IO 和SET STATISTICS TIME也是很重要的。

理解串連的影響

上文的不同查詢步驟展示了SQL Server 2000是如何運用大量的操作來解析Join(串連)的。每一個Join策略都有它的長處和短處。然而，在某些罕見的情況下，查詢引擎會選擇效率較低的策 略，如通常使用的Hash（散列）或Merge（合并）策略，而採用簡單的嵌套迴圈就足以提供很好的效能。

SQL Server 使用三種join(串連)策略，這裡由簡單到複雜分別列出：

嵌套迴圈

對於使用簡單內串連的小資料量表，嵌套迴圈是最佳策略。最適合兩個表的記錄數差別非常大，並且在串連的列上都有索引的情況。嵌套迴圈串連所需的I/O和比較都是最少的。

嵌套迴圈在外表（往往是小資料量的表）中每次迴圈一個記錄，然後在內表中尋找所匹配的記錄並輸出。有很多關於嵌套迴圈策略的名字。例如，對整個表或 索引進行查詢，稱為Naive（無知的）嵌套迴圈串連。使用正常索引或臨時索引時，被稱為索引嵌套迴圈串連或臨時索引嵌套迴圈串連。

合并

對於使用了排序串連列的大資料量並資料量相似的表，合并是最佳的策略。合併作業首先進行排序，然後對所有資料進行迴圈併產生輸出。良好的合并串連效能基於在相應的列上建立索引，通常在串連謂詞等式中用到的列。

合并串連發揮了預先排序的優點，從每個輸出中獲得行資料，直接進行比較操作。例如，例如，內串連返回的是串連謂詞相等的記錄。如果不相等，含更低值 的記錄將會被丟棄，並且用下一條記錄進行比較。這個過程將持續直到所有的記錄都被檢查完。有時合并串連被用來比較具有多對多關係的表。當這種情況發生時， SQL Server用暫存資料表來儲存這些行。

如果在使用合并串連的查詢中同時存在一個WHERE子句，那麼這個合并串連謂詞將首先被計算。然後經過合并串連謂詞的每條記錄將經過WHERE語句中的其他謂詞再次計算。Microsoft 稱之為residual predicate（剩餘謂詞）。

Hash（散列）

對於資料量大，容量不同的表，以及串連列沒有排序或索引的複雜串連需求，Hash是最佳策略。散列法被用於UNION, INTERSECT, INNER, LEFT, RIGHT和OUTER JOIN，以及集合匹配和差別等操作。Hash也用於沒有有用索引的串連表。Hash操作將建立臨時的Hash表並且迴圈所有的資料併產生輸出。

Hash使用一個build（已建造）輸入（通常是小資料量的表）和probe(探測)輸入。這個散列鍵（也就是在串連謂詞中的列，或在GROUP BY列表中的列）被查詢用來處理串連。剩餘謂詞是在WHERE子句中沒有用於串連本身的所有其他運算。剩餘謂詞是在串連謂詞之後計算。當構造一個Hash 串連時，SQL Server可按下面的優先次序選擇不同的選項：

In-memory Hash(記憶體中散列)：In-memory hash串連首先將整個build輸入掃描到記憶體中，然後在記憶體中建立一個臨時hash表。計算出Hash值，然後將每條記錄插入到Hash中。然後逐條掃描探測輸入。每條探測輸入將與對應的Hash相比較，如果匹配，將放在結果集中返回。

Hybrid Hash（混合散列）：如果散列僅比可用的記憶體稍大，SQL Server可能合并in-memory hash串連和grace hash串連的某些方面，稱之為hybrid hash 串連。

Grace Hash（優美散列）：當hash join太大而不能在記憶體中處理時，就要用到Grace hash選項。在那種情況下，整個build輸入和probe輸入都將被讀入。然後它們被分解成多個臨時的工作表，該步驟稱為分區扇出。Hash索引值上的 Hash函數確保了所有的串連記錄都在同一對分區工作表中。分區扇出將兩個耗時的步驟分解為很多小步驟，這些小步驟可以被並發處理。然後Hash串連將應 用於每對工作表，將所有匹配放在結果集中返回。

Recursive Hash(遞迴散列) ：有時Grace Hash產生的分區扇出表仍然太大以至需要更進一步的再分區，這個就叫做遞迴散列。

注意到，散列與合并串連將每個表都處理一次。如果使用SET STATISTICS IO ON來測量這種類型的查詢，會看到較低I/O的假象。然而，較低的I/O並不意味著這些串連策略一定比嵌套迴圈串連要快，因為還需要巨大的計算量。

注意，散列串連的計算量很大。如果你發現在生產中某些查詢始終用散列串連，這裡要提示你應該調優你的查詢或者在底層表中添加索引。

在下面的例子中，我們展示標準的嵌套迴圈（使用預設的查詢計劃）和散列與合并串連（強制使用提示）。

SELECT a.au_fname, a.au_lname, t.titleFROM authors AS aINNER JOIN titleauthor taON a.au_id=ta.au_idINNER JOIN titles tON t.title_id=ta.title_idORDER BY au_lname ASC, au_fname ASCStmtText-------------------------------------------------------------------------------------|--NestedLoop(Inner Join, OUTER REFERENCES:([ta],[title_id]))|--Nested Loops(Inner Join, OUTER REFERENCES:([a],[au_id]) )| |--IndexScan(OBJECT:([pubs].[dbo].[authors].[aunmind] AS [a],ORDERED FORWAD))| |--Index Seek(OBJECT:([pubs].[dbo].[titleauthor].[auidind] AS [ta],SEEK: ([ta].[au_id]=[a].[au_id]) ORDERED FORWAD)|--Clustered Index Seek (OBJECT:([pubs].[dbo].[titleas].[UPKCL_titleidind] AS [t],SEEK: ([t].[title_id]=[ta].[title_id]) ORDERED FORWAD)

上面展示計劃顯示的是通過SQL Server產生的標準查詢計劃。我們可以強制SQL Server利用提示給我們展示它是怎樣處理合并和散列串連的。

Select a.au_fname, a.au_lname, t.titleFrom authors AS aINNER MERGE JOIN titleauthor taON a.au_id=ta.au_idINNER HASH JOIN titles tON t.title_id=ta.title_idORDER BY au_lname ASC, au_fname ASC Warning:The join order has been enforced because a local join hint is used.StmtText-----------------------------------------------------------------------------------|--Sort(ORDER BY:([a].[au_lname] ASC ,[a].[au_fname] ASC)|--Hash Match(Inner Join,HASH:([ta].[title_id])=([t].[title_id]), RESIDUAL:([ta].[title_id]=[t].[title_id]))|--Merge Join(Inner Join, MERGE:([a] [au_id]=[ta].[au_id]), RESIDUAL:([ta].[au_id]=[a].[au_id]))| |--Clustered index Scan(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind] AS [a],ORDERED FORWAD)| |--Index Scan OBJECT:([pubs].[dbo].[titleauthor].[auidind] AS [ta], ORDERED FORWAD)|--Index Scan (OBJECT:([pubs].[dbo].[titles].[titleind] AS [t]))

在這個例子中，你可以很清晰的看到每一個串連將其他串連的謂詞作為剩餘謂詞。（你也會注意到提示的使用使SQL Server發出一個警告）。這個查詢還強制使用SORT操作來支援散列與合并串連。

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/zhou__zhou/archive/2007/06/26/1666500.aspx