一文弄懂Join語句最佳化

來源：MySQL資料庫聯盟

這一篇文章就來介紹一下關聯查詢的最佳化，文章有點長，請耐心看完，有問題歡迎討論指正。

1 關聯查詢的演算法特性總結

要想弄懂關聯查詢的最佳化，就必須先知道關聯查詢相關的演算法：

2 Simple Nested-Loop Join演算法

迴圈驅動表中的每一行

再到被驅動表找到滿足關聯條件的記錄

因為關聯欄位沒索引，所以在被驅動表裡的查詢需要全表掃描

這種方法邏輯簡單，但是效率很差

比如驅動表資料量是 m，被驅動表資料量是 n，則掃描行數為 m * n

當然，好在，MySQL也沒有采用這種演算法，即使關聯欄位沒索引，也會採用Block Nested-Loop Join或者Hash Join，等下會細說。

3 Index Nested-Loop Join演算法

剛才我們說的是關聯欄位沒索引的情況，假如關聯欄位有索引，就會採用Index Nested-Loop Join演算法（一般簡寫成：NLJ）

一次一行迴圈地從第一張表（稱為驅動表）中讀取行，在這行資料中取到關聯欄位，根據關聯欄位在另一張表（被驅動表）裡，透過索引匹配，取出滿足條件的行，然後取出兩張表的結果合集。

為了方便理解，我們會結合實驗進行講解，先來建立測試表並寫入測試資料：

use martin; drop table if exists t1; CREATE TABLE `t1` (`id` int NOT NULL auto_increment,`a` int DEFAULT NULL,`b` int DEFAULT NULL,`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄建立時間',`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPCOMMENT '記錄更新時間',PRIMARY KEY (`id`),KEY `idx_a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
drop procedure if exists insert_t1; 
delimiter ;;create procedure insert_t1()begindeclare i int; set i=1;while(i<=10000)doinsert into t1(a,b) values(i, i); set i=i+1; end while;end;;delimiter ; call insert_t1(); 
drop table if exists t2; create table t2 like t1; insert into t2 select * from t1 limit 100;

我們來看一個例子：

explain select * from t1 inner join t2 on t1.a = t2.a;

Tips：表 t1 和表 t2 中的 a 欄位都有索引。

執行計劃如下：

從執行計劃中可以看到這些資訊：

驅動表是 t2，被驅動表是 t1。原因是：explain 分析 join 語句時，在第一行的就是驅動表；選擇 t2 做驅動表的原因：如果沒固定連線方式（比如沒加 straight_join）,最佳化器會優先選擇小表做驅動表。所以使用 inner join 時，前面的表並不一定就是驅動表。

使用了 NLJ。原因是：一般 join 語句中，如果執行計劃 Extra 中未出現 Using join buffer （***）；則表示使用的 join 演算法是 NLJ。

4 Block Nested-Loop Join演算法

如果被驅動表的關聯欄位沒索引，在MySQL 8.0.20版本之前，就會使用 Block Nested-Loop Join(簡稱：BNL)

Block Nested-Loop Join(BNL) 演算法的思想是：把驅動表的資料讀入到 join_buffer 中，然後掃描被驅動表，把被驅動表每一行取出來跟 join_buffer 中的資料做對比，如果滿足 join 條件，則返回結果給客戶端。

我們一起看看下面這條 SQL 語句：

select * from t1 inner join t2 on t1.b = t2.b;

Tips：表 t1 和表 t2 中的 b 欄位都沒有索引

在 MySQL 5.7 版本下的執行計劃如下：

在 Extra 發現 Using join buffer (Block Nested Loop)，這個就說明該關聯查詢使用的是 BNL 演算法。

在 MySQL 8.0.25 版本下的執行計劃如下：

在 Extra 發現 Using join buffer (hash join)，因為前面提到，從 MySQL 8.0.20 開始，雜湊連線替換了塊巢狀迴圈。

5 Hash Join演算法

從 MySQL 8.0.20 開始，MySQL 不再使用 Block Nested-Loop Join 演算法，並且在以前使用 Block Nested-Loop Join 演算法的所有情況下都使用 Hash Join 最佳化。

核心思想是將驅動表的資料載入到記憶體中構建雜湊表

然後逐行讀取被驅動表的資料，並透過雜湊函式將連線條件的列的值對映為雜湊值，去之前構建的Hash表查詢匹配的記錄

一旦在Hash表中找到匹配的記錄，對這些記錄進行一一比較，得出最終的Join結果

跟Block Nested-Loop Join演算法類似，但是不需要將被驅動表的資料塊寫入記憶體或磁碟，更少的IO以及更節省資源

6 Batched Key Access演算法

在學了 NLJ 和 BNL 演算法後，你是否有個疑問，如果把 NLJ 與 BNL 兩種演算法的一些優秀的思想結合，是否可行呢？

比如 NLJ 的關鍵思想是：被驅動表的關聯欄位有索引。

而 BNL 的關鍵思想是：把驅動表的資料批次提交一部分放到 join_buffer 中。

從 MySQL 5.6 開始，確實出現了這種集 NLJ 和 BNL 兩種演算法優點於一體的新演算法：Batched Key Access(BKA)。

其原理是：

將驅動表中相關列批次放入 join_buffer 中

批次將關聯欄位的值傳送到 Multi-Range Read(MRR) 介面

MRR 透過接收到的值，根據其對應的主鍵 ID 進行排序，然後再進行資料的讀取和操作

返回結果給客戶端。

7 補充下MRR相關知識

當表很大並且沒有儲存在快取中時，使用輔助索引上的範圍掃描讀取行可能導致對錶有很多隨機訪問。

而 Multi-Range Read 最佳化的設計思路是：查詢輔助索引時，對查詢結果先按照主鍵進行排序，並按照主鍵排序後的順序，進行順序查詢，從而減少隨機訪問磁碟的次數。

使用 MRR 時，explain 輸出的 Extra 列顯示的是 Using MRR。

控制MRR的引數

optimizer_switch 中 mrr_cost_based 引數的值會影響 MRR。

如果 mrr_cost_based=on，表示最佳化器嘗試在使用和不使用 MRR 之間進行基於成本的選擇。

如果 mrr_cost_based=off，表示一直使用 MRR。

更多 MRR 資訊請參考官方手冊：https://dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html。

8 BKA開啟

先來看下這條SQL的執行計劃：

explain select * from t1 inner join t2 on t1.a = t2.a;

下面嘗試開啟 BKA ：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

這裡對上面幾個引數做下解釋：

• mrr=on 開啟 mrr

• mrr_cost_based=off 不需要最佳化器基於成本考慮使用還是不使用 MRR，也就是一直使用 MRR

• batched_key_access=on 開啟 BKA

然後再看 sql1 的執行計劃：

explain select * from t1 inner join t2 on t1.a = t2.a;

在 Extra 欄位中發現有 Using join buffer (Batched Key Access)，表示確實變成了 BKA 演算法。

9 最佳化關聯查詢

扯了這麼多，我們就來講一下：究竟怎樣最佳化關聯查詢：

關聯欄位新增索引

透過上面的內容，我們知道了 BNL、NLJ 和 BKA 的原理，因此讓 BNL（Block Nested-Loop Join）或者Hash Join變成 NLJ （Index Nested-Loop Join）或者 BKA（Batched Key Access），可以提高 join 的效率。我們來看下面的例子

我們構造出兩個演算法對於的例子：

Block Nested-Loop Join 的例子：

select * from t1 join t2 on t1.b= t2.b;

需要 0.08 秒。

Index Nested-Loop Join 的例子：

select * from t1 join t2 on t1.a= t2.a;

只需要 0.01 秒。

再對比一下兩條 SQL 的執行計劃：

前者掃描的行數是 100 和 9963。

對比執行時間和執行計劃，再結合在本節開始講解的兩種演算法的執行流程，很明顯 Index Nested-Loop Join 效率更高。

因此建議在被驅動表的關聯欄位上新增索引，讓 BNL或者Hash Join變成 NLJ 或者 BKA ，可明顯最佳化關聯查詢。

選擇小表作為驅動表

從上面幾種Join演算法，也能看出來，驅動表需要全表掃描，再存放在記憶體中

如果小表是驅動表，那遍歷的行也會更少。

來舉個例子，看下大小表做驅動表執行計劃的對比：

我們來看下以 t2 為驅動表的 SQL：

select * from t2 straight_join t1 on t2.a = t1.a;

這裡使用 straight_join 可以固定連線方式，讓前面的表為驅動表。

再看下以 t1 為驅動表的 SQL：

select * from t1 straight_join t2 on t1.a = t2.a;

我們對比下兩條 SQL 的執行計劃：

明顯前者掃描的行數少（注意關注 explain 結果的 rows 列），所以建議小表驅動大表。

資料集較大可以採用BKA最佳化

BKA演算法採用批次處理機制，利用索引快速定位匹配記錄，適合大型資料集的Join操作

版本升級

前面也提到了，如果被驅動表的關聯欄位沒索引，在MySQL 8.0.20版本之前，就會使用 Block Nested-Loop Join(簡稱：BNL)，

從 MySQL 8.0.20 開始，MySQL 不再使用 Block Nested-Loop Join 演算法，並且在以前使用 Block Nested-Loop Join 演算法的所有情況下都使用 Hash Join 最佳化。

相對於Block Nested-Loop Join演算法，Hash Join不需要將被驅動表的資料塊寫入記憶體或磁碟，使用更少的IO以及更節省資源。

所以，假如有條件，可以升級到8.0.20之後的版本。