SQL Server語句最佳化詳解

核心提示：關於執行計畫的說
明 在SQL Server查詢分析器的Query菜單中選擇Show Execution
Plan，運行SQL查詢語句，在結果視窗中有Grid、Execution
Plan、Messages三個Tab。看圖形形式的執行計畫，順序是從右至左，這也是執行的順序。執行計畫中的每一個表徵圖表示一個操作

關於執行計畫的說明

在SQL Server查詢分析器的Query菜單中選擇Show Execution
Plan，運行SQL查詢語句，在結果視窗中有Grid、Execution
Plan、Messages三個Tab。看圖形形式的執行計畫，順序是從右至左，這也是執行的順序。執行計畫中的每一個表徵圖表示一個操作，每一個操作都會
有一個或多個輸入，也會有一個或多個輸出。輸入和輸出，有可能是一個物理資料表、索引資料結構，或者是執行過程中的一些中間結果集/資料結構。鼠
標移動到表徵圖上，會顯示這個操作的具體資訊，例如邏輯和物理操作名稱、記錄的數量和大小、I/O成本、CPU成本、操作的具體運算式（參數
Argument）。滑鼠移動到串連箭頭上，會顯示箭頭開始端點的操作輸出結果集的記錄數、記錄的大小，一般情況下可以將這個輸出結果集理解為箭頭結束端的
輸入
。

 

另外關於執行計畫的一些補充說明：1.
執行計畫中顯示的資訊，都是一個“評估”的結果，不是100%準確的資訊，例如記錄數量是取自統計資訊，I/O成本、CPU成本來自執行計畫產生過程中基
於統計資訊等得出的評估結果。2. 執行計畫不一定準確，一方面受SQL
Server維護的統計資訊準確性的影響，另一方面SQL語句編譯時間刻與執行時刻的環境（記憶體使用量狀況、CPU狀況等）可能會不一樣。

關於統計資訊、I/O成本和CPU成本的評估、SQL語句的編譯和執行過程，這裡不再深入。另外儘管執行計畫不一定準確，但它仍是SQL語句分析最重要的依據，因為你可以理解為，絕大部分情況下，SQL Server是以這種方式來執行的。

JOIN方法說明

資料庫中，象tableA inner join tableB、tableA left out join tableB這樣的SQL語句是如何執行join操作的？就是說SQL Server使用什麼演算法實現兩個表資料的join操作？

SQL Server 2000有三種方式：nested loop、merge、hash。Oracle也是使用這三種方式。

1. nested loop join

1.1 樣本SQL

select ... from tableA inner join tableB on tableA.col1=tableB.col1 where tableA.col2=? and tableB.col2=?

tableA中沒有建立任何索引，tableB中在col1上有建立一個主鍵（叢集索引）。

 

1.2 演算法虛擬碼描述

foreach rowA in tableA where tableA.col2=?
{
search rowsB from tableB where tableB.col1=rowA.col1 and tableB.col2=? ;
if(rowsB.Count<=0)
discard rowA ;
else
output rowA and rowsB ;
}


join操作有兩個輸入，上面例子中tableA是outer input，用於外層迴圈；tableB是inner input，用於迴圈內部。下面針對執行計畫描述一下SQL Server完成這個操作的具體步驟。

1.3 查看執行計畫方法　　移到文章最前面。

1.4 執行步驟

下面是樣本SQL的執行計畫圖。nested loop操作的右邊，位於上面的是outer input，位於下面的是inner
input。你不能夠根據join中哪個表出現在前面來確定outer input和inner
input關係，而必須從執行計畫中來確定，因為SQL Server會自動選擇哪個作為inner input。

a) 對tableA執行Table Scan操作。這個操作的輸入是tableA表中的資料，這些資料位元於磁碟上，操作過程中被載入到記憶體；輸出是合格記錄集，將作為b)的outer input。在這個操作中，tableA.col1=?的條件會被使用。

b) 執行上面虛擬碼描述的nested loop操作。對a)中的每個輸出記錄，執行步驟c)。

c) 對tableB執行Clustered Index Seek操作。這個操作是在nested
loop迴圈裡面執行的，輸入是tableB表的叢集索引資料。它使用tableB.col1=rowA.col1和tableB.col2=?這兩個條
件，從tableB的叢集索引中選擇合格結果。

d) 構造返回結果集。從nested loop的輸出中，整理出select中指定的欄位，構造最終輸出結果集。

1.5 進階說明

上面例子對inner input使用的是叢集索引，下面看一下非叢集索引的情況，加強對執行計畫的理解、分析能力。

把tableB col1上的主鍵修改為非聚集方式，樣本的SQL語句執行計畫如下：

 

前面三個執行步驟a)、b)、c)跟1.4中一樣，有一點需要注意的是，步驟c)是執行Index Seek操作，它跟Clustered
Index
Seek有區別。叢集索引的根節點是每一條實際資料記錄，而非叢集索引的根節點是對叢集索引根結點索引值的引用（如果表存在叢集索引），或者是對實際資料記
錄rowid的引用（指沒有叢集索引的表，這種表稱為heap表）。Clustered Index
Seek執行之後，實際的物理資料記錄已經被載入到記憶體中，而Index
Seek操作之後，並沒有載入實際的物理資料記錄，而只是非叢集索引的根結點資料，其中只包含了索引欄位資料以及引用的叢集索引索引值或者rowid。
SQL Server在這個步驟中使用非叢集索引根結點資料中的索引欄位值，與outer
input中的記錄（rowA）關聯欄位進行匹配，判斷是否是合格結果，如果是，則將非叢集索引根結點資料結構儲存到nested
loop操作的輸出資料結構中，並且會建立一個書籤（Bookmark），指示在必要的時候需要根據這個書籤去擷取引用的資料。

d) 執行Bookmark Lookup操作。nested loop操作的輸出是一個記憶體資料結構，在從這個記憶體資料結構中整理出整個查詢語句的輸出結果集之前，需要處理前面的書籤引用問題，Bookmark Lookup操作就是根據書籤中引用的叢集索引索引值或者rowid擷取具體記錄資料。

 

e) Filter過濾操作。回顧前面幾個操作，在執行nested
loop時只是使用非叢集索引的索引欄位（tableB.col1）跟outer
input的關聯欄位進行匹配，到目前為止還沒有使用tableB.col2=?這個條件，這個操作就是使用tableB.col2=?對
Bookmark Lookup的輸出進行過濾。

看的仔細的人到這裡後可能會有幾個疑問，1. tableA.col2=?怎麼沒有一個Filter操作？2.
在1.4中為什麼沒有出現Filter操作？解釋如下：1. 在tableA上面執行的是Table
Scan操作，是直接對每條實際資料進行掃描，在這個掃描過程中可以使用tableA.col2=?這個條件進行過濾，避免一個額外的Filter操作。
滑鼠移動到Table Scan操作上，從提示資訊的參數（Argument）裡面可以看到tableA.col2=?的條件已經被運用上了。2.
前面說過，叢集索引的根節點是實際資料記錄，執行Clustered Index
Seek的時候，最終也是掃描到了實際資料記錄，在這個過程中運用tableB.col2=?這個條件，同樣避免一個額外的Filter操作。這就是
1.4中沒有Filter操作的原因。

 

f) 構造返回結果集。跟1.4步驟d)一樣。

1.6 nested loop使用條件

任何一個join操作，如果滿足nested loop使用條件，查詢最佳化過程中SQL Server就會對nested loop的成本（I/O成本、CPU成本等）進行評估，基於評估結果確定是否使用這種join方式。

使用nested loop方式的條件是：a) outer input的記錄數不大，最好是在1000-2000以下，一般超過3000就很難說了，基本不大會選擇nested loop。b) 作為inner input的表中，有可用於這個查詢的索引。

這是因為outer input記錄數不大，意味著外層迴圈次數比較小；inner input上有可用的索引，意味著在迴圈裡面搜尋inner
input表中是否存在匹配的記錄時，效率會很高，哪怕inner input表實際記錄數有幾百萬。基於這兩個條件，nested
loop的執行效率非常高，在三種join方式裡面，是記憶體和CPU消耗最少的一種（不合理的強制指定nested loop方式除外）。

關於使用條件另外的說明：outer input的記錄數，並不是指outer
input表中實際記錄數，例如樣本SQL中，如果tableA在col2上有維護統計資訊（存在col2的索引或者是單獨維護的統計資訊），並且
tableA.col2=?的條件值符合SARG（可搜尋參數）形式，那麼查詢編譯時間刻SQL
Server就能夠利用統計資訊和條件值評估出合格記錄數，查詢執行時刻符合條件tableA.col2=?的記錄才被用於外層迴圈。inner
input表中有可用的索引，是指inner input表中用於和outer
input表關聯的欄位（一個或多個欄位）能夠命中某個索引（這些欄位的部分或者全部出現在某個索引欄位的前面）。

符合上面的條件，也不是說SQL Server 100%就會選擇nested loop。因為SQL
Server的查詢最佳化工具是基於成本評估的，如果其它方案評估出的成本勝過這個，SQL
Server會選擇其它的join方式。舉個例子，如果inner input上合格索引是非叢集索引，這樣SQL
Server可能需要一個額外的Bookmark Lookup操作擷取實際記錄資料，如果inner
input表資料量非常大，索引片段程度很高等情況，可能導致Bookmark Lookup成本非常高，SQL
Server會嘗試其它join方案的評估選擇。

 

1.7 強制指定nested loop方式

使用loop關鍵字實現，例如tableA inner loop join tableB，將強制SQL Server使用nested loop方式執行這個join操作。或者使用option選項，例如tableA inner join tableB option(loop join)

 

nested loop演算法有它適用的範圍，在這個範圍之內效率是最高的，超出這個範圍效率反而很差，除非你有十分的把握，不要隨意強制指定join方式。

接下來就不再象上面這樣詳細的講述了。

2. merge join

merge
join第一個步驟是確保兩個關聯表都是按照關聯的欄位進行排序。如果關聯欄位有可用的索引，並且排序一致，則可以直接進行merge
join操作；否則，SQL Server需要先對關聯的表按照關聯欄位進行一次排序（就是說在merge
join前的兩個輸入上，可能都需要執行一個Sort操作，再進行merge join）。

兩個表都按照關聯欄位排序好之後，merge join操作從每個表取一條記錄開始匹配，如果符合關聯條件，則放入結果集中；否則，將關聯欄位值較小的記錄拋棄，從這條記錄對應的表中取下一條記錄繼續進行匹配，直到整個迴圈結束。

在多對多的關聯表上執行merge join時，通常需要使用暫存資料表進行操作。例如A join B使用merge
join時，如果對於關聯欄位的某一組值，在A和B中都存在多條記錄A1、A2...An、B1、B2...Bn，則為A中每一條記錄A1、
A2...An，都必須在B中對所有相等的記錄B1、B2...Bn進行一次匹配。這樣，指標需要多次從B1移動到
Bn，每一次都需要讀取相應的B1...Bn記錄。將B1...Bn的記錄預先讀出來放入記憶體暫存資料表中，比從原資料頁或磁碟讀取要快。

merge join操作本身是非常快的，但是merge join前進行的排序可能會相當耗時（SQL
Server最消耗記憶體和CPU的操作，一個是大資料排序，一個是大資料的hash運算，這都是指查詢計劃裡面的Sort以及Hash相關的操作，例如
hash join、使用hash演算法實現的Distinct操作等，而不是指你的SQL中order
by關鍵字），尤其是對資料量非常大的記錄集，因此導致使用merge join的查詢成本變得非常高。對於資料量非常大的表，如果merge
join的關聯欄位可以使用叢集索引，merge
join是最快的Join方法之一。因此最佳化方案是在表結構設計層面良好的設計關聯關係和表的索引結構，SQL語句充分利用索引，儘可能減少merge
join前的排序操作，減少Bookmark Lookup操作。

 

一般情況下，如果無法滿足nested loop條件，會考慮對merge join方法的評估。merge
join的選擇，主要是考慮兩個輸入的資料量，以及分別對應於關聯欄位是否能夠命中索引。例如tableA join
tableB，關聯欄位在兩個表中都能命中索引，資料量超過了nested loop的選擇範圍，則會考慮使用merge
join方法。當然，如果tableA和tableB的資料量過大導致評估出來的成本過高，則會放棄merge join而評估hash join了。

使用inner merge join或者option(merge join)強制使用merge join方法。

3. hash join

關於hash演算法的細節，可以查看資料結構的一些資料。hash
演算法主要是用於大資料量的搜尋，為了避免每次都象merge join一樣在全部的資料中進行搜尋匹配，通過合適的
hash函數，先給要搜尋的資料根據hash
key建立hash值作為索引，在搜尋時，先通過hash值定位到一個較小的搜尋範圍，然後在這個範圍中搜尋匹配合格結果，以提高效率。

SQL Server將資料量較小的表作為build input，盡量使根據build input構造的hash
table能夠完全放在記憶體中，這樣probe階段的匹配操作就完全是在記憶體中進行，這樣的hash join叫做In-Memory Hash
Join。

如果build input記錄數非常大，構建的hash table無法在記憶體中容納時，SQL Server分別將build
input和probe input切分成多個分區部分（partition），每個partition都包括一個獨立的、成對匹配的build
input和probe input，這樣就將一個大的hash join切分成多個獨立、互相不影響的hash join，每一個分區的hash
join都能夠在記憶體中完成。SQL Server將切分後的partition檔案儲存在磁碟上，每次裝載一個分區的build
input和probe input到記憶體中，進行一次hash join。這種hash join叫做Grace Hash
Join，使用的Grace Hash Join演算法。

伴隨著大資料的hash join運算，還會有standard external merge sorts、multiple merge
levels、multiple partitioning steps、multiple partitioning levels，SQL
Server還可能會使用Recursive Hash Join等演算法或其它的最佳化手段。

 

hash
join一般都用於大資料量的操作，例如join中某個表的資料達到一定程度或者無法一次載入到記憶體，另外如果你的關聯欄位在兩個join表中都不能夠命
中索引，也是使用hash join來處理。因此一般情況下，hahs
join處理代價非常高，是資料庫伺服器記憶體和CPU的頭號殺手之一，尤其是涉及到分區（資料量太大導致記憶體不夠的情況，或者並發訪問很高導致當前處理線
程無法獲得足夠的記憶體，那麼資料量不是特大的情況下也可能需要進行分區），為了儘快的完成所有的分區步驟，將使用大量非同步I/O操作，因此期間單一一個
線程就可能導致多個磁碟機出於忙碌狀態，這很有可能阻塞其它線程的執行。

使用inner hash join或者option (hash join)強制使用hash join方法。

建議：

三種join方法，都是擁有兩個輸入。最佳化的基本原則：1. 避免大資料的hash
join，盡量將其轉化為高效的merge join、nested loop
join。可能使用的手段有表結構設計、索引調整設計、SQL最佳化，以及業務設計最佳化。例如冗餘欄位的運用，將統計分析結果用service定期跑到靜態
表中，適當的冗餘表，使用AOP或類似機制同步更新等。2.
盡量減少join兩個輸入端的資料量。這一點比較常犯的毛病是，條件不符合SARG（光這一點就有很多高超的技巧可以發揮），在子查詢內部條件給的不充分
（SQL過於複雜情況下SQL Server查詢最佳化工具經常犯傻，寫在子查詢外部的條件不會被用在子查
詢內部，影響子查詢內部的效率或者是跟子查詢再join時候的效率）。另外也是設計、業務端盡量限制這兩個輸入的資料量了。

 

補充：關於SQL Server 2005

 

執行計畫的顯示有一些不一樣，但主要部分或者說原理上是差不多的，不會有多少偏差。上面的樣本SQL，在tableB上面使用非叢集索引時，SQL Server 2005的執行計畫圖如下：

一個主要的不同點是SQL Server 2000下面Bookmark Lookup操作，在2005下面顯示成一個RID Lookup操作
+ 一個Nested
Loops操作實現，其實這也是很好理解的，可以說這樣顯示執行計畫更合理一點，讓你一看到這個操作，就知道它是通過一個迴圈機制到tableB中擷取實
際資料。

另外一點是，將滑鼠移動到執行計畫的表徵圖上面後，彈出的提示資訊的一些改變，例如2005裡面會顯示每個操作的輸出資料行表（output
list），而我上面的文章中基本都使用“輸出資料結構”這樣一個詞彙在表達。通過查看output list，你更能明白RID
Lookup（Bookmark Lookup）這樣的操作存在的理由了。

最後，2005裡面可以將圖形顯示的執行計畫儲存下來，以後可以開啟再以圖形方式進行查看分析，這個在2000下面是不行的，2000隻能儲存執行計畫的文本。這樣一些小功能對於分析SQL效能非常有用，在圖形介面上的分析更直觀。