前言
在平時的開發過程中，大家應該經常會用到join進行多個表的之間的關聯查詢，但是如果使用不合理的話，會導緻查詢性能下降。本文就MySQL中的關聯查詢的實作原理以及一些優化建議等内容做一個講解。
關聯查詢介紹
關聯查詢，指兩個或更多個表一起完成查詢操作。
- 内連接配接（INNTER JOIN）
合并具有同一列的兩個以上的表的行, 結果集中不包含一個表與另一個表不比對的行，文法如下：
SELECT 字段清單
FROM A表 INNER JOIN B表
ON 關聯條件
WHERE 等其他子句; 
- 傳回的結果集是A表和B比對的行。
- A表或者B表哪個表是驅動表(主表)或者被驅動表(從表)由查詢優化器決定。
- 左連接配接(LEFT JOIN)
兩個表在連接配接過程中除了傳回滿足連接配接條件的行以外，還傳回左表中不滿足條件的行。
#實作查詢結果是A
SELECT 字段清單
FROM A表 LEFT JOIN B表
ON 關聯條件
WHERE 等其他子句; 
- 結果集中傳回比對的行，也傳回A表中不比對的行，不比對字段用NULL表示。
- A表是驅動表(主表)，B表是非驅動表(從表)。
- 右連接配接(Right JOIN)
兩個表在連接配接過程中除了傳回滿足連接配接條件的行以外，還傳回右表中不滿足條件的行。
#實作查詢結果是B
SELECT 字段清單
FROM A表 RIGHT JOIN B表
ON 關聯條件
WHERE 等其他子句; - 結果集中傳回比對的行，也傳回B表中不比對的行，不比對字段用NULL表示。
- B表是驅動表(主表)，A表是非驅動表(從表)。
關聯查詢原理
前面講解了連接配接查詢的幾種方式，現在談談MySQL底層是支援這幾種連接配接查詢的。
關聯查詢中涉及到多張表的的查詢，根據驅動類型分為驅動表和被驅動表，驅動表就是主表，被驅動表就是從表。我們可以在執行計劃中看出來。
- 執行計劃從上向下看，上面的屬于驅動表。
- 内連接配接的驅動表選擇由查詢優化器決定。
- 左連接配接的驅動表一般是左邊的表，右連接配接的驅動表一般是右邊的表。
了解了驅動表和被驅動表以後，現在我們看下MySQL究竟是怎麼做join查詢的。
簡單嵌套循環連接配接
簡單嵌套循環連接配接（Simple Nested-Loop join）是從驅動表A中取出一條資料，周遊表B，将比對到的資料放到result.. 以此類推, 如下圖所示：
- 算法簡單粗暴，比如驅動表A有10條，被驅動表B有100條，那麼掃描次數是A+A*B, 每一次掃描其實就是從硬碟中讀取資料加載到記憶體中,也就是一次IO，而IO是最大的瓶頸，是以效率低下，開銷如下表：
| 開銷統計 | 簡單嵌套循環連接配接 |
| 驅動表掃描次數 | 1 |
| 被驅動表掃描次數 | A |
| 讀取記錄數 | A+B*A |
| JOIN比較次數 | B*A |
| 回表讀取記錄次數 |
- 當然MySQL預設沒有采用這種算法。
塊嵌套循環連接配接
塊嵌套循環連接配接(Block Nested-Loop Join)是對上面一種算法的優化，竟然逐條的去驅動表中擷取資料去比對，和磁盤IO互動太多了，那麼能否批量的方式呢？而這種算法就是借鑒了這樣的思想。
不再是逐條擷取驅動表的資料，而是一塊一塊地擷取，引入了join buffer緩沖區，将驅動表join相關的部分資料列、緩存到join buffer中，然後全表掃描被驅動表，被驅動表的每一條記錄一次性和join buffer中的所有驅動表記錄進行比對（記憶體中操作），将簡單嵌套循環中的多次比較合并成一次，降低了被驅動表的通路頻率。整體如下圖所示：
- 注意一點，從驅動表中緩存的列不僅僅是關聯的的列，select後面的列也會緩存起來。是以，為了能讓join buffer緩存更多的資料，我們的SQL盡量不要select *, 而是select 用到的字段。
- 整體的開銷如下表所示：
| 開銷統計 | 簡單嵌套循環連接配接 | 塊嵌套循環連接配接 |
| 驅動表掃描次數 | 1 | 1 |
| 被驅動表掃描次數 | A | A*used_column_size/join_buffer_size+1 |
| 讀取記錄數 | A+B*A | A+B*(A*used_column_size/join_buffer_size) |
| JOIN比較次數 | B*A | B*A |
| 回表讀取記錄次數 |
- join buffer的大小是可以設定的，預設情況下join_buffer_size=256k。
show variables like '%join_buffer%'; 索引嵌套循環連接配接
那還有沒有效率更加高的關聯查詢算法呢？索引嵌套循環連接配接(Index Nested-Loop Join)就是效率最高的，前提條件是被驅動表的關聯字段建立了索引。通過驅動表比對條件直接與被驅動表的索引進行比對，避免和記憶體表的每條記錄去進行比較，這樣極大地減少了對記憶體表的比對次數。如下圖所示：
- 整體的開銷成本如下表所示：
| 開銷統計 | 簡單嵌套循環連接配接 | 塊嵌套循環連接配接 | 索引嵌套循環連接配接 |
| 驅動表掃描次數 | 1 | 1 | 1 |
| 被驅動表掃描次數 | A | A*used_column_size/join_buffer_size+1 | |
| 讀取記錄數 | A+B*A | A+B*(A*used_column_size/join_buffer_size) | A+B(match) |
| JOIN比較次數 | B*A | B*A | A*Index(Height) |
| 回表讀取記錄次數 | B(match)(if possible) |
因為索引查詢的成本基本一樣，為了降低開銷，驅動表是小表更加合适。
Hash Join(MySQL 8)
從MySQL8後面的版本開始廢棄塊嵌套循環連接配接，預設使用了Hash Join的方式。
- 塊嵌套循環連接配接：對于被連接配接的資料子集較小的情況下，它是個較好的選擇。
- Hash Join: 是做大資料集連接配接時的常用方式，優化器使用兩個表中較小（相對較小）的表利用Join Key在記憶體中建立散列值，然後掃描較大的表并探測散列值，找出與Hash表比對的行。它能夠很好的工作于沒有索引的大表和并行查詢的環境中，并提供最好的性能。Hash Join隻能應用于等值連接配接，這是由Hash的特點決定的。
優化建議
前面講解了關聯查詢Join的實作原理，那麼對于關聯查詢模式我們可以從中總結出下面的一些優化點：
- 優先保證被驅動表的連接配接字段建立索引，因為建立索引的查詢方式是效率最高的。
- left join或者 right join這種外連接配接的情況，要保證小表（小結果集）作為驅動表，大表(大結果集)作為被驅動表，這樣性能更好。
- 在查詢字段的話，要避免select *或者select 全部字段，而是按需，因為這些字段也會加入到join buffer中。
- 能夠直接多表關聯的盡量直接關聯，不用子查詢，因為子查詢的效率更加低。
- 在sql的查詢計劃的extra中，盡量避免出現Using join buffer，有這個表示使用了塊嵌套循環連接配接算法，盡量通過索引去解決。
- 盡量避免超過3張表以上的關聯查詢。
總結
本文分享了日常工作中使用非常頻繁的關聯查詢，主要關注關聯查詢的實作原理，這樣我們可以在平時寫關聯查詢的SQL時候性能才會更佳。如果本文對你有幫助，請留下一個贊吧。