SQL クエリの詳細な実行時間を取得する – Dev.ウェブ

MySQL または MariaDB データベースを操作する場合、SQL クエリのパフォーマンスを理解することが重要です。クエリの実行時間は、アプリケーションの全体的な効率に大きな影響を与える可能性があります。幸いなことに、MySQL と MariaDB には、各クエリの実行時間の詳細な概要を取得できるプロファイリングと呼ばれる機能が提供されています。

MySQL/MariaDB のプロファイリングは、セッションで明示的に有効にする必要がある機能です。有効にすると、サーバーは実行されたクエリの実行時間の追跡を開始します。

プロファイリングを有効にするには、MySQL/MariaDB セッションで次の SQL コマンドを実行するだけです。

SET profiling = 1;

このコマンドは、有効になった後に実行されるすべてのクエリのプロファイリング情報のキャプチャを開始するようにサーバーに指示します。

プロファイリングを有効にすると、SQL クエリを通常どおり実行できるようになります。 MySQL は各クエリの実行時間を記録します。

実行されたクエリの結果を表示するには、次のコマンドを使用できます。

SHOW PROFILES;

このコマンドは、アクティブなセッションで実行されたすべてのクエリのリストとその実行時間を返します。各クエリは、一意の識別子と合計実行時間 (秒単位) に関連付けられます。

結果がどのようになるかの例を次に示します。

Query_ID | Duration | Query
---------|----------|----------------------------------------------
       1 | 0.000745 | SELECT * FROM users;
       2 | 0.003567 | SELECT name, age FROM users WHERE age > 25;
       3 | 0.000203 | UPDATE users SET age = 30 WHERE id = 5;

特定のクエリをさらに詳細に確認するには、次のコマンドを使用できます。

SHOW PROFILE FOR QUERY [Query_ID];

交換する [Query_ID] 詳細に分析したいリクエストの識別子で指定します。このコマンドは、クエリ実行のすべてのステップのリストと、各ステップに費やされた時間を返します。出力例を次に示します。

Status                       | Duration
-----------------------------|----------
Starting                     | 0.000045
Checking query cache         | 0.000010
Opening tables               | 0.000030
System lock                  | 0.000005
Optimizing                   | 0.000008
Statistics                   | 0.000015
Preparing                    | 0.000012
Executing                    | 0.000002
Sending data                 | 0.000545
End                          | 0.000004
Query end                    | 0.000003
Closing tables               | 0.000007
Freeing items                | 0.000015
Cleaning up                  | 0.000014

このテーブルの各行はクエリ実行のステップに対応し、[期間]列には各ステップにかかった時間が表示されます。

クエリの分析が完了したら、不必要なパフォーマンスのオーバーヘッドを避けるためにプロファイリングを無効にすることをお勧めします。これを行うには、次のコマンドを実行するだけです。

SET profiling = 0;

MySQL/MariaDB でプロファイリングを使用する場合、プロファイル履歴をリセットして古いデータを削除し、現在分析しているクエリに関連する情報のみを保持すると便利です。これは、最適化後のパフォーマンスを正確に測定したい場合に特に重要です。

プロファイリング履歴をリセットするには、次のコマンドを使用できます。

SET @@profiling = 0;
SET @@profiling_history_size = 0;
SET @@profiling_history_size = 100; 
SET @@profiling = 1;

例えば、 SET profiling_history_size = 10; 最新のプロファイルされたクエリ 10 件が保持されます。