為什麼不讓用join？《死磕MySQL系列 十六》

發表於2022-03-14
MySql

大家好，我是咔咔 不期速成，日拱一卒

在平時開發工作中join的使用頻率是非常高的，很多SQL優化博文也讓把子查詢改為join從而提升效能，但部分公司的DBA又不讓用，那麼使用join到底有什麼問題呢？

死磕MySQL系列
死磕MySQL系列

一、什麼是Nested-Loop Join

在MySQL中，使用Nested-Loop Join的演算法進行優化join的使用，此演算法翻譯過來為巢狀迴圈連線，並且使用了三種演算法來實現。

  • Index Nested-Loop Join ：簡稱NLJ
  • Block Nested-Loop Join ：簡稱BNLJ
  • Simple Nested-Loop Join ：簡稱 BNL

這幾種演算法大致意思為索引巢狀迴圈連線、快取塊巢狀迴圈連線、粗暴巢狀迴圈連線，你現在看的順序就是MySQL選擇join演算法的優先順序。

從名字上給人感覺Simple Nested-Loop Join演算法是非常簡單同樣也是最快的，但實際情況是MySQL並沒有使用這種演算法而是優化成使用Block Nested-Loop Join，帶著各種疑問一起來探索其中的奧祕。

都看到這裡了，是不是對巢狀迴圈連線的意思不太明白？其實是非常簡單的，一個簡單的案例你就能明白什麼是巢狀迴圈連線。

假設現在有一張文章表article，一張文章評論表article_detail，需求是查詢文章的id查詢出所有的評論現在的首頁，那麼SQL就會是以下的樣子

select * from article a left join article_detail b on a.id = b.article_id

若使用程式碼來描述這段SQL的實現原理大致如下，這段程式碼使用切片和雙層迴圈實現氣泡排序，這段程式碼就能非常代表SQL中join的實現原理，第一層for即為驅動表，第二層for則為被驅動表。

func bubble_sort(arr []int) {
    a := 0 
    for j := 0; j < len(arr)-1; j++ {
        for i := 0; i < len(arr)-1-j; i++ {
            if arr[i] > arr[i+1] {
                a = arr[i]        
                arr[i] = arr[i+1] 
                arr[i+1] = a
            }
        }
    }
}

好了，現在你知道了什麼是Nested-Loop Join，也知道了實現Nested-Loop Join的三種演算法，接下來我們們就圍繞這三種演算法來進行討論，為什麼不讓用join。

二、Index Nested-Loop Join

為了防止優化器對SQL進行粗暴優化，接下來會使用STRAIGHT_JOIN來進行查詢操作。

為什麼會需要STRAIGHT_JOIN，在開發過程中有沒有遇到明明是驅動表的卻莫名其妙的成為了被驅動表，在MySQL中驅動表的概念是當指定了連線條件時，滿足條件並記錄行數少的表為驅動表。當沒有指定查詢條件時，則掃描行數少的為驅動表，優化器總是以小表驅動大表的方式來決定執行順序的。

索引巢狀迴圈連線是基於索引進行連線的演算法，索引是基於被驅動表的，通過驅動表查詢條件直接與被驅動表索引進行匹配，防止跟被驅動表的每條記錄進行比較，利用索引的查詢減少了對被驅動表的匹配次數，從而提升join的效能。

使用前提

使用索引巢狀查詢的前提是驅動表與被驅動表關聯欄位上有設定索引。

接下來使用一個案例來詳細解析索引巢狀查詢的具體執行流程，以下SQL是所有的表和資料，直接複製就可以用

CREATE TABLE `article` (`id` INT (11) NOT NULL AUTO_INCREMENT COMMENT 'ID',`author_id` INT (11) NOT NULL,PRIMARY KEY (`id`)) ENGINE=INNODB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci COMMENT='文章表';

CREATE PROCEDURE idata () BEGIN DECLARE i INT; SET i=1; WHILE (i<=1000) DO INSERT INTO article VALUES (i,i); SET i=i+1; END WHILE; END;

call idata();

CREATE TABLE `article_comment` (`id` INT (11) NOT NULL AUTO_INCREMENT COMMENT 'ID',`article_id` INT (11) NOT NULL COMMENT '文章ID',`user_id` INT (11) NOT NULL COMMENT '使用者ID',PRIMARY KEY (`id`),INDEX `idx_article_id` (`article_id`)) ENGINE=INNODB CHARSET=utf8mb4 COLLATE utf8mb4_german2_ci COMMENT='使用者評論表';

DROP PROCEDURE idata;

CREATE PROCEDURE idata () BEGIN DECLARE i INT;
SET i=1; WHILE (i<=1000)
DO
INSERT INTO article_comment VALUES (i,i,i);
SET i=i+1; END WHILE; END;

CALL idata ();

可以看到，此時article表和article_comment，資料都是1000行

需求是檢視文章的所有評論資訊，執行SQL如下

SELECT*FROM article STRAIGHT_JOIN article_comment ON article.id=article_comment.article_id;

現在，我們來看一下這條語句的explain結果。

死磕MySQL系列
死磕MySQL系列

可以看到，在這條語句中，被驅動表article_comment的欄位article_id使用了索引，因此這個語句的執行流程是這樣的

  • 從article表讀取一行資料R
  • 從R中去除id欄位到表article_comment去查詢
  • 取出article_comment中滿足條件的行，跟R組成一行
  • 重複前三個步驟，直到表article滿足條件的資料掃描結束

在這個流程中我們簡單的梳理一下掃描行數

  • 對article表需要做全表掃描，掃描行數為1000
  • 沒行R資料，根據article表的id去表article_comment查詢，走的是樹搜尋，因此每次的搜尋的結果都是一一對應的，也就是說每次只會掃描到一行資料，共需要掃描1000
  • 所以，這個執行流程，總掃描行數為2000行

若在程式碼中如何實現

  • 全表掃描article資料，這裡是1000行
  • 迴圈這1000行資料
  • 使用article的id作為條件，在迴圈中進行查詢

執行過程掃描行數也是2000行，先不涉及這樣寫效能如何，光與MySQL進互動就進行了1001次。

結論

顯然這麼做還不如直接使用join好

三、Simple Nested-Loop Join

簡單巢狀迴圈連線查詢是表連線使用不上索引，然後就粗暴的使用巢狀迴圈，article、article_comment表都有1000行資料，那麼掃描資料的行數就是1000*1000=1千萬，這種查詢效率可想而知是怎麼樣的。

執行SQL如下

SELECT * FROM article STRAIGHT_JOIN article_comment ON article.author_id=author_id.user_id;

在這個流程裡：

  • 對驅動表article做了全表掃描，這個過程需要掃描1000行
  • 從驅動表每讀取一行資料都需要在article_comment表中進行全表掃描，沒有使用索引就需要全表掃描
  • 因此，每次都需要全表掃描被驅動表的資料

這還是兩個非常小的表，在生產環境的表動輒就是上千萬，如果使用這種演算法估計MySQL就沒有現在的盛況

當然了，MySQL也沒有使用這種演算法，而是用了分塊巢狀查詢的演算法，這種思想在MySQL中很多地方都在使用

擴充套件

例如，索引是儲存在磁碟中的，每次使用索引進行檢索資料時會把資料從磁碟讀入記憶體中，讀取的方式也是分塊讀取，並不是一次讀取完。

假設現在作業系統需在磁碟中讀取1kb的資料，實際上會作業系統讀取到4kb的資料，在作業系統中一頁的資料是4kb，在innodb儲存引擎中預設一頁的資料是16kb。

為什麼MySQL會採用分塊來讀取資料，是因為資料的區域性性原理，資料和程式都有聚整合群的傾向，在訪問到一行資料後，在之後有極大的可能性會再次訪問這條資料和這條資料相鄰的資料。

四、Block Nested-Loop Join

使用簡單巢狀查詢的方式經過上文的分析肯定是不可取的，而是選擇了分塊的思想進行處理。

這時，執行流程是這樣的

  • 從驅動表article中讀取資料存放在join_buffer中，由於是使用的沒有條件的select ，因此會把article全表資料放入記憶體
  • 拿著join_buffer中的資料跟article_comment中的資料進行逐行對比

對應的，這條SQL的explain結果如下所示

死磕MySQL系列
死磕MySQL系列

為了復現Block Nested Loop，咔咔裝了三個版本的MySQL，分別為MySQL8，MySQL5.5，MySQL5.7在後兩個版本中都使用的是Block Nested Loop，但在MySQL8中卻發生了變化。

死磕MySQL系列
死磕MySQL系列

對於hash join 下期會聊到，在這個查詢過程中，對錶article、article_comment都做了一次全表掃描，因此掃描行數是2000。

把article中的資料讀取到join_buffer中是以無序陣列的方式儲存的，對於article_comment表中的每一行，都需要做1000次判斷，那麼就需要判斷的次數就是1000*1000=1000萬次。

這時你發現使用分塊巢狀迴圈跟簡單巢狀查詢掃描行數是一樣的，但Block Nested Loop演算法應用了join_buffer的這麼一個記憶體空間，因此速度上肯定會比Simple快很多。

五、總結

本期我們用三個問題來總結全文，以幫助你更好的理解。

第一個問題：能不能使用join？

通過三個演示案例，現在你應該知道當關聯條件的列是被驅動表的索引時，是完全沒有問題的，也就是說當使用索引巢狀查詢時，是可以使用join的。

但當使用的是分塊巢狀查詢，這種方式掃描行數為兩張錶行數的乘，掃描行數會非常的大，會佔用大量的系統資源，所以這種演算法的join是非常不建議使用的。

因此當使用join時，最大可能的讓關聯查詢的列為被驅動表的索引列，若不能達到這個條件則可以考慮表結構設計是否合理

第二個問題：如果使用join，選擇大表還是小表作為驅動表？

好的習慣都是慢慢養成的，因此你要記住無論在什麼情況下都用小表驅動大表，先記住這個結論。

如果是Nested-Loop Join演算法，應該選擇小表作為驅動表。

如果是Block Nested-Loop Join，當join_buffer足夠大的時候，使用大表還是小表作為驅動表都是一樣的，但是當join_buffer沒有手動設定更大的值時，還是應該選擇小表作為驅動表。

這裡還需要知道一點join_buffer的預設值為在MySQL8.0為256kb。

第三個問題：什麼樣的表是小表？

這裡的小表不是資料量非常小的表，這點一定不能搞錯，在所有的SQL查詢中絕大多數情況是有條件進行篩選的。

看是否為小表是根據同一條件下兩張表那個檢索的資料量小，那張表就是小表。

推薦閱讀

死磕MySQL系列總目錄

開啟order by的大門，一探究竟《死磕MySQL系列 十二》

重重封鎖，讓你一條資料都拿不到《死磕MySQL系列 十三》

闖禍了，生成環境執行了DDL操作《死磕MySQL系列 十四》

聊聊MySQL的加鎖規則《死磕MySQL系列 十五》

“

堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的網際網路上能給你帶來一點幫助，我是咔咔，下期見。

”

相關文章