MySQLでレコードの差異を効率的に取得する方法を考える

📖 はじめに

データベースの運用で「テーブル間の差異を抽出する作業」は頻繁に発生します。特に大規模データセットの場合、効率的に差分を取得することが重要です。

この記事では、MySQLで実務に役立つ差分取得手法をわかりやすく整理し、それぞれのメリット・デメリットを比較します。

🛠️ 主な差分取得手法

以下では、よく使われる6つの差分取得手法を紹介します。

1. INNER JOIN と LEFT JOIN の組み合わせ

概要

2つのテーブルの差分を取得する基本的な手法です。LEFT JOINを使用して、片方のテーブルに存在しないレコードを抽出します。

クエリ例

SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

特徴

• 適用シナリオ: 中小規模データでの差分取得。
• 利点: シンプルで可読性が高い。インデックスを利用すればパフォーマンス良好。
• 注意点: 大規模データではJOINのコストが増加。

---

2. EXCEPT 演算子（MySQL 8.0.31以降）

概要

EXCEPT演算子を使用すると、2つのクエリ間の差分を簡潔に表現できます（標準SQLの一部）。

クエリ例

SELECT * FROM table1
EXCEPT
SELECT * FROM table2;

特徴

• 適用シナリオ: MySQL 8.0.31以降。
• 利点: 短いコードで記述可能。
• 注意点: 内部でJOINを使用するため、大規模データでパフォーマンスに注意。

---

3. NOT IN または NOT EXISTS

概要

NOT INやNOT EXISTSを使用する方法は、シンプルでSQLの基本機能を活用します。

クエリ例

-- NOT IN
SELECT * FROM table1
WHERE id NOT IN (SELECT id FROM table2);

-- NOT EXISTS
SELECT * FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.id = t1.id);

特徴

• 適用シナリオ: 小規模データの差分取得。
• 利点: 記述が簡潔で直感的。
• 注意点: サブクエリの結果が大きくなるとパフォーマンスが低下。

---

4. UNION ALLとGROUP BY

概要

UNION ALLで2つのテーブルを結合し、GROUP BYを使用して差分を特定する方法です。

クエリ例

SELECT id, COUNT(*) as count
FROM (
    SELECT id FROM table1
    UNION ALL
    SELECT id FROM table2
) combined
GROUP BY id
HAVING count = 1;

特徴

• 適用シナリオ: 両方のテーブルの差分を一度に取得したい場合。
• 利点: 双方向の差分確認が可能。
• 注意点: データ量が多い場合はメモリ消費に注意。

---

5. テンポラリテーブルの使用

概要

テンポラリテーブルを活用して、一時的な中間データを保存しながら差分を取得します。

クエリ例

CREATE TEMPORARY TABLE temp_diff AS
SELECT id FROM table1
UNION
SELECT id FROM table2;

SELECT t.id, 
       CASE 
           WHEN t1.id IS NULL THEN 'Only in Table2'
           WHEN t2.id IS NULL THEN 'Only in Table1'
       END AS difference
FROM temp_diff t
LEFT JOIN table1 t1 ON t.id = t1.id
LEFT JOIN table2 t2 ON t.id = t2.id
WHERE t1.id IS NULL OR t2.id IS NULL;

DROP TEMPORARY TABLE IF EXISTS temp_diff;

特徴

• 適用シナリオ: 中規模から大規模データの差分管理。
• 利点: 高効率で柔軟な操作が可能。
• 注意点: テンポラリテーブルの寿命に注意。

---

6. バッチ処理

概要

非常に大規模なデータセットでは、バッチ処理を用いて部分的にデータを処理します。

クエリ例

SET @batch_size = 10000;
SET @last_id = 0;

REPEAT
    INSERT INTO diff_results (id, difference)
    SELECT t1.id, 'Only in Table1'
    FROM table1 t1
    LEFT JOIN table2 t2 ON t1.id = t2.id
    WHERE t2.id IS NULL
      AND t1.id > @last_id
    ORDER BY t1.id
    LIMIT @batch_size;

    SET @last_id = (SELECT MAX(id) FROM diff_results);
    SET @rows_affected = ROW_COUNT();
UNTIL @rows_affected < @batch_size END REPEAT;

特徴

• 適用シナリオ: 超大規模データの差分取得。
• 利点: メモリ使用量を抑えつつ確実な処理。
• 注意点: 実装の複雑さが課題。

---

⚡ 差分取得手法の比較

手法	データ規模	特徴	注意点
INNER JOIN + LEFT JOIN	小～中規模	シンプル・高速	大規模データで負荷増大
EXCEPT	小～中規模	簡潔な記述	バージョン依存（8.0.31以降）
NOT IN / NOT EXISTS	小規模	簡単で直感的	サブクエリが多いと遅い
UNION ALL + GROUP BY	中規模	両方向の差分確認が可能	メモリ消費
テンポラリテーブル	中～大規模	効率的で柔軟	一時テーブル管理の注意が必要
バッチ処理	超大規模	メモリ効率的	実装が複雑

---

✍️ まとめ

MySQLで差分を取得する方法は多岐にわたります。データ規模や要件に応じて、適切な手法を選択することが重要です。

1. 小規模データ → NOT INやLEFT JOINを使用。
2. 中規模データ → UNION ALLやテンポラリテーブルが有効。
3. 大規模データ → バッチ処理を検討。

この記事があなたのプロジェクトに役立つヒントとなれば幸いです！ 🚀

---