常聽說MySQL中3表 join 的執(zhí)行流程并不是前兩張表 join 得出結(jié)果，再與第三張表進(jìn)行 join；而是3表嵌套的循環(huán)連接。那這個3表嵌套的循環(huán)連接具體又是個什么流程呢？與前兩張表 join 得出結(jié)果再與第三張表進(jìn)行 join 的執(zhí)行效率相比如何呢？下面通過一個例子來分析分析。
創(chuàng)新互聯(lián)公司客戶idc服務(wù)中心，提供雅安服務(wù)器托管、成都服務(wù)器、成都主機(jī)托管、成都雙線服務(wù)器等業(yè)務(wù)的一站式服務(wù)。通過各地的服務(wù)中心，我們向成都用戶提供優(yōu)質(zhì)廉價的產(chǎn)品以及開放、透明、穩(wěn)定、高性價比的服務(wù)，資深網(wǎng)絡(luò)工程師在機(jī)房提供7*24小時標(biāo)準(zhǔn)級技術(shù)保障。
set optimizer_switch='block_nested_loop=off';
關(guān)聯(lián)字段無索引的情況下強(qiáng)制使用索引嵌套循環(huán)連接算法，目的是更好的觀察掃描行數(shù)。
表結(jié)構(gòu)和數(shù)據(jù)如下：
示例SQL：
通過 slow log 得知一共掃描 24100 行：
執(zhí)行計劃顯示用的索引嵌套循環(huán)連接算法：
掃描行數(shù)構(gòu)成：
總行數(shù)=100+4000+20000=24100。
從這個結(jié)果來看，join 過程像是先 t1 和 t3 join 得出 20 行中間結(jié)果，再與 t2 進(jìn)行 join 得出結(jié)果。這結(jié)論與我們通常認(rèn)為的 3表 join 實際上是3表嵌套的循環(huán)連接不一樣，接著往下看。
查看執(zhí)行計劃成本：
mysql explain format=json select * from t1 join t2 on t1.b=t2.b join t3 on t1.b=t3.b where t1.a21\G
其他信息：
IO成本= 1*1.0 =1
CPU成本= 100*0.2 =20
t1總成本=21
IO成本= 1*1.0 =1
CPU成本= 200*0.2 =40
t3表總成本= 驅(qū)動表扇出*(IO成本+CPU成本) = 20*(1+40) =820
階段性總成本= 21+820 =841
此處 eval_cost=80，實則為 驅(qū)動表扇出*被驅(qū)動每次掃描行數(shù)*filtered*成本常數(shù) ，即 20*200*10%*0.2 。
簡化公式為： eval_cost=rows_produced_per_json*成本常數(shù)
IO成本= 4*1.0 =4
CPU成本= 1000*0.2 =200
t2表總成本= 前2表join的扇出*(IO成本+CPU成本) = 400*(4+200) =81600
階段性總成本= 841+81600 =82441
此處 eval_cost=8000，即 rows_produced_per_json*成本常數(shù) ，即 40000*0.2
根據(jù)執(zhí)行計劃成本分析：
這樣看，3表 join 流程是：
注意，由于造的數(shù)據(jù)比較特殊，所以第 3 步得出的中間結(jié)果集實際上只有 1行，所以最終 t2 表的查找次數(shù)是 20*1=20 ，所以掃描總行數(shù)是 20*1000 。所以單看 slow log 中顯示的 24100 行，會誤認(rèn)為是先得出 t1 和 t3 join 的結(jié)果，再去和 t2 進(jìn)行 join。
當(dāng)我調(diào)整 t3 的數(shù)據(jù)，刪除20行，再插入20行，使?jié)M足 b21 的數(shù)據(jù)翻倍，這樣“第 3 步得出的中間結(jié)果集”變成 2 行：
再來看slow log 中掃描的總行數(shù)為44100，t1、t3的掃描行數(shù)不變，t2 的掃描行數(shù)變?yōu)? 20*2*1000=40000 ：
為什么執(zhí)行計劃中分析得到的是 t2 表查找 400 次呢？
因為執(zhí)行計劃對t1 join t3 的扇出是個估算值，不準(zhǔn)確。而 slow log 是真實執(zhí)行后統(tǒng)計的，是個準(zhǔn)確值。
為什么執(zhí)行計劃中，t2表的執(zhí)行次數(shù)是用“t1 join t3 的扇出”表示的？這不是說明 t1 先和 t3 join，結(jié)果再和 t2 join？
其實拆解來看，“3表嵌套循環(huán)” 和 “前2表 join 的結(jié)果和第3張表 join” 兩種算法，成本是一樣的，而且如果要按3表嵌套循環(huán)的方式展示每張表的成本將非常復(fù)雜，可讀性不強(qiáng)。所以執(zhí)行計劃中這么表示沒有問題。
總的來說，對于3表join或者多表join 來說，“3表嵌套循環(huán)” 和 “先2表 join，結(jié)果和第3張表join” 兩種算法，成本是一樣的。要注意的一點是3表嵌套循環(huán)成本并非如下圖寫的：n m x，而是 n (m+a x)，其中 a 為 t2 滿足單個等值條件的平均值。
當(dāng)被驅(qū)動表的關(guān)聯(lián)字段不是唯一索引，或者沒有索引，每次掃描行數(shù)會大于1時，其扇出誤差會非常大。比如在上面的示例中：
t3 實際的扇出只有 20，但優(yōu)化器估算值是 總掃描行數(shù)的 10%，由于t3表的關(guān)聯(lián)字段沒有索引，所以每次都要全表掃描200行，總的掃描行數(shù)= 20*200 =4000，扇出= 4000*10% =400，比實際的20大了20倍。尤其對于后續(xù)表的 join 來說，成本估算會產(chǎn)生更嚴(yán)重的偏差。
如果是 left join，每個被驅(qū)動表的 filtered 都會被優(yōu)化器認(rèn)定為 100%，誤差更大！
通常建議join不超過2表，就是因為優(yōu)化器估算成本誤差大導(dǎo)致選擇不好的執(zhí)行計劃，如果要用，一定要記?。宏P(guān)聯(lián)字段必須要有索引，最好有唯一性或者基數(shù)大。
數(shù)據(jù)庫多表關(guān)聯(lián)，一般采用外鍵比較方便，也可以額外建一個連接表做多表關(guān)聯(lián)的連接，但這樣稍微有點兒復(fù)雜，這些是建表方面的關(guān)聯(lián)。查詢關(guān)聯(lián)，可以采用多表查詢的方式關(guān)聯(lián)查詢，這點要求稍高點兒，但關(guān)聯(lián)后再操作單表時，別的表不用受太大的影響，這點特好。
---table1指的是第一張表，table2指的是第二張表，table3指的是第三張表，
select??a.uid,a.uname,a.upsw,a.urealname,a.utel,a.remark,b.rname,b.rremark,c.deptname,c.deptremark?from?table1?a,table2?b,?table3?c?where?a.sems_role_rid=b.rid?and?a.udeptid=c.deptid
可以關(guān)聯(lián)查詢的，如果兩個表的字段有關(guān)聯(lián)的話，同名表的關(guān)聯(lián)可以這樣寫
select x.*, y.*, z.* from x as xx
left join y as yy on xx.cate = yy.scid
left join z as zz on zz.cate = zz.scid
我不是很明白你說的三個表的區(qū)域之間的關(guān)聯(lián)，這個可能有錯你可以改改
如果兩個表相同名稱的字段相關(guān)聯(lián)的話條件就是aa.name = bb.name
重慶分公司
重慶分公司
