MySQL常用慢查詢分析工具詳解
引言
在日常的業務開發中
MySQL 出現慢查詢是很常見的
大部分情況下會分為兩種情況：
- 1、業務增長太快
- 2、要麼就是SQL 寫的太xx瞭
所以
對慢查詢 SQL 進行分析和優化很重要
其中 mysqldumpslow 是 MySQL 服務自帶的一款很好的分析調優工具
1、調優工具mysqldumpslow
1.1調優工具常用設置
1、什麼是MySQL 慢查詢日志
MySQL提供的一種慢查詢日志記錄，用來記錄在MySQL查詢中響應時間超過閥值的記錄 具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中
2、如何查看慢查詢設置情況
慢查詢的時間閾值設置
show variables like '%slow_query_log%';
解釋：
- slow_query_log //是否開啟，默認關閉，建議調優時才開啟
- slow_query_log_file //慢查詢日志存放路徑
3、如何開啟慢查詢日志記錄
1) 命令開啟
set global slow_query_log =1; //隻對當前會話生效，重啟失效
執行成功
再次執行
show variables like '%slow_query_log%';
先關閉客戶端連接，再進行重新連接，即可看到設置生效
發現開啟瞭mysqldumpslow調優工具
mysql> show variables like '%slow_query_log%'; +---------------------+-------------------------------------------+ | Variable_name | Value | +---------------------+-------------------------------------------+ | slow_query_log | ON | | slow_query_log_file | /opt/mysql-5.7.28/data/linux-141-slow.log | +---------------------+-------------------------------------------+ 2 rows in set (0.02 sec) mysql>
2）配置文件開啟
vim my.cnf 在[mysqld]下添加： slow_query_log = 1 slow_query_log_file = /opt/mysql-5.7.28/data/linux-141-slow.log 重啟MySQL服務
修改並且重啟後
發現開啟瞭mysqldumpslow調優工具
mysql> show variables like '%slow_query_log%'; +---------------------+-------------------------------------------+ | Variable_name | Value | +---------------------+-------------------------------------------+ | slow_query_log | ON | | slow_query_log_file | /opt/mysql-5.7.28/data/linux-141-slow.log | +---------------------+-------------------------------------------+ 2 rows in set (0.02 sec) mysql>
3）哪些 SQL 會記錄到慢查詢日志
-- 查看閥值（大於），默認10s show variables like 'long_query_time%';
默認值是10秒
4）如何設置查詢閥值
命令設置
-- 設置慢查詢閥值 set global long_query_time = 1;
備註：另外開一個session或重新連接 ，才會看到變化
執行成功發發現慢sql的時間變成瞭1秒
配置文件設置：
vim my.cnf [mysqld] long_query_time = 1 log_output = FILE 重啟MySQL服務
執行成功發發現慢sql的時間變成瞭1秒
5）如何把未使用索引的 SQL 記錄寫入慢查詢日志
-- 查看設置，默認關閉 show variables like 'log_queries_not_using_indexes';
我們發現，未使用索引的sql默認是不記錄到慢查詢日志的
開啟配置：
set global log_queries_not_using_indexes = on;
執行如下：
6）模擬數據
-- 睡眠2s再執行 select sleep(2); -- 查看慢查詢條數 show global status like '%Slow_queries%';
我們發現，每執行一次select sleep(2)，之後，再通過show global status …命令，他的值就會+1
1.2 調優工具常用命令
語法格式
mysqldumpslow [ OPTS... ] [ LOGS... ] //命令行格式
常用到的格式組合：
- -s 表示按照何種方式排序
- c 訪問次數
- l 鎖定時間
- r 返回記錄
- t 查詢時間
- al 平均鎖定時間
- ar 平均返回記錄數
- at 平均查詢時間
- -t 返回前面多少條數據
- -g 後邊搭配一個正則匹配模式，大小寫不敏感
1、拿到慢日志路徑
show variables like '%slow_query_log%';
日志路徑為：/opt/mysql-5.7.28/data/linux-141-slow.log
查看日志
[root@linux-141 mysql-5.7.28]# cat /opt/mysql-5.7.28/data/linux-141-slow.log /opt/mysql-5.7.28/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument # Time: 2021-09-15T01:40:31.342430Z # User@Host: root[root] @ [192.168.36.1] Id: 2 # Query_time: 2.000863 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 use itcast; SET timestamp=1631670031; -- 睡眠2s再執行 select sleep(2); [root@linux-141 mysql-5.7.28]#
2、得到訪問次數最多的10條SQL
[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s r -t 10 /opt/mysql-5.7.28/data/linux-141-slow.log -bash: ./bin/mysqldumpslow: /usr/bin/perl: 壞的解釋器: 沒有那個文件或目錄 [root@linux-141 mysql-5.7.28]# yum -y install perl perl-devel [root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s r -t 10 /opt/mysql-5.7.28/data/linux-141-slow.log
3、按照時間排序的前10條裡面含有左連接的SQL
[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s t -t 10 -g "left join" /opt/mysql-5.7.28/data/linux-141-slow.log Reading mysql slow query log from /opt/mysql-5.7.28/data/linux-141-slow.log Died at ./bin/mysqldumpslow line 167, <> chunk 28. [root@linux-141 mysql-5.7.28]#
1.3 慢日志文件分析
1、查看慢查詢日志
[root@linux-141 mysql-5.7.28]# cat /opt/mysql-5.7.28/data/linux-141-slow.log /opt/mysql-5.7.28/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument # Time: 2021-09-15T01:40:31.342430Z # User@Host: root[root] @ [192.168.36.1] Id: 2 # Query_time: 2.000863 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 use itcast; SET timestamp=1631670031; -- 睡眠2s再執行 select sleep(2); # Time: 2021-09-15T01:50:32.130305Z # User@Host: root[root] @ [192.168.36.1] Id: 2 # Query_time: 3.001904 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1631670632; select sleep(3); # Time: 2021-09-15T01:50:55.064372Z # User@Host: root[root] @ [192.168.36.1] Id: 2 # Query_time: 4.008082 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1631670655; select sleep(4); # Time: 2021-09-15T01:51:01.343463Z # User@Host: root[root] @ [192.168.36.1] Id: 2 # Query_time: 5.007035 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1631670661; select sleep(5); # Time: 2021-09-15T01:51:07.737834Z ###### 執行SQL時間 # User@Host: root[root] @ [192.168.36.1] Id: 2 ###### 執行SQL的主機信息 # Query_time: 6.009129 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 ###### SQL的執行信息 SET timestamp=1631670667; ###### SQL執行時間 select sleep(6); ###### SQL內容 [root@linux-141 mysql-5.7.28]#
屬性解釋
# Time: 2021-09-15T01:51:07.737834Z ###### 執行SQL時間 # User@Host: root[root] @ [192.168.36.1] Id: 2 ###### 執行SQL的主機信息 # Query_time: 6.009129 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 ###### SQL的執行信息 SET timestamp=1631670667; ###### SQL執行時間 select sleep(6); ###### SQL內容
2、 調優工具show profile
tips：
show profile，它也是調優工具
也是MySQL服務自帶的分析調優工具
不過這款更高級
比較接近底層硬件參數的調優。
簡介：
show profile是MySQL服務自帶更高級的分析調優工具
比較接近底層硬件參數的調優
1、查看show profile設置
-- 默認關閉，保存近15次的運行結果 show variables like 'profiling%';
通過上面我們發現，show profile工具默認是關閉狀態，15表示保存瞭近15次的運行結果。
2、開啟調優工具
執行下面的命令開啟
SET profiling = ON;
再次查看狀態
show variables like 'profiling%';
3、查看最近15次的運行結果
-- 查看最近15次的運行結果 show profiles; -- 可以顯示警告和報錯的信息 show warnings; -- 慢查詢語句 SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店';
顯示最近15次的運行結果
4、診斷運行的SQL
接下來，我們一起診斷一下query id為23的慢查詢
-- 語法 SHOW PROFILE cpu,block io FOR QUERY query id; -- 示例 SHOW PROFILE cpu,block io FOR QUERY 129;
開始執行：
解釋：通過Status一列，可以看到整條SQL的運行過程
- 1. starting //開始
- 2. checking permissions //檢查權限
- 3. Opening tables //打開數據表
- 4. init //初始化
- 5. System lock //鎖機制
- 6. optimizing //優化器
- 7. statistics //分析語法樹
- 8. prepareing //預準備
- 9. executing //引擎執行開始
- 10. end //引擎執行結束
- 11. query end //查詢結束
- 12. closing tables //釋放數據表
- 13. freeing items //釋放內存
- 14. cleaning up //徹底清理
查看類型選項 SHOW PROFILE...後面的列，即：SHOW PROFILE ALL, BLOCK IO, ... FOR QUERY 209; ALL //顯示索引的開銷信息 BLOCK IO //顯示塊IO相關開銷 CONTEXT SWITCHES //上下文切換相關開銷 CPU //顯示CPU相關開銷信息 IPC //顯示發送和接收相關開銷信息 MEMORY //顯示內存相關開銷信息 PAGE FAULTS //顯示頁面錯誤相關開銷信息 SOURCE //顯示和source_function,source_file,source_line相關的開銷信息 SWAPS //顯示交換次數相關開銷的信息
重要提示：
如出現以下一種或者幾種情況，說明SQL執行性能極其低下，亟需優化 * converting HEAP to MyISAM //查詢結果太大，內存都不夠用瞭往磁盤上搬瞭 * Creating tmp table //創建臨時表：拷貝數據到臨時表，用完再刪 * Copying to tmp table on disk //把內存中臨時表復制到磁盤，危險 * locked //出現死鎖
到此這篇關於MySQL常用慢查詢分析工具詳解的文章就介紹到這瞭,更多相關MySQL慢查詢工具內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！