100000 行级别数据的 Excel 导入优化之路

如何处理多个一百万行excel表的数据分析 在数据驱动的时代,处理大规模数据集的能力成为了企业和个人竞争力的重要组成部分。尤其是在日常工作中,我们经常需要处理包含数百万行数据的Excel文件。然而,当面临这样的大数据量时,传统的Excel工具往往显得力不从心,不仅处理速度慢,还容易崩溃。那么,如何高效地处理多个百万行的Excel表呢?本文将为你提供一些实用的方法和技巧。 阅读详情

Part1需求说明

项目中有一个 Excel 导入的需求:缴费记录导入

由实施 / 用户 将别的系统的数据填入我们系统中的 Excel 模板,应用将文件内容读取、校对、转换之后产生欠费数据、票据、票据详情并存储到数据库中。

在我接手之前可能由于之前导入的数据量并不多没有对效率有过高的追求。但是到了 4.0 版本,我预估导入时Excel 行数会是 10w+ 级别,而往数据库插入的数据量是大于 3n 的,也就是说 10w 行的 Excel,则至少向数据库插入 30w 行数据。因此优化原来的导入代码是势在必行的。我逐步分析和优化了导入的代码,使之在百秒内完成(最终性能瓶颈在数据库的处理速度上,测试服务器 4g 内存不仅放了数据库,还放了很多微服务应用。处理能力不太行)。具体的过程如下,每一步都有列出影响性能的问题和解决的办法。

导入 Excel 的需求在系统中还是很常见的,我的优化办法可能不是最优的,欢迎读者在评论区留言交流提供更优的思路

Part2一些细节

  • 数据导入:导入使用的模板由系统提供,格式是 xlsx (支持 65535+行数据) ,用户按照表头在对应列写入相应的数据
  • 数据校验:数据校验有两种:
    • 字段长度、字段正则表达式校验等,内存内校验不存在外部数据交互。对性能影响较小
    • 数据重复性校验,如票据号是否和系统已存在的票据号重复(需要查询数据库,十分影响性能)
  • 数据插入:测试环境数据库使用 MySQL 5.7,未分库分表,连接池使用 Druid

Part3迭代记录

1第一版:POI + 逐行查询校对 + 逐行插入

这个版本是最古老的版本,采用原生 POI,手动将 Excel 中的行映射成 ArrayList 对象,然后存储到 List,代码执行的步骤如下:

  1. 手动读取 Excel 成 List
  2. 循环遍历,在循环中进行以下步骤
    1. 检验字段长度
    2. 一些查询数据库的校验,比如校验当前行欠费对应的房屋是否在系统中存在,需要查询房屋表
    3. 写入当前行数据
  3. 返回执行结果,如果出错 / 校验不合格。则返回提示信息并回滚数据

显而易见的,这样实现一定是赶工赶出来的,后续可能用的少也没有察觉到性能问题,但是它最多适用于个位数/十位数级别的数据。存在以下明显的问题:

  • 查询数据库的校验对每一行数据都要查询一次数据库,应用访问数据库来回的网络IO次数被放大了 n 倍,时间也就放大了 n 倍
  • 写入数据也是逐行写入的,问题和上面的一样
  • 数据读取使用原生 POI,代码十分冗余,可维护性差。

2第二版:EasyPOI + 缓存数据库查询操作 + 批量插入

针对第一版分析的三个问题,分别采用以下三个方法优化

缓存数据,以空间换时间

逐行查询数据库校验的时间成本主要在来回的网络IO中,优化方法也很简单。将参加校验的数据全部缓存到 HashMap 中。直接到 HashMap 去命中。

例如:校验行中的房屋是否存在,原本是要用 区域 + 楼宇 + 单元 + 房号 去查询房屋表匹配房屋ID,查到则校验通过,生成的欠单中存储房屋ID,校验不通过则返回错误信息给用户。而房屋信息在导入欠费的时候是不会更新的。并且一个小区的房屋信息也不会很多(5000以内)因此我采用一条SQL,将该小区下所有的房屋以 区域/楼宇/单元/房号 作为 key,以 房屋ID 作为 value,存储到 HashMap 中,后续校验只需要在 HashMap 中命中

自定义 SessionMapper

Mybatis 原生是不支持将查询到的结果直接写人一个 HashMap 中的,需要自定义 SessionMapper

SessionMapper 中指定使用 MapResultHandler 处理 SQL 查询的结果集

@Repository
public class SessionMapper extends SqlSessionDaoSupport {

    @Resource
    public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
        super.setSqlSessionFactory(sqlSessionFactory);
    }

    // 区域楼宇单元房号 - 房屋ID
    @SuppressWarnings("unchecked")
    public Map<String, Long> getHouseMapByAreaId(Long areaId) {
        MapResultHandler handler = new MapResultHandler();

 this.getSqlSession().select(BaseUnitMapper.class.getName()+".getHouseMapByAreaId", areaId, handler);
        Map<String, Long> map = handler.getMappedResults();
        return map;
    }
}

MapResultHandler 处理程序,将结果集放入 HashMap

public class MapResultHandler implements ResultHandler {
    private final Map mappedResults = new HashMap();

    @Override
    public void handleResult(ResultContext context) {
        @SuppressWarnings("rawtypes")
        Map map = (Map)context.getResultObject();
        mappedResults.put(map.get("key"), map.get("value"));
    }

    public Map getMappedResults() {
        return mappedResults;
    }
}

示例 Mapper

@Mapper
@Repository 
public interface BaseUnitMapper {
    // 收费标准绑定 区域楼宇单元房号 - 房屋ID
    Map<String, Long> getHouseMapByAreaId(@Param("areaId") Long areaId);
}

示例 Mapper.xml

<select id="getHouseMapByAreaId" resultMap="mapResultLong">
    SELECT
        CONCAT( h.bulid_area_name, h.build_name, h.unit_name, h.house_num ) k,
        h.house_id v
    FROM
        base_house h
    WHERE
        h.area_id = #{areaId}
    GROUP BY
        h.house_id
</select>
            
<resultMap id="mapResultLong" type="java.util.HashMap">
    <result property="key" column="k" javaType="string" jdbcType="VARCHAR"/>
    <result property="value" column="v" javaType="long" jdbcType="INTEGER"/>
</resultMap>

之后在代码中调用 SessionMapper 类对应的方法即可。

Part4使用 values 批量插入

MySQL insert 语句支持使用 values (),(),() 的方式一次插入多行数据,通过 mybatis foreach 结合 java 集合可以实现批量插入,代码写法如下:

<insert id="insertList">
    insert into table(colom1, colom2)
    values
    <foreach collection="list" item="item" index="index" separator=",">
     ( #{item.colom1}, #{item.colom2})
    </foreach>
</insert>

Part5使用 EasyPOI 读写 Excel

http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8 采用基于注解的导入导出,修改注解就可以修改Excel,非常方便,代码维护起来也容易。

3第三版:EasyExcel + 缓存数据库查询操作 + 批量插入

第二版采用 EasyPOI 之后,对于几千、几万的 Excel 数据已经可以轻松导入了,不过耗时有点久(5W 数据 10分钟左右写入到数据库)不过由于后来导入的操作基本都是开发在一边看日志一边导入,也就没有进一步优化。但是好景不长,有新小区需要迁入,票据 Excel 有 41w 行,这个时候使用 EasyPOI 在开发环境跑直接就 OOM 了,增大 JVM 内存参数之后,虽然不 OOM 了,但是 CPU 占用 100% 20 分钟仍然未能成功读取全部数据。故在读取大 Excel 时需要再优化速度。莫非要我这个渣渣去深入 POI 优化了吗?别慌,先上 GITHUB 找找别的开源项目。这时阿里 EasyExcel 映入眼帘:

图片

emmm,这不是为我量身定制的吗!赶紧拿来试试。EasyExcel 采用和 EasyPOI 类似的注解方式读写 Excel,因此从 EasyPOI 切换过来很方便,分分钟就搞定了。也确实如阿里大神描述的:41w行、25列、45.5m 数据读取平均耗时 50s,因此对于大 Excel 建议使用 EasyExcel 读取。

4第四版:优化数据插入速度

在第二版插入的时候,我使用了 values 批量插入代替逐行插入。每 30000 行拼接一个长 SQL、顺序插入。整个导入方法这块耗时最多,非常拉跨。后来我将每次拼接的行数减少到 10000、5000、3000、1000、500 发现执行最快的是 1000。结合网上一些对 innodb_buffer_pool_size 描述我猜是因为过长的 SQL 在写操作的时候由于超过内存阈值,发生了磁盘交换。限制了速度,另外测试服务器的数据库性能也不怎么样,过多的插入他也处理不过来。所以最终采用每次 1000 条插入。

每次 1000 条插入后,为了榨干数据库的 CPU,那么网络IO的等待时间就需要利用起来,这个需要多线程来解决,而最简单的多线程可以使用 并行流 来实现,接着我将代码用并行流来测试了一下:

10w行的 excel、42w 欠单、42w记录详情、2w记录、16 线程并行插入数据库、每次 1000 行。插入时间 72s,导入总时间 95 s。

图片

Part6并行插入工具类

并行插入的代码我封装了一个函数式编程的工具类,也提供给大家

/**
 * 功能:利用并行流快速插入数据
 *
 * @author Keats
 * @date 2020/7/1 9:25
 */
public class InsertConsumer {
    /**
     * 每个长 SQL 插入的行数,可以根据数据库性能调整
     */
    private final static int SIZE = 1000;

    /**
     * 如果需要调整并发数目,修改下面方法的第二个参数即可
     */
    static {
        System.setProperty("java.util.concurrent.ForkJoinPool.common.parallelism", "4");
    }

    /**
     * 插入方法
     *
     * @param list     插入数据集合
     * @param consumer 消费型方法,直接使用 mapper::method 方法引用的方式
     * @param <T>      插入的数据类型
     */
    public static <T> void insertData(List<T> list, Consumer<List<T>> consumer) {
        if (list == null || list.size() < 1) {
            return;
        }

        List<List<T>> streamList = new ArrayList<>();

        for (int i = 0; i < list.size(); i += SIZE) {
            int j = Math.min((i + SIZE), list.size());
            List<T> subList = list.subList(i, j);
            streamList.add(subList);
        }
        // 并行流使用的并发数是 CPU 核心数,不能局部更改。全局更改影响较大,斟酌
        streamList.parallelStream().forEach(consumer);
    }
}

这里多数使用到很多 Java8 的API,不了解的朋友可以翻看我之前关于 Java 的博客。方法使用起来很简单

InsertConsumer.insertData(feeList, arrearageMapper::insertList);

Part7其他影响性能的内容

日志

避免在 for 循环中打印过多的 info 日志

在优化的过程中,我还发现了一个特别影响性能的东西:info 日志,还是使用 41w行、25列、45.5m 数据,在 开始-数据读取完毕 之间每 1000 行打印一条 info 日志,缓存校验数据-校验完毕 之间每行打印 3+ 条 info 日志,日志框架使用 Slf4j 。打印并持久化到磁盘。下面是打印日志和不打印日志效率的差别

打印日志

图片

不打印日志

图片

我以为是我选错 Excel 文件了,又重新选了一次,结果依旧

图片

缓存校验数据-校验完毕 不打印日志耗时仅仅是打印日志耗时的 1/10 !

Part9总结

提升Excel导入速度的方法:

  • 使用更快的 Excel 读取框架(推荐使用阿里 EasyExcel)
  • 对于需要与数据库交互的校验、按照业务逻辑适当的使用缓存。用空间换时间
  • 使用 values(),(),() 拼接长 SQL 一次插入多行数据
  • 使用多线程插入数据,利用掉网络IO等待时间(推荐使用并行流,简单易用)
  • 避免在循环中打印无用的日志
来源:https://mp.weixin.qq.com/s/oViATLeQ71qOx39QJ94hVg
百万数量级别导入导出,看我这样做 百万数据处理 阅读详情

相关推荐

Python 静态导入与动态导入

python中模块的静态导入 与 动态导入(运行时指定模块),以及具体对应示例。

慢也好,步子小也好,往前走就好~ 719

记录一次从15w条数据的excel导入到数据库优化过程(22秒到3秒)

记录一次15w数据的excel导入到数据库优化过程(22秒到3秒)

你若为我繁华,我愿成你烟花 7818

DRAM知识整理系列(三):部分时序参数整理

一、tCL-tRCD-tRP-tRAS(第一时序) 内存是根据行和列寻址的,当请求触发后,最初是tRAS(Active to precharge Delay),预充电后,内存才真正开始初始化RAS(Row Address Strobe)。一旦tRAS激活后,RAS开始进行需要数据的寻址。首先是行地址,然后初始化tRCD(RAS to CAS Delay),周期结束,接着通过CAS(Column Address Strobe)访问所需数据的十六进制地址。其中,从CAS开始到CAS结束,就是CAS延迟(即t

不吃鱼的猫的博客 7万+

easypoi实现导出数据、下载导入模板、导入数据

easypoi实现导入数据导出数据,下载导入模板

五宝茶四娃的博客 3773

星链V3的2048条波束与1Tbps:多波束相控阵的工程代价

低轨卫星通信的容量提升,核心在于多波束相控阵天线的空间复用能力。相控阵通过控制大量天线单元的相位,可在同一阵面同时形成多条独立指向的波束,从而在有限频谱内成倍提升系统容量。星链V3宣称的2048条波束与1 Tbps汇聚吞吐量,正是基于这一原理:更多波束意味着更高的频率复用系数和更灵活的资源调度。但工程实现并非免费午餐,数字背后是供电、散热、重量、发射成本及运维复杂度的急剧攀升。从用户侧看,更精细的波束也带来切换频繁、低仰角衰减等新挑战。本文从多波束相控阵的基础原理出发,拆解波束形成、频谱复用和动态调度的工程

weixin_33462167的博客 352

RAID 0/1/5/10 性能实测:4块 SATA SSD 组阵列,顺序读写速度对比

本文通过实测对比了RAID 0/1/5/10在4块SATA SSD上的顺序读写性能,详细展示了不同RAID级别的配置方法、理论性能与实际测试数据。测试结果显示,RAID 0在顺序读写速度上表现最佳,而RAID 10则在性能与数据安全之间取得了平衡,适合数据库服务器等关键应用场景。

weixin_33462167的博客 426

深度学习数据集——MNIST

简单介绍MNIST数据集

zchang81的博客 2万+

万字数据仓库面试题及参考答案

星型模型由一个中心的事实表和多个外围的维度表组成,事实表存储度量值和事实,维度表存储描述性属性和维度。星型模型和雪花模型的区别在于雪花模型将维度表进一步细化,形成多层次的结构,而星型模型由一个中心事实表和多个维度表组成,适用于查询较为简单的情况。维度建模通常采用星型模型或雪花模型,将数据分为事实表和维度表,其中事实表存储度量值和事实,维度表存储描述事实的属性。三者之间的关系可以这样理解:数据仓库主要关注历史数据的存储和分析,数据中台则关注数据的服务化和资产化,而数据湖则关注原始数据的存储和多样性。

大模型大数据攻城狮的专栏 560

Excel导入导出百万级数据

Excel导入导出百万数据方案

Jeck_wu的博客 2190

杰理 JL701N/JL703N USB MIDI 使用教程

摘要:本教程介绍如何在杰理JL701N/JL703N音箱的可视化SDK中配置USB MIDI功能。主要步骤包括:1)启用PC模式;2)修改SDK宏定义启用USB MIDI;3)实现MIDI数据接收处理逻辑;4)调用接口发送MIDI数据;5)使用MIDI-OX测试功能。针对低版本SDK可能出现的设备识别问题,提供了在midi.c文件中添加特定代码行的解决方案。使用前需确保SDK版本达标,并注意备份原始文件。

qq_42014563的博客 1269

orange软件:入门使用

orange做数据分析契机实验手册目录orange软件部分界面展示小试牛刀:数据导入和展示小试牛刀:散点图绘制总结 契机 今天在上大数据时代实验课,作为一名软件工程专业的大三学生,在上这节公选课之前就决定这节课做完这个学期所有的实验报告,可以说“有亿点”不屑;尤其当老师说Python很麻烦的时候,我更是震惊了,因为我接触过这么多编程语言,Python真的很友好了,很傻瓜,但是当老师打开实验手册的时候,我真香了!!!下面先来看看手册的目录: 实验手册目录 为什么会真香呢,因为目录里这些有好多机器学习算法!

weixin_45880844的博客 1万+

300万数据导入导出优化方案,从80s优化到8s(实测)

前景在项目开发中往往需要使用到数据的导入和导出,导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。写本文的背景是因为在工作中遇到了大数据的导入和导出,问题既然来了逃跑不如干掉它!!!只要这一次解决了,后期遇到同样的问题就好解决了。废话不多说,开始撸起来!!!1 传统POI的...

程序员小明1024 3458

引文:当SVM转角遇上Copy-on-Write (COW)

本文分析了Linux内核中fork系统调用的Copy-on-Write(COW)机制实现原理。重点阐述了fork时父子进程共享物理页面、标记页表项为只读并在写操作时触发COW的流程,以及在SVM环境中通过MMU Notifier机制保持CPU和GPU页表同步的解决方案。文章详细描述了COW的三个阶段:初始共享、写入触发和页面复制,并说明了MMU Notifier的回调注册和处理流程,确保异构计算环境下内存一致性。

图形/计算GPU的驱动与虚拟化技术分享园地 2677

100000行级别数据的 Excel 导入优化之路

100000行级别数据的 Excel 导入优化之路

1794

jdk-8u144-linux-x64.tar.gz.

该资源为搭建Linux系统Java环境用,资源的后缀为tar.gz格式的压缩包

霍尼韦尔SoftMaster使用说明

霍尼韦尔SoftMaster使用说明

100000行级别数据的 Excel 导入优化之路

大家好,我是不才陈某~今天分享一篇大数据量Excel导入如何优化的文章,非常不错。需求说明项目中有一个 Excel 导入的需求:缴费记录导入由实施 / 用户 将别的系统的数据填入我们系统中的 Excel 模板,应用将文件内容读取、校对、转换之后产生欠费数据、票据、票据详情并存储到数据库中。在接手之前可能由于之前导入的数据量并不多没有对效率有过高的追求。但是到了 4.0 版本,预估导入时Excel ...

码猿技术专栏 1339

jre9下载 jdk9

(Java SE Runtime Environment 9.0) 9.0u175 官方最新版 【x86|x64】

100000行级别数据的 Excel 导入优化

需求说明 项目中有一个 Excel 导入的需求:缴费记录导入由实施 / 用户 将别的系统的数据填入我们系统中的 Excel 模板,应用将文件内容读取、校对、转换之后产生欠费数据、票据、票据详情并存储到数据库中。 在接手之前可能由于之前导入的数据量并不多没有对效率有过高的追求。但是到了 4.0 版本,预估导入时Excel 行数会是 10w+ 级别,而往数据库插入的数据量是大于 3n 的,也就是说 10w 行的 Excel,则至少向数据库插入 30w 行数据。因此优化原来的导入代码是势在必行的。逐步分析和优

m0_53914150的博客 786

IEM模型,地表粗糙度模拟

利用微波反演土壤水分时候,利用IEM模型可以实现对随机地表的模拟

10w行级别数据的Excel导入优化记录

你知道的越多,不知道的就越多,业余的像一棵小草!你来,我们一起精进!你不来,我和你的竞争对手一起精进!编辑:业余草来源:cnblogs.com/keatsCoder/p/13217561...

xmt1139057136的专栏 302
上一篇: 大意了,一次MySQL分页导致的线上事故...
下一篇: 如何防止订单重复支付?
码趣资源网
博客等级 码龄4年 269粉丝 518原创
评论 2
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值