MySQL用Nested-Loop Join算法實(shí)現(xiàn)join查詢

區(qū)分驅(qū)動(dòng)表和被驅(qū)動(dòng)表，以驅(qū)動(dòng)表的結(jié)果集為循環(huán)的基礎(chǔ)，訪問被驅(qū)動(dòng)表過濾數(shù)據(jù)，然后合并結(jié)果，驅(qū)動(dòng)表在外循環(huán)、被驅(qū)動(dòng)表在內(nèi)循環(huán)。
如果還有第三張參與join查詢的表，則以合并的結(jié)果為驅(qū)動(dòng)表，第三張表作為被驅(qū)動(dòng)表，以此類推。

left join中的左表是驅(qū)動(dòng)表、右表是被驅(qū)動(dòng)表，right join剛好相反。

Nested-Loop Join有三種實(shí)現(xiàn)

SNLJ

Simple Nested-Loop Join

假設(shè)A是驅(qū)動(dòng)表，B是被驅(qū)動(dòng)表。

這里會(huì)掃描A表，用A的結(jié)果集作為外循環(huán)，
每循環(huán)一次，會(huì)掃描B表一遍(遍歷內(nèi)循環(huán)）

A表有N行，B表有M行。

SNLJ的開銷如下（最大情況下）：

掃描A表1次；
掃描B表N次。
總共讀取記錄數(shù)：N + N * M。

為了專注于理解Nested-Loop Join，這里不討論帶where子句的情況，以下相同。

BNLJ

Block Nested-Loop Join

假設(shè)A是驅(qū)動(dòng)表，B是被驅(qū)動(dòng)表。

用來join的字段在被驅(qū)動(dòng)表沒有建立索引

Join Buffer
MySQL會(huì)將驅(qū)動(dòng)表結(jié)果集中（多條記錄）用來join的字段緩存到Join Buffer，
Join Buffer的特點(diǎn)是只需要掃描被驅(qū)動(dòng)表一次，就能得到Join Buffer中所有記錄的匹配結(jié)果，
減少掃描的次數(shù)。

Join Buffer默認(rèn)大小256k，會(huì)生成n-1個(gè)Join Buffer緩沖區(qū)，n為參與join查詢的表數(shù)量。

A表有N行，B表有M行。

BNLJ的開銷如下（最大情況下）：

掃描A表1次；
掃描B表X次；
X的大小取決于N、join字段的大小、Join Buffer的大小，通常X<<N。

INLJ

Index Nested-Loop Join

假設(shè)A是驅(qū)動(dòng)表，B是被驅(qū)動(dòng)表。

用來join的字段在被驅(qū)動(dòng)表建立了索引

聚集索引

非聚集索引

在這里我們假設(shè)您已對(duì)MySQL的索引結(jié)構(gòu)有了一定的了解，
如果沒有的話，可以去看下：通過B+Tree平衡多叉樹理解InnoDB引擎的聚集和非聚集索引

這里會(huì)掃描A表，用A的結(jié)果集作為外循環(huán)，
然后通過B表的索引來檢索，不會(huì)遍歷B表。

A表有N行，B表有M行。

INLJ的開銷如下（最大情況下）：

掃描A表1次；
通過B表索引檢索N次，成本比掃描B表N次會(huì)低很多；
回表：先找到非聚集索引，再找到聚集索引，會(huì)多一次磁盤IO。

NLJ優(yōu)先級(jí)

INLJ>BNLJ>SNLJ

如何優(yōu)化join查詢效率

盡量將小表作為驅(qū)動(dòng)表，大表作為被驅(qū)動(dòng)表；
為參加join的字段在被驅(qū)動(dòng)表建立聚集索引，其次是非聚集索引；
盡可能減少join的字段數(shù)量，或者使用長度比較小的字段來join，這樣Join Buffer一次可以緩存更多條記錄。

inner join時(shí)，MySQL會(huì)自動(dòng)將小表作為驅(qū)動(dòng)表，大表作為被驅(qū)動(dòng)表。

掃描整張表是成本非常高的操作。

到此這篇關(guān)于MySQL join查詢的原理的文章就介紹到這了,更多相關(guān)MySQL join查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！