一文詳解MySQL Join使用原理

目錄

• Join的類型
• Join原理
• Simpe Nested-Loop Join
• Index Nested-Loop Join
• Block Nested-Loop Join
• Join優化

Join的類型

• left join，以左表為驅動表，以左表作為結果集基礎，連接右表的數據補齊到結果集中

• right join，以右表為驅動表，以右表作為結果集基礎，連接左表的數據補齊到結果集中

• inner join，結果集取兩個表的交集

• full join，結果集取兩個表的并集

• mysql沒有full join，union取代

  union與union all的區別為，union會去重

• cross join 笛卡爾積

• 如果不使用where條件則結果集為兩個關聯表行的乘積

  與，的區別為，cross join建立結果集時會根據on條件過濾結果集合

• straight_join

• 嚴格根據SQL順序指定驅動表，左表是驅動

Join原理

本質上可以理解為嵌套循環的操作，驅動表作為外層for循環，被驅動表作為內層for循環。根據連接組成數據的策略可以分為三種算法。

Simpe Nested-Loop Join

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

Index Nested-Loop Join

• 執行流程（磁盤掃描）

• 從表t1中讀入一行數據 R；

  從數據行R中，取出a字段到表t2里進行樹搜索查找；

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

  重復執行步驟1到3，直到表t1的末尾循環結束。

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

Block Nested-Loop Join

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

Join優化

• 為了優化join算法采用Index nested-loop join算法，在連接字段上建立索引字段
• 使用數據量小的表去驅動數據量大的表
• 增大join buffer size的大?。ㄒ淮尉彺娴臄祿蕉?，那么外層表循環的次數就越少）
• 注意連接字段的隱式轉換與字符編碼，避免索引失效

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