【問題現象】
線上
mysql數據庫爆出一個慢查詢，DBA觀察發現，查詢時服務器IO飆升，IO占用率達到100%， 執行時間長達7s左右。
SELECT DISTINCT g.*, cp.name AS cp_name, c.name AS category_name, t.name AS type_name FROMgm_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 FROMgm_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服務器選擇即可。
直接使用磁盤臨時表的場景
1）表包含TEXT或者BLOB列；
2）GROUP BY 或者 DISTINCT 子句中包含長度大于512字節的列；
3）使用UNION或者UNION ALL時，SELECT子句中包含大于512字節的列；
臨時表相關配置
tmp_table_size：指定系統創建的內存臨時表最大大?。?/div>
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size
max_heap_table_size: 指定用戶創建的內存表的最大大?。?/div>
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size
注意：最終的系統創建的內存臨時表大小是取上述兩個配置值的最小值。
表的設計原則
使用臨時表一般都意味著性能比較低，特別是使用磁盤臨時表，性能更慢，因此我們在實際應用中應該盡量避免臨時表的使用。 常見的避免臨時表的方法有：
1）創建索引：在ORDER BY或者GROUP BY的列上創建索引；
2）分拆很長的列：一般情況下，TEXT、BLOB，大于512字節的字符串，基本上都是為了顯示信息，而不會用于查詢條件， 因此表設計的時候，應該將這些列獨立到另外一張表。
SQL優化
如果表的設計已經確定，修改比較困難，那么也可以通過優化SQL語句來減少臨時表的大小，以提升SQL執行效率。
常見的優化SQL語句方法如下：
1）拆分SQL語句
臨時表主要是用于排序和分組，很多業務都是要求排序后再取出詳細的分頁數據，這種情況下可以將排序和取出詳細數據拆分成不同的SQL，以降低排序或分組時臨時表的大小，提升排序和分組的效率，我們的案例就是采用這種方法。
2）優化業務，去掉排序分組等操作
有時候業務其實并不需要排序或分組，僅僅是為了好看或者閱讀方便而進行了排序，例如數據導出、數據查詢等操作，這種情況下去掉排序和分組對業務也沒有多大影響。
如何判斷使用了臨時表？
使用explain查看執行計劃，Extra列看到Using temporary就意味著使用了臨時表。
主站蜘蛛池模板：
邢台县|
洪雅县|
章丘市|
成安县|
汽车|
延安市|
孟州市|
珲春市|
城步|
平舆县|
柞水县|
永吉县|
青田县|
上犹县|
池州市|
乐安县|
巍山|
伊吾县|
泽州县|
镇安县|
冕宁县|
黄平县|
醴陵市|
桐城市|
察隅县|
湘阴县|
东丰县|
宁陕县|
建瓯市|
宜兰县|
京山县|
祁连县|
五寨县|
庆城县|
延吉市|
项城市|
阆中市|
鹿泉市|
哈尔滨市|
宕昌县|
灵台县|