java加快批量插入的两个方法

一：配置参数：

关于rewriteBatchedStatements这个参数介绍：

MySQL的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。
MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。
只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL
另外这个选项对INSERT/UPDATE/DELETE都有效

添加rewriteBatchedStatements=true这个参数后的执行速度比较：
同个表插入一万条数据时间近似值：
JDBC BATCH 1.1秒左右 > Mybatis BATCH 2.2秒左右 > 拼接SQL 4.5秒左右

jdbc-url: jdbc:mysql://${MYSQL_HOST:xxx}:${MYSQL_PORT:3306}/${MYSQL_DB:xxx}?
useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true

二：多线程插入

1、引入多线程配置类

@Configuration
public class ThreadConfig implements AsyncConfigurer {



    @Bean
    @Override
    public Executor getAsyncExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(16);
        executor.setMaxPoolSize(1000);
        executor.setQueueCapacity(500);
        //线程前缀
        executor.setThreadNamePrefix("executor-");
        executor.setKeepAliveSeconds(30000);
        executor.initialize();
        return executor;
    }
}

2、实际调用引用

@Slf4j
@Service
public class CreateDataJob {

    @Autowired
    private SequenceGen sequenceGen;


    @Autowired
    private ThreadConfig executorService;


    /**
     * 模拟数据
     *
     * @Auth fxr
     * @Date 2021年2月10日13:26:18
     */
    @Scheduled(cron = "0 0/15 * * * ?")
    @Transactional
    public void createData() {
        //mysql
        QueryWrapper<DeviceStateEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("del_flag", 1);
        List<DeviceStateEntity> eiDeviceStates = deviceStateService.list(queryWrapper);
        eiDeviceStates.parallelStream().forEach(e -> {
            e.setOccurredTime(new Date());
            e.setId(sequenceGen.gen());
        });
        deviceStateService.remove(queryWrapper);
        //多线程入库
        CountDownLatch countDownLatch = null;
        int count = eiDeviceStates.size() / 4;
        List<DeviceStateEntity> newlist = null;
        //分4个线程执行
        for (int i = 0; i < 4; i++) {
            int startIndex = (i * count);
            int endIndex = (i + 1) * count;
            if (i == 3) {
                endIndex = eiDeviceStates.size();
            }
            newlist = eiDeviceStates.subList(startIndex, endIndex);
            List<DeviceStateEntity> finalNewlist = newlist;
            executorService.getAsyncExecutor().execute(() -> {
                try {
                    //调用数据库实现插入
                    deviceStateService.saveBatch(finalNewlist);
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    countDownLatch.countDown();
                }
            });
        }

    }

}