MySQL에서 느린 SQL을 빠르게 찾는 방법-MySQL 튜토리얼-php.cn

느린 쿼리 로그 활성화

프로젝트에서 느린 쿼리를 자주 접하게 되면 일반적으로 느린 쿼리 로그를 활성화하고 느린 쿼리 로그를 분석한 다음 느린 SQL을 찾은 다음 explain을 사용하여 분석해야 합니다.

시스템 변수

MySQL 및 느린 쿼리와 관련된 시스템 변수는 다음과 같습니다

Parameters	Meaning
slow_query_log	느린 쿼리 로그 활성화 여부를 켜면 활성화, 끄면 활성화하지 않음 활성화하면 기본값은 OFF입니다.
log_output	로그 출력 위치는 기본적으로 FILE로 설정됩니다. 즉, TABLE로 설정하면 로그가 mysql.show_log 테이블에 기록됩니다.
slow_query_log_file	느린 쿼리 로그 파일의 경로와 이름을 지정합니다.
long_query_time	느린 쿼리 로그는 실행 시간이 이 값을 초과하는 경우에만 기록됩니다. 단위는 10초입니다.

다음 명령문을 실행하여 느린 쿼리 로그가 활성화되었는지 확인합니다. ON은 활성화됨을 의미하고, OFF는 활성화되지 않음을 의미합니다.

show variables like "%slow_query_log%"

로그인 후 복사

MySQL에서 느린 SQL을 빠르게 찾는 방법

내 로그가 활성화되지 않은 것을 볼 수 있습니다. 다음 두 가지 방법으로 느린 쿼리를 활성화할 수 있습니다

구성 파일을 수정하세요

구성 파일 my.ini를 수정하고 [mysqld] 섹션에 다음 매개변수를 추가하세요

[mysqld]
log_output=&#39;FILE,TABLE&#39;
slow_query_log=&#39;ON&#39;
long_query_time=0.001

로그인 후 복사

적용하려면 MySQL을 다시 시작해야 합니다. 명령은 service mysqld restart

전역 변수 설정

입니다. 명령줄에서 다음 2문장을 실행하여 느린 로그 쿼리를 열고, 시간 제한을 0.001초로 설정하고, 로그를 파일과 mysql.slow_log 테이블

set global slow_query_log = on;
set global log_output = &#39;FILE,TABLE&#39;;
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, 일반 로그, 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입니다. 50%일 경우 전체 응답 시간에 따라 큰 것부터 작은 것 순으로 출력하고, 전체가 50%에 도달하면 출력을 차단합니다.
--host mysql 서버 주소
--user mysql 사용자 이름
--password mysql 사용자 비밀번호
--history 분석 결과를 테이블에 저장하면 분석 결과가 더욱 상세해집니다. 다음에 --history를 사용할 때 동일한 문이 존재하고 쿼리의 시간 간격이 기록 테이블과 다른 경우 데이터 테이블에 기록됩니다. 특정 유형의 기록 변경 사항을 비교할 수 있습니다. 동일한 CHECKSUM을 쿼리하여 쿼리합니다.
--review 분석 결과를 테이블에 저장합니다. 이 분석은 쿼리 조건만 매개변수화합니다. 쿼리 유형 중 하나는 비교적 간단합니다. 동일한 구문 분석이 발생하면 다음 --review를 사용할 때 데이터 테이블에 기록되지 않습니다.
--분석 결과 출력 유형, 값은 보고서(표준 분석 보고서), Slowlog(Mysql 느린 로그), json, json-anon일 수 있으며 일반적으로 쉽게 읽을 수 있도록 보고서를 사용합니다.
--since는 분석이 시작된 이후의 시간입니다. 값은 "yyyy-mm-dd [hh:mm:ss]" 형식으로 지정된 시점일 수 있습니다. 값은 단순 시간일 수 있습니다. s(초), h(시간), m(분), d(일). 예를 들어 12h는 통계가 12시간 전에 시작되었음을 의미합니다.
--마감일까지 -since와 결합하면 일정 기간 내에 느린 쿼리를 분석할 수 있습니다.

공통 DSN

A 문자 집합 지정
D 연결할 데이터베이스 지정
P 데이터베이스 포트에 연결
S 소켓 파일에 연결
h 데이터베이스 호스트 이름에 연결
p 연결 비밀번호
t --review 또는 -를 사용하십시오. 기록 중에 데이터를 어떤 테이블에 저장해야 합니까?
u 데이터베이스 사용자 이름을 연결합니다.

DSN은 키=값 형식으로 구성됩니다. 별도의

사용예

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

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

# 分析指定范围内的查询
pt-query-digest slow.log --since &#39;2020-06-20 00:00:00&#39; --until &#39;2020-06-25 00:00:00&#39;

# 把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 &#39;用户id&#39;,
  `gid` int(11) NOT NULL COMMENT &#39;客服组id&#39;,
  `name` varchar(25) NOT NULL COMMENT &#39;客服名字&#39;
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=&#39;客户信息表&#39;;

로그인 후 복사

저장 프로시저를 어떻게 정의하나요?

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

로그인 후 복사

예를 들어 ID가 1~100000인 데이터 100,000개를 삽입합니다.

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);

로그인 후 복사

3가지 매개변수 유형의 저장 프로시저

매개변수 유형	반환 여부	함수
IN	No	저장 프로시저에 매개 변수를 입력합니다. 매개 변수의 값은 저장 프로시저 중에 수정되어 반환될 수 없습니다.
OUT	Yes	저장 프로시저에서 계산한 결과를 이 매개 변수에 넣습니다. 호출자는 반환 값을 얻을 수 있습니다
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을 빠르게 찾는 방법