前言
最近我做过一个MySQL百万级别数据的excel导出功能,已经正常上线使用了。
这个功能挺有意思的,里面需要注意的细节还真不少,现在拿出来跟大家分享一下,希望对你会有所帮助。
原始需求:用户在UI界面上点击全部导出按钮,就能导出所有商品数据。
咋一看,这个需求挺简单的。
但如果我告诉你,导出的记录条数,可能有一百多万,甚至两百万呢?
这时你可能会倒吸一口气。
因为你可能会面临如下问题:
如果同步导数据,接口很容易超时。如果把所有数据一次性装载到内存,很容易引起OOM。数据量太大sql语句必定很慢。相同商品编号的数据要放到一起。如果走异步,如何通知用户导出结果?如果excel文件太大,目标用户打不开怎么办?
我们要如何才能解决这些问题,实现一个百万级别的excel数据快速导出功能呢?
1.异步处理
做一个MySQL百万数据级别的excel导出功能,如果走接口同步导出,该接口肯定会非常容易超时。
因此,我们在做系统设计的时候,第一选择应该是接口走异步处理。
说起异步处理,其实有很多种,比如:使用开启一个线程,或者使用线程池,或者使用job,或者使用mq等。
为了防止服务重启时数据的丢失问题,我们大多数情况下,会使用job或者mq来实现异步功能。
1.1 使用job
如果使用job的话,需要增加一张执行任务表,记录每次的导出任务。
用户点击全部导出按钮,会调用一个后端接口,该接口会向表中写入一条记录,该记录的状态为:待执行。
有个job,每隔一段时间(比如:5分钟),扫描一次执行任务表,查出所有状态是待执行的记录。
然后遍历这些记录,挨个执行。
需要注意的是:如果用job的话,要避免重复执行的情况。比如job每隔5分钟执行一次,但如果数据导出的功能所花费的时间超过了5分钟,在一个job周期内执行不完,就会被下一个job执行周期执行。
所以使用job时可能会出现重复执行的情况。
为了防止job重复执行的情况,该执行任务需要增加一个执行中的状态。
具体的状态变化如下:
执行任务被刚记录到执行任务表,是待执行状态。当job第一次执行该执行任务时,该记录再数据库中的状态改为:执行中。当job跑完了,该记录的状态变成:完成或失败。
这样导出数据的功能,在第一个job周期内执行不完,在第二次job执行时,查询待处理状态,并不会查询出执行中状态的数据,也就是说不会重复执行。
此外,使用job还有一个硬伤即:它不是立马执行的,有一定的延迟。
如果对时间不太敏感的业务场景,可以考虑使用该方案。
1.2 使用mq
用户点击全部导出按钮,会调用一个后端接口,该接口会向mq服务端,发送一条mq消息。
有个专门的mq消费者,消费该消息,然后就可以实现excel的数据导出了。
相较于job方案,使用mq方案的话,实时性更好一些。
对于mq消费者处理失败的情况,可以增加补偿机制,自动发起重试。
RocketMQ自带了失败重试功能,如果失败次数超过了一定的阀值,则会将该消息自动放入死信队列。
2.使用easyexcel
我们知道在Java中解析和生成Excel,比较有名的框架有Apache POI和jxl。
但它们都存在一个严重的问题就是:非常耗内存,POI有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。
百万级别的excel数据导出功能,如果使用传统的Apache POI框架去处理,可能会消耗很大的内存,容易引发OOM问题。
而easyexcel重写了POI对07版Excel的解析,之前一个3M的excel用POI sax解析,需要100M左右内存,如果改用easyexcel可以降低到几M,并且再大的Excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便。
需要在maven的pom.xml文件中引入easyexcel的jar包:
com.alibaba
easyexcel
3.0.2
复制代码
之后,使用起来非常方便。
读excel数据非常方便:
@Test
public void simpleRead() {
String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
复制代码
写excel数据也非常方便:
@Test
public void simpleWrite() {
String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去读,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}
复制代码
easyexcel能大大减少占用内存的主要原因是:在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。
3.分页查询
百万级别的数据,从数据库一次性查询出来,是一件非常耗时的工作。
即使我们可以从数据库中一次性查询出所有数据,没出现连接超时问题,这么多的数据全部加载到应用服务的内存中,也有可能会导致应用服务出现OOM问题。
因此,我们从数据库中查询数据时,有必要使用分页查询。比如:每页5000条记录,分为200页查询。
public Page searchUser(SearchModel searchModel) {
List userList = userMapper.searchUser(searchModel);
Page pageResponse = Page.create(userList, searchModel);
pageResponse.setTotal(userMapper.searchUserCount(searchModel));
return pageResponse;
}
复制代码
每页大小pageSize和页码pageNo,是SearchModel类中的成员变量,在创建searchModel对象时,可以设置设置这两个参数。
然后在Mybatis的sql文件中,通过limit语句实现分页功能:
limit #{pageStart}, #{pageSize}
复制代码
其中的pagetStart参数,是通过pageNo和pageSize动态计算出来的,比如:
pageStart = (pageNo - 1) * pageSize;
复制代码
4.多个sheet
我们知道,excel对一个sheet存放的最大数据量,是有做限制的,一个sheet最多可以保存1048576行数据。否则在保存数据时会直接报错:
invalid row number (1048576) outside allowable range (0..1048575)
复制代码
如果你想导出一百万以上的数据,excel的一个sheet肯定是存放不下的。
因此我们需要把数据保存到多个sheet中。
5.计算limit的起始位置
我之前说过,我们一般是通过limit语句来实现分页查询功能的:
limit #{pageStart}, #{pageSize}
复制代码
其中的pagetStart参数,是通过pageNo和pageSize动态计算出来的,比如:
pageStart = (pageNo - 1) * pageSize;
复制代码
如果只有一个sheet可以这么玩,但如果有多个sheet就会有问题。因此,我们需要重新计算limit的起始位置。
例如:
ExcelWriter excelWriter = EasyExcelFactory.write(out).build();
int totalPage = searchUserTotalPage(searchModel);
if(totalPage > 0) {
Page page = Page.create(searchModel);
int sheet = (totalPage % maxSheetCount == 0) ? totalPage / maxSheetCount: (totalPage / maxSheetCount) + 1;
for(int i=0;i=startPageNo && page.getPageNo()<=endPageNo) {
page = searchUser(searchModel);
if(CollectionUtils.isEmpty(page.getList())) {
break;
}
excelWriter.write(page.getList(),writeSheet);
page.setPageNo(page.getPageNo()+1);
}
}
}
复制代码
这样就能实现分页查询修改软件时间限制,将数据导出到不同的excel的sheet当中。
6.文件上传到OSS
由于现在我们导出excel数据的方案改成了异步,所以没法直接将excel文件,同步返回给用户。
因此我们需要先将excel文件存放到一个地方,当用户有需要时,可以访问到。
这时,我们可以直接将文件上传到OSS文件服务器上。
通过OSS提供的上传接口,将excel上传成功后,会返回文件名称和访问路径。
我们可以将excel名称和访问路径保存到表中,这样的话,后面就可以直接通过浏览器,访问远程excel文件了。
而如果将excel文件保存到应用服务器,可能会占用比较多的磁盘空间。
一般建议将应用服务器和文件服务器分开,应用服务器需要更多的内存资源或者CPU资源,而文件服务器需要更多的磁盘资源。
7.通过WebSocket推送通知
通过上面的功能已经导出了excel文件,并且上传到了OSS文件服务器上。
接下来的任务是要本次excel导出结果,成功还是失败,通知目标用户。
有种做法是在页面上提示:正在导出excel数据,请耐心等待。
然后用户可以主动刷新当前页面,获取本地导出excel的结果。
但这种用户交互功能,不太友好。
还有一种方式是通过webSocket建立长连接,进行实时通知推送。
如果你使用了SpringBoot框架,可以直接引入webSocket的相关jar包:
org.springframework.boot
spring-boot-starter-websocket
复制代码
使用起来挺方便的。
我们可以加一张专门的通知表,记录通过webSocket推送的通知的标题、用户、附件地址、阅读状态、类型等信息。
能更好的追溯通知记录。
webSocket给客户端推送一个通知之后,用户的右上角的收件箱上,实时出现了一个小窗口,提示本次导出excel功能是成功还是失败,并且有文件下载链接。
当前通知的阅读状态是未读。
用户点击该窗口,可以看到通知的详细内容,然后通知状态变成已读。
8.总条数可配置
我们在做导百万级数据这个需求时,是给用户用的,也有可能是给运营同学用的。
其实我们应该站在实际用户的角度出发,去思考一下,这个需求是否合理。
用户拿到这个百万级别的excel文件,到底有什么用途,在他们的电脑上能否打开该excel文件,电脑是否会出现太大的卡顿了,导致文件使用不了。
如果该功能上线之后,真的发生发生这些情况,那么导出excel也没有啥意义了。
因此,非常有必要把记录的总条数,做成可配置的,可以根据用户的实际情况调整这个配置。
比如:用户发现excel中有50万的数据修改软件时间限制,可以正常访问和操作excel,这时候我们可以将总条数调整成500000,把多余的数据截取掉。
其实,在用户的操作界面,增加更多的查询条件,用户通过修改查询条件,多次导数据,可以实现将所有数据都导出的功能,这样可能更合理一些。
此外,分页查询时,每页的大小,也建议做成可配置的。
通过总条数和每页大小,可以动态调整记录数量和分页查询次数,有助于更好满足用户的需求。
9.order by商品编号
之前的需求是要将相同商品编号的数据放到一起。
例如:
编号
商品名称
仓库名称
价格
1
笔记本
北京仓
7234
1
笔记本
上海仓
7235
1
笔记本
武汉仓
7236
2
平板电脑
成都仓
7236
2
平板电脑
大连仓
3339
但我们做了分页查询的功能,没法将数据一次性查询出来,直接在Java内存中分组或者排序。
因此,我们需要考虑在sql语句中使用order by 商品编号,先把数据排好顺序,再查询出数据,这样就能将相同商品编号,仓库不同的数据放到一起。
此外,还有一种情况需要考虑一下,通过配置的总记录数将全部数据做了截取。
但如果最后一个商品编号在最后一页中没有查询完,可能会导致导出的最后一个商品的数据不完整。
因此,我们需要在程序中处理一下,将最后一个商品删除。
但加了order by关键字进行排序之后,如果查询sql中join了很多张表,可能会导致查询性能变差。
那么,该怎么办呢?
总结
最后用两张图,总结一下excel异步导数据的流程。
如果是使用mq导数据:
如果是使用job导数据:
这两种方式都可以,可以根据实际情况选择使用。
EasyExcel简介
EasyExcel是一款阿里开源的Excel导入导出工具,具有处理快速、占用内存小、使用方便的特点,在Github上已有22k+Star,可见其非常流行。
EasyExcel读取75M(46W行25列)的Excel,仅需使用64M内存,耗时20s,极速模式还可以更快!
集成
在SpringBoot中集成EasyExcel非常简单,仅需一个依赖即可。
com.alibaba
easyexcel
3.0.5
复制代码
使用
EasyExcel和EasyPoi的使用非常类似,都是通过注解来控制导入导出。接下来我们以会员信息和订单信息的导入导出为例,分别实现下简单的单表导出和具有一对多关系的复杂导出。
简单导出
我们以会员信息的导出为例,来体验下EasyExcel的导出功能。
/**
* 购物会员
* Created by macro on 2021/10/12.
*/
@Data
@EqualsAndHashCode(callSuper = false)
public class Member {
@ExcelProperty("ID")
@ColumnWidth(10)
private Long id;
@ExcelProperty("用户名")
@ColumnWidth(20)
private String username;
@ExcelIgnore
private String password;
@ExcelProperty("昵称")
@ColumnWidth(20)
private String nickname;
@ExcelProperty("出生日期")
@ColumnWidth(20)
@DateTimeFormat("yyyy-MM-dd")
private Date birthday;
@ExcelProperty("手机号")
@ColumnWidth(20)
private String phone;
@ExcelIgnore
private String icon;
@ExcelProperty(value = "性别", converter = GenderConverter.class)
@ColumnWidth(10)
private Integer gender;
}
复制代码
/**
* excel性别转换器
* Created by macro on 2021/12/29.
*/
public class GenderConverter implements Converter {
@Override
public Class supportJavaTypeKey() {
//对象属性类型
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
//CellData属性类型
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(ReadConverterContext context) throws Exception {
//CellData转对象属性
String cellStr = context.getReadCellData().getStringValue();
if (StrUtil.isEmpty(cellStr)) return null;
if ("男".equals(cellStr)) {
return 0;
} else if ("女".equals(cellStr)) {
return 1;
} else {
return null;
}
}
@Override
public WriteCellData convertToExcelData(WriteConverterContext context) throws Exception {
//对象属性转CellData
Integer cellValue = context.getValue();
if (cellValue == null) {
return new WriteCellData("");
}
if (cellValue == 0) {
return new WriteCellData("男");
} else if (cellValue == 1) {
return new WriteCellData("女");
} else {
return new WriteCellData("");
}
}
}
复制代码
/**
* EasyExcel导入导出测试Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试")
@RequestMapping("/easyExcel")
public class EasyExcelController {
@SneakyThrows(IOException.class)
@ApiOperation(value = "导出会员列表Excel")
@RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
public void exportMemberList(HttpServletResponse response) {
setExcelRespProp(response, "会员列表");
List memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class);
EasyExcel.write(response.getOutputStream())
.head(Member.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("会员列表")
.doWrite(memberList);
}
/**
* 设置excel下载响应头属性
*/
private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
}
}
复制代码
简单导入
接下来我们以会员信息的导入为例,来体验下EasyExcel的导入功能。
/**
* EasyExcel导入导出测试Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试")
@RequestMapping("/easyExcel")
public class EasyExcelController {
@SneakyThrows
@ApiOperation("从Excel导入会员列表")
@RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
@ResponseBody
public CommonResult importMemberList(@RequestPart("file") MultipartFile file) {
List memberList = EasyExcel.read(file.getInputStream())
.head(Member.class)
.sheet()
.doReadSync();
return CommonResult.success(memberList);
}
}
复制代码
复杂导出
当然EasyExcel也可以实现更加复杂的导出,比如导出一个嵌套了商品信息的订单列表,下面我们来实现下!
使用EasyPoi实现
之前我们使用过EasyPoi实现该功能,由于EasyPoi本来就支持嵌套对象的导出,直接使用内置的@ExcelCollection注解即可实现,非常方便也符合面向对象的思想。
寻找方案
由于EasyExcel本身并不支持这种一对多的信息导出,所以我们得自行实现下,这里分享一个我平时常用的快速查找解决方案的办法。
我们可以直接从开源项目的issues里面去搜索,比如搜索下一对多,会直接找到有无一对多导出比较优雅的方案这个issue。
从此issue的回复我们可以发现,项目维护者建议创建自定义合并策略来实现,有位回复的老哥已经给出了实现代码,接下来我们就用这个方案来实现下。
解决思路
为什么自定义单元格合并策略能实现一对多的列表信息的导出呢?首先我们来看下将嵌套数据平铺,不进行合并导出的Excel。
看完之后我们很容易理解解决思路,只要把订单ID相同的列中需要合并的列给合并了,就可以实现这种一对多嵌套信息的导出了。
实现过程
/**
* 订单导出
* Created by macro on 2021/12/30.
*/
@Data
@EqualsAndHashCode(callSuper = false)
public class OrderData {
@ExcelProperty(value = "订单ID")
@ColumnWidth(10)
@CustomMerge(needMerge = true, isPk = true)
private String id;
@ExcelProperty(value = "订单编码")
@ColumnWidth(20)
@CustomMerge(needMerge = true)
private String orderSn;
@ExcelProperty(value = "创建时间")
@ColumnWidth(20)
@DateTimeFormat("yyyy-MM-dd")
@CustomMerge(needMerge = true)
private Date createTime;
@ExcelProperty(value = "收货地址")
@CustomMerge(needMerge = true)
@ColumnWidth(20)
private String receiverAddress;
@ExcelProperty(value = {"商品信息", "商品编码"})
@ColumnWidth(20)
private String productSn;
@ExcelProperty(value = {"商品信息", "商品名称"})
@ColumnWidth(20)
private String name;
@ExcelProperty(value = {"商品信息", "商品标题"})
@ColumnWidth(30)
private String subTitle;
@ExcelProperty(value = {"商品信息", "品牌名称"})
@ColumnWidth(20)
private String brandName;
@ExcelProperty(value = {"商品信息", "商品价格"})
@ColumnWidth(20)
private BigDecimal price;
@ExcelProperty(value = {"商品信息", "商品数量"})
@ColumnWidth(20)
private Integer count;
}
复制代码
/**
* EasyExcel导入导出测试Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试")
@RequestMapping("/easyExcel")
public class EasyExcelController {
private List convert(List orderList) {
List result = new ArrayList();
for (Order order : orderList) {
List productList = order.getProductList();
for (Product product : productList) {
OrderData orderData = new OrderData();
BeanUtil.copyProperties(product,orderData);
BeanUtil.copyProperties(order,orderData);
result.add(orderData);
}
}
return result;
}
}
复制代码
/**
* 自定义注解,用于判断是否需要合并以及合并的主键
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {
/**
* 是否需要合并单元格
*/
boolean needMerge() default false;
/**
* 是否是主键,即该字段相同的行合并
*/
boolean isPk() default false;
}
复制代码
/**
* 自定义单元格合并策略
*/
public class CustomMergeStrategy implements RowWriteHandler {
/**
* 主键下标
*/
private Integer pkIndex;
/**
* 需要合并的列的下标集合
*/
private List needMergeColumnIndex = new ArrayList();
/**
* DTO数据类型
*/
private Class elementType;
public CustomMergeStrategy(Class elementType) {
this.elementType = elementType;
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
// 如果是标题,则直接返回
if (isHead) {
return;
}
// 获取当前sheet
Sheet sheet = writeSheetHolder.getSheet();
// 获取标题行
Row titleRow = sheet.getRow(0);
if (null == pkIndex) {
this.lazyInit(writeSheetHolder);
}
// 判断是否需要和上一行进行合并
// 不能和标题合并,只能数据行之间合并
if (row.getRowNum() <= 1) {
return;
}
// 获取上一行数据
Row lastRow = sheet.getRow(row.getRowNum() - 1);
// 将本行和上一行是同一类型的数据(通过主键字段进行判断),则需要合并
if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
for (Integer needMerIndex : needMergeColumnIndex) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
needMerIndex, needMerIndex);
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
}
}
/**
* 初始化主键下标和需要合并字段的下标
*/
private void lazyInit(WriteSheetHolder writeSheetHolder) {
// 获取当前sheet
Sheet sheet = writeSheetHolder.getSheet();
// 获取标题行
Row titleRow = sheet.getRow(0);
// 获取DTO的类型
Class eleType = this.elementType;
// 获取DTO所有的属性
Field[] fields = eleType.getDeclaredFields();
// 遍历所有的字段,因为是基于DTO的字段来构建excel,所以字段数 >= excel的列数
for (Field theField : fields) {
// 获取@ExcelProperty注解,用于获取该字段对应在excel中的列的下标
ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
// 为空,则表示该字段不需要导入到excel,直接处理下一个字段
if (null == easyExcelAnno) {
continue;
}
// 获取自定义的注解,用于合并单元格
CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);
// 没有@CustomMerge注解的默认不合并
if (null == customMerge) {
continue;
}
for (int index = 0; index < fields.length; index++) {
Cell theCell = titleRow.getCell(index);
// 当配置为不需要导出时,返回的为null,这里作一下判断,防止NPE
if (null == theCell) {
continue;
}
// 将字段和excel的表头匹配上
if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
if (customMerge.isPk()) {
pkIndex = index;
}
if (customMerge.needMerge()) {
needMergeColumnIndex.add(index);
}
}
}
}
// 没有指定主键,则异常
if (null == this.pkIndex) {
throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
}
}
}
复制代码
/**
* EasyExcel导入导出测试Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试")
@RequestMapping("/easyExcel")
public class EasyExcelController {
@SneakyThrows
@ApiOperation(value = "导出订单列表Excel")
@RequestMapping(value = "/exportOrderList", method = RequestMethod.GET)
public void exportOrderList(HttpServletResponse response) {
List orderList = getOrderList();
List orderDataList = convert(orderList);
setExcelRespProp(response, "订单列表");
EasyExcel.write(response.getOutputStream())
.head(OrderData.class)
.registerWriteHandler(new CustomMergeStrategy(OrderData.class))
.excelType(ExcelTypeEnum.XLSX)
.sheet("订单列表")
.doWrite(orderDataList);
}
}
复制代码
其他使用
由于EasyExcel的官方文档介绍的比较简单,如果你想要更深入地进行使用的话,建议大家看下官方Demo。
总结
本文到此结束,希望对大家有所帮助!