join算法

mysql只支持一種join算法：Nested-Loop Join（嵌套循環連接），但Nested-Loop Join有三種變種：

• Simple Nested-Loop Join，簡單嵌套
• Index Nested-Loop Join，索引嵌套
• Block Nested-Loop Join ，join buffer緩沖區嵌套（臨時表）

驅動表和非驅動表的區別

驅動表就是主表，非驅動表就是從表，看以下sql就知道了，A就是驅動表，B就是非驅動表。

select * from A left join B

A join B 連表時，一定先查A表再查B表嗎？

答案是不一定，因為mysql內部有一個優化器，它會根據你查詢語句做一些優化，先查哪張表也是由優化器決定的，但可以肯定的是，先查詢的那張表就是驅動表，反之就是非驅動表；關于是那張表先查的問題，我們通過看執行計劃來得出結果；在前面加上explain關鍵字即可;

explain select * from A join B;

1、Simple Nested-Loop Join，簡單嵌套-無索引的情況

A left join B： ，每次都是全表匹配，A表的每行數據都全表匹配一次B表，也就是說，假如我A表有10條數據，B表有1000條數據，那么查詢的時候掃描次數就是10*1000，也就說查詢時需要掃描10000遍才能得出數據；

2、Index Nested-Loop Join-有索引的情況

select * from A join B on A.id=B.id where A.id = 1：在查詢時，驅動表A會根據關聯字段的索引進行查找，當在索引上找到符合的值，在回表進行查詢，也就是說得匹配到索引后才會才會進行回表查詢；
如果非驅動表B的關聯關聯字段B.id是主鍵的話，性能會非常高，如果不是主鍵，會進行多次回表查詢，先關聯索引，然后根據二級索引的主鍵id進行回表查詢，性能上比主鍵要慢；

3、Block Nested-Loop Join ，join buffer緩沖區

如果有索引，會選取Index Nested-Loop Join進行連表，如果join列沒有索引，就會采用Block Nested-Loop Join ，join buffer，在驅動表和非驅動表中間有個buffer的緩沖區，在查詢時先將驅動表的數據緩存到buffer緩沖區內，然后批量與非驅動表進行匹配，這是一種將多次比較合并為一次比較的優化方案，注意：這里緩存的不只是關聯表的列，select 后面的列也會緩存起來；

緩沖區大小

默認情況下buffer緩沖區join_biffer_size的容量為256k，如果說你的數據空間大于256k，就無法使用緩沖區了，轉為最簡單的循環嵌套Simple Nested-Loop Join，但是我們可以手動調整緩沖區大小來裝入大容量的數據；查看join_biffer_size的sql：show variables like '%join_biffer_size%'

數據量大的表和數據量小的表如何選擇連接順序

最好由小表去連接大表，這樣會減少掃描次數；比如大表有1000條數據，小表只有10條數據，那么最好的連接方式為：小表 join 大表；為什么要這么做呢？

• 如果是大表 join 小表，假如我們的數據在大表的第999行，那么查詢數據的時候就至少得掃描999次才能查出來；
• 如果是小表 join 大表，假如我們的數據在小表的第9行，

細節

• 連表查詢的時候，最好不要超過三張表，因為需要join的字段，數據類型必須一致
• 優先使用內連接來連表，外連接查詢數據消耗的性能比內連接要高；
• 確保關聯查詢中on 后面的列或者 using()中的字段帶有索引，連表時可加快數據訪問
  

到此這篇關于淺談mysql join底層原理的文章就介紹到這了,更多相關mysql join底層原理內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！