MySQL的 join功能有什么用

小編給大家分享一下MySQL的 join功能有什么用，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

關于MySQL 的 join，大家一定了解過很多它的“軼事趣聞”，比如兩表 join 要小表驅動大表，阿里開發者規范禁止三張表以上的 join 操作，MySQL 的 join 功能弱爆了等等。這些規范或者言論亦真亦假，時對時錯，需要大家自己對 join 有深入的了解后才能清楚地理解。

下面，我們就來全面的了解一下 MySQL 的 join 操作。

正文

在日常數據庫查詢時，我們經常要對多表進行連表操作來一次性獲得多個表合并后的數據，這是就要使用到數據庫的 join 語法。join 是在數據領域中十分常見的將兩個數據集進行合并的操作，如果大家了解的多的話，會發現 MySQL，Oracle，PostgreSQL 和 Spark 都支持該操作。本篇文章的主角是 MySQL，下文沒有特別說明的話，就是以 MySQL 的 join 為主語。而 Oracle ，PostgreSQL 和 Spark 則可以算做將其吊打的大boss，其對 join 的算法優化和實現方式都要優于 MySQL。

MySQL 的 join 有諸多規則，可能稍有不慎，可能一個不好的 join 語句不僅會導致對某一張表的全表查詢，還有可能會影響數據庫的緩存，導致大部分熱點數據都被替換出去，拖累整個數據庫性能。

所以，業界針對 MySQL 的 join 總結了很多規范或者原則，比如說小表驅動大表和禁止三張表以上的 join 操作。下面我們會依次介紹 MySQL join 的算法，和 Oracle 和 Spark 的 join 實現對比，并在其中穿插解答為什么會形成上述的規范或者原則。

對于 join 操作的實現，大概有 Nested Loop Join (循環嵌套連接)，Hash Join(散列連接) 和 Sort Merge Join(排序歸并連接) 三種較為常見的算法，它們各有優缺點和適用條件，接下來我們會依次來介紹。

MySQL 中的 Nested Loop Join 實現

Nested Loop Join 是掃描驅動表，每讀出一條記錄，就根據 join 的關聯字段上的索引去被驅動表中查詢對應數據。它適用于被連接的數據子集較小的場景，它也是 MySQL join 的唯一算法實現，關于它的細節我們接下來會詳細講解。

MySQL 中有兩個 Nested Loop Join 算法的變種，分別是 Index Nested-Loop Join 和 Block Nested-Loop Join。

Index Nested-Loop Join 算法

下面，我們先來初始化一下相關的表結構和數據

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

delimiter ;;
# 定義存儲過程來初始化t1
create procedure init_data()
begin
  declare i int;
  set i=1;
  while(i<=10000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
# 調用存儲過來來初始化t1
call init_data();
# 創建并初始化t2
create table t2 like t1;
insert into t2 (select * from t1 where id<=500)復制代碼

有上述命令可知，這兩個表都有一個主鍵索引 id 和一個索引 a，字段 b 上無索引。存儲過程 init_data 往表 t1 里插入了 10000 行數據，在表 t2 里插入的是 500 行數據。

為了避免 MySQL 優化器會自行選擇表作為驅動表，影響分析 SQL 語句的執行過程，我們直接使用 straight_join 來讓 MySQL 使用固定的連接表順序進行查詢，如下語句中，t1是驅動表，t2是被驅動表。

select * from t2 straight_join t1 on (t2.a=t1.a);復制代碼

使用我們之前文章介紹的 explain 命令查看一下該語句的執行計劃。

從上圖可以看到，t1 表上的 a 字段是由索引的，join 過程中使用了該索引，因此該 SQL 語句的執行流程如下：

• 從 t2 表中讀取一行數據 L1；
• 使用L1 的 a 字段，去 t1 表中作為條件進行查詢；
• 取出 t1 中滿足條件的行， 跟 L1組成相應的行，成為結果集的一部分；
• 重復執行，直到掃描完 t2 表。

這個流程我們就稱之為 Index Nested-Loop Join，簡稱 NLJ，它對應的流程圖如下所示。

需要注意的是，在第二步中，根據 a 字段去表t1中查詢時，使用了索引，所以每次掃描只會掃描一行(從explain結果得出，根據不同的案例場景而變化)。

假設驅動表的行數是N，被驅動表的行數是 M。因為在這個 join 語句執行過程中，驅動表是走全表掃描，而被驅動表則使用了索引，并且驅動表中的每一行數據都要去被驅動表中進行索引查詢，所以整個 join 過程的近似復雜度是 N2log2M。顯然，N 對掃描行數的影響更大，因此這種情況下應該讓小表來做驅動表。

當然，這一切的前提是 join 的關聯字段是 a，并且 t1 表的 a 字段上有索引。

如果沒有索引時，再用上圖的執行流程時，每次到 t1 去匹配的時候，就要做一次全表掃描。這也導致整個過程的時間復雜度編程了 N * M，這是不可接受的。所以，當沒有索引時，MySQL 使用 Block Nested-Loop Join 算法。

Block Nested-Loop Join

Block Nested-Loop Join的算法，簡稱 BNL，它是 MySQL 在被驅動表上無可用索引時使用的 join 算法，其具體流程如下所示：

• 把表 t2 的數據讀取當前線程的 join_buffer 中，在本篇文章的示例 SQL 沒有在 t2 上做任何條件過濾，所以就是講 t2 整張表 放入內存中；
• 掃描表 t1，每取出一行數據，就跟 join_buffer 中的數據進行對比，滿足 join 條件的，則放入結果集。

比如下面這條 SQL

select * from t2 straight_join t1 on (t2.b=t1.b);復制代碼

這條語句的 explain 結果如下所示。可以看出

可以看出，這次 join 過程對 t1 和 t2 都做了一次全表掃描，并且將表 t2 中的 500 條數據全部放入內存 join_buffer 中，并且對于表 t1 中的每一行數據，都要去 join_buffer 中遍歷一遍，都要做 500 次對比，所以一共要進行 500 * 10000 次內存對比操作，具體流程如下圖所示。

主要注意的是，第一步中，并不是將表 t2 中的所有數據都放入 join_buffer，而是根據具體的 SQL 語句，而放入不同行的數據和不同的字段。比如下面這條 join 語句則只會將表 t2 中符合 b >= 100 的數據的 b 字段存入 join_buffer。

select t2.b,t1.b from t2 straight_join t1 on (t2.b=t1.b) where t2.b >= 100;復制代碼

join_buffer 并不是無限大的，由 join_buffer_size 控制，默認值為 256K。當要存入的數據過大時，就只有分段存儲了，整個執行過程就變成了：

• 掃描表 t2，將符合條件的數據行存入 join_buffer，因為其大小有限，存到100行時滿了，則執行第二步；
• 掃描表 t1，每取出一行數據，就跟 join_buffer 中的數據進行對比，滿足 join 條件的，則放入結果集；
• 清空 join_buffer；
• 再次執行第一步，直到全部數據被掃描完，由于 t2 表中有 500行數據，所以一共重復了 5次

這個流程體現了該算法名稱中 Block 的由來，分塊去執行 join 操作。因為表 t2 的數據被分成了 5 次存入 join_buffer，導致表 t1 要被全表掃描 5次。

如上所示，和表數據可以全部存入 join_buffer 相比，內存判斷的次數沒有變化，都是兩張表行數的乘積，也就是 10000 * 500，但是被驅動表會被多次掃描，每多存入一次，被驅動表就要掃描一遍，影響了最終的執行效率。

基于上述兩種算法，我們可以得出下面的結論，這也是網上大多數對 MySQL join 語句的規范。

• 被驅動表上有索引，也就是可以使用Index Nested-Loop Join 算法時，可以使用 join 操作。

• 無論是Index Nested-Loop Join 算法或者 Block Nested-Loop Join 都要使用小表做驅動表。

因為上述兩個 join 算法的時間復雜度至少也和涉及表的行數成一階關系，并且要花費大量的內存空間，所以阿里開發者規范所說的嚴格禁止三張表以上的 join 操作也是可以理解的了。

但是上述這兩個算法只是 join 的算法之一，還有更加高效的 join 算法，比如 Hash Join 和 Sorted Merged join。可惜這兩個算法 MySQL 的主流版本中目前都不提供，而 Oracle ，PostgreSQL 和 Spark 則都支持，這也是網上吐槽 MySQL 弱爆了的原因(MySQL 8.0 版本支持了 Hash join，但是8.0目前還不是主流版本)。

其實阿里開發者規范也是在從 Oracle 遷移到 MySQL 時，因為 MySQL 的 join 操作性能太差而定下的禁止三張表以上的 join 操作規定的 。

Hash Join 算法

Hash Join 是掃描驅動表，利用 join 的關聯字段在內存中建立散列表，然后掃描被驅動表，每讀出一行數據，并從散列表中找到與之對應數據。它是大數據集連接操時的常用方式，適用于驅動表的數據量較小，可以放入內存的場景，它對于沒有索引的大表和并行查詢的場景下能夠提供最好的性能。可惜它只適用于等值連接的場景，比如 on a.id = where b.a_id。

還是上述兩張表 join 的語句，其執行過程如下

• 將驅動表 t2 中符合條件的數據取出，對其每行的 join 字段值進行 hash 操作，然后存入內存中的散列表中；
• 遍歷被驅動表 t1，每取出一行符合條件的數據，也對其 join 字段值進行 hash 操作，拿結果到內存的散列表中查找匹配，如果找到，則成為結果集的一部分。

可以看出，該算法和 Block Nested-Loop Join 有類似之處，只不過是將無序的 Join Buffer 改為了散列表 hash table，從而讓數據匹配不再需要將 join buffer 中的數據全部遍歷一遍，而是直接通過 hash，以接近 O(1) 的時間復雜度獲得匹配的行，這極大地提高了兩張表的 join 速度。

不過由于 hash 的特性，該算法只能適用于等值連接的場景，其他的連接場景均無法使用該算法。

Sorted Merge Join 算法

Sort Merge Join 則是先根據 join 的關聯字段將兩張表排序(如果已經排序好了，比如字段上有索引則不需要再排序)，然后在對兩張表進行一次歸并操作。如果兩表已經被排過序，在執行排序合并連接時不需要再排序了，這時Merge Join的性能會優于Hash Join。Merge Join可適于于非等值Join（>，<，>=，<=，但是不包含!=，也即<>）。

需要注意的是，如果連接的字段已經有索引，也就說已經排好序的話，可以直接進行歸并操作，但是如果連接的字段沒有索引的話，則它的執行過程如下圖所示。

• 遍歷表 t2，將符合條件的數據讀取出來，按照連接字段 a 的值進行排序；
• 遍歷表 t1，將符合條件的數據讀取出來，也按照連接字段 a 的值進行排序；
• 將兩個排序好的數據進行歸并操作，得出結果集。

Sorted Merge Join 算法的主要時間消耗在于對兩個表的排序操作，所以如果兩個表已經按照連接字段排序過了，該算法甚至比 Hash Join 算法還要快。在一邊情況下，該算法是比 Nested Loop Join 算法要快的。

下面，我們來總結一下上述三種算法的區別和優缺點。

對于 Join 操作的理解

講完了 Join 相關的算法，我們這里也聊一聊對于 join 操作的業務理解。

在業務不復雜的情況下，大多數join并不是無可替代。比如訂單記錄里一般只有訂單用戶的 user_id，返回信息時需要取得用戶姓名，可能的實現方案有如下幾種：

1. 一次數據庫操作，使用  join 操作，訂單表和用戶表進行 join，連同用戶名一起返回；
2. 兩次數據庫操作，分兩次查詢，第一次獲得訂單信息和 user_id，第二次根據 user_id 取姓名，使用代碼程序進行信息合并；
3. 使用冗余用戶名稱或者從 ES 等非關系數據庫中讀取。

上述方案都能解決數據聚合的問題，而且基于程序代碼來處理，比數據庫 join 更容易調試和優化，比如取用戶姓名不從數據庫中取，而是先從緩存中查找。

當然， join 操作也不是一無是處，所以技術都有其使用場景，上邊這些方案或者規則都是互聯網開發團隊總結出來的，適用于高并發、輕寫重讀、分布式、業務邏輯簡單的情況，這些場景一般對數據的一致性要求都不高，甚至允許臟讀。

但是，在金融銀行或者財務等企業應用場景，join 操作則是不可或缺的，這些應用一般都是低并發、頻繁復雜數據寫入、CPU密集而非IO密集，主要業務邏輯通過數據庫處理甚至包含大量存儲過程、對一致性與完整性要求很高的系統。

以上是MySQL的 join功能有什么用的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！