1 作用

在數(shù)據(jù)庫(kù)中join操作被稱(chēng)為連接，作用是能連接多個(gè)表的數(shù)據(jù)（通過(guò)連接條件），從多個(gè)表中獲取數(shù)據(jù)合并在一起作為結(jié)果集返回給客戶端。例如:

表A：

表B：

通過(guò)連接可以獲取到合并兩個(gè)表的數(shù)據(jù)：

select A.*,B.gender from  A left join B on A.id=B.uid

2 連接關(guān)鍵字

連接兩個(gè)表我們可以用兩個(gè)關(guān)鍵字：on，using。on可以指定具體條件，using則指定相同名字和數(shù)據(jù)類(lèi)型的列作為等值判斷的條件，多個(gè)則通過(guò)逗號(hào)隔開(kāi)。
如下:

on: select * from A join B on A.id=B.id and B.name='' using: select * from A join B using(id,name) = select * from A join B on  A.id=B.id and A.name=B.name

3 連接類(lèi)型

3.1 內(nèi)連接

內(nèi)連接和交叉連接

• 語(yǔ)法：A join | inner join | cross join B
• 表現(xiàn)：A和B滿足連接條件記錄的交集，如果沒(méi)有連接條件，則是A和B的笛卡爾積
• 特點(diǎn)：在MySQL中,cross join ，inner join和join所實(shí)現(xiàn)的功能是一樣的。因此在MySQL的官方文檔中，指明了三者是等價(jià)的關(guān)系。

隱式連接

• 語(yǔ)法：from A,B,C
• 表現(xiàn)：相當(dāng)于無(wú)法使用on和using的join
• 特點(diǎn)：逗號(hào)是隱式連接運(yùn)算符。 隱式連接是SQL92中的標(biāo)準(zhǔn)內(nèi)容，而在SQL99中顯式連接才是標(biāo)準(zhǔn)，雖然很多人還在用隱私連接，但是它已經(jīng)從標(biāo)準(zhǔn)中被移除。從使用的角度來(lái)說(shuō)，還是推薦使用顯示連接，這樣可以更清楚的顯示出多個(gè)表之間的連接關(guān)系和連接依賴(lài)的屬性。

3.2 外連接

左外連接

• 語(yǔ)法：A left join B
• 表現(xiàn)：左表的數(shù)據(jù)全部保留，右表滿足連接條件的記錄展示，不滿足的條件的記錄則全是null

右外連接

• 語(yǔ)法：A right join B
• 表現(xiàn)：右表的數(shù)據(jù)全部保留，左表滿足連接條件的記錄展示，不滿足的條件的記錄則全是null

全外連接

MySQL不支持全外連接，只支持左外連接和右外連接。如果要獲取全連接的數(shù)據(jù)，要可以通過(guò)合并左右外連接的數(shù)據(jù)獲取到，如 select * from A left join B on A.name = B.name union select * from A right join B on B.name = B.name;。

這里union會(huì)自動(dòng)去重，這樣取到的就是全外連接的數(shù)據(jù)了。

3.3 自然連接

• 語(yǔ)法：A natural join B ==== A natural left join B ==== A natural right join B
• 表現(xiàn)：相當(dāng)于不能指定連接條件的連接，MySQL會(huì)使用左右表內(nèi)相同名字和類(lèi)型的字段作為連接條件。
• 特點(diǎn)：自然連接也分自然內(nèi)連接，左外連接，右外連接，其表現(xiàn)和上面提到的一致，只是連接條件由MySQL自動(dòng)判定。

4 執(zhí)行順序

在連接過(guò)程中，MySQL各關(guān)鍵字執(zhí)行的順序如下：

from -> on|using -> where -> group by -> having -> select -> order by ->  limit

可以看到，連接的條件是先于where的，也就是先連接獲得結(jié)果集后，才對(duì)結(jié)果集進(jìn)行where篩選，所以在使用join的時(shí)候，我們要盡可能提供連接的條件，而少用where的條件，這樣才能提高查詢性能。

5 連接算法

join有三種算法，分別是Nested Loop Join，Hash join，Sort Merge Join。MySQL官方文檔中提到，MySQL只支持Nested Loop Join這一種算法。

具體來(lái)說(shuō)Nested Loop Join又分三種細(xì)分的算法：

• SNLJ
• BNLJ
• INLJ

我們來(lái)看下對(duì)于連接語(yǔ)句select * from A left join B on A.id=B.tid，這三種算法是怎么連接的。

5.1 Simple Nested Loop Join（SNLJ）

SNLJ是在沒(méi)有使用到索引的情況下，通過(guò)兩層循環(huán)全量掃描連接的兩張表，得到符合條件的兩條記錄則輸出。也就是讓兩張表做笛卡爾積進(jìn)行掃描，是比較暴力的算法，會(huì)比較耗時(shí)。其過(guò)程如下：

for (a in A) {      for (b in B) {          if (a.id == b.tid) {              output <a, b>;          }      }  }

當(dāng)然，MySQL即使在無(wú)索引可用，或者判斷全表掃描可能比使用索引更快的情況下，還是不會(huì)選擇使用過(guò)于粗暴的SNLJ算法，而是采用下面的算法。

5.2 Block Nested Loop Join（BNLJ）

INLJ是MySQL無(wú)法使用索引的時(shí)候采用的join算法。會(huì)將外層循環(huán)的行分片存入join buffer, 內(nèi)層循環(huán)的每一行與整個(gè)buffer中的記錄做比較，從而減少內(nèi)層循環(huán)的次數(shù)，具體邏輯如下:

for (blockA in A.blocks) {      for (b in B) {          if (b.tid in blockA.id) {              output <a, b>;          }      }  }

相比于SNLJ算法，BNLJ算法通過(guò)外層循環(huán)的結(jié)果集的分塊，可以有效的減少內(nèi)層循環(huán)的次數(shù)。

原理

舉例來(lái)說(shuō)，外層循環(huán)的結(jié)果集是100行，使用SNLJ算法需要掃描內(nèi)部表100次，如果使用BNLJ算法，假設(shè)每次分片的數(shù)量是10，則會(huì)先把對(duì)Outer Loop表(外部表)每次讀取的10行記錄放到join buffer,然后在InnerLoop表(內(nèi)部表)中每次循環(huán)都直接匹配這10行數(shù)據(jù)，這樣內(nèi)層循環(huán)只需要10次，對(duì)內(nèi)部表的掃描減少了9/10，所以BNLJ算法就能夠顯著減少內(nèi)層循環(huán)表掃描的次數(shù)。

當(dāng)然這里，不管SNLJ還是BNLJ算法，他們總的比較次數(shù)都是一樣的，都是要拿外層循環(huán)的每一行與內(nèi)層循環(huán)的每一行進(jìn)行比較。

BNLJ算法減少的是總的掃描行數(shù)，SNLJ算法是外層循環(huán)要一行行掃描A表的數(shù)據(jù)，然后取A.id去表B一行行掃描看是否匹配。而BNLJ算法則是外層循環(huán)要一行行掃描A表的數(shù)據(jù)，然后放到內(nèi)存分塊里，然后去表B一行行掃描，掃描出來(lái)的B的一行數(shù)據(jù)與內(nèi)存分塊里的A的數(shù)據(jù)塊進(jìn)行比較。這里可以一次就是很多行A的數(shù)據(jù)與B的數(shù)據(jù)進(jìn)行比較，而且是在內(nèi)存中進(jìn)行比較，速度更加快了。

影響因素

這里BNLJ算法總的掃描行數(shù)是由外層循環(huán)的數(shù)據(jù)量N，和分塊數(shù)量K還有內(nèi)層循環(huán)的數(shù)據(jù)量M決定的。其中分塊數(shù)量K與外層循環(huán)的數(shù)據(jù)量N又是息息相關(guān)的，我們可以表示為λN，其中λ取值為(0~1)。則總掃描次數(shù)C=N+λNM。

可以看出，在這個(gè)式子里，N和λ的大小都會(huì)影響掃描行數(shù)，但是λ才是影響掃描行數(shù)的關(guān)鍵因素，這個(gè)值越小越好（除非N和M的差值非常大，這時(shí)候N才會(huì)成為關(guān)鍵影響因素）。

那什么會(huì)影響 λ 的大小呢？那就是 MySQL的join_buffer_size設(shè)置項(xiàng)的大小了。λ和join_buffer_size成倒數(shù)關(guān)系,join_buffer_size越大，分塊越大，λ越小，分塊數(shù)量也就越少，也就是外層循環(huán)的次數(shù)也越少。所以在使用不上索引的時(shí)候，我們要優(yōu)先考慮擴(kuò)大join_buffer_size的大小，這樣優(yōu)化效果會(huì)更明顯。而在能使用上索引的時(shí)候，MySQL會(huì)使用以下算法來(lái)進(jìn)行join。

5.3 Index Nested Loop Join（INLJ）

INLJ是MySQL判斷能使用到被驅(qū)動(dòng)表的索引的情況下采用的算法。假設(shè)A表的數(shù)據(jù)行為10，B表的數(shù)據(jù)行為100，且B.tid建立了索引，則對(duì)于select * from A left join B on A.id=B.tid，MySQL會(huì)采用Index Nested Loop Join。其過(guò)程如下：

for (a in A) {      if (a.id in B.tid.Index) {         output <a, tid.Index所在行>;      }  }

總共需要循環(huán)10次A，每次循環(huán)的時(shí)候通過(guò)索引查詢一次B的數(shù)據(jù)。而如果我們反過(guò)來(lái)是B left join A的話，總共要循環(huán)100次B，由此可見(jiàn)如果使用join的話，需要讓小表做驅(qū)動(dòng)表，這樣才能有效減少循環(huán)次數(shù)。但是需要注意的是，這個(gè)結(jié)論的前提是可以使用被驅(qū)動(dòng)表的索引。

INLJ內(nèi)層循環(huán)讀取的是索引，可以減少內(nèi)存循環(huán)的次數(shù)，提高join效率，但是也有缺點(diǎn)的，就是如果掃描的索引是非聚簇索引，并且需要訪問(wèn)非索引的數(shù)據(jù)，會(huì)產(chǎn)生一個(gè)回表讀取數(shù)據(jù)的操作，這就多了一次隨機(jī)的I/O操作。例如上面在索引里匹配到了tid，還要去找tid所在的行在磁盤(pán)所在的位置，具體可以見(jiàn)我以前的文章：MySQL索引詳解之索引的存儲(chǔ)方式。

6 注意點(diǎn)

• 盡量增加連接條件，減少join后數(shù)據(jù)集的大小
• 用小結(jié)果集驅(qū)動(dòng)大結(jié)果集，將篩選結(jié)果小的表首先連接，再去連接結(jié)果集比較大的表
• 被驅(qū)動(dòng)表的被join的字段要建立索引，且使用上索引。使用上索引包括使用該字段，且不會(huì)有索引失效的情況出現(xiàn)
• 設(shè)置足夠大的join_buffer_size

7 外連接常見(jiàn)問(wèn)題

Q：如果想篩選驅(qū)動(dòng)表的數(shù)據(jù)，例如左連接篩選左表的數(shù)據(jù)，該在連接條件還是where篩選？
A：要通過(guò)where篩選，連接條件只影響連接過(guò)程，不影響連接返回的結(jié)果數(shù)（某些情況下連接條件會(huì)影響連接返回的結(jié)果數(shù)，例如左連接中，右側(cè)匹配的數(shù)據(jù)不唯一的時(shí)候）

Q：被驅(qū)動(dòng)表匹配的數(shù)據(jù)行不唯一導(dǎo)致最終連接數(shù)據(jù)超過(guò)驅(qū)動(dòng)表數(shù)據(jù)量該怎么辦？例如對(duì)于左連接，右表匹配的數(shù)據(jù)行不唯一。
A：join之前先對(duì)被驅(qū)動(dòng)表去重，例如通過(guò)group by去重：A lef join (select * from B group by name)。

相關(guān)學(xué)習(xí)推薦：mysql視頻教程