云认证平台提供将认证记录导出为Excel的功能。但是当业务量慢慢发展起来以后，就碰到一个比较棘手的问题：需要导出的认证记录太多，导出Excel的时候经常碰到服务器内存溢出问题。例如当要导出30w条记录的时候，服务端内存占用会超过2G，继续增加Heap空间不是一个非常合适的方案，因为无法预知一共需要多少内存。

因此，这两天特意找了一下解决方案，终于有了解决方案。最终方案导出256w条记录的时候，服务器内战占用1.6G，不再继续增加。

1. POI项目简介

用Java导出Excel就无法绕过Apache POI项目。说起来，POI项目这个名字很有意思，它是“Poor Obfuscation Implementation”的缩写，翻译过来是“劣质的困惑的实现”。不过这个名字实际上已经被废弃掉了，只能在早期的文档中找到。这个名字起源于项目的早期，因为微软格式不开放，所以只能够通过反向工程猜测格式的含义，所以才有了这个幽默的称呼。到后来微软开放了OOXML规范，实际上格式已经是开源的标准了。另一个废弃的原因则是出于市场考虑（商业用户对这个名字可能会引起过多联想而不敢采用）。

有兴趣的可以到https://en.wikipedia.org/wiki/Apache_POI上了解一些历史。

POI项目提供了对Word、Excel、PowerPoint、Visio等文件的读写支持。提供了对Office 97到Office2007的格式支持。既支持OOXML标准的文档（.docx, .xlsx, .pptx），也支持旧格式（.doc, .xls, .ppt）。

1.1 Excel相关

POI项目中包含多个子项目，分别对应不同格式文件的读写，不同的读写机制等等。与Excel相关的，主要有两个子项目：

HSSF (Horrible SpreadSheet Format) 读写.xls文件。可以读写Excel 97及之后版本生成的文件。这个格式也被称为“BIFF 8 Format”。因为这个格式没有开源，因此有些特性没有被支持。
XSSF (XML SpreadSheet Format) 读写.xlsx格式。也就是Office Open XML格式。
SXSSF（Small XSSF）读写.xlsx格式。3.8-beta3新增，基于XSSF实现的一个低内存占用的API。这是一个基于流的API。通过限制API能够访问的记录行数来达到减少内存占用的目的。在这种API中，可以只有一部分行保存在内存中，其他的则保存在硬盘缓存中。所以会有一些限制，例如：不在内存中的行无法被访问；Sheet.clone()不被支持；Formula计算不被支持等。

下面是一个表格，列出了各个API的一些特性。

HSSF、XSSF都支持两种编程模型：eventmodel（类似于XML解析中的SAX，基于流的解析）；usermodel（类似于XML解析中的DOM模型）。

2. Excel导出

实际项目中已经使用了SXSSF项目（但是依然会溢出，原因不在Excel这部分，原因下面分析）。这里依然简单的介绍一下怎么使用SXSSF导出一个Excel。下面是一个简单的例子：

 1// 创建一个Excel文件，在内存中保存100条记录，其余的会被flush到硬盘上
 2Workbook workbook = new SXSSFWorkbook(100);
 3// 创建一个Sheet
 4Sheet workingSheet = workbook.createSheet("sheet1");
 5// 创建第一行，用于显示每列标题
 6Row row = workingSheet.createRow(0);
 7// 创建单元格样式
 8Font fontHeader = workbook.createFont();
 9fontHeader.setFontHeightInPoints((short) 10);
10fontHeader.setColor(IndexedColors.BLACK.getIndex());
11fontHeader.setBoldweight(Font.BOLDWEIGHT_BOLD);
12CellStyle csHeader = workbook.createCellStyle();
13csHeader.setFont(fontHeader);
14csHeader.setBorderLeft(CellStyle.BORDER_THIN);
15csHeader.setBorderRight(CellStyle.BORDER_THIN);
16csHeader.setBorderTop(CellStyle.BORDER_THIN);
17csHeader.setBorderBottom(CellStyle.BORDER_THIN);
18csHeader.setAlignment(CellStyle.ALIGN_CENTER);
19
20// 手动设置列宽。第一个参数表示要为第几列设置，第二个参数表示列的宽度，n为列高的像素数。
21workingSheet.setColumnWidth(0, (int)(35.7 * 150));
22Cell cell = row.createCell(0);
23cell.setCellValue("Title");
24cell.setCellStyle(csHeader);

与使用XSSF的方法相比，唯一改变的就是创建Excel对象的地方：Workbook workbook = new SXSSFWorkbook(100);，SXSSF需要指定内存中可用的记录数，例如上面的例子中指定了100条记录。超出100条的记录将被写入磁盘，不能够直接访问。但是对于导出Excel这种场景来说，不会有随机访问数据的要求，这样的方式是没有影响的。

3. 使用数据库游标

因为以前已经在项目中使用了SXSSF项目，因此内存溢出问题应该不是POI项目带来的。经过调查，发现这是数据库方面的原因：

如果不适用游标，MySQL默认会将结果集整个都缓存的客户端，对于几十万上百万条记录，这个内存占用是巨大的；
MyBatis返回结果集的时候是将JDBC的ResultSet转换成具体对象的List，在这个过程中，会将所有的数据库记录转换成Java对象，更加容易带来内存溢出的问题。

在结果集比较小的情况下，将结果集整体返回，会带来性能上的优势。但是当结果集变大的时候，内存占用和性能上都会碰到极大的问题。以上两个问题，都需要启用游标来解决。

3.1 数据库游标

在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL选择语句相关联。因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

通过MyBatis使用游标，有几种方法：SpringBatch项目带来的MyBatisCursorItemReader；MyBatis本身的SqlSession.selectCursor方法；以及MyBatis提供的ResultHandler接口。这几种方法的不同下一篇博文会涉及到。这里只说一下如何使用Spring Batch提供的MyBatisCursorItemReader。

MyBatisCursorItemReader是Spring Batch项目提供的一个Bean，提供了使用游标从数据库读取数据的功能。需要MyBatis 3.4.0或更新版本的支持。其本质还是对SqlSession.selectCursor的一个简单封装，有兴趣的可以看一下源码。

3.2 引入支持包

 1<dependency>
 2    <groupId>org.apache.poi</groupId>
 3    <artifactId>poi-ooxml</artifactId>
 4    <version>3.9</version>
 5</dependency>
 6<!-- MyBatis -->
 7<dependency>
 8    <groupId>org.mybatis.spring.boot</groupId>
 9    <artifactId>mybatis-spring-boot-starter</artifactId>
10    <version>1.3.1</version>
11</dependency>
12<!-- Spring Batch -->
13<dependency>
14    <groupId>org.springframework.boot</groupId>
15    <artifactId>spring-boot-starter-batch</artifactId>
16    <version>${springboot.version}</version>
17</dependency>

3.3 FetchSize

另一个要改的地方是MyBatis的Mapper文件：

 1  <select id="exportByExample" fetchSize="-2147483648" parameterType="com.eveus.admin.po.logs.BankcardAuthNoOTPLogPOExample" resultSetType="FORWARD_ONLY" resultMap="BaseResultMap">
 2    select
 3    <include refid="Export_Column_List" />
 4    from UID_BANKCARD_AUTH_NO_OTP_LOG 
 5    <if test="_parameter != null">
 6      <include refid="Example_Where_Clause" />
 7    </if>
 8    <if test="orderByClause != null">
 9      order by ${orderByClause}
10    </if>
11  </select>

注意其中的fetchSize是新增的，其值必须为-2147483648（至于为什么，将在下一篇文章中介绍）。另外，还新增了配置项resultSetType，其值为：FORWARD_ONLY。

3.4 导出过程改造

 1// 使用游标获取数据（避免结果集太大引起的OOM错误）
 2MyBatisCursorItemReader<MatchCompanyLogPO> reader = new MyBatisCursorItemReader<>();
 3reader.setSqlSessionFactory(sqlSessionFactory);
 4reader.setQueryId("com.eveus.admin.mapper.logs.MatchCompanyLogPOCustMapper.exportByExample");
 5Map<String, Object> readerParams = new HashMap<String, Object>();
 6readerParams.put("oredCriteria", example.getOredCriteria());
 7// 设置参数
 8reader.setParameterValues(readerParams);
 9try {
10    reader.open(new ExecutionContext());    // 打开游标
11    MatchCompanyLogPO logPO;
12    while (( logPO = reader.read()) != null) {
13        // 写入Excel文件
14        ...
15    }
16    logger.info("exportMatchCompany：文件名{}，共{}条", fileName, exporter.getNumberOfRows());
17    logger.info("exportMatchCompany: Open Cursor: {}, Read all records: {}", d2.getTime()-d1.getTime(), d3.getTime()-d2.getTime());
18    ResponseWriteUtil.writeFileStream(exporter.getWorkbook(), fileName, response);
19} catch (Exception e) {
20    logger.error("exportMatchCompany exception! ", e);
21}

4. 补充

改造的代码不多，但是整个探索的时间确实不少。这可能就是重构的魅力吧。

另外，在导出过程中还发现了一个有趣的事情：最开始导出Excel的时候，发现最多只能导出1048576条记录。最初甚至怀疑是不是数据库方面有什么限制。到最后才发现原来是Excel本身的限制：Excel中一个Sheet最多支持1048576条记录，行号范围为0-1048575。

Spring使用POI导出Excel内存溢出问题的解决