前置き
最近、とある製品のテーブルの200万近いレコードに対して、部品コードのカラムをを新しい部品コードのカラムに更新する機会があった。
ここまで大きなレコードを操作するのは初めての機会だったので私感含め、記しておく。
問題にぶち当たる
業務内容としては33000件程度の旧部品コードと新部品コードのcsvファイルから対応する新部品コードを取り出し、バッチのphpファイルを動かしDBを更新する、難しくない内容だった。
当初、バルクアップデートの概念を知らなかった私は普通にUPDATE文をループ内で実行した。
しかし、処理が止まってしまう。
原因を半日ぐらい探った結果、UPDATE文が1億回以上実行されるプログラムになっていた事に気づいた。(ヒットする件数が5000件以上あった)
対処した
先輩からバルクアップデートやってみたらと言われ調べてみる。
ざっくりこんな感じ。
カラム名は架空です。
bulk_update.sql
UPDATE 'table_name'
SET parts_name=ELT(FIELD(parts_name,$proto_name_list),$change_name_list)
WHERE parts_name IN($proto_name_list)
結果、2時間程度でテーブルの更新を実現することができました。
構造の説明をします。
ELTは第一引数に数値、第二引数以降に任意の要素を取り、第一引数と一致する第二引数以降の要素を返します。
FIELDは第一引数に検索要素、第2引数以降に被検索要素を取り、第一引数と一致する第二引数以降のキーを返します。
この2つを組みわせることで旧部品コードに対応する新部品コードへの更新を実現しています。
感想
処理がDBを触る回数の意識は改めて大事だと感じた。
また上のSQLのIN文にも40000件手前ぐらいにrange_optimizer_max_memsizeの制限があり、それを超えると全件検索になり遅くなるなど調べていていろいろな情報が出てきて面白かった。
今年はもう少しDBについて詳しくなりたいと思う。
参考書籍及びURL