---

theme: cyanosis

歡迎關注MySQL專欄 MySQL歷險記
強烈建議收藏本導航文【MySQL歷險記】MySQL的核心特性彙總

前言

在項目裏面，多多少少都隱藏着一些執行比較慢的SQL, 不同的開發測試人員在平時使用的過程中多多少少都能夠遇到，但是無法立馬有時間去排查解決。那麼如果有一個文件能夠將這些使用過程中比較慢的SQL記錄下來，定期去分析排查，那該多美好啊。這種情況MySQL也替我們想到了，它提供了SQL慢查詢的日誌，本文就分享下如何使用吧。

什麼是慢查詢日誌？

MySQL的慢詢日誌，提供了記錄在MySQL中響應時間超過指定閾值語句的功能，比如設定閾值為3秒，那麼任何SQL執行超過3秒都會被記錄下來。

我們藉助慢查詢日誌功能可以發現哪些那些執行時間特別長的詢，並且有針對性地進行優化，從而提高系統的整體效率。

怎麼開啟慢查詢日誌？

默認情況下，MySQL數據庫沒有開啟慢查詢日誌，因為多多少少會帶來一定性能的影響。我們可以在開發測試環境、或者生產環境做調優的時候開啟，那怎麼查看是否開啟了呢？

1. 查看慢SQL是否開啟

執行下面命令查看是否開啟慢SQL

show variables like '%slow_query_log';

• OFF: 未開啟

• ON: 開啟

• 如何開啟慢查詢

執行下面的命令開啟慢查詢日誌

set global slow_query_log='ON';

1. 修改慢查詢閾值

前面介紹了SQL執行到達了制定的時間閾值後記錄到慢查詢日誌中，那麼如何設置呢？

``` set global long_query_time = N;

set long_query_time = N ```

• 設置global的方式對當前session的long_query_time失效。對新連接的客户端有效。所以可以一併執行下述語句

• N表示設置的閾值，單位為秒

• 這裏的show global variables like '%long_query_time%';可以查看閾值大小

• 如何設置永久生效

前面是通過命令行的方式設置，如果MySQL重啟，那麼配置就會重置。我們可以通過修改MySQL的配置my.cfg或者my.ini永久生效。

[mysqld] slow_query_log=ON # 開啟慢查詢日誌開關 slow_query_log_file=/var/lib/mysql/alvin-slow.log # 慢查詢日誌的目錄和文件名信息 long_query_time=3 # 設置慢查詢的閾值為3秒，超出此設定值的SQL即被記錄到慢查詢日誌 log_output=FILE

慢查詢日誌在哪裏呢？

前面講解了如何開啟MySQL的慢查詢日誌，那麼它把日誌記錄在哪裏了呢?

1. 查看慢查詢日誌位置

通過show variables like '%slow_query_log_file%';命令可以查看慢SQL文件位置，如下圖所示：

1. 修改慢查詢日誌位置

也很簡單，執行下面的命令即可：

set global slow_query_log_file = '/usr/local/mysql/data/alvin-slow-slow.log';

怎麼查看慢SQL內容？

現在我們已經知道慢查詢日誌在哪裏了，那麼如何查看裏面的內容呢？我們這裏用一個例子演示下吧。

1. 執行一個查詢的SQL

• 執行花了1秒多，超過了前面設置的閾值1s

• 查看慢查詢數目

執行下面命令查詢當前系統中有多少條慢查詢記錄

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

• value=1, 表明剛剛的日誌被記錄了。

• 查看日誌內容

通過cat命令查看文件內容，可以看到對應的慢SQL。

慢查詢日誌分析工具mysqldumpslow

果要手工分析日誌，查找、分析SQL，顯然是個體力活，MySQL提供了日誌分析工具 mysqldumpslow 。

mysqldumpslow 命令的具體參數如下：

• -a: 不將數字抽象成N，字符串抽象成S
• -s: 是表示按照何種方式排序：

• • c: 訪問次數
  • l: 鎖定時間
  • r: 返回記錄
  • t: 查詢時間
  • al:平均鎖定時間
  • ar:平均返回記錄數
  • at:平均查詢時間 （默認方式）
  • ac:平均查詢次數

• -t: 即為返回前面多少條的數據；
• -g: 後邊搭配一個正則匹配模式，大小寫不敏感的；

可mysqldumpslow位置mysql的bin目錄下，以通過執行 mysqldumpslow --help命令查看使用。

舉例： 我們想要按照查詢時間排序，查看前五條 SQL 語句，這樣寫即可：

mysqldumpslow -s t -t 5 /usr/local/mysql/data/alvin-slow-slow.log

常見的用法：

```

得到返回記錄集最多的10個SQL

mysqldumpslow -s r -t 10 /usr/local/mysql/data/alvin-slow-slow.log

得到訪問次數最多的10個SQL

mysqldumpslow -s c -t 10 /usr/local/mysql/data/alvin-slow-slow.log

得到按照時間排序的前10條裏面含有左連接的查詢語句

mysqldumpslow -s t -t 10 -g "left join" /usr/local/mysql/data/alvin-slow-slow.log

另外建議在使用這些命令時結合 | 和more 使用 ，否則有可能出現爆屏情況

mysqldumpslow -s r -t 10 /usr/local/mysql/data/alvin-slow-slow.log | more ```

怎麼刪除慢SQL日誌？

現在慢查詢日誌很多了啊，為了不互相混淆，我要刪除一些慢SQL日誌，怎麼刪除呢？

1. 手動刪除慢查詢日誌文件即可, 也就是rm命令。
2. 使用命令mysqladmin flush-logs重置慢sql日誌內容，完整命令如下 ：

mysqladmin -uroot -p flush-logs slow

總結

本文講解了慢SQL日誌該如何打開以及使用，是非常重要有效的排查手段，最後記得在排查完畢以後，要關閉慢SQL日誌，不然可能影響性能哦。

如果本文對你有幫助的話，請留下一個贊吧

本文正在參加「金石計劃 . 瓜分6萬現金大獎」