MySQL 百万级数据导出excel文档

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

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

最终我的方案是部署一个数据库从库 ,再单独部署一个服务器或集群服务器跑上Springboot项目作为MQ的消费者集群,所有的导出请求均落盘至MQ队列中,Springboot会采用拉模式主动拉取MQ队列中的导出Excel导出消息进行执行,合理的使用了MQ的削峰以及异步功能。

消费者这边通过分页分批量查询mysql列表数据,使用ali easy excel api进行硬盘输出,完成输出后上传oss,删除本地硬盘文件。

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

配合断路器配置,严格控制项目拉取的请求数量,保证服务的健壮性。

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

在 select 字段  in (idList) 的方式进行分页分批获取mysql数据。

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

之后经过测试155w的数据导出。共用时8分钟(其实可以更快,合理添加索引等等)。 已经可以支持当前业务场景需求。

内存增长只有10m左右的振幅,因为list 每一次只获取2500条数据 ,数据输出硬盘之后 list 内存已经被回收,所以内存增长很少。硬盘io每次3-4M。

excel 文档单片只能写入100w左右的行数,过了100w需要进行分片数据操作。并且在单文件体积过大时,需要对excel文件进行切割存储后压缩,避免内存占用太高导致客户无法打开文件。

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

随心笔记

hi 欢迎留言,共同探讨IT技术~