百万级别数据的Excel如何快速导入到数据库中

一、问题解析

35.1 需求说明

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

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

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

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

一些细节

1.数据导入:导入使用的模板由系统提供,格式是 xlsx (支持 65535+行数据) ,用户按照表头在对应列写入相应的数据

2.数据校验:数据校验有两种:

2.1 字段长度、字段正则表达式校验等,内存内校验不存在外部数据交互。对性能影响较小

2.2 数据重复性校验,如票据号是否和系统已存在的票据号重复(需要查询数据库,十分影响性能)

3.数据插入:测试环境数据库使用 MySQL 5.7,未分库分表,连接池使用 Druid

迭代记录

35.2 A. 第一版:POI + 逐行查询校对 + 逐行插入

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

1.手动读取 Excel 成 List

2.循环遍历,在循环中进行以下步骤

.检验字段长度

.一些查询数据库的校验,比如校验当前行欠费对应的房屋是否在系统中存在,需要查询房屋表

3.写入当前行数据

4.返回执行结果,如果出错 / 校验不合格。则返回提示信息并回滚数据

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

1.查询数据库的校验对每一行数据都要查询一次数据库,应用访问数据库来回的网络IO次数被放大了 n 倍,时间也就放大了 n 倍

2.写入数据也是逐行写入的,问题和上面的一样

3.数据读取使用原生 POI,代码十分冗余,可维护性差。

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

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

1.缓存数据,以空间换时间

逐行查询数据库校验的时间成本主要在来回的网络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);
    }
    // 堆代码 duidaima.com
    // 区域楼宇单元房号 - 房屋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 类对应的方法即可。

2.使用 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>

35.4 C. 第三版:优化数据插入速度

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

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

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

并行插入工具类

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

/**
 * 功能:利用并行流快速插入数据
 */
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);

35.5 其他影响性能的内容

日志

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

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

打印日志

不打印日志

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

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

35.6 总结

提升Excel导入速度的方法:

1.使用更快的 Excel 读取框架(推荐使用阿里 EasyExcel)

2.对于需要与数据库交互的校验、按照业务逻辑适当的使用缓存。用空间换时间

3.使用 values(),(),() 拼接长 SQL 一次插入多行数据

4.使用多线程插入数据,利用掉网络IO等待时间(推荐使用并行流,简单易用)

5.避免在循环中打印无用的日

二、粉丝福利 

  • 我根据我从小白到架构师多年的学习经验整理出来了一份50W字面试解析文档、简历模板、学习路线图、java必看学习书籍 、 需要的小伙伴斯我一下,或者评论区扣“求分享
SSM(Spring+SpringMVC+MyBatis)是一个基于Spring、Spring MVC和MyBatis的企业级轻量级架构,用于简化Java Web应用的开发。如果你需要处理百万级别Excel文件导入数据库,可以按照以下步骤操作: 1. **读取Excel文件**:可以使用Apache POI库来读取Excel文件。首先,创建一个服务或者工具类,通过`InputStream`流解析Excel内容。 ```java import org.apache.poi.ss.usermodel.*; ReaderFactory readerFactory = ReaderFactory.create((FileInputStream) file); HSSFWorkbook workbook = readerFactory.createWorkbook(); Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表 ``` 2. **数据转换**:遍历每个单元格,将Excel数据转换成适合数据库存储的对象实例。例如,如果字段类型映射正确,可以直接使用反射或手动编码。 3. **批量插入**:为了提高性能,避免频繁的数据库操作,可以将数据分批处理,比如每几百行或者几千行插入一次。使用MyBatis的`BatchInsert`或者自定义`Mapper`接口配合`List`参数的SQL语句进行插入。 4. **错误处理**:处理可能出现的异常,如IO异常、数据校验错误等,并记录日志。 5. **事务管理**:考虑到百万级的数据处理,可能涉及到大量的并发操作和数据一致性,所以要确保在适当的地方开启事务并进行回滚。 6. **进度监控**:如果是大文件,提供进度条或日志输出,让用户了解导入进度。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值