一文搞懂MySQL的Join，聊一聊秒殺架構設計

正文

MySQL的Join到底能不能用
經常聽到2種觀點：

• join性能低，盡量少用
• 多表join時，變為多個SQL進行多次查詢

其實對于上面的觀點一定程度上是正確的，但不是完全正確。但之所以流傳這么廣，主要還是沒有搞清楚實際狀態，而根據實際使用中總結出來的一些模糊規律。只有了解的MySQL的Join實際執行方式，就會知道上面2種觀點是一種模糊的規律，這種規律并不能指導我們實際開發。下面就說說MySQL的實際join執行方式。

MySQL的Join是如何執行的

join可以說一種集合的運算，比如left join,right join,inner join,full join,outer join，cross join等，這些集合間的計算關系對應在高中數學集合里面的交集，并集，補集，全集等。但在實際的代碼中，join運算基本上是通過多層循環來實現的。

舉一個例子，假設有t1,t2兩張表，表結構分別如下：

createtablet1(
idintnotnullAUTO_INCREMENT,
usernamevarchar(20)notnulldefault'',
ageintnotnulldefault0,
PRIMARYkey(`id`)
)ENGINE=INNODBDEFAULTCHARSET=UTF8MB4;

createtablet2(
idintnotnullauto_increment,
usernamevarchar(20)notnulldefault'',
scoreintnotnulldefalut0,
primarykey(`id`)
))ENGINE=INNODBDEFAULTCHARSET=UTF8MB4;

假設t1有100條數據，t2表有200條數

查詢sql為：

selectt1.*,t2.*fromt1leftjoint2on(t1.username=t2.username)

那么這條SQL的執行步驟如下：

1. 從表t1中取一行數據r1
2. 從r1中，取出字段username到表t2中查詢
3. 取出表t2中滿足條件的行，跟r1組成一行，作為結果集的一部份
4. 重復執行步驟1,2,3,直到表t1的所以數據循環完畢

基本上先遍歷t，1,然后根據t1中的每行數據中的username，去表t2中查找滿足條件的記錄。基本就是2層循環。

如何優化join查詢

從上面可以看出，join本質是循環，這里的開銷如下：

1. 遍歷t1數據，讀取數據為t1表的行數，假設行數為n,則復雜度也為n
2. 根據t1的匹配字段username去t2中一行一行的查詢數據
3. 這個過程，因為MySQL的數據存儲結構為二叉樹，時間復雜度為log2(m) m為t2表的總行數
4. 那么總復雜度近似為 n+n(2log2(m))

從上面的步驟可以看出，優化方向：

1. 降低t1查詢時的開銷，主要是磁盤io開銷，避免全表掃描，用索引
2. 降低t2查詢時的開銷，也用索引
3. 將數據量多的表做被驅動表，小表作驅動表，m取了對數，大表數據量大對復雜度的影響沒有線性增長
4. 緩存t1表，不用每次去磁盤load,比如一次緩存100條，那么能顯著降低磁盤讀數據次數，t2每次與緩存中的t1數據進行比較
5. 隨機磁盤讀比較耗費磁盤性能，轉為順序讀，因為二叉樹的存儲結構，每次非主鍵查找，有一個回表的動作，即根據主鍵再次查詢需要的數據

優化的基本方法：

1. 減少循環次數，減少磁盤IO次數，變隨機IO為順序IO
2. 其實MySQL針對上面的優化方法有對應的算法：
3. Simple Nested Loop Join 最普通的循環，這個要避免
4. Block Nested Loop Join 主要是針對t2表上沒有索引，在步驟2將t2中的每一行數據跟join buffer數據做對比，這樣將磁盤操作轉為內存操作進行比較，但是如果被驅動表的數據比較大的話，也影響性能，主要是cache pool被占滿，導致MySQL性能下降
5. Index Nested Join 就是都通過主鍵進行查找關聯，這種性能比較好

Batched Key Access Join 這個是 Index Nested Join上做的優化，因為回表的存在，隨機操作io也很耗費性能，這個算法的核心在于通過輔助索引去查找時，將得到的主鍵進行排序，然后按照主鍵遞增的順序進行查找，對磁盤的讀接近順序讀，從而優化性能

到底要不用Join

從上面的分析我們可以看到，用Join還是可行的，只要性能可控且在接受范圍內，還是能減少代碼復雜度的。需要避免的是join的表沒有索引，不然這樣的SQL發線上是災難性的。

總結

Join還是可以大膽的使用，只要把握好幾個原則：

1. 盡量讓join的列是索引列，而且最好是類型相同,盡可能是主鍵索引

2. 盡量將小表做驅動表（這一點MySQL在5.6某個版本后能自動完成）

3. 養成將寫好的SQL進行explain的好習慣，觀察SQL的執行過程