MySQL常用慢查詢分析工具詳解
更新時(shí)間：2022年08月14日 14:56:21 作者：博學(xué)谷狂野架構(gòu)師???????
這篇文章主要介紹了MySQL常用慢查詢分析工具詳解，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下
引言
在日常的業(yè)務(wù)開發(fā)中
MySQL 出現(xiàn)慢查詢是很常見的
大部分情況下會(huì)分為兩種情況：
- 1、業(yè)務(wù)增長(zhǎng)太快
- 2、要么就是SQL 寫的太xx了
所以
對(duì)慢查詢 SQL 進(jìn)行分析和優(yōu)化很重要
其中 mysqldumpslow 是 MySQL 服務(wù)自帶的一款很好的分析調(diào)優(yōu)工具
1、調(diào)優(yōu)工具mysqldumpslow
1.1調(diào)優(yōu)工具常用設(shè)置
1、什么是MySQL 慢查詢?nèi)罩?/strong>
MySQL提供的一種慢查詢?nèi)罩居涗洠脕碛涗浽贛ySQL查詢中響應(yīng)時(shí)間超過閥值的記錄 具體指運(yùn)行時(shí)間超過long_query_time值的SQL，則會(huì)被記錄到慢查詢?nèi)罩局?/p>
2、如何查看慢查詢?cè)O(shè)置情況
慢查詢的時(shí)間閾值設(shè)置
show variables like '%slow_query_log%';
解釋：
- slow_query_log //是否開啟，默認(rèn)關(guān)閉，建議調(diào)優(yōu)時(shí)才開啟
- slow_query_log_file //慢查詢?nèi)罩敬娣怕窂?/li>
3、如何開啟慢查詢?nèi)罩居涗?/strong>
1) 命令開啟
set global slow_query_log =1; //只對(duì)當(dāng)前會(huì)話生效，重啟失效
執(zhí)行成功
再次執(zhí)行
show variables like '%slow_query_log%';
先關(guān)閉客戶端連接，再進(jìn)行重新連接，即可看到設(shè)置生效
發(fā)現(xiàn)開啟了mysqldumpslow調(diào)優(yōu)工具
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服務(wù)
修改并且重啟后
發(fā)現(xiàn)開啟了mysqldumpslow調(diào)優(yōu)工具
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 會(huì)記錄到慢查詢?nèi)罩?/strong>
-- 查看閥值（大于），默認(rèn)10s show variables like 'long_query_time%';
默認(rèn)值是10秒
4）如何設(shè)置查詢閥值
命令設(shè)置
-- 設(shè)置慢查詢閥值 set global long_query_time = 1;
備注：另外開一個(gè)session或重新連接 ，才會(huì)看到變化
執(zhí)行成功發(fā)發(fā)現(xiàn)慢sql的時(shí)間變成了1秒
配置文件設(shè)置：
vim my.cnf [mysqld] long_query_time = 1 log_output = FILE 重啟MySQL服務(wù)
執(zhí)行成功發(fā)發(fā)現(xiàn)慢sql的時(shí)間變成了1秒
5）如何把未使用索引的 SQL 記錄寫入慢查詢?nèi)罩?/strong>
-- 查看設(shè)置，默認(rèn)關(guān)閉 show variables like 'log_queries_not_using_indexes';
我們發(fā)現(xiàn)，未使用索引的sql默認(rèn)是不記錄到慢查詢?nèi)罩镜?/p>
開啟配置：
set global log_queries_not_using_indexes = on;
執(zhí)行如下：
6）模擬數(shù)據(jù)
-- 睡眠2s再執(zhí)行 select sleep(2); -- 查看慢查詢條數(shù) show global status like '%Slow_queries%';
我們發(fā)現(xiàn)，每執(zhí)行一次select sleep(2)，之后，再通過show global status ...命令，他的值就會(huì)+1
1.2 調(diào)優(yōu)工具常用命令
語法格式
mysqldumpslow [ OPTS... ] [ LOGS... ] //命令行格式
常用到的格式組合：
- -s 表示按照何種方式排序
- c 訪問次數(shù)
- l 鎖定時(shí)間
- r 返回記錄
- t 查詢時(shí)間
- al 平均鎖定時(shí)間
- ar 平均返回記錄數(shù)
- at 平均查詢時(shí)間
- -t 返回前面多少條數(shù)據(jù)
- -g 后邊搭配一個(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再執(zhí)行 select sleep(2); [root@linux-141 mysql-5.7.28]#
2、得到訪問次數(shù)最多的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: 壞的解釋器: 沒有那個(gè)文件或目錄 [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、按照時(shí)間排序的前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、查看慢查詢?nèi)罩?/strong>
[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再執(zhí)行 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 ###### 執(zhí)行SQL時(shí)間 # User@Host: root[root] @ [192.168.36.1] Id: 2 ###### 執(zhí)行SQL的主機(jī)信息 # Query_time: 6.009129 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 ###### SQL的執(zhí)行信息 SET timestamp=1631670667; ###### SQL執(zhí)行時(shí)間 select sleep(6); ###### SQL內(nèi)容 [root@linux-141 mysql-5.7.28]#
屬性解釋
# Time: 2021-09-15T01:51:07.737834Z ###### 執(zhí)行SQL時(shí)間 # User@Host: root[root] @ [192.168.36.1] Id: 2 ###### 執(zhí)行SQL的主機(jī)信息 # Query_time: 6.009129 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 ###### SQL的執(zhí)行信息 SET timestamp=1631670667; ###### SQL執(zhí)行時(shí)間 select sleep(6); ###### SQL內(nèi)容
2、 調(diào)優(yōu)工具show profile
tips：
show profile，它也是調(diào)優(yōu)工具
也是MySQL服務(wù)自帶的分析調(diào)優(yōu)工具
不過這款更高級(jí)
比較接近底層硬件參數(shù)的調(diào)優(yōu)。
簡(jiǎn)介：
show profile是MySQL服務(wù)自帶更高級(jí)的分析調(diào)優(yōu)工具
比較接近底層硬件參數(shù)的調(diào)優(yōu)
1、查看show profile設(shè)置
-- 默認(rèn)關(guān)閉，保存近15次的運(yùn)行結(jié)果 show variables like 'profiling%';
通過上面我們發(fā)現(xiàn)，show profile工具默認(rèn)是關(guān)閉狀態(tài)，15表示保存了近15次的運(yùn)行結(jié)果。
2、開啟調(diào)優(yōu)工具
執(zhí)行下面的命令開啟
SET profiling = ON;
再次查看狀態(tài)
show variables like 'profiling%';
3、查看最近15次的運(yùn)行結(jié)果
-- 查看最近15次的運(yùn)行結(jié)果 show profiles; -- 可以顯示警告和報(bào)錯(cuò)的信息 show warnings; -- 慢查詢語句 SELECT * FROM product_list WHERE store_name = '聯(lián)想北達(dá)興科專賣店';
顯示最近15次的運(yùn)行結(jié)果
4、診斷運(yùn)行的SQL
接下來，我們一起診斷一下query id為23的慢查詢
-- 語法 SHOW PROFILE cpu,block io FOR QUERY query id; -- 示例 SHOW PROFILE cpu,block io FOR QUERY 129;
開始執(zhí)行：
解釋：通過Status一列，可以看到整條SQL的運(yùn)行過程
- 1. starting //開始
- 2. checking permissions //檢查權(quán)限
- 3. Opening tables //打開數(shù)據(jù)表
- 4. init //初始化
- 5. System lock //鎖機(jī)制
- 6. optimizing //優(yōu)化器
- 7. statistics //分析語法樹
- 8. prepareing //預(yù)準(zhǔn)備
- 9. executing //引擎執(zhí)行開始
- 10. end //引擎執(zhí)行結(jié)束
- 11. query end //查詢結(jié)束
- 12. closing tables //釋放數(shù)據(jù)表
- 13. freeing items //釋放內(nèi)存
- 14. cleaning up //徹底清理
查看類型選項(xiàng) SHOW PROFILE...后面的列，即：SHOW PROFILE ALL, BLOCK IO, ... FOR QUERY 209; ALL //顯示索引的開銷信息 BLOCK IO //顯示塊IO相關(guān)開銷 CONTEXT SWITCHES //上下文切換相關(guān)開銷 CPU //顯示CPU相關(guān)開銷信息 IPC //顯示發(fā)送和接收相關(guān)開銷信息 MEMORY //顯示內(nèi)存相關(guān)開銷信息 PAGE FAULTS //顯示頁面錯(cuò)誤相關(guān)開銷信息 SOURCE //顯示和source_function,source_file,source_line相關(guān)的開銷信息 SWAPS //顯示交換次數(shù)相關(guān)開銷的信息
重要提示：
如出現(xiàn)以下一種或者幾種情況，說明SQL執(zhí)行性能極其低下，亟需優(yōu)化 * converting HEAP to MyISAM //查詢結(jié)果太大，內(nèi)存都不夠用了往磁盤上搬了 * Creating tmp table //創(chuàng)建臨時(shí)表：拷貝數(shù)據(jù)到臨時(shí)表，用完再刪 * Copying to tmp table on disk //把內(nèi)存中臨時(shí)表復(fù)制到磁盤，危險(xiǎn) * locked //出現(xiàn)死鎖
到此這篇關(guān)于MySQL常用慢查詢分析工具詳解的文章就介紹到這了,更多相關(guān)MySQL慢查詢工具內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了mysql中如何對(duì)列求和問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-05-05
linux二進(jìn)制通用包安裝mysql5.6.20教程
這篇文章主要為大家詳細(xì)介紹了linux二進(jìn)制通用包安裝mysql5.6.20的相關(guān)資料，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
鎖是mysql提供的一種保證不同事務(wù)讀寫隔離的重要措施,通過鎖機(jī)制可以有效提升決多線程下并發(fā)處理事務(wù)能力,不同的鎖劃分對(duì)應(yīng)著不同的使用場(chǎng)景,本文來深入探討一下mysql的另一種容易被忽視的鎖,即間隙鎖,以及與之相關(guān)的相關(guān)問題,需要的朋友可以參考下2023-08-08
這篇文章主要介紹了mysql刪除操作其實(shí)是假刪除問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
大家都知道阿里云默認(rèn)是不能用客戶端遠(yuǎn)程連接的，但最近因?yàn)楣ぷ鞯男枰?，不得不?shí)現(xiàn)遠(yuǎn)程連接，下面這篇文章主要給大家介紹了在阿里云下配置MySQL遠(yuǎn)程連接的步驟，需要的朋友可以參考借鑒，下面來一起看看吧。2017-08-08
安裝Mysql時(shí)可能會(huì)遇到的一些疑難雜癥
這篇文章主要給大家介紹了關(guān)于安裝Mysql時(shí)可能會(huì)遇到的一些疑難雜癥，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-01-01
這篇文章主要介紹了MySQL?數(shù)據(jù)持久化過程講解，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，?需要的朋友可以參考一下，希望對(duì)你的學(xué)習(xí)有所幫助2022-07-07
MySQL中你可能忽略的COLLATION實(shí)例詳解
mysql的collation大致的意思就是字符序，這篇文章主要給大家介紹了關(guān)于MySQL中COLLATION的相關(guān)資料，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-05-05
MySql版本問題sql_mode=only_full_group_by的完美解決方案
這篇文章主要介紹了MySql版本問題sql_mode=only_full_group_by的完美解決方案,需要的朋友可以參考下2017-07-07
今天小編就為大家分享一篇關(guān)于Mysql5.7修改root密碼教程，小編覺得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧2019-02-02