JOIN查詢(xún)?cè)?br> 如果有兩張數(shù)據(jù)結(jié)構(gòu)一樣的表（id-主鍵） ,（a有索引） ,（b無(wú)索引）。其中表t1（100條數(shù)據(jù)） 和t2（1000條數(shù)據(jù)），他們做join查詢(xún)的時(shí)候，內(nèi)部執(zhí)行的原理是什么呢？
INLJ-（Index Nested-Loop Join）
// 使用straight_join表示，固定指定 t1是驅(qū)動(dòng)表，t2是被驅(qū)動(dòng)表，防止優(yōu)化器優(yōu)化
select * from t1 straight_join t2 on (t1.a=t2.a);
- 1.從表t1中讀取一行數(shù)據(jù)R
- 2.從R中取出字段a去t2中查找
- 3.取出t2中滿(mǎn)足要求的數(shù)據(jù)，和R合并組成結(jié)果集中的一條數(shù)據(jù)
- 4.重復(fù)1到3的步驟，流程圖如下
上述因?yàn)楸或?qū)動(dòng)表中使用了索引故，該join方法我們稱(chēng)之為（NLJ），這個(gè)流程中。對(duì)于表t1掃描的全表，故掃描了100行。對(duì)于表t2因?yàn)樽吡怂饕臉?shù)搜索，故t2表也是掃描了100行，索引這個(gè)join操作執(zhí)行了200次掃描。這時(shí)如果反過(guò)來(lái)t2作為驅(qū)動(dòng)表，則需要掃描2000次數(shù)據(jù)，故使用NLJ的時(shí)候，盡量使用小表作為驅(qū)動(dòng)表
試想以下，如果上述t2沒(méi)有使用索引，那么t1查詢(xún)出的R對(duì)應(yīng)查詢(xún)t2的數(shù)據(jù)時(shí)，每次都要全表遍歷1000次，那么查詢(xún)的次數(shù)就要達(dá)到，100*1000=10W次查詢(xún)了，這種查詢(xún)方法叫做Simple Nested-Loop Join（SNLJ），因?yàn)樾蕦?shí)在太低，所以mysql根本沒(méi)有使用這種方法。而是使用的Block Nested-Loop Join
BNLJ （Block Nested-Loop Join）
對(duì)于t1的數(shù)據(jù)并沒(méi)有一條條讀取，而是將t1的數(shù)據(jù)一次性加載到j(luò)oin_buffer的緩存中，然后掃描表t2與join_buffer中的每條數(shù)據(jù)做比對(duì)，最終一共掃描數(shù)據(jù)的次數(shù)是100+1000=1100次，大大增加了效率
不過(guò)join_buffer 的內(nèi)存是有限的，如果join_buffer中放不下t1的表的所有數(shù)據(jù)，那么他會(huì)將數(shù)據(jù)分幾次來(lái)放，所以驅(qū)動(dòng)表t1的數(shù)據(jù)越小，分的次數(shù)也就越小，查詢(xún)的效率就會(huì)越高
從上訴的BLJ還是NLJ算法得知，驅(qū)動(dòng)表盡可能的要使用小表，但是什么數(shù)據(jù)條數(shù)少的表就是小表么？
案例一：
對(duì)于上面的數(shù)據(jù)，我們執(zhí)行以下語(yǔ)句
select * from t1 straight_join t2 on(t1.b=t2.b) where t2.id<50
select * from t2 straight_join t1 on(t1.b=t2.b) where t2.id<50
這時(shí)，t2增加了where條件，那么t2作為驅(qū)動(dòng)表，加載到j(luò)oin_buffer中的大小則只有50條，這時(shí)t2才是小表
案例二：
select t1.id ,t2.* from t1 straight_join t2 on(t1.b=t2.b) where t1.id<100 and t2.id < 100
select t1.id ,t2.* from t2 straight_join t1 on(t1.b=t2.b) where t1.id<100 and t2.id < 100
這時(shí)，t1 和t2都增加了where條件，條數(shù)都是100條，但是t1只查詢(xún)了id列，所以這時(shí)t1是小表
