[慢查優化]聯表查詢注意誰是驅動表你搞不清楚誰join誰更好時請放手讓mysql自行判定...

寫在前面的話：

?? 不要求每個人一定理解聯表查詢(join/left join/inner join等)時的mysql運算過程；

???不要求每個人一定知道線上（現在或未來）哪張表數據量大，哪張表數據量小；

????但把mysql客戶端（如SQLyog，如HeidiSQL）放在桌面上，時不時拿出來 explain 一把，這是一種美德！

在實例講解之前，我們先回顧一下聯表查詢的基礎知識。

——聯表查詢的基礎知識——

引子：為什么第一個查詢using temporary，第二個查詢不用臨時表呢？

下面兩個查詢，它們只差了一個order by，效果卻迥然不同。

第一個查詢：

EXPLAIN extended

SELECT ads.id

FROM ads, city?

WHERE

? ?city.city_id = 8005

? ?AND ads.status = 'online'

? ?AND city.ads_id=ads.id

ORDER BY?ads.id?desc

執行計劃為：

??? id? select_type? table?? type??? possible_keys?? key????? key_len? ref???????????????????? rows? filtered? Extra???????????????????????? ?
------? -----------? ------? ------? --------------? -------? -------? --------------------? ------? --------? -------------------------------
???? 1? SIMPLE?????? city??? ref???? ads_id,city_id? city_id? 4??????? const?????????????????? 2838??? 100.00? Using temporary; Using filesort
???? 1? SIMPLE?????? ads???? eq_ref? PRIMARY???????? PRIMARY? 4??????? city.ads_id?????? 1??? 100.00? Using where???????????????????

第二個查詢：

EXPLAIN extended

SELECT ads.id

FROM ads,city?

WHERE

? ?city.city_id =8005

? ?AND ads.status = 'online'

? ?AND city.ads_id=ads.id

ORDER BY?city.ads_id?desc

執行計劃里沒有了using temporary：

id? select_type? table?? type??? possible_keys?? key????? key_len? ref???????????????????? rows? filtered? Extra???????????????????? ?
------? -----------? ------? ------? --------------? -------? -------? --------------------? ------? --------? ---------------------------
???? 1? SIMPLE?????? city??? ref???? ads_id,city_id? city_id? 4??????? const?????????????????? 2838??? 100.00? Using where; Using filesort
???? 1? SIMPLE?????? ads??? eq_ref? PRIMARY???????? PRIMARY? 4??????? city.ads_id?????? 1??? 100.00? Using where???????????????

為什么？

DBA告訴我們：

MySQL 表關聯的算法是 Nest Loop Join，是通過驅動表的結果集作為循環基礎數據，然后一條一條地通過該結果集中的數據作為過濾條件到下一個表中查詢數據，然后合并結果。

EXPLAIN 結果中，第一行出現的表就是驅動表（Important!）

以上兩個查詢語句，驅動表都是 city，如上面的執行計劃所示！

對驅動表可以直接排序，對非驅動表（的字段排序）需要對循環查詢的合并結果（臨時表）進行排序（Important!）

因此，order by ads.id desc 時，就要先 using temporary 了！

驅動表的定義

wwh999?在 2006年總結說，當進行多表連接查詢時，?[驅動表]?的定義為：
1）指定了聯接條件時，滿足查詢條件的記錄行數少的表為[驅動表]；
2）未指定聯接條件時，行數少的表為[驅動表]（Important!）。

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

既然“未指定聯接條件時，行數少的表為[驅動表]”了，

而且你也對自己寫出的復雜的 Nested Loop Join 不太有把握（如下面的實例所示），

就別指定誰 left/right join 誰了，

請交給 MySQL優化器運行時決定吧。

如果您對自己特別有信心，可以像火丁一樣做優化。

小結果集驅動大結果集

de.cel?在2012年總結說，不管是你，還是 MySQL，

優化的目標是盡可能減少JOIN中Nested Loop的循環次數，

以此保證：

永遠用小結果集驅動大結果集（Important!）！

——實例講解——

Nested Loop Join慢查SQL語句

先了解一下 mb 表有千萬級記錄，mbei 表要少得多。慢查實例如下：

explain

SELECT mb.id, ……

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

WHERE 1=1 ?

ORDER BY mbei.apply_time DESC

limit 0,10

夠復雜吧。Nested Loop Join 就是這樣，

以驅動表的結果集作為循環的基礎數據，然后將結果集中的數據作為過濾條件一條條地到下一個表中查詢數據，最后合并結果；此時還有第三個表，則將前兩個表的 Join 結果集作為循環基礎數據，再一次通過循環查詢條件到第三個表中查詢數據，如此反復。

這條語句的執行計劃如下：

id? select_type? table?? type??? possible_keys?? key???????????? key_len? ref???????????????????? rows? Extra????????????????????????????????????? ?
------? -----------? ------? ------? --------------? --------------? -------? -------------------? -------? --------------------------------------------
???? 1? SIMPLE?????? mb????? index?? userid????????? userid????????? 4??????? (NULL)?????????????? 6060455? Using index; Using temporary; Using filesort
???? 1? SIMPLE?????? mbei??? eq_ref? mb_id? mb_id? 4??????? mb.id???????????? 1???????????????????????????????????????????? ?
???? 1? SIMPLE?????? u?????? eq_ref? PRIMARY???????? PRIMARY???????? 4??????? mb.uid??????? 1? Using index????????????????????????????????

由于動用了“LEFT JOIN”，所以攻城獅已經指定了驅動表，雖然這張驅動表的結果集記錄數達到百萬級！

如何優化？

優化第一步：LEFT JOIN改為JOIN

干嘛要 left join 啊？直接 join！

explain

SELECT mb.id……

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

WHERE 1=1 ?

ORDER BY mbei.apply_time DESC

limit 0,10

立竿見影，驅動表立刻變為小表 mbei 了， Using temporary 消失了，影響行數少多了：

id? select_type? table?? type??? possible_keys?? key????? key_len? ref???????????????????????????? rows? Extra??????? ?
------? -----------? ------? ------? --------------? -------? -------? ----------------------------? ------? --------------
???? 1? SIMPLE?????? mbei??? ALL???? mb_id? (NULL)?? (NULL)?? (NULL)???????????????????????? 13383? 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：根據驅動表的字段排序，好嗎？

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最小：

id? select_type? table?? type??? possible_keys?? key???????????? key_len? ref??????????????????? rows? Extra???? ?
------? -----------? ------? ------? --------------? --------------? -------? -------------------? ------? -----------
???? 1? SIMPLE?????? mb????? index?? userid????????? PRIMARY???????? 4??????? (NULL)?????????????????? 10??????????? ?
???? 1? SIMPLE?????? mbei??? eq_ref? mb_id? mb_id? 4??????? mb.id??????????? 1? Using index
???? 1? SIMPLE?????? u?????? eq_ref? PRIMARY???????? PRIMARY???????? 4??????? mb.uid?????? 1? Using index

優化第二步：去除所有JOIN，讓MySQL自行決定！

寫這么多密密麻麻的 left join/inner join 很開心嗎？

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??

最后的總結：

強調再強調：

不要過于相信你的運氣！

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

請拿起 explain 武器，

如果你看到以下現象，請優化：

出現了Using temporary；
rows過多，或者幾乎是全表的記錄數；
key 是 (NULL)；
possible_keys 出現過多（待選）索引。

?

記住，explain 是一種美德！