執行計劃為：
??? 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 是一種美德！
?
?
參考資源：
1）wwh999，2006，進行多表查時的排序問題,其多表查詢時的原理論證！?；
2）de.cel，2012，MySQL中的Join 原理及優化思路?；
3）火丁，2013，MySQL優化的奇技淫巧之STRAIGHT_JOIN；
?
贈圖一枚：
聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
