1、前言

公司有个需求需要增加菜单访问明细的EXCEL导出功能。原先也有类似的导出功能但是这次的菜单访问明细的数据较大，我们在业务层面限制了最多导出一个月+单个菜单的访问明细，在这种情况下单个菜单的数量还是会有30W+的数据产生。如果按照旧的导出方案会出现OOM的问题。所以本次将导出功能加以优化，做到100W级别的导出支撑。

2、优化EXCEL导出过程

EXCEL导出方法（参数）{
    1、查询数据库获得结果集合
    2、把结果集序列化为二进制流
    3、创建输入流
    4、通过输入流写入数据
    5、关闭输入流
}

导出EXCEL大概的伪代码流程就是这样，数据量大太大时会有以下问题

一、查询数据库耗时过长、占用数据库连接数

二、查询出来的数据过大占用内存，导致频繁FullGC

我们发现问题二和问题一的底层逻辑是一样的，那么我们可以考虑一次性查询少点的数据，同时将这些数据写入文件后再去查询。

所以可以想到利用分页查询。使用分页查询后又会出现问题，Mysql 的分页查询LIMIT offset,size查询的页数越多效率越低，100w条数据到越到后面耗时越长。不仅如此，如果用户导出的数据包括了当天的数据并且使用时间降序查询，那么也会出现分页脏数据情况。

又因为这种分页查询有连续性的特点，所以我们采用了一种在移动端常用的【滚动翻页】。该方法的前提是需要有一个递增的主键，正好这种访问数据是自增id且按照时间顺序递增。 （这种方法局限性蛮多的比如说需要有个递增的键、会导致其他的业务索引失效等，但是在某些特定的场景又非常的好用）

SELECT * FROM tableX WHERE id > #{lastBatchMaxId} [其他条件] ORDER BY id [ASC|DESC](这里一般选用ASC排序) LIMIT ${size}

该方法不适用offset，利用上一次查询的数据的最后条数据的id做为过滤条件，来保证查询的速度。（分页查询也可以用流式查询替代以提高效率）

EXCEL导出方法（参数）{
    1、创建输入流
    long lastMaxId = 0L;
    while(true){
        2、查询数据库获得结果集合(利用lastMaxId过滤)
            2-1、结果集为空 break;
        3、把结果集序列化为二进制流
        4、创建输入流
        5、通过输入流写入数据
        lastMaxId = 结果集中最后一条数据的id;
    }
    6、关闭输入流
}

以上就是百万级别数据EXCLE导出优化的核心了。利用lastMaxId做到到查询条件向前滚动，直到结果集为空。同时使用limt来限制每次查询的记录条数，这个值按需去设定，有了这个限制一个是保证每次数据库查询的时间不会太长、一个是保证内存占用在一个可控制的范围内不至于OOM。

3、小结

这篇文章详细地分析大数据量导出的性能优化，最要侧重于内存优化。该方案实现了在尽可能少占用内存的前提下，在效率可以接受的范围内进行大批量的数据导出。这是一个可复用的方案，类似的设计思路也可以应用于其他领域或者场景，不局限于数据导出。

百万级EXCEL导出优化

1、前言

2、优化EXCEL导出过程

3、小结