SpringBoot 实现 Excel 导入导出,百万数据量,性能爆表!

点击关注公众号,Java干货及时送达👇

18290146ac833ba4a01f2738a5a661e8.jpeg


前言

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

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

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

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

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

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

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

  1. 如果同步导数据,接口很容易超时。

  2. 如果把所有数据一次性装载到内存,很容易引起OOM。

  3. 数据量太大sql语句必定很慢。

  4. 相同商品编号的数据要放到一起。

  5. 如果走异步,如何通知用户导出结果?

  6. 如果excel文件太大,目标用户打不开怎么办?

我们要如何才能解决这些问题,实现一个百万级别的excel数据快速导出功能呢?

2db79328d5da8f32e2bc4e4bd3121049.png

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
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肯定是存放不下的。25641d86f6bf6105e3c387f6cfe4acd5.png

因此我们需要把数据保存到多个sheet中。fde78f6b732423515c71fb649ecf76a1.png

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导数据:6f8e14a0ef85215ff1d00ac34411e40c.png

如果是使用job导数据:d6b6a89060dcfcb4ae64e5baa1106a98.png

热门内容:
用上这几个开源管理系统做项目,领导看了直呼专业!
  • ChatGPT 连夜迭代:你老婆不好使了

  • 动态可监控线程池,你还没用起来吗?

  • 一个超级牛逼的实战项目!同事们模仿了一个月……

  • 955.WLB 不加班公司名单,2023 年最新版!新增 5 家公司

  • 图文详解 Java 泛型,写得太好了!

 

f1b55a17ef65b11b1e182834397d9080.jpeg

 
最近面试BAT,整理一份面试资料《Java面试BAT通关手册》,覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。
获取方式:点“在看”,关注公众号并回复 666 领取,更多内容陆续奉上。

明天见(。・ω・。)

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/23279.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

CVE 官网提交流程

CVE 官网提交流程 一、进入官网申请二、公开流程三、拓展四、补充&#xff08;部分公司单独提交cve申请&#xff09; 一、进入官网申请 https://cveform.mitre.org/ 选择申请CVE ID即可。 Vulnerability type翻译&#xff08;chatgpt-3.5&#xff09;buffer overflow缓冲区溢…

如何解决谷歌Chrome浏览器空白页的问题

如何解决谷歌Chrome浏览器空白页的问题 谷歌Chrome浏览器突然不打开任何网页,无论是任何站点(如http://www.baidu.com), 还是Chrome浏览器的设置页面(chrome://settings/), 扩展页面 ( chrome://extensions/ ) , 所有其他浏览器(如IE浏览器,Mozilla Firefox浏览器等)很好…

Vivaldi 6.1发布,可绕过微软限制使用Bing Chat

ChatGPT狂飙160天&#xff0c;世界已经不是之前的样子。 新建了人工智能中文站https://ai.weoknow.com 每天给大家更新可用的国内可用chatGPT资源 最新版本的 Vivaldi 可在桌面端伪装成 Edge&#xff0c;使其用户受益&#xff0c;并为工作空间和标签增加了更多的功能。 支持微…

安装Chatgdt搜索答辩老师常提的问题

&#x1f345;选题推荐——以防找不到我们&#xff0c;点击上方订阅专栏✌✌ 2024年计算机Java Python安卓APP微信小程序asp.net项目PHP毕业设计2000个热门选题推荐计算机毕业设计如何选题&#xff1f; 计算机毕业设计开题报告如何书写 论文的书写如何写出一篇好论文 计算机毕业…

重磅!微软Bing爆炸级更新!答案图文并茂,网友:逼 ChatGPT 放大招?

点击下方卡片&#xff0c;关注“CVer”公众号 AI/CV重磅干货&#xff0c;第一时间送达 点击进入—>【Transformer】微信技术交流群 杨净 明敏 发自 凹非寺转载自&#xff1a;量子位&#xff08;QbitAI&#xff09; 所有人都能上手微软Bing了&#xff01; 今天&#xff0c;微…

chatgpt赋能python:如何在Python中去掉空行

如何在Python中去掉空行 如果你经常使用Python进行文本处理&#xff0c;你可能会遇到需要去掉文件中的空行&#xff08;或多余的空格&#xff09;的情况。这个问题看似简单&#xff0c;但实际上有多种方法可以解决。在本文中&#xff0c;我们将介绍其中的两种方法&#xff0c;…

chatgpt赋能python:如何在Python中输入空行

如何在Python中输入空行 如果你是一名有10年Python编程经验的工程师&#xff0c;那么你肯定知道Python作为一门高级编程语言&#xff0c;在处理文本时非常灵活。然而&#xff0c;在编写Python代码的过程中&#xff0c;有时候需要在文本中插入一些空行以使其更加易读。本文将介…

chatgpt赋能python:Python如何去除空行?

Python如何去除空行&#xff1f; 在Python编程中&#xff0c;经常会遇到需要去除文本文件或字符串中的空行的情况。空行通常是在文件或字符串中不希望存在的&#xff0c;因为它们可能会影响代码的可读性和执行效率。但是&#xff0c;如何才能在Python中高效地去除空行呢&#…

chatgpt赋能python:如何在Python中添加空行?

如何在Python中添加空行&#xff1f; 如果你是一个有经验的Python工程师&#xff0c;在编写代码时你可能会遇到需要添加空行的情况。但是有几种方法可以实现这一点&#xff0c;你应该用哪种方法呢&#xff1f;在本文中&#xff0c;我们将探讨如何在Python中添加空行以及各种添…

陶瓷工厂的进销存管理系统的设计与实现

毕业设计&#xff08;论文&#xff09; 论文题目XXX陶瓷工厂的进销存管理系统的设计与实现Thesis TopicThe Design and Implementation of the XXX Ceramic Factory Inventory Management System 毕业设计&#xff08;论文&#xff09;任务书 第1页 毕业设计&#xff08;论文&a…

【小程序全面解析】生命周期、常用组件,代码示例和使用场景

文章目录 人工智能福利文章前言小程序生命周期应用生命周期页面生命周期使用场景 小程序的基础组件视图容器表单组件媒体组件 总结写在最后 ✍创作者&#xff1a;全栈弄潮儿 &#x1f3e1; 个人主页&#xff1a; 全栈弄潮儿的个人主页 &#x1f3d9;️ 个人社区&#xff0c;欢迎…

超级实用永久免费的软著源代码材料格式文档生成辅助工具——软著源代码生成工具,软著文档生成,软著鉴别材料生成,软著源代码辅助生成

截止2023年5月工具一万次下载啦&#xff01;用过都说好 软著的全称是计算机软件著作权 申请软件著作权登记的时候会被要求提交60页的源代码。没有经验的开发者朋友第一次申请的时候难免会遇到因代码文档格式不正确、代码里含有其他版权信息等原因被要求补正的问题&#xff0c;从…

AidLux AI 应用案例悬赏征集活动正式启动!

ChatGPT爆火之后&#xff0c;AI领域的人才需求迎来了疯狂增长&#xff0c;AI学习也一跃成为业界大热门。 但AI囊括知识广、学习周期长&#xff0c;要克服理论、实战等多重阻碍并不容易。 而持续降低AI学习门槛是我们一直在做的事情。 为此&#xff0c;我们举办了多期AidLux …

教你玩转ChatGPT

文章目录 前言一、chatgpt是什么&#xff1f;二、使用步骤1.创建chatgpt账号2.获取机构id和api-keys3.准备环境3.1问题 4.示例3.1查看所有可用模型3.2使用文本模型进行问答3.2图像生成3.2图像编辑3.2图像变化3.2内容过滤 总结 前言 随着人工智能的不断发展&#xff0c;机器学习…

ART虚拟机 | 接口方法调用的具体实现

Java语言中&#xff0c;一个新创建的类只能继承一个父类&#xff0c;但是可以实现多个接口。这两种不同的语言特性使得多态在虚拟机中的实现也不相同。具体而言&#xff0c;当我们调用virtual方法时&#xff0c;可以使用对象所属类的virtual table进行派发&#xff0c;其中的元…

[MATLAB学习笔记]peaks函数1013(2)

>> Z peaksZ 1 至 10 列0.0001 0.0001 0.0002 0.0004 0.0007 0.0011 0.0017 0.0025 0.0034 0.00430.0001 0.0002 0.0004 0.0006 0.0010 0.0017 0.0026 0.0037 0.0051 0.00640.0002 0.0003 0.0005 0.000…

chatgpt赋能python:Python代码转为C语言——提高效率的必经之路

Python代码转为C语言——提高效率的必经之路 Python是一种高级编程语言&#xff0c;具有易学易用的优点&#xff0c;因此越来越多的程序员选择使用Python来开发应用程序和脚本。但是&#xff0c;在开发高性能应用程序时&#xff0c;Python的效率问题会成为拦路虎。因此&#x…

chatgpt赋能python:Python数据转换:介绍和使用方法

Python数据转换&#xff1a;介绍和使用方法 在Python编程中&#xff0c;数据转换是一项常用的操作&#xff0c;通过数据转换&#xff0c;可以把某种数据类型转换成其他数据类型。这对于处理数据和提高代码的可读性和可维护性都是非常有帮助的。在本文中&#xff0c;我们将介绍…

快讯 | 微软开源“傻瓜式”类ChatGPT模型训练工具;汤恩智能获数千万元A轮融资

一分钟速览新闻点 小米机器人公司成立&#xff0c;注册资本5000万元 国产移动机器人“大脑”杀出黑马 汤恩智能获数千万元A轮融资 微软开源“傻瓜式”类ChatGPT模型训练工具 俄制“马克”机器人将能发射多达100 架无人机 现代联手科研机构开发探月机器人&#xff0c;计划2…

自然语言模型的哲学小谈

近期&#xff0c;以chatGPT为代表的大语言模型表现非常惊艳。“In Context Learning”、“Instruct”1&#xff0c;以及推理能力&#xff0c;很难不让我们期待未来人工智能的发展&#xff0c;同时冷静思考一下为什么自然语言模型能够取得巨大进步。 文章目录 1 放空大脑从0开始…