CSVにあってテーブルにないデータを削除したい。

Question

前提・実現したいこと

MYSQL5.7にて、連携されるCSVデータをマスタテーブルに取り込みしているのですが、
CSVの中にないデータを削除をしたいと思っています。

連携されてくるCSV側での対応ができないため、取込側で何とかせざるを得ない状況ですが、
方法として2案考えており、速度的な点を重視して対応ができればと考えています。
どちらが方法がよいのか、また別案があれば教えてください。

なお、データ件数は100万件ぐらいで、テーブルデータ件数もほぼニアリーイコールです。
DB形式はInnoDBになります。

案１：
一度、truncate tableをしてから、Load Dataですべて取り込む。
→ 試したところ削除はすぐだが、取込はIndexの貼り直しがあるので、5分程度かかった。

案２：CSVをワークテーブルに入れて、ワークとマスタを比較して差分データを削除する。
→ まだ試してませんが、結局ワークに入れる際、削除から取込処理が入るため、
全体的に速度が落ちる。マスタ側への影響が少ないだけがメリット。

最初は、LOAD DATA INFILE 'test.CSV' REPLACE INTO TABLE test でやってましたが、
存在しないものは消えないというのがわかって、変更しているところです。

Answer 1

何をもってデータが合致するか次第
削除だけが目的なら後者の別テーブルにロード後
突合して合致しないデータだけ削除するのが楽
削除だけでなく挿入や更新もあるなら案1が確実

Comments

[FujiCandy]

プライマリキーが一致した場合に限り、更新処理をしてます。
そのためReplaceでなんとかなってましたが、
挿入更新以外に、削除がでてきたため、質問させていただきました。
1日1回、日々実行しているのですが、DBへのパフォーマンスへの影響はどうなんでしょうか。徐々に速度が落ちていくなどの影響も知りたいです。

[yambejp]

> 挿入更新以外に、削除がでてきた

つまりすべての処理が必要だということならtruncateが確実です
truncateは一種の初期化ですから速度が落ちることはないでしょう
気になるならテーブルの最適化は定期的におこなってください

[FujiCandy]

ありがとうございます！
サイクルテストして影響が大きそうであれば、定期メンテナンスを検討いたします。

Answer 2

CSVに無いデータをマスタから削除したいということですよね？
普通は、マスタは物理削除ではなく、論理削除するものですが、大丈夫なんでしょうか？

CSVのデータに含まれているかどうかを判別する方法ですが、OAD DATA INFILEのSET 句を使用してはどうでしょうか。
CSV連携用の項目を用意（例えば論理削除のフラグ)して連携前にクリアしておき、SET句で更新する。

そうすれば、処理後にその項目の値で、CSVのデータ有無を判断できます。

Comments

[FujiCandy]

連携元が論理削除ではなく、物理削除であり、さらに削除されたという履歴情報すらない状態でしたので、
相手側の仕様的な問題なってしまいます。
相手側の改修が見込めないため、今回のような対応になっています。

[sazi]

そうですか。
そのマスターを関係的に参照しているものも同時に削除なら話は分かりますが、そうでないなら、破綻してしまいますので、頭の片隅にでも留めて置かれた方が良いかと思います。