MySQL 亿级数据导出excel文档

前言

公司SaaS系统需要给用户提供列表数据导出 excel 文档的功能。

但有的公司的列表数据如财务流水已经高达千万，亿级别，对于这样的数据导出，我们需要提出一个解决方案。不然在月底几百家公司进行导出excel，将会导致mysql QPS急剧上升，服务器cpu，ram，瞬间到达报警阀门。大批量导出可能会导致服务器不可用甚至面临宕机（提示：对于财务等敏感数据，需要做文件加密或者临时文件授权下载操作）。

具体方案

方案的出发点：大批量导出，不能影响正常业务运行。

单独部署一个数据库从库节点进行查询压力分担，再单独部署多组服务器跑上Springboot项目作为MQ的消费者集群，所有的导出请求均请求至MQ队列中，Springboot会采用拉模式主动拉取MQ队列中的导出Excel任务消息进行执行，合理的使用了MQ的削峰填谷以及异步功能。并在SpringBoot项目中严格分配线程池资源。

具体的任务消费者通过亿级分页方案分批量查询mysql列表数据，并使用ali easy excel api进行数据硬盘落盘，完成输出后上传oss（也可以使用oss内网流传输，直接传到oss文件系统中），再删除本地硬盘文件。

这样的设计让导出功能模块完全脱离主业务，导出功能直接为一个新功能模块，部署服务器也分单独的节点。这样就算导出项目因为大量突如其来的导出请求，导致宕机（一般也不会，因为MQ可以很好的避免此问题），也不会影响正常主业务功能。

资源限制

配合线程池的资源限制，以及限制单客户最大同时导出文件数，严格控制JVM单次拉取的任务数量，保证服务的健壮性。

查询数据库 做的是亿级查询方案，select id where 条件 limit 获取数据目标id（最好是放弃limit的偏移量使用，保证每次sql查询效率都在毫秒级别），并合理查询字段信息（索引是必须的）。保证每次mysql的流出数据 在一个数据大小范围之内（100kb-500kb）。

封装抽象工厂，封装接口，提供给开发人员实现各种功能列表的导出。开发人员无需关心具体如何去分页分批获取数据，如何生成excel，如何进行上传。他们只需要写好导出sql查询列表，专注数据查询即可。

之后经过测试7550w的数据导出。共用时20多分钟（其实可以更快，合理添加索引，以及多线程进行任务分解查询导出）。 已经可以支持当前业务场景需求。

单次查询sql通讯+执行时间设立 100 ms 费时

大致时间公式计算：(755000000 / 5000 * 100ms ) / 1000 * 60 = 25min

单客户导出内存增长只有20m左右的振幅，因为 dataList 单次只获取5000条数据 ，数据输出硬盘之后 list 对象内存已经被回收，所以内存增长很少。硬盘io每次3-4M。多客户需要进行线程资源限制，总导出任务数量限制。防止JVM 出现 OOM。

Excel 文档单片只能写入100w左右的行数，过了单页100w条记录后需要进行分工作薄处理，工作薄的分割需要你根据计算机内存进行考虑，总计导出1亿条记录的话，单文件体积过大，后续还需要对excel文件进行切割存储后压缩，避免单文件过大，打开时内存占用太高，导致客户下载后无法打开文件。

数据库每次吞吐数据也不能消耗太多性能。需要多批次的进行7550w条数据的导出。输出到硬盘的 excel 文件中，输出完成到硬盘之后zip压缩直接上传至oss ，最后提供oss资源路径到前端app或web游览器给予客户端下载。

总结

就算是一个数据导出功能，要把性能做到极致，需要关注的点是非常多的，需要设计，部署层面上的合理，以及对未来业务增长横向扩容的考虑。方方面面都是需要考虑到的。