このタグの記事
Mysql
MySQLを使ったデータの洗い替え
案件によって稀に大量のデータを入れ替える必要がある場合どうしていますか？ 例えば、ECサイトで何かしらのセールで日付が変わると同時に大量の商品の在庫数を更新したり、連携する外部システムから膨大なデータが送られてきて自社のDBに取り込む場合等です。 一般的にこの様な処理を洗い替えといいます。 件数が少ない場合は、UPDATEなり、DELETE & BULK INSERTを使って直接対象のテーブルに反映させたりするかもしれません。同時トランザクション数や件数が少ないうちは気がつかないかもしれませんが、大量に処理させるとデッドロックが起きたりします。そうするとエンドユーザにも影響がでてしまいます。 洗い替え方法には幾通りか方法がありRDB毎に使える方法が違います。今回はMySQL(5.7)でのケースを考えてみます。 ケース: テーブルを入れ替える 一度にテーブル全体を入れ替える方法を考えてみます. 案1: View による参照の切り替え Viewを使えばテーブルの切り替えが簡単にできます。ただしMySQLのViewには制約があるので使用には注意が必要です。特にViewへの更新系クエリはサポートされてないケースもあります。 OR REPLACEを使えば既存のViewを置き換えることができます. CREATE OR REPLACE VIEW v1 AS SELECT * FROM t2; SWAPPING VIEW TABLES | DB Fiddle にプレイグラウンドを用意してみたので試してみてください。 案2: ALTER TABLE ~ RENAME を利用する(非推奨) 次は ALTER TABLE ~ RENAME を使った方法です. /* Bad */ ALTER TABLE t1 RENAME t_; ALTER TABLE t2 RENAME t1; ALTER TABLE t_ RENAME t2; この方法は何の問題もなくできる様に思えますが、RENAME間に別のコネクションからのテーブルの参照が行われる可能性があります。その場合にテーブルが見つからずエラーになってしまいます。 それならばとトランザクションで囲っても実は無駄です。MySQLはALTER TABLE等のDDLには 暗黙的なコミット(Implicit Commit) が発生する仕組みがあるので何も解決しません。 /* Bad */ START TRANSACTION; ALTER TABLE t1 RENAME t_; /* ここでCommitされてしまう */ ALTER TABLE t2 RENAME t1; ALTER TABLE t_ RENAME t2; COMMIT; 案3: RENAME TABLE を利用する MySQLにはこの様な時に使えるRENAME TABLE があります。RENAME TABLE は１度に複数のテーブルの名称変更ができます。 RENAME TABLE t1 TO t_, t2 TO t1, t_ TO t2; 暗黙的なコミットの確認はできませんが SWAPPING TABLES | DB Fiddle にプレイグラウンドを用意してみたので試してみてください。 ケース: テーブルの一部分だけを入れ替える こちらのケースは、外部システムからデータを受け取り部分的に置き換える様な時に使います。 案1: View による参照の切り替え テーブルを入れ替えると同様にViewが使えます。 この場合は、Viewにはインデックスを仕掛けられないので単純に複数のテーブルをJOINまたはUNIONした物になるのでソート等の実行計画が良くできません. 案2: Partitioning を利用する MySQLには Partitioning がサポートされており、このパーテーション毎にデータの置き換えができます。 ALTER TABLE t1 EXCHANGE PARTITION p00 WITH TABLE t1_p; 仕組みが複雑で更に Partitioning の制約もあります。端的にいうとPKを含むINDEXやUNIQUE INDEX にPartitionのキーを含めないといけません これさえ上手く使いこなせればViewより良い実行計画になります。 こちらも、 SWAPPING PARTIAL TABLES | DB Fiddle にプレイグラウンドを用意してみたので試してみてください。 まとめ いくつか案を出しましたが一長一短があるので銀の弾丸的なのはなく、一番マシな方法を選択する必要があります。 Also see MySQLでWebサービスを止めずに、その裏のバッチ処理で大量にデータを洗い替える。 - Qiita