MySQL5.6如何優化慢查詢的SQL語句--慢日志介紹

MySQL5.6如何優化慢查詢的SQL語句--慢日志介紹

最近有個開發團隊抱怨我們平台包含的mysql cluster不行，總是報mysql的”heartbeat Error“，分析了他們收集的日志，沒有發現mysql cluster節點之間的網絡有問題，倒是發現了一個很有意思的現象，在mysql的slow log裡面發現了大量的慢查詢，基本上都是要1秒鐘以上，很明顯這種耗時高的慢查詢會影響系統行為，指出這點讓開發團隊去優化他們的應用，很好奇他們竟然不知道有這些東西可以分析。。。。。。

數據庫響應慢問題最多的就是查詢了。現在大部分數據庫都提供了性能分析的幫助手段。例如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: 3
use 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的日志每分鐘要記錄多少條，要防止日志太多對性能產生影響。