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

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

缓存数据，以空间换时间

逐行查询数据库校验的时间成本主要在来回的网络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;

}

}

MapResu

必看视频！获取2024年最新Java开发全套学习资料 备注Java

ltHandler 处理程序，将结果集放入 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

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

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

使用 values 批量插入

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

insert into table(colom1, colom2)

values

( #{item.colom1}, #{item.colom2})

使用 EasyPOI 读写 Excel

EasyPOI 采用基于注解的导入导出,修改注解就可以修改Excel，非常方便，代码维护起来也容易。

第三版：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 读取。

第四版：优化数据插入速度

在第二版插入的时候，我使用了 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。

并行插入工具类

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

/**

• 功能：利用并行流快速插入数据

• @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 插入的数据类型

*/

public static void insertData(List list, Consumer<List> consumer) {

if (list == null || list.size() < 1) {

return;

}

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

for (int i = 0; i < list.size(); i += SIZE) {

int j = Math.min((i + SIZE), list.size());

List subList = list.subList(i, j);

streamList.add(subList);

}

// 并行流使用的并发数是 CPU 核心数，不能局部更改。全局更改影响较大，斟酌

streamList.parallelStream().forEach(consumer);

}

}

最后

针对最近很多人都在面试，我这边也整理了相当多的面试专题资料，也有其他大厂的面经。希望可以帮助到大家。

最新整理面试题

上述的面试题答案都整理成文档笔记。也还整理了一些面试资料&最新2021收集的一些大厂的面试真题

最新整理电子书

最新整理大厂面试文档

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。
rEach(consumer);

}

}

最后

针对最近很多人都在面试，我这边也整理了相当多的面试专题资料，也有其他大厂的面经。希望可以帮助到大家。

最新整理面试题
[外链图片转存中…(img-BwrmpHL5-1716375118485)]

上述的面试题答案都整理成文档笔记。也还整理了一些面试资料&最新2021收集的一些大厂的面试真题

最新整理电子书

[外链图片转存中…(img-qojR2kUo-1716375118485)]

最新整理大厂面试文档

[外链图片转存中…(img-fjN0kI2T-1716375118486)]

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。