MySQL常用慢查詢分析工具詳解

2022-08-14 18:03:24

引言

在日常的業務開發中
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慢查詢工具內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！