Bagaimana untuk mencari SQL perlahan dalam MySQL dengan cepat

Dayakan log pertanyaan perlahan

Kami sering menghadapi pertanyaan perlahan dalam projek Apabila kami menghadapi pertanyaan perlahan, kami biasanya perlu mendayakan log pertanyaan perlahan dan menganalisis log pertanyaan perlahan untuk mencari SQL perlahan. Kemudian gunakan explain untuk menganalisis

pembolehubah sistem

Pembolehubah sistem yang berkaitan dengan MySQL dan pertanyaan perlahan adalah seperti berikut

参数	含义
slow_query_log	是否启用慢查询日志， ON为启用，OFF为没有启用，默认为OFF
log_output	日志输出位置，默认为FILE，即保存为文件，若设置为TABLE，则将日志记录到mysql.show_log表中，支持设置多种格式
slow_query_log_file	指定慢查询日志文件的路径和名字
long_query_time	执行时间超过该值才记录到慢查询日志，单位为秒，默认为10

Laksanakan pernyataan berikut untuk melihat sama ada log pertanyaan perlahan didayakan bermakna didayakan, OFF bermaksud tidak didayakan

show variables like "%slow_query_log%"

Anda boleh melihat bahawa lombong tidak didayakan. . Anda boleh menggunakan dua kaedah berikut: Kaedah untuk mendayakan pertanyaan perlahan

Ubah suai fail konfigurasi

Ubah suai fail konfigurasi my.ini dan tambahkan parameter berikut dalam bahagian [mysqld]

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

Anda perlu memulakan semula MySQL untuk berkuat kuasa, arahannya ialah service mysqld restart

Tetapkan pembolehubah global

Saya melaksanakan 2 ayat berikut pada baris arahan untuk membuka log pertanyaan perlahan, tetapkan tamat masa kepada 0.001s dan rekod log Pergi ke fail dan jadual mysql.slow_log

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

Jika anda ingin mendapatkan konfigurasi dalam konfigurasi fail akan berkuat kuasa secara kekal, jika tidak, konfigurasi ini akan menjadi tidak sah selepas pangkalan data dimulakan semula

Analisis log pertanyaan perlahan

Kerana log pertanyaan lambat mysql adalah bersamaan dengan akaun yang sedang berjalan dan tidak mempunyai fungsi statistik ringkasan, jadi kami perlu menggunakan beberapa alat untuk menganalisisnya

mysqldumpslow

mysql mempunyai alat mysqldumpslow terbina dalam Mari bantu kami menganalisis log pertanyaan perlahan.

Penggunaan Biasa

# 取出使用最多的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 ialah alat yang paling saya gunakan, fungsinya Ia sangat berkuasa dan boleh menganalisis binlog, log umum, slowlog, dan juga boleh dianalisis melalui show processlist atau data protokol MySQL yang ditangkap melalui tcpdump. Hanya muat turun dan benarkan ia untuk menjalankan skrip Perl pt-query-digest

Muat turun dan memperkasakan

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

Pengenalan penggunaan

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

PILIHAN Biasa

• --create-review-table Apabila menggunakan parameter --review untuk mengeluarkan hasil analisis ke jadual, ia akan dibuat secara automatik jika tiada jadual.

• --create-history-table Apabila menggunakan parameter --history untuk mengeluarkan hasil analisis ke jadual, ia akan dibuat secara automatik jika tiada jadual.

• --tapis Padankan dan tapis pertanyaan perlahan input mengikut rentetan yang ditentukan dan kemudian analisanya

• --had had hasil output Peratusan atau kuantiti, nilai lalai ialah 20, iaitu 20 pernyataan yang paling perlahan akan dikeluarkan Jika 50%, maka jumlah masa tindak balas akan diisih dari besar ke kecil, dan output akan dipotong apabila jumlahnya. mencapai 50%.

• --host alamat pelayan mysql

• --pengguna mysql nama pengguna

• -- kata laluan kata laluan pengguna mysql

• --history Simpan hasil analisis ke jadual Hasil analisis adalah lebih terperinci Kali seterusnya anda menggunakan --history, jika pernyataan yang sama wujud dan pertanyaan ialah Jika selang masa berbeza daripada jadual sejarah, ia akan direkodkan dalam jadual data Anda boleh membandingkan perubahan sejarah bagi jenis pertanyaan tertentu dengan menanyakan CHECKSUM yang sama.

• --semak Simpan hasil analisis pada jadual Analisis ini hanya parameter syarat pertanyaan adalah untuk satu rekod, yang agak mudah. Jika analisis pernyataan yang sama berlaku, ia tidak akan direkodkan dalam jadual data pada kali berikutnya --semakan digunakan.

• --jenis keluaran hasil analisis output, nilainya boleh laporan (laporan analisis standard), slowlog (log lambat Mysql), json, json-anon, secara amnya gunakan laporan untuk bacaan yang lebih mudah .

• --sejak masa untuk memulakan analisis, nilainya ialah rentetan, yang boleh menjadi titik masa tertentu dalam format "yyyy-mm-dd [hh:mm :ss]" , atau ia boleh menjadi nilai masa mudah: s (saat), h (jam), m (minit), d (hari), contohnya, 12j bermakna pengiraan bermula 12 jam yang lalu.

• --sehingga tarikh akhir, digabungkan dengan -kerana boleh menganalisis pertanyaan perlahan dalam tempoh masa tertentu.

DSN Biasa

A Tentukan set aksara
D Tentukan pangkalan data yang disambungkan
P Sambungkan port pangkalan data
S Connection Socket fail
h Sambung ke nama hos pangkalan data
p Kata laluan untuk menyambung ke pangkalan data
t Jadual mana untuk menyimpan data semasa menggunakan --review atau --history
u Sambung ke nama pengguna pangkalan data

DSN dikonfigurasikan dalam bentuk key=value; berbilang DSN digunakan, dipisahkan

Contoh penggunaan

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

Penggunaan praktikal

Menulis prosedur tersimpan untuk mencipta data dalam kelompok

Tiada ujian prestasi dalam kerja sebenar Kami selalunya perlu mengubah kumpulan besar data, dan pemasukan manual adalah mustahil untuk menggunakan prosedur tersimpan

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

Bagaimana untuk menentukan prosedur tersimpan?

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

Contohnya, masukkan 100,000 keping data dengan ID 1-100000

Gunakan Navicat untuk melaksanakan

-- 删除之前定义的
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);

Tiga jenis parameter prosedur tersimpan

用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

执行后的文件如下

# 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左右，果然是事务提交很慢。

Atas ialah kandungan terperinci Bagaimana untuk mencari SQL perlahan dalam MySQL dengan cepat. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!