MySQL の遅いクエリを見つける方法

序文

誰もが日常業務で SQL の最適化を経験したことがあると思います。したがって、最適化の前に、遅い SQL を見つけて分析する必要があります。この記事では、遅いクエリを見つける方法について説明します。

スロークエリログは、実行に指定された時間よりも長い時間がかかる SQL ステートメントを記録する MySQL の組み込み機能です。

以下は、遅いクエリに関連するパラメータです。興味があればご覧ください。

パラメータ	意味
ログ出力	ログ出力場所。デフォルトは FILE で、ファイルに保存されます。TABLE に設定すると、ログは mysql.show_log テーブルに記録されます。複数の形式がサポートされています。
遅いクエリログファイル	スロークエリログファイルのパスと名前を指定します。絶対パスを使用して指定できます。デフォルト値は、構成された datadir ディレクトリにある hostname-slow.log です。
長いクエリ時間	実行時間がこの値を超えると、スロークエリログに記録されます。単位は秒です。デフォルト値は 10 です。
検査行数の最小制限	このパラメータより少ない行をスキャンした SQL クエリは、スロークエリログに記録されません。デフォルト値は 0 です。
インデックスを使用しないログクエリ	インデックスを使用しない SQL ステートメントをスロークエリログに記録するかどうか。この構成を有効にすると、long_query_time パラメータは無視されます。デフォルト値は OFF です。
ログスロットルクエリがインデックスを使用しない	1 分あたりに記録されるインデックスなしステートメントの数を設定します。この数を超えると、ステートメントの数と合計所要時間のみが記録されます。デフォルト値は 0 です。
遅い管理ステートメントのログ	ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE、REPAIR TABLE など、実行速度が遅い管理 SQL ステートメントを記録します。デフォルト設定は OFF です。
ログの遅いスレーブステートメント	スレーブで実行された遅いクエリステートメントを記録します。binlog の値が row の場合は無効です。デフォルト値は OFF です。

低速クエリを有効にする

スロークエリを有効にするには2つの方法があります

設定ファイルを変更する
グローバル変数の設定

方法 1 では、構成ファイル my.ini を変更し、[mysqld] セクションに次のパラメータを追加する必要があります。

[mysqld]
log_output='ファイル、テーブル'
slow_query_log='オン'
長いクエリ時間=0.001

その後、MySQLを再起動して有効にする必要があります。コマンドはservice mysqld restartです。

方法2は再起動しなくても有効になりますが、再起動すると設定が無効になります。設定コマンドは以下のとおりです。

グローバル slow_query_log を 'ON' に設定します。
グローバル log_output = 'FILE,TABLE' を設定します。
グローバルlong_query_time = 0.001を設定します。

この方法では、遅いクエリログをファイルと mysql.slow_log テーブルの両方に記録できます。

2番目の方法を使用してスロークエリログを有効にし、フルテーブルクエリステートメントSELECT * FROM userを使用します。

次に、スロークエリログをクエリします: SELECT * FROM mysql.slow_log 、次のレコードが見つかります。

スローログ

これらのうち、start_time は実行時間、user_host はユーザーのホスト名、query_time はクエリに費やされた時間、lock_time はクエリがロックを使用する時間、rows_sent はこのクエリによってクライアントに返されたデータの量、rows_examined はこのステートメントがスキャンした行数、db はデータベース、sql_text はこの SQL、thread_id はこのクエリを実行するスレッド ID です。

このようにして、slow_log テーブルのデータを分析し、SQL を調整することができます。

上記はテーブルを介した分析です。以下はファイルを介した遅いクエリの分析です。

ファイルの保存場所がわからない場合は、 SHOW VARIABLES LIKE '%slow_query_log_file%'を使用してファイルの保存場所を表示できます。スロークエリログファイルを開くと、5 行ごとに遅い SQL が表されていることがわかります。これは表示するのが面倒なので、いくつかのツールを使用して表示できます。

遅いクエリログファイル

mysqldumpslow

MySQL には、スロークエリログファイルの分析に役立つ mysqldumpslow という組み込みツールがあります。このツールを Windows 環境で使用するには、Perl 環境をインストールする必要があります。

コマンドパラメータは -help で確認できます:

mysqldumpslow ヘルプ

たとえば、 mysqldumpslow -st 10 LAPTOP-8817LKVE-slow.logコマンドを使用すると、クエリ時間順にソートされた 10 個の SQL ステートメントを取得できます。

mysqldumpslow 結果

pt-クエリダイジェスト

また、Percona Toolkit 内のツールの一つである pt-query-digest があります。ダウンロードアドレスは https://www.percona.com/downloads/percona-toolkit/LATEST/ です。Windows システムであれば、Perl がインストールされている環境でスクリプトをダウンロードできます: https://raw.githubusercontent.com/percona/percona-toolkit/3.x/bin/pt-query-digest

以下は pt-query-digest の簡単な紹介です。

pt-query-digest は、MySQL のスロークエリを分析するためのサードパーティツールです。binlog、一般ログ、スローログを分析できます。また、showprocesslist または tcpdump によってキャプチャされた MySQL プロトコルデータを分析することもできます。分析結果はファイルに出力できます。分析プロセスは、まずクエリステートメントの条件をパラメーター化し、次にパラメーター化されたクエリをグループ化してカウントします。各クエリの実行時間、回数、割合などがカウントされます。分析結果を使用して、問題を見つけて最適化することができます。

ご興味がありましたら、ぜひダウンロードして実際に使ってみてください。pt-query-digest ツールについては、次回以降の記事で詳しく紹介していきます。

プロセスリストを表示

スロークエリがまだ実行中であるにもかかわらず、スロークエリログにスローSQLが見つからないという状況もあります。この場合、show processlistコマンドを使用してスロークエリを見つけることができます。このコマンドは、実行中のスレッドを表示できます。実行結果は下の図のようになります。時間の大きさに基づいてスロークエリかどうかを判断できます。

プロセスリストを表示

要約する

この記事では、主に遅いクエリを見つける方法について説明し、mysqldumpslow および pt-query-digest ツールについて簡単に紹介します。explain、show profile、trace などの一般的な方法についても後ほど説明します。

遅いクエリを見つけたり、SQL を最適化したりするときに、どのような方法を使用しますか?

これで、MySQL のスロークエリを見つける方法に関するこの記事は終了です。MySQL のスロークエリを見つける方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

参照ドキュメント

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

以下もご興味があるかもしれません: