解析MySQL?join查詢的原理
MySQL用Nested-Loop Join算法實現(xiàn)join查詢
區(qū)分驅(qū)動表和被驅(qū)動表,以驅(qū)動表的結(jié)果集為循環(huán)的基礎(chǔ),訪問被驅(qū)動表過濾數(shù)據(jù),然后合并結(jié)果,驅(qū)動表在外循環(huán)、被驅(qū)動表在內(nèi)循環(huán)。
如果還有第三張參與join查詢的表,則以合并的結(jié)果為驅(qū)動表,第三張表作為被驅(qū)動表,以此類推。
left join中的左表是驅(qū)動表、右表是被驅(qū)動表,right join剛好相反。
Nested-Loop Join有三種實現(xiàn)
SNLJ
Simple Nested-Loop Join
假設(shè)A是驅(qū)動表,B是被驅(qū)動表。
這里會掃描A表,用A的結(jié)果集作為外循環(huán),
每循環(huán)一次,會掃描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ū)動表,B是被驅(qū)動表。
用來join的字段在被驅(qū)動表沒有建立索引
Join Buffer
MySQL會將驅(qū)動表結(jié)果集中(多條記錄)用來join的字段緩存到Join Buffer,
Join Buffer的特點是只需要掃描被驅(qū)動表一次,就能得到Join Buffer中所有記錄的匹配結(jié)果,
減少掃描的次數(shù)。
Join Buffer默認大小256k,會生成n-1個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ū)動表,B是被驅(qū)動表。
用來join的字段在被驅(qū)動表建立了索引
聚集索引
非聚集索引
在這里我們假設(shè)您已對MySQL的索引結(jié)構(gòu)有了一定的了解,
如果沒有的話,可以去看下:通過B+Tree平衡多叉樹理解InnoDB引擎的聚集和非聚集索引
這里會掃描A表,用A的結(jié)果集作為外循環(huán),
然后通過B表的索引來檢索,不會遍歷B表。
A表有N行,B表有M行。
INLJ的開銷如下(最大情況下):
掃描A表1次;
通過B表索引檢索N次,成本比掃描B表N次會低很多;
回表:先找到非聚集索引,再找到聚集索引,會多一次磁盤IO。
NLJ優(yōu)先級
INLJ>BNLJ>SNLJ
如何優(yōu)化join查詢效率
盡量將小表作為驅(qū)動表,大表作為被驅(qū)動表;
為參加join的字段在被驅(qū)動表建立聚集索引,其次是非聚集索引;
盡可能減少join的字段數(shù)量,或者使用長度比較小的字段來join,這樣Join Buffer一次可以緩存更多條記錄。
inner join時,MySQL會自動將小表作為驅(qū)動表,大表作為被驅(qū)動表。
掃描整張表是成本非常高的操作。
到此這篇關(guān)于MySQL join查詢的原理的文章就介紹到這了,更多相關(guān)MySQL join查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
簡單了解MySQL union all與union的區(qū)別
這篇文章主要介紹了簡單了解MySQL union all與union的區(qū)別,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-03-03設(shè)置MySQL中的數(shù)據(jù)類型來優(yōu)化運行速度的實例
這篇文章主要介紹了設(shè)置MySQL中索引的數(shù)據(jù)類型來優(yōu)化運行速度的實例,主要是適當使用短字節(jié)的數(shù)據(jù)類型來處理短索引,需要的朋友可以參考下2015-05-05淺談開啟magic_quote_gpc后的sql注入攻擊與防范
通過啟用php.ini配置文件中的相關(guān)選項,就可以將大部分想利用SQL注入漏洞的駭客拒絕于門外2012-01-01mysql 5.7更改數(shù)據(jù)庫的數(shù)據(jù)存儲位置的解決方法
隨著MySQL數(shù)據(jù)庫存儲的數(shù)據(jù)逐漸變大,已經(jīng)將原來的存儲數(shù)據(jù)的空間占滿了,導(dǎo)致mysql已經(jīng)鏈接不上了。所以要給存放的數(shù)據(jù)換個地方,下面小編給大家分享mysql 5.7更改數(shù)據(jù)庫的數(shù)據(jù)存儲位置的解決方法,一起看看吧2017-04-04