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

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

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

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

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

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

• 左連接(LEFT JOIN)

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

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

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

• 右連接(Right JOIN)

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

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

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

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

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

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

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

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

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

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

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

• 當(dāng)然MySQL默認(rèn)沒有采用這種算法。

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

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

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

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

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

show variables like '%join_buffer%';

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

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

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

因?yàn)樗饕樵兊某杀净疽粯樱瑸榱私档烷_銷，驅(qū)動(dòng)表是小表更加合適。

Hash Join(MySQL 8)

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

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

優(yōu)化建議

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

• 優(yōu)先保證被驅(qū)動(dòng)表的連接字段建立索引，因?yàn)榻⑺饕牟樵兎绞绞切首罡叩摹?/li>
• left join或者 right join這種外連接的情況，要保證小表（小結(jié)果集）作為驅(qū)動(dòng)表，大表(大結(jié)果集)作為被驅(qū)動(dòng)表，這樣性能更好。
• 在查詢字段的話，要避免select *或者select 全部字段，而是按需，因?yàn)檫@些字段也會加入到join buffer中。
• 能夠直接多表關(guān)聯(lián)的盡量直接關(guān)聯(lián)，不用子查詢，因?yàn)樽硬樵兊男矢拥汀?/li>
• 在sql的查詢計(jì)劃的extra中，盡量避免出現(xiàn)Using join buffer，有這個(gè)表示使用了塊嵌套循環(huán)連接算法，盡量通過索引去解決。
• 盡量避免超過3張表以上的關(guān)聯(lián)查詢。

總結(jié)

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

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

最新評論