关于解决MyBatis-Plus批量插入性能优化的问题

解决的问题
, ,

场景

前几天在做一个数据导入的功能，需要批量插入大概10万条数据到数据库

一开始用的是MyBatis-Plus的saveBatch方法，结果发现速度特别慢，插个几万条数据要好几分钟

我看了一下日志，发现虽然说是批量插入，但实际上是一条一条执行的SQL，这样的话性能肯定好不了

原因分析

MyBatis-Plus的saveBatch方法默认实现其实是循环调用insert方法，只是在同一个Session里执行而已

看了一下源码，大概是这样的逻辑：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Transactional(rollbackFor = Exception.class)
public boolean saveBatch(Collection<T> entityList, int batchSize) {
    try {
        int i = 0;
        for (T entity : entityList) {
            save(entity);  // 每次都执行一次insert
            if (++i % batchSize == 0) {
                flushStatements();
            }
        }
        flushStatements();
        return true;
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

这样的话，如果要插入10000条数据，实际上会执行10000次insert语句

虽然在同一个事务里，但网络IO和数据库解析SQL的开销还是挺大的

解决方案

方案一：使用IService的批量插入（推荐）

其实MyBatis-Plus提供了真正的批量插入方法，需要扩展一下

先自定义一个批量插入的SQL：

1
2
3
4
5
6
public interface MyBaseMapper<T> extends BaseMapper<T> {
    /**
     * 批量插入
     */
    int insertBatchSomeColumn(List<T> entityList);
}

然后实现类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public class MyBaseMapper<T> extends BaseMapperImpl<T> implements MyBaseMapper<T> {

    @Override
    public int insertBatchSomeColumn(List<T> entityList) {
        if (CollectionUtils.isEmpty(entityList)) {
            return 0;
        }
        // 这里需要自己构造批量插入的SQL
        // 可以用MyBatis的动态SQL或者用原生SQL
        return executeBatch(entityList, 1000, (sqlSession, entity) -> {
            String sql = buildInsertSql(entity);
            sqlSession.insert(sql, entity);
        });
    }
}

不过这个方法比较麻烦，还得自己写SQL

方案二：配置jdbc_url（简单有效）

这个是我后来查资料发现的，只要在数据库连接URL上加个参数就行了

MySQL的连接URL改成：

1
2
3
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

加上rewriteBatchedStatements=true这个参数之后，JDBC驱动会把批量的insert语句重写成真正的批量插入

比如原来是：

1
2
INSERT INTO user (name, age) VALUES ('name1', 18);
INSERT INTO user (name, age) VALUES ('name2', 19);

会被重写成：

1
2
3
INSERT INTO user (name, age) VALUES
('name1', 18),
('name2', 19);

这样性能提升就很明显了

我测试了一下，插10万条数据从原来的3分钟降到了20秒左右，提升还是挺大的

方案三：使用MyBatis的BatchExecutor

如果你不用MyBatis-Plus，或者想自己控制批量插入的逻辑，可以用MyBatis的BatchExecutor

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Service
public class UserService {

    @Autowired
    SqlSessionFactory sqlSessionFactory;

    public void batchInsert(List<User> users) {
        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            for (User user : users) {
                mapper.insert(user);
            }
            sqlSession.commit();
        }
    }
}

这种方式配合rewriteBatchedStatements=true效果也很好

方案四：使用MySQL的LOAD DATA INFILE

如果数据量特别大，比如上百万条，还可以考虑MySQL的LOAD DATA INFILE

先把数据导出成CSV文件，然后用LOAD DATA INFILE导入：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@RestController
public class ImportController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @PostMapping("/import")
    public String importData(MultipartFile file) throws IOException {
        // 保存上传的文件到临时目录
        String tempFile = File.createTempFile("import", ".csv").getAbsolutePath();
        file.transferTo(new File(tempFile));

        // 使用LOAD DATA INFILE导入
        String sql = String.format(
            "LOAD DATA LOCAL INFILE '%s' INTO TABLE user " +
            "FIELDS TERMINATED BY ',' " +
            "LINES TERMINATED BY '\\n' " +
            "(name, age, email)",
            tempFile
        );
        jdbcTemplate.execute(sql);

        return "导入成功";
    }
}

这个方法速度最快，但是需要文件系统权限，而且要处理CSV文件的格式问题

实测对比

我写了个测试方法，对比了一下几种方案的性能：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@SpringBootTest
public class BatchInsertTest {

    @Autowired
    UserService userService;

    @Test
    public void testBatchInsert() {
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 100000; i++) {
            User user = new User();
            user.setName("user" + i);
            user.setAge(20 + i % 30);
            users.add(user);
        }

        long start = System.currentTimeMillis();
        userService.saveBatch(users);  // 使用配置了rewriteBatchedStatements的方案
        long end = System.currentTimeMillis();

        System.out.println("耗时：" + (end - start) / 1000 + "秒");
    }
}

测试结果：

  • 原始saveBatch：约180秒
  • 加上rewriteBatchedStatements=true：约18秒
  • BatchExecutor + rewriteBatchedStatements=true：约15秒
  • LOAD DATA INFILE：约5秒

其他注意事项

批量大小

批量插入的时候，也不是一次插入越多越好

我测试了一下，每批1000-2000条效果比较好

1
2
3
4
5
6
7
8
9
10
11
@Service
public class UserService extends ServiceImpl<UserMapper, User> {

    public boolean saveBatchOptimized(List<User> entityList) {
        if (CollectionUtils.isEmpty(entityList)) {
            return true;
        }
        // 每批1000条
        return saveBatch(entityList, 1000);
    }
}

事务控制

批量插入一定要在事务里执行，不然插入一半出错就很麻烦

1
2
3
4
@Transactional(rollbackFor = Exception.class)
public void importUsers(List<User> users) {
    saveBatch(users, 1000);
}

总结

如果只是普通的业务场景，加上rewriteBatchedStatements=true这个配置就够用了

如果数据量特别大，可以考虑LOAD DATA INFILE或者其他方案

其实很多性能问题都是配置没调好，不一定非得用很复杂的方案

暂时就先记录这么多