MySQL中join的有幾種算法

這篇文章主要介紹了MySQL中join的有幾種算法，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

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

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狀態。

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL中join的有幾種算法”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!