瞭解MySQL聯表查詢中的驅動表，最佳化查詢，以小表驅動大表

標籤：des   using   mysq   開發   col   運行   bsh   temporary   from   

一、為什麼要用小表驅動大表

1、驅動表的定義

當進行多表串連查詢時， [驅動表] 的定義為：

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

2）未指定聯結條件時，行數少的表為[驅動表]（Important!）

忠告：如果你搞不清楚該讓誰做驅動表、誰 join 誰，請讓 MySQL 運行時自行判斷

既然“未指定聯結條件時，行數少的表為[驅動表]”了，而且你也對自己寫出的複雜的 Nested Loop Join 不太有把握（如下面的執行個體所示），就別指定誰 left/right join 誰了，請交給 MySQL最佳化器 運行時決定吧。

如果您對自己特別有信心

2、mysql關聯查詢的概念:

MySQL 表關聯的演算法是 Nest Loop Join，是通過驅動表的結果集作為迴圈基礎資料，然後一條一條地通過該結果集中的資料作為過濾條件到下一個表中查詢資料，然後合并結果。

例: user表10000條資料，class表20條資料

select * from user u left join class c u.userid=c.userid

這樣則需要用user表迴圈10000次才能查詢出來，而如果用class表驅動user表則只需要迴圈20次就能查詢出來

例：

select * from class c left join user u c.userid=u.userid

小結果集驅動大結果集

de.cel 在2012年總結說，不管是你，還是 MySQL，最佳化的目標是儘可能減少JOIN中Nested Loop的迴圈次數。

以此保證：永遠用小結果集驅動大結果集（Important）！

二、最佳化聯表查詢

最佳化第一步之：根據驅動表的欄位排序

left join不變，幹嘛要根據非驅動表的欄位排序呢？我們前面說過“對驅動表可以直接排序，對非驅動表（的欄位排序）需要對迴圈查詢的合并結果（暫存資料表）進行排序！”的。

explain

SELECT mb.id……

FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid  

WHERE 1=1  

ORDER BY mb.id DESC

limit 0,10

也滿足業務情境，做到了rows最小：

最佳化第二步：去除所有JOIN，讓MySQL自行決定，explain第一張表就是驅動表，資料量比其它兩張表都要小！

explain
SELECT mb.id…… 
FROM mb,mbei,u   
WHERE 
    mb.id=mbei.mb_id
    and mb.uid=u.user_id
order by mbei.apply_time desc
limit 0,10

立竿見影，驅動表一樣是小表 mbei：

id select_type table   type    possible_keys      key          key_len  ref                rows    Extra
1  SIMPLE        mbei ALL      mb_id  (NULL)      (NULL)      (NULL)                         13388 Using filesort
1  SIMPLE        mb      eq_ref  PRIMARY,userid  PRIMARY 4            mbei.mb_id   1
1  SIMPLE        u         eq_ref  PRIMARY            PRIMARY 4            mb.uid           1  Using index

三、總結

1、不要過於相信你的運氣！

2、不要相信你的開發環境裡SQL的執行速度！

3、請拿起 explain 武器，如果你看到以下現象，請最佳化：

1）出現了Using temporary

2）rows過多，或者幾乎是全表的記錄數

3）key 是 (NULL)

4）possible_keys 出現過多（待選）索引

瞭解MySQL聯表查詢中的驅動表，最佳化查詢，以小表驅動大表