前言最近做了一个MySQL百万级数据的excel导出功能，已经正常使用了。这个功能很有意思，需要注意的细节也蛮多的。现在分享给大家，希望对大家有所帮助。原需求：用户可以通过点击UI界面的导出全部按钮导出所有商品数据。乍一看，这个需求很简单。但是如果我告诉你，导出的记录数量可能会超过一百万，甚至两百万呢？在这一点上你可能会倒吸一口凉气。因为你可能会面临以下问题：如果同步导入数据，接口很容易超时。如果一次性将所有数据加载到内存中，很容易造成OOM。如果数据量太大，sql语句肯定很慢。相同产品编号的数据应放在一起。如果是异步的，导出结果如何通知用户？excel文件太大，目标用户打不开怎么办？如何解决这些问题，实现百万级excel数据的快速导出功能？1、异步处理做一个MySQL百万数据级别的excel导出功能，如果使用接口同步导出，接口肯定很容易超时。因此，我们在进行系统设计时，首选应该是使用接口的异步处理。说到异步处理，其实有很多种，比如：使用开线程，或者使用线程池，或者使用作业，或者使用mq等。为了防止服务时数据丢失重启，大多数情况下，我们会使用job或者mq来实现异步功能。(1)使用job如果使用job，需要添加一个执行任务表来记录每一个导出任务。当用户点击全部导出按钮时，会调用后台接口，接口会向表中写入一条记录，该记录的状态为：待执行。有一个job每隔一段时间（比如：5分钟）扫描一次执行任务表，找出所有状态为待执行的记录。然后遍历这些记录，一条一条执行。需要注意的是，如果使用一个job，一定要避免重复执行。例如，作业每5分钟执行一次，但如果数据导出功能耗时超过5分钟，无法在一个作业周期内执行，则将在下一个作业执行周期执行。因此，在使用作业时可能会出现重复执行的情况。为了防止重复执行作业，执行任务需要添加一个执行状态。具体状态变化如下：执行任务刚被记录到执行任务表中，处于待执行状态。当job第一次执行执行任务时，数据库中记录的状态变为：executing。当作业完成运行时，记录的状态变为：完成或失败。这样就无法在第一个作业周期完成导出数据的功能。执行第二个job时，会查询pending状态，但不会查询executing状态的数据，也就是说不会重复执行。另外，使用job还有一个缺陷：不是立即执行，有一定的延迟。如果业务场景对时间要求不是很高，可以考虑使用该方案。(2)当mq用户点击exportall按钮时，会调用一个后台接口，该接口会向mq服务器发送一条mq消息。有专门的mqconsumer消费消息，然后可以导出excel数据。与job方案相比，mq方案的实时性更好。对于mqconsumer处理失败，可以增加补偿机制，自动发起重试。RocketMQ自带失败重试功能。如果失败次数超过一定阈值，消息将自动放入死信队列。2、使用easyexcel，我们知道用Java解析生成Excel。比较出名的框架有ApachePOI和jxl。但它们都有一个严重的问题：它们消耗大量内存。POI有一套SAX模式的API，可以在一定程度上解决一些内存溢出问题。不过POI还是有一些缺陷，比如07版本的Excel解压和解压存储都是在内存中完成的，内存消耗还是很大的。如果使用传统的ApachePOI框架处理百万级excel数据导出功能，可能会消耗大量内存，容易出现OOM问题。并且easyexcel重写了POI对07版Excel的分析。之前用POIsax解析一个3M的excel，需要100M左右的内存。如果改用easyexcel，可以减少到几M，Excel再大也不会出现内存溢出；03版本依托POI的sax模式，将模型转换封装在上层，使用起来更加简单方便。easyexcel的jar包需要在maven的pom.xml文件中引入：com.alibabaeasyexcel3.0.2之后，使用起来非常方便。读取excel数据非常方便：@TestpublicvoidsimpleRead(){StringfileName=TestFileUtil.getPath()+"demo"+File.separator+"demo.xlsx";//这里需要指定读取哪个类，然后读取第一个sheet文件流会自动关闭EasyExcel.read(fileName,DemoData.class,newDemoDataListener()).sheet().doRead();}它写excel数据也很方便：@TestpublicvoidsimpleWrite(){StringfileName=TestFileUtil.getPath()+"write"+System.currentTimeMillis()+".xlsx";//这里需要指定使用哪个类进行写入，然后写入到第一个sheet，名称为模板，文件流会自动关闭//这里如果要使用03，可以传入excelType参数EasyExcel.write(fileName,DemoData.class).sheet("template").doWrite(data());}easyexcel可以大大降低内存占用原因是：解析Excel时，文件数据没有加载到内存中一次全部，但数据是从磁盘中逐行读取并逐行解析的。3、分页查询百万级数据，一次性从数据库中查询，是一项非常耗时的工作。即使我们可以一次性从数据库中查询到所有的数据，并且没有连接超时的问题，那么多的数据加载到应用服务的内存中，可能会导致应用服务出现OOM问题。因此，当我们从数据库中查询数据时，使用分页查询是很有必要的。例如：每页5000条记录，分成200页查询。publicPagesearchUser(SearchModelsearchModel){ListuserList=userMapper.searchUser(searchModel);PagepageResponse=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）*页面大小;4、多工作表我们知道Excel对一个工作表存储的数据量有最大限制，一个工作表最多可以存储1048576行数据。不然保存数据的时候直接报错：invalidrownumber(1048576)outsideallowablerange(0..1048575)如果要导出百万以上的数据，一张excel肯定存不下.所以我们需要将数据保存到多个工作表中。5.计算极限的起始位置。前面说过，我们一般通过limit语句来实现分页查询功能：limit#{pageStart},#{pageSize}其中pagetStart参数是通过pageNo和pageSize动态计算的，例如：pageStart=(pageNo-1)*页面大小;如果只有一张，可以这样玩，但是如果有多张，就会出问题。因此，我们需要重新计算极限的起始位置。例如：ExcelWriterexcelWriter=EasyExcelFactory.write(out).build();inttotalPage=searchUserTotalPage(searchModel);if(totalPage>0){Pagepage=Page.create(searchModel);intsheet=(totalPage%maxSheetCount==0)?totalPage/maxSheetCount:(totalPage/maxSheetCount)+1;for(inti=0;i=startPageNo&&page.getPageNo()<=endPageNo){page=searchUser(searchModel);如果（CollectionUtils.isEmpty（page.getList（）））{打破;}excelWriter.write(page.getList(),writeSheet);page.setPageNo(page.getPageNo()+1);}}}可以实现分页查询，将数据导出到不同的excel表中。6.上传文件到OSS现在我们导出excel数据的方案已经改成异步了，直接同步返回excel文件给用户是不可能的。因此，我们需要先将excel文件存放在一个地方，方便用户在需要的时候访问。此时，我们可以直接将文件上传到OSS文件服务器。通过OSS提供的上传接口，上传excel成功后，会返回文件名和访问路径。我们可以把excel名称和访问路径保存到表中，方便后面直接通过浏览器访问远程excel文件。但是，如果将excel文件保存到应用服务器上，可能会占用大量的磁盘空间。一般建议应用服务器和文件服务器分开。应用服务器需要更多的内存资源或CPU资源，而文件服务器需要更多的磁盘资源。7、通过WebSocket推送通知通过上述功能已经导出excel文件，上传到OSS文件服务器。接下来的任务就是将这个excel的结果导出，不管是成功还是失败，并通知目标用户。页面有提示：正在导出Excel数据，请耐心等待。然后用户可以主动刷新当前页面，获取本地导出到excel的结果。但是这个用户交互功能并不是很友好。另一种方式是通过webSocket建立长连接推送实时通知。如果使用SpringBoot框架，直接导入webSocket的相关jar包即可：>使用起来很方便。我们可以添加一个专门的通知表来记录通过webSocket推送的通知的标题、用户、附件地址、阅读状态、类型等信息。更好的可追溯性通知记录。webSocket向客户端推送通知后，用户右上角收件箱实时出现一个小窗口，提示导出excel功能是否成功，并有文件下载链接。当前通知的阅读状态为未读。用户点击这个窗口可以看到通知的详细内容，然后通知状态变为已读。8.条目总数是可配置的。我们在做百万级数据导入的需求的时候，是针对用户的，也可能是针对运维的同学。其实我们应该从实际用户的角度出发，思考这个需求是否合理。这个百万级的excel文件对用户有什么用，能不能在自己的电脑上打开excel文件，电脑会不会死机太多，导致文件无法使用。如果功能上线后真的发生了这些事情，那么导出excel就没有意义了。因此，将记录总数做成可配置的是非常有必要的，这个配置可以根据用户的实际情况进行调整。例如：用户发现excel中有50万条数据，可以正常访问和操作excel。这时候我们可以调整条目总数为50万条，截取冗余数据。事实上，在用户的操作界面中增加更多的查询条件可能更合理。通过修改查询条件，多次导入数据，用户可以实现导出所有数据的功能。另外建议在分页查询的时候可以配置每页的大小。通过条目总数和每页大小，可以动态调整记录条数和分页查询条数，以帮助更好地满足用户的需求。9、按货号订货前要求将相同货号的数据放在一起。例如：序号商品名称仓库名称价格1笔记本北京仓库72341笔记本上海仓库72351笔记本武汉仓库72362平板成都仓库72362平板大连仓库3339但是我们做了分页查询功能，无法一次性查询到数据，直接在Java内存中分组或排序。因此，我们需要考虑在SQL语句中使用orderbyproductnumber，先将数据按顺序排列，然后再查询数据，这样就可以将相同的产品编号和不同的仓库数据放在一起。此外，还有一种情况需要考虑。通过配置的总记录数截取所有数据。但是，如果没有在最后一页查找到最后一个项目编号，则可能导出的最后一个项目的数据不完整。因此，我们需要在程序中处理删除最后一项。但是添加orderby关键字进行排序后，如果查询SQL中连接了很多表，可能会导致查询性能下降。那该怎么办呢？总结最后用两张图总结一下excel异步导入数据的过程。如果使用mq导入数据：如果使用job导入数据：两种方式都可以，根据实际情况选择使用。我们按照这个方案开发代码，发到pre环境。我们原以为会很顺利，但是后来还是出现了性能问题。后来我们用了两个trick轻松解决了性能问题。
