DevDevデブ!!

プログラミングのこととか書きます。多分。。。

第一次重複レコード集約戦争勃発

あけましておめでとうございます。(遅い)

年始早々バッチの結果がアレしてアレでした。

アレになってしまった原因の一端が、あるテーブルに大量に重複レコードがあることだったため、重複レコードの集約にとりかかってました。

問題のテーブルXについて

  • 主キーなし
  • ユニーク制約なし
  • (key1, key2, key3)の組み合わせについて、ユニークでないと業務仕様的に不味い
  • create_time(レコード作成日時), update_time(レコード更新日時)はある
  • MySQL5.6系

といった感じのテーブルです。

select count(*) from (select key1, key2, key3 from x group by key1, key2, key3 having count(*) > 1) t

こんな感じで重複してる組み合わせがいくつあるのか数えたら、700万ほどありまして、上記クエリの結果を0にするのが目標です。

集約方法

重複しているレコードのうち、更新日付が最新のレコードのみを無条件に残せば問題なかったので、以下のクエリでやっつけました。

delete tempx from x tempx left join (select key1, key2, key2, max(update_time) update_time from x group by key1, key2, key3 having count(*) > 1 limit 100000) t on tempx.key1 = t.key1 and tempx.key2 = t.key2 and tempx.key3 = t.key3 where t.key1 is not null and t.key2 is not null and t.key3 is not null and tempx.update_time < t.update_time

途中でlimitを入れてるのは、一度にやると時間がかかりすぎるためで、上記のクエリを複数回実行しました。

重複がなくなったのを確認できたら(key1, key2, key3)の組に対してユニーク制約を追加して完了です。

(ユニーク制約じゃなくて、主キーにしちゃったほうがよかったかも)

(当然ユニーク制約の影響を受ける更新系クエリも修正してます)

感想

年始早々ダルかった。。。

というか主キーなし、ユニーク制約もなしとかないでしょマジで。。。