在阿里巴巴的java開(kāi)發(fā)手冊(cè)有這么一條強(qiáng)制規(guī)定：超過(guò)三個(gè)表禁止join，需要join的字段，數(shù)據(jù)類(lèi)型保持絕對(duì)一致，多表關(guān)聯(lián)查詢(xún)時(shí)，要保證被關(guān)聯(lián)的字段需要有索引。

為什么盡量避免使用join？如果使用join，我們應(yīng)該怎么用呢？接下來(lái)我們就一起聊一聊關(guān)于join的幾種算法。

Simple Nested-Loop Join

Simple Nested-Loop Join算法是指讀取驅(qū)動(dòng)表t1中的每行數(shù)據(jù)，將每行數(shù)據(jù)傳遞到被驅(qū)動(dòng)表t2上，取出被驅(qū)動(dòng)表t2中滿(mǎn)足條件的行,和t1組成結(jié)果集。

在這個(gè)算法中，需要對(duì)t1進(jìn)行全表掃描，假設(shè)t1表1000行數(shù)據(jù)，那么需要對(duì)t2表進(jìn)行1000次全表掃描，假設(shè)t2表也是1000行數(shù)據(jù)，那么就需要掃描1000 X 1000=1000000行。

示例圖如下：當(dāng)t1表5行數(shù)據(jù)，t2表5行數(shù)據(jù)時(shí)，需要掃描25行數(shù)據(jù)。

Index Nested-Loop Join

index nested-loop join算法的優(yōu)化思路是通過(guò)驅(qū)動(dòng)表的匹配條件，直接與被驅(qū)動(dòng)表的索引進(jìn)行匹配，減少了被驅(qū)動(dòng)表的掃描次數(shù)。

該算法同樣要對(duì)驅(qū)動(dòng)表t1進(jìn)行全表掃描，但是我們?cè)谀弥鴗1表的數(shù)據(jù)去被驅(qū)動(dòng)表t2進(jìn)行匹配時(shí)可以利用t2表的索引，如果t1表中1000行數(shù)據(jù)，t2表中1000行數(shù)據(jù)，那么一共就需要掃描1000+1000=2000行數(shù)據(jù)。這個(gè)過(guò)程就跟我們寫(xiě)程序時(shí)的嵌套查詢(xún)類(lèi)似，并且可以用上被驅(qū)動(dòng)表的索引，所以稱(chēng)之為“Index Nested-Loop Join”，簡(jiǎn)稱(chēng) NLJ。

示例如下：當(dāng)t1表有5行數(shù)據(jù)，t2表有5行數(shù)據(jù)時(shí)，一共需要掃描5+5=10行數(shù)據(jù)。

Block Nested-Loop Join

Block Nested-Loop join，基于塊的嵌套循環(huán)，簡(jiǎn)稱(chēng)BNL算法，其優(yōu)化思路主要是減少被驅(qū)動(dòng)表的循壞次數(shù)，它會(huì)將驅(qū)動(dòng)表的數(shù)據(jù)緩存起來(lái)，把參與查詢(xún)的列緩存到j(luò)oin buffer里，然后拿join buffer里的數(shù)據(jù)批量與內(nèi)層表的數(shù)據(jù)在join buffer中進(jìn)行匹配，滿(mǎn)足join條件的，作為結(jié)果集的一部分返回。

可以看到該算法對(duì)兩個(gè)表都進(jìn)行了全表掃描，因此掃描的行數(shù)是兩個(gè)表的行數(shù)之和。這種場(chǎng)景下，雖然在掃描行數(shù)上和NLJ算法一樣，但是由于BNL算法是在內(nèi)存中進(jìn)行判斷，速度上會(huì)快很多。

join buffer的大小是由參數(shù)join_buffer_size設(shè)定，默認(rèn)256k。如果一次放不下驅(qū)動(dòng)表的所有數(shù)據(jù)，會(huì)分段放，這種情況下會(huì)導(dǎo)致被驅(qū)動(dòng)表掃描多次。如果被驅(qū)動(dòng)表是冷數(shù)據(jù)表，并且多次掃描讀取被驅(qū)動(dòng)表間隔超過(guò)1S的話(huà)，就會(huì)將他放入LRU鏈表的young區(qū)域，導(dǎo)致業(yè)務(wù)數(shù)據(jù)無(wú)法進(jìn)入熱數(shù)據(jù)區(qū)，減少了bufferpool的命中率，這又是另外一個(gè)課題了，暫不過(guò)多展開(kāi)。我們可以通過(guò)調(diào)大join_buffer_size來(lái)提高緩存的數(shù)據(jù)量，減少對(duì)被驅(qū)動(dòng)表的掃描次數(shù)。

啟用BNL算法需要在optimizer_switch參數(shù)中設(shè)置block_nested_loop=on。

Batched Key Access

BNL算法提升了join的性能，但是它在通過(guò)輔助索引連接后需要回表，就會(huì)消耗大量的隨機(jī)I/O，我們知道隨機(jī)IO對(duì)MySQL的影響是非常大的。因此MySQL5.6引入了Batched Key Access(BKA，批量鍵訪(fǎng)問(wèn)聯(lián)接)算法。

再說(shuō)BKA算法時(shí)不得不提的就是MySQL的Multi-Range Read 優(yōu)化，MRR的目的主要是減少磁盤(pán)的隨機(jī)訪(fǎng)問(wèn)。我們都知道，Innodb索引采用的是B+tree的數(shù)據(jù)結(jié)構(gòu)，數(shù)據(jù)保存在主鍵索引中，并且是按照主鍵遞增的順序插入的，但是二級(jí)索引的排列順序和主鍵的排列順序一般是不一樣的，它保存的主鍵值也并非按照主鍵順序排列，在回表時(shí)就會(huì)出現(xiàn)隨機(jī)訪(fǎng)問(wèn)主鍵索引的情況。所以如果可以按照主鍵遞增順序查詢(xún)的話(huà)，對(duì)磁盤(pán)的讀比較接近順序讀，這樣就能夠提升讀性能。

MRR優(yōu)化的思路就是在進(jìn)行范圍查詢(xún)時(shí)，在得到主鍵值之后，先按照主鍵的順序進(jìn)行排序，然后拿著排好序的主鍵ID再去主鍵索引進(jìn)行查詢(xún)，這樣就能體現(xiàn)出順序性的優(yōu)勢(shì)了。因?yàn)槭嵌嘀挡樵?xún)，所以一般用于range、ref類(lèi)型的查詢(xún)。

再說(shuō)會(huì)BKA算法，當(dāng)被驅(qū)動(dòng)表上有索引可以利用時(shí)，那么就在行提交給被 join 的表之前，先對(duì)兩個(gè)表的對(duì)應(yīng)列的索引字段進(jìn)行join，得到主鍵值后，按照主鍵排好序后，利用 MRR 技術(shù)，批量訪(fǎng)問(wèn)表取數(shù)據(jù)，減少了隨機(jī) IO。但是如果被 join 的表沒(méi)用索引的話(huà)，那就只能使用BNL算法了。

具體算法如下圖：

開(kāi)啟BKA和MRR的方式：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

MySQL在8.0版本已經(jīng)實(shí)現(xiàn)了hash join，這里暫不做介紹。

小結(jié)

如何優(yōu)化join的速度呢，這里給出如下幾點(diǎn)建議：

盡量避免使用join。

用小表作為驅(qū)動(dòng)表，減少外層循環(huán)的次數(shù)。

多表關(guān)聯(lián)查詢(xún)時(shí)，要保證被關(guān)聯(lián)的字段要有索引。

適當(dāng)增大join_buffer_size的值，緩存的數(shù)據(jù)越多，就越能減少被驅(qū)動(dòng)表掃描的次數(shù)。

減少不必要的字段查詢(xún)。

需要join的字段，數(shù)據(jù)類(lèi)型保持絕對(duì)一致。

本文分享自微信公眾號(hào) - MySQL數(shù)據(jù)庫(kù)技術(shù)棧(Mysqltechnology)。

如有侵權(quán)，請(qǐng)聯(lián)系 support@oschina.cn 刪除。

本文參與“OSC源創(chuàng)計(jì)劃”，歡迎正在閱讀的你也加入，一起分享。

總結(jié)

以上是生活随笔為你收集整理的mysql join 算法_【MySQL】之join算法详解的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。