利用线程池优化大批量数据库操作

在数据库中如果需要对大量的数据进行批量修改，并不是一项简单的工作。而利用线程池可以帮助我们解决这些问题

1.循环操作的代码

For循环插入5000条记录

@Test
void contextLoads6() {
    ArrayList<UserInfo> allUser = new ArrayList<>(5000);
 
    //生成size个UserInfo
    for (int i = 0; i < 5000; i++) {
        UserInfo userInfo = new UserInfo();
        userInfo.setId(String.valueOf(i + 10000));
        allUser.add(userInfo);
    }
 
    long  startTime = System.currentTimeMillis();
    for (int i = 0;i<5000;i++){
        mapper.insert(allUser.get(i));
    }
    System.out.println("单线程for循环插入5000耗时:"+(System.currentTimeMillis()-startTime));
}

 

耗时:14944ms

 

2.利用多线程

实现Callable接口

public class InsertTarget implements Callable<Integer> {
 
    private UserInfoMapper mapper;
 
    private List<UserInfo> list;
 
    private CountDownLatch countDownLatch;
 
    public InsertTarget(UserInfoMapper mapper, List<UserInfo> list, CountDownLatch countDownLatch) {
        this.mapper = mapper;
        this.list = list;
        this.countDownLatch = countDownLatch;
    }
 
    @Override
    public Integer call() throws Exception {
        //记录插入成功的条数
        int rows = 0;
        for (UserInfo user :
                list) {
            mapper.insert(user);
            rows++;
        }
 
        //完全插入成功，CountDownLatch-1
        if (rows == list.size()) {
            countDownLatch.countDown();
        }
 
        return rows;
    }
}

 

Service层:

/**
 * Copyright (C), 2017-2022, 湖南兴盛优选电子商务有限公司.
 *
 * @author 13240
 * @description 针对表【user_info】的数据库操作Service实现
 * @createDate 2022-07-27 10:39:08
 */
@Slf4j
@Service
public class UserInfoServiceImpl extends ServiceImpl<UserInfoMapper, UserInfo>
        implements UserInfoService {
 
    @Autowired
    UserInfoMapper mapper;
 
    @Override
    public Integer insertList(Integer size) {
        //制造数据，list里面有size(5000)个UserInfo
        List<UserInfo> allUser = this.createSizeList(size);
        //线程数
        int threadSize = ?;
 
        ThreadPoolExecutor threadPoolExecutor = new ThreadPoolExecutor(threadSize, threadSize, 10, TimeUnit.SECONDS, new LinkedBlockingQueue<>());
        //每个线程处理的数据量
        final Integer dataPartionLength = (allUser.size() + (threadSize) - 1) / (threadSize);
 
        int downLatchSize = 0;
        //将downLatchSize这个值计算出来
        if (size%dataPartionLength==0){
           downLatchSize=size/dataPartionLength;
        }else {
            downLatchSize=size/dataPartionLength+1;
        }
 
        CountDownLatch latch = new CountDownLatch(downLatchSize);
        long startTime = System.currentTimeMillis();
        for (int i = 0; i < downLatchSize; i++) {
            //每个线程均分需要处理的数据
            List<UserInfo> threadDatas = allUser.stream()
                 .skip(i * dataPartionLength).limit(dataPartionLength).collect(Collectors.toList());
 
            //调用实现了Callable的InsertTarget类，并将mapper，threadDatas，latch传进去
            InsertTarget insertTarget = new InsertTarget(mapper, threadDatas, latch);
            FutureTask<Integer> futureTask = new FutureTask<>(insertTarget);
 
            //执行任务
            threadPoolExecutor.execute(futureTask);
        }
        try {
            //保证每个线程完成后再完成计时
            latch.await();
            long endTime = System.currentTimeMillis();
            log.info("线程数为{}的线程池插入5000用时:{}",threadSize, endTime - startTime);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        return 200;
    }
 
    /**
     * 用来生成数据，返回一个size大小的list
     */
    private List<UserInfo> createSizeList(Integer size) {
        ArrayList<UserInfo> list = new ArrayList<>(100);
 
        //生成size个UserInfo，并放进List
        for (int i = 0; i < size; i++) {
            UserInfo userInfo = new UserInfo();
            userInfo.setId(String.valueOf(i + 1));
            list.add(userInfo);
        }
        return list;
    }
}

 

耗时:

多线程提交修改时，尝试了不同线程数对插入速度的影响，具体可以看下面表格

核心线程数	1	4	5	10	15
耗时	6327	3847	3036	5230	4630

结论：

多线程对数据库进行操作时，并非线程数越多操作越快。