基于EasyExcel实现百万级别数据导出
前言
近期需要开发一个将百万数据量MySQL8的数据导出到excel的功能，查阅相关资料后便整理了这篇实现方案供读者参考。
需求简述
该数据表是一张用户表，包含id和name，该用户表数据量在300w左右，以自增id作为主键，而功能要求我们在一分钟之内完成百万数据导出到excel。需要注意的是，我们导出的excel格式为xlsx，它的每一个sheet只能容纳100w的数据，这也就意味着我们的数据必须以100w作为批次写到不同的sheet中。
实现思路
我们先来说说需要解决的问题:
- 如果一次性查询
300w左右的数据可能会占据大量的内存，如果对象字段很多的情况下，很可能出现内存溢出，我们要如何解决？ - 每个
excel文件都有sheet，并且每个sheet只能容纳100w左右的数据，对于这个问题我们要如何解决？ - 数据写入到
excel时，有没有合适的工具推荐？
对于问题1我们采用分页查询的方式进行查询，参考自己堆内存的配置推算每次分页查询的数据量。因为问题1采用了分页查询，我们完全可以通过分页查询的次数推算出一个sheet写入了多少数据，例如我们每次分页查询50w的数据，那么每两次就可以视为一个sheet写满了，我们就可以创建一个新的sheet写入数据。
这里需要注意一点，因为我们分页查询面对的是百万级别的数据，所以随着分页的推进势必出现深分页导致查询效率势降低，所以为了提高分页查询的效率，我们可以利用查询数据有序的特性，通过id作为偏移进行分页查询。
例如我们第一次分页查询的sql语句为:
select * from t_user limit 500000 ;
假如我们不以id作为索引，那么第二次的分页查询sql则是:
select * from t_user limit 500000,500000 ;
查看该查询执行计划，可以看到该查询一次性查询到几乎全表的数据，并且还走了全秒扫描性能可想而知:
id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra|
--+-----------+------+----------+----+-------------+---+-------+---+-------+--------+-----+
1|SIMPLE |t_user| |ALL | | | | |2993040| 100.0| |
因为我们的数据表是id自增的，所以我们查询的时候完全可以基于该特性通过上一次查询到的id作为筛选条件进行分页查询。
所以我们的分页查询可直接改为:
select * from t_user where id > 500000 limit 500000 ;
再次查看执行计划可以发现该查询为范围查询，查询到的数据量也少了很多，性能显著提升:
id|select_type|table |partitions|type |possible_keys|key |key_len|ref|rows |filtered|Extra |
--+-----------+------+----------+-----+-------------+-------+-------+---+-------+--------+-----------+
1|SIMPLE |t_user| |range|PRIMARY |PRIMARY|8 | |1496520| 100.0|Using where|
因为市面上比较多的excel导出工具，常见的就是Apache poi，但是它们的操作对于内存的消耗非常严重，对于我们这种大数据量的写入不是很友好，所以笔者更推荐使用阿里的EasyExcel，它对poi进行一定的封装和优化，同等数据量写入使用的内存更小。
解决上述问题之后，我们就可以说说代码实现思路了，以本文示例来说，有一张用户表有300w左右的数据，每次查询时只需查询id(4字节)和name(10字节)，按照64位的操作系统来说，一个user对象所占用的内存大小为:
object header +pointer+id字段+name字段大小=8+8+4+10=30字节
因为java对象内存大小需要16位对齐，需要补齐2个字节，所以实际大小为32字节，按照笔者对于堆内存的配置，每次查询50w条数据是允许的，所以每次从数据库读取数据并转为java对象，也只需要32*500000/1024即15M内存即可。
确定每次分页查询50w条数据之后，我们就需要确定一共需要查询几个分页，然后就可以根据pageSize确定查询的页数。 因为每次查询50w条数据，所以每两次完成分页查询和写入基本上一个sheet就会满了，这时候我们就需要创建一个新的sheet进行数据写入了。
总结一下实现步骤:
- 查询目标数据量大小。
- 根据每次分页大小确定查询页数。
- 根据页数大小进行遍历，进行分页查询，并将数据写入到文件中。
- 基于页数确定
sheet切换时机。
代码示例
以下便是笔者基于上述思路所实现的代码，查看日志也可以发现50w的数据查询和写入加起来只需6s。最终执行耗时也只需45s。
java
复制代码public static void main(String[] args) {
SpringApplication app = new SpringApplication(WebApplication.class);
Environment env = app.run(args).getEnvironment();
logger.info("启动成功！！");
logger.info("地址: \thttp://127.0.0.1:{}", env.getProperty("server.port"));
TUserMapper userMapper = SpringUtil.getBean(TUserMapper.class);
//计算总的数据量
int count = (int) userMapper.countByExample(null);
//获取分页总数
int queryCount = 50_0000;
int pageCount = count % queryCount == 0 ? count / queryCount : count / queryCount + 1;
//设置导出的文件名
String fileName = "result.xlsx";
//设置excel的sheet号码
int sheetNo = 1;
//设置第一个sheet的名字
String sheetName = "sheet-" + sheetNo;
long start = System.currentTimeMillis();
// 创建writeSheet
WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
//记录每次分页查询的最大值
Long maxId = null;
//指定文件
try (ExcelWriter excelWriter = EasyExcel.write(fileName, TUser.class).build()) {
//写入每一页分页查询的数据
for (int i = 1; i <= pageCount; i++) {
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
long queryStart = System.currentTimeMillis();
TUserExample userExample = new TUserExample();
//如果是第一次则直接进行分页查询，反之基于上一次分页查询的分页定位实际偏移量，筛选前n条数据以达到分页效果
if (i == 1) {
PageHelper.startPage(i, queryCount, false);
} else if (maxId != null) {
userExample.createCriteria().andIdGreaterThan(maxId);
PageHelper.startPage(0, queryCount, false);
}
List<TUser> userList = userMapper.selectByExample(userExample);
//更新下一次分页查询用的id
if (CollUtil.isNotEmpty(userList)) {
maxId = userList.get(userList.size() - 1).getId();
}
long queryEnd = System.currentTimeMillis();
logger.info("数据大小:{},写入sheet位置:{}，耗时:{}", userList.size(), sheetName, queryEnd - queryStart);
long writeStart = System.currentTimeMillis();
excelWriter.write(userList, writeSheet);
long writeEnd = System.currentTimeMillis();
logger.info("本次写入耗时:{}", writeEnd - writeStart);
//如果% 2 == 0，则说明一个sheet写入了50*2即100w的数据，需要创建新的sheet进行写入
if (i % 2 == 0) {
sheetName = "sheet-" + (++sheetNo);
writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
logger.info("写满一个sheet,切换到下一个sheet:{}", sheetName);
}
}
}
long total = System.currentTimeMillis() - start;
logger.info("导出结束，总耗时:{}", total);
}
可能会有读者好奇笔者这个50w的数值设计思路是什么，除了考虑避免OOM以外，还考虑到每个sheet只能写入100w条的数据，为了方便通过分页查询的轮次确定当前写入的数据量大小，笔者尝试过20w、50w。 最终在压测结果上看出，50w读写耗时虽然是20w的2倍，但是IO次数却不到20w查询的二分之一，通过更少的IO操作获得更好的执行性能。
bash
复制代码
# 50w的读写耗时
com.sharkChili.webTemplate.config.WebApplication :73 [32m [0;39m 数据大小:500000,写入sheet位置:sheet-1，耗时:4719
2023-12-03 10:13:58.675 INFO com.sharkChili.webTemplate.config.WebApplication :78 [32m [0;39m 本次写入耗时:2911
2023-12-03 10:14:02.517 INFO com.sharkChili.webTemplate.config.WebApplication :73 [32m [0;39m 数据大小:500000,写入sheet位置:sheet-1，耗时:3841
2023-12-03 10:14:04.860 INFO com.sharkChili.webTemplate.config.WebApplication :78 [32m [0;39m 本次写入耗时:2343
小结
以上便是笔者的百万级别数据导出的落地方案，可以看出笔者着重在分页查询大小和分页查询sql上进行重点优化，通过平衡分页查询的数据量和IO次数找到合适的pageSize，再通过上一次分页查询结果定位下一次查询的id作为where条件，避免分页查询时的全秒扫描以得到符合业务需求的高性能sql，从而完成百万级别数据的高效导出。
参考文章
300万数据导入导出优化方案，从80s优化到8s（实测）:juejin.cn/post/719968…
Easy Excel:easyexcel.opensource.alibaba.com/docs/curren…
PageHelper 关闭COUNT(0)查询 以及PageHelper 的分页原理分析:blog.csdn.net/qq_43842093…