mysql的left join和inner join的效率對比，以及如何優化

一、前言

最近在寫程式碼的時候，遇到了需要多表連線的一個問題，初始sql類似於：

select * from a left join b on a.x = b.x left join c on c.y = b.y left join d on d.z=c.z

      這樣的多個left join組合，一方面是心裡有點不舒服，總覺得這種寫法是有問題的，一方面有有點好奇，直接用inner join會怎樣呢？差別在哪裡？後續使用inner join發現速度要比left join快一些，所以這邊就研究一下這個問題。

二、left join為什麼會比 inner join 慢

1、關於邏輯運算量

      關於left join的概念，大家是都知道的（返回左邊全部記錄，右表不滿足匹配條件的記錄對應行返回null），那麼單純的對比邏輯運算量的話，inner join 是隻需要返回兩個表的交集部分，left join多返回了一部分左表沒有返回的資料。

2、關於mysql連線的演算法 Nest Loop Join（巢狀聯接迴圈）

這個演算法是mysql預設的連線演算法，類似於我們php程式的三個巢狀迴圈：

(foreach a as v){
	(foreach b as v1){
		(foreach c as v2){
		}
	}
}

      從演算法上來看，根據mysql文件，inner join在連線的時候，mysql會自動選擇較小的表來作為驅動表，從而達到減少迴圈次數的目的。我們在使用left join表的時候，預設是使用左表作為驅動表，那麼此時左表的大小是我們來控制的，如果控制不當，左表比較大，那麼自然迴圈次數也會變多，效率會下降。

MySQL Nested-Loop Join演算法學習

      根據這兩方面的對比，left join明顯被秒成渣，但是我們的實際業務卻經常需要使用left join，一切還是要以實際業務為主，所以大家還是仁者見仁智者見智的選擇吧。博主這裡因為業務並不是很需要left join，所以果斷選擇使用inner join來連線表。

三、關於 left join的優化

根據上面我們們的對比，基本可以總結出來一些簡單的優化方案。

1、left join選擇小表作為驅動表（這部分基本是大家的共識）

2、如果左表比較大，並且業務要求驅動表必須是左表，那麼我們可以通過where條件語句，使得左表被過濾的小一些，主要原理和第一條類似

3、關聯欄位給索引，因為在mysql的巢狀迴圈演算法中，是通過關聯欄位進行關聯，並查詢的，所以給關聯欄位索引很必要

4、如果sql裡面有排序，請給排序欄位加上索引，不然會造成排序使用全表掃描
		參考：https://www.oschina.net/question/930697_2190172
		
5、如果where條件中含有右表的非空條件（除開is null），則left join語句等同於join語句，可直接改寫成join語句。 

6、根據文件，MySQL能更高效地在宣告具有相同型別和尺寸的列上使用索引。所以把表與表之間的關聯欄位給上encoding和collation（決定字元比較的規則）全部改成統一的型別

7、右表的條件列一定要加上索引（主鍵、唯一索引、字首索引等），最好能夠使type達到range及以上（ref,eq_ref,const,system） 

注意： 此處的5、6、7參考部落格：https://luxuryzh.iteye.com/blog/1976004 ，這個部落格總結的很好，強烈推薦

推薦部落格閱讀：

MySQL索引原理以及慢查詢優化

      博主在查過各種資料之後，最終把自己的連線型別改為了inner join，並且給關聯欄位以及排序欄位都加上了索引，速度比起剛開始的時候確實是快了不少。只是說mysql還有很多需要研究的地方，加油吧，碰到並解決，不虧~

end