從MySQL JOIN 算法角度看如何優(yōu)化SQL
一、前言
在做MySQL的SQL優(yōu)化時(shí)，如果只涉及到單表查詢，那么大部分慢SQL都只需從索引上入手優(yōu)化即可，通過(guò)添加合適的索引來(lái)消除全表掃描或者排序操作，執(zhí)行效果，大概率能實(shí)現(xiàn)質(zhì)的飛躍。
然而，在實(shí)際生產(chǎn)中，除了單表查詢，更多的是多個(gè)表的聯(lián)合查詢，這樣的查詢通常是慢SQL的重災(zāi)區(qū)，查詢速度慢，且使用服務(wù)器資源較多，如果能將這類SQL優(yōu)化掉，那必將大大減輕數(shù)據(jù)庫(kù)服務(wù)器壓力。現(xiàn)在，咱就通過(guò)多表關(guān)聯(lián)內(nèi)部數(shù)據(jù)操作的角度，看看如何進(jìn)行SQL優(yōu)化。
二、準(zhǔn)備工作
現(xiàn)在線上環(huán)境大部分使用的都是MySQL 5.7.x版本，那咱就以5.7版本為主，適當(dāng)延伸MySQL 8.0版本為輔進(jìn)行講解測(cè)試。
創(chuàng)建測(cè)試表：
# 創(chuàng)建兩個(gè)表結(jié)構(gòu)一模一樣的表：t1、t2
create table t1(
id int not null auto_increment,
a int,
b int,
c int,
primary key(id),
key idx_a(a)
);
create table t2 like t1;構(gòu)造測(cè)試數(shù)據(jù)：
# 創(chuàng)建2個(gè)存儲(chǔ)過(guò)程用于構(gòu)造測(cè)試數(shù)據(jù)
# 構(gòu)造t1表數(shù)據(jù)的存儲(chǔ)過(guò)程，數(shù)據(jù)為3的整除數(shù)，1000條
delimiter //
create procedure t1_proc()
begin
declare i int default 1;
while (i<=3000) do
if (i%3) = 0 then
insert into t1(a,b,c) values(i, i, i);
end if;
set i=i+1;
end while;
end //
delimiter ;
# 構(gòu)造t2表數(shù)據(jù)的存儲(chǔ)過(guò)程，數(shù)據(jù)為2的整除數(shù)，100000條
delimiter //
create procedure t2_proc()
begin
declare i int default 1;
while (i<=200000) do
if (i%2) = 0 then
insert into t2(a,b,c) values(i, i, i);
end if;
set i=i+1;
end while;
end //
delimiter ;
# 調(diào)用存儲(chǔ)過(guò)程，生成測(cè)試數(shù)據(jù)
call t1_proc();
call t2_proc();
# 刪除存儲(chǔ)過(guò)程
drop procedure t1_proc;
drop procedure t2_proc;數(shù)據(jù)樣例：
[5.7.37-log localhost:mysql.sock]>select * from t1 limit 5;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 3 | 3 | 3 |
| 2 | 6 | 6 | 6 |
| 3 | 9 | 9 | 9 |
| 4 | 12 | 12 | 12 |
| 5 | 15 | 15 | 15 |
+----+------+------+------+
5 rows in set (0.00 sec)
[5.7.37-log localhost:mysql.sock]>select * from t2 limit 5;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 2 | 2 | 2 |
| 2 | 4 | 4 | 4 |
| 3 | 6 | 6 | 6 |
| 4 | 8 | 8 | 8 |
| 5 | 10 | 10 | 10 |
+----+------+------+------+
5 rows in set (0.00 sec)三、MySQL JOIN算法
MySQL對(duì)兩表關(guān)聯(lián)，支持多種Join算法，咱就以下面這個(gè)SQL為例，深入探討一下。
測(cè)試SQL：
select * from t1 join t2 on t1.b=t2.b;1、Simple Nested-Loop Join
設(shè)想一下，如果兩表關(guān)聯(lián)，在沒(méi)有任何干預(yù)的情況下，他像不像下面這個(gè)偽代碼的嵌套循環(huán)：
for row_1 in t1: # 循環(huán)1000次
for row_2 in t2: # 對(duì)應(yīng)每個(gè)外層循環(huán)10w次
if row_1.b == row_2.b:
do something從上面的偽代碼中，我們可以看到，其就是簡(jiǎn)單粗暴的嵌套循環(huán)，我們將其稱為 Simple Nested-Loop Join。回到數(shù)據(jù)庫(kù)層面，在測(cè)試SQL兩個(gè)表關(guān)聯(lián)的過(guò)程中，t1表中的每一行數(shù)據(jù)，都會(huì)觸發(fā)掃描一次t2表的數(shù)據(jù)，然后進(jìn)行數(shù)據(jù)匹配?？偟膩?lái)講就是，因?yàn)閠1表有1000行數(shù)據(jù)，所以t2表會(huì)被掃描1000次，并進(jìn)行1000 * 10w = 1億次數(shù)據(jù)比較。
圖片
很顯然，如果使用這種方式，當(dāng) t2 表足夠大時(shí)，反復(fù)掃描數(shù)據(jù)的過(guò)程中，磁盤(pán)必然會(huì)被拉爆，服務(wù)器性能會(huì)急劇下降。像MySQL這樣優(yōu)秀的產(chǎn)品，必然會(huì)想方設(shè)法的避免這種情況的發(fā)生。
2、Block Nested-Loop Join
緊接上面所說(shuō)，既然 Simple Nested-Loop Join最大的弊端是被驅(qū)動(dòng)表被反復(fù)掃描，那是不是可以從這方面入手，減少被驅(qū)動(dòng)表的掃描次數(shù)，以達(dá)到優(yōu)化目的。咱繼續(xù)往下看，看他是怎么實(shí)現(xiàn)的。
一般情況下，兩表關(guān)聯(lián)，MySQL都會(huì)將結(jié)果集?。ㄖ父鶕?jù)條件過(guò)濾后）的表做驅(qū)動(dòng)表，結(jié)果集大的表當(dāng)被驅(qū)動(dòng)表，那是不是可以嘗試一下，把驅(qū)動(dòng)表的結(jié)果集放到內(nèi)存中（Join Buffer），然后一次性掃描被驅(qū)動(dòng)表的所有數(shù)據(jù)，反過(guò)來(lái)與Join Buffer中的驅(qū)動(dòng)表結(jié)果集進(jìn)行比較。這方式，驅(qū)動(dòng)表和被驅(qū)動(dòng)表都只掃描一次，但在內(nèi)存中進(jìn)行數(shù)據(jù)比較的次數(shù)依然為 10w * 1000 = 1億次。很顯然，這方式，相對(duì)于Simple Nested-Loop Join而言，優(yōu)勢(shì)非常明顯，MySQL管這個(gè)叫Block Nested-Loop Join。
圖片
聰明的你，是不是在想：如果驅(qū)動(dòng)表t1的結(jié)果集，無(wú)法一次性全部存放到Join Buffer內(nèi)存中時(shí)，怎么辦？
Join Buffer 的大小由參數(shù) join_buffer_size 控制，默認(rèn)為256K。在使用Join Buffer時(shí)，如果無(wú)法一次性存放所有結(jié)果集，他會(huì)分多次進(jìn)行，比如：
1）讀取驅(qū)動(dòng)表t1的數(shù)據(jù)，存放到Join Buffer中，假設(shè)，存放400條后，Join Buffer滿了，停止讀取
2）讀取被驅(qū)動(dòng)表t2的數(shù)據(jù)，每一行數(shù)據(jù)都與Join Buffer中的數(shù)據(jù)進(jìn)行比較，并返回符合條件的結(jié)果集
3）清空J(rèn)oin Buffer
4）繼續(xù)讀取驅(qū)動(dòng)表t1的數(shù)據(jù)，將401-800的數(shù)據(jù)存放到Join Buffer，直到存滿
5）...... 繼續(xù)重復(fù)相似的動(dòng)作，直到所有數(shù)據(jù)都比對(duì)完
圖片
在上述假設(shè)情況下，因Join Buffer大小限制的原因，被驅(qū)動(dòng)表 t2 被掃描了3次?？偟膩?lái)講，雖然不算完美，但顯然比使用Simple Nested-Loop Join的方式容易接受多了。也就是說(shuō)，MySQL在經(jīng)過(guò)對(duì)表鏈接進(jìn)行優(yōu)化后，就不會(huì)再出現(xiàn)使用Simple Nested-Loop Join的情況了。
執(zhí)行計(jì)劃：
[5.7.37-log localhost:mysql.sock]>explain select * from t1 join t2 on t1.b=t2.b\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 100256
filtered: 10.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)3、Hash Join
雖然經(jīng)過(guò)MySQL優(yōu)化后的 Block Nested-Loop Join 算是改進(jìn)了不少，但是，對(duì)各位程序員大拿而言，必然是一眼就看出了還有改進(jìn)的余地。
Block Nested-Loop Join 在將驅(qū)動(dòng)表結(jié)果集存放到Join Buffer后，被驅(qū)動(dòng)表的數(shù)據(jù)與其比對(duì)時(shí)，被驅(qū)動(dòng)表的每一行數(shù)據(jù)，都要與Join Buffer中所有數(shù)據(jù)進(jìn)行比對(duì)，才能得出匹配的結(jié)果。這像不像對(duì)MySQL實(shí)體表進(jìn)行條件查詢時(shí)，進(jìn)行了全表掃描的操作一樣。這情況，如果給條件列加個(gè)索引，查詢速度是不是要瞬間起飛。
想法很好，但很不幸，MySQL 5.7.x 版本不支持；但也很慶幸，MySQL 8.0版本實(shí)現(xiàn)了，他會(huì)根據(jù)驅(qū)動(dòng)表結(jié)果集，將關(guān)聯(lián)列映射為哈希值后鍵創(chuàng)建哈希表，被驅(qū)動(dòng)表的數(shù)據(jù)在與哈希表進(jìn)行比較時(shí)，就大大降低了比較次數(shù)，這也達(dá)到了優(yōu)化的目的，我們管其叫Hash Join。
圖片
咱看看其執(zhí)行計(jì)劃：
[8.0.27 127.0.0.1:3380]>explain select * from t1 join t2 on t1.b=t2.b\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 100400
filtered: 10.00
Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)咱再來(lái)對(duì)比一下Block Nested-Loop Join和Hash Join的執(zhí)行速度：
[5.7.37-log localhost:mysql.sock]>select * from t1 join t2 on t1.b=t2.b;
......
+------+------+------+------+------+------+
500 rows in set (4.90 sec)
[8.0.27 127.0.0.1:3380]>select * from t1 join t2 on t1.b=t2.b;
......
+------+------+------+------+------+------+
500 rows in set (0.02 sec)從執(zhí)行邏輯和執(zhí)行結(jié)果上，都印證了Hash Join必然會(huì)比Block Nested-Loop Join要好。所以，在MySQL 8.0版本，Block Nested-Loop Join將不復(fù)存在，所有原先使用其算法的表關(guān)聯(lián)SQL，最終都會(huì)被優(yōu)化成選擇Hash Join進(jìn)行表關(guān)聯(lián)。
4、Index Nested-Loop Join
前面提到的 Block Nested-Loop Join 和 Hash Join，都是MySQL自己內(nèi)部實(shí)現(xiàn)的優(yōu)化，如果沒(méi)有其他更好的算法，那么基于這兩種算法基礎(chǔ)上的表關(guān)聯(lián)慢SQL，人為干預(yù)改進(jìn)的可能性，是不是就微無(wú)其微了。
我們仔細(xì)分析一下前面這兩種算法的特點(diǎn)，Block Nested-Loop Join 的改進(jìn)是降低了表掃描次數(shù)， Hash Join的改進(jìn)是降低了數(shù)據(jù)對(duì)比的次數(shù)，但他兩，依然有一個(gè)致命的共同點(diǎn)，如果被驅(qū)動(dòng)表足夠大（大表）時(shí)，比如有N億數(shù)據(jù)量，那么，哪怕掃描一次被驅(qū)動(dòng)表，也會(huì)引起數(shù)據(jù)庫(kù)性能急劇下降。
知道了問(wèn)題在哪，自然就有了優(yōu)化的方向。設(shè)想一下，如果被驅(qū)動(dòng)表的關(guān)聯(lián)列，像Hash Join中的哈希表一樣，存在索引，會(huì)是個(gè)什么情況呢？
圖片
驅(qū)動(dòng)表中的每一行記錄，都可以通過(guò)被驅(qū)動(dòng)表的索引列，進(jìn)行索引查找（與關(guān)聯(lián)列有關(guān)，可以是主鍵，也可以是二級(jí)索引），這瞬間就解決了被驅(qū)動(dòng)表被掃描的問(wèn)題。其本質(zhì)，和單表查詢中，通過(guò)建立合適索引的方式進(jìn)行優(yōu)化，是不是很相似。哪怕驅(qū)動(dòng)表再大，如果索引列每個(gè)鍵值對(duì)應(yīng)的數(shù)據(jù)量不大，那么索引查找速度依然可以快到起飛，這算法就叫 Index Nested-Loop Join。
先前咱兩個(gè)測(cè)試表中，a列和b列數(shù)據(jù)是一樣的，a列有索引，b列無(wú)索引，所以，咱將測(cè)試SQL變通一下
select * from t1 join t2 on t1.b=t2.b;
# 替換為
select * from t1 join t2 on t1.b=t2.a;執(zhí)行計(jì)劃：
[5.7.37-log localhost:mysql.sock]>explain select * from t1 join t2 on t1.b=t2.a\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: idx_a
key: idx_a
key_len: 5
ref: db1.t1.b
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)執(zhí)行速度：
[5.7.37-log localhost:mysql.sock]>select * from t1 join t2 on t1.b=t2.a;
......
+------+------+------+------+------+------+
500 rows in set (0.01 sec)你是不是在疑惑，看這執(zhí)行速度，和Hash Join差別也不大，那是因?yàn)樵鄣谋或?qū)動(dòng)表t2數(shù)據(jù)量太少，隨著測(cè)試數(shù)據(jù)量的增大，差距會(huì)越來(lái)越明顯。
四、優(yōu)化思路
前面的測(cè)試SQL，相對(duì)來(lái)講，簡(jiǎn)化的有點(diǎn)過(guò)于簡(jiǎn)單了，實(shí)際應(yīng)用中，必然會(huì)有一大堆查詢條件跟在其后，那這一堆查詢條件，在進(jìn)行SQL優(yōu)化時(shí)，會(huì)不會(huì)對(duì)你造成干擾呢？
1、初始SQL
測(cè)試SQL做個(gè)變化，讓其他稍微貼近實(shí)際情況：
select *
from t1 join t2 on t1.b = t2.b
where
t1.c in (6, 12, 18, 24, 30)
and t2.c in (6, 12, 18, 24, 30);執(zhí)行計(jì)劃：
[5.7.37-log localhost:mysql.sock]>explain select *
-> from t1 join t2 on t1.b = t2.b
-> where
-> t1.c in (6, 12, 18, 24, 30)
-> and t2.c in (6, 12, 18, 24, 30)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 50.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 100345
filtered: 5.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)從上面的執(zhí)行計(jì)劃可以看到，t1表較小為驅(qū)動(dòng)表，t2表較大為被驅(qū)動(dòng)表。咱一步一步分析，暫時(shí)剔除t2表，先看t1表是否有優(yōu)化的空間，其現(xiàn)在是全表掃描，并通過(guò)t1.c列進(jìn)行數(shù)據(jù)過(guò)濾。單表查詢，如果查詢條件列有索引，必然會(huì)加快查詢速度對(duì)吧。
2、SQL優(yōu)化1
t1表中，a、b、c列數(shù)據(jù)是一樣的，a列有索引，所以咱不額外創(chuàng)建索引了，直接使用a列替代c列，重寫(xiě)測(cè)試SQL：
select *
from t1 join t2 on t1.b = t2.b
where
t1.a in (6, 12, 18, 24, 30)
and t2.c in (6, 12, 18, 24, 30);查看新的執(zhí)行計(jì)劃：
[5.7.37-log localhost:mysql.sock]>explain select *
-> from t1 join t2 on t1.b = t2.b
-> where
-> t1.a in (6, 12, 18, 24, 30)
-> and t2.c in (6, 12, 18, 24, 30)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: idx_a
key: idx_a
key_len: 5
ref: NULL
rows: 5
filtered: 100.00
Extra: Using index condition
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 100345
filtered: 5.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)t1表從原先的全表掃描，變成了索引查找，預(yù)估讀取的數(shù)據(jù)行，也從原來(lái)的1000行變成了5行，優(yōu)化效果明顯。此時(shí)，再看看t2表，因?yàn)殛P(guān)聯(lián)列t2.b沒(méi)有索引，查詢列t2.c也沒(méi)有索引，所以t2表是掃描一次后，通過(guò)Block Nested-Loop Join算法與Join Buffer中的數(shù)據(jù)進(jìn)行匹配。
在前面講解Index Nested-Loop Join時(shí)，咱知道，如果關(guān)聯(lián)列 t2.b 有索引，就會(huì)使用Index Nested-Loop Join算法進(jìn)行數(shù)據(jù)匹配，那，如果關(guān)聯(lián)列沒(méi)索引，但是查詢過(guò)濾列 t2.c 有索引，會(huì)是怎樣的？
3、SQL優(yōu)化2
同樣的，咱用 t2.a 列替代 t2.c 列，重寫(xiě)測(cè)試SQL：
select *
from t1 join t2 on t1.b = t2.b
where
t1.a in (6, 12, 18, 24, 30)
and t2.a in (6, 12, 18, 24, 30);執(zhí)行計(jì)劃：
[5.7.37-log localhost:mysql.sock]>explain select *
-> from t1 join t2 on t1.b = t2.b
-> where
-> t1.a in (6, 12, 18, 24, 30)
-> and t2.a in (6, 12, 18, 24, 30)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: idx_a
key: idx_a
key_len: 5
ref: NULL
rows: 5
filtered: 100.00
Extra: Using index condition
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: range
possible_keys: idx_a
key: idx_a
key_len: 5
ref: NULL
rows: 5
filtered: 10.00
Extra: Using index condition; Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)與前面的執(zhí)行計(jì)劃對(duì)比發(fā)現(xiàn)，其依然是使用Block Nested-Loop Join算法，只不過(guò)原先t2表，從全表掃描，變成了通過(guò) t2.a 列索引，一次性查找出全部數(shù)據(jù)后，再與Join Buffer中t1表的結(jié)果集進(jìn)行匹配，如果 t2.a 列根據(jù)查詢條件過(guò)濾出來(lái)的數(shù)據(jù)，足夠少，這也不失為一個(gè)較好的優(yōu)化思路。
4、SQL優(yōu)化3
當(dāng)然了，如果關(guān)聯(lián)列有索引，查詢列沒(méi)索引，你已經(jīng)知道了是使用Index Nested-Loop Join算法，繼續(xù)重寫(xiě)測(cè)試SQL：
select *
from t1 join t2 on t1.b = t2.a
where
t1.a in (6, 12, 18, 24, 30)
and t2.c in (6, 12, 18, 24, 30);執(zhí)行計(jì)劃：
[5.7.37-log localhost:mysql.sock]>explain select *
-> from t1 join t2 on t1.b = t2.a
-> where
-> t1.a in (6, 12, 18, 24, 30)
-> and t2.c in (6, 12, 18, 24, 30)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: idx_a
key: idx_a
key_len: 5
ref: NULL
rows: 5
filtered: 100.00
Extra: Using index condition; Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: idx_a
key: idx_a
key_len: 5
ref: db1.t1.b
rows: 1
filtered: 50.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)被驅(qū)動(dòng)表關(guān)聯(lián)列有索引，查詢列無(wú)索引，使用Index Nested-Loop Join算法。
5、疑問(wèn)
如果t2表中，關(guān)聯(lián)列和查詢列，都有索引，他會(huì)怎么選？為了更好的比較，咱給 t2.c 列創(chuàng)建一個(gè)索引，并對(duì) t2.a 列的數(shù)據(jù)進(jìn)行適當(dāng)?shù)恼{(diào)整。
# 添加c列索引
alter table t2 add index idx_c(c);
# 調(diào)整t2表a列數(shù)據(jù)，a列查詢條件中的值，每個(gè)值對(duì)應(yīng)的數(shù)據(jù)量為4000
update t2 set a=a%50;
# 消除表碎片，避免被其干擾
alter table t2 engine=innodb;
# 驅(qū)動(dòng)表傳過(guò)來(lái)的鍵值，每個(gè)鍵值對(duì)應(yīng)的數(shù)據(jù)為4000行
[5.7.37-log localhost:mysql.sock]>select a,count(a) cnt
-> from t2
-> where a in (6, 12, 18, 24, 30)
-> group by a;
+------+------+
| a | cnt |
+------+------+
| 6 | 4000 |
| 12 | 4000 |
| 18 | 4000 |
| 24 | 4000 |
| 30 | 4000 |
+------+------+
5 rows in set (0.01 sec)
# 總共符合條件的數(shù)據(jù)，5行
[5.7.37-log localhost:mysql.sock]>select * from t2 where c in (6, 12, 18, 24, 30);
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 3 | 6 | 6 | 6 |
| 6 | 12 | 12 | 12 |
| 9 | 18 | 18 | 18 |
| 12 | 24 | 24 | 24 |
| 15 | 30 | 30 | 30 |
+----+------+------+------+
5 rows in set (0.01 sec)重寫(xiě)測(cè)試SQL：
select *
from t1 join t2 on t1.b = t2.a
where
t1.a in (6, 12, 18, 24, 30)
and t2.c in (6, 12, 18, 24, 30);執(zhí)行計(jì)劃：
[5.7.37-log localhost:mysql.sock]>explain select *
-> from t1 join t2 on t1.b = t2.a
-> where
-> t1.a in (6, 12, 18, 24, 30)
-> and t2.c in (6, 12, 18, 24, 30)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: idx_a
key: idx_a
key_len: 5
ref: NULL
rows: 5
filtered: 100.00
Extra: Using index condition
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: range
possible_keys: idx_a,idx_c
key: idx_c
key_len: 5
ref: NULL
rows: 5
filtered: 4.55
Extra: Using index condition; Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)由此可見(jiàn)，Block Nested-Loop Join （Hash Join）與 Index Nested-Loop Join 對(duì)比，并沒(méi)有哪一種算法更優(yōu)一說(shuō)，只要其整體成本比另一種低，那他就是最合適的。當(dāng)然了，前面所有例子，都是只有2個(gè)表關(guān)聯(lián)，對(duì)于3表及以上的關(guān)聯(lián)SQL而言，如果你把前2個(gè)表的關(guān)聯(lián)結(jié)果，當(dāng)成一個(gè)新的驅(qū)動(dòng)表看待，那么所有后面的表關(guān)聯(lián)，是不是都只需分析兩表關(guān)聯(lián)的情況即可。
五、最后
至此，對(duì)于想學(xué)習(xí)SQL優(yōu)化的你，功力是不是又有長(zhǎng)進(jìn)了。如果你還有其他疑問(wèn)，可以寫(xiě)在評(píng)論區(qū)，咱后面再繼續(xù)探討。
