程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 在MySQL中使用STRAIGHT_JOIN的教程

在MySQL中使用STRAIGHT_JOIN的教程

編輯:關於MYSQL數據庫

問題

   通過「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是多對多的關系,所以存在一個關聯表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 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執行計劃(篇幅所限,結果有刪減):

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

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

   對第一條SQL而言,為什麼MySQL優化器選擇了一個耗時的執行方案?對第二條SQL而言,為什麼把連接方式改成STRAIGHT_JOIN之後就提升了性能?

   這一切還得從MySQL對多表連接的處理方式說起,首先要確定以誰為驅動表,也就是說以哪個表為基准,在處理此類問題時,MySQL優化器采用了簡單粗暴的解決方法:哪個表的結果集小,就以哪個表為驅動表,通常這都是最佳選擇。

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

   繼續post連接post_tag的例子,MySQL優化器有如下兩個選擇,分別是:

  1.     以post為驅動表,通過status_created索引過濾,結果集119340行
  2.     以post_tag為驅動表,通過tag_id索引過濾,結果集71220行

       顯而易見,post_tag過濾的結果集更小,所以MySQL優化器選擇它作為驅動表,可悲催的是我們還需要以post表中的created字段來排序,也就是說排序字段不在驅動表裡,於是乎不可避免的出現了「Using filesort」,從而導致慢查詢。

       知道了來龍去脈,優化起來就容易了。頭等大事是務必保證排序字段在驅動表中,所以必須以post是驅動表,於是乎「STRAIGHT_JOIN」就成了答案,它強制了連接順序。

       …

       不過我總覺得「STRAIGHT_JOIN」這種非標准的語法屬於奇技淫巧的范疇,能不用盡量不用,畢竟多數情況下,MySQL優化器都能做出正確的選擇。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved