MySQL実行計画によるパフォーマンスチューニングの実践

こんにちは。エンジニアリング本部プラットフォームエンジニアリングチームの徳富です。この記事は、 Timee Product Advent Calendar 2024 の 20 日目として、EXPLAINを使用した実行計画の見方についてご紹介します。

背景

タイミーでは、会社の成長に伴い、パフォーマンスチューニングが喫緊に求められています。このような課題に対処するため、クエリのパフォーマンスチューニングにはEXPLAINを使用した実行計画の確認が非常に重要です。しかし、実行計画の解釈には社内でばらつきがありました。この問題を解消するために、実行計画の見方を社内でまとめ、共有することにしました。ただし、この情報を社内だけに留めておくのはもったいないと考え、テックブログを通じて広く公開することに決めました。

実行計画の基本的な見方

MySQLのEXPLAIN文は、SQLクエリがどのように実行されるかの詳細を提供します。実行計画には、以下のような重要な情報が含まれています：

項目	説明
id	SELECT識別子で、クエリが複数の部分から構成されている場合（例えば、サブクエリやUNIONが使われている場合）に重要です。同一のidを持つ行は同じSELECT文に属しています。
select_type	クエリのタイプを示します。例えば、`SIMPLE`（単一のSELECT）、`SUBQUERY`（サブクエリ内のSELECT）、`UNION`（UNIONの一部）などがあります。
table	クエリが参照するテーブル名。複数のテーブルが結合されるクエリでは、どのテーブルがどの順番で処理されるかを示します。
partitions	クエリが参照するパーティション。指定されたパーティションを明確にすることで、クエリの実行効率が向上します。
type	データへのアクセス方法のタイプ。`ALL`（フルスキャン）、`index`（インデックススキャン）、`range`（範囲スキャン）などがあります。
possible_keys	このクエリで使用可能なインデックスのリスト。適切なインデックスが選択されるかどうかの手がかりになります。
key	実際に使用されるインデックス。このインデックスがクエリのパフォーマンスに大きく影響します。
key_len	使用されるキーの長さをバイト数で示します。キーの長さは、インデックスを使用する効率に影響します。
ref	インデックスに使用される列や定数。外部キーの結合や、定数との比較で使用されます。
rows	読み込まれる行数の推定値。クエリのコスト評価やパフォーマンスチューニングの際に重要です。
filtered	フィルタ条件によって行がどれだけ絞り込まれるかのパーセンテージ。100%に近いほど、フィルタ条件によるデータの絞り込みが効果的です。
Extra	クエリの実行に関する追加情報。例えば「Using index」はインデックスのみでデータが解決されていることを、また「Using temporary」は一時テーブルを使用していることを示します。

これらの情報を基に、実行計画を読み解き、クエリのパフォーマンスを最適化する方法を理解します。

各項目の重要な部分の説明(type, filtered, Extra)

type

typeはクエリがどのように実行されるかを示すもので、パフォーマンスの観点から重要です。以下は、最も効率的な順に並べたtypeの種類とその説明です。

項目	内容
const	単一の比較によるレコードの検索で、結果が1行だけに限定される場合に使用されます。通常、主キーやユニークキーの等価比較で見られ、非常に高速です。
eq_ref	主キーまたはユニークキーに基づくジョインで一つのレコードだけを指し示す場合に使用されます。各ジョイン段階で1行のみが処理されるため、効率的です。
ref	インデックスを使用して複数の行がマッチする可能性がある検索です。非ユニークインデックスが使われることが多く、キーに基づく絞り込みが行われますが、constやeq_refほどには効率的ではありません。
range	インデックスを利用した範囲検索です。特定の範囲内の値を持つ行を効率的に検索しますが、スキャンする範囲によっては処理が重くなる可能性があります。
index	インデックス全体をスキャンしますが、テーブル自体は読み込まれません。これは特定のケースでは効率的ですが、全エントリの検査が必要な場合はコストが高くなります。
ALL	すべての行をスキャンする必要があるテーブルスキャンです。インデックスがない場合や適切なインデックスが利用されない場合に使用され、パフォーマンスが最も悪いタイプです。避けるべきです。

filtered

filteredの値が100に近いほど、行をフェッチした後に絞り込んだ量が少なかったことを意味します。一方で、この値が 100 ではない場合、インデックスが適切に設定されていない可能性があり、不要な行が多くフェッチされている可能性があります。この場合、クエリやインデックス設計の見直しが必要です。

補足情報:

インデックスが存在しないカラムの場合、MySQLのオプティマイザは統計情報を持たないため、filteredの値は以下のように固定されることが一般的です：
- 等価検索（= value）の場合、filteredは10%と固定されます。
  - もしカラムがenum型の場合、filteredの値は取りうる値の逆数（1/enumの値の総数）に基づいて計算され、それを100倍してパーセンテージで表示します。
- より大きい（> value）の場合、filteredは33.33%と固定されます。
- 範囲検索（BETWEEN start AND end）の場合、filteredは11.11%と固定されます。

これらの固定値は、統計情報がない場合のオプティマイザの仮定に基づいています。したがって、適切なインデックス設計を行い、オプティマイザが正確な統計情報を基に、インデックススキャンなど適切なクエリの実行方法を選択できるようにすることが、パフォーマンスの改善につながります。

Extra

説明: クエリの実行に関する追加情報を提供します。ここに表示される内容は、パフォーマンスのボトルネックを特定するのに役立つことがあります。
パフォーマンスが良い順に並べた内容(若干条件により前後する):
- Using index: インデックスだけを使用してデータを取得し、テーブルへのアクセスを避けます。(カバリングインデックス)これはクエリが効率的であることを示す良い兆候です。
- Using index condition: インデックスコンディションプッシュダウン（ICP）が使用されていることを示します。これはインデックス内でWHERE条件の一部を評価することにより、不要な行の読み込みを減少させ、クエリの全体的な実行時間を短縮します。
- Using where: データを取得した後に追加の絞り込みを行っている状態を示します。この状況は、インデックスがWHERE条件をすべてカバーしていない場合や、適切なインデックス自体が存在しないに発生します。
- Using filesort: MySQLが結果をソートするために一時ファイルを使用します。これはクエリのパフォーマンスに影響を及ぼす可能性がありますが、正しい結果を得るために必要な場合があります。
- Using temporary: クエリ処理のために一時テーブルが使用されます。これはGROUP BYやORDER BYの処理で見られ、大量のデータを扱う際にパフォーマンスに影響を与える可能性があります。
- Full scan on NULL key: ジョインやサブクエリでNULL値を持つキーをフルスキャンしています。このプロセスは非常にコストが高く、パフォーマンスに大きく影響を及ぼします。

チューニングの具体的な例

具体的なSQLのチューニング方法をご紹介します。使用する環境はこちらのGitHubリポジトリで公開されています：

https://github.com/hirosi1900day/tech-blog-for-mysql

レベル１: whereとgroup byを使ったクエリのチューニング

次のSQLクエリは、特定の日付における商品名ごとの総数量を求めるものです。

SELECT product_name, SUM(quantity) as total_quantity
FROM orders
WHERE order_date = '2021-01-02'
GROUP BY product_name

まず、このクエリの実行計画を確認してみましょう。

EXPLAIN SELECT product_name, SUM(quantity) as total_quantity
FROM orders
WHERE order_date = '2021-01-02'
GROUP BY product_name;

実行計画の結果は以下の通りです：

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where; Using temporary |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

実行計画からわかるように、ALL（全スキャン）が使用されており、インデックスが利用されていないことが明らかです。これはデータベースがテーブルの全行をスキャンしているため、データ量が多くなるとパフォーマンスが大きく低下する可能性があります。さらに、Extra列にUsing where; Using temporaryと表示されています。これは、WHERE句での絞り込み後に一時テーブルを使用してGROUP BY処理が行われていることを示しており、効率的ではありません。

チューニングの実施

このクエリのパフォーマンスを向上させるためには、order_dateとproduct_nameに複合インデックスを作成することが効果的です。複合インデックスでは、インデックスの列の順序が重要です。このケースでは、WHERE句でorder_dateを使用してデータを絞り込んでから、GROUP BYでproduct_nameを使うため、order_dateを第一引数に設定するのが適切です。

インデックスを追加します：

CREATE INDEX idx_date_product ON orders(order_date, product_name);

インデックスを追加した後の実行計画は以下のように改善されるはずです：

EXPLAIN SELECT product_name, SUM(quantity) as total_quantity FROM orders WHERE order_date = '2021-01-02' GROUP BY product_name;

結果：

+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_date_product | idx_date_product | 6       | const |    2 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+

この改善により、typeがrefに変わり、クエリはインデックスを使用してより効率的に実行されます。また、Extra情報もクリアされ、一時テーブルを使用することなく処理が行われていることが確認できます。

インデックスの順序の影響

SQLクエリのパフォーマンスを向上させるためには、インデックスの構成とその順序が重要な役割を果たします。ここでは、product_name を第一引数とし、order_date を第二引数とする複合インデックスの影響を考察します。

CREATE INDEX idx_product_date ON orders(product_name, order_date);

この複合インデックスを用いたクエリの実行計画を見てみましょう：

EXPLAIN SELECT product_name, SUM(quantity) as total_quantity
FROM orders
WHERE order_date = '2021-01-02'
GROUP BY product_name;

実行計画は以下のようになります：

+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | index | idx_product_date | idx_product_date | 1028    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+

ここで、type indexとなっているためidx_product_date インデックスがフルスキャンされ、Extra 列に Using where が表示されている点に注目します。これは、order_date での絞り込みがインデックスを最適に活用していないことを示しています。また、Using temporary が表示されないことから、GROUP BY product_name 処理で複合インデックスが効果的に機能し、一時テーブルが不要になっていることがわかります。このことから、複合インデックスの第一引数の product_name だけが利用され、第二引数以降の order_date は単独でインデックスとしての機能を果たしていません。

さらに、同じ条件で product_name のみにインデックスを設定した場合の実行計画を見てみましょう：

CREATE INDEX idx_product_name ON orders(product_name);

EXPLAIN SELECT product_name, SUM(quantity) as total_quantity
FROM orders
WHERE order_date = '2021-01-02'
GROUP BY product_name;

実行計画は以下の通りです：

+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | index | idx_product_name | idx_product_name | 1022    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+

この結果から、product_name のみのインデックスでも同じ実行計画になることがわかります。

つまり、複合インデックスを使用している場合でも、第一引数（product_name）が単独で使用されることがある点に注意が必要です。

レベル2: GROUP BYとHAVING、ORDER BY を使ったクエリ

次のSQLクエリは、商品がどれだけ頻繁に複数購入されるかを調べるためのものです。具体的には、商品名ごとに注文総数と平均購入数量を集計します。

SELECT
    product_name,
    COUNT(*) AS total_orders,
    AVG(quantity) AS average_quantity
FROM
    orders
GROUP BY
    product_name
HAVING
    AVG(quantity) > 1
ORDER BY
    average_quantity DESC;

このクエリの実行計画をEXPLAINを使って確認すると、以下のような結果が得られました。

+----+-------------+--------+------------+------+------------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+--------+------------+------+------------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | idx_date_product | NULL | NULL    | NULL |    1 |   100.00 | Using temporary; Using filesort |
+----+-------------+--------+------------+------+------------------+------+---------+------+------+----------+---------------------------------+

全スキャンが行われており、インデックスが利用されていません。Using temporary; Using filesortは、GROUP BY と ORDER BY によって一時テーブルが使用され、結果がファイルソートされていることを示しています。これによりデータが多い場合、クエリのパフォーマンスが低下する可能性があります。

チューニングの実施

このクエリはproduct_nameでグルーピングした後に quantity のカラムを使ってHAVINGやORDER BYを行うため、product_name とquantity に複合インデックスを追加することでデータそのものにアクセスすることなく、インデックスだけでデータの取得が行えそうです（カバリングインデックス）。これにより、データベースが効率的にデータにアクセスできるようになります。

インデックスを追加するSQLは次の通りです：

CREATE INDEX idx_product_name_quantity ON orders(product_name, quantity);

インデックス追加後、実行計画を再び確認すると、次のように改善されるはずです。

+----+-------------+--------+------------+-------+--------------------------------------------+---------------------------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys                              | key                       | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+--------+------------+-------+--------------------------------------------+---------------------------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | orders | NULL       | index | idx_date_product,idx_product_name_quantity | idx_product_name_quantity | 1027    | NULL |    1 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+--------+------------+-------+--------------------------------------------+---------------------------+---------+------+------+----------+----------------------------------------------+

クエリの最適化は行ったものの、Using temporary; Using filesortがExtra情報から消えない理由は、GROUP BYによるグループ化とORDER BYによるソート処理が原因です。カバリングインデックスが追加されたことによりUsing indexが表示され、インデックスから直接必要なデータを取得しているため、全行スキャンのコストは削減されましたが、集計とソートには依然として追加のリソースが必要です。

SQLクエリでは、GROUP BYでグルーピングした結果をORDER BYでソートする際には、MySQLが内部的にテンポラリーテーブルを作成し、そのテーブルにデータを格納後、ソート処理を行います。このプロセスはメモリやディスクスペースを消費するため、扱うデータ量が増えるとパフォーマンスの低下が発生します。

そのため、データベースでの処理負荷を軽減するために、クエリをさらに絞り込むか、あるいはアプリケーションレベルでデータを取得後に集計やソートを行うアプローチが考慮されるべきです。（その場合アプリケーションサーバーに負荷が発生することになるのでバランスが重要）

レベル3: Joinとサブクエリを使ったクエリ

次のSQLクエリは、各ユーザーが注文した商品の中で、そのユーザーが注文した商品の平均数量を超える数量を持つ商品名を取得します。

SELECT
    users.username,
    orders.product_name,
    orders.quantity
FROM
    users
JOIN
    orders ON users.id = orders.user_id
WHERE
    orders.quantity > (
        SELECT
            AVG(orders.quantity)
        FROM
            orders
        WHERE
            orders.user_id = users.id
    );

このクエリをEXPLAINで実行計画を確認した結果、以下のような内容が得られました。

+----+--------------------+--------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
| id | select_type        | table  | partitions | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra       |
+----+--------------------+--------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
|  1 | PRIMARY            | orders | NULL       | ALL    | user_id       | NULL    | NULL    | NULL                  |    1 |   100.00 | NULL        |
|  1 | PRIMARY            | users  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testdb.orders.user_id |    1 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | orders | NULL       | ref    | user_id       | user_id | 4       | testdb.users.id       |    1 |   100.00 | NULL        |
+----+--------------------+--------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+

ordersテーブルに対してフルスキャンが発生していますが、これはデータ量が少ない場合にインデックスを利用するよりも効率的だと判断されているケースです。特に注目すべきはDEPENDENT SUBQUERYというselect_typeで、これは外部クエリの結果に基づいて行ごとにサブクエリを繰り返し実行することを意味します。

例えば、100人のユーザーがそれぞれ5件の注文を持つ場合、サブクエリは500回実行されるため、効率が低下します。この問題を改善するためには、共通テーブル式（CTE）を使用し、ユーザーごとの平均数量を事前に計算してからメインクエリで利用する方法が有効です。

以下は、CTEを使用したクエリ例です。

WITH UserAverages AS (
    SELECT
        user_id,
        AVG(quantity) AS avg_quantity
    FROM
        orders
    GROUP BY
        user_id
)

SELECT
    u.username,
    o.product_name,
    o.quantity
FROM
    users u
JOIN
    orders o ON u.id = o.user_id
JOIN
    UserAverages ua ON u.id = ua.user_id
WHERE
    o.quantity > ua.avg_quantity;

このクエリの実行計画は以下のようになります。

+----+-------------+------------+------------+--------+---------------+-------------+---------+-----------------------+------+----------+--------------------------+
| id | select_type | table      | partitions | type   | possible_keys | key         | key_len | ref                   | rows | filtered | Extra                    |
+----+-------------+------------+------------+--------+---------------+-------------+---------+-----------------------+------+----------+--------------------------+
|  1 | PRIMARY     | orders     | NULL       | ALL    | user_id       | NULL        | NULL    | NULL                  |    1 |   100.00 | NULL                     |
|  1 | PRIMARY     | <derived2> | NULL       | ref    | <auto_key0>   | <auto_key0> | 4       | testdb.orders.user_id |    2 |    50.00 | Using where; Using index |
|  1 | PRIMARY     | users      | NULL       | eq_ref | PRIMARY       | PRIMARY     | 4       | testdb.orders.user_id |    1 |   100.00 | NULL                     |
|  2 | DERIVED     | orders     | NULL       | index  | user_id       | user_id     | 4       | NULL                  |    1 |   100.00 | NULL                     |
+----+-------------+------------+------------+--------+---------------+-------------+---------+-----------------------+------+----------+--------------------------+

CTEを使用することで、サブクエリの繰り返し実行を防ぎ、一度の計算で済ませるようになりました。ただし、CTEは派生テーブルとして扱われ、実行計画ではDERIVEDと表示されます。これにより、一部のデータがテンポラリーテーブルとして実体化されるため、必ずしも常にサブクエリより高速になるわけではないので注意してください。

DEPENDENT SUBQUERYが実行計画に現れた場合には、データ量が増えるとパフォーマンスが大幅に劣化する可能性があるため、注意が必要です。このような場合には、クエリの設計を見直すことを検討してください。

まとめ

MySQLの実行計画を利用することで、クエリのパフォーマンスを把握し、最適化の方向性を明確にできます。今回は、具体的な例を通じて、インデックスを追加する方法とクエリを変更することでの改善方法を紹介しました。しかし、クエリのチューニングは状況に応じて最適な手法が異なるため、これらの方法以外にも様々なアプローチが考えられます。これからも実際の問題に遭遇した際の解決策をブログで積極的に共有していくことで、より多くの技術者がデータベースのパフォーマンス問題に対処できるよう支援していきたいと思います。

参考

この記事の内容に関連するさらなる情報は、以下の書籍で詳しく学べます。

書籍名: MySQL運用・管理［実践］入門〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ
出版社: 技術評論社
出版日: 2024年5月22日

この書籍は、MySQLの内部構造や動作原理について深く掘り下げており、実際の運用や管理におけるパフォーマンスチューニングの実践的なアプローチが学べます。

Timee Product Team Blog

タイミー開発者ブログ

MySQL実行計画によるパフォーマンスチューニングの実践

背景

実行計画の基本的な見方

各項目の重要な部分の説明(type, filtered, Extra)

type

filtered

Extra

チューニングの具体的な例

レベル１: whereとgroup byを使ったクエリのチューニング

チューニングの実施

インデックスの順序の影響

レベル2: GROUP BYとHAVING、ORDER BY を使ったクエリ

チューニングの実施

レベル3: Joinとサブクエリを使ったクエリ

まとめ

参考