大数据量 Excel 导出性能优化SXSSFWorkbook 流式写入实战一、问题背景导出10万行数据到 Excel 时常见的性能问题问题原因后果内存溢出OOM所有行对象同时存在堆内存中服务崩溃导出慢20秒大对象创建 GC 频繁 ZIP 压缩用户等待超时并发导出打垮服务多个请求同时占用大量内存整个服务不可用数据库压力一次性查询全部数据慢查询、连接池耗尽注博客https://blog.csdn.net/badao_liumang_qizhi二、POI 的三种 Workbook 对比2.1 HSSFWorkbook.xls 格式文件格式Excel 97-2003.xls行数上限65536 行内存模型全部在内存适用小数据量、兼容旧系统2.2 XSSFWorkbook.xlsx 格式文件格式Excel 2007.xlsx本质是 ZIP 包裹的 XML行数上限1048576 行内存模型所有行的 DOM 对象全部在堆内存中内存公式行数 × 列数 × 每个Cell对象大小(约200~500字节)12万行 × 15列 × 300字节 ≈ 540MB 堆内存2.3 SXSSFWorkbook流式 .xlsx文件格式同 .xlsx行数上限同 1048576 行内存模型滑动窗口只保留最近 N 行在内存超出的自动刷到磁盘临时文件内存公式窗口大小 × 列数 × 每个Cell大小窗口200行 × 15列 × 300字节 ≈ 900KB 堆内存几乎忽略不计2.4 对比表指标XSSFWorkbookSXSSFWorkbook12万行内存占用300~500MB5~20MB12万行生成耗时8~15秒3~6秒并发5个导出OOM 风险正常是否支持读取已写的行✅ 可以随机访问❌ 已刷出的行不可再访问是否支持单元格样式✅ 完整支持⚠️ 有限支持窗口内可设是否支持合并单元格✅⚠️ 需在窗口内操作资源清理自动GC需手动调用dispose()三、SXSSFWorkbook 工作原理创建 SXSSFWorkbook(windowSize200) │ ├── 写入第 1 行 → 内存中保留 ├── 写入第 2 行 → 内存中保留 ├── ... ├── 写入第 200 行 → 内存中保留窗口已满 ├── 写入第 201 行 → 第 1 行从内存刷到磁盘临时文件 ├── 写入第 202 行 → 第 2 行从内存刷到磁盘临时文件 ├── ... ├── 写入第 120000 行 → 第 119800 行刷出 │ │ 此时内存中只有第 119801~120000 行200行 │ 磁盘临时文件中有第 1~119800 行 │ ├── workbook.write(outputStream) │ → 将内存中的行 临时文件合并 │ → 压缩为 ZIP.xlsx │ → 输出到 OutputStream │ └── workbook.dispose() → 删除磁盘临时文件3.1 临时文件位置默认在java.io.tmpdir通常是/tmp或C:\Users\xxx\AppData\Local\Temp。可以自定义SXSSFWorkbookworkbooknewSXSSFWorkbook(newXSSFWorkbook(),200,true,true// compressTmpFilestrue 压缩临时文件);3.2 窗口大小选择窗口大小内存占用适用场景100极小纯数据导出无需回溯200~500小一般业务导出1000中等需要在近期行内做合并、样式等操作-1无限等同 XSSFWorkbook不推荐四、分页查询的必要性即使用了 SXSSFWorkbook 解决了写入端的内存问题如果一次性从数据库查出12万条数据这些 Java 对象仍然全部在堆内存中12万条 × 每条约1KB 120MB 堆内存仅数据对象分页查询将这 120MB 分摊到多次查询中每次只有 5000 条~5MB在内存中第1次查询5000条 → 写入 Excel → 被 GC 回收 第2次查询5000条 → 写入 Excel → 被 GC 回收 ... 第24次查询5000条 → 写入 Excel → 被 GC 回收总内存峰值 5000条数据对象 SXSSFWorkbook 窗口 ≈10~20MB五、完整示例5.1 实体packagecom.example.entity;importcom.baomidou.mybatisplus.annotation.IdType;importcom.baomidou.mybatisplus.annotation.TableId;importcom.baomidou.mybatisplus.annotation.TableName;importlombok.Data;importjava.util.Date;DataTableName(order_record)publicclassOrderRecord{TableId(typeIdType.AUTO)privateLongid;privateStringorderCode;privateStringproductName;privateStringcustomerName;privateIntegerquantity;privateDoubleamount;privateStringstatus;privateDatecreateTime;}5.2 Mapperpackagecom.example.mapper;importcom.baomidou.mybatisplus.core.mapper.BaseMapper;importcom.example.entity.OrderRecord;importorg.apache.ibatis.annotations.Mapper;MapperpublicinterfaceOrderRecordMapperextendsBaseMapperOrderRecord{}5.3 导出 Service优化版packagecom.example.service;importcom.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;importcom.baomidou.mybatisplus.extension.plugins.pagination.Page;importcom.example.entity.OrderRecord;importcom.example.mapper.OrderRecordMapper;importjakarta.annotation.Resource;importjakarta.servlet.http.HttpServletResponse;importjava.io.OutputStream;importjava.net.URLEncoder;importjava.nio.charset.StandardCharsets;importjava.text.SimpleDateFormat;importjava.time.LocalDate;importjava.time.format.DateTimeFormatter;importjava.util.List;importlombok.extern.slf4j.Slf4j;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.Font;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.xssf.streaming.SXSSFWorkbook;importorg.springframework.stereotype.Service;/** * 大数据量导出服务. */Slf4jServicepublicclassExportService{/** 流式写入窗口大小. */privatestaticfinalintSXSSF_WINDOW_SIZE200;/** 每次分页查询的条数. */privatestaticfinalintEXPORT_PAGE_SIZE5000;/** 表头. */privatestaticfinalString[]HEADERS{订单编号,商品名称,客户名称,数量,金额,状态,创建时间};ResourceprivateOrderRecordMapperorderRecordMapper;/** * 导出订单数据流式写入 分页查询. * * param status 筛选状态可选 * param response HTTP响应 */publicvoidexportOrders(Stringstatus,HttpServletResponseresponse){longstartTimeSystem.currentTimeMillis();// 创建流式 WorkbookSXSSFWorkbookworkbooknewSXSSFWorkbook(SXSSF_WINDOW_SIZE);try{Sheetsheetworkbook.createSheet(订单数据);// 写入表头 CellStyleheaderStylecreateHeaderStyle(workbook);RowheaderRowsheet.createRow(0);for(inti0;iHEADERS.length;i){CellcellheaderRow.createCell(i);cell.setCellValue(HEADERS[i]);cell.setCellStyle(headerStyle);}// 分页查询并逐批写入 SimpleDateFormatsdfnewSimpleDateFormat(yyyy-MM-dd HH:mm:ss);intpageNum1;introwIndex1;inttotalExported0;while(true){// 构造查询条件LambdaQueryWrapperOrderRecordwrappernewLambdaQueryWrapper();wrapper.eq(status!null,OrderRecord::getStatus,status);wrapper.orderByDesc(OrderRecord::getCreateTime);// 分页查询PageOrderRecordpagenewPage(pageNum,EXPORT_PAGE_SIZE);PageOrderRecordpageResultorderRecordMapper.selectPage(page,wrapper);ListOrderRecordrecordspageResult.getRecords();// 无数据则结束if(records.isEmpty()){break;}// 写入当前批次的数据for(OrderRecordrecord:records){Rowrowsheet.createRow(rowIndex);row.createCell(0).setCellValue(record.getOrderCode());row.createCell(1).setCellValue(record.getProductName());row.createCell(2).setCellValue(record.getCustomerName());row.createCell(3).setCellValue(record.getQuantity()!null?record.getQuantity():0);row.createCell(4).setCellValue(record.getAmount()!null?record.getAmount():0);row.createCell(5).setCellValue(record.getStatus());row.createCell(6).setCellValue(record.getCreateTime()!null?sdf.format(record.getCreateTime()):);}totalExportedrecords.size();// 最后一页不足一页则结束if(records.size()EXPORT_PAGE_SIZE){break;}pageNum;}// 设置响应头并输出 StringdateStrLocalDate.now().format(DateTimeFormatter.ofPattern(yyyyMMdd));StringfileName订单导出-dateStr.xlsx;response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setHeader(Content-Disposition,attachment;filenameURLEncoder.encode(fileName,StandardCharsets.UTF_8));OutputStreamosresponse.getOutputStream();workbook.write(os);os.flush();longcostSystem.currentTimeMillis()-startTime;log.info(导出完成共{}条数据耗时{}ms,totalExported,cost);}catch(Exceptione){log.error(导出异常,e);thrownewRuntimeException(导出失败);}finally{// 必须调用 dispose() 清理临时文件workbook.dispose();}}/** * 创建表头样式. */privateCellStylecreateHeaderStyle(SXSSFWorkbookworkbook){CellStylestyleworkbook.createCellStyle();Fontfontworkbook.createFont();font.setBold(true);style.setFont(font);returnstyle;}}5.4 Controllerpackagecom.example.controller;importcom.example.service.ExportService;importjakarta.annotation.Resource;importjakarta.servlet.http.HttpServletResponse;importorg.springframework.web.bind.annotation.GetMapping;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RequestParam;importorg.springframework.web.bind.annotation.RestController;RestControllerRequestMapping(/api/order)publicclassOrderController{ResourceprivateExportServiceexportService;GetMapping(/export)publicvoidexport(RequestParam(requiredfalse)Stringstatus,HttpServletResponseresponse){exportService.exportOrders(status,response);}}六、关键注意事项6.1 必须调用 dispose()SXSSFWorkbook 在磁盘上创建了临时文件如果不调用dispose()临时文件会一直留在磁盘上最终耗尽磁盘空间。SXSSFWorkbookworkbooknewSXSSFWorkbook(200);try{// ... 写入和输出}finally{workbook.dispose();// 删除临时文件}不能用 try-with-resourcesSXSSFWorkbook.close()不会自动调用dispose()需要显式调用。6.2 不能回溯已刷出的行// 错误已超出窗口的行不能再访问Rowrow0sheet.getRow(0);// 如果第0行已被刷出返回 nullrow0.createCell(5).setCellValue(修改);// NullPointerException如果需要修改表头样式确保在写数据之前完成表头在窗口内。6.3 合并单元格的限制// 合并单元格需要在窗口范围内操作// 如果跨度超过窗口大小如合并第1行到第300行不可行sheet.addMergedRegion(newCellRangeAddress(0,0,0,5));// 只合并表头列没问题6.4 分页查询的边界条件// 判断是否还有下一页if(records.size()EXPORT_PAGE_SIZE){break;// 最后一页不满一页说明没有更多数据了}不要用pageResult.getTotal()来判断因为每次都执行 COUNT 会额外增加查询开销。如果确实不想每次都 COUNT// 禁用 COUNT 查询提升分页查询性能PageOrderRecordpagenewPage(pageNum,EXPORT_PAGE_SIZE,false);6.5 导出过程中数据变化分页导出期间如果数据被修改新增/删除可能出现重复数据新插入的行被后续分页查到遗漏数据删除的行导致分页偏移解决方案按主键 ID 范围查询而非 OFFSET 分页LonglastId0L;while(true){wrapper.gt(OrderRecord::getId,lastId);wrapper.last(LIMIT EXPORT_PAGE_SIZE);ListOrderRecordrecordsorderRecordMapper.selectList(wrapper);if(records.isEmpty())break;lastIdrecords.get(records.size()-1).getId();// 写入...}七、性能基准测试参考数据量XSSFWorkbookSXSSFWorkbook 分页提升倍数1万行2s / 50MB内存1s / 5MB内存2x / 10x5万行7s / 200MB3s / 15MB2.3x / 13x12万行15s / 450MB5s / 20MB3x / 22x50万行OOM18s / 25MB∞100万行OOM35s / 30MB∞八、总结优化策略 SXSSFWorkbook解决写入端内存 分页查询解决查询端内存 ┌── 写入端 ──┐ ┌── 查询端 ──┐ 优化前 │ 全在内存 │ │ 一次全查 │ 600MB 内存 优化后 │ 窗口200行 │ │ 每次5000条│ 20MB 内存 关键代码 new SXSSFWorkbook(200) → 限制写入端内存 new Page(pageNum, 5000) → 限制查询端内存 workbook.dispose() → 清理临时文件必须