SQL Server Join方式

標籤：等於   空間   into   比較   first   假設   記憶體   inner   是的   

1.測試資料準備

參考：Sql Server中的表訪問方式Table Scan, Index Scan, Index Seek 這篇部落格中的實驗資料準備。這兩篇部落格使用了相同的實驗資料。

2.SQL Server中的三種Join方式

在Sql Server中，每一個join命令，在內部執行時，都會採用三種更具體的join方式來運行。這三種join的方法是：nested loops join、merge join和hash join。這三種方法，沒有哪一種是永遠最好的，但是都有其最適合的上下文。SQL Server會根據兩個結果集所基於的表格結構，以及結果集的大小，選擇最合適的聯結方法。當然，使用者也可以在語句裡指定join的方法，也就是添加join hint，SQL Server會儘力尊重你的選擇。但是，有些查詢按照指定的join方法可能做不出執行計畫，SQL Server會報錯。而且建議不要使用sql hint，因為SqlServer的選擇基本上都是正確的

sql server有三種join方式，那麼就有三種join hint，如下所示就是按照三種join hint執行的連接以及其所對應的執行計畫，

View Code

執行計畫：

2.1Nested Loop Join

Nested Loops是一種最基本的聯結方法，被SQL Server廣泛使用。對於兩張要被join在一起的表格，SQL Server選擇一張做Outer table(在執行計畫的上端，SalesOrderHeader_test)，另外一張做Inner table(在執行計畫的下端，SalesOrderDetail_test)。如所示

其演算法是：

foreach(row r1 in outer table) --盡量小    foreach(row r2 in inner table)        if( r1, r2 符合匹配條件 )            output(r1, r2);

 以上面的查詢為例子，SQL Server選擇了SalesOrderHeader_test作為Outer table，SalesOrderDetail_test作為Inner table。首先SQL Server在SalesOrderHeader_test上做了一個clustered index seek，找出每一條a.SalesOrderID >43 659 and a.SalesOrderID< 53 660的記錄。每找到一條記錄，SQL Server都進入Inner table，找能夠和它join返回資料的記錄（a.SalesOrderID = b.SalesOrderID）。由於Outer Table SalesOrderHeader_test上有10 000條SalesOrderID在43 659和53 660的記錄，每一條SQL Server都要到inner table裡去找能join的row，所以inner table SalesOrderDetail_test被掃描了10 000次，在執行計畫中的體現就是：Clustered index seek返回的row有10000，而executes的次數是1。而Index Seek被執行的次數executes為10000，這是因為inner table被掃描了10000次。外表的rows決定了內表的executes。

Nested Loops Join是一種基本的聯結方式。它不需要SQL Server為join建立另外的資料結構，所以也比較省記憶體空間，也無須使用tempdb的空間。它適用的Join類型是非常廣泛的。有些聯結是merge和hash做不了的，但Nexted Loops可以做。所以這種聯結方式的優點是很明顯的，但是它的缺點也很明顯。

1. 演算法的複雜度等於Inner table乘以Outer table。

如果是兩張表比較大，尤其是Outer table比較大的情況，Inner table會被掃描很多次。這時候的演算法複雜度增加得非常快，總的資源消耗量也會增加得很快。所以Nested Loops Join比較適合於兩個比較小的結果集做聯結，或者至少是Outer table的結果集比較小。

像前面的那個例子，由於Outer table SalesOrderHeader_test的資料集有10 000條記錄，所以Inner table就會被掃描10 000次。這是不太划算的。如果讓SQL Server自己選擇而不加join hint，SQL Server不會選擇nested loops的聯結方式。

2. Outer table的資料集最好能夠事先排序好，以便提高檢索效率。

如果資料集能夠事先排序好，做Nested loops當然能夠更快一些。當然如果沒有排序，Nested Loops Join也能做得出來，就是cost會大大增加。

3. Inner table上最好有一個索引，能夠支援檢索。

nested loop演算法會逐一拿著Outer table裡的每一個值，在Inner table裡找所有合格記錄，所以在Inner table裡找得快慢也能很大程度上影響整體的速度。如果進行檢索的欄位上有一個索引，尋找的速度會大大加快，Inner table資料集稍微大一點也沒關係。否則就要每次做整個資料集的掃描，是很浪費資源的。

總之，Nested Loops Join對資料集比較小的聯結，效率是最高的，因此在SQL Server裡使用得很廣泛。當SQL Server發現能夠選擇一個很小的資料集作為Outer table的時候，它往往會選擇Nested Loops，效能也比較好。但是Nested Loops Join對資料集大小的敏感性太強。如果SQL Server預測發生錯誤，用大的資料集做Outer table，效能會急劇下降。很多語句效能問題，都是由於這個造成的。

2.2Merge join

在前面提到過，Nested Loops Join只適用於Outer table資料集比較小的情況。如果資料集比較大，SQL Server會使用其他兩種聯結方式，Merge Join和Hash Join。如果需要串連的兩張表已經聯結列上排序（例如，如果它們是通過掃描已排序的索引獲得的），則Merge Join是最快的聯結操作。如果兩個聯結輸入都很大，而且這兩個輸入的大小差不多，則預先排序的Merge Join提供的效能與Hash Join相近。但是，如果這兩個輸入的大小相差很大，則Hash Join操作通常快得多。

Merge Join演算法如下：

get first row R1 from input 1 get first row R2 from input 2 while not at the end of either input begin     if (R1 joins with R2)     begin     output (R1, R2)     get next row R2 from input 2 end else if (R1 < R2)    get next row R1 from input 1 else     get next row R2 from input 2 end 

也就是說，從兩邊的資料集裡各取一個值，比較一下。如果相等，就把這兩行聯結起來返回。如果不相等，那就把小的那個值丟掉，按順序取下一個更大的。兩邊的資料集有一邊遍曆結束，整個Join的過程就結束。所以整個演算法的複雜度是O(M+N)，這個比起Nested Loops Join兩個資料集相乘的複雜度O(M*N)，的確是小了很多。所以在資料集大的情況下，Merge Join的優勢是非常明顯的。

但是從上面的Merge Join演算法看出，它的局限性也很強，所以在實際的語句裡，使用得並不是那麼的普遍。它的局限性主要有：

1. 做聯結的兩個資料集必須要事先按照Join的欄位排好序。

這個先決條件是Merge Join演算法的基礎，而對大的資料集排序本來就是一件比較複雜的事情。不過有些資料集是基於Join的那個欄位上的索引得到的，所以能夠不費額外的資源就排好了順序，這時候使用Merge Join可能就比較合適。例如範例查詢，兩個資料集都是根據在SalesOrderID欄位的索引上seek出來的，所以不需要再做排序。範例查詢的執行計畫如下所示：

從查詢計劃中我們可以看到merge join的範例查詢可以分解成兩個查詢，

select * from dbo.SalesOrderHeader_test where SalesOrderID >43659 and SalesOrderID< 53660select count(SalesOrderID) from dbo.SalesOrderDetail_test where SalesOrderID >43659 and SalesOrderID< 53660

第一個查詢使用clustered index seek，因為有叢集索引，所以查詢結果肯定按照叢集索引列SalesOrderID排序。第二個查詢雖然SalesOrderID不是SalesOrderDetail_test表的叢集索引鍵，但是因為在SalesOrderDetail_test表上有非叢集索引，而且只需要查詢count(SalesOrderID)，所以之在非叢集索引上面查詢，查詢結果也是按照SalesOrderID排序。從而最終兩個結果集都是按照SalesOrderID排序的。

2. Merge Join只能做以“值相等”為條件的聯結，而且如果資料集可能有重複的資料，Merge Join要採用Many-To-Many這種很費資源的聯結方式。

在SQL Server掃描資料集時，如果資料集1有兩個或者多個記錄值相等，SQL Server必須得把資料集2裡掃描過的資料暫時建立一個資料結構存放起來，萬一資料集1裡下一個記錄還是這個值，那還有用。這個臨時資料結構被稱為“Worktable”，會被放在tempdb或者記憶體裡。這樣做很耗資源，所以在上面的執行計畫裡，Merge Join的兩句子句的Subtree Cost分別為0.202和0.109。但Many-To-Many的Join子句Subtree Cost是5.051。也就是說，Join自己的cost是4.74（5.051 – 0.202 – 0.109 =4.74)）。這是一個不小的cost。

如果在[SalesOrderHeader_test]表的SalesOrderID列上再添加一個Unique的索引（或者將原來的叢集索引改成唯一叢集索引），

--SalesOrderID列上原本有了叢集索引，現在再添加一個唯一索引--如果SalesOrderID列上有重複之，添加唯一索引會失敗。create unique index idx_uniq_SalesOrderID on SalesOrderHeader_test(SalesOrderID);

SQL Server就知道資料集1（SalesOrderHeader_test）的值不會重複的，也就不需要做Many-To-Many Join。執行計畫果然發生變化，預估的cost降低了一個數量級。

總結:

上面這兩個限制，影響了Merge Join的使用範圍。但是Merge Join的一個獨特好處是，返回的資料集也是按照順序排好的。這裡順便提一下結果集的順序問題。我們在使用同一個查詢的時候，會發現結果集有時候是按我們想要的順序排列，有時候又不是。或者是在SQL Server 2000裡是這個順序，到了SQL Server 2005/2008又是另外順序。在講完了Merge Join以後，我們就能夠明白，同樣做Join操作，Merge Join就能夠按順序返回，但是Nested Loops就不能。只要語句裡沒有指定“Order By”，SQL Server選取哪一種Join並不需要考慮結果集是否是按順序返回的。它更多考慮的是哪一種Join演算法代價最小。如果資料量和資料分布讓SQL Server覺得Nested Loops划算，它就轉用Nested Loops。結果集就不按順序返回了，但是SQL Server並沒有做錯什麼。一句話，如果想要結果集按照某個順序返回，就要明確地用“order by”指定。如果沒有指定，哪怕一模一樣的查詢，結果集順序這一次和上一次不一樣是很正常的。因為資料發生變化，或者參數不同，SQL Server很可能就會選擇不同的執行計畫。

2.3Hash Join

顧名思義，Hash Join就是利用雜湊演算法作匹配的聯結演算法。具體的雜湊演算法可以參考我的另外一篇部落格：Hashmap實現原理。簡單來說，雜湊演算法分成兩步，“構建雜湊桶(Build hash bucket)”和“探測雜湊桶中的值(Probe hash bucket)”。在“Build”階段，SQL Server選擇兩個要做Join的資料集中的一個，根據記錄的值建立起一張在記憶體中的Hash表。然後在“Probe”階段，SQL Server選擇另外一個資料集，將裡面的記錄值依次帶入，返回符合條件可以做聯結的行。具體的演算法是：

for each row R1 in the build table   begin      calculate hash value on join key(s) of R1      insert R1 into the appropriate hash bucket   endfor each row R2 in the probe table   begin      calculate hash value on join key(s) of R2      for each row R1 in the corresponding hash bucket         if R1 joins with R2            output (R1, R2)   end

演算法描述：

1. 選擇兩個需要join的表中的一個a，對於a中的每一個記錄R1，計算其聯結列的hash值，然後根據hash值將R1插入到hash bucket當中。
2. 選擇兩外一張表b，對於b中的每一條記錄R2，我們也計算其聯結列的hash值，然後去hash bucket上尋找。如果hash bucket上有R1能夠跟R2進行串連，那麼久輸出(R1,R2)的聯結結果，可能有多個R1的記錄。

其結構可以參考所示：

上面的0-15就是hash bucket，而右邊的那些節點就是R1。

和其他兩種Join演算法比，Hash Join的優點是很明顯的。

1. 它的演算法複雜度就是分別遍曆兩邊的資料集各一遍。

這對於資料集比較大的Join，其複雜度能夠控制在合理的範圍以內。相對於已經排好序的Merge Join，Hash Join多了一步計算Hash值，因此複雜度要比Merge Join要高一些，但是比Nested Loops要簡單許多。

2. 它不需要資料集事先按照什麼順序排序，也不要求上面有索引。

因為聯結使用的是雜湊演算法，對輸入沒有限制，不需要SQL Server像為Merge Join一樣，事先準備好一個排過序的輸入。由於做Hash Join總是要把兩邊的資料集都要掃描一遍，所以有沒有索引其實協助也不大。沒有索引，對效能也不會有太大的影響。

3. 可以比較容易地升級成使用多處理器的並存執行計劃。

因為演算法沒有要求代入的資料有任何次序，所以用多個CPU並行完成是比較容易的。

總之，Hash Join是一種適合於要Join的資料集比較大，上面沒有合適的索引的情況。像剛才的那個例子，是一個10 000條記錄的資料集和一個50 577條記錄的資料集之間的聯結。使用Nested Loops要迴圈10 000次，代價比較高。SQL Server預估出來的cost是2.233。使用Merge Join時，雖然兩個資料集都是排序好的，但是由於可能有重複的值，SQL Server只好使用Many-To-Many的join方式，cost也很高，預估是5.882。使用Hash Join，預估的cost是0.727，比前兩個都小。所以如果不代入Join Hint的話，SQL Server預設會對這句話使用Hash Join。

但是，Hash Join並不是一種最優的Join演算法，只是SQL Server在輸入不最佳化（Join的資料集比較大，或上面沒有合適的索引）的時候的一種不得已選擇。這是因為Hash Join是一種最耗資源的Join演算法。它在做Join之前，要先在記憶體裡建立一張Hash表。建立的過程需要CPU資源，Hash表需要用記憶體或tempdb存放。而Join的過程也要使用CPU資源來計算（“Probe”）。如果同時有很多使用者在用Hash演算法做Join，對SQL Server的整體負擔是比較重的。從降低SQL Server整體負荷的角度考慮，還是要盡量降低Join輸入的資料集的大小，配以合適的索引，引導SQL Server盡量使用Nested Loops Join或者Merge Join。

下面用表對這三種Join方式作一下比較。

	Nested Loops Join	Merge Join	Hash Join
最適合於	相對較小的兩個資料集，inner table在做Join的欄位上有一個索引	輸入資料集大小中等或較大，且在Join欄位上有索引協助排序，或者語句要求返回一個排好序的結果集	輸入資料集較大。尤其適合於Data warehouse 環境下的那些複雜的查詢語句
並發性	能夠支援大量的並發使用者同時運行	有索引支援的Many-to-one的join並發性較好，Many-To-Many的就差了	最好同時只有少數使用者在同時運行
Join時要否兩個欄位相等	不要	要(除非是full outer join)	要
要否使用記憶體資源	不使用	不使用(如果要為Merge Join做排序，可能要使用)	使用
要否使用tempdb	不使用	many-to-many join要使用	使用
輸入資料集要否排序	不要	要	不要
希望輸入資料集排序否	希望outer input是排序的	是的	不要

在SQL Server做聯結的時候，會按照輸入資料集所基於的表格的結構，衡量可能利用的索引，也根據統計資訊，預估兩個輸入資料集的大小，選擇使用三種Join方式其中的一種。如果選得不對，可能就會造成Join的速度非常慢。

實際應用

這是一道面試題，可以參考我之前寫過的一篇部落格：查詢在一張表不在另外一張表的記錄。現在發現之前寫的還是不夠清晰透徹，我會修改那篇部落格，具體的解法也在寫在那篇部落格中。

題目的大致意思是：

假如要查詢在a表中存在，但是在b表中不存在的記錄，應該如何查詢。為了便於說明，我們假設a表和b表都只有一個欄位id，a表中的記錄為{1,2,3,4,5}，b表中的記錄為{2,4}，那麼我們需要通過一個sql查詢得到{1,3,5}這樣的結果集。還有就是a和b表中id不一定是排序的，a表的資料集大，b表的資料集小。

 

來源：https://www.cnblogs.com/xwdreamer/archive/2012/09/07/2674756.html

SQL Server Join方式