MySQL調優篇 | 表連線方式及演算法（3）

【前言】

經常有一些朋友向我諮詢，如何寫出高效的SQL，這不是三言兩語能說得清的，索性認真來寫一下，增刪查改方面的知識我不再贅述，如果有基礎薄弱的同學，可以好好的補一補再來看。

以MySQL為基礎，MySQL調優篇內容主要包含MySQL邏輯架構、索引知識、表關聯演算法、explain執行計劃解讀及SQL調優實戰等。

文章受眾主要為兩類人：

第一類人是工作中不可避免的會接觸到MySQL的人，比如說一些專案人員、開發人員、測試人員等。

第二類人是專職DBA。

其實不管是專職的還是非專職的，就我接觸到的情況而言，很多DBA平時維護MySQL看起來沒什麼問題，但其實沒有很好的理論支撐，知其然而不知其所以然，解釋一個簡單的問題就能問倒一大部分的人。

比如說：MySQL的邏輯架構，分析當前業務架構優缺點？SQL工作原理是什麼樣的？

而且很多公司招聘面試的時候，考驗的也是背後的原理居多，基本上沒有機試。面試官問一個問題，即便你會解決但就是說不出原理，那麼你肯定要不了高薪。

理論+實戰=高薪

文章能夠讓大家有所收穫、有所借鑑那是最好的。

【表連線方式】

常見的七種Join理論，如圖所示：

 

 

1、左連線：A獨有+在A中的B部分

 

 

語法：

select * from A left join B on A.key = B.key

沒有滿足A的B補Null。

2、內連線：A和B的交集

 

 

語法：

select * from A inner join B on A.key = B.key

3、右連線：B獨有+在B中的A部分

 

 

語法：

select * from A right join B on A.key = B.key

沒有滿足B的A補Null。

上面三個是非常常見且常用的Join，那麼還有四類Join：

4、A去掉B的部分，A的獨有

 

 

對比左連線，其實是把中間屬於A的B部分給幹掉了。
語法：

select * from A LEFT JOIN B on A.aid = B.bid    左連線 where B.bid is null   B不在A的部分

因為本身左連線已經把所有A的值都包含出來了，同時多了的部分，就是B在A的部分，只要拿到為空的部分，其實就是B不在A的部分。

5、B去掉A的部分，B的獨有

 

 

對比右連線，其實是把中間屬於B的A部分給幹掉了
語法：
       

select * from A RIGHT JOIN B on A.aid = B.bid    右連線 where A.aid is null   A不在B的部分

6、A和B去掉交集部分

 

 

語法：

select * from A LEFT JOIN B on A.aid = B.bid    左連線 where B.bid is null   B不在A的部分 union select * from A RIGHT JOIN B on A.aid = B.bid    右連線 where A.aid is null   A不在B的部分

實際上是不是上面兩個的合體？

7、外連線：A和B的並集

語法：

select * from A LEFT JOIN B on A.aid = B.bid   union select * from A RIGHT JOIN B on A.aid = B.bid

【表連線演算法】

MySQL資料庫根據不同的使用場合，支援兩種Nested-Loops Join演算法，一種是Simple Nested-Loops Join（NLJ）演算法，另一種是Block Nested-Loops Join（BNL）演算法。

 

 

1、簡單巢狀迴圈連線（Simple Netsted-Loop Join）

對於兩表連線，驅動表只會被訪問一遍，被驅動表具體訪問幾遍取決於對驅動表執行單表查詢後的結果集中的記錄條數。

對於內連線，選取哪個表為驅動表都沒關係，而外連線的驅動表是固定的，也就是說左外連線的驅動表就是左邊的那個表，而右外連線的驅動表就是右邊那個表。

兩表連線的大致過程：

• 選取驅動表，使用與驅動表相關的過濾條件，選取代價最低的單表訪問方法來執行對驅動表的單表查詢。

• 對上一步中查詢驅動得到的結果集中每一條記錄都被分別到被驅動表中查詢匹配的記錄。

   

如圖：

 

 

如果有3個表進行連線的話，那麼步驟2中得到的結果集就像是新的驅動表，然後第三個表就成為了被驅動表，重複上邊過程。

這種驅動表只訪問一次，但被驅動表卻可能被多次訪問的連線執行方式稱之為巢狀迴圈連線（Nested-Loop Join）。

2、 索引巢狀迴圈連線（Index Nested Loops Join），

有一方在連線欄位上有索引，這種場景在MySQL的使用中見的比較多。

最佳化器會考慮選擇有索引的一方作為被驅動表，雙方都有索引則選擇索引高度低的，索引高度一樣則選擇記錄數多的作為被驅動表，對於驅動表的每一條記錄，在被驅動表中使用索引查詢，大大減少了比較次數，提高了查詢效率。
索引是主鍵時效率更高。

如圖：

 

 

3、基於塊的巢狀迴圈連線（Block Nested-Loop Join）

掃描一個表的過程其實就是把這個表的資料從磁碟上載入到記憶體中，然後在記憶體中比較匹配條件。

在實際環境中，面對百千萬的數，記憶體放不下，所以在掃描表前邊記錄的時候後邊的記錄可能還在磁碟上，等掃描後面的記錄的時候可能記憶體不足，需要把前面的資料在記憶體中釋放掉。

而採用巢狀迴圈連線演算法的兩個表中，被驅動表要被訪問好多次，如果被驅動表中的資料特別多而且不能使用索引訪問的話，那就相當於從磁碟上讀好多次這個表，這個IO代價就非常大，所以我們應該儘量減少訪問被驅動表的次數。

在巢狀迴圈連線中，驅動表查詢結果集中有多少條記錄，就需要驅動表資料被載入多少次來進行匹配，那可不可以把被驅動表的記錄載入到記憶體的時候，一次性和多條驅動表中的記錄做匹配，這樣就可以大大減少重複從磁碟上載入被驅動表的代價了。

所以提出可join buffer的概念，join buffer就是執行連線查詢前申請的一塊固定大小的記憶體，先把若干條驅動表結果集中的記錄裝在這個join buffer中，然後掃描被驅動表，每一條被驅動表的記錄一次性和join buffer中的多條驅動表記錄進行匹配，因為匹配的過程是在記憶體中完成的，所以這樣可以減少被驅動表的IO代價。

 

 

這種只需要訪問一次被驅動表就可以完成的連線操作稱為基於塊的巢狀連線演算法；

4、批次鍵訪問聯接（Batched Key Access Join）

當被驅動表的連結欄位有非主鍵索引時，而是透過範圍掃描讀取一部分記錄放入記憶體中，然後按照主鍵排序，這樣匹配到資料後需要按對應的主鍵索引去查詢被驅動表的真實資料時，可以按照排好序的主鍵進行順序訪問，因為InnoDB葉子節點的資料也是按主鍵排序的，所以這種讀取方式能提高查詢效率。

MRR的使用流程中用到了排序，有一定的開銷，有些sql中效率可能沒有那麼高。

 

 

5、Hash Join

MySQL8.0正式引入了Hash Join的連線方式，Hash Join可以在被驅動表沒有索引的情況下進行快速的連線並查詢。

• Hash Join首先使用了Join Buffer，把驅動表相關欄位存入記憶體。這一步和塊巢狀迴圈連線套路相同。

• 把Join Buffer中對應的欄位值生成一個雜湊表，儲存在記憶體中。這一步叫build。

• 掃描被驅動表，對被驅動表中的相關欄位進行雜湊並比較。這一步叫probe。

可見，Hash Join也依賴Join Buffer，在最好的場景下，如果Join Buffer能覆蓋驅動表所有相關欄位，那麼在查詢的過程中驅動表和被驅動表都只需要掃描一次，如果雜湊演算法夠好，比較次數也只是被驅動表的記錄數。

Hash Join只能用於等值連線，大表連線Hash Join的最佳化效果比較明顯。

最佳化思路

• 用小結果集驅動大結果集，儘量減少 join 語句中的Nested Loop迴圈總次數。

• 優先最佳化 Nested Loop 內層迴圈，因為內層迴圈是迴圈中執行次數最多的，每次迴圈提升很小的效能都能在整個迴圈中提升很大的效能。

• 對被驅動表的 join 欄位上建立索引，並且Join ON 條件的欄位應該是相同型別的。

• 當被驅動表的 join 欄位上無法建立索引的時候，設定足夠的 Join Buffer Size。

• 對於非主鍵的連線查詢，如果被驅動表資料特別多，建議先使用子查詢查出一個臨時的結果集然後再連線。（待驗證）

• 對於可以直接從一個表中取資料的情況。（例如同一個表中取交集，例如好友表，互相關注才是好友）這樣的情況，使用 Join 效率是要高於子查詢的。

【總結】

掌握表連線演算法，結合實踐中不斷的實驗和摸索，從而真正達到高效使用MySQL演算法的目的。

下一篇講explain執行計劃相關的知識，希望對大家的學習或者工作具有一定的參考價值。

【參考】

• InnoDB儲存引擎 – 姜承堯

• MySQL Join演算法與調優白皮書 – 姜承堯

【往期回顧】
MySQL調優篇 | 索引知識解讀（2）
MySQL調優篇 | 邏輯架構解讀（1）

更多精彩內容，關注我們▼▼