Java 如何从数据库中导出100W的数据又快又不内存溢出?

最开始是先从数据库中查询完数据读入内存,结果直接OOM,后来想着可以分页导出,每次5000条,共查询200次,但是这种因为查询需要耗时间(多表查询),…
关注者
11
被浏览
34,520
登录后你可以
不限量看优质回答 私信答主深度交流 精彩内容一键收藏

最近我做过一个MySQL百万级别数据的excel导出功能,已经正常上线使用了,快速很快,也没有出现内存溢出的问题,给大家参考一下。

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

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

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

Java突击队: susan.net.cn ,一个能够快速提升java的神奇网站。

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

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

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

  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);