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

序文

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

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

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

低速クエリを有効にする

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

1. 設定ファイルを変更する
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