MySQL進階學習：深入了解 join 的3種演算法

這篇文章是MySQL的進階學習，給大家詳細介紹一下join連結的原理，以及join的3種演算法，希望對大家有幫助！

我們經常在多表查詢的時候使用join 去連接多個表，其實join的效率比不好還是應該盡量避免使用的，其本質就是各個表之間循環匹配的，MySQL中只支援一種join演算法Nested-Loop Join(循環嵌套連接)，但是其有多種變種的演算法，其實就是提高join的執行效率。 【相關推薦：mysql影片教學】

1. Simple Nested-Loop Join(簡單巢狀循環連接)

Simple Nested-Loop join(NLJ)演算法從循環中的第一個表中一次讀取一行，將每一行傳遞給一個嵌套循環，該循環中匹配資料是否一致。例如驅動表User,被驅動表UserInfo 的sql是select * from User u left join User_info info on u.id = info.user_id，其實就是我們常用的for循環，偽代碼的邏輯應該是

for(User u:Users){
    for(UserInfo info:UserInfos){
        if(u.id == info.userId){
            // 得到匹配数据
        }
    }
}

簡單粗暴的演算法，每次從User表中取出一條數據，然後掃描User_info中的所有記錄匹配，最後合併數據返回。

假如驅動表User有10個數據，被驅動表UserInfo也有10條數據，那麼實際上驅動表User會被掃描10次，而被驅動表會被掃描10*10=100次(每掃描一次驅動表，就會掃描全部的被驅動表)，這種效率是很低的，對資料庫的開銷比較大，尤其是被驅動表。 每次掃描其實就是從硬碟中讀取資料載入到記憶體中,也就是一次IO，目前IO是最大的瓶頸

##2. Index Nested-Loop Join(索引嵌套循環連接)

#索引嵌套循環是使用索引減少掃描的次數來提高效率的，所以要求非驅動表上必須有索引才行。

在查詢的時候，驅動表(User) 會根據關聯欄位的索引進行查詢，當索引上找到符合的值，才會進行回表查詢。如果非驅動表(User_info)的關聯欄位(user_id)是主鍵的話，查詢效率會非常高(主鍵索引結構的葉子結點包含了完整的行資料(InnoDB))，如果不是主鍵，每次都符合索引後都需要進行一次回表查詢(根據二級索引(非主鍵索引)的主鍵ID進行回表查詢)，性能肯定弱於主鍵的查詢。

上圖中的索引查詢之後不一定會回表，什麼情況下會回表，這個要看索引查詢到的字段能不能滿足查詢需要的字段，具體可以參考之前的文章：

你需要知道的一些索引基礎知識和B 樹的索引知識

3. Block Nested-Loop Join(快取區塊巢狀循環連接)

如果存在索引，那麼會使用index的方式進行join，如果join的列沒有索引，被驅動表要掃描的次數太多了，每次存取被驅動表，其表中的記錄都會被載入到內存中，然後再從驅動表中取一條與其匹配，匹配結束後清除內存，然後再從驅動表中加載一條記錄然後把被驅動表的記錄在載入到記憶體匹配，這樣周而復始，大大增加了IO的次數。為了減少被驅動表的IO次數，就出現了Block Nested-Loop Join的方式。

不再是逐條獲取驅動表的數據，而是一塊一塊的獲取，引入了join buffer緩衝區，將驅動表join相關的部分數據列(大小是join buffer的限制)緩存到join在 buffer中，然後全表掃描被驅動表，被驅動表的每一條記錄一次性和join buffer中的所有驅動表記錄進行匹配（內存中操作），將簡單嵌套循環中的多次比較合併成一次，降低了非驅動表的存取頻率。

驅動表能不能一次載入完，要看join buffer能不能儲存所有的數據，預設情況下

join_buffer_size=256k,查詢的時候Join Buffer 會快取所有參與查詢的欄位而不是只有join的列，在一個有N個join關聯的sql中會分配N-1個join buffer。所以查詢的時候盡量減少不必要的字段，可以讓join buffer中可以存放更多的列。

可以調整join_buffer_size的快取大小

show variables like '%join_buffer%'這個值可以根據實際情況更改。

使用Block Nested-Loop Join演算法需要開啟優化器管理配置的optimizer_switch的設定block_nested_loop為on，預設是開啟的。可透過 show variables like '%optimizer_switch%' 查看block_nested_loop狀態。

以上三種演算法了解即可，其實實際工作中只要我們能都用好索引就不錯了，即使是join的連接也要注意關聯字段是否建立索引，還是要善於使用索引來提供查詢效率。

原文網址：https://juejin.cn/post/7014105037517357093

作者：紀先生

#更多程式相關知識，請訪問：程式設計入門！ ！

以上是MySQL進階學習：深入了解 join 的3種演算法的詳細內容。更多資訊請關注PHP中文網其他相關文章！