MySQL中的Join 的演算法（NLJ、BNL、BKA）

Booksea發表於2023-05-08
MySql演算法

本文已收錄至Github，推薦閱讀 ? Java隨想錄

微信公眾號：Java隨想錄

目錄

摘要

Join是MySQL中最常見的查詢操作之一，用於從多個表中獲取資料並將它們組合在一起。Join演算法通常使用兩種基本方法：Index Nested-Loop Join（NLJ）和Block Nested-Loop Join（BNL）。本文將探討這兩種演算法的工作原理，以及如何在MySQL中使用它們。

什麼是Join

在MySQL中，Join是一種用於組合兩個或多個表中資料的查詢操作。Join操作通常基於兩個表中的某些共同的列進行，這些列在兩個表中都存在。MySQL支援多種型別的Join操作，如Inner Join、Left Join、Right Join、Full Join等。

Inner Join是最常見的Join型別之一。在Inner Join操作中，只有在兩個表中都存在的行才會被返回。例如，如果我們有一個“customers”表和一個“orders”表，我們可以透過在這兩個表中共享“customer_id”列來組合它們的資料。

SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

上面的查詢將返回所有存在於“customers”和“orders”表中的“customer_id”列相同的行。

Index Nested-Loop Join

Index Nested-Loop Join（NLJ）演算法是Join演算法中最基本的演算法之一。在NLJ演算法中，MySQL首先選擇一個表（通常是小型表）作為驅動表，並迭代該表中的每一行。然後，MySQL在第二個表中搜尋匹配條件的行，這個搜尋過程通常使用索引來完成。一旦找到匹配的行，MySQL將這些行組合在一起，並將它們作為結果集返回。

工作流程如圖：

例如，下面這個語句：

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

在這個語句裡，假設t1 是驅動表，t2是被驅動表。我們來看一下這條語句的explain結果。

可以看到，在這條語句裡，被驅動表t2的欄位a上有索引，join過程用上了這個索引，因此這個語句的執行流程是這樣的：

  1. 從表t1中讀入一行資料 R；
  2. 從資料行R中，取出a欄位到表t2裡去查詢；
  3. 取出表t2中滿足條件的行，跟R組成一行，作為結果集的一部分；
  4. 重複執行步驟1到3，直到表t1的末尾迴圈結束。

這個過程就跟我們寫程式時的巢狀查詢類似，並且可以用上被驅動表的索引，所以我們稱之為“Index Nested-Loop Join”，簡稱NLJ。

NLJ是使用上了索引的情況，如果查詢條件沒有使用到索引呢？

MySQL會選擇使用另一個叫作“Block Nested-Loop Join”的演算法，簡稱BNL。

Block Nested-Loop Join

Block Nested Loop Join（BNL）演算法與NLJ演算法不同的是，BNL演算法使用一個類似於快取的機制，將表資料分成多個塊，然後逐個處理這些塊，以減少記憶體和CPU的消耗。

例如，下面這個語句：

select * from t1 straight_join t2 on (t1.a=t2.b);

欄位b上是沒有建立索引的。

這時候，被驅動表上沒有可用的索引，演算法的流程是這樣的：

  1. 把表t1的資料讀入執行緒記憶體join_buffer中，由於我們這個語句中寫的是select *，因此是把整個表t1放入了記憶體；
  2. 掃描表t2，把表t2中的每一行取出來，跟join_buffer中的資料做對比，滿足join條件的，作為結果集的一部分返回。

這條SQL語句的explain結果如下所示：

可以看到，在這個過程中，對錶t1和t2都做了一次全表掃描，因此總的掃描行數是1100。由於join_buffer是以無序陣列的方式組織的，因此對錶t2中的每一行，都要做100次判斷，總共需要在記憶體中做的判斷次數是：100*1000=10萬次。

雖然Block Nested-Loop Join演算法是全表掃描。但是是在記憶體中進行的判斷操作，速度上會快很多。但是效能仍然不如NLJ。

join_buffer的大小是由引數join_buffer_size設定的，預設值是256k。如果放不下表t1的所有資料話，策略很簡單，就是分段放。

  1. 順序讀取資料行放入join_buffer中，直到join_buffer滿了。
  2. 掃描被驅動表跟join_buffer中的資料做對比，滿足join條件的，作為結果集的一部分返回。
  3. 清空join_buffer，重複上述步驟。

雖然分成多次放入join_buffer，但是判斷等值條件的次數還是不變的，依然是10萬次。

MRR & BKA

上篇文章裡我們講到了MRR（Multi-Range Read）。MySQL在5.6版本後引入了Batched Key Acess(BKA)演算法了。這個BKA演算法，其實就是對NLJ演算法的最佳化，BKA演算法正是基於MRR。

NLJ演算法執行的邏輯是：從驅動表t1，一行行地取出a的值，再到被驅動表t2去做join。也就是說，對於表t2來說，每次都是匹配一個值。這時，MRR的優勢就用不上了。

我們可以從表t1裡一次性地多拿些行出來，，先放到一個臨時記憶體，一起傳給表t2。這個臨時記憶體不是別人，就是join_buffer。

透過上一篇文章，我們知道join_buffer 在BNL演算法裡的作用，是暫存驅動表的資料。但是在NLJ演算法裡並沒有用。那麼，我們剛好就可以複用join_buffer到BKA演算法中。

NLJ演算法最佳化後的BKA演算法的流程，如圖所示：

圖中，我在join_buffer中放入的資料是P1~P100，表示的是隻會取查詢需要的欄位。當然，如果join buffer放不下P1~P100的所有資料，就會把這100行資料分成多段執行上圖的流程。

如果要使用BKA最佳化演算法的話，你需要在執行SQL語句之前，先設定

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

其中，前兩個引數的作用是要啟用MRR。這麼做的原因是，BKA演算法的最佳化要依賴於MRR。

對於BNL，我們可以透過建立索引轉為BKA。對於一些列建立索引代價太大，不好建立索引的情況，我們可以使用臨時表去最佳化。

例如，對於這個語句：

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

使用臨時表的大致思路是：

  1. 把表t2中滿足條件的資料放在臨時表tmp_t中；
  2. 為了讓join使用BKA演算法，給臨時表tmp_t的欄位b加上索引；
  3. 讓表t1和tmp_t做join操作。

這樣可以大大減少掃描的行數，提升效能。

總結

在MySQL中，不管Join使用的是NLJ還是BNL總是應該使用小表做驅動表。更準確地說，在決定哪個表做驅動表的時候，應該是兩個表按照各自的條件過濾，過濾完成之後，計算參與join的各個欄位的總資料量，資料量小的那個表，就是“小表”，應該作為驅動表。應當儘量避免使用BNL演算法，如果確認最佳化器會使用BNL演算法，就需要做最佳化。最佳化的常見做法是，給被驅動表的join欄位加上索引，把BNL演算法轉成BKA演算法。對於不好在索引的情況，可以基於臨時表的改進方案，提前過濾出小資料新增索引。

本篇文章就到這裡，感謝閱讀，如果本篇部落格有任何錯誤和建議，歡迎給我留言指正。文章持續更新，可以關注公眾號第一時間閱讀。

相關文章