2016-04-20

JDBC と MyBatis でバルク・インサート（multi-row vs batch）

MyBatis でバルク・インサート（複数行一括挿入）を実装するのに multi-row insert を使った例を割と良く見かけます。
ですが、特に挿入対象の行数が多い場合は batch insert の方が良い選択肢となります。

この記事では２つの方法を比較して、なぜ batch insert を使うべきなのか説明します。
テストに使ったプロジェクトは GitHub に置いてあります。

Multi-row insert

multi-row insert とは？

multi-row insert は INSERT 文の一種で、１つの INSERT 文で複数行のデータを一括挿入することができます。
Wikipedia によれば SQL-92 標準の一部としてほとんどのデータベース・エンジンで実装されています。

下記は SQL での multi-row insert 記述例です。

insert into person
(id, name, address)
values
(1, 'John', 'Kyoto'),
(2, 'Mike', 'Tokyo'),
(3, 'Paul', 'Sapporo');

JDBC で multi-row insert を実装する

JDBC API では multi-row insert は通常の insert と変わりません。
（行数 * 列数） 個のプレースホルダーを含む単一の INSERT ステートメントということになります。

// Build SQL statement as a string
StringBuilder sql
  = new StringBuilder("insert into person")
    .append(" (id, name, address) values ");
for (int i = 0; i < persons.size(); i++) {
  if (i > 0)
    sql.append(",");
  sql.append("(?, ?, ?)"
}
// Prepare statement
PreparedStatement ps = connection.prepareStatement(sql);
// Set parameters
int k = 0;
for (Person p : persons) {
  ps.setInt(++k, p.getId());
  ps.setString(++k, p.getName());
  ps.setString(++k, p.getAddress());
}
// Execute
ps.executeUpdate();

単一のステートメントとして実行することになるため、多くの場合データベース固有の制限を受けることになります。
例を挙げると

  • MySQL : 送信できるパケットサイズは max_alloweed_packet 以下である必要があります。
  • PostgreSQL : プレースホルダーの数は 32767 以下である必要があります。

挿入対象の行が多い場合はステートメントの文字列が大きくなるため、MyBatis を使わず JDBC のみで実行する場合でも問題が生じることがあります。

MyBatis で multi-row insert を実装する

MyBatis では <foreach /> を使うことで multi-row insert を簡潔に記述することができます。

<insert id="insertPersons">
  insert into person
  (id, name, address)
  values
  <foreach item="p" items="list" separator=",">
    (#{p.id}, #{p.name}, #{p.address})
  </foreach>
</insert>

Mapper メソッドは Person オブジェクトのリストを引数に取ります。

int insertPersons(List<Person> persons);
SqlSession sqlSession = sqlSessionFactory.openSession();
Mapper mapper = sqlSession.getMapper(Mapper.class);
try {
  mapper.insert(persons.get(i));
} finally {
  session.close();
}

内部的には前項の JDBC と同様、多数のプレースホルダーを含む単一の INSERT 文が生成されます。

MyBatis では生成された PreparedStatement をキャッシュする機能がありますが、このステートメントは <foreach /> を使っているためキャッシュすることができません。
結果的に、MyBatis はこのステートメントが呼び出されるたびに 1) foreach 部分を評価して INSERT 文を組み立て、 2) 組み立てた INSERT 文をパースしてプレースホルダーと引数のマッピングを構築する、というステップを踏む必要があります。
ステートメントの文字列が大きく、多くのプレースホルダーを含む場合、これらのステップはコストの高い処理となります。

Batch insert

‘batch update’ は、一括処理を効率よく行うために JDBC 2.0 で追加された機能です。

JDBC で batch insert を実装する

JDBC で batch insert を実装するには java.sql.Statement に用意された addBatch(), executeBatch() メソッドを呼び出します。

int batchSize = 100;
// Build SQL statement as a string
String sql = "insert into person"
  + " (id, name, address) values "
  + " (?, ?, ?);"
// Turn off auto-commit
connection.setAutoCommit(false);
// Prepare statement
PreparedStatement ps = connection.prepareStatement(sql);
for (int i = 0; i < persons.size();) {
  Person p = persons.get(i);
  ps.setInt(1, p.getId());
  ps.setString(2, p.getName());
  ps.setString(3, p.getAddress());
  ps.addBatch();
  i++;
  if (i % batchSize == 0 || i == persons.size()) {
    ps.executeBatch();
  }
}

JDBC で batch insert を実装する際の一般的な注意点は下記の２点です。

  • auto-commit をオフにする。
  • 適切な batch size を設定する。

上記の例では batch size は 100 に設定してあります。
これはつまりクライアントからサーバーへのデータ送信は 100 行ごとに実行されるということです。

MyBatis で batch insert を実装する

MyBatis で batch insert を実装するには、SqlSession を取得する際に ExecutorType.BATCH を指定します。

int batchSize = 100;
SqlSession sqlSession
  = sqlSessionFactory.openSession(ExecutorType.BATCH);
Mapper mapper = sqlSession.getMapper(Mapper.class);
try {
  int size = persons.size();
  for (int i = 0; i < size;) {
    mapper.insert(persons.get(i));
    i++;
    if (i % batchSize == 0 || i == size) {
      sqlSession.flushStatements();
      sqlSession.clearCache();
    }
  }
  sqlSession.commit();
} finally {
  sqlSession.close();
}

Mapper の実装は通常の INSERT と同じです。

int insert(Person person);
<insert id="insert">
  insert into person (id, name, address)
  values (#{id}, #{name}, #{address})
</insert>

テスト

multi-row insert と batch insert の性能を比較するため、簡単な プロジェクト を作成しました。
一応お断りしておきますが、パフォーマンスは様々な要因に影響を受けるので同じコードを実行しても結果は同じにはならないのが普通です。

テスト環境

  • Mac OS X 10.11.4
  • MySQL 5.6.29 (InnoDB, Connector/J 5.1.38, rewriteBatchedStatements=true)
  • Eclipse 4.6.0M6

MySQL はローカル環境で動かしています。

実行時間 : １万行挿入

最初に、メソッドごとに異なる方法で１万行のデータを挿入したときの実行時間を見てみます（一行の列数は 10）。

  • insert メソッド : バッチを使わず、単純に insert を１万行挿入
  • insertBatch メソッド : バッチを使って１万行挿入 (batch size = 100)
  • insertMultiRow メソッド : multi-row insert

Fig. 1 : JDBC で１万行挿入

Fig. 2 : MyBatis で１万行挿入

実行時間 : ５万行挿入

同じテストですが、挿入対象の行数を５万に増やしてみます。

Fig. 3 : JDBC で５万行挿入

Fig. 4 : MyBatis で５万行挿入

batch insert の方がやや速いですが multi-row insert もそれほど悪くないように見えます。
次にメモリ使用量を調べてみます。

メモリ使用量

ここでは Eclipse launcher for VisualVM を使ってメモリ使用量を調べました。
メソッドごとのメモリ使用量を知りたいので、１メソッドずつ実行しています。

Fig. 5 : １万行 / batch / JDBC （Fig. 1 の insertBatch と同じ）

Fig. 6 : １万行 / multi-row / JDBC （Fig. 1 の insertMultiRow と同じ）

Person オブジェクトに割り当てられるメモリは同じなので、そのパーセンテージを比較することでトータルのメモリ使用量を大雑把に比較することができます。
今回の例だと、multi-row insert は batch の２倍以上のメモリを使っていることになります。

挿入対象を５万行に増やすと、その差も開きます。

Fig. 7 : ５万行 / batch / JDBC （Fig. 3 の insertBatch と同じ）

Fig. 8 : ５万行 / multi-row / JDBC （Fig. 3 の insertMultiRow と同じ）

MyBatis を使った場合でも傾向は同じです。

Fig. 9 : ５万行 / batch / MyBatis （Fig. 4 の insertBatch と同じ）

Fig. 10 : ５万行 / multi-row / MyBatis （Fig. 4 の insertMultiRow と同じ）

まとめ

JDBC あるいは MyBatis を使ってバルク・インサートを実装するなら、multi-row insert よりも batch insert を使うことをお勧めします。
もちろんここに書いてあることを鵜呑みにせず、本番に近い環境でテストして確認した方が良いです。

投稿者 時刻:
ラベル: , ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)