数据库查询数据导出excel功能优化
需求
用户从前台输入sql语句,根据sql语句从数据库中查询数据,导出到excel文件,并打包上传到fastdfs服务器。(数据量达百万级别)
1. 根据sql语句从数据库查询到数据
(1) 使用ResutSet接收sql执行结果,最大行数为10000,超出报错。
(2)ResultSet结果数据集用StringBuffer接收比Set、List集合占用内存少得多,防止垃圾来不及回收,JVM内存溢出。
(3)数据用StringBuffer接收后还要从StringBuffer中取出,依次放入excel文件的每个单元格,要考虑如何放入和如何取出的问题。
2. 将数据写入excel
2.1 获取列头信息
2.2 根据列头信息存放列数据
(1)使用apache poi的SXSSFWorkbook(提升效率减少内存消耗的关键),对于SXSSFWorkbook的第二个参数,我这里设置了50,因为数据的列特别多的时候,100行也会对内存造成很大的负担,但我不知道设置太小会有什么影响,总之剩余的存到磁盘,后面写入excel之后就调用sxssWorkbook.dispose()清理相应文件了。
//大数据量读写的流式版本的SXSSFWorkbook,限制内存中最大只存储50行(默认100行),溢出的存储在硬盘的临时文件,数据存到excel后调用dispose及时清理临时文件
XSSFWorkbook workbook = new XSSFWorkbook();
SXSSFWorkbook sxssWorkbook = new SXSSFWorkbook(workbook,50);
(2) 从StringBuffer中取出数据,如何取出长度不确定的每个数据?——设置特定的分隔符(这里我用的时数学字符和希腊语做唯一分割符,保证唯一性和安全性),根据分隔符查找位置,然后取出放入excel单元格。
3. excel文件打包压缩
问题:之前公司底层写好的压缩实现类是转为字节数组,然后将字节数据作为参数传递到fastdfs服务器进行上传。
问题在于Byte数组下标限制为int类型,最大为Integer.MAX_VALUE-2,换算一下Byte数组可存放的大小为1.99G不到2G,也就是说我们查询的数据量最多不能超过2G,那肯定不够。(就算够内存也会撑不住)所以不能以Byte数组的形式传递文件。
解决思路:
(1)试过分卷压缩,但分卷压缩好像要有相应的分卷解压文件才能正常使用,但我这边只能分卷压缩,下载的问题不归我管,是上传到fastdfs服务器之后用户自行下载到本地然后解压查看的。没有分卷解压,我在本地试了一下解压不成功,只能打开查看,但报错总是不友好的,弃之。
(2)后来在知乎上看到弹道一篇Java压缩优化的好文,改成多线程并行压缩
新大陆,Java-压缩竟然还能这么优化!
(3)最后就是,使用多线程并行压缩,同时将数据通过流存到硬盘的指定File文件,文件上传到fastdfs服务器之后及时删除,这样既减少内存的消耗,也能允许大文件。
4. 文件上传fastdfs服务器
上传到fastdfs服务器主要有两种方式,一种是以字节数组的形式(之前公司就是配套的这一种),另一种是以InputStream流的形式,我采用的是后者。
前面已经说到,我们将压缩文件存放到磁盘,这样我们根据文件路径创建InputStream流就可以了。
5. 优化代码,减少new的使用
这个不用多说了,减少内存消耗。
6. 总结
以上就是我优化的整个过程,当然这只是成功之后的整体介绍,其中的艰辛各位懂得都懂,接触到新事物要从0学起,然后不断地试错,比如不了解的函数网上查不到资料一个参数就要试好久。。。
非常非常感谢我的同事们给我的帮助,帮我找思路,还要麻烦他帮我升级到测试环境,有时候一天好几次。。。总之非常非常感谢大家对我的帮助。
还有一篇提供优化思路的文章,如下:
关于Java导出100万行数据到Excel的优化方案