在mysql中，join 主要有Nested Loop、Hash Join、Merge Join 這三種方式，我們今天來(lái)看一下最普遍 Nested Loop 循環(huán)連接方式，主要包括三種：

• Simple Nested-Loop Join：簡(jiǎn)單嵌套循環(huán)連接
• Block Nested-Loop Join：緩存塊嵌套循環(huán)連接
• Index Nested-Loop Join：索引嵌套循環(huán)連接

Simple Nested-Loop Join

我們來(lái)看一下當(dāng)進(jìn)行 join 操作時(shí)，mysql是如何工作的。常見(jiàn)的 join 方式有哪些？如圖，當(dāng)我們進(jìn)行連接操作時(shí)，左邊的表是 「驅(qū)動(dòng)表」 ，右邊的表是**「被驅(qū)動(dòng)表」**

Simple Nested-Loop Join 這種連接操作是從驅(qū)動(dòng)表中取出一條記錄然后逐條匹配被驅(qū)動(dòng)表的記錄，如果條件匹配則將結(jié)果返回。然后接著取驅(qū)動(dòng)表的下一條記錄進(jìn)行匹配，直到驅(qū)動(dòng)表的數(shù)據(jù)全都匹配完畢

「因?yàn)槊看螐尿?qū)動(dòng)表取數(shù)據(jù)比較耗時(shí)，所以MySQL并沒(méi)有采用這種算法來(lái)進(jìn)行連接操作」

Block Nested-Loop Join

既然每次從驅(qū)動(dòng)表取數(shù)據(jù)比較耗時(shí)，那我們每次從驅(qū)動(dòng)表取一批數(shù)據(jù)放到內(nèi)存中，然后對(duì)這一批數(shù)據(jù)進(jìn)行匹配操作。這批數(shù)據(jù)匹配完畢，再?gòu)尿?qū)動(dòng)表中取一批數(shù)據(jù)放到內(nèi)存中，直到驅(qū)動(dòng)表的數(shù)據(jù)全都匹配完畢

批量取數(shù)據(jù)能減少很多IO操作，因此執(zhí)行效率比較高，這種連接操作也被MySQL采用

對(duì)了，這塊內(nèi)存在MySQ中有一個(gè)專有的名詞，叫做 join buffer，我們可以執(zhí)行如下語(yǔ)句查看 join buffer 的大小

show variables like '%join_buffer%'

把我們之前用的 single_table 表搬出來(lái)，基于 single_table 表創(chuàng)建2個(gè)表，每個(gè)表插入1w條隨機(jī)記錄

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

create table t1 like single_table;
create table t2 like single_table;

如果直接使用 join 語(yǔ)句，MySQL優(yōu)化器可能會(huì)選擇表 t1 或者 t2 作為驅(qū)動(dòng)表，這樣會(huì)影響我們分析sql語(yǔ)句的過(guò)程，所以我們用 straight_join 讓mysql使用固定的連接方式執(zhí)行查詢

select * from t1 straight_join t2 on (t1.common_field = t2.common_field)

運(yùn)行時(shí)間為0.035s執(zhí)行計(jì)劃如下在Extra列中看到了 Using join buffer ，說(shuō)明連接操作是基于 「Block Nested-Loop Join」 算法

Index Nested-Loop Join

了解了 「Block Nested-Loop Join」 算法之后，可以看到驅(qū)動(dòng)表的每條記錄會(huì)把被驅(qū)動(dòng)表的所有記錄都匹配一遍，非常耗時(shí)，能不能提高一下被驅(qū)動(dòng)表匹配的效率呢？

估計(jì)這種算法你也想到了，就是給被驅(qū)動(dòng)表連接的列加上索引，這樣匹配的過(guò)程就非?？?，如圖所示我們來(lái)看一下基于索引列進(jìn)行連接執(zhí)行查詢有多快？

select * from t1 straight_join t2 on (t1.id = t2.id)

執(zhí)行時(shí)間為0.001秒，可以看到比基于普通的列進(jìn)行連接快了不止一個(gè)檔次執(zhí)行計(jì)劃如下「驅(qū)動(dòng)表的記錄并不是所有列都會(huì)被放到 join buffer，只有查詢列表中的列和過(guò)濾條件中的列才會(huì)被放入 join buffer，因此我們不要把 * 作為查詢列表，只需要把我們關(guān)心的列放到查詢列表就好了，這樣可以在 join buffer 中放置更多的記錄」

如何選擇驅(qū)動(dòng)表？

知道了 join 的具體實(shí)現(xiàn)，我們來(lái)聊一個(gè)常見(jiàn)的問(wèn)題，即如何選擇驅(qū)動(dòng)表？

「如果是 Block Nested-Loop Join 算法：」

1. 當(dāng) join buffer 足夠大時(shí)，誰(shuí)做驅(qū)動(dòng)表沒(méi)有影響
2. 當(dāng) join buffer 不夠大時(shí)，應(yīng)該選擇小表做驅(qū)動(dòng)表（小表數(shù)據(jù)量少，放入 join buffer 的次數(shù)少，減少表的掃描次數(shù)）

「如果是 Index Nested-Loop Join 算法」

假設(shè)驅(qū)動(dòng)表的行數(shù)是M，因此需要掃描驅(qū)動(dòng)表M行

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

驅(qū)動(dòng)表的每一行數(shù)據(jù)都要到被驅(qū)動(dòng)表上搜索一次，整個(gè)執(zhí)行過(guò)程近似復(fù)雜度為

「顯然M對(duì)掃描行數(shù)影響更大，因此應(yīng)該讓小表做驅(qū)動(dòng)表。當(dāng)然這個(gè)結(jié)論的前提是可以使用被驅(qū)動(dòng)表的索引」

「總而言之，我們讓小表做驅(qū)動(dòng)表即可」

「當(dāng) join 語(yǔ)句執(zhí)行的比較慢時(shí)，我們可以通過(guò)如下方法來(lái)進(jìn)行優(yōu)化」

1. 進(jìn)行連接操作時(shí)，能使用被驅(qū)動(dòng)表的索引
2. 小表做驅(qū)動(dòng)表
3. 增大 join buffer 的大小
4. 不要用 * 作為查詢列表，只返回需要的列