MySQL Using temporary; Using filesort INNER JOIN最佳化

標籤：mysq   target   ima   str   強制   避免   關聯表   www   status   

問題

通過「SHOW FULL PROCESSLIST」語句很容易就能查到問題SQL，如下：

SELECT post.*FROM postINNER JOIN post_tag ON post.id = post_tag.post_idWHERE post.status = 1 AND post_tag.tag_id = 123ORDER BY post.created DESCLIMIT 100

說明：因為post和tag是多對多的關係，所以存在一個關聯表post_tag。

試著用EXPLAIN查詢一下SQL執行計畫（篇幅所限，結果有刪減）：

+----------+---------+-------+-----------------------------+| table    | key     | rows  | Extra                       |+----------+---------+-------+-----------------------------+| post_tag | tag_id  | 71220 | Using where; Using filesort || post     | PRIMARY |     1 | Using where                 |+----------+---------+-------+-----------------------------+

下面給出最佳化後的SQL，唯一的變化就是把串連方式改成了「STRAIGHT_JOIN」：

SELECT post.*FROM postSTRAIGHT_JOIN post_tag ON post.id = post_tag.post_idWHERE post.status = 1 AND post_tag.tag_id = 123ORDER BY post.created DESCLIMIT 100

試著用EXPLAIN查詢一下SQL執行計畫（篇幅所限，結果有刪減）：

+----------+----------------+--------+-------------+| table    | key            | rows   | Extra       |+----------+----------------+--------+-------------+| post     | status_created | 119340 | Using where || post_tag | post_id        |      1 | Using where |+----------+----------------+--------+-------------+

對比最佳化前後兩次EXPLAIN的結果來看，最佳化後的SQL雖然「rows」更大了，但是沒有了「Using filesort」，綜合來看，效能依然得到了提升。

提醒：注意兩次EXPLAIN結果中各個表出現的先後順序，稍後會解釋。

解釋

對第一條SQL而言，為什麼MySQL最佳化器選擇了一個耗時的執行方案？對第二條SQL而言，為什麼把串連方式改成STRAIGHT_JOIN之後就提升了效能？

這一切還得從MySQL對多表串連的處理方式說起，首先MySQL最佳化器要確定以誰為驅動表，也就是說以哪個表為基準，在處理此類問題時，MySQL最佳化器採用了簡單粗暴的解決方案：哪個表的結果集小，就以哪個表為驅動表，當然MySQL最佳化器實際的處理方式會複雜許多，具體可以參考：MySQL最佳化器如何選擇索引和JOIN順序。

說明：在EXPLAIN結果中，第一行出現的表就是驅動表。

繼續post串連post_tag的例子，MySQL最佳化器有如下兩個選擇，分別是：

• 以post為驅動表，通過status_created索引過濾，結果集119340行
• 以post_tag為驅動表，通過tag_id索引過濾，結果集71220行

顯而易見，post_tag過濾的結果集更小，所以MySQL最佳化器選擇它作為驅動表，可悲催的是我們還需要以post表中的created欄位來排序，也就是說排序欄位不在驅動表裡，於是乎不可避免的出現了「Using filesort」，甚至「Using temporary」。

知道了來龍去脈，最佳化起來就容易了，要儘可能的保證排序欄位在驅動表中，所以必須以post為驅動表，於是乎必須藉助「STRAIGHT_JOIN」強制串連順序。

實際上在某些特殊情況裡，排序欄位可以不在驅動表裡，比如驅動表結果集只有一行記錄，並且在串連其它表時，索引除了串連欄位，還包含了排序欄位，此時串連表後，索引中的資料本身自然就是排好序的。

既然聊到這裡順帶說點題外話，大家可能會遇到類似下面的問題：原本運行良好的查詢語句，過了一段時間後，可能會突然變得很糟糕。一個很大可能的原因就是資料分布情況發生了變化，從而導致MySQL最佳化器對驅動表的選擇發生了變化，進而出現索引失效的情況，所以沒事最好多查查，關注一下這些情況。

MySQL Using temporary; Using filesort INNER JOIN最佳化