淺談查詢優化器中的JOIN演算法

查詢優化器都是支援JOIN操作的，而SQL Server 中主要有以下三類JOIN演算法：Nested Loop、Sort-Merge以及Hash Join。儘管每種演算法都並不是很複雜，但考慮到效能優化，在產品級的優化器實現時往往使用的是改進過的變種演算法。譬如SQL Server 支援block nested loops、index nexted loops、sort-merge、hash join以及hash team。我們在這裡只對上述三種基本演算法的原型做一個簡單的介紹。

　　【假設】有兩張表R和S，R共佔有M頁，S共佔有N頁。r 和 s 分別代表元組，而 i 和 j 分別代表第i或者第 j 個欄位，也就是後文提到的JOIN欄位。

　　1. Nested Loop Join(巢狀迴圈聯結)

　　演算法：

　　其思路相當的簡單和直接：對於關係R的每個元組 r 將其與關係S的每個元組 s 在JOIN條件的欄位上直接比較並篩選出符合條件的元組。寫成虛擬碼就是：

foreach tuple r Î R do 　　foreach tuple s Î S do 　　if ri == sj then add to result

　　代價：

　　被聯結的表所處內層或外層的順序對磁碟I/O開銷有著非常重要的影響。而CPU開銷相對來說影響較小，主要是元組讀入記憶體以後(in-memory)的開銷，是 O (n * m)

　　對於I/O開銷，根據 page-at-a-time 的前提條件，I/O cost = M + M * N，翻譯一下就是 I/O的開銷 = 讀取M頁的I/O開銷 + M次讀取N頁的I/O開銷。

　　使用小結：

　　• 適用於一個集合大而另一個集合小的情況(將小集合做為外迴圈)，I/O效能不錯。

　　• 當外迴圈輸入相當小而內迴圈非常大且有索引建立在JOIN欄位上時，I/O效能相當不錯。

　　• 當兩個集合中只有一個在JOIN欄位上建立索引時，一定要將該集合作為內迴圈。

　　• 對於一對一的匹配關係(兩個具有唯一約束欄位的聯結)，可以在找到匹配元組後跳過該次內迴圈的剩餘部分(類似於程式語言迴圈語句中的continue)。

　　2. Sort-Merge Join (排序合併聯結)

　　Nested Loop一般在兩個集合都很大的情況下效率就相當差了，而Sort-Merge在這種情況下就比它要高效不少，尤其是當兩個集合的JOIN欄位上都有聚集索引(clustered index)存在時，Sort-Merge效能將達到最好。

　　演算法：

　　基本思路也很簡單(複習一下資料結構中的合併排序吧)，主要有兩個步驟：

　　(1) 按JOIN欄位進行排序

　　(2) 對兩組已排序集合進行合併排序，從來源端各自取得資料列後加以比較(需要根據是否在JOIN欄位有重複值做特殊的“分割槽”處理)

　　代價：(主要是I/O開銷)

　　有兩個因素左右Sort-Merge的開銷：JOIN欄位是否已排序 以及 JOIN欄位上的重複值有多少。

　　• 最好情況下(兩列都已排序且至少有一列沒有重複值)：O (n + m) 只需要對兩個集合各掃描一遍

　　• 最差情況下(兩列都未排序且兩列上的所有值都相同)：O (n * log n + m * log m + n * m) 兩次排序以及一次全部元組間的笛卡爾乘積

使用小結：

　　如前所述，可以考慮在兩個結果集都很大情況下使用，最好能有聚集索引保證已經排序完畢。而在實際應用中，我們經常會與遇到的主鍵-外來鍵關係就是Sort-Merge的一個很好的應用。這種情況下，一般兩列都會有聚集索引(已排序)而且一對多的關係保證了至少有一列沒有重複值，這種情況下，Sort-Merge的效能是三種裡面最好的。

　　另外，如果要求查詢的SQL語法本身就要求GROUP BY、ORDER BY、CUBE等執行，則查詢語法整體本來就要做排序，因此可以重用排序結果，此時Merge也是不錯的選擇。

　　3. Hash Join (雜湊聯結)

　　Hash Join在本質上類似於兩列都有重複值時的Sort-Merge的處理思想——分割槽(patitioning)。但它們也有區別：Hash Join通過雜湊來分割槽(每一個桶就是一個分割槽)而Sort-Merge通過排序來分割槽(每一個重複值就是一個分割槽)。

　　值得注意的是，Hash Join與上述兩種演算法之間的較大區別同時也是一個較大限制是它只能應用於等值聯結(equality join)，這主要是由於雜湊函式及其桶的確定性及無序性所導致的。

　　演算法：

　　基本的Hash Join演算法由以下兩步組成：

　　(1) Build Input Phase： 基於JOIN欄位，使用雜湊函式h2為較小的S集合構建記憶體中(in-memory)的雜湊表，相同鍵值的以linked list組成一個桶(bucket)

　　(2) Probe Input Phase： 在較大的R集合上對雜湊表進行核對以完成聯結。其中核對操作包括：

foreach tuple r Î R do 　　hash on the joining attribute using the hash function of step 1 to find a bucket in the hash table 　　if the bucket is nonempty 　　foreach tuple s in the found bucket 　　if ri == sj then add to result

　　代價：

　　值得注意的是對於大集合R的每個元組 r ，hash bucket中對應 r 的那個bucket中的每個元組都需要與 r 進行比較，這也是演算法最耗時的地方所在。

　　CPU開銷是O (m + n * b) b是每個bucket的平均元組數量。

　　使用小結：

　　一般來說，查詢優化器會首先考慮Nested Loop和Sort-Merge，但如果兩個集合量都不小且沒有合適的索引時，才會考慮使用Hash Join。

　　Hash Join也用於許多集合比較操作，inner join、left/right/full outer join、intersect、difference等等，當然了，需要保證都是等值聯結。

　　另外，Hash Join的變種能夠移除重複和進行分組，它只使用一個輸入，兼做Build和Probe的角色。

　  其實產品級的優化器一般都改進了這些基本演算法，而改進過的版本的確有較大的效能提升。在這裡只是給需要判斷執行計劃優劣或者研究查詢優化器實現的兄弟提供原理方面的介紹，在實際應用中我們還得結合豐富的statistics作出準確的判斷。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-557584/，如需轉載，請註明出處，否則將追究法律責任。