技術文章 » 快速學會慢查詢SQL排查

快速學會慢查詢SQL排查

2022-12-09 06:03:30

轉載請註明出處❤️

作者：測試蔡坨坨

原文連結：caituotuo.top/c56bd0c5.html

你好，我是測試蔡坨坨。

在往期文章中，我們聊過資料庫基礎知識，可參考「資料庫基礎，看完這篇就夠了！」。

學完資料庫基礎知識，要想更深入地瞭解資料庫，就需要學習資料庫進階知識，今天我們就先來聊一聊慢SQL查詢那些事兒。

在日常工作中，我們經常會遇到資料庫慢查詢問題，那麼我們要如何進行排查呢？

假設一次執行20條SQL，我們如何判斷哪條SQL是執行慢的爛SQL，這裡就需要用到慢查詢紀錄檔。

在SQL中，廣義的查詢就是CRUD操作，而狹義的查詢僅僅是SELECT查詢操作，而我們所說的慢查詢其實指的是廣義的查詢，包括增刪改查，一般是查詢，所以稱為慢查詢。

什麼是慢查詢紀錄檔

MySQL提供的一種紀錄檔記錄，用於記錄MySQL中響應時間超過閾值[yù zhí]的SQL語句（也就是long_query_time的值，預設時間是10秒）。

慢查詢紀錄檔預設是關閉的，開啟會消耗一定的效能，一般是開發調優時開啟，而部署時會關閉。

檢查是否開啟了慢查詢紀錄檔

執行語句：

show variables like '%slow_query_log';

從以下執行結果可以看出，慢查詢紀錄檔預設是OFF關閉狀態：

如何開啟慢查詢紀錄檔

開啟慢查詢紀錄檔有兩種方式，分別是臨時開啟和永久開啟。

臨時開啟

一般使用臨時開啟，即在記憶體中開啟，MySQL退出就會自動關閉，從而避免過多的效能開銷：

set global slow_query_log = 1;  // 1表示開啟
set global slow_query_log_file="D:/MySQL Server 5.5/slow_query_log.log"; // 紀錄檔儲存位置

臨時開啟，重啟MySQL服務後慢查詢紀錄檔會變成OFF狀態：

Linux重啟MySQL服務：

service mysql restart

Windows重啟MySQL服務：

右擊開始選單——計算機管理——找到MySQL服務——重啟：

重啟後的可以看到慢查詢紀錄檔變成OFF關閉狀態：

永久開啟

通過修改組態檔的方式可以永久開啟慢查詢紀錄檔。

Linux：在 /etc/my.cnf 中追加設定

vi /etc/my.cnf

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow-query-log.log

Windows：D:\MySQL Server 5.5\my.ini

[mysqld]
slow_query_log=1
slow_query_log_file="D:/MySQL Server 5.5/slow_query_log.log"

永久開啟後，即使重啟MySQL服務，慢查詢紀錄檔也不會關閉：

慢查詢閾值修改

檢視預設值
show variables like '%long_query_time%';

可以看到預設值是10s：

臨時設定閾值

設定完畢後，需要重新登入MySQL才能生效（注意是重新登入MySQL，不是重啟MySQL服務）。

set global long_query_time = 5;

永久設定閾值

通過修改組態檔的方式可以永久設定閾值（修改完成後需要重啟MySQL服務）：

[mysqld]
long_query_time=3

慢SQL排查測試

模擬慢SQL資料

執行如下SQL語句休眠4秒，模擬慢SQL：

select sleep(4);

查詢超過閾值的SQL的數量：

show global status like '%slow_queries%';

可以看到超過閾值的SQL數為1：

查詢超過閾值的具體SQL語句

主要有兩種方式可以定位到具體的慢SQL語句，分別為檢視紀錄檔檔案和使用mysqldumpslow工具檢視。

方式一：通過檢視紀錄檔檔案，也就是前面設定的slow_query_log_file

方式二：通過mysqldumpslow工具快速定位慢SQL

假設執行了1000條SQL，其中有30條SQL都超過了閾值，如果直接檢視紀錄檔檔案，無法快速定位到具體的SQL，所以需要使用mysqldumpslow工具，通過一些過濾條件，快速查詢出慢SQL。

Linux：

mysqldumpslow命令

--help命令檢視幫助檔案：

mysqldumpslow --help

-s ORDER排序   what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count（存取次數）
                 l: lock time（鎖的時間）
                 r: rows sent（返回的記錄數）
                 t: query time（查詢時間）
-r：逆序 reverse the sort order (largest last instead of first)
-l：鎖定時間 don't subtract lock time from total time
-g：後面跟一個正則匹配模式，大小寫不敏感
-t：top n，即為返回前面多少條的資料

舉慄：

  • 獲取返回記錄最多的3個SQL

    mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow-query-log.log

  • 獲取存取次數最多的3個SQL

    mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow-query-log.log

  • 按照時間排序，前10條包含left join查詢語句的SQL

    mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow-query-log.log

Windows：

MySQL安裝後以後在bin下有mysqldumpslow.pl檔案，如果沒有可以自行下載。

D:\MySQL Server 5.5\bin\mysqldumpslow.pl：

mysqldumpslow是一個perl指令碼，要想在Windows執行，首先需要安裝Perl。

安裝過程比較簡單，從官網 http://strawberryperl.com/ 下載windows安裝包，安裝完成後通過perl -v命令測試是否安裝成功，如果能顯示版本號，表示安裝成功。

百度網路硬碟：

連結：https://pan.baidu.com/s/1MiJ3FNUGEoSE1U6dJzOsAg
提取碼：slt7

檢視幫助檔案：

perl mysqldumpslow.pl --help

舉慄：

  • 獲取返回記錄最多的3個SQL

    perl mysqldumpslow.pl -s r -t 3 "D:/MySQL Server 5.5/slow_query_log.log"

  • 獲取存取次數最多的3個SQL

    perl mysqldumpslow.pl -s c -t 3 "D:/MySQL Server 5.5/slow_query_log.log"

  • 按照時間排序，前10條包含left join查詢語句的SQL

    perl mysqldumpslow.pl -s t -t 10 -g "left join" "D:/MySQL Server 5.5/slow_query_log.log"