MySQLで大量のINSERT処理等をする場合、VALUES で長いクエリを作って一気に突っ込むとかなり高速になることはよく知られています。何となく10倍くらいっていう体感だったのですが、計ってみることにしました。
以下と同じスキーマのテーブル foo1, foo2 foo3 を用意します。
CREATE TABLE `foo1` ( `field1` int(10) unsigned NOT NULL DEFAULT '0', `field2` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`field1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
テスト環境は以下のような感じです。
Server version: 5.1.44 MySQL Community Server (GPL) 2.13 GHz Intel Core 2 Duo 2GB Mem DBI 1.609 DBD::mysql 4.012
ベンチマークスクリプト
#!/usr/bin/perl use strict; use warnings; use DBI; use Benchmark qw/cmpthese/; use constant { DSN => 'DBI:mysql:database=bonardb;host=localhost;port=3306', TEST_INSERT_RANGE => [1..100000], TEST_TABLES => [qw/foo1 foo2 foo3/], }; sub connect_db { return DBI->connect(DSN, 'root', ''); } sub cleanup_db { my $dbh = connect_db(); $dbh->prepare('TRUNCATE TABLE ' . $_)->execute() for @{ TEST_TABLES() }; $dbh->disconnect(); } sub insert_normal { my $dbh = connect_db(); my $sth = $dbh->prepare( 'INSERT INTO foo1 VALUES (?,?)'); foreach (@{ TEST_INSERT_RANGE() }) { $sth->execute($_, 'foo'); } $dbh->disconnect(); } sub insert_bulk { my ($unit, $table) = @_; my $dbh = connect_db(); my $sql_prefix = 'INSERT INTO ' . $table . ' VALUES '; my $sql = $sql_prefix; my @value = (); foreach (@{ TEST_INSERT_RANGE() }) { push @value, ($_, 'foo'); $sql .= '(?,?),'; # 指定された件数たまったら INSERT if ((scalar @value) >= ($unit * 2)) { chop($sql); my $sth = $dbh->prepare($sql); $sth->execute(@value); @value = (); $sql = $sql_prefix; } } # 残りがあれば挿入 if (@value) { chop($sql); my $sth = $dbh->prepare($sql); $sth->execute(@value); } $dbh->disconnect(); } cleanup_db(); cmpthese(1, { 'normal' => sub { insert_normal(); }, 'bulk(10)' => sub { insert_bulk(10, 'foo2'); }, 'bulk(100)' => sub { insert_bulk(100, 'foo3'); }, }); # rows check print "--------- rows -------------\n"; my $dbh = connect_db(); foreach my $table (@{ TEST_TABLES() }) { my $sth = $dbh->prepare('SELECT count(field1) FROM ' . $table); $sth->execute(); my $rows = $sth->fetchrow_array(); printf "%10s %5d\n", $table, $rows; }
insert_normal() は普通に一行ずつ入れて、insert_bulk() の方は 10, 100 の両方でまとめて入れています。insert_bulk() の方がコード的にはちょっと混みいった感じになりますね。
too few iteration なのですが、以下のような結果になりました。
s/iter normal bulk(10) bulk(100)
normal 6.00 -- -81% -95%
bulk(10) 1.16 417% -- -76%
bulk(100) 0.280 2043% 314% --
--------- rows -------------
foo1 100000
foo2 100000
foo3 1000001件ずつ入れるのに対して、100件同時に入れる方が 20倍 程度高速なことがわかります。更新が同時であることの優位性と、接続が少なくてすむことの優位性の両方があると思いますが、どちらの影響が大きいのかは調べる必要がありそうです。
みんなが体感的に知っていたことですが、改めて見てみると結構な違いですね。数パーセントの違いなまだしも、20倍違うとなると無視出来ません。同じことをするなら速い方を使わない手は無いです。ただこの方法にはデメリットもあって、書き方が煩雑になるのと（これは工夫出来そうですが）、クエリサイズの上限値に気をつけないと、SQL が途中で切られてエラーになります。
予めデータ長が分かっているような場合にはすごくいいですね。
