MySQL 亿级数据导出excel文档

公司 SaaS 系统需要给用户提供列表数据导出 excel 文档的功能。但有的公司的列表数据如财务流水已经高达千万、亿级别,对于这样的数据导出,我们需要一个专门的解决方案。

不然到了月底,几百家公司同时导出 excel,会导致 mysql QPS 急剧上升,服务器 cpu、ram 瞬间到达报警阈值,大批量导出可能会导致服务器不可用甚至面临宕机。(提示:对于财务等敏感数据,需要做文件加密或者临时文件授权下载操作)

具体方案

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

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

具体的任务消费者通过亿级分页方案分批查询 mysql 列表数据,使用阿里 EasyExcel 将数据落盘到硬盘,输出完成后上传 oss(也可以走 oss 内网流传输,直接传到 oss 文件系统中),再删除本地硬盘文件。

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

资源限制与分页查询

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

查询数据库做的是亿级分页方案:先按条件查出目标数据的 id,放弃 limit 的大偏移量用法,改用 id 游标,保证每次 sql 查询效率都在毫秒级别;同时合理选择查询字段(索引是必须的),保证每次 mysql 流出的数据在一个大小范围之内(100kb-500kb)。

-- 用 id 游标代替大偏移量 limit,每次以上一批的最大 id 作为起点
select id from 表 where 条件 and id > 上一批最大id order by id limit 5000;

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

实际效果

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

单次查询 sql 通讯 + 执行时间按 100 ms 估算,大致时间公式:(75500000 / 5000 × 100ms) / 1000 / 60 ≈ 25min

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

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

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

总结

就算是一个数据导出功能,要把性能做到极致,需要关注的点也非常多:从 MQ 异步削峰、从库分担查询,到游标分页、内存控制与文件切割,再到部署层面的隔离,以及对未来业务增长横向扩容的考虑,方方面面都需要设计到位。