「看這篇就夠了」Mysql join條件是要寫在on裏還是在where裏？

一線碼農

對於join系列語句，大部分開發人員都經常用到。但是對於裏面的運行原理，我相信很少人真正認識，下面我們從幾個方面介紹下。

為了能夠覆蓋更多的點，這裏複制一位大佬的表和圖。我們先建兩個表和添加一批數據，注意只有a表的f1有索引，a表和B表的數據不完全一致
：

結果集區別

上圖可以看出，結果集是不一樣的，條件寫在ON裏，數據有6條，比條件放在where裏面多出2條。

算法區別

select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2)語句執行順序是：

1、先掃描a表的數據，放到join_buffer中，join_buffer的數據結構是數組。

2、順序
掃描b表，每一條數據跟join_buffer的a的數據進行on條件判斷，匹配則放入結果集中。最後a中未匹配的補上null，合並到結果集中返回。

以上這種查找方法就是Mysql的Block Nexted Loop Join（簡稱BNL）算法。b表是沒有索引情況下，順序掃描全表根據驅動表join buffer匹配，進而計算結果集！

select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2)語句執行是：

因為where條件中b.f2為null值，且mysql優化器會認為這sql具有優化空間。所以就將這個left join的語句優化為join：select * from a left join b where a.f1=b.f1 and a.f2=b.f2。也就是即使我們用了left join 在這裏也不能保證執行順序，因為優化器會進行優化。現在因為a表f1有索引，優化器優化後變成了b表是驅動表，a表是被驅動表，走的是IndexNested-Loop Join（簡稱NLJ）算法。

結論

1、如果要結果集包含左表全部數據，則條件寫在ON裏

2、如果要保證sql性能，可以寫在where裏面

關注我，下一篇繼續介紹這個join話題。

「看這篇就夠了」Mysql大表中查詢全表掃描是否會占用完內存？

「看這篇就夠了」Mysql事務提交是怎麼在保證性能情況下持久化？

「看這篇就夠了」Mysql幻讀的原理介紹

「看這篇就夠了」Mysql的limit有哪些優點，快來了解下吧