Join的類型

• left join，以左表為驅(qū)動表，以左表作為結(jié)果集基礎(chǔ)，連接右表的數(shù)據(jù)補齊到結(jié)果集中

• right join，以右表為驅(qū)動表，以右表作為結(jié)果集基礎(chǔ)，連接左表的數(shù)據(jù)補齊到結(jié)果集中

• inner join，結(jié)果集取兩個表的交集

• full join，結(jié)果集取兩個表的并集

• mysql沒有full join，union取代

  union與union all的區(qū)別為，union會去重

• cross join 笛卡爾積

• 如果不使用where條件則結(jié)果集為兩個關(guān)聯(lián)表行的乘積

  與，的區(qū)別為，cross join建立結(jié)果集時會根據(jù)on條件過濾結(jié)果集合

• straight_join

• 嚴(yán)格根據(jù)SQL順序指定驅(qū)動表，左表是驅(qū)動

Join原理

本質(zhì)上可以理解為嵌套循環(huán)的操作，驅(qū)動表作為外層for循環(huán)，被驅(qū)動表作為內(nèi)層for循環(huán)。根據(jù)連接組成數(shù)據(jù)的策略可以分為三種算法。

Simpe Nested-Loop Join

• 連接比如有A表，B表，兩個表JOIN的話會拿著A表的連表條件一條一條在B表循環(huán)，匹配A表和B表相同的id 放入結(jié)果集，這種效率是最低的。

Index Nested-Loop Join

• 執(zhí)行流程（磁盤掃描）

• 從表t1中讀入一行數(shù)據(jù) R；

  從數(shù)據(jù)行R中，取出a字段到表t2里進(jìn)行樹搜索查找；

  取出表t2中滿足條件的行，跟R組成一行，作為結(jié)果集的一部分；

  重復(fù)執(zhí)行步驟1到3，直到表t1的末尾循環(huán)結(jié)束。

• 而對于每一行R，根據(jù)a字段去表t2查找，走的是樹搜索過程。

Block Nested-Loop Join

• mysql使用了一個叫join buffer的緩沖區(qū)去減少循環(huán)次數(shù)，這個緩沖區(qū)默認(rèn)是256KB，可以通過命令show variables like 'join_%'查看
• 其具體的做法是，將第一表中符合條件的列一次性查詢到緩沖區(qū)中，然后遍歷一次第二個表，并逐一和緩沖區(qū)的所有值比較，將比較結(jié)果加入結(jié)果集中
• 只有當(dāng)JOIN類型為ALL，index，rang或者是index_merge的時候才會使用join buffer，可以通過explain查看SQL的查詢類型。

Join優(yōu)化

• 為了優(yōu)化join算法采用Index nested-loop join算法，在連接字段上建立索引字段
• 使用數(shù)據(jù)量小的表去驅(qū)動數(shù)據(jù)量大的表
• 增大join buffer size的大?。ㄒ淮尉彺娴臄?shù)據(jù)越多，那么外層表循環(huán)的次數(shù)就越少）
• 注意連接字段的隱式轉(zhuǎn)換與字符編碼，避免索引失效

到此這篇關(guān)于一文詳解MySQL Join使用原理的文章就介紹到這了,更多相關(guān)MySQL Join原理內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論