寫在前面

1、不要求每個(gè)人一定理解 聯(lián)表查詢(join/left join/inner join等)時(shí)的MySQL運(yùn)算過程

2、不要求每個(gè)人一定知道線上（現(xiàn)在或未來）哪張表數(shù)據(jù)量大，哪張表數(shù)據(jù)量小

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

在實(shí)例講解之前，我們先回顧一下聯(lián)表查詢的基礎(chǔ)知識

聯(lián)表查詢的基礎(chǔ)知識

引子：為什么第一個(gè)查詢using temporary，第二個(gè)查詢不用臨時(shí)表呢？

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

第一個(gè)查詢：

EXPLAIN extendedSELECT ads.id FROM ads, city WHERE   city.city_id = 8005   AND ads.status = 'online'   AND city.ads_id=ads.idORDER BY ads.id desc
執(zhí)行計(jì)劃為：
id  select_type table  type    possible_keys  key          key_len  ref             rows  filtered  Extra1   SIMPLE        city    ref      ads_id,city_id   city_id     4            const          2838  100.00   Using temporary; Using filesort1   SIMPLE        ads    eq_ref PRIMARY          PRIMARY 4            city.ads_id   1        100.00   Using where
第二個(gè)查詢：
EXPLAIN extendedSELECT ads.id FROM ads,city WHERE   city.city_id =8005   AND ads.status = 'online'   AND city.ads_id=ads.idORDER BY city.ads_id desc
執(zhí)行計(jì)劃里沒有了using temporary：
id  select_type table type    possible_keys  key           key_len  ref              rows  filtered  Extra1   SIMPLE        city   ref      ads_id,city_id   city_id      4           const          2838  100.00   Using where; Using filesort1   SIMPLE        ads   eq_ref  PRIMARY         PRIMARY  4           city.ads_id   1       100.00    Using where
為什么？DBA告訴我們：
MySQL 表關(guān)聯(lián)的算法是 Nest Loop Join，是通過驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后一條一條地通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個(gè)表中查詢數(shù)據(jù)，然后合并結(jié)果。
EXPLAIN 結(jié)果中，第一行出現(xiàn)的表就是驅(qū)動(dòng)表（Important!）
以上兩個(gè)查詢語句，驅(qū)動(dòng)表都是 city，如上面的執(zhí)行計(jì)劃所示！
對驅(qū)動(dòng)表可以直接排序，對非驅(qū)動(dòng)表（的字段排序）需要對循環(huán)查詢的合并結(jié)果（臨時(shí)表）進(jìn)行排序（Important!）
因此，order by ads.id desc 時(shí)，就要先 using temporary 了！
驅(qū)動(dòng)表的定義
wwh999 在 2006年總結(jié)說，當(dāng)進(jìn)行多表連接查詢時(shí)， [驅(qū)動(dòng)表] 的定義為：
1）指定了聯(lián)接條件時(shí)，滿足查詢條件的記錄行數(shù)少的表為[驅(qū)動(dòng)表]
2）未指定聯(lián)接條件時(shí)，行數(shù)少的表為[驅(qū)動(dòng)表]（Important!）
忠告：如果你搞不清楚該讓誰做驅(qū)動(dòng)表、誰 join 誰，請讓 MySQL 運(yùn)行時(shí)自行判斷
既然“未指定聯(lián)接條件時(shí)，行數(shù)少的表為[驅(qū)動(dòng)表]”了，而且你也對自己寫出的復(fù)雜的 Nested Loop Join 不太有把握（如下面的實(shí)例所示），就別指定誰 left/right join 誰了，請交給 MySQL優(yōu)化器 運(yùn)行時(shí)決定吧。
如果您對自己特別有信心，可以像火丁一樣做優(yōu)化。
小結(jié)果集驅(qū)動(dòng)大結(jié)果集
de.cel 在2012年總結(jié)說，不管是你，還是 MySQL，優(yōu)化的目標(biāo)是盡可能減少JOIN中Nested Loop的循環(huán)次數(shù)。
以此保證：永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大結(jié)果集（Important!）！
實(shí)例講解
Nested Loop Join慢查SQL語句
先了解一下 mb 表有 千萬級記錄，mbei 表要少得多。慢查實(shí)例如下：
explainSELECT 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 DESClimit 0,10
夠復(fù)雜吧。Nested Loop Join 就是這樣，以驅(qū)動(dòng)表的結(jié)果集作為循環(huán)的基礎(chǔ)數(shù)據(jù)，然后將結(jié)果集中的數(shù)據(jù)作為過濾條件一條條地到下一個(gè)表中查詢數(shù)據(jù)，最后合并結(jié)果。
此時(shí)還有第三個(gè)表，則將前兩個(gè)表的 Join 結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，再一次通過循環(huán)查詢條件到第三個(gè)表中查詢數(shù)據(jù)，如此反復(fù)。
這條語句的執(zhí)行計(jì)劃如下：
id select_type table   type    possible_keys   key           key_len  ref       rows       Extra1  SIMPLE        mb      index   userid              userid       4           (NULL)  6060455 Using index; Using temporary; Using filesort1  SIMPLE        mbei   eq_ref  mb_id  mb_id   4               mb.id     11  SIMPLE        u        eq_ref  PRIMARY          PRIMARY  4            mb.uid   1          Using index
由于動(dòng)用了“LEFT JOIN”，所以攻城獅已經(jīng)指定了驅(qū)動(dòng)表，雖然這張驅(qū)動(dòng)表的結(jié)果集記錄數(shù)達(dá)到百萬級！
如何優(yōu)化？
優(yōu)化第一步：LEFT JOIN改為JOIN，干嘛要 left join ??？直接 join！
explainSELECT 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 DESClimit 0,10
立竿見影，驅(qū)動(dòng)表立刻變?yōu)樾”?mbei 了， Using temporary 消失了，影響行數(shù)少多了：
id select_type table  type     possible_keys     key         key_len  ref                rows    Extra1  SIMPLE        mbei  ALL       mb_id                 (NULL)      (NULL)   (NULL)          13383  Using filesort1  SIMPLE        mb     eq_ref  PRIMARY,userid  PRIMARY  4          mbei.mb_id  11  SIMPLE        u        eq_ref  PRIMARY            PRIMARY  4          mb.uid          1  Using index
優(yōu)化第一步之分支1：根據(jù)驅(qū)動(dòng)表的字段排序，好嗎？
left join不變。干嘛要根據(jù)非驅(qū)動(dòng)表的字段排序呢？我們前面說過“對驅(qū)動(dòng)表可以直接排序，對非驅(qū)動(dòng)表（的字段排序）需要對循環(huán)查詢的合并結(jié)果（臨時(shí)表）進(jìn)行排序！”的。
explainSELECT 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 DESClimit 0,10
也滿足業(yè)務(wù)場景，做到了rows最?。?/p>
id select_type table  type     possible_keys  key           key_len  ref        rows  Extra1  SIMPLE        mb     index   userid              PRIMARY  4            (NULL)   101  SIMPLE        mbei  eq_ref  mb_id  mb_id   		     4            mb.id     1       Using index1  SIMPLE        u        eq_ref  PRIMARY         PRIMARY  4            mb.uid   1       Using index
優(yōu)化第二步：去除所有JOIN，讓MySQL自行決定！寫這么多密密麻麻的 left join/inner join 很開心嗎？
explainSELECT mb.id…… FROM mb,mbei,u   WHERE     mb.id=mbei.mb_id    and mb.uid=u.user_idorder by mbei.apply_time desclimit 0,10
立竿見影，驅(qū)動(dòng)表一樣是小表 mbei：
id select_type table   type    possible_keys      key          key_len  ref                rows    Extra1  SIMPLE        mbei   ALL      mb_id  (NULL)      (NULL)      (NULL)                         13388 Using filesort1  SIMPLE        mb      eq_ref  PRIMARY,userid  PRIMARY 4            mbei.mb_id   11  SIMPLE        u         eq_ref  PRIMARY            PRIMARY 4            mb.uid           1  Using index
最后的總結(jié)：
強(qiáng)調(diào)再強(qiáng)調(diào)：
1、不要過于相信你的運(yùn)氣！
2、不要相信你的開發(fā)環(huán)境里SQL的執(zhí)行速度！
3、請拿起 explain 武器，如果你看到以下現(xiàn)象，請優(yōu)化：
1）出現(xiàn)了Using temporary
2）rows過多，或者幾乎是全表的記錄數(shù)
3）key 是 (NULL)
4）possible_keys 出現(xiàn)過多（待選）索引
記住，explain 是一種美德！