SQL Server 效能調優 之執行計畫（Execution Plan）調優

標籤：技術   jsb   sele   bar   查詢   CM   語句   IV   div   

SQL Server 存在三種 Join 策略：Hash Join，Merge Join，Nested Loop Join。

Hash Join：用來處理沒有排過序/沒有索引的資料，它在記憶體中把 Join 兩邊資料（的關聯key）分別建立一個雜湊表。例如有以下的查詢語句，關聯的兩張表沒有建立索引，執行計畫將顯示為Hash Join。

[sql]
 

1. SELECT  
2.   sh.*  
3. FROM  
4.   SalesOrdHeaderDemo AS sh  
5. JOIN  
6.   SalesOrdDetailDemo AS sd  
7. ON  
8.   sh.SalesOrderID=sd.SalesOrderID  
9. GO  

 

Merge Join：用來處理有索引的資料，它比Hash Join輕量化。我們為前面兩張表的關聯列建立索引，然後再次上面的查詢，執行計畫將變更為Merge Join

[sql]

1. CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_SalesOrderID ON SalesOrdHeaderDemo (SalesOrderID)  
2. GO  
3. CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderlID ON SalesOrdDetailDemo (SalesOrderID,SalesOrderDetailID)  
4. GO  

Nested Loop Join：在滿足Merge Join的基礎上，如果某一邊的資料較少，那麼SQL Server 會把資料較少的那個作為外部迴圈，另一個作為內部迴圈來完成Join處理。繼續前面的例子為查詢語句加上WHERE語句來減少 Join 一邊的資料量，執行計畫顯示為Nested Loop Join。

 

[sql] 

1. SELECT  
2.   sh.*  
3. FROM  
4.   SalesOrdHeaderDemo AS sh  
5. JOIN  
6.   SalesOrdDetailDemo AS sd  
7. ON  
8.   sh.SalesOrderID=sd.SalesOrderID  
9. WHERE  
10.   sh.SalesOrderID=43659  

 

執行計畫中的（table/index scan）的改進

在許多場合我們需要在一張包含許多資料的表中提取出一小部分資料，此時應當避免Scan，因為掃描處理會遍曆每一行，這是相當耗時耗力的。下面我們來看一個例子：

[sql]

1. SELECT  
2.   sh.SalesOrderID  
3. FROM  
4.   SalesOrdHeaderDemo AS sh  
5. JOIN  
6.   SalesOrdDetailDemo AS sd  
7. ON  
8.   sh.SalesOrderID=sd.SalesOrderID  
9. WHERE  
10.   sh.OrderDate=‘2005-07-01 00:00:00.000‘  
11. GO  

 

圖中的紅圈標出了table scan，並且執行計畫也智能得建議建立索引。我們先嘗試在SalesOrdHeader 表上建立一個索引：

 

[sql] 

1. CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_SalesOrderID ON SalesOrdHeaderDemo (SalesOrderID)  
2. GO  

然後再次執行相同的查詢語句，執行計畫變成以下的模樣：

 

table scan 變為了 Index Scan，繼續給另一張表也加上索引：

 

[sql]

1. CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderlID ON SalesOrdDetailDemo (SalesOrderID,SalesOrderDetailID)  
2. GO  

執行計畫發生以下的變化：

 

雖然不能說 Scan 比 Seek 差，但絕大多數的場合（尤其是在許多資料中尋找少量資料時）Seek 是更好的選擇。舉例來說如果你有一個上億條資料的表，你要取其中的100條，那麼你應當保證其採用 Seek，但如果你需要取出其中絕大多數（比如95%）的資料時，Scan 可能更好。（有較權威的文章給出了這個閥值為30%，即取出超過30%資料時 scan 更高效；反之則 Seek 更好）

另外你可能注意到兩張表上都建立了索引但一張表在執行計畫中表現為 Clustered index scan，而另一張表現為 Clustered index seek，我們期待的不是兩個 Clustered index seek 嗎？這是因為前一張表沒有斷言（predicate），而後一張表通過 ON 關鍵字對SalesOrderID 進行了斷言限制。

執行計畫中的 Key Lookup

為了後續的樣本，我們先在同一張表上建立兩個不同的索引：

[sql]

1. CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderlID ON SalesOrdDetailDemo (SalesOrderID,SalesOrderDetailID)  
2. GO  
3. CREATE NONCLUSTERED INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo(ModifiedDate)  
4. GO  

執行以下的查詢：

[sql]

1. SELECT  
2.   ModifiedDate  
3. FROM SalesOrdDetailDemo  
4.   WHERE ModifiedDate=‘2005-07-01 00:00:00.000‘  
5. GO  

執行計畫如，他利用了我們先前建立在 ModifiedDate 欄位上的 Non-Clustered Index，產生為一個Index Seek 處理。

我們改造一下查詢語句，SELECT 中多加兩個欄位：

[sql] 

1. SELECT  
2.   ModifiedDate,  
3.   SalesOrderID,  
4.   SalesOrderDetailID  
5. FROM SalesOrdDetailDemo  
6. WHERE ModifiedDate=‘2005-07-01 00:00:00.000‘  
7. GO  

執行計畫如，基本沒變：

上面選出的欄位不是屬於 Non-Clustered Index 就是屬於 Clustered Index，如果再增加幾個其他的欄位呢？

 

[sql] 

1. SELECT  
2.   ModifiedDate,  
3.   SalesOrderID,  
4.   SalesOrderDetailID,  
5.   ProductID,  
6.   UnitPrice  
7. FROM SalesOrdDetailDemo  
8. WHERE ModifiedDate=‘2005-07-01 00:00:00.000‘  
9. GO  

乖乖，執行計畫一下多了兩個處理（Key Lookup, Nested Loop）：

Key Lookup 是一個繁重的處理，我們可以使用關鍵字 WITH 來指定使用 Clustered Index，以此迴避Key Lookup。

 

[sql] 

1. SELECT  
2.   ModifiedDate,  
3.   SalesOrderID,  
4.   SalesOrderDetailID,  
5.   ProductID,  
6.   UnitPrice  
7. FROM SalesOrdDetailDemo WITH(INDEX=idx_SalesDetail_SalesOrderlID)  
8. WHERE ModifiedDate=‘2005-07-01 00:00:00.000‘  
9. GO  

執行計畫應聲而變成為一個 Clustered Index Scan：

前文提過 Scan 似乎也不是一個很好的處理，那麼矮子裡拔高個，使用 SET STATISTICS IO ON 來比較一下：

[sql] 

1. SET STATISTICS IO ON  
2. GO  
3.   
4. SELECT  
5.   ModifiedDate,  
6.   SalesOrderID,  
7.   SalesOrderDetailID,  
8.   ProductID,  
9.   UnitPrice  
10. FROM SalesOrdDetailDemo  
11. WHERE ModifiedDate=‘2005-07-01 00:00:00.000‘  
12. GO  
13.   
14. SELECT  
15.   ModifiedDate,  
16.   SalesOrderID,  
17.   SalesOrderDetailID,  
18.   ProductID,  
19.   UnitPrice  
20. FROM SalesOrdDetailDemo WITH(INDEX=idx_SalesDetail_SalesOrderlID)  
21. WHERE ModifiedDate=‘2005-07-01 00:00:00.000‘  
22. GO  
23.   
24. SELECT  
25.   ModifiedDate,  
26.   SalesOrderID,  
27.   SalesOrderDetailID,  
28.   ProductID,  
29.   UnitPrice  
30. FROM SalesOrdDetailDemo WITH(INDEX=idx_non_clust_SalesOrdDetailDemo_ModifiedDate)  
31. WHERE ModifiedDate=‘2005-07-01 00:00:00.000‘  
32. GO  

比較下來，採用了 clustered index 的查詢表現最差，另外 SET STATISTICS IO 輸出的資料中clustered index 的查詢在 logical reads 上花費了更多的時間。

 

看起來採用 non-clustered index + Key Lookup 執行計畫表現還不錯，但如果能迴避 Key Lookup 就完美了，我們來把 non-clustered index 修改一下，用 INCLUDE 關鍵字在索引中包含其他的欄位：

 

[sql]

1. DROP INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo  
2. GO  
3. CREATE NONCLUSTERED INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo(ModifiedDate)  
4. INCLUDE  
5. (  
6.   ProductID,  
7.   UnitPrice  
8. )  
9. GO  
10.   
11. -- 清下緩衝，僅用於開發環境！  
12. DBCC FREEPROCCACHE  
13. DBCC DROPCLEANBUFFERS  
14. GO  

再次執行之前的查詢：

[sql] 

1. SELECT  
2.   ModifiedDate,  
3.   SalesOrderID,  
4.   SalesOrderDetailID,  
5.   ProductID,  
6.   UnitPrice  
7. FROM SalesOrdDetailDemo  
8. WHERE ModifiedDate=‘2005-07-01 00:00:00.000‘  
9. GO  

 

這下完美了，因為我們的查詢欄位都包含在索引中，所以執行計畫最終被最佳化為 Index Seek。

SQL Server 效能調優 之執行計畫（Execution Plan）調優