2026年5月27日

MySQLのCTE（共通テーブル式）を使ったクエリ最適化

データベースサーバー

ステップ1: CTEの基本的な使い方

CTE（共通テーブル式）は、一時的に複雑なSQLクエリを簡潔に表現するための機能です。MySQL 8.0からは本格的にサポートされており、データベースのパフォーマンスとメンテナンス性を向上させることができます。

# CTEを使用して、従業員情報とその上司情報を取得する例
WITH EmpHierarchy AS (
    SELECT employee_id, manager_id, name FROM employees WHERE department = 'IT'
)
SELECT e1.employee_id, e2.name AS manager_name FROM EmpHierarchy e1 JOIN employees e2 ON e1.manager_id = e2.employee_id;

ステップ2: 再帰的なCTEの使用

再帰的なCTEは、階層構造を扱う際に特に有用です。例えば、従業員とその下の全従業員を取得する場合など。

# 部門内の全ての従業員とその直属上司を表示
WITH RECURSIVE EmpHierarchy AS (
    SELECT employee_id, manager_id, name FROM employees WHERE department = 'IT'
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name 
    FROM employees e JOIN EmpHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmpHierarchy;

ステップ3: CTEとJOINの組み合わせ

CTEはJOINなどの複数のテーブル操作を簡略化します。例えば、商品情報を基にした在庫状況を取得する場合。

# 商品情報と在庫情報を結合し、在庫が0以下である商品を抽出
WITH Inventory AS (
    SELECT product_id, stock_quantity FROM inventory_table WHERE warehouse = 'main'
)
SELECT p.product_name, i.stock_quantity 
FROM products p JOIN Inventory i ON p.id = i.product_id 
WHERE i.stock_quantity <= 0;

ステップ4: CTEの実践とトラブルシュート

CTEはクエリの可読性を向上させますが、誤った使用ではパフォーマンスの低下も招くことがあります。例えば、不要な計算を行ったり、大規模なデータセットに対してCTEを使用するなど。

# テストケース: 重複した商品情報を削除する際の誤ったCTE使用例
WITH DuplicateCheck AS (
    SELECT id, product_name FROM products WHERE product_name = 'iPhone'
)
SELECT * FROM DuplicateCheck;

注意事項

CTEはパフォーマンスに影響を与えるため、必要性を慎重に検討してから使用すること。
大規模なデータセットではCTEの効果が薄れることも考えられるので、実行計画を確認する。
再帰的なCTEの場合、深さ制御が必要となる。

まとめ

1. CTEの基本的な使い方: CTEはSQLクエリを簡潔に書き換えるための強力なツールです。特に複雑なJOINや階層構造の処理で効果的。

2. 再帰的なCTE: 階段的な関係性を持つデータを扱う際に有用。再帰的なCTEを使用することで、コードが簡潔になり、メンテナンス性も向上します。

3. CTEとJOINの組み合わせ: 多くのテーブル操作を行う場合に効果的です。CTEは複雑なJOINを一括で処理でき、可読性も高まります。

4. 実践とトラブルシュート: CTEの使用には注意が必要で、パフォーマンスの影響や適切な使用方法を理解することが重要です。

5. セキュリティ上の注意: CTEを使用する際は、権限管理に気をつけ、必要最小限の情報だけを操作すること。

Post Views: 14