常用慢查詢分析工具

引言
在日常的業務開發中
MySQL 出現慢查詢是很常見的
大部分情況下會分為兩種情況
1、業務增長太快
2、要么就是SQL 寫的太xx了
所以
對慢查詢 SQL 進行分析和優化很重要
其中 mysqldumpslow 是 MySQL 服務自帶的一款很好的分析調優工具

3.1 調優工具mysqldumpslow

3.1.1 調優工具常用設定

1、什么是MySQL 慢查詢日志

MySQL提供的一種慢查詢日志記錄，用來記錄在MySQL查詢中回應時間超過閥值的記錄
具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中

2、如何查看慢查詢設定情況

慢查詢的時間閾值設定

show variables like '%slow_query_log%';

file

解釋

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%';

file

默認值是10秒

4）如何設定查詢閥值

命令設定

-- 設定慢查詢閥值
set global long_query_time = 1;

備注：另外開一個session或重新連接，才會看到變化

執行成功發發現慢sql的時間變成了1秒

file

組態檔設定

vim my.cnf
[mysqld]
long_query_time = 1
log_output = FILE
重啟MySQL服務

執行成功發發現慢sql的時間變成了1秒

file

5）如何把未使用索引的 SQL 記錄寫入慢查詢日志

-- 查看設定，默認關閉
show variables like 'log_queries_not_using_indexes';

我們發現，未使用索引的sql默認是不記錄到慢查詢日志的

file

開啟配置

set global log_queries_not_using_indexes = on;

執行如下

file

6）模擬資料

-- 睡眠2s再執行
select sleep(2);
-- 查看慢查詢條數
show global status like '%Slow_queries%';

我們發現，每執行一次select sleep(2)，之后，再通過show global status ...命令，他的值就會+1

file

3.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]#

3.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內容

3.2 調優工具show profile

tips：

show profile，它也是調優工具

也是MySQL服務自帶的分析調優工具

不過這款更高級

比較接近底層硬體引數的調優，

簡介：

show profile是MySQL服務自帶更高級的分析調優工具

比較接近底層硬體引數的調優

1、查看show profile設定

-- 默認關閉，保存近15次的運行結果
show variables like 'profiling%';

file

通過上面我們發現，show profile工具默認是關閉狀態，15表示保存了近15次的運行結果，

2、開啟調優工具

執行下面的命令開啟

SET profiling = ON;

再次查看狀態

show variables like 'profiling%';

file

3、查看最近15次的運行結果

-- 查看最近15次的運行結果
show profiles;

-- 可以顯示警告和報錯的資訊
show warnings;

-- 慢查詢陳述句
SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店';

顯示最近15次的運行結果

file

4、診斷運行的SQL

接下來，我們一起診斷一下query id為23的慢查詢

-- 語法
SHOW PROFILE cpu,block io FOR QUERY query id;
-- 示例
SHOW PROFILE cpu,block io FOR QUERY 129;

開始執行

file

解釋：
通過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 //出現死鎖

本文由傳智教育博學谷 - 狂野架構師教研團隊發布
如果本文對您有幫助，歡迎關注和點贊；如果您有任何建議也可留言評論或私信，您的支持是我堅持創作的動力
轉載請注明出處！

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/499836.html

標籤：其他

上一篇：rust庫學習-env_logger

下一篇：SpringBoot（14）JDBCTemplate

【性能優化】MySQL常用慢查詢分析工具

常用慢查詢分析工具

3.1 調優工具mysqldumpslow

3.1.1 調優工具常用設定

3.1.2 調優工具常用命令

3.1.3 慢日志檔案分析

3.2 調優工具show profile