Java百万数据导出Excel性能优化

https://zhuanlan.zhihu.com/p/533753443

用Java导出百万数据到excel中，需要注意一些事项，一是Excel 2007及以上版本(.xlsx)，最大支持单Sheet 一百万(1048576)行，如果要在一个Excel文件中写入大于1百万行的数据，则需要每百万行创建一个Sheet，二是数据一般是从数据库中查出来的，如果一次查出太多数据内存可能装不下，需要分页查询*或流式查询导出。主要代码类似如下，此处使用的easyexcel库写入excel，分页查询并写入，每一百万行新建一个Sheet。

先读后写

在导出的数据量比较多的时候(分页查询有很多页)，上述代码会比较慢，一是因为此代码写入时会阻塞下一页的查询，如果每次写入需要0.1秒，百万数据的导出，分页查询每页1万条，则有近10秒的时间浪费了；二是，查询性能通常数倍慢于写入速度，查询一般都经过网络，网络耗时+SQL执行耗时比较多，而写入一般是直接写入到本地磁盘中（或先写入本地临时文件，再通过IO流输出到HTTP响应中），所以上述代码有两个性能优化的点，一是读写分离，写入时不要影响到下一页的读，二是，可以改为并发分页查询*，减少因为写入太快而需要等待查询数据的时间；或改用流式查询。

读(并发)同时写

重要：使用分页查询需要注意深分页问题，流式查询需要注意长时间占用数据库连接的问题。可以通过“炸了！使用 MyBatis 查询千万数据量？”这篇文章了解。

---

代码实现：[easyexcel、mybatis-plus、java8+、lombok]；

第一版：分页-先读后写版 *第三版只依赖java8+easyexcel

使用示例：

@RestController
public class ExcelController {
    @Autowired
    private DemoService demoService;

    // FIXME 实际应用中，pageSize不应对外开放，此处为方便测试，或pageSize需要设置上限，防止恶意用户传一个很大的pageSize值，而撑满内存。
    @GetMapping("/export")
    public void export(HttpServletResponse response,
                       @RequestParam(value = "pageSize", defaultValue = "10000") int pageSize) throws IOException {
       ExcelUtil.download(response, "测试")
                .pageExcelWriter(Demo.class, page -> demoService.lambdaQuery().page(page.setSize(pageSize)));
    }
}

日志输出：

先读后写版(查询和写入用时差距不大是因为读写在同一台电脑上)（MySQL）

第二版：读写分离版（并发读）：[阻塞队列，线程池]，[easyexcel、mybatis-plus、java8+、lombok]

通过定义一个阻塞队列，在主线程中新起一个线程用于查询([并发]查询时将查询结果放入队列中)，写入时直接从队列中进行获取待写入数据，实现读写分离和降低写入时等待查询数据的时间，从而降低总体用时。

使用示例：

@RestController
public class ExcelController {
    @Autowired
    private DemoService demoService;
    // FIXME 实际应用中，pageSize和queueSize参数不应对外开放，此处为方便测试，或pageSize需要设置上限，防止恶意用户传一个很大的pageSize值，而撑满内存。
    @GetMapping("/exportFast")
    public void exportFast(HttpServletResponse response,
                            @RequestParam(value = "parallelNum", defaultValue = "4") int parallelNum,
                            @RequestParam(value = "pageSize", defaultValue = "10000") int pageSize) throws IOException {
        ExcelUtil.download(response, "测试")
                .parallel(parallelNum)
                .pageExcelWriter(Demo.class, page -> demoService.lambdaQuery().page(page.setSize(pageSize)));
    }
}

代码实现：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.function.UnaryOperator;

/**
 * Excel工具类
 * @author www@yiynx.cn
 */
@Slf4j
public class ExcelUtil {
    public static final Integer EXCEL_SHEET_ROW_MAX_SIZE = 1000001; // excel sheet最大行数(算标题)
    private static final long DEF_PAGE_SIZE = 1000; // 默认页大小
    private static final int DEF_PARALLEL_NUM = Math.min(Runtime.getRuntime().availableProcessors(), 4);
    private HttpServletResponse httpServletResponse;
    private boolean parallel;
    private int parallelNum;

    private ExcelUtil() {}

    public static ExcelUtil download(HttpServletResponse response, String excelFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(excelFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        ExcelUtil excelUtil = new ExcelUtil();
        excelUtil.httpServletResponse = response;
        return excelUtil;
    }

    public ExcelUtil parallel() {
        this.parallel = true;
        this.parallelNum = DEF_PARALLEL_NUM;
        return this;
    }

    public ExcelUtil parallel(int parallelNum) {
        this.parallel = true;
        this.parallelNum = parallelNum;
        return this;
    }

    public void pageExcelWriter(Class head, UnaryOperator<Page> pageFunction) throws IOException {
        if (parallel) {
            pageExcelWriterParallel(head, parallelNum, pageFunction);
        } else {
            pageExcelWriter(httpServletResponse.getOutputStream(), head, pageFunction);
        }
    }

    public static void pageExcelWriter(OutputStream outputStream, Class head, UnaryOperator<Page> pageFunction) {
        long start = System.currentTimeMillis();
        log.debug("excel writer start");
        try (ExcelWriter excelWriter = EasyExcel.write(outputStream, head).build()) {
            Page page = null;
            WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
            do {
                long pageSearchStartTime = System.currentTimeMillis();
                page = pageFunction.apply(page == null ? new Page(1, DEF_PAGE_SIZE) : new Page(page.getCurrent() + 1, page.getSize(), page.getTotal(), false)); // 分页查询
                long pageExcelWriteStartTime = System.currentTimeMillis();
                writeSheet.setSheetNo((int) (page.getCurrent() * page.getSize() / EXCEL_SHEET_ROW_MAX_SIZE));
                excelWriter.write(page.getRecords(), writeSheet); // excel写入数据
                log.debug("total:{}, pageSize:{}, totalPage:{}, pageNo:{}, sheetNo:{}, pageSearchTime:{}ms, pageExcelWriterTime:{}ms", page.getTotal(), page.getSize(), page.getPages(), page.getCurrent(), writeSheet.getSheetNo(), pageExcelWriteStartTime - pageSearchStartTime, System.currentTimeMillis() - pageExcelWriteStartTime);
            } while (page.hasNext()); // 是否还有下一页
        }
        log.debug("excel writer done, totalTime:{}ms", System.currentTimeMillis() - start);
    }

    private void pageExcelWriterParallel(Class head, UnaryOperator<Page> pageFunction) throws IOException {
        pageExcelWriterParallel(httpServletResponse.getOutputStream(), head, parallelNum, pageFunction);
    }

    private void pageExcelWriterParallel(Class head, int parallelNum, UnaryOperator<Page> pageFunction) throws IOException {
        pageExcelWriterParallel(httpServletResponse.getOutputStream(), head, parallelNum, pageFunction);
    }

    public static void pageExcelWriterParallel(OutputStream outputStream, Class head, int parallelNum, UnaryOperator<Page> pageFunction) {
        try (ExcelWriter excelWriter = EasyExcel.write(outputStream, head).build()) {
            final WriteSheet writeSheet = EasyExcel.writerSheet(0, "Sheet0").build();
            Page firstPage = pageFunction.apply(new Page<>(1, DEF_PAGE_SIZE)); // 查首页
            Parallel.parallel(Page.class, parallelNum, firstPage.getPages()).asyncProducer(pageNo -> {
                log.debug("[读]pageNo:{}, total:{}, pageSize:{}, totalPage:{}", pageNo, firstPage.getTotal(), firstPage.getSize(), firstPage.getPages());
                if (pageNo == firstPage.getCurrent()) {return firstPage;}  // 第一页直接返回
                return pageFunction.apply(new Page<>(pageNo, firstPage.getSize(), firstPage.getTotal(), false));
            }).syncConsumer(page -> {
                writeSheet.setSheetNo((int) (page.getCurrent() * page.getSize() / EXCEL_SHEET_ROW_MAX_SIZE));
                writeSheet.setSheetName("Sheet" + writeSheet.getSheetNo());
                log.debug("[写]pageNo:{}, total:{}, pageSize:{}, totalPage:{}, sheetNo:{}", page.getCurrent(), page.getTotal(), page.getSize(), page.getPages(), writeSheet.getSheetNo());
                excelWriter.write(page.getRecords(), writeSheet);
            }).start();
        }
    }
}

并发生产数据，串行消费数据[有序] - Java工具类 - 知乎 (zhihu.com)

其它优化项：

1、因查询第一页时已经count查询了总记录数，之后的分页查询不再count查询总记录数
2、因读写分离需要创建一个新线程用于查询，如果只有一页数据时，没有必要使用读写分离的方式。
3、可调优的参数是：分页查询的页大小，队列&并发线程数......
4、并发分页查询，结果有序写入excel。

日志输出：

读写分离（并发读）（*MySQL limit未优化）

第三版：只依赖Java8+和EasyExcel，移除mybatis-plus、lombok依赖

代码实现：EasyExcel读写简化 - Java工具类 - 知乎 (zhihu.com)

使用示例：

@GetMapping("/writeExcelForParallel")
public void writeExcelForParallel(HttpServletResponse response,
       @RequestParam(value = "parallelNum", defaultValue = "3") int parallelNum,
       @RequestParam(value = "pageSize", defaultValue = "10000") int pageSize) throws IOException, InterruptedException {
    LambdaQueryWrapper<Demo> queryWrapper = Wrappers.lambdaQuery(Demo.class);
    demoService.writeExcelForParallel(excelOutput(response, "测试"), parallelNum, pageSize, queryWrapper);
}

四、流式查询导出

流式查询

mybatis-plus 3.5.4 + 流式查询

五、ExcelUtil使用示例项目地址：https://gitee.com/yiynx/example

---

六、测试：104万数据，导出excel用时由59秒优化到19秒

测试数据

pageSize：10万（MySQL）

page/fetchSize：2万（MySQL）

pageSize：1万(PgSQL)

*MySQL比较适合流式查询导出（分页查询导出速度受深分页问题影响比较大），PgSQL、Oracle深分页问题影响较小，流式查询导出和分页查询导出都可以

七、其它注意事项：

1、导出过程中会较多占用CPU、内存、磁盘，需全局对Excel导出限流，防止同时对大量数据进行导出，导致内存、磁盘、CPU使用率过高等问题。

参考：磁盘空间不足时poi-sheet.xml临时文件清理失败 · Issue #2878 · alibaba/easyexcel · GitHub

2、导出时直接向HttpServletResponse写入的方式，在数据量比较大，耗时较久的情况下，用户体验并不是很好且容易超时，可考虑做导出下载中心功能（优势：限流、导出历史记录、进度条、可中断任务等）。

参考：SaaS产品在什么情况下需要做「导出下载中心」功能？

3、分页查询如果使用了非唯一字段排序，可能会出现数据重复的问题：MySQL查询时，ORDER BY 后面根据时间(TIMESTAMP)排序并做翻页(LIMIT)处理经常出现顺序紊乱和导致前后页的数据重复问题调查及解决-优快云博客

其它：

ExcelUtil.read使用代码示例

转转技术团队：性能提升 2000%！揭秘 MyBatis-Plus 批量插入的终极优化技巧