如何深入分析SqlServer查詢計劃

本文主要側重對SqlServer查詢計劃的理解，尤其是對微軟復雜的運算函數的理解，供大家參考。

首先，打開【SQL Server Management Studio】，輸入一個查詢語句看看SQL Server是如何顯示查詢計劃的吧。

說明：本文所演示的數據庫，是我為一個演示程序專用准備的數據庫，可以在此網頁中下載。

select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Finished

from  OrdersView as v

where v.OrderDate >= '2010-12-1' and v.OrderDate < '2011-12-1';

其中，OrdersView是一個視圖，其定義如下：

SELECT   dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate,

dbo.Orders.SumMoney, dbo.Orders.Finished,

ISNULL(dbo.Customers.CustomerName, N'') AS CustomerName

FROM     dbo.Orders LEFT OUTER JOIN

dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

對於前一句查詢，SQL Server給出的查詢計劃如下(點擊工具欄上的【顯示估計的執行計劃】按鈕)：

從這個圖中，我們至少可以得到3個有用的信息：

1. 哪些執行步驟花費的成本比較高。顯然，最右邊的二個步驟的成本是比較高的。

2. 哪些執行步驟產生的數據量比較多。對於每個步驟所產生的數據量， SQL Server的執行計劃是用【線條粗細】來表示的，因此也很容易地從分辨出來。

3. 每一步執行了什麼樣的動作。

對於一個比較慢的查詢來說，我們通常要知道哪些步驟的成本比較高，進而，可以嘗試一些改進的方法。 一般來說，如果您不能通過：提高硬件性能或者調整OS,SQL Server的設置之類的方式來解決問題，那麼剩下的可選方法通常也只有以下這些了：

1. 為【scan】這類操作增加相應字段的索引。

2. 有時重建索引或許也是有效的，具體情形請參考後文。

3. 調整語句結構，引導SQL Server采用其它的查詢方案去執行。

4. 調整表結構(分表或者分區)。

下面再來說說一些很重要的理論知識，這些內容對於執行計劃的理解是很有幫助的。

回到頂部SQL Server 查找記錄的方法

說到這裡，不得不說SQL Server的索引了。SQL Server有二種索引：聚集索引和非聚集索引。二者的差別在於：【聚集索引】直接決定了記錄的存放位置， 或者說：根據聚集索引可以直接獲取到記錄。【非聚集索引】保存了二個信息：1.相應索引字段的值，2.記錄對應聚集索引的位置(如果表沒有聚集索引則保存記錄指針)。 因此，如果能通過【聚集索引】來查找記錄，顯然也是最快的。

SQL Server 會有以下方法來查找您需要的數據記錄：

1. 【Table Scan】：遍歷整個表，查找所有匹配的記錄行。這個操作將會一行一行的檢查，當然，效率也是最差的。

2. 【Index Scan】：根據索引，從表中過濾出來一部分記錄，再查找所有匹配的記錄行，顯然比第一種方式的查找范圍要小，因此比【Table Scan】要快。

3. 【Index Seek】：根據索引，定位(獲取)記錄的存放位置，然後取得記錄，因此，比起前二種方式會更快。

4. 【Clustered Index Scan】：和【Table Scan】一樣。注意：不要以為這裡有個Index，就認為不一樣了。 其實它的意思是說：按聚集索引來逐行掃描每一行記錄，因為記錄就是按聚集索引來順序存放的。 而【Table Scan】只是說：要掃描的表沒有聚集索引而已，因此這二個操作本質上也是一樣的。

5. 【Clustered Index Seek】：直接根據聚集索引獲取記錄，最快!

所以，當發現某個查詢比較慢時，可以首先檢查哪些操作的成本比較高，再看看那些操作在查找記錄時， 是不是【Table Scan】或者【Clustered Index Scan】，如果確實和這二種操作類型有關，則要考慮增加索引來解決了。 不過，增加索引後，也會影響數據表的修改動作，因為修改數據表時，要更新相應字段的索引。所以索引過多，也會影響性能。 還有一種情況是不適合增加索引的：某個字段用0或1表示的狀態。例如可能有絕大多數是1，那麼此時加索引根本就沒有意義。 這時只能考慮為0或者1這二種情況分開來保存了，分表或者分區都是不錯的選擇。

如果不能通過增加索引和調整表來解決，那麼可以試試調整語句結構，引導SQL Server采用其它的查詢方案去執行。 這種方法要求： 1.對語句所要完成的功能很清楚， 2.對要查詢的數據表結構很清楚， 3.對相關的業務背景知識很清楚。 如果能通過這種方法去解決，當然也是很好的解決方法了。不過，有時SQL Server比較智能，即使你調整語句結構，也不會影響它的執行計劃。

如何比較二個相同功能的SQL語句的性能好壞呢，我建議采用二種方法： 1. 直接把二個查詢語句放在【SQL Server Management Studio】，然後去看它們的【執行計劃】，SQL Server會以百分比的方式告訴你二個查詢的【查詢開銷】。 這種方法簡單，通常也是可以參考的，不過，有時也會不准，具體原因請接著往下看(可能索引統計信息過舊)。

2. 根據真實的程序調用，寫相應的測試代碼去調用：這種方法就麻煩一些，但是它更能代表現實調用情況， 得到的結果也是更具有參考價值的，因此也是值得的。

回到頂部SQL Server Join 方式

在SQL Server中，每個join命令，都會在內部執行時采用三種更具體的方式來運行：

1. 【Nested Loops join】，如果一個聯接輸入很小，而另一個聯接輸入很大而且已在其聯接列上創建了索引， 則索引 Nested Loops 連接是最快的聯接操作，因為它們需要的 I/O 和比較都最少。

嵌套循環聯接也稱為“嵌套迭代”，它將一個聯接輸入用作外部輸入表(顯示為圖形執行計劃中的頂端輸入)，將另一個聯接輸入用作內部(底端)輸入表。外部循環逐行處理外部輸入表。內部循環會針對每個外部行執行，在內部輸入表中搜索匹配行。可以用下面的偽碼來理解：

foreach(row r1 in outer table)

foreach(row r2 in inner table)

if( r1, r2 符合匹配條件 )

output(r1, r2);

最簡單的情況是，搜索時掃描整個表或索引;這稱為“單純嵌套循環聯接”。如果搜索時使用索引，則稱為“索引嵌套循環聯接”。如果將索引生成為查詢計劃的一部分(並在查詢完成後立即將索引破壞)，則稱為“臨時索引嵌套循環聯接”。查詢優化器考慮了所有這些不同情況。

如果外部輸入較小而內部輸入較大且預先創建了索引，則嵌套循環聯接尤其有效。在許多小事務中(如那些只影響較小的一組行的事務)，索引嵌套循環聯接優於合並聯接和哈希聯接。但在大型查詢中，嵌套循環聯接通常不是最佳選擇。

2. 【Merge Join】，如果兩個聯接輸入並不小但已在二者聯接列上排序(例如，如果它們是通過掃描已排序的索引獲得的)，則合並聯接是最快的聯接操作。如果兩個聯接輸入都很大，而且這兩個輸入的大小差不多，則預先排序的合並聯接提供的性能與哈希聯接相近。但是，如果這兩個輸入的大小相差很大，則哈希聯接操作通常快得多。

合並聯接要求兩個輸入都在合並列上排序，而合並列由聯接謂詞的等效 (ON) 子句定義。通常，查詢優化器掃描索引(如果在適當的一組列上存在索引)，或在合並聯接的下面放一個排序運算符。在極少數情況下，雖然可能有多個等效子句，但只用其中一些可用的等效子句獲得合並列。

由於每個輸入都已排序，因此 Merge Join 運算符將從每個輸入獲取一行並將其進行比較。例如，對於內聯接操作，如果行相等則返回。如果行不相等，則廢棄值較小的行並從該輸入獲得另一行。這一過程將重復進行，直到處理完所有的行為止。

合並聯接操作可以是常規操作，也可以是多對多操作。多對多合並聯接使用臨時表存儲行(會影響效率)。如果每個輸入中有重復值，則在處理其中一個輸入中的每個重復項時，另一個輸入必須重繞到重復項的開始位置。 可以創建唯一索引告訴SQL Server不會有重復值。

如果存在駐留謂詞，則所有滿足合並謂詞的行都將對該駐留謂詞取值，而只返回那些滿足該駐留謂詞的行。

合並聯接本身的速度很快，但如果需要排序操作，選擇合並聯接就會非常費時。然而，如果數據量很大且能夠從現有 B 樹索引中獲得預排序的所需數據，則合並聯接通常是最快的可用聯接算法。

3. 【Hash Join】，哈希聯接可以有效處理未排序的大型非索引輸入。它們對復雜查詢的中間結果很有用，因為： 1. 中間結果未經索引(除非已經顯式保存到磁盤上然後創建索引)，而且通常不為查詢計劃中的下一個操作進行適當的排序。 2. 查詢優化器只估計中間結果的大小。由於對於復雜查詢，估計可能有很大的誤差，因此如果中間結果比預期的大得多，則處理中間結果的算法不僅必須有效而且必須適度弱化。

哈希聯接可以減少使用非規范化。非規范化一般通過減少聯接操作獲得更好的性能，盡管這樣做有冗余之險(如不一致的更新)。哈希聯接則減少使用非規范化的需要。哈希聯接使垂直分區(用單獨的文件或索引代表單個表中的幾組列)得以成為物理數據庫設計的可行選項。

哈希聯接有兩種輸入：生成輸入和探測輸入。查詢優化器指派這些角色，使兩個輸入中較小的那個作為生成輸入。

哈希聯接用於多種設置匹配操作：內部聯接;左外部聯接、右外部聯接和完全外部聯接;左半聯接和右半聯接;交集;聯合和差異。此外，哈希聯接的某種變形可以進行重復刪除和分組，例如 SUM(salary) GROUP BY department。這些修改對生成和探測角色只使用一個輸入。

哈希聯接又分為3個類型：內存中的哈希聯接、