轉--最佳化暫存資料表使用，SQL語句效能提升100倍

標籤：des   style   blog   http   io   使用   ar   java   strong   

轉自：http://www.51testing.com/html/01/n-867201-2.html

【問題現象】　　線上 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 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：指定系統建立的記憶體暫存資料表最大大小；　　http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size　　max_heap_table_size: 指定使用者建立的記憶體表的最大大小；　　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就意味著使用了暫存資料表。

轉--最佳化暫存資料表使用，SQL語句效能提升100倍