目錄什么是JoinIndex Nested-Loop JoinBlock Nested-Loop JoinMRR & BKA總結(jié)什么是Join

在MySQL中，Join是一種用于組合兩個或多個表中數(shù)據(jù)的查詢操作。

Join操作通常基于兩個表中的某些共同的列進行，這些列在兩個表中都存在。

MySQL支持多種類型的Join操作，如Inner Join、Left Join、Right Join、Full Join等。

Inner Join是最常見的Join類型之一。在Inner Join操作中，只有在兩個表中都存在的行才會被返回。

例如，如果我們有一個“customers”表和一個“orders”表，我們可以通過在這兩個表中共享“customer_id”列來組合它們的數(shù)據(jù)。

SELECT *FROM customersINNER JOIN ordersON customers.customer_id = orders.customer_id;

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

Index Nested-Loop Join

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

工作流程如圖：

例如，下面這個語句：

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

在這個語句里，假設(shè)t1 是驅(qū)動表，t2是被驅(qū)動表。我們來看一下這條語句的explain結(jié)果。

可以看到，在這條語句里，被驅(qū)動表t2的字段a上有索引，join過程用上了這個索引，因此這個語句的執(zhí)行流程是這樣的：

從表t1中讀入一行數(shù)據(jù) R；從數(shù)據(jù)行R中，取出a字段到表t2里去查找；取出表t2中滿足條件的行，跟R組成一行，作為結(jié)果集的一部分；重復(fù)執(zhí)行步驟1到3，直到表t1的末尾循環(huán)結(jié)束。

這個過程就跟我們寫程序時的嵌套查詢類似，并且可以用上被驅(qū)動表的索引，所以我們稱之為**“Index Nested-Loop Join”，簡稱NLJ**。

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

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

Block Nested-Loop Join

Block Nested Loop Join（BNL）算法與NLJ算法不同的是，BNL算法使用一個類似于緩存的機制，將表數(shù)據(jù)分成多個塊，然后逐個處理這些塊，以減少內(nèi)存和CPU的消耗。

例如，下面這個語句：

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

字段b上是沒有建立索引的。

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

把表t1的數(shù)據(jù)讀入線程內(nèi)存join_buffer中，由于我們這個語句中寫的是select *，因此是把整個表t1放入了內(nèi)存；掃描表t2，把表t2中的每一行取出來，跟join_buffer中的數(shù)據(jù)做對比，滿足join條件的，作為結(jié)果集的一部分返回。

這條SQL語句的explain結(jié)果如下所示：

可以看到，在這個過程中，對表t1和t2都做了一次全表掃描，因此總的掃描行數(shù)是1100。由于join_buffer是以無序數(shù)組的方式組織的，因此對表t2中的每一行，都要做100次判斷，總共需要在內(nèi)存中做的判斷次數(shù)是：100*1000=10萬次。

雖然Block Nested-Loop Join算法是全表掃描。但是是在內(nèi)存中進行的判斷操作，速度上會快很多。但是性能仍然不如NLJ。

join_buffer的大小是由參數(shù)join_buffer_size設(shè)定的，默認值是256k。如果放不下表t1的所有數(shù)據(jù)話，策略很簡單，就是分段放。

順序讀取數(shù)據(jù)行放入join_buffer中，直到j(luò)oin_buffer滿了。掃描被驅(qū)動表跟join_buffer中的數(shù)據(jù)做對比，滿足join條件的，作為結(jié)果集的一部分返回。清空join_buffer，重復(fù)上述步驟。

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

MRR & BKA

上篇文章里我們講到了MRR（Multi-Range Read）。MySQL在5.6版本后引入了Batched Key Acess(BKA)算法了。這個BKA算法，其實就是對NLJ算法的優(yōu)化，BKA算法正是基于MRR。

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

我們可以從表t1里一次性地多拿些行出來，，先放到一個臨時內(nèi)存，一起傳給表t2。這個臨時內(nèi)存不是別人，就是join_buffer。

通過上一篇文章，我們知道join_buffer 在BNL算法里的作用，是暫存驅(qū)動表的數(shù)據(jù)。但是在NLJ算法里并沒有用。那么，我們剛好就可以復(fù)用join_buffer到BKA算法中。

NLJ算法優(yōu)化后的BKA算法的流程，如圖所示：

圖中，我在join_buffer中放入的數(shù)據(jù)是P1~P100，表示的是只會取查詢需要的字段。當然，如果join buffer放不下P1~P100的所有數(shù)據(jù)，就會把這100行數(shù)據(jù)分成多段執(zhí)行上圖的流程。

如果要使用BKA優(yōu)化算法的話，你需要在執(zhí)行SQL語句之前，先設(shè)置

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

其中，前兩個參數(shù)的作用是要啟用MRR。這么做的原因是，BKA算法的優(yōu)化要依賴于MRR。

對于BNL，我們可以通過建立索引轉(zhuǎn)為BKA。對于一些列建立索引代價太大，不好建立索引的情況，我們可以使用臨時表去優(yōu)化。

例如，對于這個語句：

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

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

把表t2中滿足條件的數(shù)據(jù)放在臨時表tmp_t中；為了讓join使用BKA算法，給臨時表tmp_t的字段b加上索引；讓表t1和tmp_t做join操作。

這樣可以大大減少掃描的行數(shù)，提升性能。

總結(jié)

在MySQL中，不管Join使用的是NLJ還是BNL總是應(yīng)該使用小表做驅(qū)動表。更準確地說，**在決定哪個表做驅(qū)動表的時候，應(yīng)該是兩個表按照各自的條件過濾，過濾完成之后，計算參與join的各個字段的總數(shù)據(jù)量，數(shù)據(jù)量小的那個表，就是“小表”，應(yīng)該作為驅(qū)動表。**應(yīng)當盡量避免使用BNL算法，如果確認優(yōu)化器會使用BNL算法，就需要做優(yōu)化。優(yōu)化的常見做法是，給被驅(qū)動表的join字段加上索引，把BNL算法轉(zhuǎn)成BKA算法。對于不好在索引的情況，可以基于臨時表的改進方案，提前過濾出小數(shù)據(jù)添加索引。

到此這篇關(guān)于MySQL中Join的算法(NLJ、BNL、BKA)詳解的文章就介紹到這了,更多相關(guān)MySQL中Join的算法內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！