mysql百万级数据导出excel文档

技术 Jan 16, 2020

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

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

最终我的方案是部署一个单独从库或集群的从库 ,单独部署一个服务器或集群服务器跑上springboot。

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

让导出功能完全脱离主业务,导出功能直接为一个新的功能模块,部署服务器也分单独的节点。这样就算导出项目因为大量的导出请求,导致宕机,也不会影响正常主业务功能。

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

查询数据库 做的是 百万级查询方案,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需要进行分片数据操作。

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

tanzhuo

咸鱼一只