前言
在日常的开发中,用的比较多的方式就是 Apache 下的 POI 框架了,但在目前数据量大的时代下,这种方式显得已经不适合了
编写该篇文章主要是公司中实际上用到了这种场景,将它作为文章分享出来,如果这种方式的导入/导出能帮助到大家也挺开心的,或者大家也可以发表自己在这方面的看法,提出来,做为更好的一种方式去呈现
节点分以下几步走,先是介绍原始 Apache POI 以及由阿里巴巴开源的一框 POI 框架做对比,再是提出解决这方面问题上的思路,然后进行案例演示,最后记录一下在使用中遇到的错误点.
POI 框架特性对比
poi 依赖的基础接口:WorkBook,有几种实现子类需要进行区分,如下:
- HSSFWorkbook:Excel 2003(包含) 之前版本使用的子类对象,处理的文件格式都是 .xls 的,其是 poi 中最常用的方式,处理的行数在 6W+,一般处理的数据不超过这个大小就不会出现内存溢出的,这个量内存也是足够支撑的.
- XSSFWorkbook:Excel 2003-2007 使用的子类对象,目前还是有大量公司使用的这个,文件格式为 .xlsx,出现这个格式就是为了突破 HSSFWorkBook 6W 数据的局限,是为了针对Excel2007版本的 1048576行,16384 列,最多可以导出 104w 条数据,虽然在数据上增加了,但是内存的瓶颈也就来了,OOM 离之不远了.
- SXSSFWorkbook:该实现类是 POI3.8 之后的版本才有的,它可以操作 Excel2007 以后的所有版本 Excel,扩展名是 .xlsx,这种方式提供了一种低内存占用机制,存储百万数据丝毫不是问题,一般不会出现内存溢出(它使用硬盘来换内存,也就是说当内存数据到达一定时会采用硬盘来进行存储,内存里存储的只会是最新的数据),缺点:因为它使用到了硬盘,当数据到达硬盘以后,也就无法完成数据的克隆或者公式计算,sheet.clone() 已经无法被支持了
在使用过程中,如果不涉及到 Excel 公式和样式并且数据量较大的情况下,推荐使用 SXSSFWorkbook;数据量不超过 6W~7W 也涉及到了公式的计算,推荐使用 XSSFWorkbook
大数据量解决思路
使用传统的 poi 导入导出方式,当数据量过大时,明显会出现 OOM 异常,因此推荐大家使用阿里巴巴开源的 easyExcel 框架作为导入导出的媒介
GitHub - alibaba/easyexcel: 快速、简单避免OOM的处理Excel工具
导入
- 导入数据也分批次的导入,导入也需要集合来存储 Excel 行列的值,当这个批次被导入完成后,集合进行清空,防止重复处理且减少内存的占用
- 在下面的案例中,会通过 MyBatis、JDBC 做批量的数据库插入操作,很明显你就会 JDBC 的效率比 MyBatis 高,因为 MyBatis 底层也是通过 JDBC 对数据库进行操作的,MyBatis 在处理结果集的时候都是一条一条数据进行循环遍历处理的,所以在这个基础上效率就下降了.
导出
- 不要一次性将数据量全量查询出来,如果几百万数据一下查出来,内存是吃不下的,所以应该对数据进行分批查出,比如:20W一次、10W一次
- 将数据查询出来以后,进行 Excel 写入时,也应该做批次的写入操作,一行一行的写入会造成大量 IO 请求,性能随之就会下降
- 几百万数据同时放在一个 sheet 里面,不然打开一个 sheet 都会停顿很长时间,操作时也会明显变慢,例如:一个 sheet 存储一百万数据
- 导出到 excel 时,肯定需要依赖集合进行数据的临时存储,在每个批次的数据处理完以后,记得对集合进行清空,以便让 GC 知道这个可以提前进行回收了,内存也不会一直被占用.
案例演示
准备工作
前期准备好 SQL 脚本和存储函数用于批量生成假数据
# 表结构脚本 CREATE TABLE `act_result_log` ( `id` int(10) NOT NULL AUTO_INCREMENT, `onlineseqid` varchar(32) DEFAULT NULL, `businessid` varchar(32) DEFAULT NULL, `becifno` varchar(32) DEFAULT NULL, `ivisresult` varchar(32) DEFAULT NULL, `createdby` varchar(32) DEFAULT NULL, `createddate` date DEFAULT NULL, `updateby` varchar(32) DEFAULT NULL, `updateddate` date DEFAULT NULL, `risklevel` varchar(32) DEFAULT NULL, `is_deleted` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `account_id_IDX` (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1000002 DEFAULT CHARSET=utf8mb4; # 存储函数,生成五百万数据,等待时间会比较长 CREATE PROCEDURE demo.batch_generate ( ) BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 5000000 DO INSERT INTO `demo`.`act_result_log` (`onlineseqid`, `businessid`, `becifno`, `ivisresult`, `createdby`,`createddate`, `updateby`, `updateddate`,`risklevel`) VALUES(CONCAT('0' + i), CONCAT('1'+ i), concat('2'+i), concat('3'+i),'1',now(),'1',now(),'1'); SET i = i + 1; END WHILE; END
EasyExcel maven 版本依赖
<easyexcel.version>3.0.5</easyexcel.version> <!--EasyExcel相关依赖--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>${easyexcel.version}</version> </dependency>
若在你项目还用到了低版本的 EasyExcel,需要对旧版本的 poi 依赖进行排除
<easyexcel.high.version>3.0.5</easyexcel.high.version> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>${easyexcel.high.version}</version> <exclusions> <exclusion> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> </exclusion> <exclusion> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> </exclusion> <exclusion> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> </exclusion> </exclusions> </dependency>
示例代码
基础依赖的实体类、mapper、service、控制器、公共类
1、数据库实体
@Data @Accessors(chain = true) @TableName("act_result_log") public class ActResultLogDO implements Serializable { private static final long serialVersionUID = 1L; @TableId(value = "id", type = IdType.AUTO) private Long id; @TableField("onlineseqid") private String onlineseqid; @TableField("businessid") private String businessid; @TableField("becifno") private String becifno; @TableField("ivisresult") private String ivisresult; @TableField("createdby") private String createdby; @TableField("createddate") private LocalDate createddate; @TableField("updateby") private String updateby; @TableField("updateddate") private LocalDate updateddate; @TableField("risklevel") private String risklevel; }
2、实体 Vo
@Data public class ActResultLogVO implements Serializable { private static final long serialVersionUID = 1L; @ExcelProperty(value = "onlineseqid",index = 0) private String onlineseqid; @ExcelProperty(value = "businessid",index = 1) private String businessid; @ExcelProperty(value = "becifno",index = 2) private String becifno; @ExcelProperty(value = "ivisresult",index = 3) private String ivisresult; @ExcelProperty(value = "createdby",index = 4) private String createdby; @ExcelProperty(value = "createddate",index = 5) private LocalDate createddate; @ExcelProperty(value = "updateby",index = 6) private String updateby; @ExcelProperty(value = "updateddate",index = 7) private LocalDate updateddate; @ExcelProperty(value = "risklevel",index = 8) private String risklevel; }
3、控制器
@RequestMapping("/excel") @RestController public class ExcelController { @Resource private EasyExcelUtil easyExcelUtil; @GetMapping("/export") public void exportExcel(HttpServletResponse response) { easyExcelUtil.dataExport300w(response); } }
4、常量类
public class ExcelConstants { public static final Integer PER_SHEET_ROW_COUNT = 100*10000; public static final Integer PER_WRITE_ROW_COUNT = 20*10000; public static final Integer GENERAL_ONCE_SAVE_TO_DB_ROWS_JDBC = 10*10000; public static final Integer GENERAL_ONCE_SAVE_TO_DB_ROWS_MYBATIS = 5*10000; }
导入
导入分两种方式进行,第一种是通过 MyBatis 编写 SQL 语句运行,不使用 MP 是因为它底层的批量新增是一条一条写入的,并不是实际意义上的批次;第二种是通过原生 JDBC 进行批量操作,使用手动提交事务的方式.
1、Service 接口
public interface IActResultLogService extends IService<ActResultLogDO> { /** * 通过分页参数查询一百w数据 * @return */ List<ActResultLogDO> findByPage100w(Integer pageNum,Integer pageSize); /** * 从 Excel 导入数据,批次为 10w,通过 JDBC * @param dataList * @return */ Map<String, Object> import2DBFromExcel10wByJDBC(List<Map<Integer, String>> dataList); /** * 从 Excel 导入数据,批次为 10W,通过 MyBatis * @param actResultLogList */ void import2DBFromExcel10wByMybatis(List<ActResultLogDO> actResultLogList); }
2、Service 接口实现类
@Slf4j @Service public class ActResultLogServiceImpl extends ServiceImpl<ActResultLogMapper, ActResultLogDO> implements IActResultLogService { @Override public List<ActResultLogDO> findByPage100w(Integer pageNum, Integer pageSize) { PageInfo<ActResultLogDO> pageInfo = PageHelper.startPage(pageNum, pageSize).doSelectPageInfo(() -> { QueryWrapper<ActResultLogDO> wrapper = new QueryWrapper<>(); // TODO 此处可以进行添加条件过滤 baseMapper.selectList(wrapper); }); return pageInfo.getList(); } // Service中具体业务逻辑 /** * 测试用Excel导入超过10w条数据,经过测试发现,使用 Mybatis 批量插入速度非常慢,所以这里可以使用 数据分批+JDBC分批插入+事务来继续插入速度会非常快 */ @Override public Map<String, Object> import2DBFromExcel10wByJDBC(List<Map<Integer, String>> dataList) { Map<String, Object> result = new HashMap<>(); // 结果集中数据为0时,结束方法.进行下一次调用 if (dataList.size() == 0) { result.put("empty", "0000"); return result; } // JDBC分批插入+事务操作完成对10w数据的插入 Connection conn = null; PreparedStatement ps = null; try { long startTime = System.currentTimeMillis(); log.info("{} 条,开始导入到数据库时间:{}", dataList.size(), startTime + "ms"); conn = JDBCDruidUtils.getConnection(); // 控制事务:默认不提交 conn.setAutoCommit(false); String sql = "insert into ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel) values"; sql += "(?,?,?,?,?,?,?,?,?)"; ps = conn.prepareStatement(sql); // 循环结果集:这里循环不支持"烂布袋"表达式 for (int i = 0; i < dataList.size(); i++) { Map<Integer, String> item = dataList.get(i); ps.setString(1, item.get(0)); ps.setString(2, item.get(1)); ps.setString(3, item.get(2)); ps.setString(4, item.get(3)); ps.setString(5, item.get(4)); ps.setTimestamp(6, new Timestamp(System.currentTimeMillis())); ps.setString(7, item.get(6)); ps.setTimestamp(8, new Timestamp(System.currentTimeMillis())); ps.setString(9, item.get(8)); // 将一组参数添加到此 PreparedStatement 对象的批处理命令中。 ps.addBatch(); } // 执行批处理 ps.executeBatch(); // 手动提交事务 conn.commit(); long endTime = System.currentTimeMillis(); log.info("{} 条,结束导入到数据库时间:{}", dataList.size(), endTime + "ms"); log.info("{} 条,导入用时:{}", dataList.size(), (endTime - startTime) + "ms"); result.put("success", "1111"); } catch (Exception e) { result.put("exception", "0000"); e.printStackTrace(); } finally { // 关连接 JDBCDruidUtils.close(conn, ps); } return result; } // 采用 mapper 编写 SQL 语句进行测试,效率明显比原生 JDBC 要低 @Override public void import2DBFromExcel10wByMybatis(List<ActResultLogDO> actResultLogList) { baseMapper.importToDb(actResultLogList); } }