JOIN 的執(zhí)行流程
建表
create table test8 (id int(11) PRIMARY key,a int(11) not null, b int(11) not null,key a(`a`))
delimiter ;;
CREATE PROCEDURE idata()
BEGIN
DECLARE i int;
set i = 1;
WHILE i < 1000 DO
INSERT into test8 values(i,i,i);
SET i = i + 1;
END WHILE;
END;;
delimiter ;
CALL idata();
CREATE table test9 like test8;
INSERT into test9 (select * FROM test8 WHERE id <= 100)
Index Nested-Loop Join
對(duì)于如下 SQL 語(yǔ)句:
select * FROM test9 STRAIGHT_JOIN test8 on test9.a = test8.a;
NOTE:
STRAIGHT_JOIN 可以手動(dòng)指定驅(qū)動(dòng)表和被驅(qū)動(dòng)表赴邻，而不要經(jīng)過(guò)優(yōu)化器的判斷赏壹，有時(shí)候可以用來(lái)優(yōu)化 JOIN 查詢每界，但最好不要那么做制圈，因?yàn)楝F(xiàn)在的優(yōu)化器會(huì)做出合理的判斷剂府。上述 SQL 只是為了便于分析竖慧！
上述SQL的執(zhí)行計(jì)劃如下：
由于被驅(qū)動(dòng)表 test8 的字段 a 上有索引疲吸，join 過(guò)程用上了該索引，所以上述 SQL 的執(zhí)行流程如下：
- 從表 test9 取出一行 D
- 取出D行中的 a 的值到表 test8 中去查找
- 取出 test8 中滿足條件的行狮惜，和 R 組成一行奸远，作為結(jié)果集的一部分
- 重復(fù)執(zhí)行 步驟 1 到步驟 2 既棺，直到取到表 test9 的最后一行
上述流程稱之為 "Index Nested-Loop Join"讽挟，簡(jiǎn)稱 NIJ
在這個(gè)流程中懒叛，對(duì)表 test9 做全表掃描，需要掃描 100 行耽梅，對(duì)于每一行 D薛窥，根據(jù) a 字段去表 test8 查找，由于走的是樹(shù)的搜索過(guò)程眼姐，因此每次搜索都只掃描一行诅迷，也是總共掃描 100 行，所以整個(gè)流程一共掃描 200 行众旗。
假設(shè)驅(qū)動(dòng)表的行數(shù)是 N罢杉，被驅(qū)動(dòng)表的行數(shù) 是 M，那整個(gè)過(guò)程的復(fù)雜度近似是 N + 2 * N * log2M贡歧，顯然 N 的值對(duì)掃描行數(shù)更大些滩租，所以應(yīng)該用小表做驅(qū)動(dòng)表
Simple Nested-Loop Join
對(duì)于如下 SQL 語(yǔ)句
select * FROM test9 STRAIGHT_JOIN test8 on test9.b = test8.b;
NOTE：
b 字段沒(méi)有建立索引
如果繼續(xù)使用 上述的流程，那么這個(gè) SQL 得掃描 100 * 1000 = 10萬(wàn) 行數(shù)據(jù)利朵，如果兩個(gè)表的行數(shù)都比價(jià)大律想，那么這樣速度會(huì)很慢，好在 MySQL 沒(méi)有使用這個(gè)绍弟，而是使用了一個(gè)叫 "Block Nested-Loop Join"
的算法技即，簡(jiǎn)稱 BNL。
Block Nested-Loop Join
被驅(qū)動(dòng)表上沒(méi)有索引樟遣，則執(zhí)行的流程是：
- 把表 test9(驅(qū)動(dòng)表) 的數(shù)據(jù)讀入線程內(nèi)存 join_buffer 而叼，由于是 select * ，因此是把整個(gè)表 test9 放入內(nèi)存中
2.掃描表 test8(被驅(qū)動(dòng)表) 豹悬，把 test8 中的每一行和 join_buffer 中的數(shù)據(jù)做對(duì)比葵陵，滿足 join 條件的作為結(jié)果集的一部分返回。
其執(zhí)行計(jì)劃如下：
雖然也是掃描了 100 * 1000 = 10 萬(wàn) 行屿衅，但是這十萬(wàn)次判斷是在內(nèi)存中進(jìn)行埃难，速度上會(huì)快很多，性能也會(huì)更好涤久。
在這種情況下涡尘，應(yīng)該選擇哪個(gè)表作為驅(qū)動(dòng)表？
假設(shè)小表的行數(shù)是 N响迂，大表的行數(shù)是 M考抄，那么在這個(gè)算法中：
1.兩個(gè)表都要做一次全表掃描，掃描行數(shù)是 M + N
2.在內(nèi)存中判斷次數(shù)是 M * N
因此不論誰(shuí)做驅(qū)動(dòng)表都是一樣的
如果 join_buffer 一次放不下驅(qū)動(dòng)表的數(shù)據(jù)蔗彤，則需要驅(qū)動(dòng)表的數(shù)據(jù)分段放進(jìn) join_buffer 川梅，則流程是：
1.取驅(qū)動(dòng)表的一部分?jǐn)?shù)據(jù)放入 join_buffer疯兼，直至 join_buffer 放不了
2.掃描被驅(qū)動(dòng)表的每一行數(shù)據(jù)，跟 join_buffer 中的數(shù)據(jù)做對(duì)比贫途，滿足
join 條件的數(shù)據(jù)作為結(jié)果集的一部分返回
3.清空 join_buffer
4.繼續(xù)讀取驅(qū)動(dòng)表剩下的數(shù)據(jù)吧彪，重復(fù)步驟 1 到步驟 3，一直驅(qū)動(dòng)表的數(shù)據(jù)被掃描完
若驅(qū)動(dòng)表的行數(shù)是 N丢早，需要分 K 段才能掃描完姨裸，被驅(qū)動(dòng)表的行數(shù)是 M
則掃描的行數(shù)是 N + K * M （N 越大，K 越大） ==> N + λ * N * M( 0 < λ < 1 )
內(nèi)存判斷次數(shù)還是 N + M
由此可見(jiàn)在 M 怨酝，N 大小固定的情況下傀缩，N越小，其掃描行數(shù)越小农猬。
綜上所述赡艰，
1.如果是 NLJ 算法，應(yīng)該選擇小表做驅(qū)動(dòng)
2.如果是 BNL 算法
- 如果 join_buffer 足夠大斤葱，是一樣的
- 如果 join_buffer 不是足夠大慷垮，應(yīng)該選擇小表做驅(qū)動(dòng)表
所以不管如何，都應(yīng)該選擇小表作為驅(qū)動(dòng)表
[備注] 參考了 極客時(shí)間 的 MySQL實(shí)戰(zhàn)45講苦掘。鏈接如下：
https://time.geekbang.org/column/article/79700
