用Java导出Excel缓慢问题的解决思路

这是本人的第一篇文章,说是文章其实也就是工作总结,希望以此记录一些遇到的问题

前几天被分配了一个需求,目前在做的系统的历史数据的导出,当数据量达到10W,20W甚至更多时,导出速度十分缓慢,OK,先做测试,发现真的好慢,仅仅6000条数据居然道出了10分钟!太夸张了,检查了一下逻辑,发现问题似乎出现在查询数据的SQL上面,仔细一卡,我去,SQL内容及其复杂,最夸张的是,居然包含了4条子查询!(ibatis的级联查询机制),每查出一条数据便要执行4次子查询SQL,6000条便要执行24000次! 难怪会慢成这样,好好,赶紧修改,去掉了子查询,将需要的字段放进查询SQL中

再次测试,果然快了不少,但是立马又发现了一个新的问题,因为之前导出工具用的是HSSFWorkBook,导出的是excel2003表,仅支持单页存储65535条数据,超出这个数据量便会报错(所以说之前完全没人发现这个问题吗!!!),没办法,既然单页支持不了那么多,那就改成多页吧,利用分页查询+创建多Sheet,实现单页最多导出50000条数据,当数据量超过50000时,便创建新的Sheet分页,以此类推,实现单页50000条的导入方式

OK,大功告成,那么来测试一下吧,直接上25W级的数据,好家伙,6分多钟,不知比之前快了多少倍,然而......还是很慢啊!!!没有达到预期需求,而且,最重要的是,数据量过大时,时不时的会出现内存溢出的情况,不行,还得继续优化

于是在网上寻求好的办法,发觉有一种叫做XSSFWorkBook的工具,可以导出excel2007表,而这种表可以存储百万级数据!除此之外还有一个专用的导出百万级的工具,名曰SXSSFWorkBook,可以进一步提升导出效率,最重要的是,不会出现内存溢出的情况

OK,既然这么好用那就来试一下,替换配置文件,修改相关类,完成后测试,吧唧,出错了,仔细一瞥,是因为模板文件没有读取到内容,导致的空指针,上网一查,大概的意思就是SXSSFWorkBook只能写入,不能读出,所以不能读取模板文件,好吧,既然不能直接读,那就手动写吧,先用HSSFWo rkBook读取出模板文件,再将模板文件的内容复制并写入SXSSFWorkBook,接着写入数据,perfect!这回总算成了,但一看时间...算了不用看了,光等待就让人抓狂了,当数据量达20W级时,异常缓慢,还不如之前的速度,思考了一下原因,因为excel2007可以支持百万级的数据,所以这次便没有采用分Sheet导出,难道是因为这个?换回分sheet模式,果不其然,导出25W数据1分钟不到(仅导出用时,不包括SQL执行时间)!看来数据量过大时还是需要分Sheet

至此,已经快接近胜利了,但是最本质的问题还没有解决,那便是SQL本身,执行速度实在太慢,以至于无论怎么提升导出速度最终时间还是不能让人满意,好吧好吧,来硬啃SQL,由于导出数据字段繁多,关联的表也异常之多,很多数据更是需要先查出子表数据才能取得,导出整个SQL子查询异常的多,光With子句就有两个,众所周知子查询是非常影响效率的,所以只好从子查询下手,最后可悲的发现,大部分的子查询并没有好的替代办法(或者说我太菜了,不知道怎么去优化),可以优化的地方只有一处子查询自身部分字段作为where匹配的条件的地方,只这一句需要执行整整43秒!果断将其改为自连接查询,跑一下,卧槽!只要3秒,子查询果然很耗时啊!

虽然SQL的修改并没有让人满意,但成果还是算勉强达到了,经测试,导出25W数据的总时间提升到了2分50秒!发布到测试环境上测试,26W数据2分24秒!虽然依然不算快,但暂时可以满足目前生产环境平均18W数据的导出量了。

至此,该需求完成,虽然这个速度在很多大佬的眼中就是个渣,但对我而言是一次很大的收获,之前从未接触过导出相关的功能,导出工具的用法也是现学的,这一次全面的了解了,另外也更加理解了request和response的工作原理,还有对于SQL的理解和优化等等,具体总结一下:

  • excel导出工具:HSSFWorkBook,XSSFWorkBook,SXSSFWorkBook,第一种用于03版的导出,后两种用于07版的导出,其中SXSSFWorkBook用于百万级数据的导出,推荐使用
  • SXSSFWorkBook似乎不能读取excel模板文件,只能写入全新的excel模板,不知道是不是真的如此还是我没有找对方法,有大佬知道的话望不吝赐教
  • 导出相关一些常用的方法:
  SXSSFWorkBook wookBook =new SXSSFWorkBook(100);

  Sheet sheet = wookBook.getSheet(String s)//按Sheet名称获取对应Sheet
  
  Sheet sheet = wookBook.getSheetAt(int i)//按下标获取对应Sheet,0开始
  
  Sheet sheet = wookBook.createSheet(String s)//创建指定名称的新Sheet
  
  Row row = sheet.createRow(int i)//创建指定下标的新行
  
  Cell cell = row.createCell(int j)//创建指定下标的新单元格
  
  cell.setCellValue(value)//为该单元格设置指定值
复制代码
  • 对于大数据量的数据,如果导出过于缓慢,可以分为多个Sheet进行处理,可以显著提升效率
  • ibatis的级联查询会影响查询效率,数据量大时应尽量避免,子查询同理
  • 一个request对应一个响应,只能返回一次response,response的输出流的wrute方法等同于页面响应,所以不能再次通过return modelAndView对象进行响应

以上便是我个人总结的一点小心得

转载于:https://juejin.im/post/5b82936f6fb9a01a2022851b

评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符 “速评一下”
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页