白川潼雨ShirakawaTyu

JDBC 批量插入参数 rewriteBatchedStatements 的坑

ShirakawaTyu2026/03/18技术博客JDBCrewriteBatchedStatements批量插入SQL执行优化批处理操作多表插入问题数据库性能调优MyBatis批量处理批量操作限制参数配置陷阱

JDBC 批量插入参数 rewriteBatchedStatements 的坑

不支持多表交错插入

先看代码：

 try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
      UserRoleMapper userRoleMapper = sqlSession.getMapper(UserRoleMapper.class);
      UserProfileStudentMapper userProfileStudentMapper = sqlSession.getMapper(UserProfileStudentMapper.class);
      UserRoleScopeMapper userRoleScopeMapper = sqlSession.getMapper(UserRoleScopeMapper.class);

      // 做了简化，省略具体逻辑
      for (Map.Entry<String, Object> entry : studentGroups.entrySet()) {
          // 创建user_profile_student记录
          UserProfileStudentDAO studentProfile = new UserProfileStudentDAO();
          userProfileStudentMapper.insert(studentProfile);

          // 创建user_role记录
          UserRoleDAO userRole = new UserRoleDAO();
          userRoleMapper.insert(userRole);

          // 创建user_role_scope记录
          UserRoleScopeDAO userRoleScope = new UserRoleScopeDAO();
          userRoleScopeMapper.insert(userRoleScope);
      }
      sqlSession.commit();
  }

这段代码做的事情非常简单，针对读入的每一行数据，解析成三个记录插入三个表中。注意看开头的 SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)，这表示我们启用了批量插入，设想中驱动应该帮我们把三个表的所有 insert 语句合并成每个表一条，然后插入数据库，然而调用 Profile 工具会发现 sqlSession.commit() 执行用了非常长的时间（如下图）。

不对劲，看一下 log

==>  Preparing: INSERT INTO user_profile_student ( user_id, gender, class_name, grade ) VALUES ( ?, ?, ?, ? )
==> Parameters: 8390(Long), null, 24机械本6(String), 暂无(String)
==>  Preparing: INSERT INTO user_role_scope (user_id, role_id, college_id, department_id, create_by, update_by, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
==> Parameters: 8390(Long), 1(Long), null, null, 7(Long), 7(Long), 2026-03-17T22:07:19.580212900(LocalDateTime), 2026-03-17T22:07:19.580212900(LocalDateTime)
==>  Preparing: INSERT INTO user_role_scope ( user_id, role_id, college_id, department_id, create_by, update_by, create_time, update_time ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )
==> Parameters: 8390(Long), 1(Long), null, null, 7(Long), 7(Long), 2026-03-17T22:07:19.581212600(LocalDateTime), 2026-03-17T22:07:19.581212600(LocalDateTime)
==>  Preparing: INSERT INTO user_profile_student ( user_id, gender, class_name, grade ) VALUES ( ?, ?, ?, ? )
==> Parameters: 8391(Long), null, 24数媒本1(String), 暂无(String)
==>  Preparing: INSERT INTO user_role_scope (user_id, role_id, college_id, department_id, create_by, update_by, create_time, update_time) VALUES (?, ?, NULL, NULL, ?, ?, ?, ?)
...

可以看到 user_profile_student 和 user_role_scope 都出现了多次 insert。

很显然这段 log 中出现的两个表的 insert 语句都没有被合并！问题就出在驱动不能对多表交错插入的语句进行优化，尽管相同的表的插入语句都是一个格式。

解决办法也很简单，每个表分别执行 insert 即可。

try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
    UserProfileStudentMapper userProfileStudentMapper = sqlSession.getMapper(UserProfileStudentMapper.class);
    UserRoleScopeMapper userRoleScopeMapper = sqlSession.getMapper(UserRoleScopeMapper.class);
    int operationCount = 0;
    for (Map.Entry<String, Object> entry : studentGroups.entrySet()) {
        // 创建user_profile_student记录
        UserProfileStudentDAO studentProfile = new UserProfileStudentDAO();
        userProfileStudentMapper.insert(studentProfile);

        operationCount++;
        if (operationCount % 1000 == 0) {
            sqlSession.flushStatements();
        }
    }

    // 推入数据库
    sqlSession.flushStatements();
    operationCount = 0;

    for (Map.Entry<String, List<StudentEnrollmentRowData>> entry : studentGroups.entrySet()) {
        // 创建user_role记录
        UserRoleDAO userRole = new UserRoleDAO();
        userRoleMapper.insert(userRole);

        operationCount++;
        if (operationCount % 1000 == 0) {
            sqlSession.flushStatements();
        }
    }
    sqlSession.flushStatements();
    operationCount = 0;

    for (Map.Entry<String, List<StudentEnrollmentRowData>> entry : studentGroups.entrySet()) {
        // 创建user_role_scope记录
        UserRoleScopeDAO userRoleScope = new UserRoleScopeDAO();
        userRoleScopeMapper.insert(userRoleScope);

        operationCount++;
        if (operationCount % 1000 == 0) {
            sqlSession.flushStatements();
        }
    }
    // 提交事务
    sqlSession.commit();
}

max_allowed_packet 限制

可能有人注意到上面第二版代码多了：

operationCount++;
if (operationCount % 1000 == 0) {
    sqlSession.flushStatements();
}

这是因为 rewriteBatchedStatements 的原理是把成百上千条 INSERT 拼接成一条巨大的 INSERT ... VALUES (...), (...)...，而 MySQL 服务端对单条 SQL 的大小有限制（默认通常是 4MB 或 16MB），如果一次性 flush 的数据量太大（比如攒了 1 万条大字符串记录），生成的 SQL 字符串超过了服务器的 max_allowed_packet，数据库会直接报错：Packet for query is too large。

所以最好是每隔一定的批量大小就 flush 一次

Edit this page
ON THIS PAGE