前言

MySQL Join 你用過(guò)嗎？你知道其中的原理嗎？

現(xiàn)在有張 user 表，這個(gè) user 表很簡(jiǎn)單，一個(gè)主鍵 id，也就是我們的用戶 id，還有個(gè) name 字段，很明顯就是用戶的姓名。

這時(shí)候還有一張 user_info 表，這個(gè) user_info 表存的是用戶的一些其他信息，有 user_id 代表用戶的 id，還有個(gè) account 代表用戶的存款。

遍歷循環(huán)查詢

如果要查出所有用戶的姓名和存款，我們可以這樣查：

data?=?select?*?from?user;
for?(i=0;i<len(data);i++)?{
??info?=?select?account?from?user_info?where?user_id=?data[i].user_id
}

這種方式最直觀，先通過(guò) user 表拿到所有的用戶信息，然后根據(jù)連接鍵 user_id 去 user_info 表里查詢對(duì)應(yīng)的 account，這樣就能得到想要的數(shù)據(jù)，但是這種方式幾個(gè)問(wèn)題：

• 第一次全表掃描 user 表需要一次網(wǎng)絡(luò)通信，假設(shè) user 表的數(shù)據(jù)量是n。

• 然后循環(huán)查詢 user_info 表，這里需要 n 次網(wǎng)絡(luò)通信

因此一共需要 n+1 次網(wǎng)絡(luò)通信，如果使用的是長(zhǎng)連接，還能省去 3 次握手的時(shí)間，如果是短連接，整體的開(kāi)銷會(huì)更大。

其次如果 user_id 沒(méi)有索引，那么整體更傷，假設(shè) user_info 一共有 m 條數(shù)據(jù)，那么掃描的次數(shù)是怎么算的呢？

• 首先 user 表是全表掃，一共需要查詢 n 次。

• 由于 user_info 表的 user_id 沒(méi)有索引，那么每次查詢等于都是全表掃，總共需要 n*m 次。

因此這種查詢的方式一共需要掃描 n+n*m 次。當(dāng)然一般不會(huì)出現(xiàn) user_id 沒(méi)有索引的情況，在 user_id 有索引的時(shí)候，可以根據(jù)索引快速定位到我們的目標(biāo)數(shù)據(jù)，并不需要全表掃描，因此總共需要掃描的行數(shù)為 n+m 次。

join 查詢

一般對(duì)于這種情況的查詢，我們會(huì)用 join 來(lái)做，于是我們的 sql 或許如下：

select?a.name,b.account?from?user?a?left?join?user_info?b?on?a.id=b.user_id

首先從網(wǎng)絡(luò)通信上來(lái)說(shuō)，總體只需要一次通信，至于 MySQL 內(nèi)部怎么處理數(shù)據(jù)，怎么把我們想要的數(shù)據(jù)返回回來(lái)是它內(nèi)部的事。

其次我們來(lái)看看這種 join 方式的原理：

• 從 user 表掃描一條數(shù)據(jù)，然后去 user_info 表中匹配

• 在連接鍵 user_id 有索引的情況下，可以利用索引快速匹配

• 然后把 user 表中的 name 和 user_info 表中的 account 作為結(jié)果集的一部分返回回去

• 重復(fù) 1-3 步驟，直至 user 表掃描完畢，數(shù)據(jù)全部返回。

其中第三步驟，每次組合一條數(shù)據(jù)的時(shí)候，并不是立馬返回給客戶端，這樣效率太低，其實(shí)是有緩沖區(qū)的，也就是先把數(shù)據(jù)放在緩沖區(qū)中，等緩沖區(qū)滿了，一次性響應(yīng)給客戶端可以大大提升效率。

從原理來(lái)看和上面的遍歷查詢差不多，主要不同的是，客戶端不需要和服務(wù)端多次通信。

join buffer (Block Nested Loop)

以上說(shuō)的還是連接鍵有索引的，我們來(lái)看看連接鍵沒(méi)有索引的情況，這時(shí)候你通過(guò) explain 來(lái)看 MySQL 的執(zhí)行計(jì)劃，你會(huì)發(fā)現(xiàn)其中 user_info 的 extra 字段中會(huì)提示這個(gè)：

Using?where;?Using?join?buffer?(Block?Nested?Loop)

這是什么意思呢？

因?yàn)闆](méi)有索引，所以每次去 user 表得到一條數(shù)據(jù)的時(shí)候，肯定是要再到 user_info 表做全表掃描，這個(gè)掃描的成本我們上面也提到了，就是 n+n*m=n(1+m)，因此這個(gè)時(shí)間復(fù)雜度是和 n 成正比的，這也是為什么我們一般推薦「小表驅(qū)動(dòng)大表」的方式。

但是如果我們按照這個(gè)方式來(lái)做 join，未免開(kāi)銷太大了，太耗時(shí)了，于是還是沿用老套路，也就是用個(gè)臨時(shí)存儲(chǔ)區(qū)，也就是 extra 中的 join buffer，有了這個(gè) join buffer 后，首先會(huì)把 user 表的數(shù)據(jù)放進(jìn)去，然后掃描 user_info 表，每掃描一行數(shù)據(jù)，就和 join buffer 中的每一行 user 數(shù)據(jù)匹配，如果匹配上了，也就是我們要的結(jié)果，因?yàn)?user_info 表有 m 條數(shù)據(jù)，因此需要判斷 n*m 次，咦！這個(gè)也沒(méi)減少呀，還是和上面的一樣。其實(shí)不一樣，這里的 m 條數(shù)據(jù)其實(shí)每次都是和內(nèi)存中的 n 條數(shù)據(jù)做匹配的，并非磁盤(pán)，內(nèi)存的速度不用多說(shuō)。

聰明的讀者可能會(huì)發(fā)現(xiàn)，如果 user 表的數(shù)據(jù)很多，join buffer 能放得下嗎？

+------------------+--------+
|?Variable_name????|?Value??|
+------------------+--------+
|?join_buffer_size?|?262144?|
+------------------+--------+

buffer 默認(rèn)是 256K，多的話確實(shí)放不下，放不下的話，怎么辦？其實(shí)也很簡(jiǎn)單，分段放即可，當(dāng)讀 user 表的數(shù)據(jù)占滿 buffer 的時(shí)候，就不放了，然后直接和 user_info 做匹配，邏輯還是同上，在 buffer 的數(shù)據(jù)處理完之后，就清空它，接著上次的位置繼續(xù)讀入數(shù)據(jù)，再次重復(fù)同樣的邏輯，直至數(shù)據(jù)讀完。

雖說(shuō)連接鍵沒(méi)有索引的時(shí)候，會(huì)通過(guò) join buffer 來(lái)優(yōu)化速度，但是現(xiàn)實(shí)中，還是建議大家盡量要保證連接鍵有索引。

附：mysql  join查詢沒(méi)有走索引的原因

把行數(shù)最小的作為主表，然后去join行數(shù)多的，這樣對(duì)于索引而言掃描的行數(shù)會(huì)少很多

在join之后On的條件，類型不同是無(wú)法走索引的，也就是說(shuō)如果on A.id = B.id，雖然A表和B表的id都設(shè)置了索引，但是A表的id是Int，而B(niǎo)表的id是varchar，則無(wú)法走索引

字符編碼也會(huì)導(dǎo)致無(wú)法走索引。字符編碼常見(jiàn)的是utf8和utf8mb4，utf8mb4是可以兼容utf8的，也就是說(shuō)如果A表是utf8mb4，B表是utf8，則on A.uinstanceid = B. uinstanceid是可以走索引的，但是如果把B表當(dāng)作主表，讓B去join A on B.uinstanceid = A. uinstanceid則無(wú)法走索引

在我的項(xiàng)目里，就是上面的字符編碼問(wèn)題導(dǎo)致的join后沒(méi)有走索引

改表和字段的字符編碼，統(tǒng)一成ut8mb4

ALTER TABLE visitor DEFAULT CHARSET utf8mb4;
ALTER TABLE visitor CHANGE visitor_id visitor_id varchar(100) CHARACTER SET utf8mb4 NOT NULL DEFAULT '';

總結(jié)

到此這篇關(guān)于MySQL如何優(yōu)化無(wú)索引join的文章就介紹到這了,更多相關(guān)MySQL優(yōu)化無(wú)索引join內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論