優化臨時表使用，SQL語句性能提升100倍

作者：運華 2013-09-26 14:11:23

線上mysql數據庫爆出一個慢查詢，DBA觀察發現，查詢時服務器IO飆升，IO占用率達到100%，執行時間長達7s左右。

【問題現象】

線上mysql數據庫爆出一個慢查詢，DBA觀察發現，查詢時服務器IO飆升，IO占用率達到100%，執行時間長達7s左右。

SQL語句如下：

SELECT DISTINCT g.*, cp.name AS cp_name, c.name AS category_name, t.name AS type_name 
FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0
 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0
 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;

【問題分析】

使用explain查看執行計劃，結果如下：

這條sql語句的問題其實還是比較明顯的：

查詢了大量數據(包括數據條數、以及g.* )，然后使用臨時表order by，但最終又只返回了20條數據。

DBA觀察到的IO高，是因為sql語句生成了一個巨大的臨時表，內存放不下，于是全部拷貝到磁盤，導致IO飆升。

【優化方案】

優化的總體思路是拆分sql，將排序操作和查詢所有信息的操作分開。

第一條語句：查詢符合條件的數據，只需要查詢g.id即可

SELECT DISTINCT g.id FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;

第二條語句：查詢符合條件的詳細數據，將第一條sql的結果使用in操作拼接到第二條的sql

SELECT DISTINCT g.*, cp.name AS cp_name,c.name AS category_name,t.name AS type_name FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 and g.id in(…………………) ORDER BY g.modify_time DESC ;

【實測效果】

在SATA機器上測試，優化前大約需要50s，優化后第一條0.3s，第二條0.1s，優化后執行速度是原來的100倍以上，IO從100%降到不到1%

在SSD機器上測試，優化前大約需要7s，優化后第一條0.3s，第二條0.1s，優化后執行速度是原來的10倍以上，IO從100%降到不到1%

可以看出，優化前磁盤io是性能瓶頸，SSD的速度要比SATA明顯要快，優化后磁盤不再是瓶頸，SSD和SATA性能沒有差別。

【理論分析】

MySQL在執行SQL查詢時可能會用到臨時表，一般情況下，用到臨時表就意味著性能較低。

臨時表存儲

MySQL臨時表分為“內存臨時表”和“磁盤臨時表”，其中內存臨時表使用MySQL的MEMORY存儲引擎，磁盤臨時表使用MySQL的MyISAM存儲引擎；
一般情況下，MySQL會先創建內存臨時表，但內存臨時表超過配置指定的值后，MySQL會將內存臨時表導出到磁盤臨時表；
Linux平臺上缺省是/tmp目錄，/tmp目錄小的系統要注意啦。

使用臨時表的場景

1）ORDER BY子句和GROUP BY子句不同，例如：ORDERY BY price GROUP BY name；

2）在JOIN查詢中，ORDER BY或者GROUP BY使用了不是第一個表的列例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name

3）ORDER BY中使用了DISTINCT關鍵字 ORDERY BY DISTINCT(price)

4）SELECT語句中指定了SQL_SMALL_RESULT關鍵字 SQL_SMALL_RESULT的意思就是告訴MySQL，結果會很小，請直接使用內存臨時表，不需要使用索引排序 SQL_SMALL_RESULT必須和GROUP BY、DISTINCT或DISTINCTROW一起使用一般情況下，我們沒有必要使用這個選項，讓MySQL服務器選擇即可。