MySQL慢查詢最佳化之慢查詢日誌分析的執行個體教程_Mysql

資料庫響應慢問題最多的就是查詢了。現在大部分資料庫都提供了效能分析的協助手段。例如Oracle中會幫你直接找出慢的語句，並且提供最佳化方案。在MySQL中就要自己開啟慢日誌記錄加以分析（記錄可以儲存在表或者檔案中，預設是儲存在檔案中，我們系統使用的就是預設）。

先看看MySQL慢查詢日誌裡面的記錄長什麼樣的：

Time         Id Command  Argument# Time: 141010 9:33:57# User@Host: root[root] @ localhost [] Id:   1# Query_time: 0.000342 Lock_time: 0.000142 Rows_sent: 3 Rows_examined: 3use test;SET timestamp=1412904837;select * from t;

這個日誌應該很好理解了，第一個#記錄時間戳記，第二個#記錄執行命令的使用者和地址資訊，第三個#記錄執行查詢的時間、鎖的時間、返回行數、被掃描的行數。接著後面記錄真正執行的SQL語句。還可以通過以下命令看看cvs儲存格式每個欄位意義。

SHOW CREATE TABLE mysql.slow_log;

接下來說說如何擷取和分析慢日誌吧。

查看MySQL慢日誌參數

進入啟動好的MySQL，執行以下命令

mysql> show variables like '%slow_query%';

+---------------------------+----------------------------------------+| Variable_name       | Value                 |+---------------------------+----------------------------------------+| slow_query_log      | OFF                  || slow_query_log_file    | /usr/local/mysql/data/cloudlu-slow.log |+---------------------------+----------------------------------------+

這裡告訴我們慢日誌的日誌存放位置，慢日誌是否有開啟。
那麼什麼樣的查詢需要被日誌呢？在MySQL中， 沒有index的查詢 以及 超過指定時間同時超過指定掃描行數的查詢 需要記錄在慢日誌查詢裡面。

那麼它們的參數又是怎麼查看的呢？

沒有index的查詢記錄開關

mysql> show global variables like '%indexes%';

+----------------------------------------+-------+| Variable_name             | Value |+----------------------------------------+-------+| log_queries_not_using_indexes     | OFF  || log_throttle_queries_not_using_indexes | 0   |+----------------------------------------+-------+

第一個參數 表示是否開啟記錄沒有index的查詢，第二個

參數用來做日誌記錄的流量控制，一分鐘可以記錄多少條，預設0是表示不限制。

超過指定時間長度的查詢開關

mysql> show global variables like '%long_query%';

+-----------------+-----------+| Variable_name  | Value   |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)

就一個參數指定超過多少時間長度的查詢需要被記錄

超過指定行數的掃描查詢開關

mysql> show variables like '%min_examined_row_limit%';

+------------------------+-------+| Variable_name     | Value |+------------------------+-------+| min_examined_row_limit | 0   |+------------------------+-------+1 row in set (0.00 sec)

預設是0，代表不現在掃描行數

設定開啟MySQL慢日誌參數

進入MySQL，輸入以下命令或者在MySQL的啟動設定檔裡面修改或者給MySQL添加啟動參數，進入MySQL後的修改如下：

set global long_query_time=0.1;set global log_queries_not_using_indexes=on;set global slow_query_log = on;

這裡要斟酌的有2點，第一是超過什麼時間長度的日誌是有問題的，這個由系統需求來決定。第二是沒有使用indexes的日誌每分鐘要記錄多少條，要防止日誌太多對效能產生影響。

在實際的日誌分析中，通常慢日誌的log數量不少，同時相同的查詢被記錄的條數也會很多，這裡就需要如何從慢日誌查詢中找到最有問題，最需要最佳化的日誌。在這方面，有很多分析工具，最基本的分析工具就是MySQL內建的mysqldumpslow，mysqldumpslow(Perl指令碼)的輸出樣本：

[root@cloudlu bin]# ./mysqldumpslow -s t -t 1 /usr/local/mysql/data/cloudlu-slow.log 

 

Reading mysql slow query log from /usr/local/mysql/data/cloudlu-slow.log Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=3.0 (3), root[root]@localhost  select * from t 

一看就非常清楚，它的輸出主要 統計不同慢sql的出現次數(Count 1)，執行最長時間(Time 0.00s)，累計總耗費時間(Time 0s)，等待鎖的時間(Lock 0.00s)，等待鎖的總時間(Lock 0s)，發送給用戶端的行總數(Rows 3.0)，掃描的行總數(Rows 3)，使用者(root)以及sql語句本身。它最常用的參數包括：

• -s 排序選項：c 查詢次數 r 返回記錄行數 t 查詢時間
• -t n：顯示top n條查詢

對於一般的分析已經差不多了，不過對於百分比等等資料mysqldumpslow就不夠完善了。所以世界上多了很多各種MySQL慢日誌分析工具，比較優秀的有mysqlsla(Perl指令碼)和pt-query-digest(Perl指令碼)，可以提供Count, sql的執行次數及佔總的slow log數量的百分比，Time, 執行時間, 包括總時間, 平均時間, 最小, 最大時間, 時間佔到總慢sql時間的百分比，95% of Time, 去除最快和最慢的sql, 覆蓋率佔95%的sql的執行時間，Lock Time, 等待鎖的時間，95% of Lock , 95%的慢sql等待鎖時間，Rows sent, 結果行統計數量, 包括平均, 最小, 最大數量，Rows examined, 掃描的行數量，還可以產生表報，儲存分析結果。這裡就不一一介紹了。

通過這些慢日誌分析軟體定位到了慢查詢語句就已經完成了SQL最佳化的一大半。接下來通過在MySQL中執行explain或者desc命令查看慢查詢語句，可以看出為什麼SQL查詢慢。

mysql> explain select * from test.t \G 

*************************** 1. row ***************************       id: 1  select_type: SIMPLE     table: t      type: ALL possible_keys: NULL      key: NULL    key_len: NULL      ref: NULL      rows: 2     Extra: NULL 1 row in set (0.00 sec) 

它的輸出格式細節可以關注MySQL explain format，在輸出中最要注意的是：
1. type：ALL是效率最差，最要注意的

2. key：是否有使用Key，key長度如何

3. Extra：最好不要出現filesort以及temporary，最主要是要關注在orderby和groupby。

Note: SQL最佳化是個很複雜的過程，有可能出現拆東牆補西牆的情況：比如給資料庫表加入了索引之後，確實查詢快了，可是儲存空間加多了，插入刪除操作耗時也增加了，如果在一個寫多讀少的系統中，執行這種最佳化可能會起到反效果。所以最佳化完之後千萬不能大意，要持續監控系統，防止出現引入新瓶頸的情況。