一、聯(lián)接過(guò)程介紹

為了后面一些測(cè)試案例，我們事先創(chuàng)建了兩張表，表數(shù)據(jù)如下：

聯(lián)接操作的本質(zhì)就是把各個(gè)聯(lián)接表中的記錄都取出來(lái)依次匹配的組合加入結(jié)果集并返回給用戶(hù)。如果沒(méi)有任何條件的話(huà)，多表聯(lián)接起來(lái)產(chǎn)生的笛卡爾積可能是非常巨大的。比方說(shuō)3個(gè)100行記錄的表連接起來(lái)產(chǎn)生的笛卡爾積就有100×100×100=1000000行數(shù)據(jù)！所以在連接的時(shí)候過(guò)濾掉特定記錄組合是有必要的，在連接查詢(xún)中的過(guò)濾條件可以分成兩種，我們以一個(gè)JOIN查詢(xún)?yōu)槔?/p>

?

• 涉及單表的條件

WHERE條件也可以稱(chēng)為搜索條件，比如t1.m1 > 1是只針對(duì)t1表的過(guò)濾條件，t2.n2 < ‘d’是只針對(duì)t2表的過(guò)濾條件。

• 涉及兩表的條件

比如t1.m1 = t2.m2、t1.n1 > t2.n2等，這些條件中涉及到了兩個(gè)表，我們稍后會(huì)仔細(xì)分析這種過(guò)濾條件是如何使用的。

在這個(gè)查詢(xún)中我們指明了這三個(gè)過(guò)濾條件：

1. t1.m1 > 1

2. t1.m1 = t2.m2

3. t2.n2 < ‘d’

那么這個(gè)連接查詢(xún)的大致執(zhí)行過(guò)程如下：

首先確定第一個(gè)需要查詢(xún)的表，這個(gè)表稱(chēng)之為驅(qū)動(dòng)表。怎樣在單表中執(zhí)行查詢(xún)語(yǔ)句，只需要選取代價(jià)最小的那種訪(fǎng)問(wèn)方法去執(zhí)行單表查詢(xún)語(yǔ)句就好了（就是說(shuō)從const、ref、ref_or_null、range、index、all這些執(zhí)行方法中選取代價(jià)最小的去執(zhí)行查詢(xún)）。此處假設(shè)使用t1作為驅(qū)動(dòng)表，那么就需要到t1表中找滿(mǎn)足t1.m1 > 1的記錄，假設(shè)這里并沒(méi)有給t1字段添加索引，所以此處查詢(xún)t1表的訪(fǎng)問(wèn)方法就設(shè)定為all吧，也就是采用全表掃描的方式執(zhí)行單表查詢(xún)。關(guān)于如何提升連接查詢(xún)的性能我們之后再說(shuō)，現(xiàn)在先把基本概念捋清楚哈。所以查詢(xún)過(guò)程就如下圖所示：

針對(duì)上一步驟中從驅(qū)動(dòng)表產(chǎn)生的結(jié)果集中的每一條記錄，分別需要到t2表中查找匹配的記錄，所謂匹配的記錄，指的是符合過(guò)濾條件的記錄。因?yàn)槭歉鶕?jù)t1表中的記錄去找t2表中的記錄，所以t2表也可以被稱(chēng)之為被驅(qū)動(dòng)表。比如上一步驟從驅(qū)動(dòng)表中得到了2條記錄，所以需要查詢(xún)2次t2表。此時(shí)涉及兩個(gè)表的列的過(guò)濾條件t1.m1 = t2.m2就派上用場(chǎng)了：

• 當(dāng)t1.m1 = 2時(shí)，過(guò)濾條件t1.m1 = t2.m2就相當(dāng)于t2.m2 = 2，所以此時(shí)t2表相當(dāng)于有了t1.m1 = 2、t2.n2 < ‘d’這兩個(gè)過(guò)濾條件，然后到t2表中執(zhí)行單表查詢(xún)。
• 當(dāng)t1.m1 = 3時(shí)，過(guò)濾條件t1.m1 = t2.m2就相當(dāng)于t2.m2 = 3，所以此時(shí)t2表相當(dāng)于有了t1.m1 = 3、t2.n2 < ‘d’這兩個(gè)過(guò)濾條件，然后到t2表中執(zhí)行單表查詢(xún)。

所以整個(gè)連接查詢(xún)的執(zhí)行過(guò)程就如下圖所示：

也就是說(shuō)整個(gè)連接查詢(xún)最后的結(jié)果只有兩條符合過(guò)濾條件的記錄：

從上邊兩個(gè)步驟可以看出來(lái)，我們上邊說(shuō)的這個(gè)兩表聯(lián)接查詢(xún)共需要查詢(xún)1次t1表，2次t2表。當(dāng)然這是在特定的過(guò)濾條件下的結(jié)果，如果我們把t1.m1 > 1這個(gè)條件去掉，那么從t1表中查出的記錄就有3條，就需要查詢(xún)3次t3表了。也就是說(shuō)在兩表連接查詢(xún)中，驅(qū)動(dòng)表只需要訪(fǎng)問(wèn)一次，被驅(qū)動(dòng)表可能被訪(fǎng)問(wèn)多次，這種方式在MySQL中有一個(gè)專(zhuān)有名詞，叫Nested-Loops Join（嵌套循環(huán)聯(lián)接）。我們?cè)谡嬲褂肕ySQL的時(shí)候表動(dòng)不動(dòng)就是幾百上千萬(wàn)數(shù)據(jù)，如果都按照Nested-Loops Join算法，一次Join查詢(xún)的代價(jià)也太大了。所以下面就來(lái)看看MySQL支持的Join算法都有哪些？

二、聯(lián)接算法介紹?

聯(lián)接算法是MySQL數(shù)據(jù)庫(kù)用于處理聯(lián)接的物理策略。目前MySQL數(shù)據(jù)庫(kù)僅支持Nested-Loops Join算法。而MySQL的分支版本MariaDB除了支持Nested-Loops Join算法外，還支持Classic Hash Join算法。當(dāng)聯(lián)接的表上有索引時(shí)，Nested-Loops Join是非常高效的算法。根據(jù)B+樹(shù)的特性，其聯(lián)接的時(shí)間復(fù)雜度為O(N)，若沒(méi)有索引，則可視為最壞的情況，時(shí)間復(fù)雜度為O(N2)。MySQL數(shù)據(jù)庫(kù)根據(jù)不同的使用場(chǎng)合，支持兩種Nested-Loops Join算法，一種是Simple Nested-Loops Join（NLJ）算法，另一種是Block Nested-Loops Join（BNL）算法。

在講述MySQL的Join類(lèi)型與算法前，看看兩張表的Join的過(guò)程：

上圖的Fetch階段是指當(dāng)內(nèi)表關(guān)聯(lián)的列是輔助索引時(shí)，但是需要訪(fǎng)問(wèn)表中的數(shù)據(jù)，那么這時(shí)就需要再訪(fǎng)問(wèn)主鍵索引才能得到數(shù)據(jù)的過(guò)程，不論表的存儲(chǔ)引擎是InnoDB存儲(chǔ)引擎還是MyISAM，這都是無(wú)法避免的，只是MyISAM的回表速度要快點(diǎn)，因?yàn)槠漭o助索引存放的就是指向記錄的指針，而InnoDB存儲(chǔ)引擎是索引組織表，需要再次通過(guò)索引查找才能定位數(shù)據(jù)。

Fetch階段也不是必須存在的，如果是聚集索引聯(lián)接，那么直接就能得到數(shù)據(jù)，無(wú)需回表，也就沒(méi)有Fetch這個(gè)階段。另外，上述給出了兩張表之間的Join過(guò)程，多張表的Join就是繼續(xù)上述這個(gè)過(guò)程。

接著計(jì)算兩張表Join的成本，這里有下列幾種概念：

評(píng)判一個(gè)Join算法是否優(yōu)劣，就是查看上述這些操作的開(kāi)銷(xiāo)是否比較小。當(dāng)然，這還要考慮I/O的訪(fǎng)問(wèn)方式，順序還是隨機(jī)，總之Join的調(diào)優(yōu)也是門(mén)藝術(shù)，并非想象的那么簡(jiǎn)單。

• Simple Nested-Loops Join（SNLJ，簡(jiǎn)單嵌套循環(huán)聯(lián)接）

Simple Nested-Loops Join算法相當(dāng)簡(jiǎn)單、直接。即外表（驅(qū)動(dòng)表）中的每一條記錄與內(nèi)表（被驅(qū)動(dòng)表）中的記錄進(jìn)行比較判斷。對(duì)于兩表連接來(lái)說(shuō)，驅(qū)動(dòng)表只會(huì)被訪(fǎng)問(wèn)一遍，但被驅(qū)動(dòng)表卻要被訪(fǎng)問(wèn)到好多遍，具體訪(fǎng)問(wèn)幾遍取決于對(duì)驅(qū)動(dòng)表執(zhí)行單表查詢(xún)后的結(jié)果集中的記錄條數(shù)。對(duì)于內(nèi)連接來(lái)說(shuō)，選取哪個(gè)表為驅(qū)動(dòng)表都沒(méi)關(guān)系，而外連接的驅(qū)動(dòng)表是固定的，也就是說(shuō)左（外）連接的驅(qū)動(dòng)表就是左邊的那個(gè)表，右（外）連接的驅(qū)動(dòng)表就是右邊的那個(gè)表。

用偽代碼表示一下這個(gè)過(guò)程就是這樣：

下圖能更好地顯示整個(gè)SNLJ的過(guò)程：

其中R表為外部表（Outer Table），S表為內(nèi)部表（Inner Table）。這是一個(gè)最簡(jiǎn)單的算法，這個(gè)算法的開(kāi)銷(xiāo)其實(shí)非常大。假設(shè)在兩張表R和S上進(jìn)行聯(lián)接的列都不含有索引，外表的記錄數(shù)為RN，內(nèi)表的記錄數(shù)位SN。根據(jù)上一節(jié)對(duì)于Join算法的評(píng)判標(biāo)準(zhǔn)來(lái)看，SNLJ的開(kāi)銷(xiāo)如下表所示：

可以看到讀取記錄數(shù)的成本和比較次數(shù)的成本都是SN*RN，也就是笛卡兒積。假設(shè)外表內(nèi)表都是1萬(wàn)條記錄，那么其讀取的記錄數(shù)量和Join的比較次數(shù)都需要上億。實(shí)際上數(shù)據(jù)庫(kù)并不會(huì)使用到SNLJ算法。

• Index?Nested-Loops Join（INLJ，基于索引的嵌套循環(huán)聯(lián)接）

SNLJ算法雖然簡(jiǎn)單明了，但是也是相當(dāng)?shù)拇直?，需要多次訪(fǎng)問(wèn)內(nèi)表（每一次都是全表掃描）。因此，在Join的優(yōu)化時(shí)候，通常都會(huì)建議在內(nèi)表建立索引，以此降低Nested-Loop Join算法的開(kāi)銷(xiāo)，減少內(nèi)表掃描次數(shù)，MySQL數(shù)據(jù)庫(kù)中使用較多的就是這種算法，以下稱(chēng)為INLJ。來(lái)看這種算法的偽代碼：

由于內(nèi)表上有索引，所以比較的時(shí)候不再需要一條條記錄進(jìn)行比較，而可以通過(guò)索引來(lái)減少比較，從而加速查詢(xún)。整個(gè)過(guò)程如下圖所示：

可以看到外表中的每條記錄通過(guò)內(nèi)表的索引進(jìn)行訪(fǎng)問(wèn)，就是讀取外部表一行數(shù)據(jù)，然后去內(nèi)部表索引進(jìn)行二分查找匹配；而一般B+樹(shù)的高度為3~4層，也就是說(shuō)匹配一次的io消耗也就3~4次，因此索引查詢(xún)的成本是比較固定的，故優(yōu)化器都傾向于使用記錄數(shù)少的表作為外表（這里是否又會(huì)存在潛在的問(wèn)題呢？）。故INLJ的算法成本如下表所示：

上表Smatch表示通過(guò)索引找到匹配的記錄數(shù)量。同時(shí)可以發(fā)現(xiàn)，通過(guò)索引可以大幅降低內(nèi)表的Join的比較次數(shù)，每次比較1條外表的記錄，其實(shí)就是一次indexlookup（索引查找），而每次index lookup的成本就是樹(shù)的高度，即IndexHeight。

INLJ的算法并不復(fù)雜，也算簡(jiǎn)單易懂。但是效率是否能達(dá)到用戶(hù)的預(yù)期呢？其實(shí)如果是通過(guò)表的主鍵索引進(jìn)行Join，即使是大數(shù)據(jù)量的情況下，INLJ的效率亦是相當(dāng)不錯(cuò)的。因?yàn)樗饕檎业拈_(kāi)銷(xiāo)非常小，并且訪(fǎng)問(wèn)模式也是順序的（假設(shè)大多數(shù)聚集索引的訪(fǎng)問(wèn)都是比較順序的）。

大部分人詬病MySQL的INLJ慢，主要是因?yàn)樵谶M(jìn)行Join的時(shí)候可能用到的索引并不是主鍵的聚集索引，而是輔助索引，這時(shí)INLJ的過(guò)程又需要多一步Fetch的過(guò)程，而且這個(gè)過(guò)程開(kāi)銷(xiāo)會(huì)相當(dāng)?shù)拇螅?/p>

由于訪(fǎng)問(wèn)的是輔助索引，如果查詢(xún)需要訪(fǎng)問(wèn)聚集索引上的列，那么必要需要進(jìn)行回表取數(shù)據(jù)，看似每條記錄只是多了一次回表操作，但這才是INLJ算法最大的弊端。首先，輔助索引的index lookup是比較隨機(jī)I/O訪(fǎng)問(wèn)操作。其次，根據(jù)index lookup再進(jìn)行回表又是一個(gè)隨機(jī)的I/O操作。所以說(shuō)，INLJ最大的弊端是其可能需要大量的離散操作，這在SSD出現(xiàn)之前是最大的瓶頸。而即使SSD的出現(xiàn)大幅提升了隨機(jī)的訪(fǎng)問(wèn)性能，但是對(duì)比順序I/O，其還是慢了很多，依然不在一個(gè)數(shù)量級(jí)上。

另外，在INNER JOIN中，兩張聯(lián)接表的順序是可以變換的，即R INNER JOIN S ON Condition P等效于S INNER JOIN R ON Condition P。根據(jù)前面描述的Simple Nested-Loops Join算法，優(yōu)化器在一般情況下總是選擇將聯(lián)接列含有索引的表作為內(nèi)部表。如果兩張表R和S在聯(lián)接列上都有索引，并且索引的高度相同，那么優(yōu)化器會(huì)選擇記錄數(shù)少的表作為外部表，這是因?yàn)閮?nèi)部表的掃描次數(shù)總是索引的高度，與記錄的數(shù)量無(wú)關(guān)。所以，聯(lián)接列只要有一個(gè)字段有索引即可，但最好是數(shù)據(jù)集多的表有索引；但是，但有WHERE條件的時(shí)候又另當(dāng)別論了。

然后我們給上面的 t1.m1 和 t2.m2 分別添加主鍵，看一下下面這個(gè)內(nèi)聯(lián)接的執(zhí)行計(jì)劃：

可以看到執(zhí)行計(jì)劃是將 t1 表作為驅(qū)動(dòng)表，將 t2 表作為被驅(qū)動(dòng)表，因?yàn)閷?duì) t2.m2 列的條件是等值查找，比如 t2.m2=2、t2.m2=3 等，所以MySQL把在聯(lián)接查詢(xún)中對(duì)被驅(qū)動(dòng)表使用主鍵值或者唯一二級(jí)索引列的值進(jìn)行等值查找的查詢(xún)執(zhí)行方式稱(chēng)之為eq_ref。

Tips：如果被驅(qū)動(dòng)表使用了非唯一二級(jí)索引列的值進(jìn)行等值查詢(xún)，則查詢(xún)方式為 ref。另外，如果被驅(qū)動(dòng)表使用了主鍵或者唯一二級(jí)索引列的值進(jìn)行等值查找，但主鍵或唯一二級(jí)索引如果有多個(gè)列的話(huà)，則查詢(xún)類(lèi)型也會(huì)變成 ref。

有時(shí)候聯(lián)接查詢(xún)的查詢(xún)列表和過(guò)濾條件中可能只涉及被驅(qū)動(dòng)表的部分列，而這些列都是某個(gè)索引的一部分，這種情況下即使不能使用eq_ref、ref、ref_or_null或者range這些訪(fǎng)問(wèn)方法執(zhí)行對(duì)被驅(qū)動(dòng)表的查詢(xún)的話(huà)，也可以使用索引掃描，也就是index的訪(fǎng)問(wèn)方法來(lái)查詢(xún)被驅(qū)動(dòng)表。所以我們建議在真實(shí)工作中最好不要使用*作為查詢(xún)列表，最好把真實(shí)用到的列作為查詢(xún)列表。

這里為什么將 t1 作為驅(qū)動(dòng)表？因?yàn)楸?t1 中的記錄少于表 t2，這樣聯(lián)接需要匹配的次數(shù)就少了，所以SQL優(yōu)化器選擇表 t1 作為驅(qū)動(dòng)表。

若我們執(zhí)行的SQL帶有WHERE條件時(shí)呢？看看不一樣的執(zhí)行計(jì)劃。如果條件為表 t1 的主鍵，執(zhí)行計(jì)劃如下：

可以看到執(zhí)行計(jì)劃算是極優(yōu)，同時(shí) t1 表還是驅(qū)動(dòng)表，因?yàn)榻?jīng)過(guò)WHERE條件過(guò)濾后的數(shù)據(jù)只有一條（我們知道在單表中使用主鍵值或者唯一二級(jí)索引列的值進(jìn)行等值查找的方式稱(chēng)之為const，所以我們可以看到 t1 的type為const；如果這里條件為 t1.m1 > 1，那么自然 type 就為 range），同時(shí) t2.m2 也是主鍵，自然只有一條數(shù)據(jù)，type也為const。

如果WHERE條件是一個(gè)沒(méi)有索引的字段呢？執(zhí)行計(jì)劃如下：

從執(zhí)行計(jì)劃上看跟不加WHERE條件幾乎差不多，但是可以看到filtered為33%了，而不是100%，說(shuō)明需要返回的數(shù)據(jù)量變少了。另外Extra字段中標(biāo)識(shí)使用了WHERE條件過(guò)濾。

如果WHERE條件是一個(gè)有索引的字段呢（比如給 t2.n2 添加一個(gè)非唯一二級(jí)索引）？這里就不得不提MySQL一個(gè)非常重要的特性了，pushed-down conditions（條件下推）優(yōu)化。就是把索引條件下推到存儲(chǔ)引擎層進(jìn)行數(shù)據(jù)的過(guò)濾并返回過(guò)濾后的數(shù)據(jù)。那么此時(shí)的執(zhí)行計(jì)劃就如下：

可以看到 t2 表成為了驅(qū)動(dòng)表（經(jīng)過(guò)二級(jí)索引過(guò)濾后數(shù)據(jù)只有1條，所以這里使用到ref的訪(fǎng)問(wèn)方法）。

如果我們把 t2.n2 換為范圍查詢(xún)呢？看執(zhí)行計(jì)劃如下：

可以看到雖然WHERE條件有索引，但由于?t2.n2>’a’ 過(guò)濾后的數(shù)據(jù)還是比 t1 表多，所以?xún)?yōu)化器就選擇了 t1 表作為驅(qū)動(dòng)表。而此時(shí) t2 表的查詢(xún)條件類(lèi)似如下：

由于 t2.m2 是主鍵，t2.n2 有二級(jí)索引，優(yōu)化器平衡了一下，可能覺(jué)得 t2.n2 過(guò)濾后的數(shù)據(jù)占全表比例太大，回表的成本比直接訪(fǎng)問(wèn)主鍵成本要高，所以就直接使用了主鍵。如果說(shuō) t2.n2 過(guò)濾后的數(shù)據(jù)占全表數(shù)據(jù)比例較小，是有可能會(huì)選擇 idx_n2 索引。

最后，我們使用 t1.n1 與 t2.n2 作為條件，看一下執(zhí)行計(jì)劃如下：

一切按照我們預(yù)想的結(jié)果在工作，就是由于 t2.n2 不是主鍵或唯一索引，type類(lèi)型變成了 ref。

Tips：雖然在INNER JOIN中可以使用pushed-down conditions的優(yōu)化方式，但是不能直接在OUTER JOIN中使用該方式，因?yàn)橛行┎粷M(mǎn)足聯(lián)接條件的記錄會(huì)通過(guò)外部表行的方式再次添加到結(jié)果中，因此需要有條件地使用pushed-down conditions的優(yōu)化。在優(yōu)化器內(nèi)部對(duì)于聯(lián)接查詢(xún)會(huì)設(shè)置一個(gè)標(biāo)志來(lái)表示是否啟用pushed-down conditions的過(guò)濾。

• Block Nested-Loops Join（BNL，基于塊的嵌套循環(huán)聯(lián)接）

掃描一個(gè)表的過(guò)程其實(shí)是先把這個(gè)表從磁盤(pán)上加載到內(nèi)存中，然后從內(nèi)存中比較匹配條件是否滿(mǎn)足。但內(nèi)存里可能并不能完全存放的下表中所有的記錄，所以在掃描表前邊記錄的時(shí)候后邊的記錄可能還在磁盤(pán)上，等掃描到后邊記錄的時(shí)候可能內(nèi)存不足，所以需要把前邊的記錄從內(nèi)存中釋放掉。我們前邊又說(shuō)過(guò)，采用Simple Nested-Loop Join算法的兩表連接過(guò)程中，被驅(qū)動(dòng)表可是要被訪(fǎng)問(wèn)好多次的，如果這個(gè)被驅(qū)動(dòng)表中的數(shù)據(jù)特別多而且不能使用索引進(jìn)行訪(fǎng)問(wèn)，那就相當(dāng)于要從磁盤(pán)上讀好幾次這個(gè)表，這個(gè)I/O代價(jià)就非常大了，所以我們得想辦法：盡量減少訪(fǎng)問(wèn)被驅(qū)動(dòng)表的次數(shù)。

當(dāng)被驅(qū)動(dòng)表中的數(shù)據(jù)非常多時(shí)，每次訪(fǎng)問(wèn)被驅(qū)動(dòng)表，被驅(qū)動(dòng)表的記錄會(huì)被加載到內(nèi)存中，在內(nèi)存中的每一條記錄只會(huì)和驅(qū)動(dòng)表結(jié)果集的一條記錄做匹配，之后就會(huì)被從內(nèi)存中清除掉。然后再?gòu)尿?qū)動(dòng)表結(jié)果集中拿出另一條記錄，再一次把被驅(qū)動(dòng)表的記錄加載到內(nèi)存中一遍，周而復(fù)始，驅(qū)動(dòng)表結(jié)果集中有多少條記錄，就得把被驅(qū)動(dòng)表從磁盤(pán)上加載到內(nèi)存中多少次。所以我們可不可以在把被驅(qū)動(dòng)表的記錄加載到內(nèi)存的時(shí)候，一次性和多條驅(qū)動(dòng)表中的記錄做匹配，這樣就可以大大減少重復(fù)從磁盤(pán)上加載被驅(qū)動(dòng)表的代價(jià)了。這也就是Block Nested-Loop Join算法的思想。

也就是說(shuō)在有索引的情況下，MySQL會(huì)嘗試去使用Index Nested-Loop Join算法，在有些情況下，可能Join的列就是沒(méi)有索引，那么這時(shí)MySQL的選擇絕對(duì)不會(huì)是最先介紹的Simple Nested-Loop Join算法，因?yàn)槟莻€(gè)算法太粗暴，不忍直視。數(shù)據(jù)量大些的復(fù)雜SQL估計(jì)幾年都可能跑不出結(jié)果。而B(niǎo)lock Nested-Loop Join算法較Simple Nested-Loop Join的改進(jìn)就在于可以減少內(nèi)表的掃描次數(shù)，甚至可以和Hash Join算法一樣，僅需掃描內(nèi)表一次。其使用Join Buffer（聯(lián)接緩沖）來(lái)減少內(nèi)部循環(huán)讀取表的次數(shù)。

可以看到相比Simple Nested-Loop Join算法，Block Nested-LoopJoin算法僅多了一個(gè)所謂的Join Buffer，為什么這樣就能減少內(nèi)表的掃描次數(shù)呢？下圖相比更好地解釋了Block Nested-Loop Join算法的運(yùn)行過(guò)程：

可以看到Join Buffer用以緩存聯(lián)接需要的列（所以再次提醒我們，最好不要把*作為查詢(xún)列表，只需要把我們關(guān)心的列放到查詢(xún)列表就好了，這樣還可以在join buffer中放置更多的記錄呢），然后以Join Buffer批量的形式和內(nèi)表中的數(shù)據(jù)進(jìn)行聯(lián)接比較。就上圖來(lái)看，記錄r1，r2 … rT的聯(lián)接僅需掃內(nèi)表一次，如果join buffer可以緩存所有的外表列，那么聯(lián)接僅需掃描內(nèi)外表各一次，從而大幅提升Join的性能。

MySQL數(shù)據(jù)庫(kù)使用Join Buffer的原則如下：

* 系統(tǒng)變量Join_buffer_size決定了Join Buffer的大小。

* Join Buffer可被用于聯(lián)接是ALL、index、和range的類(lèi)型。

* 每次聯(lián)接使用一個(gè)Join Buffer，因此多表的聯(lián)接可以使用多個(gè)Join Buffer。

* Join Buffer在聯(lián)接發(fā)生之前進(jìn)行分配，在SQL語(yǔ)句執(zhí)行完后進(jìn)行釋放。

* Join Buffer只存儲(chǔ)要進(jìn)行查詢(xún)操作的相關(guān)列數(shù)據(jù)，而不是整行的記錄。

Join_buffer_size變量

所以，Join Buffer并不是那么好用的。首先變量join_buffer_size用來(lái)控制Join Buffer的大小，調(diào)大后可以避免多次的內(nèi)表掃描，從而提高性能。也就是說(shuō)，當(dāng)MySQL的Join有使用到Block Nested-Loop Join，那么調(diào)大變量join_buffer_size才是有意義的。而前面的Index Nested-Loop Join如果僅使用索引進(jìn)行Join，那么調(diào)大這個(gè)變量則毫無(wú)意義。

變量join_buffer_size的默認(rèn)值是256K，顯然對(duì)于稍復(fù)雜的SQL是不夠用的。好在這個(gè)是會(huì)話(huà)級(jí)別的變量，可以在執(zhí)行前進(jìn)行擴(kuò)展。建議在會(huì)話(huà)級(jí)別進(jìn)行設(shè)置，而不是全局設(shè)置，因?yàn)楹茈y給一個(gè)通用值去衡量。另外，這個(gè)內(nèi)存是會(huì)話(huà)級(jí)別分配的，如果設(shè)置不好容易導(dǎo)致因無(wú)法分配內(nèi)存而導(dǎo)致的宕機(jī)問(wèn)題。

Join Buffer緩存對(duì)象

另外，Join Buffer緩存的對(duì)象是什么，這個(gè)問(wèn)題相當(dāng)關(guān)鍵和重要。然在MySQL的官方手冊(cè)中是這樣記錄的：Only columns of interest to the join are stored in the join buffer, not whole rows.

可以發(fā)現(xiàn)Join Buffer不是緩存外表的整行記錄，而是緩存“columns of interest”，具體指所有參與查詢(xún)的列都會(huì)保存到Join Buffer，而不是只有Join的列。比如下面的SQL語(yǔ)句，假設(shè)沒(méi)有索引，需要使用到Join Buffer進(jìn)行鏈接：

假設(shè)上述SQL語(yǔ)句的外表是a，內(nèi)表是b，那么存放在Join Buffer中的列是所有參與查詢(xún)的列，在這里就是（a.col1，a.col2，a.col3）。

通過(guò)上面的介紹，我們現(xiàn)在可以得到內(nèi)表的掃描次數(shù)為：

對(duì)于有經(jīng)驗(yàn)的DBA就可以預(yù)估需要分配的Join Buffer大小，然后盡量使得內(nèi)表的掃描次數(shù)盡可能的少，最優(yōu)的情況是只掃描內(nèi)表一次。

Join Buffer的分配

需要牢記的是，Join Buffer是在Join之前就進(jìn)行分配，并且每次Join就需要分配一次Join Buffer，所以假設(shè)有N張表參與Join，每張表之間通過(guò)Block Nested-Loop Join，那么總共需要分配N(xiāo)-1個(gè)Join Buffer，這個(gè)內(nèi)存容量是需要DBA進(jìn)行考量的。

在MySQL 5.6（包括MariaDB 5.3）中，優(yōu)化了Join Buffer在多張表之間聯(lián)接的內(nèi)存使用效率。MySQL 5.6將Join Buffer分為Regular join buffer和Incremental join buffer。假設(shè)B1是表t1和t2聯(lián)接使用的Join Buffer，B2是t1和t2聯(lián)接產(chǎn)生的結(jié)果和表t3進(jìn)行聯(lián)接使用的join buffer，那么：

* 如果B2是Regular join buffer，那么B2就會(huì)包含B1的Join Buffer中r1相關(guān)的列，以及表t2中相關(guān)的列。

* 如果B2是Incremental join buffer，那么B2包含表t2中的數(shù)據(jù)及一個(gè)指針，該指針指向B1中r1相對(duì)應(yīng)的數(shù)據(jù)。

因此，對(duì)于第一次聯(lián)接的表，使用的都是Regular join buffer，之后再聯(lián)接，則使用Incremental join buffer。又因?yàn)镮ncremental join buffer只包含指向之前Join Buffer中數(shù)據(jù)的指針，所以Join Buffer的內(nèi)存使用效率得到了大幅的提高。

此外，對(duì)于NULL類(lèi)型的列，其實(shí)不需要存放在Join Buffer中，而對(duì)于VARCHAR類(lèi)型的列，也是僅需最小的內(nèi)存即可，而不是以CHAR類(lèi)型在Join Buffer中保存。最后，在MySQL 5.5版本中，Join Buffer只能在INNER JOIN中使用，在OUTER JOIN中則不能使用，即Block Nested Loop算法不支持OUTER JOIN。從MySQL 5.6及MariaDB 5.3開(kāi)始，Join Buffer的使用得到了進(jìn)一步擴(kuò)展，在OUTER JOIN中使join buffer得到支持。

Block Nested-Loop Join開(kāi)銷(xiāo)

Block Nested-Loop Join極大的避免了內(nèi)表的掃描次數(shù)，如果Join Buffer可以緩存外表的數(shù)據(jù)，那么內(nèi)表的掃描僅需一次，這和Hash Join非常類(lèi)似。但是Block Nested-Loop Join依然沒(méi)有解決的是Join比較的次數(shù)，其仍然通過(guò)Join判斷式進(jìn)行比較。綜上所述，到目前為止各Join算法的成本比較如下所示：

這個(gè)算法很好測(cè)試，我們可以隨便構(gòu)建兩張沒(méi)有索引的字段進(jìn)行聯(lián)接，然后查看一下執(zhí)行計(jì)劃。下面是我在MySQL 5.7版本上的執(zhí)行計(jì)劃。

可以看到，SQL 執(zhí)行計(jì)劃的 Extra 列中提示 Using join buffer (Block Nested Loop)，很明顯使用了BNL算法。

另外，可以看出這條 SQL 先根據(jù)索引進(jìn)行了條件過(guò)濾，然后拿過(guò)濾后的結(jié)果集作為驅(qū)動(dòng)表，也是為了減少被驅(qū)動(dòng)表掃描次數(shù)。如果 t2.n2 沒(méi)有索引呢？使用 BNL 算法來(lái) join 的話(huà)，這個(gè)語(yǔ)句的執(zhí)行流程是這樣的，假設(shè)表 t1 是驅(qū)動(dòng)表，表 t2 是被驅(qū)動(dòng)表：

1. 把表 t1 的所有字段取出來(lái)，存入 join_buffer 中。

2. 掃描表 t2，取出每一行數(shù)據(jù)跟 join_buffer 中的數(shù)據(jù)進(jìn)行對(duì)比；如果不滿(mǎn)足 t1.m1=t2.m2，則跳過(guò)； 如果滿(mǎn)足 t1.m1=t2.m2，再判斷其他條件，也就是是否滿(mǎn)足 t2.n2>’c’ 的條件，如果是，就作為結(jié)果集的一部分返回，否則跳過(guò)。

對(duì)于表 t2 的每一行，判斷 join 是否滿(mǎn)足的時(shí)候，都需要遍歷 join_buffer 中的所有行。因此判斷等值條件的次數(shù)是 t1表行數(shù)*t2表行數(shù)，數(shù)據(jù)量稍微大點(diǎn)時(shí)，這個(gè)判斷的次數(shù)都是上億次。如果不想在表 t2 的字段 n2 上創(chuàng)建索引，又想減少比較次數(shù)。那么，有沒(méi)有兩全其美的辦法呢？這時(shí)候，我們可以考慮使用臨時(shí)表。使用臨時(shí)表的大致思路是：

1. 把表 t2 中滿(mǎn)足條件的數(shù)據(jù)放在臨時(shí)表 tmp_t 中；

2. 為了讓 join 使用 BKA 算法，給臨時(shí)表 tmp_t 的字段 n2 加上索引；

3. 讓表 t1 和 tmp_t 做 join 操作。

Block Nested-Loop Join影響

在使用 Block Nested-Loop Join(BNL) 算法時(shí)，可能會(huì)對(duì)被驅(qū)動(dòng)表做多次掃描。如果這個(gè)被驅(qū)動(dòng)表是一個(gè)大的冷數(shù)據(jù)表，除了會(huì)導(dǎo)致 IO 壓力大以外，還會(huì)對(duì) buffer poll 產(chǎn)生嚴(yán)重的影響。

如果了解 InnoDB 的 LRU 算法就會(huì)知道，由于 InnoDB 對(duì) Bufffer Pool 的 LRU 算法做了優(yōu)化，即：第一次從磁盤(pán)讀入內(nèi)存的數(shù)據(jù)頁(yè)，會(huì)先放在 old 區(qū)域。如果 1 秒之后這個(gè)數(shù)據(jù)頁(yè)不再被訪(fǎng)問(wèn)了，就不會(huì)被移動(dòng)到 LRU 鏈表頭部，這樣對(duì) Buffer Pool 的命中率影響就不大。

但是，如果一個(gè)使用 BNL 算法的 join 語(yǔ)句，多次掃描一個(gè)冷表，而且這個(gè)語(yǔ)句執(zhí)行時(shí)間超過(guò) 1 秒，就會(huì)在再次掃描冷表的時(shí)候，把冷表的數(shù)據(jù)頁(yè)移到 LRU 鏈表頭部。這種情況對(duì)應(yīng)的，是冷表的數(shù)據(jù)量小于整個(gè) Buffer Pool 的 3/8，能夠完全放入 old 區(qū)域的情況。如果這個(gè)冷表很大，就會(huì)出現(xiàn)另外一種情況：業(yè)務(wù)正常訪(fǎng)問(wèn)的數(shù)據(jù)頁(yè)，沒(méi)有機(jī)會(huì)進(jìn)入 young 區(qū)域。

由于優(yōu)化機(jī)制的存在，一個(gè)正常訪(fǎng)問(wèn)的數(shù)據(jù)頁(yè)，要進(jìn)入 young 區(qū)域，需要隔 1 秒后再次被訪(fǎng)問(wèn)到。但是，由于我們的 join 語(yǔ)句在循環(huán)讀磁盤(pán)和淘汰內(nèi)存頁(yè)，進(jìn)入 old 區(qū)域的數(shù)據(jù)頁(yè)，很可能在 1 秒之內(nèi)就被淘汰了。這樣，就會(huì)導(dǎo)致這個(gè) MySQL 實(shí)例的 Buffer Pool 在這段時(shí)間內(nèi)，young 區(qū)域的數(shù)據(jù)頁(yè)沒(méi)有被合理地淘汰。

也就是說(shuō)，這兩種情況都會(huì)影響 Buffer Pool 的正常運(yùn)作。 大表 join 操作雖然對(duì) IO 有影響，但是在語(yǔ)句執(zhí)行結(jié)束后，對(duì) IO 的影響也就結(jié)束了。但是，對(duì) Buffer Pool 的影響就是持續(xù)性的，需要依靠后續(xù)的查詢(xún)請(qǐng)求慢慢恢復(fù)內(nèi)存命中率。

為了減少這種影響，你可以考慮增大 join_buffer_size 的值，減少對(duì)被驅(qū)動(dòng)表的掃描次數(shù)。

也就是說(shuō)，BNL 算法對(duì)系統(tǒng)的影響主要包括三個(gè)方面： 可能會(huì)多次掃描被驅(qū)動(dòng)表，占用磁盤(pán) IO 資源； 判斷 join 條件需要執(zhí)行 M*N 次對(duì)比（M、N 分別是兩張表的行數(shù)），如果是大表就會(huì)占用非常多的 CPU 資源； 可能會(huì)導(dǎo)致 Buffer Pool 的熱數(shù)據(jù)被淘汰，影響內(nèi)存命中率。

• Batched Key Access Join（BKA，批量鍵訪(fǎng)問(wèn)聯(lián)接）

Index Nested-Loop Join雖好，但是通過(guò)輔助索引進(jìn)行聯(lián)接后需要回表，這里需要大量的隨機(jī)I/O操作。若能優(yōu)化隨機(jī)I/O，那么就能極大的提升Join的性能。為此，MySQL 5.6（MariaDB 5.3）開(kāi)始支持Batched Key Access Join算法（簡(jiǎn)稱(chēng)BKA），該算法通過(guò)常見(jiàn)的空間換時(shí)間，隨機(jī)I/O轉(zhuǎn)順序I/O，以此來(lái)極大的提升Join的性能。

在說(shuō)明Batched Key Access Join前，首先介紹下MySQL 5.6的新特性mrr——multi range read。因?yàn)檫@個(gè)特性也是BKA的重要支柱。MRR優(yōu)化的目的就是為了減少磁盤(pán)的隨機(jī)訪(fǎng)問(wèn)，InnoDB由于索引組織表的特性，如果你的查詢(xún)是使用輔助索引，并且有用到表中非索引列（投影非索引字段，及條件有非索引字段），因此需要回表讀取數(shù)據(jù)做后續(xù)處理，過(guò)于隨機(jī)的回表會(huì)伴隨著大量的隨機(jī)I/O。這個(gè)過(guò)程如下圖所示：

而mrr的優(yōu)化在于，并不是每次通過(guò)輔助索引讀取到數(shù)據(jù)就回表去取記錄，范圍掃描（range access）中MySQL將掃描到的數(shù)據(jù)存入由?read_rnd_buffer_size 變量定義的內(nèi)存大小中，默認(rèn)256K。然后對(duì)其按照Primary Key（RowID）排序，然后使用排序好的數(shù)據(jù)進(jìn)行順序回表，因?yàn)槲覀冎繧nnoDB中葉子節(jié)點(diǎn)數(shù)據(jù)是按照PRIMARY KEY（ROWID）進(jìn)行順序排列的，所以我們可以認(rèn)為，如果按照主鍵的遞增順序查詢(xún)的話(huà)，對(duì)磁盤(pán)的讀比較接近順序讀，能夠提升讀性能。這對(duì)于IO-bound類(lèi)型的SQL查詢(xún)語(yǔ)句帶來(lái)性能極大的提升。

MRR 能夠提升性能的核心在于，這條查詢(xún)語(yǔ)句在索引上做的是一個(gè)范圍查詢(xún)（也就是說(shuō)，這是一個(gè)多值查詢(xún)），可以得到足夠多的主鍵id。這樣通過(guò)排序以后，再去主鍵索引查數(shù)據(jù)，才能體現(xiàn)出“順序性”的優(yōu)勢(shì)。所以MRR優(yōu)化可用于range，ref，eq_ref類(lèi)型的查詢(xún)，工作方式如下圖：

要開(kāi)啟mrr還有一個(gè)比較重的參數(shù)是在變量optimizer_switch中的mrr和mrr_cost_based選項(xiàng)。mrr選項(xiàng)默認(rèn)為on，mrr_cost_based選項(xiàng)默認(rèn)為off。mrr_cost_based選項(xiàng)表示通過(guò)基于成本的算法來(lái)確定是否需要開(kāi)啟mrr特性。然而，在MySQL當(dāng)前版本中，基于成本的算法過(guò)于保守，導(dǎo)致大部分情況下優(yōu)化器都不會(huì)選擇mrr特性。為了確保優(yōu)化器使用mrr特性，請(qǐng)執(zhí)行下面的SQL語(yǔ)句：

但如果強(qiáng)制開(kāi)啟MRR，那在某些SQL語(yǔ)句下，性能可能會(huì)變差；因?yàn)镸RR需要排序，假如排序的時(shí)間超過(guò)直接掃描的時(shí)間，那性能就會(huì)降低。optimizer_switch可以是全局的，也可以是會(huì)話(huà)級(jí)的。

當(dāng)然，除了調(diào)整參數(shù)外，數(shù)據(jù)庫(kù)也提供了語(yǔ)句級(jí)別的開(kāi)啟或關(guān)閉MRR，使用方法如下：

理解了 MRR 性能提升的原理，我們就能理解 MySQL 在 5.6 版本后開(kāi)始引入的 Batched Key Acess(BKA) 算法了。這個(gè) BKA 算法，其實(shí)就是對(duì) INLJ 算法的優(yōu)化。

我們知道 INLJ 算法執(zhí)行的邏輯是：從驅(qū)動(dòng)表一行行地取出 join 條件值，再到被驅(qū)動(dòng)表去做 join。也就是說(shuō)，對(duì)于被驅(qū)動(dòng)表來(lái)說(shuō)，每次都是匹配一個(gè)值。這時(shí)，MRR 的優(yōu)勢(shì)就用不上了。那怎么才能一次性地多傳些值給被驅(qū)動(dòng)表呢？方法就是，從驅(qū)動(dòng)表里一次性地多拿些行出來(lái)，一起傳給被驅(qū)動(dòng)表。既然如此，我們就把驅(qū)動(dòng)表的數(shù)據(jù)取出來(lái)一部分，先放到一個(gè)臨時(shí)內(nèi)存。這個(gè)臨時(shí)內(nèi)存不是別人，就是 join_buffer。

我們知道 join_buffer 在 BNL 算法里的作用，是暫存驅(qū)動(dòng)表的數(shù)據(jù)。但是在 NLJ 算法里并沒(méi)有用。那么，我們剛好就可以復(fù)用 join_buffer 到 BKA 算法中。NLJ 算法優(yōu)化后的 BKA 算法的流程，整個(gè)過(guò)程如下所示：

對(duì)于多表join語(yǔ)句，當(dāng)MySQL使用索引訪(fǎng)問(wèn)第二個(gè)join表的時(shí)候，使用一個(gè)join buffer來(lái)收集第一個(gè)操作對(duì)象生成的相關(guān)列值。BKA構(gòu)建好key后，批量傳給引擎層做索引查找。key是通過(guò)MRR接口提交給引擎的，這樣，MRR使得查詢(xún)更有效率。

如果外部表掃描的是主鍵，那么表中的記錄訪(fǎng)問(wèn)都是比較有序的，但是如果聯(lián)接的列是非主鍵索引，那么對(duì)于表中記錄的訪(fǎng)問(wèn)可能就是非常離散的。因此對(duì)于非主鍵索引的聯(lián)接，Batched Key Access Join算法將能極大提高SQL的執(zhí)行效率。BKA算法支持內(nèi)連接，外連接和半連接操作，包括嵌套外連接。

Batched Key Access Join算法的工作步驟如下：

1. 將外部表中相關(guān)的列放入Join Buffer中。

2. 批量的將Key（索引鍵值）發(fā)送到Multi-Range Read（MRR）接口。

3. Multi-Range Read（MRR）通過(guò)收到的Key，根據(jù)其對(duì)應(yīng)的ROWID進(jìn)行排序，然后再進(jìn)行數(shù)據(jù)的讀取操作。

4. 返回結(jié)果集給客戶(hù)端。

Batched Key Access Join算法的本質(zhì)上來(lái)說(shuō)還是Simple Nested-Loops Join算法，其發(fā)生的條件為內(nèi)部表上有索引，并且該索引為非主鍵，并且聯(lián)接需要訪(fǎng)問(wèn)內(nèi)部表主鍵上的索引。這時(shí)Batched Key Access Join算法會(huì)調(diào)用Multi-Range Read（MRR）接口，批量的進(jìn)行索引鍵的匹配和主鍵索引上獲取數(shù)據(jù)的操作，以此來(lái)提高聯(lián)接的執(zhí)行效率，因?yàn)樽x取數(shù)據(jù)是以順序磁盤(pán)IO而不是隨機(jī)磁盤(pán)IO進(jìn)行的。

在MySQL 5.6中默認(rèn)關(guān)閉BKA（MySQL 5.7默認(rèn)打開(kāi)），必須將optimizer_switch系統(tǒng)變量的batched_key_access標(biāo)志設(shè)置為on。BKA使用MRR，因此mrr標(biāo)志也必須打開(kāi)。目前，MRR的成本估算過(guò)于悲觀(guān)。因此，mrr_cost_based也必須關(guān)閉才能使用BKA。以下設(shè)置啟用BKA：

因?yàn)锽KA算法的本質(zhì)是通過(guò)MRR接口將非主鍵索引對(duì)于記錄的訪(fǎng)問(wèn)，轉(zhuǎn)化為根據(jù)ROWID排序的較為有序的記錄獲取，所以要想通過(guò)BKA算法來(lái)提高性能，不但需要確保聯(lián)接的列參與match的操作（聯(lián)接的列可以是唯一索引或者普通索引，但不能是主鍵），還要有對(duì)非主鍵列的search操作。例如下列SQL語(yǔ)句：

列a.gender是表employees的數(shù)據(jù)，但不是通過(guò)搜索idx_birth_date索引就能得到數(shù)據(jù)，還需要回表訪(fǎng)問(wèn)主鍵來(lái)獲取數(shù)據(jù)。因此這時(shí)可以使用BKA算法。但是如果聯(lián)接不涉及針對(duì)主鍵進(jìn)一步獲取數(shù)據(jù)，內(nèi)部表只參與聯(lián)接判斷，那么就不會(huì)啟用BKA算法，因?yàn)闆](méi)有必要去調(diào)用MRR接口。比如search的主鍵（a.emp_no），那么肯定就不需要BKA算法了，直接覆蓋索引就可以返回?cái)?shù)據(jù)了（二級(jí)索引有主鍵值）。

在EXPLAIN輸出中，當(dāng)Extra值包含Using join buffer（Batched Key Access）且類(lèi)型值為ref或eq_ref時(shí)，表示使用BKA。

• Classic Hash Join（CHJ）

MySQL數(shù)據(jù)庫(kù)雖然提供了BKA Join來(lái)優(yōu)化傳統(tǒng)的JOIN算法，的確在一定程度上可以提升JOIN的速度。但不可否認(rèn)的是，仍然有許多用戶(hù)對(duì)于Hash Join算法有著強(qiáng)烈的需求。Hash Join不需要任何的索引，通過(guò)掃描表就能快速地進(jìn)行JOIN查詢(xún)，通過(guò)利用磁盤(pán)的帶寬帶最大程度的解決大數(shù)據(jù)量下的JOIN問(wèn)題。

MariaDB支持Classic Hash Join算法，該算法不同于Oracle的Grace Hash Join，但是也是通過(guò)Hash來(lái)進(jìn)行連接，不需要索引，可充分利用磁盤(pán)的帶寬。

其實(shí)MariaDB的Classic Hash Join和Block Nested Loop Join算法非常類(lèi)似（Classic Hash Join也稱(chēng)為Block Nested Loop Hash Join），但并不是直接通過(guò)進(jìn)行JOIN的鍵值進(jìn)行比較，而是根據(jù)Join Buffer中的對(duì)象創(chuàng)建哈希表，內(nèi)表通過(guò)哈希算法進(jìn)行查找，從而在Block Nested Loop Join算法的基礎(chǔ)上，又進(jìn)一步減少了內(nèi)表的比較次數(shù)，從而提升JOIN的查詢(xún)性能。過(guò)程如下圖所示：

Classic Hash Join算法先將外部表中數(shù)據(jù)放入Join Buffer中，然后根據(jù)鍵值產(chǎn)生一張散列表，這是第一個(gè)階段，稱(chēng)為build階段。隨后讀取內(nèi)部表中的一條記錄，對(duì)其應(yīng)用散列函數(shù)，將其和散列表中的數(shù)據(jù)進(jìn)行比較，這是第二個(gè)階段，稱(chēng)為probe階段。

如果將Hash查找應(yīng)用于Simple Nested-Loops Join中，則執(zhí)行計(jì)劃的Extra列會(huì)顯示BNLH。如果將Hash查找應(yīng)用于Batched Key Access Join中，則執(zhí)行計(jì)劃的Extra列會(huì)顯示BKAH。

同樣地，如果Join Buffer能夠緩存所有驅(qū)動(dòng)表（外表）的查詢(xún)列，那么驅(qū)動(dòng)表和內(nèi)表的掃描次數(shù)都將只有1次，并且比較的次數(shù)也只是內(nèi)表記錄數(shù)（假設(shè)哈希算法沖突為0）。反之，需要掃描多次內(nèi)部表。為了使Classic Hash Join更有效果，應(yīng)該更好地規(guī)劃Join Buffer的大小。

要使用Classic Hash Join算法，需要將join_cache_level設(shè)置為大于等于4的值，并顯示地打開(kāi)優(yōu)化器的選項(xiàng)，設(shè)置過(guò)程如下：

最后，各JOIN算法成本之間的比較如下表所示：

Hash Join算法雖好，但是僅能用于等值連接，非等值連接的JOIN查詢(xún)，其就顯得為力了。另外，創(chuàng)建哈希表也是費(fèi)時(shí)的工作，但是一旦建立完成后，其就能大幅提升JOIN的速度。所以通常情況下，大表之間的JOIN，Hash Join算法會(huì)比較有優(yōu)勢(shì)。小表通過(guò)索引查詢(xún)，利用BKA Join就已經(jīng)能很好的完成查詢(xún)。目前MySQL 8.0已經(jīng)出了，但目前還沒(méi)有看到Hash Join的身影，不知未來(lái)會(huì)不會(huì)加入。

三、總結(jié)

經(jīng)過(guò)上面的學(xué)習(xí)，我們能發(fā)現(xiàn)聯(lián)接查詢(xún)成本占大頭的就是“驅(qū)動(dòng)表記錄數(shù) 乘以 單次訪(fǎng)問(wèn)被驅(qū)動(dòng)表的成本”，所以我們的優(yōu)化重點(diǎn)其實(shí)就是下面這兩個(gè)部分：

• 盡量減少驅(qū)動(dòng)表的記錄數(shù)
• 對(duì)被驅(qū)動(dòng)表的訪(fǎng)問(wèn)成本盡可能降低

這兩點(diǎn)對(duì)于我們實(shí)際書(shū)寫(xiě)聯(lián)接查詢(xún)語(yǔ)句時(shí)十分有用，我們需要盡量在被驅(qū)動(dòng)表的聯(lián)接列上建立索引（主鍵或唯一索引最優(yōu)，其次是非唯一二級(jí)索引），這樣就可以使用 eq_ref 或 ref 訪(fǎng)問(wèn)方法來(lái)降低訪(fǎng)問(wèn)被驅(qū)動(dòng)表的成本了。

<摘錄>

InnoDB存儲(chǔ)引擎 – 姜

MySQL Join算法與調(diào)優(yōu) – 姜

https://dev.mysql.com/doc/refman/5.7/en/bnl-bka-optimization.html

?

?

https://www.ywnds.com/?p=14399? ? 好文