首頁  >  文章  >  資料庫  >  MySQL怎麼快速定位慢SQL

MySQL怎麼快速定位慢SQL

PHPz
PHPz轉載
2023-06-02 19:34:121935瀏覽

開啟慢查詢日誌

在專案中我們會經常遇到慢查詢,當我們遇到慢查詢的時候一般都要開啟慢查詢日誌,並且分析慢查詢日誌,找到慢sql,接著用explain來分析

系統變數

##MySQL和慢查詢相關的系統變數如下

##參數slow_query_log#log_outputslow_query_log_filelong_query_time

執行如下語句看是否啟用慢查詢日誌,ON為啟用,OFF為沒有啟用

show variables like "%slow_query_log%"

MySQL怎麼快速定位慢SQL

可以看到我的沒有啟用,可以透過以下兩種方式開啟慢查詢

修改設定檔

修改設定檔my.ini,在[mysqld]段落中加入以下參數

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

需要重新啟動MySQL 才可以生效,指令為service mysqld restart

設定全域變數

我在指令列中執行如下2句開啟慢查詢日誌,設定逾時時間為0.001s,並且將日誌記錄到檔案以及mysql.slow_log表中

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

想要永久生效得到設定檔中配置,否則資料庫重新啟動後,這些配置失效

分析慢查詢日誌

因為mysql慢查詢日誌相當於是一個流水賬,並沒有匯總統計的功能,所以我們需要用一些工具來分析一下

mysqldumpslow

mysql內建了mysqldumpslow這個工具來幫我們分析慢查詢日誌。

MySQL怎麼快速定位慢SQL

常見用法

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

# 取出查询时间最慢的3条慢查询
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log 

# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log

pt-query-digest

pt-query-digest是我用的最多的一個工具,功能非常強大,可以分析binlog、General log、slowlog,也可以透過show processlist或是透過tcpdump抓取的MySQL協定資料來進行分析。只要下載並授權,即可執行pt-query-digest 這個Perl 腳本

下載和賦權

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  當使用--review參數把分析結果輸出到表中時,如果沒有表格就自動建立。

  • --create-history-table  當使用--history參數把分析結果輸出到表中時,如果沒有表格就自動建立。

  • --filter  對輸入的慢查詢按指定的字串進行匹配過濾後再進行分析

  • --limit限制輸出結果百分比或數量,預設值是20,即將最慢的20個語句輸出,如果是50%則按總回應時間佔比從大到小排序,輸出到總和達到50%位置截止。

  • --host  mysql伺服器位址

  • --user  mysql使用者名稱

  • -- password  mysql使用者密碼

  • --history 將分析結果儲存到表中,分析結果比較詳細,下次再使用--history時,如果有相同的語句,且查詢所在的時間區間和歷史表中的不同,則會記錄到資料表中,可以透過查詢相同CHECKSUM來比較某類型查詢的歷史變化。

  • --review 將分析結果儲存到表中,這個分析只是對查詢條件進行參數化,一個類型的查詢一筆記錄,比較簡單。如果出現相同的語句分析,下次使用--review時,不會被記錄到資料表中。

  • --output 分析結果輸出類型,值可以是report(標準分析報告)、slowlog(Mysql slow log)、json、json-anon,一般使用report,以便於閱讀。

  • --since 從什麼時間開始分析,值為字串,可以是指定的某個」yyyy-mm-dd [hh:mm:ss]」格式的時間點,也可以是簡單的一個時間值:s(秒)、h(小時)、m(分鐘)、d(天),如12h表示從12小時前開始統計。

  • --until 截止時間,配合—since可以分析一段時間內的慢查詢。

常用DSN

A    指定字元集
D    指定連接的資料庫
P    連接資料庫連接埠
S    連接Socket file
h    連接資料庫主機名稱
p    連接資料庫的密碼
t    使用--review或--history時將資料儲存到哪張表裡
u    連接資料庫使用者名稱

DSN使用key=value的形式配置;多個DSN使用,分隔

使用範例

# 展示slow.log中最慢的查询的报表
pt-query-digest slow.log

# 分析最近12小时内的查询
pt-query-digest --since=12h slow.log

# 分析指定范围内的查询
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协议数据,然后产生最慢查询的报表
# tcpdump使用说明: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

用法實戰

##編寫儲存過程批量造數據

在實際工作中沒有測試效能,我們經常需要改造大批量的數據,手動插入是不太可能的,這時候就得用到預存程序了

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='客户信息表';

如何定義一個預存程序呢?

CREATE PROCEDURE 存储过程名称 ([参数列表])
BEGIN
    需要执行的语句
END

舉個例子,插入id為1-100000的100000條資料

用Navicat執行

-- 删除之前定义的
DROP PROCEDURE IF EXISTS create_kf;

-- 开始定义
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;

-- 调用
call create_kf(100000);

預存程序的三種參數型別

意義
#是否啟用慢速查詢日誌, ON為啟用,OFF為沒有啟用,預設為OFF
日誌輸出位置,預設為FILE,即儲存為文件,若設定為TABLE,則將日誌記錄到mysql.show_log表中,支援設定多種格式
指定慢查詢日誌檔案的路徑和名字
執行時間超過該值才記錄到慢查詢日誌,單位為秒,預設為10
參數類型是否回傳作用IN否向預存程序傳入參數,預存程序修改參數的值,不能被傳回OUT是把預存程序計算的結果放到該參數中,呼叫者可以得到傳回值INOUT是IN和OUT的結合,也就是用於預存程序的傳入參數,同時又可以把運算結構放到參數中,呼叫者可以得到回傳值
###

用MySQL执行

得用DELIMITER 定义新的结束符,因为默认情况下SQL采用(;)作为结束符,这样当存储过程中的每一句SQL结束之后,采用(;)作为结束符,就相当于告诉MySQL可以执行这一句了。但是存储过程是一个整体,我们不希望SQL逐条执行,而是采用存储过程整段执行的方式,因此我们就需要定义新的DELIMITER ,新的结束符可以用(//)或者($$)

因为上面的代码应该就改为如下这种方式

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 ;

查询已经定义的存储过程

show procedure status;

开始执行慢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;

可以执行如下sql查看慢sql的相关信息。

SELECT * FROM mysql.slow_log order by start_time desc;

查看一下慢日志存储位置

show variables like "slow_query_log_file"
pt-query-digest /var/lib/mysql/VM-0-14-centos-slow.log

执行后的文件如下

MySQL怎麼快速定位慢SQL

# 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>

从最上面的统计sql中就可以看到执行慢的sql

可以看到响应时间,执行次数,每次执行耗时(单位秒),执行的sql

下面就是各个慢sql的详细分析,比如,执行时间,获取锁的时间,执行时间分布,所在的表等信息

不由得感叹一声,真是神器,查看慢sql超级方便

最后说一个我遇到的一个有意思的问题,有一段时间线上的接口特别慢,但是我查日志发现sql执行的很快,难道是网络的问题?

为了确定是否是网络的问题,我就用拦截器看了一下接口的执行时间,发现耗时很长,考虑到方法加了事务,难道是事务提交很慢?

于是我用pt-query-digest统计了一下1分钟左右的慢日志,发现事务提交的次很多,但是每次提交事务的平均时长是1.4s左右,果然是事务提交很慢。

MySQL怎麼快速定位慢SQL

以上是MySQL怎麼快速定位慢SQL的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:yisu.com。如有侵權,請聯絡admin@php.cn刪除