詳細(xì)說(shuō)明MySQL JOIN原理介紹

一．Join語(yǔ)法概述

join 用于多表中字段之間的聯(lián)系，語(yǔ)法如下：

... FROM table1 INNER LEFT RIGHT JOIN table2 ON conditiona

table1:左表；table2:右表。

JOIN 按照功能大致分為如下三類(lèi)：

INNER JOIN（內(nèi)連接,或等值連接）：取得兩個(gè)表中存在連接匹配關(guān)系的記錄。

LEFT JOIN（左連接）：取得左表（table1）完全記錄，即是右表（table2）并無(wú)對(duì)應(yīng)匹配記錄。

RIGHT JOIN（右連接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）并無(wú)匹配對(duì)應(yīng)記錄。

注意：mysql不支持Full join,不過(guò)可以通過(guò)UNION 關(guān)鍵字來(lái)合并 LEFT JOIN 與 RIGHT JOIN來(lái)模擬FULL join.

先看一下實(shí)驗(yàn)的兩張表：

以上兩張表是我們測(cè)試的基礎(chǔ)，然后看一下索引，comments_for這個(gè)表comments_id是有索引的，ID為主鍵。

最近被公司某一開(kāi)發(fā)問(wèn)道JOIN了MySQL JOIN的問(wèn)題，細(xì)數(shù)之下發(fā)下我對(duì)MySQL JOIN的理解并不是很深刻，所以也查看了很多文檔，最后在InsideMySQL公眾號(hào)看到了兩篇關(guān)于JOIN的分析，感覺(jué)寫(xiě)的太好了，拿出來(lái)分享一下我對(duì)于JOIN的實(shí)際測(cè)試吧。下面先介紹一下MySQL關(guān)于JOIN的算法，總共分為三種（來(lái)源為InsideMySQL）：

MySQL是只支持一種JOIN算法Nested-Loop Join（嵌套循環(huán)鏈接），不像其他商業(yè)數(shù)據(jù)庫(kù)可以支持哈希鏈接和合并連接，不過(guò)MySQL的Nested-Loop Join（嵌套循環(huán)鏈接）也是有很多變種，能夠幫助MySQL更高效的執(zhí)行JOIN操作：

（1）Simple Nested-Loop Join（圖片為InsideMySQL取來(lái)）

這個(gè)算法相對(duì)來(lái)說(shuō)就是很簡(jiǎn)單了，從驅(qū)動(dòng)表中取出R1匹配S表所有列，然后R2，R3,直到將R表中的所有數(shù)據(jù)匹配完，然后合并數(shù)據(jù)，可以看到這種算法要對(duì)S表進(jìn)行RN次訪問(wèn)，雖然簡(jiǎn)單，但是相對(duì)來(lái)說(shuō)開(kāi)銷(xiāo)還是太大了

（2）Index Nested-Loop Join，實(shí)現(xiàn)方式如下圖：

索引嵌套聯(lián)系由于非驅(qū)動(dòng)表上有索引，所以比較的時(shí)候不再需要一條條記錄進(jìn)行比較，而可以通過(guò)索引來(lái)減少比較，從而加速查詢(xún)。這也就是平時(shí)我們?cè)谧鲫P(guān)聯(lián)查詢(xún)的時(shí)候必須要求關(guān)聯(lián)字段有索引的一個(gè)主要原因。

這種算法在鏈接查詢(xún)的時(shí)候，驅(qū)動(dòng)表會(huì)根據(jù)關(guān)聯(lián)字段的索引進(jìn)行查找，當(dāng)在索引上找到了符合的值，再回表進(jìn)行查詢(xún)，也就是只有當(dāng)匹配到索引以后才會(huì)進(jìn)行回表。至于驅(qū)動(dòng)表的選擇，MySQL優(yōu)化器一般情況下是會(huì)選擇記錄數(shù)少的作為驅(qū)動(dòng)表，但是當(dāng)SQL特別復(fù)雜的時(shí)候不排除會(huì)出現(xiàn)錯(cuò)誤選擇。

在索引嵌套鏈接的方式下，如果非驅(qū)動(dòng)表的關(guān)聯(lián)鍵是主鍵的話，這樣來(lái)說(shuō)性能就會(huì)非常的高，如果不是主鍵的話，關(guān)聯(lián)起來(lái)如果返回的行數(shù)很多的話，效率就會(huì)特別的低，因?yàn)橐�多次的回表操作。先關(guān)聯(lián)索引，然后根據(jù)二級(jí)索引的主鍵ID進(jìn)行回表的操作。這樣來(lái)說(shuō)的話性能相對(duì)就會(huì)很差。

（3）Block Nested-Loop Join，實(shí)現(xiàn)如下：

在有索引的情況下，MySQL會(huì)嘗試去使用Index Nested-Loop Join算法，在有些情況下，可能Join的列就是沒(méi)有索引，那么這時(shí)MySQL的選擇絕對(duì)不會(huì)是最先介紹的Simple Nested-Loop Join算法，而是會(huì)優(yōu)先使用Block Nested-Loop Join的算法。

Block Nested-Loop Join對(duì)比Simple Nested-Loop Join多了一個(gè)中間處理的過(guò)程，也就是join buffer，使用join buffer將驅(qū)動(dòng)表的查詢(xún)JOIN相關(guān)列都給緩沖到了JOIN BUFFER當(dāng)中，然后批量與非驅(qū)動(dòng)表進(jìn)行比較，這也來(lái)實(shí)現(xiàn)的話，可以將多次比較合并到一次，降低了非驅(qū)動(dòng)表的訪問(wèn)頻率。也就是只需要訪問(wèn)一次S表。這樣來(lái)說(shuō)的話，就不會(huì)出現(xiàn)多次訪問(wèn)非驅(qū)動(dòng)表的情況了，也只有這種情況下才會(huì)訪問(wèn)join buffer。

在MySQL當(dāng)中，我們可以通過(guò)參數(shù)join_buffer_size來(lái)設(shè)置join buffer的值，然后再進(jìn)行操作。默認(rèn)情況下join_buffer_size=256K，在查找的時(shí)候MySQL會(huì)將所有的需要的列緩存到j(luò)oin buffer當(dāng)中，包括select的列，而不是僅僅只緩存關(guān)聯(lián)列。在一個(gè)有N個(gè)JOIN關(guān)聯(lián)的SQL當(dāng)中會(huì)在執(zhí)行時(shí)候分配N(xiāo)-1個(gè)join buffer。

上面介紹完了，下面看一下具體的列子

（1）全表JOIN

EXPLAIN SELECT * FROM comments gc
JOIN comments_for gcf ON gc.comments_id=gcf.comments_id;

看一下輸出信息：

可以看到在全表掃描的時(shí)候comments_for 作為了驅(qū)動(dòng)表，此事因?yàn)殛P(guān)聯(lián)字段是有索引的，所以對(duì)索引idx_commentsid進(jìn)行了一個(gè)全索引掃描去匹配非驅(qū)動(dòng)表comments ，每次能夠匹配到一行。此時(shí)使用的就是Index Nested-Loop Join，通過(guò)索引進(jìn)行了全表的匹配，我們可以看到因?yàn)閏omments_for 表的量級(jí)遠(yuǎn)小于comments ，所以說(shuō)MySQL優(yōu)先選擇了小表comments_for 作為了驅(qū)動(dòng)表。

（2）全表JOIN+篩選條件

SELECT * FROM comments gc
JOIN comments_for gcf ON gc.comments_id=gcf.comments_id
WHERE gc.comments_id =2056

此時(shí)使用的是Index Nested-Loop Join，先對(duì)驅(qū)動(dòng)表comments 的主鍵進(jìn)行篩選，符合一條，對(duì)非驅(qū)動(dòng)表comments_for 的索引idx_commentsid進(jìn)行seek匹配，最終匹配結(jié)果預(yù)計(jì)為影響一條，這樣就是僅僅對(duì)非驅(qū)動(dòng)表的idx_commentsid索引進(jìn)行了一次訪問(wèn)操作，效率相對(duì)來(lái)說(shuō)還是非常高的。

（3）看一下關(guān)聯(lián)字段是沒(méi)有索引的情況：

EXPLAIN SELECT * FROM comments gc
JOIN comments_for gcf ON gc.order_id=gcf.product_id

我們看一下執(zhí)行計(jì)劃：

從執(zhí)行計(jì)劃我們就可以看出，這個(gè)表JOIN就是使用了Block Nested-Loop Join來(lái)進(jìn)行表關(guān)聯(lián)，先把comments_for （只有57行）這個(gè)小表作為驅(qū)動(dòng)表，然后將comments_for 的需要的數(shù)據(jù)緩存到JOIN buffer當(dāng)中，批量對(duì)comments 表進(jìn)行掃描，也就是只進(jìn)行一次匹配，前提是join buffer足夠大能夠存下comments_for的緩存數(shù)據(jù)。

而且我們看到執(zhí)行計(jì)劃當(dāng)中已經(jīng)很明確的提示：Using where; Using join buffer (Block Nested Loop)

一般情況出現(xiàn)這種情況就證明我們的SQL需要優(yōu)化了。

要注意的是這種情況下，MySQL也會(huì)選擇Simple Nested-Loop Join這種暴力的方法，我還沒(méi)搞懂他這個(gè)優(yōu)化器是怎么選擇的，但是一般是使用Block Nested-Loop Join，因?yàn)镃BO是基于開(kāi)銷(xiāo)的，Block Nested-Loop Join的性能相對(duì)于Simple Nested-Loop Join是要好很多的。

（4）看一下left join

EXPLAIN SELECT * FROM comments gc
LEFT JOIN comments_for gcf ON gc.comments_id=gcf.comments_id

看一下執(zhí)行計(jì)劃：

這種情況，由于我們的關(guān)聯(lián)字段是有索引的，所以說(shuō)Index Nested-Loop Join，只不過(guò)當(dāng)沒(méi)有篩選條件的時(shí)候會(huì)選擇第一張表作為驅(qū)動(dòng)表去進(jìn)行JOIN，去關(guān)聯(lián)非驅(qū)動(dòng)表的索引進(jìn)行Index Nested-Loop Join。

如果加上篩選條件gc.comments_id =2056的話，這樣就會(huì)篩選出一條對(duì)非驅(qū)動(dòng)表進(jìn)行Index Nested-Loop Join，這樣效率是很高的。

如果是下面這種：

EXPLAIN SELECT * FROM comments_for gcf
LEFT JOIN comments gc ON gc.comments_id=gcf.comments_id
WHERE gcf.comments_id =2056

通過(guò)gcf表進(jìn)行篩選的話，就會(huì)默認(rèn)選擇gcf表作為驅(qū)動(dòng)表，因?yàn)楹苊黠@他進(jìn)行過(guò)了篩選，匹配的條件會(huì)很少，具體可以看下執(zhí)行計(jì)劃：

此，join基本上已經(jīng)很明了了，未完待續(xù)中，歡迎大家指出錯(cuò)誤，我會(huì)認(rèn)真改正。。。。

以上就是詳解MySQL JOIN原理介紹的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！