mysql支援的join演算法

• Nested Loop Join
• Index Nested-Loop Join
• Block Nested-Loop Join

Index Nested-Loop Join 和 Block Nested-Loop Join是在Nested-Loop Join基礎上做了最佳化。

1.  Nested Loop Join

Nested-Loop Join的思想就是透過雙層迴圈比較資料來獲得結果；其中左表為外迴圈，右表為內迴圈，左表為驅動表。其實現邏輯簡單粗暴，可以理解為兩層for迴圈，小表在外環，大表在內環，資料比較的次數 = 小表記錄數 * 大表記錄數。
//select * from t1 inner join t2 on t1.a= t2.a;List<結果> lists = new ArrayList<>();for(t2 t2 : t2){ //外層迴圈 for(t1 t1 : t1){ //內迴圈 if(t2.a().equals(t1.a())){ //條件匹配 //存放結果到結果集 結果 = t1的欄位 + t2的欄位 lists.add(結果集); } }}
索引巢狀迴圈連線 Index Nested-Loop Join

2.  Index Nested-Loop Join

最佳化思路：內表為大表，可在 join欄位上建立索引，減少內表資料的掃描次數。

執行流程：
0.前置條件：外表 t2 已在連線用的 a 欄位以建立索引；
1.從外表 t1 中讀取一行資料 R；
2.使用 R 中的a 欄位和內表 t2 的 a 欄位進行索引關聯查詢；
3.根據索引到的記錄取出表 t2 中滿足條件的行，跟 R 組成一行，作為結果集的一部分；
4.重複執行步驟 1 到 3，直到表 t1 迴圈結束。

可見，透過索引的建立，避免了對大表進行全表掃描，加快了查詢速度。
快取塊巢狀迴圈連線 Block Nested-Loop Join

3.  Block Nested-Loop Join

最佳化思路：透過一次性快取多條資料，減少外層表的迴圈次數。
t1為小表時的執行流程：1.把t1 表查詢的欄位資料整個讀入執行緒記憶體 join_buffer 中；2.掃描表 t2，把表 t2 中的每一行取出來，跟 join_buffer 中的 資料做對比，滿足 join 條件的，作為結果集的一部分返回。

Block Nested-Loop Join2
t1為大表時的執行流程：
1.掃描表 t1，順序讀取一定長度的資料行放入 join_buffer 中；
2.掃描表 t2，把 t2 中的每一行取出來，跟 join_buffer 中的資料做對比，滿足 join 條件的，作為結果集的一部分返回；

3.清空 join_buffer；
4.順序讀取 t1表下一批次資料放入 join_buffer 中，重複步驟2

Oracle的join演算法

• Nested Loop Join，巢狀迴圈
• Hash Join，將兩個表中較小的一個在記憶體中構造一個 Hash 表（對JoinKey），掃描另一個表
• Sort Merge Join，將兩個表排序，然後再進行join
DB2和SQL Server也使用這三種方式join演算法。

1.  Hash Join

Hash Join的使用場景：

• 適合於小表與大表連線、返回大型結果集的連線
• 只能用於等值連線，且只能在CBO最佳化器模式下
在 inner/left/right join，以及union/group by等 都會使用hash join進行操作。
實現原理 :

Hash Join中的小表稱之為hash表，大表稱為探查表，以小表作為驅動表。
• 兩個輸入：
– build input（也叫做outer input），小表
– probe input（也叫做inner input），大表
• 兩個階段：
– Build（構造）階段，處理build input
– Probe（探測）階段，探測probe input

build 階段，主要是構造雜湊表(hash table)：
• 在inner/left/right join等操作中，表關聯欄位作為hash key
• 在group by操作中，group by的欄位作為hash key；
• 在union或其它去重操作中，hash key包括所有的select欄位。
一個 hash值對應到hash table中的hash buckets。多個hash buckets可以使用連結串列資料結構連線起來。

Probe 階段，從probe input中取出每一行記錄，根據key值生成hash值，從build階段構造的hash table中搜尋對應的hash bucket。
• Grace Hash join

在記憶體足夠大的情況下，建立的雜湊表整體都放在記憶體中。

當小表資料量大到記憶體無法容納時，會分別將 build input和probe input切分成多個分割槽（partition），這樣就將一個大的 Hash Join 切分成了多個獨立且互相不影響的 Hash Join。

Hash Join的效能和資源消耗：

Hash join效率最高，因為只要對兩張表掃描一次。

Hash join的主要資源消耗在於CPU。因其在記憶體中建立臨時的hash表並進行hash計算時，都需要做大量的hash操作。

2. Merge Join
Merge join前需要確保兩個關聯表都是按照關聯的欄位進行排序；如果關聯欄位有索引並且排序一致，則可直接進行Merge Join操作。

通常用在資料沒有索引但是已經排序的情況下，如果不需要排序操作， Merge Join本身的速度很快。

由於兩表資料都要先做排序，再做 merge，因此效率相對較差。merge join的資源消耗主要在於磁碟I/O(掃描表或索引)。

Hive的join演算法

• Reduce Join

hive-map-reduce

在 Hive中也叫Common Join或Shuffle Join。
Shuffle階段主要根據Key值hash分組，推送到不同的Reduce階段。

• Map Join
如果一張大表一張小表，就可以將小表放到記憶體裡，在 map端做join

Map Join可以進行不等值的連線操作。

• SMB(Sort-Merge-Buket) Join
首先進行排序，繼而合併，然後放到所對應的 bucket中去。
bucket 就是按key進行hash，相同值的都放到一個bucket檔案中去 。
SparkSQL的join演算法
• shuffle hash join
• broadcast hash join
• sort merge join

前兩者都屬於 hash join，只不過在hash join之前需要先shuffle還是先broadcast。
shuffler hash join 類似Grace Hash join，先根據join key進行分割槽，再hash join。

broadcast hash join 是將小表廣播分發到大表所在的分割槽節點上,與分割槽記錄進行hash join。
Sort-Merge Join：

sort-merge-join
shuffle階段：將兩張大表根據join key進行重新分割槽
sort階段：對單個分割槽節點的兩表資料，分別進行排序
merge階段：對排好序的兩張分割槽表資料執行join操作。

sort-merge-join
理論上資料經過 shuffle之後是不需要sort的，可以直接merge。
sort merge join適合兩張大表進行Join。

以上為資料庫常見的JOIN演算法， 「分散式技術專題」是國產資料庫 hubble 團隊精心整編，專題會持續更新，歡迎大家保持關注。