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

前言

最近我做过一个MySQL百万级别数据的excel导出功能已经正常上线使用了。

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

原始需求用户在UI界面上点击全部导出按钮就能导出所有商品数据。

咋一看这个需求挺简单的。

但如果我告诉你导出的记录条数可能有一百多万甚至两百万呢

这时你可能会倒吸一口气。

因为你可能会面临如下问题

1. 如果同步导数据接口很容易超时。
2. 如果把所有数据一次性装载到内存很容易引起OOM。
3. 数据量太大sql语句必定很慢。
4. 相同商品编号的数据要放到一起。
5. 如果走异步如何通知用户导出结果
6. 如果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 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
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<User> searchUser(SearchModel searchModel) {
    List<User> userList = userMapper.searchUser(searchModel);
    Page<User> 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<User> page = Page.create(searchModel);
   int sheet = (totalPage % maxSheetCount == 0) ? totalPage / maxSheetCount: (totalPage / maxSheetCount) + 1;
   for(int i=0;i<sheet;i++) {
      WriterSheet writeSheet = buildSheet(i,"sheet"+i);
      int startPageNo = i*(maxSheetCount/pageSize)+1;
      int endPageNo = (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年我干了一件很有价值的事情

最后说一句(求关注别白嫖我)

如果这篇文章对您有所帮助或者有所启发的话帮忙扫描下发二维码关注一下您的支持是我坚持写作最大的动力。

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

关注公众号【苏三说技术】在公众号中回复面试、代码神器、开发手册、时间管理有超赞的粉丝福利另外回复加群可以跟很多BAT大厂的前辈交流和学习。