[MySQL最佳化] -- 如何定位效率較低的SQL

標籤：blog   http   ar   os   使用   sp   strong   檔案   資料   

一般通過以下兩種方式定位執行效率較低的 SQL 陳述式。 

• 通過慢查詢日誌定位那些執行效率較低的 SQL 陳述式，用 --log-slow-queries[=file_name] 選項啟動時， mysqld 會 寫一個包含所有執行時間超過 long_query_time 秒的 SQL 陳述式的記錄檔，通過查看這個記錄檔定位效率較低的 SQL 。 
• 慢查詢日誌在查詢結束以後才紀錄，所以在應用反映執行效率出現問題的時候查詢慢查詢日誌並不能定位問題，可以使用 show processlist 命令查看當前 MySQL 在進行的線程，包括線程的狀態、是否鎖表等，可以即時地查看 SQL 的 執行情況，同時對一些鎖表操作進行最佳化。

下面我們舉例說明一下，如何通過慢查詢日誌定位執行效率底的 SQL 陳述式： 
開啟慢查詢日誌 , 配置範例： 
[mysqld] 
log-slow-queries 
在 my.cnf 設定檔中增加上述配置項並重啟 mysql 服務，這時 mysql 慢查詢功能生效。慢查詢 日誌將寫入參數 DATADIR （資料目錄）指定的路徑下，預設檔案名稱是 host_name-slow.log 。 
和錯誤記錄檔、查詢日誌一樣，慢查詢日誌記錄的格式也是純文字，可以被直接讀取。下例中示範了慢查詢日誌的設定和讀取過程。 
（ 1 ）首先查詢一下 long_query_time 的值 。 
mysql> show variables like ‘long%‘; 
+-----------------+-------+ 
| Variable_name | Value | 
+-----------------+-------+ 
| long_query_time | 10 | 
+-----------------+-------+ 
1 row in set (0.00 sec) 
（ 2 ）為了方便測試，將修改慢查詢時間為 5 秒。 
mysql> set long_query_time=5; 
Query OK, 0 rows affected (0.02 sec) 
（ 3 ）依次執行下面兩個查詢語句。 
第一個查詢因為查詢時間低於 5 秒而不會出現在慢查詢日誌中： 
mysql> select count(*) from order2008; 
+----------+ 
| count(*) | 
+----------+ 
| 208 | 
+----------+ 
1 row in set (0.00 sec) 
第二個查詢因為查詢時間大於 5 秒而應該出現在慢查詢日誌中： 
mysql> select count(*) from t_user; 
+----------+ 
| count(*) | 
+----------+ 
| 6552961 | 
+----------+ 
1 row in set (11.07 sec) 
（ 4 ）查看慢查詢日誌。 
[[email protected] mysql]# more localhost-slow.log 
# Time: 081026 19:46:34 
# [email protected]: root[root] @ localhost [] 
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961 
select count(*) from t_user; 
從上面日誌中，可以發現查詢時間超過 5 秒的 SQL ，而小於 5 秒的則沒有出現在此日誌中。 
如果慢查詢日誌中記錄內容很多，可以使用 mysqldumpslow 工具（ MySQL 用戶端安裝內建）來對慢查詢日誌進行分類匯總。下例中對記錄檔 mysql_master-slow.log 進行了分類匯總，只顯示匯總後摘要結果： 
[[email protected]_master mysql_data]# mysqldumpslow mysql_master-slow.log 
Reading mysql slow query log from mysql_master-slow.log 
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql_master 
select count(N) from t_user; 
對於 SQL 文本完全一致，只是變數不同的語句， mysqldumpslow 將會自動視為同一個語句進行統計，變數值用 N 來代替。這個統計結果將大大增加使用者閱讀慢查詢日誌的效率，並迅速定位系統的 SQL 瓶頸。 
注意：慢查詢日誌對於我們發現應用中有效能問題的 SQL 很有協助，建議正常情況下，開啟此日誌並經常查看分析。 



本文來自ChinaUnix部落格，如果查看原文請點：http://blog.chinaunix.net/u3/93470/showart_2001472.html

[MySQL最佳化] -- 如何定位效率較低的SQL