Nested Loop的Join有哪些用法

這篇文章主要講解了“Nested Loop的Join有哪些用法”，文中的講解內(nèi)容簡單清晰，易于學(xué)習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習“Nested Loop的Join有哪些用法”吧！

我們都知道SQL的join關(guān)聯(lián)表的使用方式，但是這次聊的是實現(xiàn)join的算法，join有三種算法，分別是Nested Loop Join，Hash join，Sort Merge Join。

MySQL官方文檔中提到，MySQL只支持Nested Loop Join這一種join algorithm

MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. explain-output

所以本篇只聊Nested Loop Join。

NLJ是通過兩層循環(huán)，用第一張表做Outter Loop，第二張表做Inner Loop，Outter Loop的每一條記錄跟Inner Loop的記錄作比較，符合條件的就輸出。而NLJ又有3種細分的算法：

1、Simple Nested Loop Join（SNLJ）

    // 偽代碼
    for (r in R) {
        for (s in S) {
            if (r satisfy condition s) {
                output <r, s>;
            }
        }
    }

SNLJ就是兩層循環(huán)全量掃描連接的兩張表，得到符合條件的兩條記錄則輸出，這也就是讓兩張表做笛卡爾積，比較次數(shù)是R * S，是比較暴力的算法，會比較耗時。

2、Index Nested Loop Join（INLJ）

    // 偽代碼
    for (r in R) {
        for (si in SIndex) {
            if (r satisfy condition si) {
                output <r, s>;
            }
        }
    }

INLJ是在SNLJ的基礎(chǔ)上做了優(yōu)化，通過連接條件確定可用的索引，在Inner Loop中掃描索引而不去掃描數(shù)據(jù)本身，從而提高Inner Loop的效率。 而INLJ也有缺點，就是如果掃描的索引是非聚簇索引，并且需要訪問非索引的數(shù)據(jù)，會產(chǎn)生一個回表讀取數(shù)據(jù)的操作，這就多了一次隨機的I/O操作。

3、Block Nested Loop Join（BNLJ）

一般情況下，MySQL優(yōu)化器在索引可用的情況下，會優(yōu)先選擇使用INLJ算法，但是在無索引可用，或者判斷full scan可能比使用索引更快的情況下，還是不會選擇使用過于粗暴的SNLJ算法。 這里就出現(xiàn)了BNLJ算法了，BNLJ在SNLJ的基礎(chǔ)上使用了join buffer，會提前讀取Inner Loop所需要的記錄到buffer中，以提高Inner Loop的效率。

    // 偽代碼
    for (r in R) {
        for (sbu in SBuffer) {
            if (r satisfy condition sbu) {
                output <r, s>;
            }
        }
    }

MySQL中控制join buffer大小的參數(shù)名是join_buffer_size。

We only store the used columns in the join buffer, not the whole rows.<br/>join-buffer-size

根據(jù)MySQL手冊中的說法，join_buffer_size緩沖的是被使用到的列。

算法比較(外表大小R，內(nèi)表大小S)：

\algorithm comparison\	Simple Nested Loop Join	Index Nested Loop Join	Block Nested Loop Join
外表掃描次數(shù)	1	1	1
內(nèi)表掃描次數(shù)	R	0
比較次數(shù)	R * S	R * IndexHeight	R * S
回表次數(shù)	0	RS_Matches	0

在MySQL5.6中，對INLJ的回表操作進行了優(yōu)化，增加了Batched Key Access Join（批量索引訪問的表關(guān)聯(lián)方式，這樣翻譯可以不。。。）和Multi Range Read（mrr，多范圍讀?。┨匦裕趈oin操作中緩存所需要的數(shù)據(jù)的rowid，再批量去獲取其數(shù)據(jù)，把I/O從多次零散的操作優(yōu)化為更少次數(shù)批量的操作，提高效率。

感謝各位的閱讀，以上就是“Nested Loop的Join有哪些用法”的內(nèi)容了，經(jīng)過本文的學(xué)習后，相信大家對Nested Loop的Join有哪些用法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！