如何開啟慢查詢日志

1、查看慢查詢日志是否開啟

執行命令：show variables like 'slow%'。

得到以下結果：

可以看到slow_query_log屬性是OFF，處于關閉狀態，那么我們需要先開啟慢查詢。

slow_query_log_file表示慢查詢日志文件的存放路徑，我們可以自定義文件路徑：

set global slow_query_log_file = '路徑'。

2、開啟慢查詢日志

執行命令：set global slow_query_log = on。

然后再查詢一下，發現slow_query_log處于開啟狀態：

開啟了之后，是不是所有的查詢都會記錄在文件里呢?

當然不是，慢查詢日志，顧名思義是只記錄查詢比較慢的語句，那問題又來了，怎么才算查詢比較慢的語句呢?

實際上，這里會有一個標準值，而且這個標準值是可以由我們自己設定的。

3、慢查詢的臨界值設定

首先查看一下默認的臨界值。

執行命令：show variables like '%long%'。

其中有一個long_query_time屬性，它的值為10.000000。它表示的意思是，只記錄查詢時間在10s以上的語句。

顯然10s我們是不可接受的，所以我們需要自己設定一下這個值。因為我自己的測試表中只有10w條數據，查詢很快，所以這里我們設置的小一點。如果有條件的話，最好設置一個百萬級的表進行測試。

我們把慢查詢的臨界值設置為0.02：set long_query_time=0.02。

可以看到現在臨界值是0.02秒了。

現在來模擬查詢時間小于0.01和大于0.01的兩個查詢，看是否都記錄在了慢查詢日志中。

然后看一下日志文件中的數據：

可以看到只有第二條查詢的日志。

需要注意的是，我們上面的操作是在交互界面進行的，如果數據庫進行重啟，這些設置都會失效。如果要永久生效，需要修改配置文件：

vi /etc/my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 0.1
slow_query_log_file =/usr/local/mysql/mysql_slow.log

在配置文件中加上這三行就可以了。主要要重啟mysql才能生效!

慢查詢語句解析

我們通過慢查詢日志，可以定位到是哪一條語句查詢比較慢，找到這條語句之后，如何去分析它慢的原因呢?最簡單的方法，可以通過explain解析。

執行命令：explain (sql語句)。

我們把上面執行的兩條語句放一起對比解析一下：

需要重點關注possible_keys、key、rows這幾個屬性值。

possible_keys表示該語句可能會用到的索引。

key表示該語句實際用到的索引。

rows表示該語句掃描的行數。

通過這些屬性，我們可以大致的分析一下，第一條語句沒有走索引，它掃描了9萬多行數據，所以查詢速度比較慢，而第二條語句走了主鍵索引，僅僅掃描了一條語句，所以它的執行速度比較快。這樣我們就可以快速定位到問題，然后針對性的去解決。

開啟性能詳情

如果通過上面的語句解析沒有定位到問題，我該加的索引也加了，但是還是比較慢，那就可以通過性能詳情來進一步的探究一下原因。

性能詳情可以追蹤查詢語句的整個生命周期的狀態，有了這些狀態值，就可以從更深層次找出具體是哪個環節慢了，從而能針對性的進行改善。

1、查看性能詳情是否開啟

執行命令：show variables like '%profiling%'。

可以看到profiling屬性值為OFF，表示關閉，那么我們先開啟它。

執行命令：set profiling = on 。

這樣就開啟了。開啟之后，我們就可以執行查詢語句，mysql會自動的保存性能記錄。

2、查看性能記錄

我們執行一條sql語句：

然后查看性能記錄：

執行命令：show profiles。

可以看到開啟后的所有查詢語句的記錄。我們想查看一下第二條執行語句的整個執行周期的狀態詳情：

執行命令：show profile for query 2。

可以看到整個執行過程每個狀態的耗時情況。然后定位到具體是哪個狀態最耗時，然后針對性的排查原因。

官方也給出了每個狀態的解釋，具體可查看：

https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html。