一文教你MySQL如何優化無索引的join

Posted on by

前言

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

現在有張 user 表，這個 user 表很簡單，一個主鍵 id，也就是我們的用戶 id，還有個 name 字段，很明顯就是用戶的姓名。

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

遍歷循環查詢

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

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 表拿到所有的用戶信息，然後根據連接鍵 user_id 去 user_info 表裡查詢對應的 account，這樣就能得到想要的數據，但是這種方式幾個問題：

  • 第一次全表掃描 user 表需要一次網絡通信，假設 user 表的數據量是n。

  • 然後循環查詢 user_info 表，這裡需要 n 次網絡通信

因此一共需要 n+1 次網絡通信，如果使用的是長連接，還能省去 3 次握手的時間，如果是短連接，整體的開銷會更大。

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

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

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

因此這種查詢的方式一共需要掃描 n+n*m 次。當然一般不會出現 user_id 沒有索引的情況，在 user_id 有索引的時候，可以根據索引快速定位到我們的目標數據，並不需要全表掃描，因此總共需要掃描的行數為 n+m 次。

join 查詢

一般對於這種情況的查詢，我們會用 join 來做，於是我們的 sql 或許如下：

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

首先從網絡通信上來說，總體隻需要一次通信，至於 MySQL 內部怎麼處理數據，怎麼把我們想要的數據返回回來是它內部的事。

其次我們來看看這種 join 方式的原理：

  • 從 user 表掃描一條數據，然後去 user_info 表中匹配

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

  • 然後把 user 表中的 name 和 user_info 表中的 account 作為結果集的一部分返回回去

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

其中第三步驟，每次組合一條數據的時候，並不是立馬返回給客戶端，這樣效率太低，其實是有緩沖區的，也就是先把數據放在緩沖區中，等緩沖區滿瞭，一次性響應給客戶端可以大大提升效率。

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

join buffer (Block Nested Loop)

以上說的還是連接鍵有索引的，我們來看看連接鍵沒有索引的情況，這時候你通過 explain 來看 MySQL 的執行計劃，你會發現其中 user_info 的 extra 字段中會提示這個：

Using where; Using join buffer (Block Nested Loop)

這是什麼意思呢？

因為沒有索引，所以每次去 user 表得到一條數據的時候，肯定是要再到 user_info 表做全表掃描，這個掃描的成本我們上面也提到瞭，就是 n+n*m=n(1+m)，因此這個時間復雜度是和 n 成正比的，這也是為什麼我們一般推薦「小表驅動大表」的方式。

但是如果我們按照這個方式來做 join，未免開銷太大瞭，太耗時瞭，於是還是沿用老套路，也就是用個臨時存儲區，也就是 extra 中的 join buffer，有瞭這個 join buffer 後，首先會把 user 表的數據放進去，然後掃描 user_info 表，每掃描一行數據，就和 join buffer 中的每一行 user 數據匹配，如果匹配上瞭，也就是我們要的結果，因為 user_info 表有 m 條數據，因此需要判斷 n*m 次，咦！這個也沒減少呀，還是和上面的一樣。其實不一樣，這裡的 m 條數據其實每次都是和內存中的 n 條數據做匹配的，並非磁盤，內存的速度不用多說。

聰明的讀者可能會發現，如果 user 表的數據很多，join buffer 能放得下嗎？

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

buffer 默認是 256K，多的話確實放不下，放不下的話，怎麼辦？其實也很簡單，分段放即可，當讀 user 表的數據占滿 buffer 的時候，就不放瞭，然後直接和 user_info 做匹配，邏輯還是同上，在 buffer 的數據處理完之後，就清空它，接著上次的位置繼續讀入數據，再次重復同樣的邏輯，直至數據讀完。

雖說連接鍵沒有索引的時候，會通過 join buffer 來優化速度，但是現實中，還是建議大傢盡量要保證連接鍵有索引。

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

把行數最小的作為主表，然後去join行數多的，這樣對於索引而言掃描的行數會少很多

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

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

在我的項目裡，就是上面的字符編碼問題導致的join後沒有走索引

改表和字段的字符編碼，統一成ut8mb4

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

總結

到此這篇關於MySQL如何優化無索引join的文章就介紹到這瞭,更多相關MySQL優化無索引join內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：