MySQL 表連接配接優化

概述

表連接配接是業務場景中經常會出現的一種SQL類型，一般的，複雜的業務會存在大量的表連接配接查詢。就性能而言，多表的連接配接的性能會很低。做好表連接配接優化也是提升業務性能的重要方面

表連接配接語句

認識表連接配接之前，先認識下表連接配接的類型。一般的，表三種常見的表連接配接方式:

• INNER JOIN：如果表中有至少一個比對，則傳回行
• LEFT JOIN：即使右表中沒有比對，也從左表傳回所有的行
• RIGHT JOIN：即使左表中沒有比對，也從右表傳回所有的行

inner join

隻顯示兩表互相比對的行

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a inner join user_info b on a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老師 | 唱跳rap |
| 2 | 藍天 | 1 | 浙江杭州 | 司機 | 閱讀，汽車 |
| 3 | 小白 | 1 | 上海 | 程式員 | 遊戲，電影 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
傳回行數：[3]，耗時：6 ms.           

left join

可以看到，左表中有的行而右表中的沒有，右表的字段會用null值顯示出來

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a left join user_info b on a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老師 | 唱跳rap |
| 2 | 藍天 | 1 | 浙江杭州 | 司機 | 閱讀，汽車 |
| 3 | 小白 | 1 | 上海 | 程式員 | 遊戲，電影 |
| 5 | 庫洛洛 | 2 | 四川成都 | | |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
傳回行數：[4]，耗時：3 ms.           

right join

右表中有的左表中沒有行，會顯示右表中的行，左表中會null值顯示

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a right join user_info b on a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老師 | 唱跳rap |
| 2 | 藍天 | 1 | 浙江杭州 | 司機 | 閱讀，汽車 |
| 3 | 小白 | 1 | 上海 | 程式員 | 遊戲，電影 |
| | | | | 宇航員 | 悠悠球，籃球 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
傳回行數：[4]，耗時：4 ms.           

MySQL表連接配接算法

Nested-Loop Join算法

mysql的基本表連接配接算法是Nested-Loop Join，也就是我們常說的嵌套循環算法。一個簡單的嵌套循環連接配接（NLJ）算法一次一個循環地從第一個表中讀取行，将每一行傳遞給一個嵌套循環，該循環處理連接配接中的下一個表 。

對于一個t1,t2,t3，官方文檔上給出一個僞代碼如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}           

這裡是假設有三張表t1,t2,t3.分别使用類型為range，ref，all來連接配接，從僞代碼中可以看出

t3表會被全表掃，然後依次去讀取t1和t2上 的資料，t3一般就被稱為驅動表，MySQL 的優化器自稱是可以智能選擇結果集最小的表作為驅動表，根據算法來看，結果集較小的驅動表确實可以使循環次數減少，達到優化的目的。

Block Nested-Loop join算法

除了嵌套循環之外，官方文檔上還介紹了 一種塊循環算法，将外層循環的資料存在join buffer中，内層循環中的表會和buffer中的資料進行對比，進而減少循環次數

官方文檔給出一個表達式，來表述循環次數的關系

(S * C)/join_buffer_size + 1           

以上面的t1,t2,t3三表連接配接為例，S表示t1，t2組合在緩存中的大小，C是這些組合在buffer中的數量，整個式子就是t3被掃描的次數

可以看出，join_buffer_size越大，掃描的次數越小，但是這個優化有上限，當join_buffer_size大到能夠緩存所有之前的行組合，那麼就是性能最好的時候，再增大這個值，也就沒有優化效果了。

join 優化總結

從基礎算法可以看出，一般情況下，這種嵌套循環的算法消耗非常高，尤其是時間上的消耗，我們在建立涉及到表連接配接的SQL時，一定要選擇最優性能的SQL

一般的，表連接配接的SQL性能優化可從這幾個方面入手

• 索引優化

  在有索引的情況下，MySQL會嘗試使用Index Nested-Loop Join算法，相對于簡單的嵌套循環，性能會好一些。這就需要在必要的字段上建上索引

那對于左連接配接和右連接配接，建索引的列有不同，對于left join，左邊的表會全部傳回，右邊則隻傳回比對到的行，是以一般的，左連接配接的連接配接條件字段中，右表的字段最好需要一個索引，用來過濾不需要的行。相對的，對于right join 而言，表連接配接字段的左表字段的索引就顯得十分重要了

那有時除了比對字段之外，表連接配接會伴随着一些條件語句，如：

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a left join user_info b on a.user_id = b.ifnoid where a.sex = 1;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老師 | 唱跳rap |
| 2 | 藍天 | 1 | 浙江杭州 | 司機 | 閱讀，汽車 |
| 3 | 小白 | 1 | 上海 | 程式員 | 遊戲，電影 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+           

對于這些有條件字段的語句，毫無疑問，需要索引的支援，過濾掉無需要的行數，來減少循環的次數。進而使SQL執行的更加迅速

• 驅動表優化

  當進行多表連接配接查詢時，驅動表的定義為：

1）指定了聯接條件時，滿足查詢條件的記錄行數少的表為驅動表

2）未指定聯接條件時，行數少的表為驅動表

注意：如果搞不清楚該讓誰做驅動表，誰join誰，可以讓MySQL運作時自行判斷

對于left和right join mysql的驅動表選擇一般都是需要傳回全部行的那個表，對于inner join和full join，一般情況下，mysql自己的優化器，智能選擇較少的表作為驅動表，這樣循環的效率會高很多。

此外，根據驅動表的字段進行排序，也會使表連接配接性能提升

select * from t1 a left join t2 b on a.id = b.id left join t3 c a.id = c.id ORDER BY a.id DESC ;           

如果你實在難以确定驅動表的選擇，可以去掉join,如下，mysql會選擇最少的表作為驅動表，這會對你的選擇給出建議

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a,user_info b where a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老師 | 唱跳rap |
| 2 | 藍天 | 1 | 浙江杭州 | 司機 | 閱讀，汽車 |
| 3 | 小白 | 1 | 上海 | 程式員 | 遊戲，電影 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+           

• join_buffer_size

  在join的操作需求，MySQL在完成某些join需求的時候（all row join/all index /scan join）為了減少參與join的“被驅動表”的讀取次數以提高性能，需要使用到join buffer來協助完成join操作，當join buffer 太小，MySQL不會将該buffer存入磁盤檔案而是先将join buffer中的結果與需求join的表進行操作，然後清空join buffer中的資料，繼續将剩餘的結果集寫入次buffer中，如此往複，這勢必會造成被驅動表需要被多次讀取，成倍增加IO通路，降低效率（執行計劃中如果現實using join buffer）           

  也可以從上面的循環次數的配置可以看出，當join_buffer_size的值增大時，是可以減少循環的次數，相當于進行了一些優化

(S * C)/join_buffer_size + 1           

MySQL5.7版本這個參數的預設值256K ，若是感覺過小，可以适當增加。最大值不能超過3GB