程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL STRAIGHT_JOIN，mysqlstraight_join

MySQL STRAIGHT_JOIN，mysqlstraight_join

編輯：MySQL綜合教程

MySQL STRAIGHT_JOIN，mysqlstraight_join

問題

最近在調試一條查詢耗時5s多的sql語句，這條sql語句用到了多表關聯（inner join），按時間字段排序（order by），時間字段上已經創建了索引（索引名IDX_published_at）。通過explain分析發現，時間字段上的索引沒用上（Using temporary和Using filesort），問題很明顯，但是原因是什麼呢？

SELECT * FROM news n0_ inner join news_translations n1_ ON n0_.id = n1_.translatable_id inner join channels_news c3_ ON n0_.id = c3_.news_id 
WHERE 
((n0_.unpublished_at IS NOT NULL AND (CURRENT_TIMESTAMP >= n0_.published_at AND CURRENT_TIMESTAMP < n0_.unpublished_at)) OR (CURRENT_TIMESTAMP >= n0_.published_at AND n0_.unpublished_at IS NULL))
AND (n0_.status = 1 AND n0_.content_type_id = 1) AND n0_.id NOT IN (510466, 510433, 24, 11, 10, 9, 4) 
AND n0_.home_position_id IS NULL 
AND 
n1_.locale = 'zh_CN' 
AND 
c3_.channel_id = 1 
ORDER BY n0_.published_at DESC 
LIMIT 5 ;

優化前sql語句

+-------+--------+-------------------------------+--------+-----------------------------------------------------------+
| table | type   | key                           | rows   | Extra                                                     |
+-------+--------+-------------------------------+--------+-----------------------------------------------------------+
| c3_   | ref    | IDX_87B9249E72F5A1AA          | 161590 | Using where; Using index; Using temporary; Using filesort |
| n0_   | eq_ref | PRIMARY                       |      1 | Using where                                               |
| n1_   | ref    | UNIQ_20FDB3302C2AC5D34180C698 |      1 | Using where                                               |
+-------+--------+-------------------------------+--------+-----------------------------------------------------------+

explain分析結果有所刪減

經過一輪折騰的優化，得到了下面的sql語句

SELECT * FROM news n0_ STRAIGHT_JOIN news_translations n1_ ON n0_.id = n1_.translatable_id STRAIGHT_JOIN channels_news c3_ ON n0_.id = c3_.news_id 
WHERE 
((n0_.unpublished_at IS NOT NULL AND (CURRENT_TIMESTAMP >= n0_.published_at AND CURRENT_TIMESTAMP < n0_.unpublished_at)) OR (CURRENT_TIMESTAMP >= n0_.published_at AND n0_.unpublished_at IS NULL))
AND (n0_.status = 1 AND n0_.content_type_id = 1) AND n0_.id NOT IN (510466, 510433, 24, 11, 10, 9, 4) 
AND n0_.home_position_id IS NULL 
AND 
n1_.locale = 'zh_CN' 
AND 
c3_.channel_id = 1 
ORDER BY n0_.published_at DESC 
LIMIT 5 ;

優化後sql語句

+-------+--------+-------------------------------+--------+--------------------------+
| table | type   | key                           | rows   | Extra                    |
+-------+--------+-------------------------------+--------+--------------------------+
| n0_   | range  | IDX_published_at              | 255440 | Using where              |
| n1_   | ref    | UNIQ_20FDB3302C2AC5D34180C698 |      1 | Using where              |
| c3_   | eq_ref | PRIMARY                       |      1 | Using where; Using index |
+-------+--------+-------------------------------+--------+--------------------------+

優化後explain分析結果有所刪減

優化前後的變化有四點：1、不再Using temporary和Using filesort；2、表的查詢順尋變了；3、查詢掃描的rows增加了；4、查詢時間由5s降到了0.02s。

原因分析

優化前後出現的四點變化，性能顯著提升，需要從mysql的關聯的連接處理說起。

以下參考《高性能MySQL》

1）優化前的sql語句以channels_news為第一個關聯表，找到161590條記錄；2）優化後的sql語句以news表為第一關聯表，找到255440條記錄，比第一條sql語句查找多了9W多條。因此，優化前的sql語句的關聯順序是MySQL優化器的選擇，可以讓查詢進行更小的嵌套循環和回溯操作。MySQL通過選擇合適的關聯順序來讓查詢執行的成本盡可能低，重新定義關聯的順序是優化器很重要的一部分功能。不過有時候，優化器給出的並不是最優的關聯順序。這時可以使用STRAIGHT_JOIN關鍵字重寫查詢，讓優化器按照你認為的最優關聯順序執行。

從優化後的explain分析結果看出，news是驅動表，結果以news表的published_at字段進行排序，所以用上了索引，避免了Using temporary和Using filesort，自然而然的，查詢時間也降下來了。正如前面說的，mysql的優化器通過粗暴的小表驅動大表來選擇連接的順序，第一條sql語句掃描了161590行，第二條sql語句掃描了255440行，優化後的sql語句掃描的行數增加了。

結語

結案陳詞：造成這次sql語句查詢耗時5s的原因是，sql語句order by的字段不在mysql的優化器選在驅動表上，所以導致這次關聯查詢排序字段上的索引沒有被使用。因此，通過使用STRAIGHT_JOIN來強制制定關聯查詢的表順序，以達到優化的目的。但是，有時候我們人為地指定順序不一定比mysql的優化引擎准確，所以在使用STRAIGHT_JOIN的時候三思而後行。

本文鏈接：http://www.hcoding.com/?p=211

原創文章，轉載請注明：JC&hcoding.com

書憤

陸游

早歲那知世事艱，中原北望氣如山。

樓船夜雪瓜洲渡，鐵馬秋風大散關。

塞上長城空自許，鏡中衰鬓已先斑。

出師一表真名世，千載誰堪伯仲間。