MySQL で遅い SQL をすばやく見つける方法

スロー クエリ ログを有効にする

プロジェクトではスロー クエリが頻繁に発生します。スロー クエリが発生した場合は、通常、スロー クエリ ログを有効にし、スロー クエリ ログを分析して遅い SQL を見つける必要があります。次に、explain を使用して分析します。

システム変数

MySQL とスロー クエリに関連するシステム変数は次のとおりです。

##long_query_time実行時間はこの値を超える必要があります。スロー クエリ ログに記録します。単位は秒、デフォルトは 10

次のステートメントを実行して、スロー クエリ ログが有効かどうかを確認します。ON は有効であることを意味し、OFF は有効でないことを意味します。

show variables like "%slow_query_log%"

私のものは有効になっていないことがわかります。スロークエリを有効にする方法

設定ファイルを変更する

設定ファイル my.ini を変更し、[mysqld] 段落に次のパラメータを追加します

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

MySQL を再起動する必要があります。有効になります。コマンドは、service mysqld restart

Set global variables

で次の 2 つの文を実行します。コマンド ラインでスロー クエリ ログを開き、タイムアウトを 0.001 秒に設定し、ログを記録します。ファイルと 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 ツールが組み込まれています。スロー クエリ ログの分析にご協力ください。

一般的な使用法

# 取出使用最多的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]

共通オプション

• --create-review-table --review パラメータを使用して分析結果をテーブルに出力する場合、テーブルが存在しない場合は自動的に作成されます。

• --create-history-table --history パラメータを使用して分析結果をテーブルに出力する場合、テーブルが存在しない場合は自動的に作成されます。

• --filter 指定された文字列に従って入力スロークエリを照合およびフィルタリングし、それを分析します

• --limit 出力結果を制限しますパーセンテージまたは数量のデフォルト値は 20、つまり最も遅い 20 個のステートメントが出力されます。50% の場合は、合計応答時間によって大きいものから小さいものにソートされ、時間が経過すると出力が打ち切られます。合計が50%に達します。

• --ホスト mysql サーバー アドレス

• --ユーザー mysql ユーザー名

• --パスワードmysql ユーザーパスワード

• --history 分析結果をテーブルに保存します。分析結果はより詳細になります。次回 --history を使用すると、同じステートメントが存在し、クエリが時間間隔が履歴テーブルの時間間隔と異なる場合は、データ テーブルに記録されます。同じ CHECKSUM をクエリすることで、特定の種類のクエリの履歴変化を比較できます。

• --review 分析結果をテーブルに保存します。この分析はクエリ条件をパラメータ化するだけです。クエリの 1 種類は 1 つのレコードに対するもので、比較的単純です。同じステートメント分析が発生した場合、次回 --review が使用されるときにはデータ テーブルに記録されません。

• --出力分析結果の出力タイプ。値はレポート (標準分析レポート)、slowlog (Mysql スロー ログ)、json、json-anon にすることができます。通常は読みやすいようにレポートを使用します。 。

• --分析を開始する時刻であるため、値は文字列であり、「yyyy-mm-dd [hh:mm」の形式で指定された時点を指定できます。 :ss]" 、または単純な時間値: s (秒)、h (時間)、m (分)、d (日) にすることもできます。たとえば、12h は、統計が 12 時間前に開始されたことを意味します。

• --期限まで、-since と組み合わせると、一定期間内の遅いクエリを分析できます。

一般的に使用される DSN

A 文字セットを指定します

D 接続するデータベースを指定します
P データベース ポートに接続します
S Connect 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);

ストアド プロシージャの 3 つのパラメーター タイプ

パラメータ	意味
slow_query_log	スロー クエリ ログを有効にするかどうか。ON は有効を意味し、OFF は無効を意味します。デフォルトは OFF
log_output	ログの出力場所のデフォルトは FILE で、これはファイルとして保存することを意味します。TABLE に設定すると、ログは mysql.show_log テーブルに記録されます。複数の形式をサポートします
slow_query_log_file	スロー クエリ ログ ファイルのパスと名前を指定します

#パラメータの種類##INNo パラメータをストアド プロシージャに渡します。パラメータの値はストアド プロシージャ中に変更されるため、返すことはできません。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

执行后的文件如下

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

返すかどうか	Function
## です# ストアド プロシージャを変更します。 計算結果はこのパラメータに格納され、呼び出し元は戻り値を取得できます。

以上がMySQL で遅い SQL をすばやく見つける方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。