接口优化方案

一、sql使用in查询时  in的值最好使用set去重

Set<String> memberIds = list.stream().map(ExportBathCustomerTemplateExcelDto::getMemberId).collect(Collectors.toSet());

二、不要在循环里、查询/新增/修改数据库 可以在循环外建立几个List  批量操作

List<CustomerBind> bingUpdateList = new ArrayList<>();

List<CustomerBind> bingSaveList = new ArrayList<>();

List<Transmit> transmitSaveList = new ArrayList<>();

List<String> customerUpateList = new ArrayList<>();
for (ExportBathCustomerTemplateExcelDto vo : list) {

        //业务操作 把需要单个新增/修改操作放到List集合内  在循环外批量操作

        bingUpdateList.add(bindVO);
        bingSaveList.add(bindVO);    

        transmitSaveList.add(transmit);
        customerUpateList.add(vo.getMemberId());

}

//批量操作

customerBindService.updateBatchById(bindList);

customerBindService.saveBatch(bindList);

transmitService.saveBatch(tranList);

三、使用线程池异步处理数据

List<CrmCustomerBind> bingUpdateList = new ArrayList<>();
List<CrmCustomerBind> bingSaveList = new ArrayList<>();
List<CrmTransmit> transmitSaveList = new ArrayList<>();
List<String> customerUpateList = new ArrayList<>();            
for (ExportBathCustomerTemplateExcelDto vo : list) {
   bingUpdateList.add(bindVO);
   bingSaveList.add(bindVO);
   transmitSaveList.add(transmit);
   customerUpateList.add(vo.getMemberId());
}
//执行批量操作
List<List<CrmCustomerBind>> updateList = SplitListUtils.split(bingUpdateList, 200);
List<List<CrmCustomerBind>> saveList = SplitListUtils.split(bingSaveList, 200);
List<List<CrmTransmit>> tranSaveList = SplitListUtils.split(transmitSaveList, 200);
if (updateList != null && updateList.size() > 0){
    updateList.forEach(obj ->{
        CompletableFuture<Void> cf = CompletableFuture.runAsync(() -> updateBatchBind(obj), threadPoolTaskExecutor);
    });
}
if (saveList != null && saveList.size() > 0){
    saveList.forEach(obj ->{
        CompletableFuture<Void> cf = CompletableFuture.runAsync(() -> saveBatchBind(obj), threadPoolTaskExecutor);
    });
}
if (tranSaveList != null && tranSaveList.size() > 0){
    tranSaveList.forEach(obj ->{
        CompletableFuture<Void> cf = CompletableFuture.runAsync(() -> saveBatchTran(obj), threadPoolTaskExecutor);
    });
}

void updateBatchBind(List<CrmCustomerBind> bindList){
    crmCustomerBindService.updateBatchById(bindList);
}
void saveBatchBind(List<CrmCustomerBind> bindList){
    crmCustomerBindService.saveBatch(bindList);
}
void saveBatchTran(List<CrmTransmit> tranList){
    crmTransmitService.saveBatch(tranList);
}

线程bean引入

@Autowired
@Qualifier(value = "myExecutor")
private Executor threadPoolTaskExecutor;

package com.cim.panel.config;

import lombok.Data;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.util.concurrent.Executor;
import java.util.concurrent.ThreadPoolExecutor;

/**
 * @author wsp
 * @date 2023/3/15 14:36
 */
@Configuration
@Data
public class ExecutorConfig {
    /**
     * 核心线程
     */
    @Value("${spring.task.execution.pool.core-size}")
    private int corePoolSize;
    /**
     * 最大线程
     */
    @Value("${spring.task.execution.pool.max-size}")
    private int maxPoolSize;
    /**
     * 队列容量
     */
    @Value("${spring.task.execution.pool.queue-capacity}")
    private int queueCapacity;
    /**
     * 保持时间
     */
    @Value("${spring.task.execution.pool.keep-alive}")
    private int keepAliveSeconds;
    /**
     * 名称前缀
     */
    @Value("${spring.task.execution.pool.core-size}")
    private String preFix;

    @Bean("myExecutor")
    public Executor myExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(corePoolSize);
        executor.setMaxPoolSize(maxPoolSize);
        executor.setQueueCapacity(queueCapacity);
        executor.setKeepAliveSeconds(keepAliveSeconds);
        executor.setThreadNamePrefix(preFix);
        executor.setRejectedExecutionHandler( new ThreadPoolExecutor.AbortPolicy());
        executor.initialize();
        return executor;
    }
}

yml配置文件添加配置

spring:
  task:
    execution:
      pool:
        max-size: 50
        core-size: 10
        keep-alive: 3
        queue-capacity: 1000
        thread-name-prefix: name

工具类

package com.cim.panel.utils;

import com.google.common.collect.Lists;
import org.apache.commons.collections4.CollectionUtils;

import java.util.List;

/**
 * 拆分结合工具类
 *
 * @author myl
 */
public class SplitListUtils {

    /**
     * 将一个大集合拆分成多个小集合
     *
     * @param <T>           泛型对象
     * @param resList       需要拆分的集合
     * @param subListLength 每个子集合的元素个数
     * @return 返回拆分后的各个集合组成的列表
     * 代码里面用到了guava和common的结合工具类
     **/
    public static <T> List<List<T>> split(List<T> resList, int subListLength) {

        if (CollectionUtils.isEmpty(resList) || subListLength <= 0) {
            return Lists.newArrayList();
        }
        List<List<T>> ret = Lists.newArrayList();
        int size = resList.size();
        if (size <= subListLength) {
            // 数据量不足 subListLength 指定的大小
            ret.add(resList);
        } else {
            int pre = size / subListLength;
            int last = size % subListLength;
            // 前面pre个集合，每个大小都是 subListLength 个元素
            for (int i = 0; i < pre; i++) {
                List<T> itemList = Lists.newArrayList();
                for (int j = 0; j < subListLength; j++) {
                    itemList.add(resList.get(i * subListLength + j));
                }
                ret.add(itemList);
            }
            // last的进行处理
            if (last > 0) {
                List<T> itemList = Lists.newArrayList();
                for (int i = 0; i < last; i++) {
                    itemList.add(resList.get(pre * subListLength + i));
                }
                ret.add(itemList);
            }
        }
        return ret;
    }
}

三、