mysql jdbc批处理,mybatis批处理,mybatis sql拼接批量传插入速度比较

环境:JDK8,Mysql5.7,Mybatis3.5.3

注意Mysql批处理时连接配置需开启批处理,否则批处理无效,速度比单条循环还要慢

数据库连接

数据库表

create table user
(
    id   varchar(32)  not null
        primary key,
    name varchar(100) null,
    sex  varchar(5)   null,
    age  int          null,
    corp varchar(100) null,
    job  varchar(100) null
);

实体类

```java
public class User {
    @ExcelIgnore
    private String id;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("性别")
    private String sex;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("部门")
    private String corp;

    @ExcelProperty("工作")
    private String job;
}

插入数量50万

		DataSource datasource = applicationContext.getBean(DataSource.class);

        Connection connection = datasource.getConnection();
        connection.setAutoCommit(false);

        PreparedStatement statement = connection.prepareStatement("insert into user(id,name,sex,age,corp,job) values (?,?,?,?,?,?)");

        User user;
        int batchCount = 0;
        Instant start = Instant.now();

        for (int i = 0; i < 500000; i++) {
            user = initUser();

            statement.setString(1,user.getId());
            statement.setString(2,user.getName());
            statement.setString(3,user.getSex());
            statement.setInt(4,user.getAge());
            statement.setString(5,user.getCorp());
            statement.setString(6,user.getJob());
            statement.addBatch();

            batchCount++;

            if(batchCount >= 2000){
                statement.executeBatch();
                connection.commit();
                statement.clearBatch();
                System.out.println("提交数量:"+batchCount);
                batchCount = 0;
            }

        }
        
        if(batchCount > 0 ){
            statement.executeBatch();
            connection.commit();
        }
        
        connection.close();
        Instant end = Instant.now();
        System.out.println(Duration.between(start,end).getSeconds());

		SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        Instant start = Instant.now();
        int batchCount = 0;

        for (int i = 0; i < 500000; i++) {
            User user = initUser();
            mapper.insertUser(user);
            batchCount++;

            if(batchCount >= 2000){
                sqlSession.commit();
                sqlSession.clearCache();
                System.out.println("提交数量:"+batchCount);
                batchCount = 0;
            }
        }

        if(batchCount > 0 ){
            sqlSession.commit();
        }
        sqlSession.close();
        Instant end = Instant.now();
        System.out.println(Duration.between(start,end).getSeconds());  

        Instant start = Instant.now();
        int batchCount = 0;
        List userList = new ArrayList<>();

        for (int i = 0; i < 500000; i++) {
            User user = initUser();
            userList.add(user);
            batchCount++;

            if(batchCount >= 500){
                userMapper.insertUserList(userList);
                System.out.println("提交数量:"+batchCount);
                userList.clear();
                batchCount = 0;
            }
        }

        if(batchCount > 0 ){
            userMapper.insertUserList(userList);
        }
        Instant end = Instant.now();
        System.out.println(Duration.between(start,end).getSeconds());

        insert into user(id,name,sex,age,corp,job)
            values
            
                (#{item.id,jdbcType=VARCHAR},
                #{item.name,jdbcType=VARCHAR},
                #{item.sex,jdbcType=VARCHAR},
                #{item.age,jdbcType=INTEGER},
                #{item.corp,jdbcType=VARCHAR},
                #{item.job,jdbcType=VARCHAR})