什么是MySQL 連接查詢？相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。
在數據庫中join操作被稱為連接，作用是能連接多個表的數據（通過連接條件），從多個表中獲取數據合并在一起作為結果集返回給客戶端。例如:
表A：
| id | name | age |
|---|---|---|
| 1 | A | 18 |
| 2 | B | 19 |
| 3 | C | 20 |
表B：
| id | uid | gender |
|---|---|---|
| 1 | 1 | F |
| 2 | 2 | M |
通過連接可以獲取到合并兩個表的數據：
select A.*,B.gender from A left join B on A.id=B.uid
| id | name | age | gender |
|---|---|---|---|
| 1 | A | 18 | F |
| 2 | B | 19 | M |
| 3 | C | 20 | null |
連接兩個表我們可以用兩個關鍵字：on，using。on可以指定具體條件，using則指定相同名字和數據類型的列作為等值判斷的條件，多個則通過逗號隔開。
如下:
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
A join | inner join | cross join Bcross join ，inner join和join所實現的功能是一樣的。因此在MySQL的官方文檔中，指明了三者是等價的關系。from A,B,Con和using的joinA left join BnullA right join BnullMySQL不支持全外連接，只支持左外連接和右外連接。如果要獲取全連接的數據，要可以通過合并左右外連接的數據獲取到，如 select * from A left join B on A.name = B.name union select * from A right join B on B.name = B.name;。
這里union會自動去重，這樣取到的就是全外連接的數據了。
A natural join B ==== A natural left join B ==== A natural right join B在連接過程中，MySQL各關鍵字執行的順序如下：
from -> on|using -> where -> group by -> having -> select -> order by -> limit
可以看到，連接的條件是先于where的，也就是先連接獲得結果集后，才對結果集進行where篩選，所以在使用join的時候，我們要盡可能提供連接的條件，而少用where的條件，這樣才能提高查詢性能。
join有三種算法，分別是Nested Loop Join，Hash join，Sort Merge Join。MySQL官方文檔中提到，MySQL只支持Nested Loop Join這一種算法。
具體來說Nested Loop Join又分三種細分的算法：
我們來看下對于連接語句select * from A left join B on A.id=B.tid，這三種算法是怎么連接的。
SNLJ是在沒有使用到索引的情況下，通過兩層循環全量掃描連接的兩張表，得到符合條件的兩條記錄則輸出。也就是讓兩張表做笛卡爾積進行掃描，是比較暴力的算法，會比較耗時。其過程如下：
for (a in A) {
for (b in B) {
if (a.id == b.tid) {
output <a, b>;
}
}
}當然，MySQL即使在無索引可用，或者判斷全表掃描可能比使用索引更快的情況下，還是不會選擇使用過于粗暴的SNLJ算法，而是采用下面的算法。
INLJ是MySQL無法使用索引的時候采用的join算法。會將外層循環的行分片存入join buffer, 內層循環的每一行與整個buffer中的記錄做比較，從而減少內層循環的次數，具體邏輯如下:
for (blockA in A.blocks) {
for (b in B) {
if (b.tid in blockA.id) {
output <a, b>;
}
}
}相比于SNLJ算法，BNLJ算法通過外層循環的結果集的分塊，可以有效的減少內層循環的次數。
原理
舉例來說，外層循環的結果集是100行，使用SNLJ算法需要掃描內部表100次，如果使用BNLJ算法，假設每次分片的數量是10，則會先把對Outer Loop表(外部表)每次讀取的10行記錄放到join buffer,然后在InnerLoop表(內部表)中每次循環都直接匹配這10行數據，這樣內層循環只需要10次，對內部表的掃描減少了9/10，所以BNLJ算法就能夠顯著減少內層循環表掃描的次數。
當然這里，不管SNLJ還是BNLJ算法，他們總的比較次數都是一樣的，都是要拿外層循環的每一行與內層循環的每一行進行比較。
BNLJ算法減少的是總的掃描行數，SNLJ算法是外層循環要一行行掃描A表的數據，然后取A.id去表B一行行掃描看是否匹配。而BNLJ算法則是外層循環要一行行掃描A表的數據，然后放到內存分塊里，然后去表B一行行掃描，掃描出來的B的一行數據與內存分塊里的A的數據塊進行比較。這里可以一次就是很多行A的數據與B的數據進行比較，而且是在內存中進行比較，速度更加快了。
影響因素
這里BNLJ算法總的掃描行數是由外層循環的數據量N，和分塊數量K還有內層循環的數據量M決定的。其中分塊數量K與外層循環的數據量N又是息息相關的，我們可以表示為λN，其中λ取值為(0~1)。則總掃描次數C=N+λNM。
可以看出，在這個式子里，N和λ的大小都會影響掃描行數，但是λ才是影響掃描行數的關鍵因素，這個值越小越好（除非N和M的差值非常大，這時候N才會成為關鍵影響因素）。
那什么會影響 λ 的大小呢？那就是 MySQL的join_buffer_size設置項的大小了。λ和join_buffer_size成倒數關系,join_buffer_size越大，分塊越大，λ越小，分塊數量也就越少，也就是外層循環的次數也越少。所以在使用不上索引的時候，我們要優先考慮擴大join_buffer_size的大小，這樣優化效果會更明顯。而在能使用上索引的時候，MySQL會使用以下算法來進行join。
INLJ是MySQL判斷能使用到被驅動表的索引的情況下采用的算法。假設A表的數據行為10，B表的數據行為100，且B.tid建立了索引，則對于select * from A left join B on A.id=B.tid，MySQL會采用Index Nested Loop Join。其過程如下：
for (a in A) {
if (a.id in B.tid.Index) {
output <a, tid.Index所在行>;
}
}總共需要循環10次A，每次循環的時候通過索引查詢一次B的數據。而如果我們反過來是B left join A的話，總共要循環100次B，由此可見如果使用join的話，需要讓小表做驅動表，這樣才能有效減少循環次數。但是需要注意的是，這個結論的前提是可以使用被驅動表的索引。
INLJ內層循環讀取的是索引，可以減少內存循環的次數，提高join效率，但是也有缺點的，就是如果掃描的索引是非聚簇索引，并且需要訪問非索引的數據，會產生一個回表讀取數據的操作，這就多了一次隨機的I/O操作。例如上面在索引里匹配到了tid，還要去找tid所在的行在磁盤所在的位置，具體可以見我以前的文章：MySQL索引詳解之索引的存儲方式。
join后數據集的大小join的字段要建立索引，且使用上索引。使用上索引包括使用該字段，且不會有索引失效的情況出現join_buffer_sizeQ：如果想篩選驅動表的數據，例如左連接篩選左表的數據，該在連接條件還是where篩選？
A：要通過where篩選，連接條件只影響連接過程，不影響連接返回的結果數（某些情況下連接條件會影響連接返回的結果數，例如左連接中，右側匹配的數據不唯一的時候）
Q：被驅動表匹配的數據行不唯一導致最終連接數據超過驅動表數據量該怎么辦？例如對于左連接，右表匹配的數據行不唯一。
A：join之前先對被驅動表去重，例如通過group by去重：A lef join (select * from B group by name)。
看完上述內容，你們掌握什么是MySQL 連接查詢的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
