Jdbc批处理功能和MybatisPlus

1. 序言

MybatisPlus的ServiceImpl类中提供了批处理方法saveBatch，用来批量插入数据，速度要比”一个一个”插入更快。而这个方法的底层依赖了JDBC数据库驱动的批处理功能。
本文先介绍JDBC数据库驱动的批处理功能，然后对"JDBC批量插入"进行性能测试，从而说明saveBatch快的原因，最后说明MybatisPlus ServiceImpl.saveBatch的最佳使用方式(其实就一句话)。

2. JDBC批处理功能和rewriteBatchedStatements

• JDBC批处理功能是指：将多条SQL语句打包起来，一次性发给数据库服务器，服务器执行后将结果返回给客户端。相对于单条SQL逐条发送，批处理功能可以减少网络传输开销。JDBC的Statement和PreparedStatement都支持批处理，下面测试基于PreparedStatement。
  • PreparedStatement.addBatch()用来添加一条SQL到列表中，但是不会提交给服务器。
  • PreparedStatement.executeBatch()将列表中的所有SQL提交给服务器，并获得执行结果。
• rewriteBatchedStatements：假如数据库URL连接设置了参数rewriteBatchedStatements=true，那么执行executeBatch()时，多条INSERT语句就会被重写为一条INSERT语句，再发送给服务器，使得插入速度更快。默认值为false。

3. JDBC批量插入的测试

1. 配置：MySQL驱动 8.3.0，两台电脑：PC1运行MySQL服务器，PC2作为客户端运行以下测试程序，PC1和PC2在同一局域网。
2. 表结构：

	CREATE TABLE `user` (`id` bigint NOT NULL,`username` varchar(255) NOT NULL,`age` int NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

3. 获取数据库连接代码

	private Connection getConnection() throws Exception {String url = "jdbc:mysql://192.168.1.9:3306/jdbc";String username = "root";String password = "root";return DriverManager.getConnection(url, username, password);}

4. 逐条插入：耗时171172 ms

    @Testpublic void testSingleInsert() throws Exception {long start = System.currentTimeMillis();// 逐条SQL语句发送Connection conn = getConnection();PreparedStatement psmt = conn.prepareStatement("INSERT INTO user VALUES (?, ?, ?)");for (int i=1; i<=10000; i++) {psmt.setLong(1, i);psmt.setString(2, "user_" + i);psmt.setInt(3, 17);psmt.execute();}long end = System.currentTimeMillis();
//       171172 msSystem.out.println("cost = " + (end-start) + " ms");}

5. 批量插入：耗时116244 ms，使用批处理减少了网络开销，变快了。

    @Testpublic void testBatchInsert() throws Exception {long start = System.currentTimeMillis();// 批量SQL发送Connection conn = getConnection();PreparedStatement psmt = conn.prepareStatement("INSERT INTO user VALUES (?, ?, ?)");for (int i=1; i<=10000; i++) {psmt.setLong(1, i);psmt.setString(2, "user_" + i);psmt.setInt(3, 17);psmt.addBatch();if (i % 1000 == 0) {psmt.executeBatch();}}long end = System.currentTimeMillis();
//        116244 msSystem.out.println("cost = " + (end-start) + " ms");}

注意：经过测试，如果MySQL服务器和客户端运行在同一台PC，"批量插入"和上一个"逐条插入"花费时间差不多，因为网络传输消耗的时间太小。

7. 批量插入 + 手动开启事务：耗时68922 ms
   上一个"批量插入"的例子，服务器执行每一条INSERT SQL都会自动开启和提交事务。而"批量插入 + 手动开启事务"只开启和提交了一次事务，所以速度会更快。

    @Testpublic void testBatchInsertWithTransaction() throws Exception {long start = System.currentTimeMillis();// 批量SQL发送Connection conn = getConnection();conn.setAutoCommit(false);PreparedStatement psmt = conn.prepareStatement("INSERT INTO user VALUES (?, ?, ?)");for (int i=1; i<=10000; i++) {psmt.setLong(1, i);psmt.setString(2, "user_" + i);psmt.setInt(3, 17);psmt.addBatch();if (i % 1000 == 0) {psmt.executeBatch();}}conn.commit();long end = System.currentTimeMillis();//cost = 68922 msSystem.out.println("cost = " + (end-start) + " ms");}

8. 批量插入 + 手动开启事务 + rewriteBatchedStatements：耗时1532 ms

    private Connection getConnection() throws Exception {// 添加参数rewriteBatchedStatements=trueString url = "jdbc:mysql://192.168.1.9:3306/jdbc?rewriteBatchedStatements=true";String username = "root";String password = "root";return DriverManager.getConnection(url, username, password);}@Testpublic void testBatchInsertWithTransactionAndRewrite() throws Exception {long start = System.currentTimeMillis();// 批量SQL发送，Connection conn = getConnection();conn.setAutoCommit(false);PreparedStatement psmt = conn.prepareStatement("INSERT INTO user VALUES (?, ?, ?)");for (int i=1; i<=10000; i++) {psmt.setLong(1, i);psmt.setString(2, "user_" + i);psmt.setInt(3, 17);psmt.addBatch();if (i % 1000 == 0) {psmt.executeBatch();}}conn.commit();long end = System.currentTimeMillis();//cost = 1532 msSystem.out.println("cost = " + (end-start) + " ms");}

可见"批量插入 + 手动开启事务 + rewriteBatchedStatements"速度最快。

4. MybatisPlus#ServiceImpl.saveBatch()

MybatisPlus 3.5.8

可以看到ServiceImpl.saveBatch是开启了事务的，因此性能 = “批量插入 + 手动开启事务”。
所以通常我们会在JDBC数据库连接的后面加上rewriteBatchedStatements=true，变成"批量插入 + 手动开启事务 + rewriteBatchedStatements"，达到最佳性能。
因此MybatisPlus批量插入的最佳方式是：saveBatch + rewriteBatchedStatements。

5. 结语：如果对大家有帮助，请点赞支持。如果有问题随时在评论中指出，感谢。