MySQL常用慢查詢分析工具詳解
更新時(shí)間：2022年08月14日 14:56:21 作者：博學(xué)谷狂野架構(gòu)師???????
這篇文章主要介紹了MySQL常用慢查詢分析工具詳解，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下
引言
在日常的業(yè)務(wù)開發(fā)中
MySQL 出現(xiàn)慢查詢是很常見的
大部分情況下會分為兩種情況：
- 1、業(yè)務(wù)增長太快
- 2、要么就是SQL 寫的太xx了
所以
對慢查詢 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)罩居涗?，用來記錄在MySQL查詢中響應(yīng)時(shí)間超過閥值的記錄 具體指運(yùn)行時(shí)間超過long_query_time值的SQL，則會被記錄到慢查詢?nèi)罩局?/p>
2、如何查看慢查詢設(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; //只對當(dāng)前會話生效，重啟失效
執(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 會記錄到慢查詢?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;
備注：另外開一個session或重新連接 ，才會看到變化
執(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 ...命令，他的值就會+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 后邊搭配一個正則匹配模式，大小寫不敏感
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: 壞的解釋器: 沒有那個文件或目錄 [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)工具
不過這款更高級
比較接近底層硬件參數(shù)的調(diào)優(yōu)。
簡介：
show profile是MySQL服務(wù)自帶更高級的分析調(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)錯的信息 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 //顯示頁面錯誤相關(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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
tpch是TPC(Transaction Processing Performance Council)組織提供的工具包。用于進(jìn)行OLAP測試，以評估商業(yè)分析中決策支持系統(tǒng)（DSS）的性能。它包含了一整套面向商業(yè)的ad-hoc查詢和并發(fā)數(shù)據(jù)修改，強(qiáng)調(diào)測試的是數(shù)據(jù)庫、平臺和I/O性能，關(guān)注查詢能力2016-05-05
MySQL?的觸發(fā)器和存儲過程一樣，都是嵌入到?MySQL?中的一段程序，是?MySQL?中管理數(shù)據(jù)的有力工具，本文給大家詳細(xì)介紹mysql觸發(fā)器的定義語法知識，感興趣的朋友一起看看吧2022-11-11
本文給大家分享的是將項(xiàng)目從MySQL遷移至MariaDB的詳細(xì)步驟，非常的實(shí)用，有需要的小伙伴可以參考下2017-07-07
這篇文章主要給大家介紹了關(guān)于MySQL多表連接的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
MySQL單表百萬數(shù)據(jù)記錄分頁性能優(yōu)化技巧
自己的一個網(wǎng)站，由于單表的數(shù)據(jù)記錄高達(dá)了一百萬條，造成數(shù)據(jù)訪問很慢，Google分析的后臺經(jīng)常報(bào)告超時(shí)，尤其是頁碼大的頁面更是慢的不行2016-08-08
MYSQL不能從遠(yuǎn)程連接的一個解決方法(s not allowed to connect to this MySQL s
MYSQL不能從遠(yuǎn)程連接的一個解決方法(s not allowed to connect to this MySQL server)2011-08-08
MySQL一鍵安裝Shell腳本的實(shí)現(xiàn)
本文主要介紹了MySQL一鍵安裝Shell腳本，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01
Win10安裝mysql8.0.15 winx64及連接服務(wù)器過程中遇到的問題
這篇文章主要介紹了Win10安裝mysql8.0.15 winx64及連接服務(wù)器過程中遇到的問題,本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2019-12-12
這篇文章主要為大家詳細(xì)介紹了基于Windows安裝MySQL 8.0.12圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-08-08
mysql decimal數(shù)據(jù)類型轉(zhuǎn)換的實(shí)現(xiàn)
這篇文章主要介紹了mysql decimal數(shù)據(jù)類型轉(zhuǎn)換的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02