MySQLでレコードの差異を効率的に取得する忘備録

最近、仕事でデータベース（DB）のレコードデータの差異を抽出する業務を経験しましたので、忘備録として復習していこうと思います。あまりMySQLを触ることがない人間としては、非常に勉強になりました。
改めて、自分自身で調べたことをまとめてみました。

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

最も一般的で効率的な方法の一つは、INNER JOINとLEFT JOINを組み合わせと思いました。

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

この方法は、`table1`にあって`table2`にないレコードを取得します。JOINを使用することで、インデックスを効果的に活用でき、大規模なデータセットでも高速に動作します。

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

MySQL 8.0.31以降では、EXCEPT演算子が導入されました。

SELECT * FROM table1
EXCEPT
SELECT * FROM table2;

シンプルで読みやすいコードですが、パフォーマンスは内部的なJOINの実装に依存します。

3. NOT IN または NOT EXISTS

小規模なデータセットであれば、NOT INやNOT EXISTSも有効な選択肢です。

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

または

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

ただし、大規模なデータセットではパフォーマンスが低下する可能性があるので注意が必要です。

4. UNION ALLとGROUP BY

両方のテーブルの差分を一度に取得したい場合、UNION ALLと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;

この方法は、両方のテーブルから差分のあるレコードを識別できますが、大量のデータを扱う場合はメモリ使用量に注意が必要です。

---

５．テンポラリテーブルの使用

大規模なデータセットを扱う場合、テンポラリテーブルを使用する方法も効果的です。

-- テンポラリテーブルの作成
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;

この方法は、メモリ使用量を抑えつつ、大規模なデータセットでも効率的に差分を取得できます。

６．バッチ処理による差分取得

非常に大規模なデータセットを扱う場合、バッチ処理を使用して差分を取得する方法があります。

-- バッチサイズの設定
SET @batch_size = 10000;
SET @last_id = 0;

-- バッチ処理のループ
REPEAT
    -- 差分の取得
    INSERT INTO diff_results (id, difference)
    SELECT t1.id, 'Only in Table1' AS difference
    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;

    -- 最後に処理したIDの更新
    SET @last_id = (SELECT MAX(id) FROM diff_results);

    -- 処理の続行判定
    SET @rows_affected = ROW_COUNT();

UNTIL @rows_affected < @batch_size END REPEAT;

この方法は、大規模なデータセットを小さなバッチに分割して処理するため、メモリ使用量を抑えつつ効率的に差分を取得できます。

パフォーマンスの最適化とベストプラクティス

• 適切なインデックスを作成する：比較に使用するカラムにインデックスを作成することで、検索速度が大幅に向上します。

• 実行計画を確認する：`EXPLAIN`コマンドを使用して、クエリの実行計画を確認し、ボトルネックを特定しましょう。

• 統計情報を最新に保つ：`ANALYZE TABLE`を定期的に実行して、統計情報を最新の状態に保ちます。

• パーティショニングの活用：大規模なテーブルでは、パーティショニングを使用してデータを分割し、検索性能を向上させることができます。

• クエリキャッシュの活用：頻繁に実行される同じクエリに対しては、クエリキャッシュを活用して応答時間を短縮できます。

まとめ

MySQLでレコードデータの差分を取得する方法は複数ありますが、データ量やテーブル構造、求める結果によって最適な方法が変わります。小規模なデータセットではシンプルな方法で十分ですが、大規模なデータセットではテンポラリテーブルやバッチ処理などの高度な技術が必要になることがあります。

実際の環境でテストを行い、性能測定を行うことで、プロジェクトに最適な方法を見つけることができるでしょう。また、定期的にクエリのパフォーマンスを見直し、必要に応じて最適化を行うことをお勧めします。

---