快速學會慢查詢SQL排查

測試蔡坨坨 發表於 2022-12-08
SQL

轉載請註明出處❤️

作者:測試蔡坨坨

原文連結: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關閉狀態:

快速學會慢查詢SQL排查

如何開啟慢查詢日誌

開啟慢查詢日誌有兩種方式,分別是臨時開啟永久開啟

臨時開啟

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

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

快速學會慢查詢SQL排查

臨時開啟,重啟MySQL服務後慢查詢日誌會變成OFF狀態:

Linux重啟MySQL服務:

service mysql restart

Windows重啟MySQL服務:

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

快速學會慢查詢SQL排查 快速學會慢查詢SQL排查

重啟後的可以看到慢查詢日誌變成OFF關閉狀態:

快速學會慢查詢SQL排查

永久開啟

透過修改配置檔案的方式可以永久開啟慢查詢日誌。

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服務,慢查詢日誌也不會關閉:

快速學會慢查詢SQL排查

慢查詢閾值修改

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

可以看到預設值是10s:

快速學會慢查詢SQL排查

臨時設定閾值

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

set global long_query_time = 5;

快速學會慢查詢SQL排查

永久設定閾值

透過修改配置檔案的方式可以永久設定閾值(修改完成後需要重啟MySQL服務):

[mysqld]
long_query_time=3

快速學會慢查詢SQL排查

慢SQL排查測試

模擬慢SQL資料

執行如下SQL語句休眠4秒,模擬慢SQL:

select sleep(4);

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

show global status like '%slow_queries%';

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

快速學會慢查詢SQL排查

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

主要有兩種方式可以定位到具體的慢SQL語句,分別為檢視日誌檔案使用mysqldumpslow工具檢視

方式一:透過檢視日誌檔案,也就是前面設定的slow_query_log_file

快速學會慢查詢SQL排查

方式二:透過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:

快速學會慢查詢SQL排查

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

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

百度網盤:

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

快速學會慢查詢SQL排查 快速學會慢查詢SQL排查

檢視幫助文件:

perl mysqldumpslow.pl --help
快速學會慢查詢SQL排查

舉慄:

  • 獲取返回記錄最多的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"