MYSQL數(shù)據(jù)庫(kù)基礎(chǔ)之Join操作原理

Join使用的是Nested-Loop Join算法，Nested-Loop Join有三種

select * from t1 join t2 on t1.a = t2.a;
-- a 100條數(shù)據(jù), b 1000條數(shù)據(jù)

Simple Nested-Loop Join

會(huì)遍歷t1全表，t1作為驅(qū)動(dòng)表，t1中的每一條數(shù)據(jù)都會(huì)到t2中做一次全表查詢，該過(guò)程會(huì)比較100*1000次。

每次在t2中做全表查詢時(shí)，全表掃描可就不保證在內(nèi)存里了，Buffer Pool會(huì)淘汰，有可能在磁盤(pán)。

Block Nested-Loop Join（MYSQL驅(qū)動(dòng)鏈接沒(méi)有使用索引）

會(huì)遍歷t1全表，將t1數(shù)據(jù)加載到j(luò)oin_buffer中，再遍歷t2全表，讓t2的每條數(shù)據(jù)去匹配join_buffer中t1緩存的數(shù)據(jù)。

t1全表掃描 = 100次

t2全表掃描 = 1000次

查詢次數(shù) = 1100次

join_buffer中比較 = 100 * 1000次

比較的次數(shù)和Simple Nested-Loop Join是一樣的，但是比較的過(guò)程會(huì)比Simple Nested-Loop Join快很多，性能更好。

join_buffer是有大小的，如果t1查出來(lái)的數(shù)據(jù)是大于join_buffer大小的，則會(huì)先加載部分t1中的數(shù)據(jù)，比較完t2以后，清空join_buffer，再加載t1中剩余數(shù)據(jù)，加載不完全，再重復(fù)該操作。

t1全表掃描次數(shù)和join_buffer中比較1次數(shù)不變，但是t2的掃描次數(shù)會(huì)根據(jù)分段次數(shù)做一個(gè)乘法。

假設(shè)，驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)是 N，需要分 K 段才能完成算法流程，被驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)是 M。

K = λ * N

掃描被驅(qū)動(dòng)表次數(shù) = M * λ * N

λ是和join_buffer的大小有關(guān)的，join_buffer大小足夠的情況下，大表驅(qū)動(dòng)和小表驅(qū)動(dòng)的時(shí)間是一樣的。

需要分段的情況下，分段次數(shù)越少，被驅(qū)動(dòng)表掃描的次數(shù)也會(huì)越少，所以應(yīng)該采用小表驅(qū)動(dòng)。

Index Nested-Loop Join（MYSQL驅(qū)動(dòng)鏈接使用索引）

還是以上面的sql為例，如果a字段是有索引的。

t1表會(huì)掃描全表，t1表中每條數(shù)據(jù)會(huì)去t2表中做索引查詢，查到id后再進(jìn)行回表查詢（如果連接字段是t2表的主鍵，回表操作將省略）。

t1掃描全表 = 100次

t2索引查詢 = log1000次

t2回表查詢 = log1000次

假設(shè)，驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)是 N，被驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)是 M。

總查詢次數(shù) = N + N * 2logM

由上可見(jiàn)，驅(qū)動(dòng)表數(shù)據(jù)越大，查詢的次數(shù)會(huì)越多，所以應(yīng)該使用小表作為驅(qū)動(dòng)表。

文章參考《MySQL實(shí)戰(zhàn)45講--第34講》

總結(jié)

到此這篇關(guān)于MYSQL數(shù)據(jù)庫(kù)基礎(chǔ)之Join操作原理的文章就介紹到這了,更多相關(guān)MYSQL Join原理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！