実行計画キャッシュ の再利用 多段階JOINを含むSQL 中編

はじめに

この記事は「 実行計画キャッシュ の再利用 多段階JOINを含むSQL」シリーズの中編です。前編では、10万件以上のデータを投入するためのテーブル設計とINSERT文を整備しました。中編となる本記事では、実際に使用するクエリのパターンを4つ紹介し、それぞれのEXPLAIN ANALYZEの出力を掲載しながら、MySQLがどのようにクエリを最適化・実行しているかを紐解いていきます。MySQLは8.4.5を利用しています。

---

比較する4つのクエリパターン

今回比較するのは以下の4つのパターンです。

1. パターン1：リテラル固定埋め込み
   user_id = 1234 をSQL文に直接書き込む。
2. パターン2：リテラル可変埋め込み
   1234 の部分を別のidに差し替えて何度も実行。
3. パターン3：PREPARE + EXECUTE（値固定）
   変数は使わず、クエリ文字列内に固定の = 1234 を埋め込んでPREPARE。
4. パターン4：SET + PREPARE + EXECUTE（値可変）
   SET @user_id = 1234; で変数を定義し、クエリ内では = @user_id を使用。

クエリはいずれも以下のようなJOIN構造と集計を含みます：

SELECT
    COUNT(DISTINCT o.id) AS order_count,
    COUNT(DISTINCT p.id) AS product_count,
    SUM(p.price * oi.quantity) AS total_amount
FROM
    order_items AS oi
INNER JOIN orders AS o
    ON oi.order_id = o.id
    AND o.user_id = 1234
INNER JOIN products AS p
    ON oi.product_id = p.id
INNER JOIN categories AS c
    ON p.category_id = c.id
INNER JOIN suppliers AS s
    ON p.supplier_id = s.id;

---

パターンごとの EXPLAIN ANALYZE

以下に、それぞれのクエリに対する EXPLAIN ANALYZE の抜粋結果を掲載します。なお、これらの EXPLAIN ANALYZE はすべてクエリの初回実行時に行ったものであるため、MySQLによる実行計画キャッシュの再利用は行われていません。したがって、実行時間は構文の差や初回解析・最適化コストの影響を受けている点に注意が必要です。EXPLAIN ANALYZEより各クエリの実行計画に大きな差がないことが確認できますね。

パターン1：リテラル固定

-> Group aggregate: count(distinct o.id), ... (actual time=1958..1958 rows=1 loops=1)
    -> Nested loop inner join (actual time=388..1956 rows=42 loops=1)
        -> Filter: (o.user_id = 1234) (actual time=0.00317..0.00317 rows=0 loops=300000)

最も単純なパターンであり、実行時間はやや長め。

パターン2：リテラル可変

-> Group aggregate: count(distinct o.id), ... (actual time=728..728 rows=1 loops=1)
    -> Nested loop inner join (actual time=8.86..727 rows=42 loops=1)
        -> Filter: (o.user_id = 1234) (actual time=0.00194..0.00194 rows=0 loops=300000)

リテラルが変更されても構文が類似しているため、パースや最適化の結果にあまり差が見られない。

パターン3：PREPARE + EXECUTE

-> Group aggregate: count(distinct o.id), ... (actual time=714..714 rows=1 loops=1)
    -> Nested loop inner join (actual time=3.82..714 rows=42 loops=1)
        -> Filter: (o.user_id = 1234) (actual time=0.00192..0.00192 rows=0 loops=300000)

PREPAREによってパースのコストは削減されるが、EXECUTE時に若干のオーバーヘッドが発生している可能性があります。

パターン4：SET + PREPARE + EXECUTE

-> Group aggregate: count(distinct o.id), ... (actual time=717..717 rows=1 loops=1)
    -> Nested loop inner join (actual time=3.21..717 rows=42 loops=1)
        -> Filter: (o.user_id = 1234) (actual time=0.00193..0.00193 rows=0 loops=300000)

SETによって変数に値を設定し、PREPAREされたクエリでそれを参照。パターン3とほぼ同等の実行計画と時間。

---

次回予告

後編ではこれらのパターンを用いて 1000回ずつ実行したパフォーマンステスト の結果と、その考察をまとめていきます。