前言
最近我做过一个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包：
<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导数据：
假如是运用job导数据：
这两种方法都能够，能够依据实际状况挑选运用。
2023年我干了一件很有价值的工作
咱们按照这套计划的开发了代码，发到了pre环境，原本以为会十分顺利，但后边却仍是呈现了功能问题。
后来，咱们用了两招轻松处理了功能问题。
最终说一句(求重视，别白嫖我)
假如这篇文章对您有所协助，或许有所启发的话，帮忙扫描下发二维码重视一下，您的支撑是我坚持写作最大的动力。
求一键三连：点赞、转发、在看。
重视公众号：【苏三说技能】，在公众号中回复：面试、代码神器、开发手册、时刻管理有超赞的粉丝福利，别的回复：加群，能够跟很多BAT大厂的长辈沟通和学习。
