我們在做SQL查詢的時候，經(jīng)常會用到各各種關聯(lián)查詢，對于不同的聯(lián)接，效率還是有差別的，具體該用哪種呢?雖說數(shù)據(jù)庫會做一些查詢的優(yōu)化，但了解原理，能有助我們直指核心。

開始join吧。

[[339944]]

我們分析三種常見的join: Merge join，Hash join 和 NestedLoop Join。在此之前，我們先介紹一些關鍵詞：

Inner ralation 和 outer relation。

一個 relation 可以是：

• 一張表
• 一個索引
• 一個前面操作的中間結果

當你在對兩個 relation 進行 Join 的時候，join 算法對inner 和 outer relation 的方式是有區(qū)別的。outer relation 是左數(shù)據(jù)集， inner relation 是右數(shù)據(jù)集。

比如說 A JOIN B，此時 A 是 outer relation，B 是 inner relation。而且一般 A JOIN B 和 B JOIN A 用時是不一樣的。

后面我們假設 outer relation 有 N 個元素， inner relation 有 M個元素。不過實際的優(yōu)化器里，可以從統(tǒng)計信息中拿到確切的值。

Nested loop join

嵌套關聯(lián)是最容易的一個。過程大概是：

遍歷 outer relation 的每一行

然后去查找inner relation 的每一行是否匹配

寫成偽代碼是這樣：

nested_loop_join(array outer, array inner) 
  for each row a in outer 
    for each row b in inner 
      if (match_join_condition(a,b)) 
        write_result_in_output(a,b) 
      end if 
    end for 
   end for 

因為兩重遍歷，所以復雜度是 O(N*M)。對應到磁盤的I/O，在outer relation中，N 行中的每一行，都需要從inner relation 中循環(huán)讀取M行數(shù)據(jù)。

所以這個算法需要從磁盤讀 N + N*M行數(shù)據(jù)。但是，如果 inner relation 足夠小，可以放到內存里的話，就只需要讀 M + N 次了。雖然說在時間復雜度上沒什么變化，但在磁盤I/O上這個方式還不錯，因此， inner relation 可以被索引替代，磁盤I/O也更有利。

Hash join

哈希連接更復雜，不過很多時候也比循環(huán)嵌套連接成本要低

哈希連接的原理是：

• 從 inner relation 中獲取所有元素
• 保存哈希表到磁盤
• 在內存中建立一個哈希表
• 逐條讀取outer relation 的所有元素
• (用哈希表的哈希函數(shù))計算每個元素的哈希值，來查找inner relation 關聯(lián)的哈希桶
• 查看 outer relation 的元素是否有哈希桶內的匹配。

在時間復雜度方面我們需要做點假設簡化問題：

• inner relation 被劃分成 X 個哈希桶
• 哈希函數(shù)接近均勻地分布每個 relation 內數(shù)據(jù)的哈希值，相當于說哈希桶大小是一致的。
• outer relation 的元素與哈希桶內的所有元素的匹配，成本是哈希桶內元素的數(shù)量。

時間復雜度是 (M/X) * N + cost_to_create_hash_table(M) + cost_of_hash_function*N。如果哈希函數(shù)創(chuàng)建了足夠小規(guī)模的哈希桶，那么復雜度就是 O(M+N)。

還有個哈希聯(lián)接的版本，對內存更友好，但是對磁盤 I/O 不夠有利。情況是這樣的：

• 計算outer relation 和 inner relation 雙方的哈希表
• 保存哈希表到磁盤
• 然后逐個比較兩個 relation 的哈希桶(一個關系讀到內存里，另一個逐行讀取)

Merge join

合并聯(lián)接是唯一產生排序的聯(lián)接算法。

注：這個簡化的合并聯(lián)接不區(qū)分內表或外表;兩個表扮演同樣的角色。但實際實現(xiàn)方式是不同的，比如當處理重復值時。

• (可選)排序聯(lián)接運算：兩個輸入源都按照聯(lián)接關鍵字排序。
• 合并聯(lián)接運算：排序后的輸入源合并到一起。

(1) 排序

我們已經(jīng)說過合并排序，在這里合并排序是個很好的算法。

有些時候數(shù)據(jù)集已經(jīng)排序了，比如：

• 如果表內部就是有序的，比如聯(lián)接條件里有一個索引組織表
• 如果 relation 是聯(lián)接條件里的一個索引
• 如果聯(lián)接是作用在一個已經(jīng)排序的查詢的中間結果

(2) 合并聯(lián)接

這部分與我們說過的合并排序中的合并運算非常相似。區(qū)別只在于我們不從兩個關系里挑選所有元素，只選相同的元素。

大致原理如下：

• 在兩個 relation 里，比較當前元素(當前的等號第一次出現(xiàn))
• 相同的時候，就把兩個元素都放到結果里，再比較兩個關系里的下一個元素
• 不相同的話，就去帶有最小元素的關系里找下一個元素
• 重復 1、2、3步驟直到其中一個關系的最后一個元素。

因為兩個關系都是已排序的，你不再需要「回過頭找」，所以這個方法很有效。

這個算法是個簡化版本，它沒有處理兩組數(shù)據(jù)中相同數(shù)據(jù)出現(xiàn)多次的情況。

哪個連接算法最好?

如果有最好的，就沒必要弄那么多種類型了。由于很多因素要考慮，所以不會有一個簡單的答案，需要考慮的因素例如這些：

• 空閑內存大?。簺]有足夠的內存的話，就和有力的哈希聯(lián)接，至少是完全內存中哈希聯(lián)接 說bye bye吧。
• 兩個數(shù)據(jù)集的大?。喝绻粋€大表聯(lián)接一個很小的表，嵌套循環(huán)聯(lián)接就比哈希聯(lián)接要快，因為后者有創(chuàng)建哈希的成本;如果兩個表都非常大，那么嵌套循環(huán)聯(lián)接CPU成本就很高。
• 是否有索引：有兩個 B+樹索引的話，合并聯(lián)接似乎是更聰明的選擇。
• 結果集是否需要排序：即使你用到的是無序的數(shù)據(jù)集，你也可能想用成本較高的合并聯(lián)接(帶排序的)，因為最終的結果是有序的，你可以把它和另一個結果集通過合并聯(lián)接合起來(也可能查詢用的 ORDER BY/GROUP BY/DISTINCT 等操作符隱式或顯式地要求一個排序結果)。
• 關系是否已經(jīng)排序：這時候合并聯(lián)接是最佳的選擇。
• 聯(lián)接的類型：是等值聯(lián)接(比如 tableA.col1 = tableB.col2 )還是內聯(lián)接?外聯(lián)接?笛卡爾乘積?或者自聯(lián)接?有些聯(lián)接在特定環(huán)境下是無法工作的。
• 數(shù)據(jù)的分布：假如聯(lián)接條件的數(shù)據(jù)是傾斜的(比如根據(jù)姓氏來聯(lián)接人，會有很多同姓的人)，用哈希聯(lián)接將是個災難，因為是哈希函數(shù)將產生分布極不均勻的哈希桶。
• 如果你希望聯(lián)接操作使用多線程或多進程。

【本文為51CTO專欄作者“侯樹成”的原創(chuàng)稿件，轉載請通過作者微信公眾號『Tomcat那些事兒』獲取授權】

戳這里，看該作者更多好文