前引

相信大家 MySQL 都用了很久了，各種 join 查詢天天都在寫，但是 join 查詢到底是怎么查的，怎么寫才是最正確的，今天我就和大家一起學(xué)習(xí)探討一下

索引對(duì) join 查詢的影響

數(shù)據(jù)準(zhǔn)備

假設(shè)有兩張表 t1、t2，兩張表都存在有主鍵索引 id 和索引字段 a，b 字段無索引，然后在 t1 表中插入 100 行數(shù)據(jù)，t2 表中插入 1000 行數(shù)據(jù)進(jìn)行實(shí)驗(yàn)

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)；因?yàn)?join 查詢 MYSQL 優(yōu)化器不一定能按照我們的意愿去執(zhí)行，所以為了分析我們選擇用 STRAIGHT_JOIN 來代替，從而更直觀的進(jìn)行觀察

圖 1

可以看出我們使用了 t1 作為驅(qū)動(dòng)表，t2 作為被驅(qū)動(dòng)表，上圖的 explain 中顯示本次查詢用上了 t2 表的字段 a索引，所以這個(gè)語句的執(zhí)行過程應(yīng)該是下面這樣的：

• 從 t1 表中讀取一行數(shù)據(jù) r
• 從數(shù)據(jù) r中取出字段 a到表 t2 中進(jìn)行匹配
• 取出 t2 表中符合條件的行，和 r組成一行作為結(jié)果集的一部分
• 重復(fù)執(zhí)行步驟 1-3，直到表 t1 循環(huán)數(shù)據(jù)

該過程稱之為 Index Nested-Loop Join，在這個(gè)流程里，驅(qū)動(dòng)表 t1 進(jìn)行了全表掃描，因?yàn)槲覀兘o t1 表插入了 100 行數(shù)據(jù)，所以本次的掃描行數(shù)是 100，而進(jìn)行 join 查詢時(shí)，對(duì)于 t1 表的每一行都需去 t2 表中進(jìn)行查找，走的是索引樹搜索，因?yàn)槲覀儤?gòu)造的數(shù)據(jù)都是一一對(duì)應(yīng)的，所以每次搜索只掃描一行，也就是 t2 表也是總共掃描 100 行，整個(gè)查詢過程掃描的總行數(shù)是 100+100=200 行。

無索引查詢過程

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

圖 2

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

當(dāng)然了這個(gè)查詢結(jié)果還是在我們建的這兩個(gè)都是小表的情況下，如果是數(shù)量級(jí) 10 萬行的表，就需要掃描 100 億行，這就太恐怖了！

了解 Block Nested-Loop Join

Block Nested-Loop Join查詢過程

那么被驅(qū)動(dòng)表上沒有存在索引，這一切都是怎么發(fā)生的呢？

實(shí)際上當(dāng)被驅(qū)動(dòng)表上沒有可用的索引，算法流程是這樣的：

• 把 t1 的數(shù)據(jù)讀取線程內(nèi)存 join_buffer 中，因?yàn)樯鲜鑫覀儗懙氖?select * from，所以相當(dāng)于是把整個(gè) t1 表放入了內(nèi)存;
• 掃描 t2 的過程，實(shí)際上是把 t2 的每一行取出來，跟 join_buffer 中的數(shù)據(jù)去做對(duì)比，滿足 join 條件的，作為結(jié)果集的一部分進(jìn)行返回。

所以結(jié)合圖 2中 Extra 部分說明 Using join buffer 可以發(fā)現(xiàn)這一絲端倪，整個(gè)過程中，對(duì)表 t1 和t2 都做了一次全表掃描，因此掃描的行數(shù)是 100+1000=1100 行，因?yàn)?join_buffer 是以無序數(shù)組的方式組織的，因此對(duì)于表 t2 中每一行，都要做 100 次判斷，總共需要在內(nèi)存中進(jìn)行的判斷次數(shù)是 100*1000=10 萬次，但是因?yàn)檫@ 10 萬次是發(fā)生在內(nèi)存中的所以速度上要快很多，性能也更好。

Join_buffer

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

SHOW VARIABLES LIKE '%join_buffer_size%';

圖 4

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

• 掃描表 t1，順序讀取數(shù)據(jù)行放入 join_buffer 中，直至加載完第 80 行滿了
• 掃描表 t2，把 t2 表中的每一行取出來跟 join_buffer 中的數(shù)據(jù)做對(duì)比，將滿足條件的數(shù)據(jù)作為結(jié)果集的一部分返回
• 清空 join_buffer
• 繼續(xù)掃描表 t1，順序讀取剩余的數(shù)據(jù)行放入 join_buffer 中，執(zhí)行步驟 2

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

所以這就是有時(shí)候 join 查詢很慢，有些大佬會(huì)讓你把 join_buffer_size 調(diào)大的原因。

如何正確的寫出 join 查詢

驅(qū)動(dòng)表的選擇

• 有索引的情況下

在這個(gè) join 語句執(zhí)行過程中，驅(qū)動(dòng)表是走全表掃描，而被驅(qū)動(dòng)表是走樹搜索。

假設(shè)被驅(qū)動(dòng)表的行數(shù)是 M，每次在被驅(qū)動(dòng)表查詢一行數(shù)據(jù)，先要走索引 a，再搜索主鍵索引。每次搜索一棵樹近似復(fù)雜度是以 2為底的 M的對(duì)數(shù)，記為 log2M，所以在被驅(qū)動(dòng)表上查詢一行數(shù)據(jù)的時(shí)間復(fù)雜度是 2*log2M。

假設(shè)驅(qū)動(dòng)表的行數(shù)是 N，執(zhí)行過程就要掃描驅(qū)動(dòng)表 N 行，然后對(duì)于每一行，到被驅(qū)動(dòng)表上 匹配一次。因此整個(gè)執(zhí)行過程，近似復(fù)雜度是 N + N2log2M。顯然，N 對(duì)掃描行數(shù)的影響更大，因此應(yīng)該讓小表來做驅(qū)動(dòng)表。

• 那沒有索引的情況

上述我知道了，因?yàn)?join_buffer 因?yàn)榇嬖谙拗疲圆樵兊倪^程可能存在多次加載 join_buffer，但是判斷的次數(shù)都是 10 萬次，這種情況下應(yīng)該怎么選擇？

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

掃描的行數(shù)就變成了 N+λNM，顯然內(nèi)存的判斷次數(shù)是不受哪個(gè)表作為驅(qū)動(dòng)表而影響的，而考慮到掃描行數(shù)，在 M和 N大小確定的情況下，N 小一些，整個(gè)算是的結(jié)果會(huì)更小，所以應(yīng)該讓小表作為驅(qū)動(dòng)表

總結(jié)：真相大白了，不管是有索引還是無索引參與 join 查詢的情況下都應(yīng)該是使用小表作為驅(qū)動(dòng)表。

什么是小表

還是以上面表 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 行就是那個(gè)相對(duì)較小的表，也就是我們上面說所說的‘小表’。

再看另一組：

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;

這個(gè)例子里，表 t1 和 t2 都是只有 100 行參加 join。 但是，這兩條語句每次查詢放入 join_buffer 中的數(shù)據(jù)是不一樣的: 表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，只需要放入字段 b 的值; 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的話，就需要放入三個(gè)字 段 id、a 和 b。

這里，我們應(yīng)該選擇表 t1 作為驅(qū)動(dòng)表。也就是說在這個(gè)例子里，”只需要一列參與 join 的 表 t1“是那個(gè)相對(duì)小的表。

結(jié)論：

在決定哪個(gè)表做驅(qū)動(dòng)表的時(shí)候，應(yīng)該是兩個(gè)表按照各自的條件過濾，過 濾完成之后，計(jì)算參與 join 的各個(gè)字段的總數(shù)據(jù)量，數(shù)據(jù)量小的那個(gè)表，就是“小表”， 應(yīng)該作為驅(qū)動(dòng)表。

到此這篇關(guān)于MySQL中join查詢的文章就介紹到這了,更多相關(guān)MySQL join查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！