Posted inMySQL

MySQL 8 CTE: 階層データ処理と再帰的クエリのプロフェッショナルな解決策

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

旧バージョンのMySQLにおける階層データの課題

親子構造（parent-child）の処理は、MySQL 5.7以前を利用する開発者にとって常に頭の痛い問題でした。このようなデータ構造は、多階層の商品カテゴリー、ネストされたコメントシステム、企業の組織図などで頻繁に見かけられます。

CTEが登場する前、最も一般的な解決策はSelf-join（自己結合）を入れ子にすることでした。データが3階層あれば3回、10階層あれば10回結合する必要があります。この手法はSQL文を数百行の「スパゲッティコード」に変えてしまい、可読性とメンテナンス性を著しく低下させます。多くの開発者は、再帰処理を行うために全データをアプリケーション側（PHPやNode.jsなど）に取得する方法を選びますが、データ量が増えるにつれてレイテンシが200〜500msほど増加する原因となります。

MySQL 8.0の登場は、共通テーブル式（CTE: Common Table Expressions）によってこの状況を一変させました。これは単なる新しい構文ではなく、ツリー構造（Tree structure）を最もスマートに処理するための現代的なアプローチです。

CTEとは？

CTEは、クエリの結果を一時的に保存する変数のようなものだと考えてください。それはSELECT、INSERT、UPDATEといった単一のステートメントの実行中のみ存在します。複雑なサブクエリを書く代わりに、それらを名前付きの仮想テーブルとして切り出し、再利用できるようにします。

CTEは主に2つのタイプに分けられます：

  • 非再帰的CTE (Non-Recursive CTE): サブクエリの代わりとして機能し、コード構造をより明確で理解しやすくします。
  • 再帰的CTE (Recursive CTE): 同一テーブル内のレコードが別のレコードを指し示すような、反復的または階層的なデータを処理するための専用ツールです。

WITH句による基本構文

CTEを開始するには、常にWITHキーワードを使用します。この書き方により、生のデータの取得部分と最終的なロジック処理部分を分離できます。

WITH category_summary AS (
    -- 一時テーブルの定義
    SELECT id, name FROM categories WHERE is_active = 1
)
SELECT * FROM category_summary; -- 上記の一時テーブルから直接クエリを実行

CTEに明確な名前を付けることで、チームの他のメンバーは内部のロジックをすべて読み込まなくても、そのコードの目的を即座に理解できるようになります。

Recursive CTEによる再帰処理

これは、深さが決まっていない階層データを処理するための最も重要なテクニックです。標準的な再帰的CTEは、常に以下の3つの要素で構成されます：

  1. アンカーメンバー (Anchor Member): 企業の最上位層など、開始点となるデータを取得します。
  2. UNION ALL: アンカーの結果と後続の反復結果を結合します。
  3. 再帰メンバー (Recursive Member): 直属の部下（子階層）を見つけるために、CTE自体を逆参照してクエリを実行します。

活用例：組織図の構築

以下のようなシンプルな構造のemployeesテーブルがあると仮定します：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

INSERT INTO employees VALUES 
(1, 'CEO - 佐藤 健一', NULL),
(2, 'ITディレクター - 鈴木 一郎', 1),
(3, '営業部長 - 高橋 花子', 1),
(4, '開発チームリーダー - 伊藤 健二', 2),
(5, 'シニアエンジニア - 渡辺 亮', 4);

各従業員の階層レベルを表示するには、以下のように再帰的CTEを使用します：

WITH RECURSIVE employee_hierarchy AS (
    -- ステップ1: CEOをルートとして取得（レベル1）
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- ステップ2: CTE自体とテーブルを結合して部下を検索
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level;

内部の動作メカニズム：

システムはまずCEO（ID 1）を特定することから始めます。次に、そのIDを使用してmanager_id = 1を持つ従業員（ITディレクターと営業部長）を探します。このプロセスは、最下層に達するまで繰り返し実行されます。

実務におけるパフォーマンスの注意点

本番環境（production）にCTEを導入する際は、システムリソースへの配慮が必要です：

1. インデックスは必須

結合に使用するカラム（manager_idなど）には必ずインデックスを貼る必要があります。100万件のレコードがあるテーブルでインデックスがない場合、各反復がフルテーブルスキャン（Full Table Scan）になってしまいます。これによりCPU使用率が100%に達し、数秒でデータベースがハングアップする可能性があります。

2. 再帰の深さの制御

MySQLはデフォルトで、変数cte_max_recursion_depthを通じて1000回の反復制限を設けています。もしデータに無限ループ（AがBを管理し、BがAを管理する等）の不備があっても、この制限がサーバーを保護します。実際のツリー構造がより深い場合は、この制限を増やすことができます：

SET SESSION cte_max_recursion_depth = 2000;

3. 適切な使い分け

CTEは一時テーブルの初期化のために追加のメモリを消費します。単純なフラットなクエリの場合、通常のSELECT文の方がメモリ管理のオーバーヘッドがないため、5〜10%ほど高速になることがあります。

まとめ

MySQL 8のCTEは単なる新機能ではなく、現代のバックエンドエンジニアにとって必須のスキルです。これにより階層データの課題が根本的に解決され、従来の自己結合に比べてソースコードが格段にクリーンになり、デバッグも容易になります。

プロジェクトに多階層メニューや権限管理システムがある場合は、ぜひ再帰的CTEを試してみてください。将来的なシステムのメンテナンスや拡張において、その効果を実感できるはずです。

Share:
Tags: