Java多线程批量插入百万级数据

前言

        最近在项目中需要定时同步第三方的数据源,起初用定时任务写了同步逻辑,以及定时增量同步逻辑,后面发现数据源中的数据存在删除操作,导致无法同步被删除的数据,由于数据量级较大,一百多万的数据量进行逐行对比删除耗时较长,且我在本地对表结构进行了修改,于是就只能使用全量同步.而一百多万的数据量,全量同步的耗时已达到五分钟,为了优化性能使用了线程池进行批量插入.话不多说,直接上代码.

线程池配置

import lombok.extern.slf4j.Slf4j;
import org.springframework.aop.interceptor.AsyncUncaughtExceptionHandler;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.support.RootBeanDefinition;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Role;
import org.springframework.scheduling.annotation.AsyncConfigurer;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.lang.reflect.Method;

@Configuration
@Slf4j
@Role(RootBeanDefinition.ROLE_INFRASTRUCTURE)
public class AsyncConfig implements AsyncConfigurer {

    @Bean("async")
    @Qualifier("async")
    public ThreadPoolTaskExecutor asyncExecutor() {
        ThreadPoolTaskExecutor threadPoolTaskExecutor = new ThreadPoolTaskExecutor();
        //设置核心线程数 默认值 1    8020原则
        threadPoolTaskExecutor.setCorePoolSize(8);
        //最大线程数
        threadPoolTaskExecutor.setMaxPoolSize(8*2);
        //线程池所使用的缓冲队列
        threadPoolTaskExecutor.setQueueCapacity(1000);
        threadPoolTaskExecutor.setThreadNamePrefix("async-");
        threadPoolTaskExecutor.initialize();//初始化
        return threadPoolTaskExecutor;
    }


    @Override
    public AsyncUncaughtExceptionHandler getAsyncUncaughtExceptionHandler() {
        return new SpringAsyncExceptionHandler();
    }

    static class SpringAsyncExceptionHandler implements AsyncUncaughtExceptionHandler {
        @Override
        public void handleUncaughtException(Throwable throwable, Method method, Object... obj) {
            log.error("错误原因:" + throwable.getMessage(), throwable);
            log.error("------------   Exception occurs in async method-----------------------{}", throwable.getMessage());
        }
    }
}

定时任务执行业务数据同步

/**
     * 每天晚上凌晨2点执行一次
     */
    @Scheduled(cron = "0 0 2 * * ?")
    public void fullSyncEventRecord(){
        log.info("================开始全量同步数据表=================");
        //从源数据中获取所有数据
        Connection sqConnection = getSQConnection(mysqlUrl);

        long startTime = System.currentTimeMillis();
        try {
            Statement statement = sqConnection.createStatement();
            //删除表中所有数据
            eventRecordMapper.deleteAllEventRecord();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM DataSourceTable");
            //批量处理数据
            batchProcessEventRecord(resultSet);
            long endTime = System.currentTimeMillis();
            log.info("全量同步数据表完成,耗时{}ms", endTime - startTime);
            resultSet.close();
        }catch (Exception e){
            log.error("全量同步数据库失败:{}",  e);
        }
        finally {
            try {
                sqConnection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

批量插入数据

/**
     * 批量处理数据
     * @param resultSet 数据结果集
     * @throws SQLException SQL异常
     */
    private void batchProcessEventRecord(ResultSet resultSet) throws SQLException {
        List<EventRecord> batch = new ArrayList<>();
        //设置批处理大小为1000
        int batchSize = 1000;
        int totalCount = 0;

        while (resultSet.next()){
            //属性赋值逻辑....省略
            batch.add(eventRecord);
            totalCount++;
            //达到批次大小时提交处理
            if (batch.size() >= batchSize){
                submitBatchInsert(batch);
                batch = new ArrayList<>();
            }
        }
        //处理剩余数据
        if (!batch.isEmpty()){
            submitBatchInsert(batch);
        }
        log.info("批量处理数据完成,共处理{}条数据", totalCount);
    }

线程池执行任务

/**
     * 提交批次插入任务到线程池
     * @param batch 批次数据
     */
    private void submitBatchInsert(List<EventRecord> batch) {
        CompletableFuture.runAsync(() -> {
            try {
                // 批量插入数据
                for (EventRecord eventRecord : batch) {
                    eventRecordMapper.insertEventRecord(eventRecord);
                }
                log.info("成功插入批次数据，共 {} 条记录", batch.size());
            } catch (Exception e) {
                log.error("批量插入失败，批次大小: {}: {}", batch.size(), e.getMessage(), e);
            }
        }, threadPoolTaskExecutor);
    }

效果实测

同步时间从最初的5分钟优化至57秒,优化效果比较明显