前言

最近我做过一个MySQL百万等级数据的excel导出功用,现已正常上线运用了。

这个功用挺有意思的,里面需求注意的细节还真不少,现在拿出来跟大家分享一下,希望对你会有所协助。

原始需求:用户在UI界面上点击悉数导出按钮,就能导出一切产品数据。

咋一看,这个需求挺简略的。

但假如我告诉你,导出的记载条数,或许有一百多万,甚至两百万呢?

这时你或许会倒吸一口气。

由于你或许会面对如下问题:

  1. 假如同步导数据,接口很简单超时。
  2. 假如把一切数据一次性装载到内存,很简单引起OOM。
  3. 数据量太大sql句子必定很慢。
  4. 相同产品编号的数据要放到一起。
  5. 假如走异步,怎么告诉用户导出成果?
  6. 假如excel文件太大,方针用户打不开怎么办?

咱们要怎么才干处理这些问题,完结一个百万等级的excel数据快速导出功用呢?

百万级数据excel导出功能如何实现?

1.异步处理

做一个MySQL百万数据等级的excel导出功用,假如走接口同步导出,该接口肯定会十分简单超时

因而,咱们在做体系规划的时候,榜首挑选应该是接口走异步处理。

说起异步处理,其实有很多种,比方:运用敞开一个线程,或许运用线程池,或许运用job,或许运用mq等。

为了防止服务重启时数据的丢掉问题,咱们大多数状况下,会运用job或许mq来完结异步功用。

1.1 运用job

假如运用job的话,需求添加一张履行任务表,记载每次的导出任务。

用户点击悉数导出按钮,会调用一个后端接口,该接口会向表中写入一条记载,该记载的状况为:待履行

有个job,每隔一段时刻(比方:5分钟),扫描一次履行任务表,查出一切状况是待履行的记载。

然后遍历这些记载,挨个履行。

需求注意的是:假如用job的话,要防止重复履行的状况。比方job每隔5分钟履行一次,但假如数据导出的功用所花费的时刻超过了5分钟,在一个job周期内履行不完,就会被下一个job履行周期履行。

所以运用job时或许会呈现重复履行的状况。

为了防止job重复履行的状况,该履行任务需求添加一个履行中的状况。

具体的状况改变如下:

  1. 履行任务被刚记载到履行任务表,是待履行状况。
  2. 当job榜首次履行该履行任务时,该记载再数据库中的状况改为:履行中
  3. 当job跑完了,该记载的状况变成:完结失利

这样导出数据的功用,在榜首个job周期内履行不完,在第二次job履行时,查询待处理状况,并不会查询出履行中状况的数据,也就是说不会重复履行。

此外,运用job还有一个硬伤即:它不是立马履行的,有必定的延迟。

假如对时刻不太灵敏的业务场景,能够考虑运用该计划。

1.2 运用mq

用户点击悉数导出按钮,会调用一个后端接口,该接口会向mq服务端,发送一条mq音讯

有个专门的mq消费者,消费该音讯,然后就能够完结excel的数据导出了。

相较于job计划,运用mq计划的话,实时性更好一些。

对于mq消费者处理失利的状况,能够添加补偿机制,自动发起重试

RocketMQ自带了失利重试功用,假如失利次数超过了必定的阀值,则会将该音讯自动放入死信队列

2.运用easyexcel

咱们知道在Java中解析和生成Excel,比较有名的结构有Apache POIjxl

但它们都存在一个严重的问题就是:十分耗内存,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形式,在上层做了模型转换的封装,让运用者愈加简略便利。

需求在mavenpom.xml文件中引进easyexcel的jar包:

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.2</version>
</dependency>

之后,运用起来十分便利。

读excel数据十分便利:

@Test
publicvoidsimpleRead(){
StringfileName=TestFileUtil.getPath()+"demo"+File.separator+"demo.xlsx";
//这儿需求指定读用哪个class去读,然后读取榜首个sheet文件流会自动封闭
EasyExcel.read(fileName,DemoData.class,newDemoDataListener()).sheet().doRead();
}

写excel数据也十分便利:

@Test
publicvoidsimpleWrite(){
StringfileName=TestFileUtil.getPath()+"write"+System.currentTimeMillis()+".xlsx";
//这儿需求指定写用哪个class去读,然后写到榜首个sheet,姓名为模板然后文件流会自动封闭
//假如这儿想运用03则传入excelType参数即可
EasyExcel.write(fileName,DemoData.class).sheet("模板").doWrite(data());
}

easyexcel能大大减少占用内存的主要原因是:在解析Excel时没有将文件数据一次性悉数加载到内存中,而是从磁盘上一行行读取数据,逐一解析。

3.分页查询

百万等级的数据,从数据库一次性查询出来,是一件十分耗时的工作。

即便咱们能够从数据库中一次性查询出一切数据,没呈现衔接超时问题,这么多的数据悉数加载到应用服务的内存中,也有或许会导致应用服务呈现OOM问题。

因而,咱们从数据库中查询数据时,有必要运用分页查询。比方:每页5000条记载,分为200页查询。

publicPage<User>searchUser(SearchModelsearchModel){
List<User>userList=userMapper.searchUser(searchModel);
Page<User>pageResponse=Page.create(userList,searchModel);
pageResponse.setTotal(userMapper.searchUserCount(searchModel));
returnpageResponse;
}

每页大小pageSize和页码pageNo,是SearchModel类中的成员变量,在创建searchModel目标时,能够设置设置这两个参数。

然后在Mybatis的sql文件中,经过limit句子完结分页功用:

limit#{pageStart},#{pageSize}

其间的pagetStart参数,是经过pageNo和pageSize动态核算出来的,比方:

pageStart=(pageNo-1)*pageSize;

4.多个sheet

咱们知道,excel对一个sheet寄存的最大数据量,是有做约束的,一个sheet最多能够保存1048576行数据。否则在保存数据时会直接报错:

invalidrownumber(1048576)outsideallowablerange(0..1048575)

假如你想导出一百万以上的数据,excel的一个sheet肯定是寄存不下的。图片

因而咱们需求把数据保存到多个sheet中。图片

5.核算limit的开始位置

我之前说过,咱们一般是经过limit句子来完结分页查询功用的:

limit#{pageStart},#{pageSize}

其间的pagetStart参数,是经过pageNo和pageSize动态核算出来的,比方:

pageStart=(pageNo-1)*pageSize;

假如只要一个sheet能够这么玩,但假如有多个sheet就会有问题。因而,咱们需求从头核算limit的开始位置。

例如:

ExcelWriterexcelWriter=EasyExcelFactory.write(out).build();
inttotalPage=searchUserTotalPage(searchModel);
if(totalPage>0){
Page<User>page=Page.create(searchModel);
intsheet=(totalPage%maxSheetCount==0)?totalPage/maxSheetCount:(totalPage/maxSheetCount)+1;
for(inti=0;i<sheet;i++){
WriterSheetwriteSheet=buildSheet(i,"sheet"+i);
intstartPageNo=i*(maxSheetCount/pageSize)+1;
intendPageNo=(i+1)*(maxSheetCount/pageSize);
while(page.getPageNo()>=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包:

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-websocket</artifactId>
</dependency>

运用起来挺便利的。

咱们能够加一张专门的告诉表,记载经过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导数据:

百万级数据excel导出功能如何实现?

假如是运用job导数据:

百万级数据excel导出功能如何实现?

这两种方法都能够,能够依据实际状况挑选运用。

2023年我干了一件很有价值的工作

咱们按照这套计划的开发了代码,发到了pre环境,原本以为会十分顺利,但后边却仍是呈现了功能问题。

后来,咱们用了两招轻松处理了功能问题。

最终说一句(求重视,别白嫖我)

假如这篇文章对您有所协助,或许有所启发的话,帮忙扫描下发二维码重视一下,您的支撑是我坚持写作最大的动力。

求一键三连:点赞、转发、在看。

重视公众号:【苏三说技能】,在公众号中回复:面试、代码神器、开发手册、时刻管理有超赞的粉丝福利,别的回复:加群,能够跟很多BAT大厂的长辈沟通和学习。