V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
bryan31
V2EX  ›  Java

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

  •  
  •   bryan31 · 2020-08-24 15:14:18 +08:00 · 5969 次点击
    这是一个创建于 1313 天前的主题,其中的信息可能已经有所发展或是发生改变。

    背景

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

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

    他知道是因为数据全部加载到 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 条测试数据:

    1

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

    2

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

    3

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

    4

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

    5

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

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

    6

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

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

    原理和核心代码

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

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

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

    sql 就是select * from t_personRowCallbackHandler这个回调接口是指每一条数据遍历后要执行的回调函数。现在贴出我自己的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 就可以打开文中案例的模拟页面。

    img

    69 条回复    2020-08-26 13:11:33 +08:00
    opengps
        1
    opengps  
       2020-08-24 15:15:41 +08:00
    写了这么多字,少了一个最基础的考虑:硬盘介质
    liprais
        2
    liprais  
       2020-08-24 15:20:43 +08:00
    设置下 fetchsize 就行了
    ireqhawk
        3
    ireqhawk  
       2020-08-24 15:21:08 +08:00   ❤️ 14
    @opengps 想多了,就是个 ctrl c v 的推广账号
    npe
        4
    npe  
       2020-08-24 15:22:24 +08:00 via iPhone
    如果需要合并单元格呢?
    gamexg
        5
    gamexg  
       2020-08-24 15:24:18 +08:00
    为什么要写到硬盘?
    gamexg
        6
    gamexg  
       2020-08-24 15:24:56 +08:00
    @gamexg #5 直接流式输出到浏览器不更好吗?
    Resource
        7
    Resource  
       2020-08-24 15:44:24 +08:00   ❤️ 1
    我还以为是什么多高明的操作
    bryan31
        8
    bryan31  
    OP
       2020-08-24 15:45:50 +08:00
    @ireqhawk 个人原创,谢谢。只是在 V2EX 平台再发一下
    toan
        9
    toan  
       2020-08-24 16:08:17 +08:00
    思路很好
    chendy
        10
    chendy  
       2020-08-24 16:11:48 +08:00
    看完了,这不是基操么?
    murmur
        11
    murmur  
       2020-08-24 16:14:59 +08:00   ❤️ 4
    内部应用怎么都可以,甚至你说我第一天晚上提交申请,第二天给你发到邮箱都没事,跟用户解释 csv 和 xls 得区别,不行
    tsening
        12
    tsening  
       2020-08-24 16:19:53 +08:00
    @murmur 是的,光这一条 csv 要用户自己转换大概就 pass 了
    AngryPanda
        13
    AngryPanda  
       2020-08-24 16:20:20 +08:00
    直接取单表数据,4 秒?还可以更快呢。
    toesbieya
        14
    toesbieya  
       2020-08-24 16:24:24 +08:00
    有点没看懂,比如要导出 100W 条数据,你这个是每次查出 500 条然后写入文件,还是说一次性查出 100W 条,然后每 500 条写一次文件?
    securityCoding
        15
    securityCoding  
       2020-08-24 16:31:38 +08:00
    @toesbieya 看到 callback 应该是每次查 500 再回调实时写文件的
    muzuiget
        16
    muzuiget  
       2020-08-24 16:33:54 +08:00
    写了一大堆,最后发现是基本操作,数据库驱动不是都有 cursor 迭代器之类的设计,用多少取多少。
    justNoBody
        17
    justNoBody  
       2020-08-24 16:36:52 +08:00
    人家这是七年前的方案了. 如果是现在的话, 是不是可以考虑数据库分页+线程池+NIO?
    zxfreedom
        18
    zxfreedom  
       2020-08-24 16:40:20 +08:00   ❤️ 4
    直接拉到最后,果然。。V2 上单纯分享技术已经少之又少了。
    xyjincan
        19
    xyjincan  
       2020-08-24 16:45:47 +08:00
    费流量啊,excel 还是有希望流式的
    todd7zhang
        20
    todd7zhang  
       2020-08-24 16:50:06 +08:00
    我还以为是时间跨度大导致 SQL 执行效率低的问题呢,搞了半天就是个基本的 cursor.fetchall() 和 cursor 迭代...
    Foxkeh
        21
    Foxkeh  
       2020-08-24 16:57:58 +08:00
    思路 OK, 差不多就行了.
    曾经有个想法, 只返回原始数据,在前端存储拼接完毕, 然后引用个牛掰的前端插件在浏览器端生成报表, 只是当时没找着这样的插件, 后来不负责哪块业务了也没去研究了.
    有木有大佬刚好用过这种方案呀?
    bryan31
        22
    bryan31  
    OP
       2020-08-24 16:58:50 +08:00
    @xyjincan 是的,是可以流式的。我过两天改一个版本。这只是 7 年前的版本
    bryan31
        23
    bryan31  
    OP
       2020-08-24 16:59:26 +08:00
    @Foxkeh 没研究过前端,不知道前端也可以生成 excel 。。。
    tcfenix
        24
    tcfenix  
       2020-08-24 17:03:32 +08:00
    分批写 s3/oss/cos 不就好了?为什么要搞这么麻烦的事情?
    x66
        25
    x66  
       2020-08-24 17:19:44 +08:00
    我直接调用命令行 mysqldump 不是更快?
    beastk
        26
    beastk  
       2020-08-24 17:38:28 +08:00 via iPhone
    还是太慢了
    wakzz
        27
    wakzz  
       2020-08-24 17:48:39 +08:00
    就这?
    Ritr
        28
    Ritr  
       2020-08-24 18:28:43 +08:00   ❤️ 1
    散了吧,V 站人均水平吊打你,还敢发公众号
    Ritr
        29
    Ritr  
       2020-08-24 18:32:07 +08:00   ❤️ 1
    @Foxkeh 前端使用 BLOB,返回数据流可以生成 excel 文件,这样服务器就不用写硬盘操作了。
    steptodream
        30
    steptodream  
       2020-08-24 18:36:13 +08:00
    真看不出来有什么特别的地方 标题党
    sadfQED2
        31
    sadfQED2  
       2020-08-24 18:40:07 +08:00 via Android
    就这???
    JJstyle
        32
    JJstyle  
       2020-08-24 19:16:36 +08:00 via iPhone
    一行行的写文件,真奢侈
    yanzhiling2001
        33
    yanzhiling2001  
       2020-08-24 19:34:45 +08:00
    这么长篇累牍的,直接拉到最下面,果然是营销号(ˉ▽ ̄~) 切~~
    Donss
        34
    Donss  
       2020-08-24 19:46:09 +08:00 via Android
    看到二维码就懂了
    talen666
        35
    talen666  
       2020-08-24 19:53:26 +08:00
    真无聊。。
    Erroad
        36
    Erroad  
       2020-08-24 20:03:02 +08:00
    就这
    securityCoding
        37
    securityCoding  
       2020-08-24 20:09:36 +08:00
    @JJstyle 哈哈 ,但凡是个正常的开发都会选择批量写入
    moonsn
        38
    moonsn  
       2020-08-24 20:58:29 +08:00 via Android
    这么慢吗?
    cigarzh
        39
    cigarzh  
       2020-08-24 22:23:46 +08:00
    看到标题,直接拉到最后,果然不出所料
    SbloodyS
        40
    SbloodyS  
       2020-08-24 23:00:19 +08:00
    20W 要 4 秒这么慢...
    xcstream
        41
    xcstream  
       2020-08-25 00:48:06 +08:00
    20w 条数据 还以为几亿呢
    chihiro2014
        42
    chihiro2014  
       2020-08-25 02:04:34 +08:00
    啊这。。。没有什么大改变。。。你还不如多线程走起来。或者一次性限定返回条目
    littlewing
        43
    littlewing  
       2020-08-25 03:29:45 +08:00 via iPhone
    mysqldump 一条命令的事,为啥要整这么复杂
    littlewing
        44
    littlewing  
       2020-08-25 03:30:35 +08:00 via iPhone   ❤️ 1
    原来是营销号,浪费时间,b 了
    zjttfs
        45
    zjttfs  
       2020-08-25 09:22:27 +08:00
    浪费时间
    yolee599
        46
    yolee599  
       2020-08-25 09:22:55 +08:00
    直接拉到最后,果然公众号,给爷爬
    sarices
        47
    sarices  
       2020-08-25 09:33:26 +08:00
    还要用的人学会怎么到如 excel,也是搞笑
    zarte
        48
    zarte  
       2020-08-25 09:35:16 +08:00
    能导成 xlsx 的再来吧。
    Vegetable
        49
    Vegetable  
       2020-08-25 09:38:38 +08:00
    。。。。
    AlkTTT
        50
    AlkTTT  
       2020-08-25 09:42:03 +08:00
    不应该发在推广里吗?
    Felldeadbird
        51
    Felldeadbird  
       2020-08-25 09:49:43 +08:00
    要导入 EXCEL 就是个麻烦事了。
    既然都导出 CSV 了,为什么不直接整合成 html table 形式? 既保留了精度,又不需要二次导入。
    encro
        52
    encro  
       2020-08-25 09:55:09 +08:00
    既然都能导出数据了,直接开个 sql 账号,自己用 navicat 导去吧。。
    zjx1993
        53
    zjx1993  
       2020-08-25 10:02:19 +08:00
    能分享就挺好的,有更好的方法就说出来,嘲讽或者贬低真的没必要了。人家最起码给出了一个解决方案。
    dongisking
        54
    dongisking  
       2020-08-25 10:22:56 +08:00
    标题党
    ragnaroks
        55
    ragnaroks  
       2020-08-25 10:35:07 +08:00
    asp.net core + websocket 直接流式导出 xlsx,不考虑传输时间(内网测试),超过 2w 但不到 4w 条数据,瞬间
    tankren
        56
    tankren  
       2020-08-25 10:50:44 +08:00
    话说做报表的哪有从单表拿数据的 都是 N 张表关联的 而且现在是 powerbi 的时代
    JokeEnd
        57
    JokeEnd  
       2020-08-25 11:00:20 +08:00
    我还以为穿越来到 5 年前的简书年代了
    ytll21
        58
    ytll21  
       2020-08-25 11:37:13 +08:00
    写的挺好的,楼上一群嘲讽的人也是没看懂。乐于分享是好事,无论水平高低。何况在我看来,楼主水平还是可以的,继续加油。
    ohao
        59
    ohao  
       2020-08-25 11:37:57 +08:00 via iPhone
    直接拉到最后看到二维码我就放心了.......
    duanxianze
        60
    duanxianze  
       2020-08-25 13:04:29 +08:00
    还以为多牛的技术呢+1 没嘲讽的意思 愿意分享也不错了 就是标题党不可取
    lurenC
        61
    lurenC  
       2020-08-25 13:14:58 +08:00   ❤️ 2
    乐于分享是好事,如果能指正优化也是好事。何必冷嘲热讽,搞得像个舌战群儒的场景,十分做作。
    Zheming
        62
    Zheming  
       2020-08-25 13:19:54 +08:00 via iPhone
    在这个圈子,分享点什么都会被挑刺。希望楼主不要在意。难道天天挂在嘴边的开源精神,不是别人产出了内容分享出来应当支持,而是看到哪里不好先喷一遍?那以后也都别开源了,开源还被人挑刺儿
    xuewuchen
        63
    xuewuchen  
       2020-08-25 13:33:44 +08:00
    假设我们不利用数据库本身的功能和命令,想单纯在可视数据上实现除了分页+多线程+数据拼接或者文件流写入之类的,不知道楼上的高手牛人们所谓的还有更快的代码实现方法是什么?
    反正我水平是真心没高到那种程度,上次自己写了个几万条可视记录一次性生成 EXCEL 都高兴的很。。
    V 站 NB 的人多,装 B 的人也不少。。啥也不是!
    symons
        64
    symons  
       2020-08-25 14:18:04 +08:00
    这是啥。。。标题党么
    Salvation
        65
    Salvation  
       2020-08-25 14:19:13 +08:00
    假设你说的全部都是真的,槽点太多,营销号啊?那没事了。

    核心就一个看法:哪怕是一个正常有一年经验的工程师,压根都不会遇到你这个问题。
    stevenkang
        66
    stevenkang  
       2020-08-25 16:25:31 +08:00
    这种帖子直接拉到底部看评论,主题内容都不用看的
    hpeng
        67
    hpeng  
       2020-08-25 17:18:58 +08:00 via iPhone
    很早以前的做法是异步任务,磁盘分段合并,对象存储中转。你这还不如我自己弄的
    siweipancc
        68
    siweipancc  
       2020-08-26 09:02:41 +08:00 via iPhone
    :) 内容都不用看,直接跳评论,说的就是你这种推广帖
    yamasa
        69
    yamasa  
       2020-08-26 13:11:33 +08:00
    NM 的什么破玩意啊,毛技术含量没有。有没有老哥知道怎么 @v2 的管理员,直接把这公号狗 ban 了?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5311 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 47ms · UTC 08:16 · PVG 16:16 · LAX 01:16 · JFK 04:16
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.