导出excel 大数据量分页_线程池分页接口优化

干活：

原来的导出是分页列表+详情查询+参数包装+poi进行导出，然后数据量大起来后，批量导出接口一直等待超时；线上查看耗时26秒；

原因：pagesize=1000;每一条数据调用了三个provider，各执行一到两条不等sql,查看数据库已加唯一索引，是因为sql请求过多在成数据响应缓慢；

优化：

1.异步下载，先发起导出请求，后台查询数据，生成excel,再通知页面进行excel文件下载；

2.开启10个线程，每个线程查询100条数据，然后合并结果写入excel进行导出；

• 方法如下：

public class businessThread implements Callable<List<BusinessExportResponseVO>> {
    
    //注入请求对象
    private AllUserInfoDTO allUserInfoDTO;
    private BusinessByAreaPageRequestVO requestVO;
    //注入调用方法(使用构造注入,不用注解)
    private ITradeListService tradeListService;

    //构造注入依赖
    public businessThread(AllUserInfoDTO allUserInfoDTO, BusinessByAreaPageRequestVO requestVO, ITradeListService tradeListService) {
        this.requestVO = requestVO;
        this.allUserInfoDTO = allUserInfoDTO;
        this.tradeListService = tradeListService;
    }

    @Override
    public List<BusinessExportResponseVO> call() {
        List<BusinessExportResponseVO> dataList = new LinkedList<>();
        try {
            ResponsePageVO<BusinessPageResponseVO> responsePageDTO = tradeListService.selectBusinessPageByCondition(allUserInfoDTO, requestVO);
            CollectionCopyUtils.copyProperties(responsePageDTO.getData(), dataList, BusinessExportResponseVO.class);
        } catch (Exception e) {
            LoggerUtil.info(log, LoggerMarker.BUSINESS, "批量查询交易户异常:{}", e.toString());
        }
        return Arrays.asList(new BusinessExportResponseVO());
    }
}

服务：

public List<BusinessExportResponseVO> callThreadExcel(BusinessByAreaPageRequestVO vo) {
    AllUserInfoDTO allUserInfoDTO = userHelper.getUserInfo();
    List<BusinessExportResponseVO> result = new ArrayList<>();
    List<Callable<List<BusinessExportResponseVO>>> tasks = new ArrayList<>();

    //分页查询用户列表，10个线程,一共10页,每页100条数据
    for (int i = 1; i < 11; i++) {
        BusinessByAreaPageRequestVO requestVO = new BusinessByAreaPageRequestVO();
        BeanUtil.copyProperties(vo, requestVO);
        requestVO.setCurrentPage(i);
        requestVO.setPageSize(100);
        //构造注入请求参数,执行业务逻辑，存储到线程队列，触发后执行
        Callable<List<BusinessExportResponseVO>> callBusiness= new businessThread(allUserInfoDTO, requestVO, tradeListService);
        tasks.add(callBusiness);
    }
    //创建线程池
    ExecutorService exService = Executors.newFixedThreadPool(10);
    try {
        //开启多线程执行任务
        List<Future<List<BusinessExportResponseVO>>> futures = exService.invokeAll(tasks);
        if (CollectionUtils.isNotEmpty(futures)) {
            for (Future<List<BusinessExportResponseVO>> future : futures) {
                result.addAll(future.get());
            }
        }
    } catch (Exception e) {
        LoggerUtil.info(log, LoggerMarker.BUSINESS, "多线程->批量查询交易户异常:{}", e.toString());
    } finally {
        threadPoolExecutor.shutdown();
    }
    if (log.isInfoEnabled()) {
        log.info("开启多线程查询导出数 共:{}条", result.size());
    }
    return result;
}

调用：

@PostMapping("/exportCompareData")
@ApiOperation(value = "导出交易户列表数据")
public void exportCompareData(HttpServletResponse response, @RequestBody BusinessByAreaPageRequestVO vo) throws Exception {
    LoggerUtil.info(log, LoggerMarker.BUSINESS, "0-1 交易户导出,vo={}", JSON.toJSONString(vo));
    List<BusinessExportResponseVO> responseVOS = callThreadExcel(vo);
    LoggerUtil.info(log, LoggerMarker.BUSINESS, "0-2 交易户导出,responseVOS={}", JSON.toJSONString(responseVOS.size()));
    tradeListService.exportCompareData(response, responseVOS);
}

结果：1000条数据查询包装加写入excel耗时3秒

验证：

2020-12-16 09:40:00.326 INFO [TID: N/A] c.a.b.f.a.c.t.TradeListController info:308 BUSINESS 0-1 交易户导出,vo={"currentPage":0,"offset":0,"pageSize":20}

2020-12-16 09:40:03.384 INFO [TID: N/A] c.a.b.f.a.c.t.TradeListController callThreadExcel:280  开启多线程查询导出数 共:1000条

20201216补充===========================================

参考：Java高并发编程中ForkJoinPool的使用及详细介绍-刘宇_刘宇的博客-CSDN博客

public List<BusinessExportResponseVO> callThreadExcel(BusinessByAreaPageRequestVO requestVO) {
    List<BusinessExportResponseVO> result = new ArrayList<>();
    AllUserInfoDTO allUserInfoDTO = userHelper.getUserInfo();
    requestVO.setCurrentPage(1);
    requestVO.setPageSize(1000);
    ForkJoinPool pool = new ForkJoinPool();
    try {
        ForkJoinTask<List<BusinessExportResponseVO>> forkJoinTask = pool.submit(new businessExcelThread(allUserInfoDTO, requestVO, tradeListService));
        result = forkJoinTask.get();
    } catch (Exception e) {
        log.info("开启多线程查询异常：,{}", JSON.toJSONString(e.toString()));
        e.printStackTrace();
    }finally {
        pool.shutdown();
    }
    log.info("开启多线程查询导出数 共:{}条", result.size());
    return result;
}

public class businessExcelThread extends RecursiveTask<List<BusinessExportResponseVO>> {
    private AllUserInfoDTO allUserInfoDTO;
    private BusinessByAreaPageRequestVO requestVO;
    private ITradeListService tradeListService;

    //每页查询最大条数
    private int maxPageSize=100;

    //带参构造注入待执行对象和方法
    public businessExcelThread(AllUserInfoDTO allUserInfoDTO, BusinessByAreaPageRequestVO requestVO, ITradeListService tradeListService) {
        this.requestVO = requestVO;
        this.allUserInfoDTO = allUserInfoDTO;
        this.tradeListService = tradeListService;
    }

    @Override
    protected List<BusinessExportResponseVO> compute() {
        List<BusinessExportResponseVO> dataList = new LinkedList<>();
        if (requestVO.getPageSize() <= maxPageSize) {
            ResponsePageVO<BusinessPageResponseVO> responsePageDTO = tradeListService.selectBusinessPageByCondition(allUserInfoDTO, requestVO);
            CollectionCopyUtils.copyProperties(responsePageDTO.getData(), dataList, BusinessExportResponseVO.class);
            return dataList;
        } else {
            for (int i = 1; i < 11; i++) {
                requestVO.setCurrentPage(i);
                requestVO.setPageSize(maxPageSize);
                businessExcelThread task = new businessExcelThread(allUserInfoDTO, requestVO, tradeListService);
                task.fork();
                dataList.addAll(task.join());
            }
            return dataList;
        }
    }
}

验证：

2020-12-16 09:37:07.202 INFO [TID: N/A] c.a.b.f.a.c.t.TradeListController info:308 BUSINESS 0-1 交易户导出,vo={"currentPage":0,"offset":0,"pageSize":20}

2020-12-16 09:37:21.741 INFO [TID: N/A] c.a.b.f.a.c.t.TradeListController callThreadExcel:189  开启多线程查询导出数 共:1000条