join中級篇---------hash join & merge join & nested loop Join

標籤：

嵌套迴圈串連(Nested Loop Join)

迴圈嵌套串連是最基本的串連,正如其名所示那樣，需要進行迴圈嵌套，嵌套迴圈是三種方式中唯一支援不等式串連的方式，這種串連方式的過程可以簡單的用展示:

圖1.迴圈嵌套串連的第一步

圖2.迴圈嵌套串連的第二步

由上面兩個圖不難看出，迴圈嵌套串連尋找內部迴圈表的次數等於外部迴圈的行數，當外部迴圈沒有更多的行時，迴圈嵌套結束。另外，還可以看出，這種串連方式需要內部迴圈的表有序（也就是有索引），並且外部迴圈表的行數要小於內部迴圈的行數，否則查詢分析器就更傾向於Hash Join(會在本文後面講到)。

通過嵌套迴圈串連也可以看出，隨著資料量的增長這種方式對效能的消耗將呈現出指數層級的增長，所以資料量到一定程度時，查詢分析器往往就會採用這種方式。

 

 

合并串連(Merge Join)

Merge Join其實上就是將兩個有序隊列進行串連，需要兩端都已經有序，所以不必像Loop Join那樣不斷的尋找迴圈內部的表。其次，Merge Join需要表串連條件中至少有一個等號查詢分析器才會去選擇Merge Join。

Merge Join的過程我們可以簡單用下面圖進行描述:

圖8.Merge Join第一步

Merge Join首先從兩個輸入集合中各取第一行，如果匹配，則返回匹配行。加入兩行不匹配，則有較小值的輸入集合+1，9所示。

.更小值的輸入集合向下進1

 

因此，通常來說Merge Join如果輸入兩端有序，則Merge Join效率會非常高，但是如果需要使用顯式Sort來保證有序實現Merge Join的話，那麼Hash Join將會是效率更高的選擇。但是也有一種例外，那就是查詢中存在order by,group by,distinct等可能導致查詢分析器不得不進行顯式排序，那麼對於查詢分析器來說，反正都已經進行顯式Sort了,何不一石二鳥的直接利用Sort後的結果進行成本更小的MERGE JOIN？在這種情況下，Merge Join將會是更好的選擇。

另外，我們可以由Merge Join的原理看出，當串連條件為不等式(但不包括!=)，比如說> < >=等方式時，Merge Join有著更好的效率。

 

雜湊匹配(Hash Join)

雜湊匹配串連相對前面兩種方式更加複雜一些，但是雜湊匹配對於大量資料，並且無序的情況下效能均好於Merge Join和Loop Join。對於串連列沒有排序的情況下(也就是沒有索引)，查詢分析器會傾向於使用Hash Join。

雜湊匹配分為兩個階段,分別為產生和探測階段，首先是產生階段，第一階段產生階段具體的過程可以12所示。

圖12.雜湊匹配的第一階段

圖12中，將輸入源中的每一個條目經過散列函數的計算都放到不同的Hash Bucket中，其中Hash Function的選擇和Hash Bucket的數量都是黑盒，微軟並沒有公布具體的演算法，但我相信已經是非常好的演算法了。另外在Hash Bucket之內的條目是無序的。通常來講，查詢最佳化工具都會使用串連兩端中比較小的哪個輸入集來作為第一階段的輸入源。

接下來是探測階段，對於另一個輸入集合，同樣針對每一行進行散列函數，確定其所應在的Hash Bucket,在針對這行和對應Hash Bucket中的每一行進行匹配，如果匹配則返回對應的行。

通過瞭解雜湊匹配的原理不難看出，雜湊匹配涉及到散列函數，所以對CPU的消耗會非常高，此外，在Hash Bucket中的行是無序的，所以輸出結果也是無序的。

 

上面的情況都是記憶體可以容納下產生階段所需的記憶體，如果記憶體吃緊，則還會涉及到Grace雜湊匹配和遞迴雜湊匹配，這就可能會用到TempDB從而吃掉大量的IO。這裡就不細說了,有興趣的同學可以移步:http://msdn.microsoft.com/zh-cn/library/aa178403(v=SQL.80).aspx。

總結

下面我們通過一個表格簡單總結這幾種串連方式的消耗和使用情境:

	嵌套迴圈串連	合并串連	雜湊串連
適用情境	外層迴圈小，記憶體迴圈條件列有序	輸入兩端都有序	資料量大，且沒有索引
CPU	低	低（如果沒有顯式排序）	高
記憶體	低	低（如果沒有顯式排序）	高
IO	可能高可能低	低	可能高可能低

 

參考文獻:

http://msdn.microsoft.com/zh-cn/library/aa178403(v=SQL.80).aspx

http://www.dbsophic.com/SQL-Server-Articles/physical-join-operators-merge-operator.html

以下部落格做了一些三種join的耗時實驗，可以參考分析：

http://www.jasongj.com/2015/03/07/Join1/

join中級篇---------hash join & merge join & nested loop Join