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

    @Test
    public 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 ms
        System.out.println("cost = " + (end-start) + " ms");
    }

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

    @Test
    public 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 ms
        System.out.println("cost = " + (end-start) + " ms");
    }

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

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

    @Test
    public 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 ms
        System.out.println("cost = " + (end-start) + " ms");
    }

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

    private Connection getConnection() throws Exception {
    // 添加参数rewriteBatchedStatements=true
        String url = "jdbc:mysql://192.168.1.9:3306/jdbc?rewriteBatchedStatements=true";
        String username = "root";
        String password = "root";
        return DriverManager.getConnection(url, username, password);
    }
    @Test
    public 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 ms
        System.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. 结语：如果对大家有帮助，请点赞支持。如果有问题随时在评论中指出，感谢。