MySQL實戰教學之Join語句執行流程

2023-03-07 06:03:25

Join語句執行流程

Hi，我是阿昌，今天學習記錄的是關於Join語句執行流程的內容。

在實際生產中，關於 join 語句使用的問題，一般會集中在以下兩類：

不讓使用 join，使用 join 有什麼問題呢？
如果有兩個大小不同的表做 join，應該用哪個表做驅動表呢？

建立兩個表 t1 和 t2 來說明。

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

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

可以看到，這兩個表都有一個主鍵索引 id 和一個索引 a，欄位 b 上無索引。

儲存過程 idata() 往表 t2 裡插入了 1000 行資料，在表 t1 裡插入的是 100 行資料。

一、Index Nested-Loop Join

如果直接使用 join 語句，MySQL 優化器可能會選擇表 t1 或 t2 作為驅動表，這樣會影響分析 SQL 語句的執行過程。

所以，為了便於分析執行過程中的效能問題，改用 straight_join 讓 MySQL 使用固定的連線方式執行查詢，這樣優化器只會按照指定的方式去 join。

來看一下這個語句：

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

在這個語句裡，t1 是驅動表，t2 是被驅動表。

現在，來看一下這條語句的 explain 結果。

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

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

這個過程是先遍歷表 t1，然後根據從表 t1 中取出的每行資料中的 a 值，去表 t2 中查詢滿足條件的記錄。

在形式上，這個過程就跟寫程式時的巢狀查詢類似，並且可以用上被驅動表的索引，所以稱之為“Index Nested-Loop Join”，簡稱 NLJ。它對應的流程圖如下所示：

在這個流程裡：

對驅動表 t1 做了全表掃描，這個過程需要掃描 100 行；而對於每一行 R，根據 a 欄位去表 t2 查詢，走的是樹搜尋過程。由於構造的資料都是一一對應的，因此每次的搜尋過程都只掃描一行，也是總共掃描 100 行；所以，整個執行流程，總掃描行數是 200。

能不能使用 join?

假設不使用 join，那就只能用單表查詢。

看看上面這條語句的需求，用單表查詢怎麼實現。

執行select * from t1，查出表 t1 的所有資料，這裡有 100 行；
迴圈遍歷這 100 行資料：
- 從每一行 R 取出欄位 a 的值 $R.a；
- 執行select * from t2 where a=$R.a；
- 把返回的結果和 R 構成結果集的一行。

可以看到，在這個查詢過程，也是掃描了 200 行，但是總共執行了 101 條語句，比直接 join 多了 100 次互動。

除此之外，使用者端還要自己拼接 SQL 語句和結果。

顯然，這麼做還不如直接 join 好。

怎麼選擇驅動表？

在這個 join 語句執行過程中，==驅動表是走全表掃描，而被驅動表是走樹搜尋。==假設被驅動表的行數是 M。

每次在被驅動表查一行資料，要先搜尋索引 a，再搜尋主鍵索引。

每次搜尋一棵樹近似複雜度是以 2 為底的 M 的對數，記為 log2M，所以在被驅動表上查一行的時間複雜度是 2*log2M。

假設驅動表的行數是 N，執行過程就要掃描驅動表 N 行，然後對於每一行，到被驅動表上匹配一次。

因此整個執行過程，近似複雜度是 N + N*2*log2M。

顯然，N 對掃描行數的影響更大，因此應該讓小表來做驅動表。

如果沒覺得這個影響有那麼“顯然”，可以這麼理解：
N 擴大 1000 倍的話，掃描行數就會擴大 1000 倍；
而 M 擴大 1000 倍，掃描行數擴大不到 10 倍。

小結一下，通過上面的分析得到了兩個結論：

使用 join 語句，效能比強行拆成多個單表執行 SQL 語句的效能要好；
如果使用 join 語句的話，需要讓小表做驅動表。

但是，需要注意，這個結論的前提是“可以使用被驅動表的索引”。

二、Simple Nested-Loop Join

再看看被驅動表用不上索引的情況。

現在，把 SQL 語句改成這樣：

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

由於表 t2 的欄位 b 上沒有索引，因此再用圖 2 的執行流程時，每次到 t2 去匹配的時候，就要做一次全表掃描。

你可以先設想一下這個問題，繼續使用圖 2 的演演算法，是不是可以得到正確的結果呢？

如果只看結果的話，這個演演算法是正確的，而且這個演演算法也有一個名字，叫做“Simple Nested-Loop Join”。

但是，這樣算來，這個 SQL 請求就要掃描表 t2 多達 100 次，總共掃描 100*1000=10 萬行。

這還只是兩個小表，如果 t1 和 t2 都是 10 萬行的表（當然了，這也還是屬於小表的範圍），就要掃描 100 億行，這個演演算法看上去太“笨重”了。

三、Block Nested-Loop Join

當然，MySQL 也沒有使用這個 Simple Nested-Loop Join 演演算法，而是使用了另一個叫作“Block Nested-Loop Join”的演演算法，簡稱 BNL。

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

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

這個過程的流程圖如下：

對應地，這條 SQL 語句的 explain 結果如下所示：

可以看到，在這個過程中，對錶 t1 和 t2 都做了一次全表掃描，因此總的掃描行數是 1100。

由於 join_buffer 是以無序陣列的方式組織的，因此對錶 t2 中的每一行，都要做 100 次判斷，總共需要在記憶體中做的判斷次數是：100*1000=10 萬次。

前面我們說過，如果使用 Simple Nested-Loop Join 演演算法進行查詢，掃描行數也是 10 萬行。因此，從時間複雜度上來說，這兩個演演算法是一樣的。但是，Block Nested-Loop Join 演演算法的這 10 萬次判斷是記憶體操作，速度上會快很多，效能也更好。

在這種情況下，應該選擇哪個表做驅動表。

假設小表的行數是 N，大表的行數是 M，那麼在這個演演算法裡：

兩個表都做一次全表掃描，所以總的掃描行數是 M+N；
記憶體中的判斷次數是 M*N。

可以看到，調換這兩個算式中的 M 和 N 沒差別，因此這時候選擇大表還是小表做驅動表，執行耗時是一樣的。

這個例子裡表 t1 才 100 行，要是表 t1 是一個大表，join_buffer 放不下怎麼辦呢？

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

join_buffer_size 改成 1200，再執行：

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

執行過程就變成了：

掃描表 t1，順序讀取資料行放入 join_buffer 中，放完第 88 行 join_buffer 滿了，繼續第 2 步；
掃描表 t2，把 t2 中的每一行取出來，跟 join_buffer 中的資料做對比，滿足 join 條件的，作為結果集的一部分返回；
清空 join_buffer；
繼續掃描表 t1，順序讀取最後的 12 行資料放入 join_buffer 中，繼續執行第 2 步。

執行流程圖也就變成這樣：

圖中的步驟 4 和 5，表示清空 join_buffer 再複用。

這個流程才體現出了這個演演算法名字中“Block”的由來，表示“分塊去 join”。

可以看到，這時候由於表 t1 被分成了兩次放入 join_buffer 中，導致表 t2 會被掃描兩次。

雖然分成兩次放入 join_buffer，但是判斷等值條件的次數還是不變的，依然是 (88+12)*1000=10 萬次。

在這種情況下驅動表的選擇問題。

假設，驅動表的資料行數是 N，需要分 K 段才能完成演演算法流程，被驅動表的資料行數是 M。

注意，這裡的 K 不是常數，N 越大 K 就會越大，因此把 K 表示為λ*N，顯然λ的取值範圍是 (0,1)。

所以，在這個演演算法的執行過程中：

掃描行數是 N+λNM；
記憶體判斷 N*M 次。

顯然，記憶體判斷次數是不受選擇哪個表作為驅動表影響的。

而考慮到掃描行數，在 M 和 N 大小確定的情況下，N 小一些，整個算式的結果會更小。所以結論是，應該讓小表當驅動表。

在 N+λNM 這個式子裡，λ才是影響掃描行數的關鍵因素，這個值越小越好。

剛剛我們說了 N 越大，分段數 K 越大。那麼，N 固定的時候，什麼引數會影響 K 的大小呢？（也就是λ的大小）答案是 join_buffer_size。

join_buffer_size 越大，一次可以放入的行越多，分成的段數也就越少，對被驅動表的全表掃描次數就越少。

如果你的 join 語句很慢，就把 join_buffer_size 改大。

第一個問題：能不能使用 join 語句？

如果可以使用 Index Nested-Loop Join 演演算法，也就是說可以用上被驅動表上的索引，其實是沒問題的；
如果使用 Block Nested-Loop Join 演演算法，掃描行數就會過多。尤其是在大表上的 join 操作，這樣可能要掃描被驅動表很多次，會佔用大量的系統資源。所以這種 join 儘量不要用。

所以在判斷要不要使用 join 語句時，就是看 explain 結果裡面，Extra 欄位裡面有沒有出現“Block Nested Loop”字樣。

第二個問題是：如果要使用 join，應該選擇大表做驅動表還是選擇小表做驅動表？

如果是 Index Nested-Loop Join 演演算法，應該選擇小表做驅動表；
如果是 Block Nested-Loop Join 演演算法：
- 在 join_buffer_size 足夠大的時候，是一樣的；
- 在 join_buffer_size 不夠大的時候（這種情況更常見），應該選擇小表做驅動表。

所以，這個問題的結論就是，總是應該使用小表做驅動表。

當然了，這裡我需要說明下，什麼叫作“小表”。

如果我在語句的 where 條件加上 t2.id<=50 這個限定條件，再來看下這兩條語句：

select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

注意，為了讓兩條語句的被驅動表都用不上索引，所以 join 欄位都使用了沒有索引的欄位 b。

但如果是用第二個語句的話，join_buffer 只需要放入 t2 的前 50 行，顯然是更好的。

所以這裡，“t2 的前 50 行”是那個相對小的表，也就是“小表”。

再來看另外一組例子：

select t1.b,t2.* from  t1  straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from  t2  straight_join t1 on (t1.b=t2.b) where t2.id<=100;

這個例子裡，表 t1 和 t2 都是隻有 100 行參加 join。

但是，這兩條語句每次查詢放入 join_buffer 中的資料是不一樣的：

表 t1 只查欄位 b，因此如果把 t1 放到 join_buffer 中，則 join_buffer 中只需要放入 b 的值；
表 t2 需要查所有的欄位，因此如果把表 t2 放到 join_buffer 中的話，就需要放入三個欄位 id、a 和 b。

應該選擇表 t1 作為驅動表。也就是說在這個例子裡，“只需要一列參與 join 的表 t1”是那個相對小的表。

所以，更準確地說，在決定哪個表做驅動表的時候，應該是兩個表按照各自的條件過濾，過濾完成之後，計算參與 join 的各個欄位的總資料量，資料量小的那個表，就是“小表”，應該作為驅動表。

四、總結

如果可以使用被驅動表的索引，join 語句還是有其優勢的；
不能使用被驅動表的索引，只能使用 Block Nested-Loop Join 演演算法，這樣的語句就儘量不要使用；
在使用 join 的時候，應該讓小表做驅動表。

使用 Block Nested-Loop Join 演演算法，可能會因為 join_buffer 不夠大，需要對被驅動表做多次全表掃描。如果被驅動表是一個大表，並且是一個冷資料表，除了查詢過程中可能會導致 IO 壓力大以外，覺得對這個 MySQL 服務還有什麼更嚴重的影響嗎？

如果被驅動表是一個大表(因為不論用BNL還是ILJ演演算法) 都是優先讓被參與join的總的欄位量較大的一張表作為一個被驅動表。

但是由於關聯的時候被驅動表的資料會頻繁被走索引數，所以根據MYSQL 的LRU演演算法其實冷資料也會被提到連結串列的前部 ,造成冷資料的前移，其餘業務資料被淘汰。造成記憶體命中率降低。請求響應變慢,業務可能造成阻塞。

到此這篇關於Join語句執行流程 -MySQL實戰的文章就介紹到這了,更多相關mysql Join語句內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！