問題

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

SELECT post.*
FROM post
INNER JOIN post_tag ON post.id = post_tag.post_id
WHERE post.status = 1 AND post_tag.tag_id = 123
ORDER BY post.created DESC
LIMIT 100

   說明：因為post和tag是多對多的關(guān)系，所以存在一個關(guān)聯(lián)表post_tag。

   試著用EXPLAIN查詢一下SQL執(zhí)行計劃(篇幅所限，結(jié)果有刪減)：

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

   下面給出優(yōu)化后的SQL，唯一的變化就是把連接方式改成了「STRAIGHT_JOIN」：

SELECT post.*
FROM post
STRAIGHT_JOIN post_tag ON post.id = post_tag.post_id
WHERE post.status = 1 AND post_tag.tag_id = 123
ORDER BY post.created DESC
LIMIT 100

   試著用EXPLAIN查詢一下SQL執(zhí)行計劃(篇幅所限，結(jié)果有刪減)：

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

   對比優(yōu)化前后兩次EXPLAIN的結(jié)果來看，優(yōu)化后的SQL雖然「rows」更大了，但是沒有了「Using filesort」，綜合來看，性能依然得到了提升。
解釋

   對第一條SQL而言，為什么MySQL優(yōu)化器選擇了一個耗時的執(zhí)行方案？對第二條SQL而言，為什么把連接方式改成STRAIGHT_JOIN之后就提升了性能？

   這一切還得從MySQL對多表連接的處理方式說起，首先要確定以誰為驅(qū)動表，也就是說以哪個表為基準(zhǔn)，在處理此類問題時，MySQL優(yōu)化器采用了簡單粗暴的解決方法：哪個表的結(jié)果集小，就以哪個表為驅(qū)動表，通常這都是最佳選擇。

   說明：在EXPLAIN結(jié)果中，第一行出現(xiàn)的表就是驅(qū)動表。

   繼續(xù)post連接post_tag的例子，MySQL優(yōu)化器有如下兩個選擇，分別是：

1.     以post為驅(qū)動表，通過status_created索引過濾，結(jié)果集119340行
2.     以post_tag為驅(qū)動表，通過tag_id索引過濾，結(jié)果集71220行

       顯而易見，post_tag過濾的結(jié)果集更小，所以MySQL優(yōu)化器選擇它作為驅(qū)動表，可悲催的是我們還需要以post表中的created字段來排序，也就是說排序字段不在驅(qū)動表里，于是乎不可避免的出現(xiàn)了「Using filesort」，從而導(dǎo)致慢查詢。

       知道了來龍去脈，優(yōu)化起來就容易了。頭等大事是務(wù)必保證排序字段在驅(qū)動表中，所以必須以post是驅(qū)動表，于是乎「STRAIGHT_JOIN」就成了答案，它強制了連接順序。

       …

       不過我總覺得「STRAIGHT_JOIN」這種非標(biāo)準(zhǔn)的語法屬于奇技淫巧的范疇，能不用盡量不用，畢竟多數(shù)情況下，MySQL優(yōu)化器都能做出正確的選擇。

最新評論