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<p>有上述命令可知，這兩個表都有一個主鍵索引id 和一個索引a，欄位b 上無索引。預存程序 init_data 往表 t1 插入了 10000 行數據，在表 t2 插入的是 500 行數據。 </p><p>為了避免MySQL 優化器會自行選擇表作為驅動表，影響分析SQL 語句的執行過程，我們直接使用straight_join 來讓MySQL 使用固定的連接表順序進行查詢，如下語句中，t1是驅動表，t2是被驅動表。 </p><pre class="brush:php;toolbar:false">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的算法，简称 MySQL 的 join 功能變弱了？，它是 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次。

	全部存入	分5次存入
内存操作	10000 * 500	10000 * (100 + 100 + 100 + 100 + 100)
扫描行数	10000 + 500	10000 *  5 + 500

如上所示，和表格資料可以全部存入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 演算法要快的。

下面，我們來總結上述三種演算法的差異和優缺點。

	Nested Loop Join	#Hash Join	Sorted Merge Join
#連接條件	適用於任何條件	只適用於等值連接（=）	等值或非等值連接( >，=，'除外
主要消耗資源	CPU、磁碟I/ O	記憶體、暫存空間	記憶體、暫存空間
#特點	當有高選擇性索引或進行限制性搜尋時效率比較高，能夠快速返回第一次的搜尋結果	當缺乏索引或索引條件模糊時，Hash Join 比Nested Loop 有效。通常比 Merge Join 快。在資料倉儲環境下，如果表的紀錄數多，效率高	當缺乏索引或索引條件模糊時，Sort Merge Join 比 Nested Loop 更有效。當連接欄位有索引或提前排好序時，比hash join 快，並且支援更多的連接條件
缺點	無索引或表記錄多時效率低	建立哈希表需要大量內存，第一次的結果返回較慢	所有的表都需要排序。它為最優化的吞吐量而設計，並且在結果沒有全部找到前不返回資料
#需要索引	是(沒有索引效率太差)	否	否

對於Join 運算的理解

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

在業務不複雜的情況下，大多數join並不是無可取代。例如訂單記錄裡一般只有訂單使用者的user_id，回傳資訊時需要取得使用者姓名，可能的實作方案有以下幾種：

1. 一次資料庫操作，使用 join 操作，訂單表和使用者表進行join，連同使用者名稱一起返回；
2. 兩次資料庫操作，分兩次查詢，第一次獲得訂單資訊和user_id，第二次根據user_id 取姓名，使用代碼程式進行資訊合併；
3. 使用冗餘使用者名稱或從ES 等非關聯式資料庫讀取。

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

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

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

更多相關免費學習推薦：mysql教學##(影片)

#

以上是MySQL 的 join 功能變弱了？的詳細內容。更多資訊請關注PHP中文網其他相關文章！