最近のプロジェクトで様々なinsertBatchのコードを書いてきましたが、いつも「バッチ挿入はループ挿入よりずっと効率的だ」と言われます。そこで今回は、本当にそうなのかを検証してみましょう。
テスト環境：
- SpringBoot 2.5
- Mysql 8
- JDK 8
- Docker
まず、複数データの挿入には以下の選択肢があります：
foreachループによる挿入- SQLを結合して一度に実行
- バッチ処理機能を使用した挿入
テスト環境の構築
SQLファイル：
drop database IF EXISTS test;
CREATE DATABASE test;
use test;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT "",
`age` int(11) DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
アプリケーションの設定ファイル：
server:
port: 8081
spring:
#データベース接続設定
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&&serverTimezone=UTC&setUnicode=true&characterEncoding=utf8&&nullCatalogMeansCurrent=true&&autoReconnect=true&&allowMultiQueries=true
username: root
password: 123456
#mybatisの関連設定
mybatis:
#mapper設定ファイル
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.techdemo.model
#キャメルケース名の有効化
configuration:
map-underscore-to-camel-case: true
logging:
level:
root: error
起動ファイル、Mapperファイルのスキャンパスを設定：
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.techdemo.mapper")
public class TechdemoApplication {
public static void main(String[] args) {
SpringApplication.run(TechdemoApplication.class, args);
}
}
Mapperファイルにはいくつかのメソッドを準備しました。単一オブジェクトの挿入、すべてのオブジェクトの削除、複数オブジェクトの結合挿入：
import com.techdemo.model.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UserMapper {
int insertSingle(User user);
int deleteAll();
int batchInsert(@Param("items") List<User> items);
}
Mapper.xmlファイル：
<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="com.techdemo.mapper.UserMapper">
<insert id="insertSingle" parameterType="com.techdemo.model.User">
insert into user(id,age) values(#{id},#{age})
</insert>
<delete id="deleteAll">
delete from user where id>0;
</delete>
<insert id="batchInsert" parameterType="java.util.List">
insert into user(id,age) VALUES
<foreach collection="items" item="record" index="index" separator=",">
(#{record.id}, #{record.age})
</foreach>
</insert>
</mapper>
テスト時は、毎回すべてのデータを削除して、前のデータの影響を受けないようにしてテストの客観性を確保します。
異なるテスト方法
1. foreachによる挿入
リストを取得し、各データごとにデータベース操作を実行してデータを挿入します：
@SpringBootTest
@MapperScan("com.techdemo.mapper")
class TechdemoApplicationTests {
@Autowired
SqlSessionFactory sqlSessionFactory;
@Resource
UserMapper userMapper;
static int recordCount = 100000;
static int startId = 1;
@Test
void testLoopInsertion() {
List<User> records = generateRandomUsers();
long startTime = System.currentTimeMillis();
for (int i = 0; i < records.size(); i++) {
userMapper.insertSingle(records.get(i));
}
long endTime = System.currentTimeMillis();
System.out.println("処理時間: " + (endTime - startTime));
}
}
2. SQL結合による挿入
以下の方法でデータを挿入します：
INSERT INTO `user` (`id`, `age`)
VALUES (1, 11),
(2, 12),
(3, 13),
(4, 14),
(5, 15);
@Test
void testSqlSplicing() {
List<User> records = generateRandomUsers();
long startTime = System.currentTimeMillis();
userMapper.batchInsert(records);
long endTime = System.currentTimeMillis();
System.out.println("処理時間: " + (endTime - startTime));
}
3. Batchによるバッチ挿入
MyBatisセッションのexecutor typeをBatchに設定し、sqlSessionFactoryで実行方式をバッチに、自動コミットをfalseに設定し、すべて挿入後に一度にコミットします：
@Test
public void testBatchInsertion() {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> records = generateRandomUsers();
long startTime = System.currentTimeMillis();
for(int i=0;i<records.size();i++){
mapper.insertSingle(records.get(i));
}
sqlSession.commit();
sqlSession.close();
long endTime = System.currentTimeMillis();
System.out.println("処理時間: " + (endTime - startTime));
}
4. バッチ処理+分割コミット
バッチ処理の基礎上、1000件ごとに先にコミットし、分割コミットします：
@Test
public void testBatchWithChunkCommit() {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> records = generateRandomUsers();
long startTime = System.currentTimeMillis();
for(int i=0;i<records.size();i++){
mapper.insertSingle(records.get(i));
if (i % 1000 == 0 || i == recordCount - 1) {
sqlSession.commit();
sqlSession.clearCache();
}
}
sqlSession.close();
long endTime = System.currentTimeMillis();
System.out.println("処理時間: " + (endTime - startTime));
}
初回の結果、明らかに間違っている？
上記のコードを実行すると、以下の結果が得られます。forループによる挿入の効率は確かに非常に悪く、SQL結合の効率は相対的に高いです。一部の資料ではSQL結合がMySQLによって制限される可能性があると述べていますが、1000万件まで実行したときにようやくヒープメモリオーバーフローが発生しました。
以下は間違った結果です！
| 挿入方法 | 10 | 100 | 1000 | 1万 | 10万 | 100万 | 1000万 |
|---|---|---|---|---|---|---|---|
| forループ挿入 | 387 | 1150 | 7907 | 70026 | 635984 | 時間がかかりすぎ... | 時間がかかりすぎ... |
| SQL結合挿入 | 308 | 320 | 392 | 838 | 3156 | 24948 | OutOfMemoryError: ヒープメモリオーバーフロー |
| バッチ処理 | 392 | 917 | 5442 | 51647 | 470666 | 時間がかかりすぎ... | 時間がかかりすぎ... |
| バッチ処理 + 分割コミット | 359 | 893 | 5275 | 50270 | 472462 | 時間がかかりすぎ... | 時間がかかりすぎ... |
SQL結合はメモリを超えていない
MySQLの制限を見てみましょう：
mysql> show VARIABLES like '%max_allowed_packet%';
+---------------------------+------------+
| Variable_name | Value |
+---------------------------+------------+
| max_allowed_packet | 67108864 |
| mysqlx_max_allowed_packet | 67108864 |
| slave_max_allowed_packet | 1073741824 |
+---------------------------+------------+
3 rows in set (0.12 sec)
この67108864は600MB以上もあり、大きすぎます。そのためエラーにならないのは当然です。では、これを変更してみましょう：
- まずMySQLを起動した状態で、コンテナ内に入ります。Dockerデスクトップ版で直接Cliアイコンをクリックして入っても構いません：
docker exec -it mysql bash
/etc/mysqlディレクトリに入り、my.cnfファイルを編集します：
cd /etc/mysql
- vimをインストールしないとファイルを編集できません：
apt-get update
apt-get install vim
my.cnfを編集します：
vim my.cnf
- 最後の行に
max_allowed_packet=20Mを追加します（編集モードに入るにはiを押し、編集後はescを押して:wqで退出）
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# シンボリックリンクを無効化することは、様々なセキュリティリスクを防ぐために推奨されます
symbolic-links=0
# カスタム設定はここに記述
!includedir /etc/mysql/conf.d/
max_allowed_packet=2M
- コンテナから退出します：
# exit
- MySQLコンテナのIDを確認します：
docker ps -a
- MySQLを再起動します：
docker restart c178e8998e68
再起動成功後、最大のmax_allowed_packetを確認すると、変更が成功したことがわかります：
mysql> show VARIABLES like '%max_allowed_packet%';
+---------------------------+------------+
| Variable_name | Value |
+---------------------------+------------+
| max_allowed_packet | 2097152 |
| mysqlx_max_allowed_packet | 67108864 |
| slave_max_allowed_packet | 1073741824 |
+---------------------------+------------+
再度SQL結合を実行すると、100万件の時点でSQLが約3.6MBに達し、設定した2MBを超えているため、エラーが発生することが確認できます：
org.springframework.dao.TransientDataAccessResourceException:
### Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.
; Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.; nested exception is com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.
バッチ処理がなぜ遅いのか？
しかし、よく見ると、上記の方法ではバッチ処理時に優位性が示されておらず、forループと大差ないように見えます。これは正しいのでしょうか？
これは間違いです。公式ドキュメントによると、バッチ更新を行い、毎回プリペアドステートメントを作成しないため、理論上はより速いはずです。
その後、私が見つけた最も重要な問題：データベース接続URLにrewriteBatchedStatements=trueが欠けていました。
このパラメータを書かない場合、MySQL JDBCドライバはデフォルトでexecuteBatch()ステートメントを無視し、期待するバッチ実行のSQLステートメントを分割して、実際には1件ずつMySQLデータベースに送信し、実質的に単一挿入となり、性能が低くなります。なぜ性能がループ挿入と同じくらいになるのかがわかりました。
rewriteBatchedStatementsパラメータをtrueに設定することで、データベースドライバがバッチ実行SQLを助けてくれます。
正しいデータベース接続：
jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&&serverTimezone=UTC&setUnicode=true&characterEncoding=utf8&&nullCatalogMeansCurrent=true&&autoReconnect=true&&allowMultiQueries=true&&&rewriteBatchedStatements=true
問題を見つけた後、再度バッチテストを実行し、最終的な結果は以下の通りです：
| 挿入方法 | 10 | 100 | 1000 | 1万 | 10万 | 100万 | 1000万 |
|---|---|---|---|---|---|---|---|
| forループ挿入 | 387 | 1150 | 7907 | 70026 | 635984 | 時間がかかりすぎ... | 時間がかかりすぎ... |
| SQL結合挿入 | 308 | 320 | 392 | 838 | 3156 | 24948（SQL長さ制限を超える可能性あり） | OutOfMemoryError: ヒープメモリオーバーフロー |
| バッチ処理（重要） | 333 | 323 | 362 | 636 | 1638 | 8978 | OutOfMemoryError: ヒープメモリオーバーフロー |
| バッチ処理 + 分割コミット | 359 | 313 | 394 | 630 | 2907 | 18631 | OutOfMemoryError: ヒープメモリオーバーフロー |
上記の結果から、バッチ処理は確かにずっと速いことがわかります。数量が大きすぎると、メモリオーバーフローを超える可能性があります。バッチ処理に分割コミットを加えても、バッチ処理とほぼ同じか、むしろ遅くなります。コミットが多すぎるためです。SQL結合の方案は数量が少ない場合、バッチ処理と大差ないですが、最悪の方案はforループによるデータ挿入で、100件の時点で既に1秒かかります。この方案は選択できません。
最初にバッチ処理が遅いと感じた時、本当に自分を疑いました。後でパラメータがあることに気づき、霧が晴れたような気持ちでした。知ること越多、知らないこと越多。