mysql 如何優(yōu)化left join
? 今天遇到一個left join優(yōu)化的問題亏栈，搞了一下午顾翼，中間查了不少資料挡毅，對MySQL的查詢計劃還有查詢優(yōu)化有了更進一步的了解，做一個簡單的記錄：
select c.* from hotel_info_original c
left join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
where h.hotel_id is null
? 這個sql是用來查詢出c表中有h表中無的記錄暴构，所以想到了用left join的特性（返回左邊全部記錄，右表不滿足匹配條件的記錄對應(yīng)行返回null）來滿足需求段磨，不料這個查詢非常慢取逾。先來看查詢計劃：
? rows代表這個步驟相對上一步結(jié)果的每一行需要掃描的行數(shù)，可以看到這個sql需要掃描的行數(shù)為35773*8134苹支，非常大的一個數(shù)字砾隅。本來c和h表的記錄條數(shù)分別為40000+和10000+，這幾乎是兩個表做笛卡爾積的開銷了（select * from c,h）债蜜。
于是我上網(wǎng)查了下MySQL實現(xiàn)join的原理晴埂，原來MySQL內(nèi)部采用了一種叫做 nested loop join的算法究反。Nested Loop Join 實際上就是通過驅(qū)動表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后一條一條的通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個表中查詢數(shù)據(jù)儒洛，然后合并結(jié)果精耐。如果還有第三個參與 Join，則再通過前兩個表的 Join 結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)琅锻，再一次通過循環(huán)查詢條件到第三個表中查詢數(shù)據(jù)卦停，如此往復(fù)，基本上MySQL采用的是最容易理解的算法來實現(xiàn)join恼蓬。所以驅(qū)動表的選擇非常重要惊完，驅(qū)動表的數(shù)據(jù)小可以顯著降低掃描的行數(shù)。
那么為什么一般情況下join的效率要高于left join很多处硬？很多人說不明白原因小槐，只人云亦云，我今天下午感悟出來了一點荷辕。一般情況下參與聯(lián)合查詢的兩張表都會一大一小凿跳，如果是join，在沒有其他過濾條件的情況下MySQL會選擇小表作為驅(qū)動表桐腌，但是left join一般用作大表去join小表拄显，而left join本身的特性決定了MySQL會用大表去做驅(qū)動表，這樣下來效率就差了不少案站，如果我把上面那個sql改成
select c.* from hotel_info_original c
join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
查詢計劃如下：
? ? 很明顯躬审，MySQL選擇了小表作為驅(qū)動表，再配合(hotel_id,hotel_type)上的索引瞬間降低了好多個數(shù)量級蟆盐。承边。。石挂。博助。
另外，我今天還明白了一個關(guān)于left join 的通用法則痹愚，即：如果where條件中含有右表的非空條件（除開is null）富岳，則left join語句等同于join語句，可直接改寫成join語句拯腮。
后記：
隨著查看MySQL reference manual對這個問題進行了更進一步的了解窖式。MySQL在執(zhí)行join時會把join分為system/const/eq_ref/ref/range/index/ALl等好幾類，連接的效率從前往后
依次遞減动壤，對于我的第一個sql萝喘，連接類型是index，所以幾乎是全表掃描的效果。但是我很奇怪我在(hotel_id,hotel_type)兩列上聲明了unique key阁簸，根據(jù)官方文檔連接類型應(yīng)該是eq_ref才對爬早，
? ? 這個問題一直困擾了我兩天，在google和stackoverflow上都沒有找到能夠解釋這個問題的文章启妹，莫非我這個問題無解了筛严？抱著解決這個問題的決心今天又翻看了一遍MySQL官方文檔
關(guān)于優(yōu)化查詢的部分，看到了這樣一句：這里的一個問題是MySQL能更高效地在聲明具有相同類型和尺寸的列上使用索引。我感覺我找到了問題所在，于是我將original和 collection表的(hotel_type,hotel_id)的encoding和collation（決定字符比較的規(guī)則）全部改成統(tǒng)一的utf8_general_ci乍恐，然后再次運行第一條sql的查詢計劃茴她，得到如下結(jié)果：
? ? 連接類型已經(jīng)由index優(yōu)化到了ref，如果將hotel_type申明為not null可以優(yōu)化到eq_ref，不過這里影響不大了，優(yōu)化后這條sql能在0.01ms內(nèi)運行完。
? ? 那么如何優(yōu)化left join：
1网杆、條件中盡量能夠過濾一些行將驅(qū)動表變得小一點，用小表去驅(qū)動大表
2伊滋、右表的條件列一定要加上索引（主鍵碳却、唯一索引、前綴索引等）笑旺，最好能夠使type達到range及以上（ref,eq_ref,const,system）
3昼浦、無視以上兩點，一般不要用left join~~筒主！
