多线程之批量插入小demo

时间:2023-01-26 11:54:25

多线程之批量插入

背景

昨天在测试mysql的两种批量更新时,由于需要入库大量测试数据,反复执行插入脚本,过程繁琐,档次很低,测试完后我就想着写个批量插入的小demo,然后又想写个多线程的批量插入的demo,然后就有了下面的东西了……

环境

spring-boot 1.5.6 集成 mysql druid mybits 还有一些无关紧要的东西

代码

线程类:
/**
 * @ClassName InsertDataThread
 * @Description <
插入数据类>
 * @Author zhaiyt
 * @Date 2018/8/29 17:04
 * @Version 1.0
 */
public class InsertDataThread extends Thread {     //日志
   
private final Logger logger = LoggerFactory.getLogger(InsertDataThread.class);     //数据访问层
   
private UserEntityMapper userEntityMapper;     //具体插入批次
   
private int batch;     //插入的数据
   
private List<UserEntity> list;     public InsertDataThread(UserEntityMapper userMpper, List<UserEntity> li, int batch) {
        this.userEntityMapper = userMpper;
        this.list = li;
        this.batch = batch;
    }     @Override
    public void run() {
        try {
            this.userEntityMapper.insertBatch(this.list);
            logger.info("" + this.batch + "批次插入成功");
        } catch (Exception e) {
            logger.error("" + this.batch + "批次插入失败");
        }     }
}

===============================================================================

service层的多线程批量插入方法:

/**
 * @param
list
 
* @return int
 * @Description <
批量插入>
 * @Author zhaiyt
 * @Date 9:51 2018/8/29
 * @Param [list]
 */
@Override
public int insertBatch(List<UserEntity> list) throws Exception {
    PageHelper.offsetPage(0, 500000);
    long start = System.currentTimeMillis();
    List<UserEntity> listUser = userEntityMapper.selectAllUser();
    int betch = 0;
        if (CollectionUtils.isEmpty(listUser)) {
        logger.error("表中无数据,需要改造测试");
        return 0;
    }
    //根据数据量判断是否使用多线程 选择开启线程数
   
if (listUser.size() > 1000000) {
        betch = 10;
    } else if (listUser.size() > 100000) {
        betch = 5;
    } else if (listUser.size() > 10000) {
        betch = 3;
    } else {
        //不走多线程
       
long end = System.currentTimeMillis();
        logger.error("查询耗时:" + (end - start));
        start = System.currentTimeMillis();
        int count = userEntityMapper.insertBatch(listUser);
        end = System.currentTimeMillis();
        logger.error("插入耗时:" + (end - start));
        return count;
    }     //计数器
   
int size = 0;
    //创建线程池
   
ExecutorService fixedThreadPool = Executors.newFixedThreadPool(betch);     for (int i = 0; i < (Math.ceil(listUser.size() / 5000)); i++) {
        int startLen = i * 5000;
        int endLen = ((i + 1) * 5000 > listUser.size() ? listUser.size() - 1 : (i + 1) * 5000);
        // 该线程处理
        List<UserEntity> threadList = listUser.subList(startLen, endLen);
        size = size + threadList.size();
        fixedThreadPool.execute(new InsertDataThread(userEntityMapper, threadList, i));
    }
    System.err.println("插入数据总条数:" + size);
    long end = System.currentTimeMillis();
    logger.error("查询耗时:" + (end - start));
    return size;
}

项目路径:https://git.lug.ustc.edu.cn/zhaiyt/threadInsertDemo