絕了,通俗的給你講懂MySQL表連接原理

你知道的越多，不知道的就越多，業(yè)余的像一棵小草！

你來，我們一起精進(jìn)！你不來，我和你的競(jìng)爭(zhēng)對(duì)手一起精進(jìn)！

編輯：業(yè)余草

liuchenyang0515.blog.csdn.net

推薦：https://www.xttblog.com/?p=5317

一、表連接的簡(jiǎn)介

create?table?t1(m1?int,?n1?char(1));
create?table?t2(m2?int,?n2?char(1));

insert?into?t1?values(1,'a'),(2,'b'),(3,'c');
insert?into?t2?values(2,'b'),(3,'c'),(4,'d');

t1表數(shù)據(jù)如下

t2表數(shù)據(jù)如下

我們知道，所謂表連接就是把各個(gè)表中的記錄都取出來進(jìn)行依次匹配，最后把匹配組合的記錄一起發(fā)送給客戶端。比如下面把t1表和t2表連接起來的過程如下圖

「什么是連接查詢？」

比如上面t1和t2表的記錄連接起來組成一個(gè)新的更大的記錄，這個(gè)查詢過程就稱為連接查詢。

「什么是笛卡爾積？」

如果連接查詢的結(jié)果集中包含一個(gè)表中的每一條記錄與另一個(gè)表中的每一條記錄相互匹配組合的記錄，那么這樣的結(jié)果集就可以稱為笛卡爾積。

#?這三者效果一樣，只要不寫條件，就產(chǎn)生笛卡爾積，結(jié)果集的數(shù)量一樣。
select?*?from?t1,?t2;
#?內(nèi)連接
select?*?from?t1?inner?join?t2;
#?全連接
select?*?from?t1?cross?join?t2;

表t1中有3條記錄，表t2中也有3條記錄，兩個(gè)表連接后的笛卡爾積就有3 x 3 = 9條記錄，只要把兩個(gè)表的記錄數(shù)相乘，就能得到笛卡爾積的數(shù)量。

二、表連接的過程

笛卡爾積也是一個(gè)很大的問題，不加限制條件，結(jié)果集的數(shù)量就會(huì)很大。比如你在開發(fā)過程中需要2個(gè)表的連接，表1有20000條記錄，表2有10000條記錄，表3有100條記錄，那么3張表連接后產(chǎn)生的笛卡爾積就有20000 x 10000 x 100 = 20000000000條記錄（兩百億條記錄）。

所以在連接時(shí)過濾掉特定的記錄組合是很有必要的，為了避免笛卡爾積，一定要在表連接的時(shí)候加上條件！

下面來看一下有過濾條件的表連接的執(zhí)行過程。

#?下面兩種寫法都一樣，執(zhí)行效率沒有區(qū)別，看看自己習(xí)慣于哪種寫法
select?*?from?t1?join?t2?on?t1.m1?>?1?and?t1.m1?=?t2.m2?and?t2.n2?'d';

select?*?from?t1,?t2?where?t1.m1?>?1?and?t1.m1?=?t2.m2?and?t2.n2?'d';

注意：先說明條件的概念，要區(qū)分什么是「連接條件」和「過濾條件」??！

「連接條件」是針對(duì)兩張表而言的，比如t1.m1 = t2.m2、t1.n1 > t2.n2，表達(dá)式兩邊是兩個(gè)表的字段比較。

「過濾條件」是針對(duì)單表而言的，比如t1.m1 > 1是針對(duì)t1表的過濾條件，t2.n2 < 'd'是針對(duì)t2表的過濾條件。

1. 首先確定第一個(gè)需要查詢的表，這個(gè)表稱之為「驅(qū)動(dòng)表」。

在單表中選擇代價(jià)最小的查詢方式，簡(jiǎn)單理解就是走合適的索引即可。此處假設(shè)使用t1作為驅(qū)動(dòng)表，那么就需要到t1表中找滿足過濾條件t1.m1 > 1的記錄，因?yàn)楸碇械臄?shù)據(jù)太少，我們也沒在表上建立索引，所以此處查詢t1表的查詢的方式就是all，也就是采用全表掃描的方式執(zhí)行單表查詢，篩選出符合條件的驅(qū)動(dòng)表記錄。

這里篩選出來的t1驅(qū)動(dòng)表記錄有2條。

1. 從第1步中驅(qū)動(dòng)表篩選出來的每一條記錄，都要到t2表中查詢匹配記錄。

匹配記錄就是找到滿足連接條件和過濾條件的記錄。因?yàn)槭歉鶕?jù)t1表中的記錄去找t2表中的記錄，所以t2表也可以稱為「被驅(qū)動(dòng)表」。上一步從驅(qū)動(dòng)表篩選出了2條記錄，意味著需要從頭到尾將t2表查詢2次，此時(shí)就得看兩表之間的連接條件了，這里就是t1.m1 = t2.m2。

對(duì)于從t1表查詢得到的第一條記錄，而這條記錄t1.m1=2，根據(jù)連接條件t1.m1 = t2.m2，就相當(dāng)于在t2表加上過濾條件t2.m2 = 2，此時(shí)t2表相當(dāng)于有了兩個(gè)過濾條件t2.m2 = 2 and t2.n2 < 'd'，然后到t2表執(zhí)行單表查詢，每當(dāng)匹配到滿足條件的一條記錄后立即返回給MySQL客戶端，以此類推。

所以整個(gè)連接查詢的執(zhí)行過程如下：

最后連接查詢的結(jié)果只有2條記錄。

如果把t1.m1 > 1這個(gè)過濾條件去掉了，那么從t1表查出的記錄就有3條，就需要從頭到尾掃3次t2表了。

「其實(shí)這個(gè)流程的套路就是用偽代碼說明非常合適，你細(xì)品，看懂這個(gè)偽代碼，你就理解了表連接的步驟。」

for??篩選?驅(qū)動(dòng)表?滿足條件的每條記錄?{
?for?篩選?被驅(qū)動(dòng)表?滿足條件的每條記錄?{
??發(fā)送到MySQL客戶端;
?}
}

從這個(gè)偽代碼可以看出，驅(qū)動(dòng)表的每一條記錄都會(huì)嘗試遍歷被驅(qū)動(dòng)表的每條記錄并匹配連接，每成功連接一條就返回給MySQL客戶端。

總結(jié)：

1. 在兩表連接查詢中，驅(qū)動(dòng)表只需訪問一次，而被驅(qū)動(dòng)表可能需要訪問多次。

2. 并不是將所有滿足過濾條件的驅(qū)動(dòng)表記錄先查詢出來放到一個(gè)地方，然后再去被驅(qū)動(dòng)表查詢的（因?yàn)槿绻麧M足過濾條件的驅(qū)動(dòng)表很大，需要的臨時(shí)存儲(chǔ)空間就會(huì)非常大）。而是每獲得一條滿足過濾條件的驅(qū)動(dòng)表記錄，就立即到被驅(qū)動(dòng)表中查詢匹配的記錄。

三、內(nèi)連接和外連接

1.內(nèi)連接

上面第二節(jié)所講的，都是內(nèi)連接。

先建立2張表，后續(xù)根據(jù)這2張表來講解。

CREATE?TABLE?student?(
????stu_no?INT?NOT?NULL?AUTO_INCREMENT?COMMENT?'學(xué)號(hào)',
????name?VARCHAR(5)?COMMENT?'姓名',
????major?VARCHAR(30)?COMMENT?'專業(yè)',
????PRIMARY?KEY?(stu_no)
)?Engine=InnoDB?CHARSET=utf8mb4?COMMENT?'學(xué)生信息表';

CREATE?TABLE?score?(
????stu_no?INT?COMMENT?'學(xué)號(hào)',
????subject?VARCHAR(30)?COMMENT?'科目',
????score?TINYINT?COMMENT?'成績(jī)',
????PRIMARY?KEY?(stu_no,?subject)
)?Engine=InnoDB?CHARSET=utf8mb4?COMMENT?'學(xué)生成績(jī)表';

插入一些數(shù)據(jù)

insert?into?student?values(20210901,?'王大個(gè)',?'軟件工程');
insert?into?student?values(20210902,?'劉帥哥',?'物聯(lián)網(wǎng)工程');
insert?into?student?values(20210903,?'張小偉',?'電子工程');

insert?into?score?values(20210901,?'數(shù)據(jù)結(jié)構(gòu)',?92);
insert?into?score?values(20210901,?'計(jì)算機(jī)網(wǎng)絡(luò)',?94);
insert?into?score?values(20210902,?'計(jì)算機(jī)網(wǎng)絡(luò)',?88);
insert?into?score?values(20210902,?'數(shù)據(jù)結(jié)構(gòu)',?80);

student表數(shù)據(jù)如下：

score表數(shù)據(jù)如下：

如果想要把學(xué)生的成績(jī)都查出來，就需要表連接(score表中沒有姓名，所以不能只查score表)，連接過程就是從student表取出記錄，然后在score表中查找number相同的成績(jī)記錄，連接條件是student.stu_no= score.stu_no;

select?*?from?student?join?score?where?student.stu_no?=?score.stu_no;

表連接的全部字段就在這里了，字段有點(diǎn)多，stu_no是重復(fù)的，我們修改一下

select?s1.stu_no,?s1.name,?s2.subject,?s2.score?from?student?as?s1?join?score?as?s2?on?s1.stu_no?=?s2.stu_no;

可以看到，學(xué)生的各科成績(jī)都被查出來了。但是張小偉(學(xué)號(hào)為20210903的同學(xué))因?yàn)槿笨?，?code style="font-family:'Operator Mono', Consolas, Monaco, Menlo, monospace;color:rgb(53,148,247);padding-left:2px;">score表中沒有記錄。要是老師想查看所有學(xué)生的成績(jī)(包括缺考的同學(xué))該怎么辦呢？也就是說，哪怕成績(jī)?yōu)榭?，也要顯示這位同學(xué)在這個(gè)表里面，咱們不能把他給踢了吧！

「這個(gè)問題就化為這個(gè)模型：對(duì)于驅(qū)動(dòng)表中的某條記錄，哪怕根據(jù)連接條件或者過濾條件在被驅(qū)動(dòng)表中沒有找到對(duì)應(yīng)的記錄，也還是要把該驅(qū)動(dòng)表的記錄加到結(jié)果集。」

這就是內(nèi)連接的局限性。

「其實(shí)我們想要看到的結(jié)果集是這樣的」

為了解決這個(gè)問題，就有了「內(nèi)連接」和「外連接」的區(qū)別。

對(duì)于內(nèi)連接來說，若驅(qū)動(dòng)表中的記錄按照「連接條件或者過濾條件」在被驅(qū)動(dòng)表中找不到匹配的記錄，則該記錄不會(huì)加入到最后的結(jié)果集。

前面提到的都是內(nèi)連接，比如前面例子中，當(dāng)t1.m1 = 2時(shí)，根據(jù)連接條件t1.m1 = t2.m2，在被驅(qū)動(dòng)表中如果沒有記錄滿足過濾條件t2.m2 = 2 and t2.n2 < 'd'，驅(qū)動(dòng)表的記錄就不會(huì)加到最后的結(jié)果集。

注意：我們說過，內(nèi)連接語法有很多種。對(duì)于內(nèi)連接來說，連接條件選擇on或者where都可以，凡是不符合on子句或者where子句條件的記錄都會(huì)被過濾掉，不會(huì)被連接，更不會(huì)在最后的結(jié)果集。

#?以下三者效果一樣，當(dāng)用join進(jìn)行內(nèi)連接時(shí)，條件用on或者where連接都可以。
select?*?from?student?join?score?on?student.stu_no=?score.stu_no;

select?*?from?student?join?score?where?student.stu_no=?score.stu_no;

select?*?from?student,?score?where?student.stu_no=?score.stu_no;

2.外連接

對(duì)于外連接來說，即使驅(qū)動(dòng)表中的記錄按照「連接條件和過濾條件」在被驅(qū)動(dòng)表中找不到匹配的記錄，該記錄也仍然需要加入到結(jié)果集。

對(duì)于外連接來說，又有左(外)連接和右(外)連接的區(qū)別

左(外)連接：選取左側(cè)的表為驅(qū)動(dòng)表。

右(外)連接：選取右側(cè)的表為驅(qū)動(dòng)表。

?

「重點(diǎn)強(qiáng)調(diào)：對(duì)于內(nèi)連接來說，選取哪個(gè)表為驅(qū)動(dòng)表都沒關(guān)系。而外連接的驅(qū)動(dòng)表是固定的，左(外)連接的驅(qū)動(dòng)表就是左邊那個(gè)表，右(外)連接的驅(qū)動(dòng)表就是右邊那個(gè)表?！?/strong>

?

「左(外)連接的語法：」

比如要把t1表和t2表進(jìn)行左連接查詢。

select?*?from?t1?
left?[outer]?join?t2
on?條件
[where?普通過濾條件]

#?注意這個(gè)on條件包括連接條件和驅(qū)動(dòng)表與被驅(qū)動(dòng)表的單表過濾條件。
#?[]括號(hào)代表可以省略

左表所有記錄都會(huì)有，右表沒有與之匹配的則用NULL填充。

對(duì)于外連接來說，on和where是有區(qū)別的。

即使被驅(qū)動(dòng)表中的記錄無法匹配on子句的條件，該驅(qū)動(dòng)表的記錄仍然是滿足條件的一條記錄，對(duì)應(yīng)被驅(qū)動(dòng)表的各個(gè)字段用NULL填充。

「簡(jiǎn)言之，對(duì)于外連接，驅(qū)動(dòng)表的記錄一定都有，被驅(qū)動(dòng)表不匹配就用NULL填充?！?/strong>

而where過濾條件是在記錄連接過后的普通過濾條件，即連接的記錄會(huì)再次判斷是否符合條件，不符合就從結(jié)果集中剔除。

回到剛剛的問題，要把所有學(xué)生成績(jī)顯示出來(包括缺考的學(xué)生)

select?s1.stu_no,?s1.name,?s2.subject,?s2.score?from?student?as?s1?
left?join?
score?as?s2?
on?s1.stu_no?=?s2.stu_no;

從上面結(jié)果集可以看出，雖然張小偉缺考，但是還是在結(jié)果集中，只不過對(duì)應(yīng)的科目成績(jī)用NULL填充。

「右(外)連接的語法」

select?*?from?t1?
right?[outer]?join?t2
on?條件
[where?普通過濾條件]

#?注意這個(gè)on條件包括連接條件和驅(qū)動(dòng)表與被驅(qū)動(dòng)表的單表過濾條件。
#?[]括號(hào)代表可以省略

右連接中，驅(qū)動(dòng)表是右邊的表，被驅(qū)動(dòng)表是左邊的表，右表所有記錄都會(huì)有，左表沒有與之匹配的則用NULL填充。這里就不舉例了。

---

四、表連接的原理

1.簡(jiǎn)單的嵌套循環(huán)連接（Simple Nested-Loop Join）

我們前邊說過，對(duì)于兩表連接來說，驅(qū)動(dòng)表只會(huì)訪問一遍，但被驅(qū)動(dòng)表要被訪問到好多遍，具體訪問幾遍取決于驅(qū)動(dòng)表執(zhí)行單表查詢后滿足條件的記錄條數(shù)。

假設(shè)t1表和t2表都沒有索引，t1表和t2表內(nèi)連接的大致過程如下：

步驟1：選取驅(qū)動(dòng)表t1，使用與驅(qū)動(dòng)表t1相關(guān)的過濾條件，選取成本最低的單表訪問方法來執(zhí)行對(duì)驅(qū)動(dòng)表的單表查詢。（根據(jù)你的索引和記錄數(shù)量，查詢優(yōu)化器會(huì)選擇成本最低的訪問方法，這里沒有索引則全表掃描）

步驟2：對(duì)上一步中查詢驅(qū)動(dòng)表得到的每一條滿足條件的記錄，都分別到被驅(qū)動(dòng)表t2中查找匹配的記錄。

具體細(xì)節(jié)在第二節(jié)說過，這里就不細(xì)致展開。

如果有第3個(gè)表t3進(jìn)行連接的話，那么總體查詢過程就是，查找t1表滿足單表過濾條件的第一條記錄，匹配連接t2表滿足單表過濾條件的第一條記錄(此時(shí)驅(qū)動(dòng)表是t1，被驅(qū)動(dòng)表是t2)，然后匹配連接t3表滿足單表過濾條件的第1條記錄(此時(shí)驅(qū)動(dòng)表是t2，被驅(qū)動(dòng)表是t3)，將這條滿足所有條件的一條記錄返回給MySQL客戶端；前面條件不變，接著匹配連接t3表滿足單表過濾條件的第2條記錄…

這個(gè)過程最適合用偽代碼來說明了

for??篩選t1表滿足條件的每條記錄?{
?for?篩選t2表滿足條件的每條記錄?{
??for?篩選t3表滿足條件的每條記錄?{
???發(fā)送到MySQL客戶端;
??}
?}
}

這個(gè)過程就像是一個(gè)嵌套的循環(huán)，驅(qū)動(dòng)表每一條記錄，都要從頭到尾掃描一遍被驅(qū)動(dòng)表去嘗試匹配。這種連接執(zhí)行方式稱之為簡(jiǎn)單的嵌套循環(huán)連接（Simple Nested-Loop Join），這是比較笨拙的一種連接查詢算法。

注意：對(duì)于嵌套循環(huán)連接算法來說，每當(dāng)從驅(qū)動(dòng)表獲得一條記錄，就根據(jù)這條記錄立即到被驅(qū)動(dòng)表查一次，如果得到匹配連接記錄，那就把這條連接的記錄立即發(fā)送給MySQL客戶端，而不是等查詢完所有結(jié)果后才返回。然后再到被驅(qū)動(dòng)表獲取下一條符合條件的記錄，直到被驅(qū)動(dòng)表遍歷完成，就切換到驅(qū)動(dòng)表的下一條記錄再次遍歷被驅(qū)動(dòng)表的每條記錄，以此類推。

2.基于索引的嵌套循環(huán)連接（Index Nested-Loop Join）

在上一小節(jié)嵌套循環(huán)連接的步驟2中可能需要訪問多次被驅(qū)動(dòng)表，如果訪問被驅(qū)動(dòng)表的方式都是全表掃描，掃描次數(shù)就非常多。

幸好MySQL優(yōu)化器會(huì)找出所有可以用來執(zhí)行該語句的方案，并會(huì)對(duì)比之后找出成本最低的方案，簡(jiǎn)單理解就是使用哪個(gè)索引最好。所以既然會(huì)多次訪問被驅(qū)動(dòng)表，索引好不好就是性能的瓶頸。

查詢被驅(qū)動(dòng)表其實(shí)就相當(dāng)于一次單表掃描，那么我們可以利用索引來加快查詢速度。

回到最開始介紹的t1表和t2表進(jìn)行內(nèi)連接的例子：

select?*?from?t1?join?t2?on?t1.m1?>?1?and?t1.m1?=?t2.m2?and?t2.n2?'d';

這其實(shí)是嵌套循環(huán)連接算法執(zhí)行的連接查詢，再把上邊那個(gè)查詢執(zhí)行過程拿下來給大家看一下：

查詢驅(qū)動(dòng)表t1后的結(jié)果集中有2條記錄，嵌套循環(huán)連接算法需要查詢被驅(qū)動(dòng)表2次：

當(dāng)t1.m1 = 2時(shí)，去查詢一遍t2表，對(duì)t2表的查詢語句相當(dāng)于：

select?*?from?t2?where?t2.m2?=?2?and?t2.n2?'d';

當(dāng)t1.m1 = 3時(shí)，再去查詢一遍t2表，此時(shí)對(duì)t2表的查詢語句相當(dāng)于：

select?*?from?t2?where?t2.m2?=?3?and?t2.n2?'d';

可以看到，原來的t1.m1 = t2.m2這個(gè)涉及兩個(gè)表的過濾條件在針對(duì)t2表進(jìn)行查詢時(shí)，選出t1表的一條記錄之后，t2表的條件就已經(jīng)確定了，即t2.m2 = 常數(shù)值，所以我們只需要優(yōu)化對(duì)t2表的查詢即可，上述兩個(gè)對(duì)t2表的查詢語句中利用到的列是m2和n2列，我們可以進(jìn)行如下嘗試：

1. 在m2列上建立索引，因?yàn)閷?duì)m2列的條件是等值查找，比如t2.m2 = 2、t2.m2 = 3等，所以可能使用到ref的訪問方法，假設(shè)使用ref的訪問方法去執(zhí)行對(duì)t2表的查詢的話，需要回表之后再判斷t2.n2 < 'd'這個(gè)條件是否成立。

2. 在n2列上建立索引，涉及到的條件是t2.n2 < 'd'，可能用到range的訪問方法，假設(shè)使用range的訪問方法對(duì)t2表進(jìn)行查詢，需要在回表之后再判斷在m2列的條件是否成立。

假設(shè)m2和n2列上都存在索引，那么就需要從這兩個(gè)里面挑一個(gè)代價(jià)更低的索引來查詢t2表。也有可能不使用m2和n2列的索引，只有在非聚集索引 + 回表的代價(jià)比全表掃描的代價(jià)更低時(shí)才會(huì)使用索引。

Index Nested-Loop Join與Simple Nested-Loop Join的不同就是被驅(qū)動(dòng)表加了索引，后面只說Index Nested-Loop Join。

?

擴(kuò)展思考：假設(shè)驅(qū)動(dòng)表全表掃描，行數(shù)是N，被驅(qū)動(dòng)表走索引，行數(shù)是M。那么

• 1.每次在被驅(qū)動(dòng)表查一行數(shù)據(jù)，則要先搜索索引，再回表查找主鍵索引。

• 2.每次被驅(qū)動(dòng)表查找次數(shù)是以2為底的M的對(duì)數(shù)，記為log M，所以在被驅(qū)動(dòng)表上查一行的掃描次數(shù)是 2*log M（因?yàn)橐乇聿檎依玫街麈I索引）。驅(qū)動(dòng)表執(zhí)行過程就要掃描驅(qū)動(dòng)表N行，然后對(duì)于每一行，到被驅(qū)動(dòng)表上匹配一次。因此整個(gè)執(zhí)行過程，查找的總次數(shù)是 N+N*2*log M。

• 3.顯然N對(duì)掃描行數(shù)的影響更大，因此應(yīng)該讓小表來做驅(qū)動(dòng)表。N擴(kuò)大1000倍的話，掃描行數(shù)就會(huì)擴(kuò)大 1000倍；而M擴(kuò)大1000倍，掃描行數(shù)擴(kuò)大不到10倍。

  「總結(jié)：如果被驅(qū)動(dòng)表可以使用索引，那么驅(qū)動(dòng)表一定要選擇數(shù)據(jù)量小的小表?！?/strong>

?

3.基于塊的嵌套循環(huán)連接（Block Nested-Loop Join）

「掃描一個(gè)表的過程其實(shí)是先把這個(gè)表從磁盤上加載到內(nèi)存中，然后從內(nèi)存中比較匹配條件是否滿足?！?/strong>

實(shí)際開發(fā)中的表可不像t1、t2這種只有3條記錄，幾千萬甚至幾億條記錄的表到處都是?，F(xiàn)在假設(shè)我們不能使用索引加快被驅(qū)動(dòng)表的查詢過程，所以對(duì)于驅(qū)動(dòng)表的每一條記錄，都需要對(duì)被驅(qū)動(dòng)表進(jìn)行全表掃描。而對(duì)被驅(qū)動(dòng)表全表掃描時(shí)，可能表前面的記錄還在內(nèi)存中，表后邊的記錄可能還在磁盤上。等掃描到后邊記錄的時(shí)候，可能由于內(nèi)存不足，所以需要把表前面的記錄從內(nèi)存中釋放掉給正在掃描的記錄騰地方，這樣就非常消耗性能。

采用嵌套循環(huán)連接算法的兩表連接過程中，被驅(qū)動(dòng)表是要被訪問好多次的，所以我們得想辦法，「盡量減少被驅(qū)動(dòng)表的訪問次數(shù)?！?/strong>

「驅(qū)動(dòng)表中滿足篩選條件的有多少條記錄，就得把被驅(qū)動(dòng)表中的所有記錄從磁盤上加載到內(nèi)存中多少次?！?/strong>

讀磁盤代價(jià)太大，能不能在內(nèi)存中操作呢？于是一個(gè)Join Buffer（連接緩沖區(qū)）的概念就出現(xiàn)了，Join Buffer就是執(zhí)行連接查詢前申請(qǐng)的一塊固定大小的內(nèi)存（默認(rèn)256K），先把滿足條件的若干條驅(qū)動(dòng)表的記錄裝在這個(gè)Join Buffer中，然后開始掃描被驅(qū)動(dòng)表，每一條「被驅(qū)動(dòng)表」的記錄一次性與Join Buffer中的所有記錄進(jìn)行匹配，因?yàn)槠ヅ涞倪^程都是在內(nèi)存中完成的，所以這樣可以顯著減少被驅(qū)動(dòng)表的I/O代價(jià)。使用Join Buffer的過程如下圖所示：

「為什么Join Buffer要裝驅(qū)動(dòng)表而不是被驅(qū)動(dòng)表呢？上面說過，小表作為驅(qū)動(dòng)表，Join Buffer裝小表更容易裝得下，下一節(jié)會(huì)講這個(gè)原因?！?/strong>

其實(shí)很好記憶，想想笛卡爾積順序也很奇妙。笛卡爾積的順序就是一條被驅(qū)動(dòng)表記錄匹配多條驅(qū)動(dòng)表記錄的順序，而不是一條驅(qū)動(dòng)表記錄去匹配被驅(qū)動(dòng)表的記錄的順序，你看看這個(gè)順序是不是很神奇，可以自行鍵兩張表連接看看笛卡爾積，觀察一下。

笛卡爾積順序是
1?a?2?b
2?b?2?b
3?c?2?b
.....
而不是
1?a?2?b
1?a?3?c
1?a?4?d
...
你發(fā)現(xiàn)了嗎？

其實(shí)最好的情況是Join Buffer足夠大，能容納驅(qū)動(dòng)表結(jié)果集中的所有記錄，這樣只需要訪問一次被驅(qū)動(dòng)表就可以完成連接操作了。這種加入了Join Buffer的嵌套循環(huán)連接算法稱之為基于塊的嵌套連接（Block Nested-Loop Join）算法。

這個(gè)Join Buffer的大小是可以通過啟動(dòng)參數(shù)或者系統(tǒng)變量join_buffer_size進(jìn)行配置，默認(rèn)大小為262144字節(jié)（也就是256KB），最小可以設(shè)置為128字節(jié)。對(duì)于被驅(qū)動(dòng)表，最好是為被驅(qū)動(dòng)表加上效率高的索引，如果實(shí)在不能使用索引，可以嘗試調(diào)大join_buffer_size的值來對(duì)連接查詢進(jìn)行優(yōu)化。

另外需要注意的是，只有滿足條件的select中的列才會(huì)被放到Join Buffer中，所以再次提醒我們，最好不要把*作為查詢列表，這樣還可以在Join Buffer中放置更多的記錄。

4.Nested-Loop Join和Block Nested-Loop Join對(duì)比說明

假設(shè)t1表的行數(shù)是N，t2表的行數(shù)是M，t1表是小表，即N < M

「Simple Nested-Loop Join算法：」

1. 驅(qū)動(dòng)表的每一條記錄都會(huì)去被驅(qū)動(dòng)表逐一匹配，所以總的掃描行數(shù)是N * M（開頭說了，掃描表就是把表從磁盤加載到內(nèi)存中）；
2. 內(nèi)存中的判斷次數(shù)是N * M（掃描一次就會(huì)在內(nèi)存中判斷一次）。

別糾結(jié)了，這種方法太笨了，不管選擇哪個(gè)表作為驅(qū)動(dòng)表，最后掃描和內(nèi)存中判斷的成本都是一樣的。

「Index Nested-Loop Join算法」

該算法被驅(qū)動(dòng)表的查詢條件字段加上了合適的索引。

1. 驅(qū)動(dòng)表的每一條記錄都會(huì)去被驅(qū)動(dòng)表逐一匹配，所以總的掃描行數(shù)是N * log M（掃描行數(shù)不變，但是因?yàn)楸或?qū)動(dòng)表有索引，掃描速度會(huì)大大增加）；
2. 內(nèi)存中的判斷次數(shù)是M * N（掃描一次就會(huì)在內(nèi)存中判斷一次）。

「Block Nested-Loop Join算法：」

該算法又得區(qū)分Join Buffer裝得下和裝不下的情況。

「Join Buffer裝得下的情況」

1. t1表和t2表都做一次全表掃描，將t1表記錄都裝入Join Buffer，總的掃描行數(shù)是M + N（開頭說了，掃描表就是把表從磁盤加載到內(nèi)存中，驅(qū)動(dòng)表掃描M行一次性裝到Join Buffer，被驅(qū)動(dòng)表掃描一行會(huì)在Join Buffer進(jìn)行比較，最終掃描N行）；
2. 內(nèi)存中的判斷次數(shù)是M * N，由于Join Buffer是以「無序數(shù)組」的方式組織的，因此對(duì)t2表中的每一行數(shù)據(jù)，都要與Join Buffer中的記錄相比較。

可以看到，調(diào)換這兩個(gè)算式中的M和N沒差別，因此這時(shí)候選擇t1還是t2表做驅(qū)動(dòng)表，成本都是一樣的。

「Join Buffer裝不下的情況」

我們先用直觀的數(shù)據(jù)說明過程，假如表t1是100行，而Join Buffer放不下，此時(shí)就分段放，執(zhí)行過程就變成了：

1. 掃描表t1，順序讀取數(shù)據(jù)行放入Join Buffer中，放完第80行Join Buffer滿了，繼續(xù)第2步；
2. 掃描表t2，把t2中的每一行取出來，跟Join Buffer中的所有記錄做對(duì)比，滿足join條件的，返回該條記錄給MySQL客戶端；
3. 清空Join Buffer；
4. 繼續(xù)掃描表t1，順序讀取最后的20行數(shù)據(jù)放入Join Buffer中，繼續(xù)執(zhí)行第2步。

這個(gè)流程體現(xiàn)出了這個(gè)算法名字中“Block”的由來，表示“分塊的join”。

現(xiàn)在總結(jié)一下這個(gè)過程。驅(qū)動(dòng)表t1的數(shù)據(jù)行數(shù)是N，假設(shè)需要分K次才能完成算法流程，被驅(qū)動(dòng)表t2的數(shù)據(jù)行數(shù)是M。

注意，這里的K不是常數(shù)，N越大K就會(huì)越大。所以，在這個(gè)執(zhí)行過程中：

1. 掃描行數(shù)是N + K * M，每次裝完一次Join Buffer，被驅(qū)動(dòng)表t2的M條記錄就會(huì)從頭到尾去Join Buffer匹配，Join Buffer需要裝K次，則掃描K次t2表；
2. 內(nèi)存判斷N * M次，由于Join Buffer是以「無序數(shù)組」的方式組織的，因此對(duì)t2表中的每一行數(shù)據(jù)，都要與Join Buffer中的記錄相比較。

顯然，內(nèi)存判斷次數(shù)是不受選擇哪個(gè)表作為驅(qū)動(dòng)表影響的。而掃描行數(shù)考慮到Join Buffer的大小，在M和N大小確定的情況下，驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)N小一些，則分段K就少一些，那么整個(gè)表達(dá)式的結(jié)果會(huì)更小。

?

總結(jié)：如果Join Buffer能裝任意一張表里的所有數(shù)據(jù)，那么不管選擇哪個(gè)表作為驅(qū)動(dòng)表，執(zhí)行成本都一樣。對(duì)于Join Buffer一次裝不下驅(qū)動(dòng)表的情況下，應(yīng)該讓小表當(dāng)驅(qū)動(dòng)表，因?yàn)樾”碛涗浛傂袛?shù)N越小，Join Buffer裝完所需的次數(shù)K就越小，在N + K * M這個(gè)式子里，表達(dá)式的值越小。

?

剛剛我們說了N越大，分段數(shù)K越大。那么N固定的時(shí)候，什么參數(shù)會(huì)影響K的大小呢？答案是join_buffer_size。join_buffer_size越大，Join Buffer中一次可以放入的行越多，分成的段數(shù)K也就越少，對(duì)被驅(qū)動(dòng)表的全表掃描次數(shù)就越少。

join_buffer_size默認(rèn)256K，我所在的公司配置的是4M。

?

1.不能使用被驅(qū)動(dòng)表的索引，只能使用Block Nested-Loop Join算法，這樣的語句就盡量不要使用
2.Explain下，沒用Index Nested-Loop 的全要優(yōu)化

?

「綜上：從上面1234小節(jié)來看，無論哪種情況，總是應(yīng)該選擇小表作為驅(qū)動(dòng)表。并且兩張表有個(gè)各自的索引，這樣表連接才能達(dá)到更好的性能。在內(nèi)連接中，你可以使用STRAIGHT_JOIN替換JOIN，這樣在內(nèi)連接中就是強(qiáng)制左表為驅(qū)動(dòng)表。」

---

歡迎一鍵三連~