你的哪些SQL慢?看看MySQL慢查詢日誌吧

語言: CN / TW / HK

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的方式對當前sessionlong_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萬現金大獎」