基于EasyExcel实现百万级别数据导出

createh52个月前 (02-01)技术教程32

前言

近期需要开发一个将百万数据量MySQL8的数据导出到excel的功能,查阅相关资料后便整理了这篇实现方案供读者参考。

需求简述

该数据表是一张用户表,包含id和name,该用户表数据量在300w左右,以自增id作为主键,而功能要求我们在一分钟之内完成百万数据导出到excel。需要注意的是,我们导出的excel格式为xlsx,它的每一个sheet只能容纳100w的数据,这也就意味着我们的数据必须以100w作为批次写到不同的sheet中。

实现思路

我们先来说说需要解决的问题:

  1. 如果一次性查询300w左右的数据可能会占据大量的内存,如果对象字段很多的情况下,很可能出现内存溢出,我们要如何解决?
  2. 每个excel文件都有sheet,并且每个sheet只能容纳100w左右的数据,对于这个问题我们要如何解决?
  3. 数据写入到excel时,有没有合适的工具推荐?

对于问题1我们采用分页查询的方式进行查询,参考自己堆内存的配置推算每次分页查询的数据量。因为问题1采用了分页查询,我们完全可以通过分页查询的次数推算出一个sheet写入了多少数据,例如我们每次分页查询50w的数据,那么每两次就可以视为一个sheet写满了,我们就可以创建一个新的sheet写入数据。

这里需要注意一点,因为我们分页查询面对的是百万级别的数据,所以随着分页的推进势必出现深分页导致查询效率势降低,所以为了提高分页查询的效率,我们可以利用查询数据有序的特性,通过id作为偏移进行分页查询。

例如我们第一次分页查询的sql语句为:

select * from t_user limit 500000;

假如我们不以id作为索引,那么第二次的分页查询sql则是:

select * from t_user limit 500000,500000;

查看该查询执行计划,可以看到该查询一次性查询到几乎全表的数据,并且还走了全秒扫描性能可想而知:

id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows   |filtered|Extra|
--+-----------+------+----------+----+-------------+---+-------+---+-------+--------+-----+
 1|SIMPLE     |t_user|          |ALL |             |   |       |   |2993040|   100.0|     |

因为我们的数据表是id自增的,所以我们查询的时候完全可以基于该特性通过上一次查询到的id作为筛选条件进行分页查询。

所以我们的分页查询可直接改为:

select * from t_user where id > 500000 limit 500000;

再次查看执行计划可以发现该查询为范围查询,查询到的数据量也少了很多,性能显著提升:

id|select_type|table |partitions|type |possible_keys|key    |key_len|ref|rows   |filtered|Extra      |
--+-----------+------+----------+-----+-------------+-------+-------+---+-------+--------+-----------+
 1|SIMPLE     |t_user|          |range|PRIMARY      |PRIMARY|8      |   |1496520|   100.0|Using where|

因为市面上比较多的excel导出工具,常见的就是Apache poi,但是它们的操作对于内存的消耗非常严重,对于我们这种大数据量的写入不是很友好,所以笔者更推荐使用阿里的EasyExcel,它对poi进行一定的封装和优化,同等数据量写入使用的内存更小。

EasyExcel

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用easyexcel可以降低到几M,并且再大的excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便。

解决上述问题之后,我们就可以说说代码实现思路了,以本文示例来说,有一张用户表有300w左右的数据,每次查询时只需查询id(4字节)和name(10字节),按照64位的操作系统来说,一个user对象所占用的内存大小为:

object header +pointer+id字段+name字段大小=8+8+4+10=30字节

因为java对象内存大小需要16位对齐,需要补齐2个字节,所以实际大小为32字节,按照笔者对于堆内存的配置,每次查询50w条数据是允许的,所以每次从数据库读取数据并转为java对象,也只需要32*500000/1024即15M内存即可。

确定每次分页查询50w条数据之后,我们就需要确定一共需要查询几个分页,然后就可以根据pageSize确定查询的页数。
因为每次查询50w条数据,所以每两次完成分页查询和写入基本上一个sheet就会满了,这时候我们就需要创建一个新的sheet进行数据写入了。

总结一下实现步骤:

  1. 查询目标数据量大小。
  2. 根据每次分页大小确定查询页数。
  3. 根据页数大小进行遍历,进行分页查询,并将数据写入到文件中。
  4. 基于页数确定sheet切换时机。

代码示例

以下便是笔者基于上述思路所实现的代码,查看日志也可以发现50w的数据查询和写入加起来只需6s。最终执行耗时也只需45s。

public static void main(String[] args) {
        SpringApplication app = new SpringApplication(WebApplication.class);
        Environment env = app.run(args).getEnvironment();
        logger.info("启动成功!!");
        logger.info("地址: \thttp://127.0.0.1:{}", env.getProperty("server.port"));

        TUserMapper userMapper = SpringUtil.getBean(TUserMapper.class);
        //计算总的数据量
        int count = (int) userMapper.countByExample(null);


        //获取分页总数
        int queryCount = 50_0000;
        int pageCount = count % queryCount == 0 ? count / queryCount : count / queryCount + 1;

        //设置导出的文件名
        String fileName = "result.xlsx";
        //设置excel的sheet号码
        int sheetNo = 1;
        //设置第一个sheet的名字
        String sheetName = "sheet-" + sheetNo;


        long start = System.currentTimeMillis();
        // 创建writeSheet
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
        //记录每次分页查询的最大值
        Long maxId = null;

        //指定文件
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, TUser.class).build()) {
            //写入每一页分页查询的数据
            for (int i = 1; i <= pageCount; i++) {
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                long queryStart = System.currentTimeMillis();
                TUserExample userExample = new TUserExample();


                //如果是第一次则直接进行分页查询,反之基于上一次分页查询的分页定位实际偏移量,筛选前n条数据以达到分页效果
                if (i == 1) {
                    PageHelper.startPage(i, queryCount, false);
                } else if (maxId != null) {
                    userExample.createCriteria().andIdGreaterThan(maxId);
                    PageHelper.startPage(0, queryCount, false);
                }


                List userList = userMapper.selectByExample(userExample);
                //更新下一次分页查询用的id
                if (CollUtil.isNotEmpty(userList)) {
                    maxId = userList.get(userList.size() - 1).getId();
                }

                long queryEnd = System.currentTimeMillis();
                logger.info("数据大小:{},写入sheet位置:{},耗时:{}", userList.size(), sheetName, queryEnd - queryStart);

                long writeStart = System.currentTimeMillis();
                excelWriter.write(userList, writeSheet);

                long writeEnd = System.currentTimeMillis();
                logger.info("本次写入耗时:{}", writeEnd - writeStart);

                //如果% 2 == 0,则说明一个sheet写入了50*2即100w的数据,需要创建新的sheet进行写入
                if (i % 2 == 0) {
                    sheetName = "sheet-" + (++sheetNo);
                    writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
                    logger.info("写满一个sheet,切换到下一个sheet:{}", sheetName);
                }
            }
        }
        long total = System.currentTimeMillis() - start;
        logger.info("导出结束,总耗时:{}", total);

    }

可能会有读者好奇笔者这个50w的数值设计思路是什么,除了考虑避免OOM以外,还考虑到每个sheet只能写入100w条的数据,为了方便通过分页查询的轮次确定当前写入的数据量大小,笔者尝试过20w、50w。
最终在压测结果上看出,50w读写耗时虽然是20w的2倍,但是IO次数却不到20w查询的二分之一,通过更少的IO操作获得更好的执行性能。

# 50w的读写耗时
com.sharkChili.webTemplate.config.WebApplication  :73   [32m                  [0;39m 数据大小:500000,写入sheet位置:sheet-1,耗时:4719
2023-12-03 10:13:58.675 INFO  com.sharkChili.webTemplate.config.WebApplication  :78   [32m                  [0;39m 本次写入耗时:2911
2023-12-03 10:14:02.517 INFO  com.sharkChili.webTemplate.config.WebApplication  :73   [32m                  [0;39m 数据大小:500000,写入sheet位置:sheet-1,耗时:3841
2023-12-03 10:14:04.860 INFO  com.sharkChili.webTemplate.config.WebApplication  :78   [32m                  [0;39m 本次写入耗时:2343

小结

以上百万级别数据导出的落地方案,可以看出着重在分页查询大小和分页查询sql上进行重点优化,通过平衡分页查询的数据量和IO次数找到合适的pageSize,再通过上一次分页查询结果定位下一次查询的id作为where条件,避免分页查询时的全秒扫描以得到符合业务需求的高性能sql,从而完成百万级别数据的高效导出。

相关文章

Hutool Java工具类库导出Excel,超级简单

作者:程序猿的内心独白原文链接:http://suo.im/5Zxx2L前言在开发应用系统的时候,导出文件是必不可放的功能。以前用过POI、easyexcel等工具的导入导出功能,但总感觉太麻烦了,代...

【干货】如何使用Java实现百万数据的Excel导出功能?

Java作为一种常用的编程语言,在实现大量数据导出功能时具有很高的效率和可扩展性。本文将介绍如何使用Java实现百万数据的Excel导出功能。一、需求分析在很多实际应用场景中,我们需要将大量数据导出到...

盘点三种Excel转SHP文件的方法(附练习数据下载)

概述数据的获取渠道是多种多样的,获取的数据格式也是多种多样,作为一名GISer,需要熟练掌握各种格式的数据之间的转换,例如本文要介绍的Excel格式的数据,经常会遇到,如果需要转换为SHP格式应该怎么...

程序员:超级简单导出Excel 工具,Hutool Java工具类库

前言在开发应用系统的时候,导出文件是必不可放的功能。以前用过POI、easyexcel等工具的导入导出功能,但总感觉太麻烦了,代码特别多,感觉并不是很好用。今天给大家介绍一款新工具,java工具类库H...

【Java技巧】高效数据传输:Java通过绑定快速将数据导出至Excel

前言把数据导出至 Excel 是很常见的需求,而数据的持久化,往往又放在数据库中。因此把数据库中的数据导出到 Excel中,成了非常普遍的一个需求。以关系型数据库为例,数据表是一个二维矩阵,但是为了易...

如何在SpringBoot中实现Excel数据导出功能?

在一些企业级的应用中,对于数据导出成Excel表格的需求是一个非常常见的需求,在SpringBoot中,我们可以借助于Apache POI库来实现这个需求,下面我们就来详细介绍一下如何在Spring...