从系统报表页面导出 20w 条数据到本地只用了 4 秒，我是如何做到的

[bryan31]

背景

最近有个学弟找到我，跟我描述了以下场景：

他们公司内部管理系统上有很多报表，报表数据都有分页显示，浏览的时候速度还可以。但是每个报表在导出时间窗口稍微大一点的数据时，就异常缓慢，有时候多人一起导出时还会出现堆溢出。

他知道是因为数据全部加载到 jvm 内存导致的堆溢出。所以只能对时间窗口做了限制。以避免因导出过数据过大而引起的堆溢出。最终拍脑袋定下个限制为：导出的数据时间窗口不能超过 1 个月。

虽然问题解决了，但是运营小姐姐不开心了，跑过来和学弟说，我要导出一年的数据，难道要我导出 12 次再手工合并起来吗。学弟心想，这也是。系统是为人服务的，不能为了解决问题而改变其本质。

所以他想问我的问题是：有没有什么办法可以从根本上解决这个问题。

所谓从根本上解决这个问题，他提出要达成 2 个条件

• 比较快的导出速度
• 多人能并行下载数据集较大的数据

我听完他的问题后，我想，他的这个问题估计很多其他童鞋在做 web 页导出数据的时候也肯定碰到过。很多人为了保持系统的稳定性，一般在导出数据时都对导出条数或者时间窗口作了限制。但需求方肯定更希望一次性导出任意条件的数据集。

鱼和熊掌能否兼得？

答案是可以的。

我坚定的和学弟说，大概 7 年前我做过一个下载中心的方案，20w 数据的导出大概 4 秒吧。。。支持多人同时在线导出。。。

学弟听完表情有些兴奋，但是眉头又一皱，说，能有这么快，20w 数据 4 秒？

为了给他做例子，我翻出了 7 年前的代码。。。花了一个晚上把核心代码抽出来，剥离干净，做成了一个下载中心的例子

超快下载方案演示

先不谈技术，先看效果，(完整案例代码文末提供)

数据库为 mysql （理论上此套方案支持任何结构化数据库），准备一张测试表t_person。表结构如下：

CREATE TABLE `t_person` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  `age` int(11) default NULL,
  `address` varchar(50) default NULL,
  `mobile` varchar(20) default NULL,
  `email` varchar(50) default NULL,
  `company` varchar(50) default NULL,
  `title` varchar(50) default NULL,
  `create_time` datetime default NULL,
  PRIMARY KEY  (`id`)
);

一共 9 个字段。我们先创建测试数据。

案例代码提供了一个简单的页面，点以下按钮一次性可以创建 5w 条测试数据：

这里我连续点了 4 下，很快就生成了 20w 条数据，这里为了展示下数据的大致样子，我直接跳转到了最后一页

然后点开下载大容量文件，点击执行执行按钮，开始下载t_person这张表里的全部数据

点击执行按钮之后，点下方刷新按钮，可以看到一条异步下载记录，状态是P，表示pending状态，不停刷新刷新按钮，大概几秒后，这一条记录就变成S状态了，表示Success

然后你就可以下载到本地，文件大小大概 31M 左右

看到这里，很多童鞋要疑惑了，这下载下来是 csv ？ csv 其实是文本文件，用 excel 打开会丢失格式和精度。这解决不了问题啊，我们要 excel 格式啊！！

其实稍微会一点 excel 技巧的童鞋，可以利用 excel 导入数据这个功能，数据->导入数据，根据提示一步步，当中只要选择逗号分隔就可以了，关键列可以定义格式，10 秒就能完成数据的导入

你只要告诉运营小姐姐，根据这个步骤来完成 excel 的导入就可以了。而且下载过的文件，还可以反复下。

是不是从本质上解决了下载大容量数据集的问题？

原理和核心代码

学弟听到这里，很兴奋的说，这套方案能解决我这里的痛点。快和我说说原理。

其实这套方案核心很简单，只源于一个知识点，活用JdbcTemplate的这个接口：

@Override
public void query(String sql, @Nullable Object[] args, RowCallbackHandler rch) throws DataAccessException {
  query(sql, newArgPreparedStatementSetter(args), rch);
}

sql 就是select * from t_person，RowCallbackHandler这个回调接口是指每一条数据遍历后要执行的回调函数。现在贴出我自己的RowCallbackHandler的实现

private class CsvRowCallbackHandler implements RowCallbackHandler{

    private PrintWriter pw;

    public CsvRowCallbackHandler(PrintWriter pw){
        this.pw = pw;
    }

    public void processRow(ResultSet rs) throws SQLException {
        if (rs.isFirst()){
            rs.setFetchSize(500);
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++){
                if (i == rs.getMetaData().getColumnCount() - 1){
                    this.writeToFile(pw, rs.getMetaData().getColumnName(i+1), true);
                }else{
                    this.writeToFile(pw, rs.getMetaData().getColumnName(i+1), false);
                }
            }
        }else{
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++){
                if (i == rs.getMetaData().getColumnCount() - 1){
                    this.writeToFile(pw, rs.getObject(i+1), true);
                }else{
                    this.writeToFile(pw, rs.getObject(i+1), false);
                }
            }
        }
        pw.println();
    }

    private void writeToFile(PrintWriter pw, Object valueObj, boolean isLineEnd){
        ...
    }
}

这个CsvRowCallbackHandler做的事就是每次从数据库取出 500 条，然后写入服务器上的本地文件中，这样，无论你这条 sql 查出来是 20w 条还是 100w 条，内存理论上只占用 500 条数据的存储空间。等文件写完了，我们要做的，只是从服务器把这个生成好的文件 download 到本地就可以了。

因为内存中不断刷新的只有 500 条数据的容量，所以，即便多线程下载的环境下。内存也不会因此而溢出。这样，完美解决了多人下载的场景。

当然，太多并行下载虽然不会对内存造成溢出，但是会大量占用 IO 资源。为此，我们还是要控制下多线程并行的数量，可以用线程池来提交作业

ExecutorService threadPool = Executors.newFixedThreadPool(5);

threadPool.submit(new Thread(){
	@Override
	public void run() {
    下载大数据集代码
  }
}
                

最后测试了下 50w 这样子的 person 数据的下载，大概耗时 9 秒，100w 的 person 数据，耗时 19 秒。这样子的下载效率，应该可以满足大部分公司的报表导出需求吧。

最后

学弟拿到我的示例代码后，经过一个礼拜的修改后，上线了页面导出的新版本，所有的报表提交异步作业，大家统一到下载中心去进行查看和下载文件。完美的解决了之前的 2 个痛点。

但最后学弟还有个疑问，为什么不可以直接生成 excel 呢。也就是说在在RowCallbackHandler中持续往 excel 里写入数据呢？

我的回答是：

1.文本文件流写入比较快

2.excel 文件格式好像不支持流持续写入，反正我是没有试成功过。

我把剥离出来的案例整理了下，无偿提供给大家，希望帮助到碰到类似场景的童鞋们。

关注我公众号回复 导出案例 获得完整的案例代码，直接可以运行起来，页面上输入 http://127.0.0.1:8080 就可以打开文中案例的模拟页面。

[opengps]

写了这么多字，少了一个最基础的考虑：硬盘介质

[liprais]

设置下 fetchsize 就行了

[ireqhawk]

@opengps 想多了,就是个 ctrl c v 的推广账号

[npe]

如果需要合并单元格呢？

[gamexg]

为什么要写到硬盘？

[gamexg]

@gamexg #5 直接流式输出到浏览器不更好吗？

[Resource]

我还以为是什么多高明的操作

[bryan31]

@ireqhawk 个人原创，谢谢。只是在 V2EX 平台再发一下

[toan]

思路很好

[chendy]

看完了，这不是基操么？

[murmur]

内部应用怎么都可以，甚至你说我第一天晚上提交申请，第二天给你发到邮箱都没事，跟用户解释 csv 和 xls 得区别，不行

[tsening]

@murmur 是的，光这一条 csv 要用户自己转换大概就 pass 了

[AngryPanda]

直接取单表数据，4 秒？还可以更快呢。

[toesbieya]

有点没看懂，比如要导出 100W 条数据，你这个是每次查出 500 条然后写入文件，还是说一次性查出 100W 条，然后每 500 条写一次文件？

[securityCoding]

@toesbieya 看到 callback 应该是每次查 500 再回调实时写文件的

[muzuiget]

写了一大堆，最后发现是基本操作，数据库驱动不是都有 cursor 迭代器之类的设计，用多少取多少。

[justNoBody]

人家这是七年前的方案了. 如果是现在的话, 是不是可以考虑数据库分页+线程池+NIO?

[zxfreedom]

直接拉到最后，果然。。V2 上单纯分享技术已经少之又少了。

[xyjincan]

费流量啊，excel 还是有希望流式的

[todd7zhang]

我还以为是时间跨度大导致 SQL 执行效率低的问题呢，搞了半天就是个基本的 cursor.fetchall() 和 cursor 迭代...

[Foxkeh]

思路 OK, 差不多就行了.
曾经有个想法, 只返回原始数据,在前端存储拼接完毕, 然后引用个牛掰的前端插件在浏览器端生成报表, 只是当时没找着这样的插件, 后来不负责哪块业务了也没去研究了.
有木有大佬刚好用过这种方案呀?

[bryan31]

@xyjincan 是的，是可以流式的。我过两天改一个版本。这只是 7 年前的版本

[bryan31]

@Foxkeh 没研究过前端，不知道前端也可以生成 excel 。。。

[tcfenix]

分批写 s3/oss/cos 不就好了?为什么要搞这么麻烦的事情?

[x66]

我直接调用命令行 mysqldump 不是更快？

[beastk]

还是太慢了

[wakzz]

就这？

[Ritr]

散了吧，V 站人均水平吊打你，还敢发公众号

[Ritr]

@Foxkeh 前端使用 BLOB，返回数据流可以生成 excel 文件，这样服务器就不用写硬盘操作了。

[steptodream]

真看不出来有什么特别的地方 标题党

[sadfQED2]

就这？？？

[JJstyle]

一行行的写文件，真奢侈

[40EaE5uJO3Xt1VVa]

这么长篇累牍的，直接拉到最下面，果然是营销号(ˉ▽￣～) 切~~

[Donss]

看到二维码就懂了

[talen666]

真无聊。。

[Erroad]

就这

[securityCoding]

@JJstyle 哈哈 ,但凡是个正常的开发都会选择批量写入

[moonsn]

这么慢吗？

[cigarzh]

看到标题，直接拉到最后，果然不出所料

[SbloodyS]

20W 要 4 秒这么慢...

[xcstream]

20w 条数据 还以为几亿呢

[chihiro2014]

啊这。。。没有什么大改变。。。你还不如多线程走起来。或者一次性限定返回条目

[littlewing]

mysqldump 一条命令的事，为啥要整这么复杂

[littlewing]

原来是营销号，浪费时间，b 了

[zjttfs]

浪费时间

[yolee599]

直接拉到最后，果然公众号，给爷爬

[sarices]

还要用的人学会怎么到如 excel，也是搞笑

[zarte]

能导成 xlsx 的再来吧。

[Vegetable]

。。。。

[AlkTTT]

不应该发在推广里吗？

[Felldeadbird]

要导入 EXCEL 就是个麻烦事了。
既然都导出 CSV 了，为什么不直接整合成 html table 形式？ 既保留了精度，又不需要二次导入。

[encro]

既然都能导出数据了，直接开个 sql 账号，自己用 navicat 导去吧。。

[zjx1993]

能分享就挺好的，有更好的方法就说出来，嘲讽或者贬低真的没必要了。人家最起码给出了一个解决方案。

[dongisking]

标题党

[ragnaroks]

asp.net core + websocket 直接流式导出 xlsx,不考虑传输时间(内网测试),超过 2w 但不到 4w 条数据,瞬间

[tankren]

话说做报表的哪有从单表拿数据的 都是 N 张表关联的 而且现在是 powerbi 的时代

[JokeEnd]

我还以为穿越来到 5 年前的简书年代了

[ytll21]

写的挺好的，楼上一群嘲讽的人也是没看懂。乐于分享是好事，无论水平高低。何况在我看来，楼主水平还是可以的，继续加油。

[ohao]

直接拉到最后看到二维码我就放心了.......

[duanxianze]

还以为多牛的技术呢+1 没嘲讽的意思 愿意分享也不错了 就是标题党不可取

[lurenC]

乐于分享是好事，如果能指正优化也是好事。何必冷嘲热讽，搞得像个舌战群儒的场景，十分做作。

[Zheming]

在这个圈子，分享点什么都会被挑刺。希望楼主不要在意。难道天天挂在嘴边的开源精神，不是别人产出了内容分享出来应当支持，而是看到哪里不好先喷一遍？那以后也都别开源了，开源还被人挑刺儿

[xuewuchen]

假设我们不利用数据库本身的功能和命令，想单纯在可视数据上实现除了分页+多线程+数据拼接或者文件流写入之类的，不知道楼上的高手牛人们所谓的还有更快的代码实现方法是什么？
反正我水平是真心没高到那种程度，上次自己写了个几万条可视记录一次性生成 EXCEL 都高兴的很。。
V 站 NB 的人多，装 B 的人也不少。。啥也不是！

[symons]

这是啥。。。标题党么

[Salvation]

假设你说的全部都是真的，槽点太多，营销号啊？那没事了。

核心就一个看法：哪怕是一个正常有一年经验的工程师，压根都不会遇到你这个问题。

[stevenkang]

这种帖子直接拉到底部看评论，主题内容都不用看的

[hpeng]

很早以前的做法是异步任务，磁盘分段合并，对象存储中转。你这还不如我自己弄的

[siweipancc]

:) 内容都不用看，直接跳评论，说的就是你这种推广帖

[yamasa]

NM 的什么破玩意啊，毛技术含量没有。有没有老哥知道怎么 @v2 的管理员，直接把这公号狗 ban 了？