第一次重複レコード集約戦争勃発
あけましておめでとうございます。(遅い)
年始早々バッチの結果がアレしてアレでした。
アレになってしまった原因の一端が、あるテーブルに大量に重複レコードがあることだったため、重複レコードの集約にとりかかってました。
問題のテーブル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)の組に対してユニーク制約を追加して完了です。
(ユニーク制約じゃなくて、主キーにしちゃったほうがよかったかも)
(当然ユニーク制約の影響を受ける更新系クエリも修正してます)
感想
年始早々ダルかった。。。
というか主キーなし、ユニーク制約もなしとかないでしょマジで。。。