MySQL怎么優(yōu)化無索引的join

MySQL怎么優(yōu)化無索引的join，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。

前言

MySQL Join 你用過嗎？你知道其中的原理嗎？

現(xiàn)在有張 user 表，這個(gè) user 表很簡單，一個(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
}

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

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

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

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

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

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

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

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

join 查詢

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

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

首先從網(wǎng)絡(luò)通信上來說，總體只需要一次通信，至于 MySQL 內(nèi)部怎么處理數(shù)據(jù)，怎么把我們想要的數(shù)據(jù)返回回來是它內(nè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)給客戶端可以大大提升效率。

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

join buffer (Block Nested Loop)

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

Using where; Using join buffer (Block Nested Loop)

這是什么意思呢？

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

但是如果我們按照這個(gè)方式來做 join，未免開銷太大了，太耗時(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è)也沒減少呀，還是和上面的一樣。其實(shí)不一樣，這里的 m 條數(shù)據(jù)其實(shí)每次都是和內(nèi)存中的 n 條數(shù)據(jù)做匹配的，并非磁盤，內(nèi)存的速度不用多說。

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

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

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

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

附：mysql  join查詢沒有走索引的原因

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

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

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

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

改表和字段的字符編碼，統(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 '';

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝您對(duì)億速云的支持。