Posted inMySQL

MySQLで重複行を削除する3つの手法：初級から上級テクニックまで

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

重複データ：データベースに突然「ゴミ」が増えるとき

データベースを長く扱っていると、各行が一意であるべきテーブルに、全く同じレコードが複数現れるという不可解な状況に必ず遭遇します。このエラーは通常、アプリケーション側のロジックミス、UNIQUE制約の欠如、または数百万行のCSV/Excelファイルのインポート時の不手際によって発生します。

駆け出しの頃、決済システムで肝を冷やす経験をしました。データベース層での重複チェックが漏れていたため、ネットワークエラーによりインサート処理が繰り返され、5,200人の顧客から二重に課金されてしまったのです。その日の朝、私は冷や汗を流しながら、返金のために膨大な不要データを精査しました。この苦い教訓から、私はデータの整合性に執着するようになり、定期的な「ゴミ掃除」のシナリオを常に用意しています。

システムを停止させることなく不要なデータを一掃するための、最も実践的な3つの方法を紹介します。

どの重複削除方法を選ぶべきか？

実際、すべてのケースに最適な唯一の方法はありません。選択は、使用しているMySQLのバージョンと、データテーブルの規模（数千行か数千万行か）によります。

  • 手法1: CTE (共通テーブル式) & ROW_NUMBER(): モダンで読みやすく、非常に明快です。MySQL 8.0以降が必要です。
  • 手法2: 自己結合 (Self-Join): 伝統的ですが非常に強力です。バージョン5.6以降のすべてのバージョンで動作します。
  • 手法3: 一時テーブル (Temporary Table): 「着実かつ堅実」。テーブルロックを長時間避ける必要がある10GB以上の巨大なテーブルに適しています。

技術的なメリットとデメリットの分析

1. Common Table Expressions (CTE)

メリット: コードが綺麗です。実行ボタンを押す前に、削除される予定の行をSELECTで確認できます。

デメリット: MySQL 8.0以降のみ対応。プロジェクトがまだ5.7で動作している場合、この方法は使えません。

2. Self-Join (自己結合)

メリット: バージョンを選びません。比較対象のカラムにインデックスを貼っていれば、処理速度は非常に高速です。

デメリット: 比較演算子を一つ間違える（>の代わりに<を使うなど）だけで、重複レコードではなく元のレコードを誤って削除してしまう可能性があります。

3. Temporary Table (一時テーブル)

メリット: 絶対的な安全性。クリーンなデータを別の場所にコピーしてから上書きするため、システム停止（データベースの競合）のリスクを最小限に抑えられます。

デメリット: ディスク容量を余計に消費し、数千万行ある場合はコピーに時間がかかります。

実践的な導入アドバイス

私の経験に基づくと：

  • テーブルが50万行未満でMySQL 8.0以降を使用している場合：楽をするためにCTEを使いましょう。
  • レガシー（旧バージョン）システムを保守している場合は、Self-Joinを使用してください。
  • テーブルが数十GBある場合や、毎秒数千のリクエストがある場合は、一時テーブルを優先してください。

詳細な実装ガイド

customersテーブルでメールアドレスが重複していると仮定します。最小のidを持つレコードを保持し、「後から作成された」レコードを削除する必要があります。

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ステップ1：「敵」を特定する

まず、いくつのデータグループが重複しているかカウントします：

SELECT email, COUNT(email) 
FROM customers 
GROUP BY email 
HAVING COUNT(email) > 1;

ステップ2：CTEによる処理 (MySQL 8.0+)

各メールアドレスのグループに連番を振ります。最初の行に1を割り当て、その後の重複行には2、3…と割り当てます。

WITH cte AS (
    SELECT id, 
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as row_num
    FROM customers
)
DELETE FROM customers 
WHERE id IN (SELECT id FROM cte WHERE row_num > 1);

ステップ3：自己結合による処理（最も一般的）

ここでのロジックは、customersテーブルをそれ自体と比較することです。メールアドレスは同じでも、元の行よりIDが大きい行を探します。

DELETE c1 FROM customers c1
INNER JOIN customers c2 
ON c1.email = c2.email 
WHERE c1.id > c2.id;

ヒント：最新の顧客を保持したい場合は、c1.id < c2.idに変更するだけです。

ステップ4：一時テーブルを使った「確実な」方法

深夜のシステムメンテナンス時など、ミスが許されない場合によく使われます：

  1. 一意のIDリストを一時テーブルに抽出する：
CREATE TEMPORARY TABLE unique_ids AS
SELECT MIN(id) as id
FROM customers
GROUP BY email;
  1. この「安全な」リストに含まれていないレコードをすべて削除する：
DELETE FROM customers 
WHERE id NOT IN (SELECT id FROM unique_ids);

実行前の「血の教訓」

自分のSQLスキルを過信してはいけません。DELETEコマンドのEnterキーを押す前に、以下のことを忘れないでください：

  1. 常にバックアップを: mysqldumpを使用してテーブルをバックアップしてください。わずか2分で済みますが、万が一の際にキャリアを救ってくれます。
  2. SELECTを実行してみる: DELETEをSELECT *に置き換えて、削除予定のリストが意図通りか確認してください。
  3. インデックスを確認する: emailカラムにインデックスが貼られていることを確認してください。そうしないと、巨大なテーブルの削除クエリでサーバーがハングし、サービスの中断を招く恐れがあります。

これらの共有が、皆さんが自信を持ってデータベースをクリーンアップする助けになれば幸いです。もし奇妙なエラーに遭遇したり、より最適な方法を知っていたりする場合は、ぜひ下のコメント欄で議論しましょう！

Share:
Tags: