クエリのパフォーマンスチューニングの第一歩。実行計画や統計情報について入門する

SQL実行の流れ

まずはSQLがどのような流れで実行されるのかを見ていきます。

SQL実行の流れは大まかに捉えると以下のようになります。

パーサ

パーサでは、ユーザーから送信されたクエリを受け取り、その文法的な正確さを検証します。SQLクエリが正しくフォーマットされているか、必要な構文要素が全て含まれているかをチェックし、例えばFROM句で指定されたテーブルが存在するかどうかも確認します。

文法的なエラーがある場合、例えばカンマの欠落や存在しないテーブルの参照など、クエリはエラーとして返されます。

エラーがない場合は、クエリは「抽象構文木」というデータ構造に変換されます。これにより、データベースはクエリをより効率的に解析し、次の処理ステップに進めることができます。

オプティマイザ

SQLクエリがパーサを通過した後、次にクエリの最適化を行うのが「オプティマイザ」です。オプティマイザの主な役割は、クエリをできるだけ効率的に実行するための最適な実行計画を生成することです。

具体的には、クエリが要求するデータの取得方法を決定し、複数の実行計画の中からコスト（リソース使用量、時間等）が最も少ない計画を選択します。

この「実行計画」には、クエリを実行するための具体的な手順が詳述されており、どのテーブルからデータを取得し、どの順序でオペレーションを行うかが含まれています。

カタログマネージャー

最適な実行計画を立てるためには、正確な「統計情報」が不可欠です。この情報は「カタログマネージャー」によって管理され、データベース内の各テーブルの行数や列の分布、インデックスの詳細などを含みます。

統計情報は、クエリの実行コストを正確に見積もるのに必要です。例えば、あるテーブルが10レコードしか持たない場合、フルスキャンで全レコードを確認した方がインデックスを使うよりも早いかもしれません。逆に、データ量が非常に大きくて1億レコードほどある場合は、適切なインデックスがクエリのパフォーマンスを大幅に向上させることができます。

重要なのはオプティマイザが実行計画を作成する際に、実際のデータベースの情報をもとに実行計画を作成するのではなく、統計情報を参考にするということです。

なので統計情報は定期的に更新する必要があり、データの変動が激しい場合には特に注意が必要です。古いまたは不正確な統計情報に基づく実行計画は、パフォーマンスの低下を招く可能性があります。

わかりやすい例えを引用します。

例えば、あなたが最安値で世界一周旅行の計画を立てるとします。 その際に最新のデータではなく、3年前の飛行機代や電車賃、ホテル代を用いて計画を立てたとします。 すると、実査にその計画を実行した際に、「泊まる予定だったホテルがつぶれていた」や「実際は最安値ではなかった」等の事象が発生するでしょう。 この様に、計画を立てる時のデータというのは、最新のものである必要があります。

https://techtionary.jp/2737/より

実行計画を見てみる

MySQLで実行計画を確認するには、EXPLAIN や EXPLAIN ANALYZE コマンドを使用します。これらのコマンドは、SQLクエリの実行計画に関する詳細な情報を提供し、クエリのパフォーマンスを最適化するのに役立ちます。

今回はEXPLAINコマンドについて説明します。

EXPLAINの使用方法

EXPLAIN SELECT * FROM テーブル名 WHERE 条件;

使い方は非常に簡単で実行計画を調べたいクエリの頭にEXPLAINを入れるだけです。

それでは試しに10万レコードが格納されたUserテーブルを見てみましょう。(実験用データの作り方はこちら)

mysql> select * from user limit 10;
+----+---------+--------------------+---------------------+
| id | name    | email              | created_at          |
+----+---------+--------------------+---------------------+
|  1 | User 1  | [e-mail]  | 2024-05-01 14:44:20 |
|  2 | User 2  | [e-mail]  | 2024-05-01 14:44:20 |
|  3 | User 3  | [e-mail]  | 2024-05-01 14:44:20 |
|  4 | User 4  | [e-mail]  | 2024-05-01 14:44:20 |
|  5 | User 5  | [e-mail]  | 2024-05-01 14:44:20 |
|  6 | User 6  | [e-mail]  | 2024-05-01 14:44:20 |
|  7 | User 7  | [e-mail]  | 2024-05-01 14:44:20 |
|  8 | User 8  | [e-mail]  | 2024-05-01 14:44:20 |
|  9 | User 9  | [e-mail]  | 2024-05-01 14:44:20 |
| 10 | User 10 | [e-mail] | 2024-05-01 14:44:20 |
+----+---------+--------------------+---------------------+
10 rows in set (0.00 sec)

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.00 sec)

現段階ではインデックスを貼っていません。

nameをwhere句に指定してクエリを実行してみます。

mysql> select * from user where name = 'User 50000';
+-------+------------+-----------------------+---------------------+
| id    | name       | email                 | created_at          |
+-------+------------+-----------------------+---------------------+
| 50000 | User 50000 | [e-mail] | 2024-05-01 14:45:33 |
+-------+------------+-----------------------+---------------------+
1 row in set (0.02 sec)

実行時間は0.02秒！恐ろしく早い実行結果。俺じゃなきゃ見逃しちゃうね。。

人間にとっては早いですが、実行計画をみると非常に効率の悪いクエリだと判明します。

実行計画を見るためにexplainをつけてselect文を実行してみます。

mysql> explain select * from user where name = 'User 50000';
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99760 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

いろいろ出てきましたね。まずはrowsに注目してください。

rowsのカラムを見てみると99760とあり、where句のレコードを探すのに約10万レコードも読み込まれる見積もりであると意味してます

これは10万レコードをフルスキャンするので効率が悪いクエリです。

実行計画の読み方

実行計画の読み方を簡単に紹介します。

実行計画には以下のような情報が含まれています：

• id: クエリの識別子で、クエリの各部分がどの順番で実行されるかを示します。
• select_type: クエリのタイプを示します（例：SIMPLE、PRIMARY、SUBQUERYなど）。
• table: アクセスされるテーブルの名前。
• partitions: クエリがアクセスするパーティション。
• type: JOINのタイプ（例：ALL、index、range、refなど）。
• possible_keys: 利用可能なインデックス。
• key: 実際に使用されたインデックス。
• key_len: 使用されたインデックスの長さ。
• ref: インデックスを参照するカラムや定数。
• rows: 読み込まれる行の見積もり。
• filtered: フィルタリング後の行のパーセンテージ。
• Extra: 追加情報。

クエリパフォーマンスを簡単に見るうえでは、特に「possible_keys」「key」「rows」に注目すると良いと思います。

先ほどのクエリではインデックスが使用されず、ほぼ全レコードが読み取られる見積もりだとわかります。

ではインデックスを貼ったら実行計画はどう変化するでしょうか。試しにnameカラムにインデックスを貼ってみます。

ALTER TABLE user ADD INDEX idx_name (name);

mysql> select * from user where name = 'User 50000';
+-------+------------+-----------------------+---------------------+
| id    | name       | email                 | created_at          |
+-------+------------+-----------------------+---------------------+
| 50000 | User 50000 | [e-mail] | 2024-05-01 14:45:33 |
+-------+------------+-----------------------+---------------------+
1 row in set (0.00 sec)

インデックス追加後の実行時間は0.00秒。早すぎて計測できてませんね。

続いて実行計画を見てみます。

mysql> explain select * from user where name = 'User 50000';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 203     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

インデックスがきちんと使われており、読み込まれる行の見積もりが1行になっていることが確認できます。

最後に

パフォーマンスの悪いクエリがあったときには、まず実行計画を見てインデックスが使われているのかを確認しましょう。

もし使われているのに遅い場合は、統計情報が古くなっているかもしれません。統計情報は古くなっているとオプティマイザは最適な実行計画を作成できなくなるので、定期的な更新が重要です。

参考

• SQL実践入門
• https://techtionary.jp/2737/
• https://dev.mysql.com/doc/refman/8.0/ja/explain-output.html