MySQLのおまとめINSERTはどれくらい速いか

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 100000

1件ずつ入れるのに対して、100件同時に入れる方が 20倍 程度高速なことがわかります。更新が同時であることの優位性と、接続が少なくてすむことの優位性の両方があると思いますが、どちらの影響が大きいのかは調べる必要がありそうです。

みんなが体感的に知っていたことですが、改めて見てみると結構な違いですね。数パーセントの違いなまだしも、20倍違うとなると無視出来ません。同じことをするなら速い方を使わない手は無いです。ただこの方法にはデメリットもあって、書き方が煩雑になるのと（これは工夫出来そうですが）、クエリサイズの上限値に気をつけないと、SQL が途中で切られてエラーになります。

予めデータ長が分かっているような場合にはすごくいいですね。