解析MySQL?join查詢(xún)的原理
MySQL用Nested-Loop Join算法實(shí)現(xiàn)join查詢(xún)
區(qū)分驅(qū)動(dòng)表和被驅(qū)動(dòng)表，以驅(qū)動(dòng)表的結(jié)果集為循環(huán)的基礎(chǔ)，訪(fǎng)問(wèn)被驅(qū)動(dòng)表過(guò)濾數(shù)據(jù)，然后合并結(jié)果，驅(qū)動(dòng)表在外循環(huán)、被驅(qū)動(dòng)表在內(nèi)循環(huán)。
如果還有第三張參與join查詢(xún)的表，則以合并的結(jié)果為驅(qū)動(dòng)表，第三張表作為被驅(qū)動(dòng)表，以此類(lèi)推。
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的開(kāi)銷(xiāo)如下（最大情況下）：
掃描A表1次；
掃描B表N次。
總共讀取記錄數(shù)：N + N * M。
為了專(zhuān)注于理解Nested-Loop Join，這里不討論帶where子句的情況，以下相同。
BNLJ
Block Nested-Loop Join
假設(shè)A是驅(qū)動(dòng)表，B是被驅(qū)動(dòng)表。
用來(lái)join的字段在被驅(qū)動(dòng)表沒(méi)有建立索引
Join Buffer
MySQL會(huì)將驅(qū)動(dòng)表結(jié)果集中（多條記錄）用來(lái)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查詢(xún)的表數(shù)量。
A表有N行，B表有M行。
BNLJ的開(kāi)銷(xiāo)如下（最大情況下）：
掃描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)表。
用來(lái)join的字段在被驅(qū)動(dòng)表建立了索引
聚集索引
非聚集索引
在這里我們假設(shè)您已對(duì)MySQL的索引結(jié)構(gòu)有了一定的了解，
如果沒(méi)有的話(huà)，可以去看下：通過(guò)B+Tree平衡多叉樹(shù)理解InnoDB引擎的聚集和非聚集索引
這里會(huì)掃描A表，用A的結(jié)果集作為外循環(huán)，
然后通過(guò)B表的索引來(lái)檢索，不會(huì)遍歷B表。
A表有N行，B表有M行。
INLJ的開(kāi)銷(xiāo)如下（最大情況下）：
掃描A表1次；
通過(guò)B表索引檢索N次，成本比掃描B表N次會(huì)低很多；
回表：先找到非聚集索引，再找到聚集索引，會(huì)多一次磁盤(pán)IO。
NLJ優(yōu)先級(jí)
INLJ>BNLJ>SNLJ
如何優(yōu)化join查詢(xún)效率
盡量將小表作為驅(qū)動(dòng)表，大表作為被驅(qū)動(dòng)表；
為參加join的字段在被驅(qū)動(dòng)表建立聚集索引，其次是非聚集索引；
盡可能減少join的字段數(shù)量，或者使用長(zhǎng)度比較小的字段來(lái)join，這樣Join Buffer一次可以緩存更多條記錄。
inner join時(shí)，MySQL會(huì)自動(dòng)將小表作為驅(qū)動(dòng)表，大表作為被驅(qū)動(dòng)表。
掃描整張表是成本非常高的操作。
到此這篇關(guān)于MySQL join查詢(xún)的原理的文章就介紹到這了,更多相關(guān)MySQL join查詢(xún)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
最近在深入學(xué)習(xí)hibernate,在進(jìn)行批量操作時(shí),發(fā)現(xiàn)hibernate批量操作性能非常低.于是就想找一個(gè)性能較高的方法,在對(duì)jdbc、jdbcTemplate、hibernate進(jìn)行測(cè)試后,發(fā)現(xiàn)jdbc的執(zhí)行效率是最高的,jdbcTemplate也很相近,hibernate就不考慮了,慘不忍睹啊2012-08-08
Mysql主從三種復(fù)制模式(異步復(fù)制,半同步復(fù)制,組復(fù)制)
這篇文章主要介紹了Mysql主從三種復(fù)制模式(異步復(fù)制,半同步復(fù)制,組復(fù)制),MySQL異步復(fù)制是主從復(fù)制過(guò)程中默認(rèn)的復(fù)制模式,下文簡(jiǎn)單介紹，感興趣的朋友可以參考一下2022-08-08
gorm操作MySql數(shù)據(jù)庫(kù)的方法
這篇文章主要介紹了gorm操作MySql數(shù)據(jù)庫(kù)的方法,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
本篇文章是對(duì)mysql慢查詢(xún)?cè)O(shè)置進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
這篇文章主要介紹了記一次MySQL更新語(yǔ)句update的踩坑，幫助大家更好的理解和使用MySQL的更新語(yǔ)句，感興趣的朋友可以了解下2020-11-11
MySQL數(shù)據(jù)庫(kù)JDBC編程詳解流程
JDBC是指Java數(shù)據(jù)庫(kù)連接，是一種標(biāo)準(zhǔn)Java應(yīng)用編程接口（?JAVA?API），用來(lái)連接?Java?編程語(yǔ)言和廣泛的數(shù)據(jù)庫(kù)。從根本上來(lái)說(shuō)，JDBC?是一種規(guī)范，它提供了一套完整的接口，允許便攜式訪(fǎng)問(wèn)到底層數(shù)據(jù)庫(kù)，本篇文章我們來(lái)了解MySQL連接JDBC的流程方法2022-01-01
這篇文章主要介紹了mysql中ROW_FORMAT的選擇問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-10-10
MySQL關(guān)鍵字IN與EXISTS的使用與區(qū)別詳解
in和exists是兩種常用的條件查詢(xún)關(guān)鍵字,兩種常用于子查詢(xún),它們?cè)谀承┣闆r下可以互換使用,但它們的工作方式和效率可能會(huì)有所不同,這篇文章主要給大家介紹了關(guān)于MySQL關(guān)鍵字IN與EXISTS的使用與區(qū)別的相關(guān)資料,需要的朋友可以參考下2024-09-09
本文主要介紹了mysql拆分字符串作為查詢(xún)條件的示例代碼，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-07-07
mysql密碼忘記后如何修改密碼(2022年最新版詳細(xì)教程保姆級(jí))
因?yàn)殚L(zhǎng)時(shí)間不操作mysql而忘記root密碼的朋友估計(jì)不在少數(shù),下面這篇文章主要給大家介紹了關(guān)于mysql密碼忘記后如何修改密碼的相關(guān)資料,本教程是2022年最新版詳細(xì)教程保姆級(jí),需要的朋友可以參考下2022-04-04