開(kāi)啟慢查詢?nèi)罩?/h2>

在項(xiàng)目中我們會(huì)經(jīng)常遇到慢查詢，當(dāng)我們遇到慢查詢的時(shí)候一般都要開(kāi)啟慢查詢?nèi)罩?，并且分析慢查詢?nèi)罩?，找到慢sql，然后用explain來(lái)分析

系統(tǒng)變量

MySQL和慢查詢相關(guān)的系統(tǒng)變量如下

執(zhí)行如下語(yǔ)句看是否啟用慢查詢?nèi)罩荆琌N為啟用，OFF為沒(méi)有啟用

show variables like "%slow_query_log%"

可以看到我的沒(méi)有啟用，可以通過(guò)如下兩種方式開(kāi)啟慢查詢

修改配置文件

修改配置文件my.ini，在[mysqld]段落中加入如下參數(shù)

[mysqld]
log_output='FILE,TABLE'
slow_query_log='ON'
long_query_time=0.001

需要重啟 MySQL 才可以生效，命令為 service mysqld restart

設(shè)置全局變量

我在命令行中執(zhí)行如下2句打開(kāi)慢查詢?nèi)罩?，設(shè)置超時(shí)時(shí)間為0.001s，并且將日志記錄到文件以及mysql.slow_log表中

set global slow_query_log = on;
set global log_output = 'FILE,TABLE';
set global long_query_time = 0.001;

想要永久生效得到配置文件中配置，否則數(shù)據(jù)庫(kù)重啟后，這些配置失效

分析慢查詢?nèi)罩?/h2>

因?yàn)閙ysql慢查詢?nèi)罩鞠喈?dāng)于是一個(gè)流水賬，并沒(méi)有匯總統(tǒng)計(jì)的功能，所以我們需要用一些工具來(lái)分析一下

mysqldumpslow

mysql內(nèi)置了mysqldumpslow這個(gè)工具來(lái)幫我們分析慢查詢?nèi)罩尽?/p>

常見(jiàn)用法

# 取出使用最多的10條慢查詢
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log

# 取出查詢時(shí)間最慢的3條慢查詢
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log 

# 得到按照時(shí)間排序的前10條里面含有左連接的查詢語(yǔ)句
mysqldumpslow -s t -t 10 -g “l(fā)eft join” /database/mysql/slow-log 

pt-query-digest

pt-query-digest是我用的最多的一個(gè)工具，功能非常強(qiáng)大，可以分析binlog、General log、slowlog，也可以通過(guò)show processlist或者通過(guò)tcpdump抓取的MySQL協(xié)議數(shù)據(jù)來(lái)進(jìn)行分析。pt-query-digest是一個(gè)perl腳本，只需下載并賦權(quán)即可執(zhí)行

下載和賦權(quán)

wget www.percona.com/get/pt-query-digest
chmod u+x pt-query-digest
ln -s /opt/soft/pt-query-digest /usr/bin/pt-query-digest

用法介紹

// 查看具體使用方法 
pt-query-digest --help
// 使用格式
pt-query-digest [OPTIONS] [FILES] [DSN]

常用OPTIONS

• --create-review-table  當(dāng)使用--review參數(shù)把分析結(jié)果輸出到表中時(shí)，如果沒(méi)有表就自動(dòng)創(chuàng)建。
• --create-history-table  當(dāng)使用--history參數(shù)把分析結(jié)果輸出到表中時(shí)，如果沒(méi)有表就自動(dòng)創(chuàng)建。
• --filter  對(duì)輸入的慢查詢按指定的字符串進(jìn)行匹配過(guò)濾后再進(jìn)行分析
• --limit限制輸出結(jié)果百分比或數(shù)量，默認(rèn)值是20,即將最慢的20條語(yǔ)句輸出，如果是50%則按總響應(yīng)時(shí)間占比從大到小排序，輸出到總和達(dá)到50%位置截止。
• --host  mysql服務(wù)器地址
• --user  mysql用戶名
• --password  mysql用戶密碼
• --history 將分析結(jié)果保存到表中，分析結(jié)果比較詳細(xì)，下次再使用--history時(shí)，如果存在相同的語(yǔ)句，且查詢所在的時(shí)間區(qū)間和歷史表中的不同，則會(huì)記錄到數(shù)據(jù)表中，可以通過(guò)查詢同一CHECKSUM來(lái)比較某類型查詢的歷史變化。
• --review 將分析結(jié)果保存到表中，這個(gè)分析只是對(duì)查詢條件進(jìn)行參數(shù)化，一個(gè)類型的查詢一條記錄，比較簡(jiǎn)單。當(dāng)下次使用--review時(shí)，如果存在相同的語(yǔ)句分析，就不會(huì)記錄到數(shù)據(jù)表中。
• --output 分析結(jié)果輸出類型，值可以是report(標(biāo)準(zhǔn)分析報(bào)告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于閱讀。
• --since 從什么時(shí)間開(kāi)始分析，值為字符串，可以是指定的某個(gè)”yyyy-mm-dd [hh:mm:ss]”格式的時(shí)間點(diǎn)，也可以是簡(jiǎn)單的一個(gè)時(shí)間值：s(秒)、h(小時(shí))、m(分鐘)、d(天)，如12h就表示從12小時(shí)前開(kāi)始統(tǒng)計(jì)。
• --until 截止時(shí)間，配合—since可以分析一段時(shí)間內(nèi)的慢查詢。

常用DSN

A    指定字符集
D    指定連接的數(shù)據(jù)庫(kù)
P    連接數(shù)據(jù)庫(kù)端口
S    連接Socket file
h    連接數(shù)據(jù)庫(kù)主機(jī)名
p    連接數(shù)據(jù)庫(kù)的密碼
t    使用--review或--history時(shí)把數(shù)據(jù)存儲(chǔ)到哪張表里
u    連接數(shù)據(jù)庫(kù)用戶名

DSN使用key=value的形式配置；多個(gè)DSN使用,分隔

使用示例

# 展示slow.log中最慢的查詢的報(bào)表
pt-query-digest slow.log

# 分析最近12小時(shí)內(nèi)的查詢
pt-query-digest --since=12h slow.log

# 分析指定范圍內(nèi)的查詢
pt-query-digest slow.log --since '2020-06-20 00:00:00' --until '2020-06-25 00:00:00'

# 把slow.log中查詢保存到query_history表
pt-query-digest --user=root --password=root123 --review h=localhost,D=test,t=query_history --create-review-table slow.log

# 連上localhost，并讀取processlist，輸出到slowlog
pt-query-digest --processlist h=localhost --user=root --password=root123 --interval=0.01 --output slowlog

# 利用tcpdump獲取MySQL協(xié)議數(shù)據(jù)，然后產(chǎn)生最慢查詢的報(bào)表
# tcpdump使用說(shuō)明：https://blog.csdn.net/chinaltx/article/details/87469933
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

# 分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest  --type=binlog mysql-bin000093.sql

# 分析general log
pt-query-digest  --type=genlog  localhost.log

用法實(shí)戰(zhàn)

編寫存儲(chǔ)過(guò)程批量造數(shù)據(jù)

在實(shí)際工作中沒(méi)有測(cè)試性能，我們經(jīng)常需要改造大批量的數(shù)據(jù)，手動(dòng)插入是不太可能的，這時(shí)候就得用到存儲(chǔ)過(guò)程了

CREATE TABLE `kf_user_info` (
  `id` int(11) NOT NULL COMMENT '用戶id',
  `gid` int(11) NOT NULL COMMENT '客服組id',
  `name` varchar(25) NOT NULL COMMENT '客服名字'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客戶信息表';

如何定義一個(gè)存儲(chǔ)過(guò)程呢？

CREATE PROCEDURE 存儲(chǔ)過(guò)程名稱 ([參數(shù)列表])
BEGIN
    需要執(zhí)行的語(yǔ)句
END

舉個(gè)例子，插入id為1-100000的100000條數(shù)據(jù)

用Navicat執(zhí)行

-- 刪除之前定義的
DROP PROCEDURE IF EXISTS create_kf;

-- 開(kāi)始定義
CREATE PROCEDURE create_kf(IN loop_times INT) 
BEGIN
	DECLARE var INT;
	SET var = 1;
	WHILE var < loop_times DO    
		INSERT INTO kf_user_info (`id`,`gid`,`name`) 
		VALUES (var, 1000, var);
		SET var = var + 1;
	END WHILE; 
END;

-- 調(diào)用
call create_kf(100000);

存儲(chǔ)過(guò)程的三種參數(shù)類型

用MySQL執(zhí)行

得用DELIMITER 定義新的結(jié)束符，因?yàn)槟J(rèn)情況下SQL采用（;）作為結(jié)束符，這樣當(dāng)存儲(chǔ)過(guò)程中的每一句SQL結(jié)束之后，采用（；）作為結(jié)束符，就相當(dāng)于告訴MySQL可以執(zhí)行這一句了。但是存儲(chǔ)過(guò)程是一個(gè)整體，我們不希望SQL逐條執(zhí)行，而是采用存儲(chǔ)過(guò)程整段執(zhí)行的方式，因此我們就需要定義新的DELIMITER ，新的結(jié)束符可以用（//）或者（$$）

因?yàn)樯厦娴拇a應(yīng)該就改為如下這種方式

DELIMITER //
CREATE PROCEDURE create_kf_kfGroup(IN loop_times INT)  
BEGIN  
	DECLARE var INT;
	SET var = 1;
	WHILE var <= loop_times DO    
		INSERT INTO kf_user_info (`id`,`gid`,`name`) 
		VALUES (var, 1000, var);
		SET var = var + 1;
	END WHILE;  
END //
DELIMITER ; 

查詢已經(jīng)定義的存儲(chǔ)過(guò)程

show procedure status;

開(kāi)始執(zhí)行慢sql

select * from kf_user_info where id = 9999;
select * from kf_user_info where id = 99999;
update kf_user_info set gid = 2000 where id = 8888;
update kf_user_info set gid = 2000 where id = 88888;

可以執(zhí)行如下sql查看慢sql的相關(guān)信息。

SELECT * FROM mysql.slow_log order by start_time desc;

查看一下慢日志存儲(chǔ)位置

show variables like "slow_query_log_file"

pt-query-digest /var/lib/mysql/VM-0-14-centos-slow.log

執(zhí)行后的文件如下

# Profile
# Rank Query ID                            Response time Calls R/Call V/M 
# ==== =================================== ============= ===== ====== ====
#    1 0xE2566F6154AFF41948FE497E53631B43   0.1480 56.1%     4 0.0370  0.00 UPDATE kf_user_info
#    2 0x2DFBC6DBF0D68EF2EC2AE954DC37A1A4   0.1109 42.1%     4 0.0277  0.00 SELECT kf_user_info
# MISC 0xMISC                               0.0047  1.8%     2 0.0024   0.0 <2 ITEMS>

從最上面的統(tǒng)計(jì)sql中就可以看到執(zhí)行慢的sql

可以看到響應(yīng)時(shí)間，執(zhí)行次數(shù)，每次執(zhí)行耗時(shí)（單位秒），執(zhí)行的sql

下面就是各個(gè)慢sql的詳細(xì)分析，比如，執(zhí)行時(shí)間，獲取鎖的時(shí)間，執(zhí)行時(shí)間分布，所在的表等信息

不由得感嘆一聲，真是神器，查看慢sql超級(jí)方便

最后說(shuō)一個(gè)我遇到的一個(gè)有意思的問(wèn)題，有一段時(shí)間線上的接口特別慢，但是我查日志發(fā)現(xiàn)sql執(zhí)行的很快，難道是網(wǎng)絡(luò)的問(wèn)題？

為了確定是否是網(wǎng)絡(luò)的問(wèn)題，我就用攔截器看了一下接口的執(zhí)行時(shí)間，發(fā)現(xiàn)耗時(shí)很長(zhǎng)，考慮到方法加了事務(wù)，難道是事務(wù)提交很慢？

于是我用pt-query-digest統(tǒng)計(jì)了一下1分鐘左右的慢日志，發(fā)現(xiàn)事務(wù)提交的次很多，但是每次提交事務(wù)的平均時(shí)長(zhǎng)是1.4s左右，果然是事務(wù)提交很慢。

參考博客

很全的一篇文章
[0]https://zhuanlan.zhihu.com/p/106405711
[1]https://blog.csdn.net/lt326030434/article/details/109222848
[1]https://tech.meituan.com/2014/06/30/mysql-index.html
[2]https://blog.csdn.net/itguangit/article/details/82145322
[3]https://mp.weixin.qq.com/s/_SWewX-8nFam20Wcg6No1Q
下載
[4]https://www.cnblogs.com/zi-xing/p/4269854.html

到此這篇關(guān)于MySQL如何快速定位慢SQL的實(shí)戰(zhàn) 的文章就介紹到這了,更多相關(guān)MySQL 定位慢SQL內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論