MySQL中join查詢實例代碼分析

這篇“MySQL中join查詢實例代碼分析”文章的知識點大部分人都不太理解，所以小編給大家總結了以下內容，內容詳細，步驟清晰，具有一定的借鑒價值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“MySQL中join查詢實例代碼分析”文章吧。

索引對 join 查詢的影響

數據準備

假設有兩張表 t1、t2，兩張表都存在有主鍵索引 id 和索引字段 a，b 字段無索引，然后在 t1 表中插入 100 行數據，t2 表中插入 1000 行數據進行實驗

CREATE TABLE `t2` (
 `id` int NOT NULL,
 `a` int DEFAULT NULL,
 `b` int DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `t2_a_index` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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;
CALL **idata**();
CREATE TABLE t1 LIKE t2;
INSERT INTO t1 (SELECT * FROM t2 WHERE id <= 100);

有索引查詢過程

我們使用查詢 SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a)；因為 join 查詢 MYSQL 優化器不一定能按照我們的意愿去執行，所以為了分析我們選擇用 STRAIGHT_JOIN 來代替，從而更直觀的進行觀察

圖 1

可以看出我們使用了 t1 作為驅動表，t2 作為被驅動表，上圖的 explain 中顯示本次查詢用上了 t2 表的字段 a索引，所以這個語句的執行過程應該是下面這樣的：

• 從 t1 表中讀取一行數據 r

• 從數據 r中取出字段 a到表 t2 中進行匹配

• 取出 t2 表中符合條件的行，和 r組成一行作為結果集的一部分

• 重復執行步驟 1-3，直到表 t1 循環數據

該過程稱之為 Index Nested-Loop Join，在這個流程里，驅動表 t1 進行了全表掃描，因為我們給 t1 表插入了 100 行數據，所以本次的掃描行數是 100，而進行 join 查詢時，對于 t1 表的每一行都需去 t2 表中進行查找，走的是索引樹搜索，因為我們構造的數據都是一一對應的，所以每次搜索只掃描一行，也就是 t2 表也是總共掃描 100 行，整個查詢過程掃描的總行數是 100+100=200 行。

無索引查詢過程

SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a = t2.b);

圖 2

可以看出由于 t2 表字段 B上沒有索引，所以按照上述 SQL 執行時每次從 t1 去匹配 t2 的時候都要做一次全表掃描，這樣算下來掃描 t2 多大 100 次，總掃描次數就是 100*1000 = 10 萬行。

當然了這個查詢結果還是在我們建的這兩個都是小表的情況下，如果是數量級 10 萬行的表，就需要掃描 100 億行，這就太恐怖了！

2. 了解 Block Nested-Loop Join

Block Nested-Loop Join查詢過程

那么被驅動表上沒有存在索引，這一切都是怎么發生的呢？

實際上當被驅動表上沒有可用的索引，算法流程是這樣的：

• 把 t1 的數據讀取線程內存 join_buffer 中，因為上述我們寫的是 select * from，所以相當于是把整個 t1 表放入了內存;

• 掃描 t2 的過程，實際上是把 t2 的每一行取出來，跟 join_buffer 中的數據去做對比，滿足 join 條件的，作為結果集的一部分進行返回。

所以結合圖 2中 Extra 部分說明 Using join buffer 可以發現這一絲端倪，整個過程中，對表 t1 和t2 都做了一次全表掃描，因此掃描的行數是 100+1000=1100 行，因為 join_buffer 是以無序數組的方式組織的，因此對于表 t2 中每一行，都要做 100 次判斷，總共需要在內存中進行的判斷次數是 100*1000=10 萬次，但是因為這 10 萬次是發生在內存中的所以速度上要快很多，性能也更好。

Join_buffer

根據上述已經知道了，沒有索引的情況下 MySQL 是將數據讀取內存進行循環判斷的，那么這個內存肯定不是無限制讓你使用的，這時我們就需要用到一個參數 join_buffer_size，該值默認大小 256k，如下圖：

SHOW VARIABLES LIKE '%join_buffer_size%';

圖 4

假如查詢的數據過大一次加載不完，只能夠加載部分數據（80 條），那么查詢的過程就變成了下面這樣

• 掃描表 t1，順序讀取數據行放入 join_buffer 中，直至加載完第 80 行滿了

• 掃描表 t2，把 t2 表中的每一行取出來跟 join_buffer 中的數據做對比，將滿足條件的數據作為結果集的一部分返回

• 清空 join_buffer

• 繼續掃描表 t1，順序讀取剩余的數據行放入 join_buffer 中，執行步驟 2

這個流程體現了算法名稱中 Block 的由來，分塊 join，可以看出雖然查詢過程中 t1 被分成了兩次放入 join_buffer 中，導致 t2 表被掃描了 2次，但是判斷等值條件的次數還是不變的，依然是（80+20）*1000=10 萬次。

所以這就是有時候 join 查詢很慢，有些大佬會讓你把 join_buffer_size 調大的原因。

如何正確的寫出 join 查詢

驅動表的選擇

• 有索引的情況下

在這個 join 語句執行過程中，驅動表是走全表掃描，而被驅動表是走樹搜索。

假設被驅動表的行數是 M，每次在被驅動表查詢一行數據，先要走索引 a，再搜索主鍵索引。每次搜索一棵樹近似復雜度是以 2為底的 M的對數，記為 log2M，所以在被驅動表上查詢一行數據的時間復雜度是 2*log2M。

假設驅動表的行數是 N，執行過程就要掃描驅動表 N 行，然后對于每一行，到被驅動表上 匹配一次。因此整個執行過程，近似復雜度是 N + N2log2M。顯然，N 對掃描行數的影響更大，因此應該讓小表來做驅動表。

• 那沒有索引的情況

上述我知道了，因為 join_buffer 因為存在限制，所以查詢的過程可能存在多次加載 join_buffer，但是判斷的次數都是 10 萬次，這種情況下應該怎么選擇？

假設，驅動表的數據行數是 N，需要分 K 段才能完成算法流程，被驅動表的數據行數是 M。這里的 K不是常數，N 越大 K就越大，因此把 K 表示為λ*N，顯然λ的取值范圍 是 (0,1)。

掃描的行數就變成了 N+λNM，顯然內存的判斷次數是不受哪個表作為驅動表而影響的，而考慮到掃描行數，在 M和 N大小確定的情況下，N 小一些，整個算是的結果會更小，所以應該讓小表作為驅動表

總結：真相大白了，不管是有索引還是無索引參與 join 查詢的情況下都應該是使用小表作為驅動表。

什么是小表

還是以上面表 t1 和表 t2 為例子：

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;

上面這兩條 SQL 我們加上了條件 t2.id <= 50，我們使用了字段 b，所以兩條 SQL 都沒有用上索引，但是第二條 SQL 可以看出 join_buffer 只需要放入前 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 中，只需要放入字段 b 的值; 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的話，就需要放入三個字 段 id、a 和 b。

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

以上就是關于“MySQL中join查詢實例代碼分析”這篇文章的內容，相信大家都有了一定的了解，希望小編分享的內容對大家有幫助，若想了解更多相關的知識內容，請關注億速云行業資訊頻道。