Posted inMySQL

MySQL 8における共通テーブル式（CTE）と再帰CTEを完全攻略：サブクエリの最適化と階層構造データの処理

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

クイックスタート：3分で書く最初のCTE

4〜5階層にもネストされたSUBQUERYを含む300行ものSQLファイルの保守で、バグ調査に苦労した経験は誰にでもあるはずです。共通テーブル式（CTE: Common Table Expression）は、こうした問題を根本から解決します。

本質的にCTEは、WITH句を使用してクエリの実行スコープ内でのみ有効な名前付き一時結果セットを定義する機能です。FROM句の中に巨大なサブクエリを埋め込む代わりに、計算処理ブロック全体を先頭に切り出すことができます。これにより、バックエンド開発で変数に値を代入するかのように、上から下へと流れる自然な可読性を実現できます。

以下は、MySQL 8.0以降ですぐに動作確認できる5件のサンプル注文データです。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    order_date DATE
);

INSERT INTO orders VALUES 
(1, 101, 500, '2026-01-10'),
(2, 102, 1200, '2026-01-12'),
(3, 101, 700, '2026-02-01'),
(4, 103, 300, '2026-02-05'),
(5, 102, 400, '2026-02-15');

-- 累計利用額が1,000ドルを超える顧客を抽出
WITH customer_spending AS (
    SELECT 
        customer_id,
        SUM(amount) AS total_spent,
        COUNT(order_id) AS total_orders
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_spent, total_orders
FROM customer_spending
WHERE total_spent > 1000;

集計ロジックがcustomer_spendingブロック内に整理され、最後のSELECT文では通常の物理テーブルを参照するかのように呼び出すだけで済みます。

標準CTEの技術的本質

1. 基本構文とCTEの連結（チェイニング）

標準構文はWITHから始まり、CTEの識別子名と丸括弧()で囲まれたサブクエリが続きます。

WITH cte_name (col1, col2) AS (
    SELECT col_a, col_b FROM table_name
)
SELECT * FROM cte_name;

CTEの真価は、複数のCTEを連結できる点にあります。カンマで区切ることで複数のCTEを連続して宣言でき、後続のCTEから先行するCTEの結果を直接参照することが可能です。

WITH 
january_orders AS (
    SELECT customer_id, amount 
    FROM orders 
    WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
),
loyal_customers AS (
    SELECT customer_id, SUM(amount) AS jan_total
    FROM january_orders
    GROUP BY customer_id
    HAVING jan_total >= 500
)
SELECT * FROM loyal_customers;

2. CTE・サブクエリ・一時テーブル（Temporary Table）の比較

比較項目 CTE（WITH） 派生テーブル（サブクエリ） 一時テーブル（Temporary Table）
ライフサイクル 単一クエリの実行中のみ 単一クエリの実行中のみ 接続セッション全体で保持
再利用性 同一クエリ内で複数回参照可能 サブクエリを重複して記述する必要あり 異なるクエリ間で何度でも再利用可能
メモリとI/O RAM（Mergeまたはインメモリ一時テーブル） CTEと同様 領域確保とディスクI/Oのオーバーヘッドが発生
デバッグの容易さ 非常に高い（各ブロックを独立してテスト可能） 3階層以上のネストでは困難 中程度

再帰CTE（Recursive CTE）による階層構造データの処理

MySQL 8.0より前は、ディレクトリツリーや親子関係を持つコメントスレッドを探索するために、ストアドプロシージャや複雑な入れ子集合モデル（Nested Sets Model）を採用せざるを得ませんでした。再帰CTE（Recursive CTE）は、数学的な再帰処理によってこの課題をエレガントに解決します。

再帰クエリの構成要素

有効なWITH RECURSIVE構文は、常に以下の3つの要素で構成されます。

  1. アンカーメンバー（Anchor Member / 基底クエリ）: ツリーの第1階層（通常はルートノード）を取得するクエリ。CTE自身は参照しません。
  2. UNION ALL演算子: 親階層と子階層のデータを結合します。
  3. 再帰メンバー（Recursive Member）: CTE自身とJOINして次の階層を取得するクエリブロック。条件を満たすレコードがなくなるまで処理が繰り返されます。

実践課題：6階層の組織ツリー探索

manager_idカラムで親子関係を持たせたサンプル社員テーブルを作成します。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'CEO グエン・ヴァン・A', NULL),
(2, 'CTO チャン・ティ・B', 1),
(3, 'CFO レ・ヴァン・C', 1),
(4, 'Dev Lead ファム・ヴァン・D', 2),
(5, 'Senior Dev ホアン・ティ・E', 4),
(6, 'Junior Dev ダン・ヴァン・F', 5);

要件：CEOからJunior Devまでの管理ツリーを出力し、階層の深さ（level）と階層パス（path）を明確に表示します。

WITH RECURSIVE org_tree AS (
    -- 1. Anchor: 最上位の社員（管理者がNULL）を取得
    SELECT 
        employee_id, 
        name, 
        manager_id, 
        1 AS level,
        CAST(name AS CHAR(255)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 2. Recursive: 次の階層を結合
    SELECT 
        e.employee_id, 
        e.name, 
        e.manager_id, 
        ot.level + 1 AS level,
        CONCAT(ot.path, ' -> ', e.name) AS path
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT employee_id, name, level, path 
FROM org_tree
ORDER BY level, employee_id;

技術的な注意点：アンカー部分でCAST(name AS CHAR(255))を使用することが必須です。これを省略すると、MySQLはpathカラムの長さを最初の行の文字数（CEOの15文字）に固定してしまい、子階層でData truncation: Data too long for columnエラーが発生します。

レポートの欠損日を埋める連番データ生成（Data Generator）

直近30日間の売上グラフを描画する際、注文が1件もない日付はデータから抜け落ちてしまいがちです。再帰CTEを使用すれば、補助テーブルを用意することなく連続した日付シーケンスを生成できます。

WITH RECURSIVE date_range AS (
    SELECT CAST('2026-03-01' AS DATE) AS report_date
    UNION ALL
    SELECT report_date + INTERVAL 1 DAY
    FROM date_range
    WHERE report_date < '2026-03-07'
)
SELECT report_date FROM date_range;

本番環境におけるパフォーマンス最適化の勘所

1. 無限ループ（Infinite Loop）の防止策

循環参照データは再帰処理における悪夢です。例えば「AがBを管理し、BがCを管理しているのに、CがAを参照している」といったケースです。クエリが停止せず無限ループに陥り、CPU使用率が100%に張り付いてしまいます。

MySQLにはcte_max_recursion_depthというシステム変数（デフォルトは1,000回）が用意されています。オプティマイザヒントのSET_VARを活用し、クエリ単位でこの上限を厳格に制御することをお勧めします。

-- 再帰の深さを最大20階層に制限
WITH RECURSIVE org_tree AS (
    SELECT /*+ SET_VAR(cte_max_recursion_depth = 20) */
        employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree;

2. CTEを使ってもクエリが自動的に高速化されるわけではない

CTEはあくまで構文上の糖衣構文（シンタックスシュガー）に過ぎません。適切なインデックス設計の代替にはならない点に留意してください。

MySQLのオプティマイザは、CTEを次の2つの戦略のいずれかで処理します。

  • Merge: CTEのロジックを親クエリに直接展開・統合します。この場合、元のテーブルのインデックススキャンが有効に活用されます。
  • Materialize: CTEの結果をあらかじめ計算し、一時テーブルとしてRAM上（tmp_table_sizeの閾値を超えた場合はディスク上、デフォルト16MB）に実体化します。この一時テーブルにはインデックスが存在しないため、親クエリはフルテーブルスキャン（Full Scan）を強いられることになります。

CTE内にGROUP BY、DISTINCT、UNIONなどが含まれている場合、MySQLは強制的にMaterializeを選択します。元テーブルが100万〜200万行を超える規模になると、深刻なパフォーマンスボトルネックが発生します。

本番デプロイ前のチェックリスト：

  1. 必ずEXPLAIN ANALYZEを実行し、MySQLがMaterializeとMergeのどちらを選択したか確認する。
  2. 再帰処理に使用する外部キーの組み合わせに対して複合インデックスを作成する（例：CREATE INDEX idx_manager ON employees(manager_id, employee_id);）。
  3. 必要に応じてオプティマイザヒントを活用し、明示的に制御する（MySQL 8.0.21以降で利用可能な/*+ MERGE(cte_name) */または/*+ NO_MERGE(cte_name) */）。
Share:
Tags: