關(guān)聯(lián)查詢介紹

關(guān)聯(lián)查詢，指兩個或更多個表一起完成查詢操作。

• 內(nèi)連接（INNTER JOIN）

合并具有同一列的兩個以上的表的行, 結(jié)果集中不包含一個表與另一個表不匹配的行，語法如下：

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 關(guān)聯(lián)條件
WHERE 等其他子句;

• 返回的結(jié)果集是A表和B匹配的行。
• A表或者B表哪個表是驅(qū)動表(主表)或者被驅(qū)動表(從表)由查詢優(yōu)化器決定。

• 左連接(LEFT JOIN)

兩個表在連接過程中除了返回滿足連接條件的行以外，還返回左表中不滿足條件的行。

#實現(xiàn)查詢結(jié)果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 關(guān)聯(lián)條件
WHERE 等其他子句;

• 結(jié)果集中返回匹配的行，也返回A表中不匹配的行，不匹配字段用NULL表示。
• A表是驅(qū)動表(主表)，B表是非驅(qū)動表(從表)。

• 右連接(Right JOIN)

兩個表在連接過程中除了返回滿足連接條件的行以外，還返回右表中不滿足條件的行。

#實現(xiàn)查詢結(jié)果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 關(guān)聯(lián)條件
WHERE 等其他子句;

• 結(jié)果集中返回匹配的行，也返回B表中不匹配的行，不匹配字段用NULL表示。
• B表是驅(qū)動表(主表)，A表是非驅(qū)動表(從表)。

關(guān)聯(lián)查詢原理

前面講解了連接查詢的幾種方式，現(xiàn)在談?wù)凪ySQL底層是支持這幾種連接查詢的。

關(guān)聯(lián)查詢中涉及到多張表的的查詢，根據(jù)驅(qū)動類型分為驅(qū)動表和被驅(qū)動表，驅(qū)動表就是主表，被驅(qū)動表就是從表。我們可以在執(zhí)行計劃中看出來。

• 執(zhí)行計劃從上向下看，上面的屬于驅(qū)動表。
• 內(nèi)連接的驅(qū)動表選擇由查詢優(yōu)化器決定。
• 左連接的驅(qū)動表一般是左邊的表，右連接的驅(qū)動表一般是右邊的表。

了解了驅(qū)動表和被驅(qū)動表以后，現(xiàn)在我們看下MySQL究竟是怎么做join查詢的。

簡單嵌套循環(huán)連接

簡單嵌套循環(huán)連接（Simple Nested-Loop join）是從驅(qū)動表A中取出一條數(shù)據(jù)，遍歷表B，將匹配到的數(shù)據(jù)放到result.. 以此類推, 如下圖所示：

• 算法簡單粗暴，比如驅(qū)動表A有10條，被驅(qū)動表B有100條，那么掃描次數(shù)是A+A*B, 每一次掃描其實就是從硬盤中讀取數(shù)據(jù)加載到內(nèi)存中,也就是一次IO，而IO是最大的瓶頸，所以效率低下，開銷如下表：

• 當然MySQL默認沒有采用這種算法。

塊嵌套循環(huán)連接

塊嵌套循環(huán)連接(Block Nested-Loop Join)是對上面一種算法的優(yōu)化，竟然逐條的去驅(qū)動表中獲取數(shù)據(jù)去匹配，和磁盤IO交互太多了，那么能否批量的方式呢？而這種算法就是借鑒了這樣的思想。

不再是逐條獲取驅(qū)動表的數(shù)據(jù)，而是一塊一塊的獲取，引入了join buffer緩沖區(qū)，將驅(qū)動表join相關(guān)的部分數(shù)據(jù)列、緩存到j(luò)oin buffer中，然后全表掃描被驅(qū)動表，被驅(qū)動表的每一條記錄一次性和join buffer中的所有驅(qū)動表記錄進行匹配（內(nèi)存中操作），將簡單嵌套循環(huán)中的多次比較合并成一次，降低了被驅(qū)動表的訪問頻率。整體如下圖所示：

• 注意一點，從驅(qū)動表中緩存的列不僅僅是關(guān)聯(lián)的的列，select后面的列也會緩存起來。因此，為了能讓join buffer緩存更多的數(shù)據(jù)，我們的SQL盡量不要select *, 而是select 用到的字段。
• 整體的開銷如下表所示：

• join buffer的大小是可以設(shè)置的，默認情況下join_buffer_size=256k。

show variables like '%join_buffer%';

索引嵌套循環(huán)連接

那還有沒有效率更加高的關(guān)聯(lián)查詢算法呢？索引嵌套循環(huán)連接(Index Nested-Loop Join)就是效率最高的，前提條件是被驅(qū)動表的關(guān)聯(lián)字段建立了索引。通過驅(qū)動表匹配條件直接與被驅(qū)動表的索引進行匹配，避免和內(nèi)存表的每條記錄去進行比較，這樣極大的減少了對內(nèi)存表的匹配次數(shù)。如下圖所示：

• 整體的開銷成本如下表所示：

因為索引查詢的成本基本一樣，為了降低開銷，驅(qū)動表是小表更加合適。

Hash Join(MySQL 8)

從MySQL8后面的版本開始廢棄塊嵌套循環(huán)連接，默認使用了Hash Join的方式。

• 塊嵌套循環(huán)連接：對于被連接的數(shù)據(jù)子集較小的情況下，它是個較好的選擇。
• Hash Join: 是做大數(shù)據(jù)集連接時的常用方式，優(yōu)化器使用兩個表中較?。ㄏ鄬^?。┑谋砝肑oin Key在內(nèi)存中建立散列值，然后掃描較大的表并探測散列值，找出與Hash表匹配的行。它能夠很好的工作于沒有索引的大表和并行查詢的環(huán)境中，并提供最好的性能。Hash Join只能應(yīng)用于等值連接，這是由Hash的特點決定的。

優(yōu)化建議

前面講解了關(guān)聯(lián)查詢Join的實現(xiàn)原理，那么對于關(guān)聯(lián)查詢模式我們可以從中總結(jié)出下面的一些優(yōu)化點：

• 優(yōu)先保證被驅(qū)動表的連接字段建立索引，因為建立索引的查詢方式是效率最高的。
• left join或者 right join這種外連接的情況，要保證小表（小結(jié)果集）作為驅(qū)動表，大表(大結(jié)果集)作為被驅(qū)動表，這樣性能更好。
• 在查詢字段的話，要避免select *或者select 全部字段，而是按需，因為這些字段也會加入到join buffer中。
• 能夠直接多表關(guān)聯(lián)的盡量直接關(guān)聯(lián)，不用子查詢，因為子查詢的效率更加低。
• 在sql的查詢計劃的extra中，盡量避免出現(xiàn)Using join buffer，有這個表示使用了塊嵌套循環(huán)連接算法，盡量通過索引去解決。
• 盡量避免超過3張表以上的關(guān)聯(lián)查詢。

總結(jié)

本文分享了日常工作中使用非常頻繁的關(guān)聯(lián)查詢，主要關(guān)注關(guān)聯(lián)查詢的實現(xiàn)原理，這樣我們可以在平時寫關(guān)聯(lián)查詢的SQL時候性能才會更佳。如果本文對你有幫助，請留下一個贊吧。

以上就是MySQL關(guān)聯(lián)查詢Join的實現(xiàn)原理和優(yōu)化建議的詳細內(nèi)容，更多關(guān)于MySQL關(guān)聯(lián)查詢Join的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論