1、問題復現
今天優化一個多表JOIN語句，調整順序后，使性能提升7倍，這個提升在我以往文章中算是很低的提升了，但在實際性能優化過程中，不要只去優化那種能提升十倍，百倍的SQL, 哪怕是一個SQL要執行5ms優化到3ms也是有意義的，而且大多數優化均是小幅提升，特別是那種執行頻繁的SQL,優化幾ms對性能也是極大的提升。
經過簡化后的慢SQL如下
select * from a
left join b on a.xx=b.xx
left join c on b.xx= c.xx
left join d on c.xx = d.xx
left join e on d.xx = e.xx
left join e1 on d.xx = e1.xx
left join e2 on c.xx =e2.xx
left join e3 on d.xx = e3.xx
left join e4 on c.xx =e4.xx
left join f on b.xx = f.xx
left join g on f.xx = g.xx
where a.col = ? and b.col =? and f.col =? and g.col =?
該SQL執行時間為16.7秒
2、問題排查
a、排查第一步 查看執行計劃
執行計劃如下
從執行計劃可以看到。符合條件的驅動表行數=51758，
但在join第二張表后數據膨脹到了 434939行
join 第九張表后數據訊速下降到170行。
b、檢查JOIN順序
從上面的SQL可以看到 join的第九張表為
left join f on b.xx = f.xx
f表join的依賴順序為a,b 只需要a,b表之后即可
那我們通過hint強制指定join順序，下面寫出兩種指定順序的方法，在MySQL8.0版本支持。5.X版本不支持。
#通過JOIN_ORDER指定
select
/*+ JOIN_ORDER(a,b,f) */
* from a
left join b on a.xx=b.xx
left join c on b.xx= c.xx
left join d on c.xx = d.xx
left join e on d.xx = e.xx
left join e1 on d.xx = e1.xx
left join e2 on c.xx =e2.xx
left join e3 on d.xx = e3.xx
left join e4 on c.xx =e4.xx
left join f on b.xx = f.xx
left join g on f.xx = g.xx
where a.col = ? and b.col =? and f.col =? and g.col =?
#通過JOIN_FIXED_ORDER指定
select
/*+
qb_name(qb1)
JOIN_FIXED_ORDER(@qb1)
*/
* from a
left join b on a.xx=b.xx
left join f on b.xx = f.xx
left join c on b.xx= c.xx
left join d on c.xx = d.xx
left join e on d.xx = e.xx
left join e1 on d.xx = e1.xx
left join e2 on c.xx =e2.xx
left join e3 on d.xx = e3.xx
left join e4 on c.xx =e4.xx
left join g on f.xx = g.xx
where a.col = ? and b.col =? and f.col =? and g.col =?
再次查看執行計劃
通過執行計劃看到在第三張表后。行數就訊速下降到170行了。而因為join順序依賴關系，f表是不能放在b表前面的。
這里多說一句，為什么明明是left join join后的行數下降了。這是因為雖然寫的是left join 只要where 條件上有left join 右表的過濾條件。 MySQL會自動優化為 inner join 這個行為是可觀測的，如果不知道怎么觀測這個left join 自動轉化為inner join的?？梢栽u論區留言，我給大家寫個演示。
通過執行計劃可以看到最終執行時間為2.67S
這里還有另外一優化手段：將b,f表寫到子查詢中先過濾，這個優化手法叫延遲關聯，在這篇文章中就不展開了。有興趣的可參見這篇MySQL/SQL Server分頁優化（三）
不需要做多大的調整去改寫SQL.只需要調整join順序就有這樣的性能提升。
3、多表join該如何確定順序
a、驅動表的選擇
多表join 第一件大事就是確定驅動表，MySQL join 原則：小表驅動大表。所以常規來說一定要小表做驅動表。
select * from a
inner join b on a.xx = b.xx
where a.xx=? and b.xx=?
原則上就是a與b哪個表小，那個表就會被選為驅動表。 但這里有一個情況會打破這個常規。如果大表的where條件過濾性很好。比如a表數據10W. b表數據1000W 但執行b.xx=?后。b表只會剩下30W。 那這個時候也該選b表做驅動表
b、被驅表的順序排列
join 關系只會有兩種 1：1 和1：N
join順序就該把1:1的放在前面。這樣就不會導致join數據迅速膨脹。然后再按照N的大小來排，N的大小可以通過 show index from tablename的區分度+表行數來估算
區分度大+行數小的也要先join
最后再排區分度小+表行數大的表，這類表往往是導致join結果快速膨脹的兇手。
還有一個特列就是 where條件有很好的過濾性。能大幅過濾行數的也要放在join順序靠前位置。
本貼的SQL就屬于這種情況。
