金利0無利息キャッシング – キャッシングできます

 | 

2009-07-29

MySQLにおけるbulk insert と bulk update

00:10 | MySQLにおけるbulk insert と bulk update - 金利0無利息キャッシング – キャッシングできます を含むブックマーク はてなブックマーク - MySQLにおけるbulk insert と bulk update - 金利0無利息キャッシング – キャッシングできます

SQL::Abstractが複数行INSERTや複数行UPDATEのSQL文を生成してくれないので適当なのをでっち上げた。

http://gist.github.com/158203

perlのO/RマッパーであるData::ObjectDriverはbulk_insertというメソッドをサポートしていて、これは複数行を一度に挿入するものだ。

複数行を一度に挿入や更新すると、

  • 1. 通信が一回で済むことによる高速化
  • 2. インデックスの更新コストが減ることによる高速化

が見込める。10件とかあるいは100件1000件とか同時に更新したい場合は結構顕著な差になるだろう。

条件を指定して複数行をまとめて同じ値に更新するのは、こういうので出来る。

UPDATE table SET key = value WHERE status = 0;

複数の行の複数の列を同時に、ばらばらの値に変更したい場合はどうすると良いか。

複数行を同時にupdateする

存在してない場合はINSERTされてしまう可能性があるが、まあそれでもいいやという人向けに、MySQLではON DUPLICATE KEY UPDATEかREPLACEが使える。

http://dev.mysql.com/doc/refman/5.1/ja/insert-on-duplicate.html

http://dev.mysql.com/doc/refman/5.1/ja/replace.html

replaceの方が多分遅い、挿入する前に削除なので。

この構文を使って、複数のINSERTとUPDATEを同時に行う、というのを考えたのだが、変更されていないカラムについてまでデータを送る必要が出てきてしまうので、場合によっては遅くなるかもしれない。例えば、Aはcol1,col2,col3が更新されてBはcol2のみ更新、Cはcol3のみ更新、というようなケースでも、更新されたカラム数が最大である行Aに合わせてSQL文を生成しないといけない。新しくDを挿入col1..10まであるとすると、更新されていないcol4..10まで送らないといけない。つまり、まとめて実行したいクエリの、対象となる行で変更が発生する全てのカラムについて送らないといけない。

ちなみにこのコード( http://gist.github.com/158203 )は、そういうケースには配慮してない。一番最初のハッシュを元に変更すべきカラムの一覧を作る。行ごとに更新したいカラムが食い違っていた場合、更新したかったカラムが更新されなかったりとか、更新すべきでないカラムをundefにしてしまう可能性があるのでエラーを吐くべきだろう。

こんな感じで動く

repl> OreOre::SQLMaker->update_multi("table", [{a => 1,b=>2}, {a=>3,b=>4}])
(
  "INSERT  INTO table (a,b) VALUES (?,?),(?,?) \nON DUPLICATE KEY UPDATE a=VALUES(a),b=VALUES(b)",
  1,
  2,
  3,
  4,
)
トラックバック - http://subtech.g.hatena.ne.jp/mala/20090729
 |