当前位置： mysql将数导入数据库-access数据导入mysql
10w Excel导入行级数据优化记录要求
项目中有个Excel导入需求：付款记录导入
实施/用户将其他系统的数据填写到我系统的Excel模板中，应用程序读取、校对、转换文件内容，生成欠费数据、账单、账单明细存入数据库。
在我接手之前mysql将数导入数据库，可能是之前导入的数据量不是太高，追求效率。 但是到了4.0版本，我估计导入的时候Excel行数是10w+，插入数据库的数据量大于3n，也就是说10w行的Excel，至少30w数据行将被插入到数据库中。 因此，对原有导入代码进行优化势在必行。 我对导入的代码逐步分析优化，百秒内完成（最终性能瓶颈在于数据库的处理速度，测试服务器4g内存不仅放了数据库，还放了很多微服务应用.处理能力不好）。 具体过程如下，每一步都列出了影响性能的问题和解决方法。
导入Excel的需求在系统中还是很常见的。 我的优化方法可能不是最优的。 欢迎读者在评论区留言，提供更好的想法
第一版迭代记录的一些细节：POI+逐行查询校对+逐行插入
这个版本是最老的版本，使用原生POImysql将数导入数据库，手动将Excel中的行映射到ArrayList对象，然后存储到List中，代码执行步骤如下：
1、手动将Excel读入List
2.循环遍历，在循环中执行以下步骤
3. 返回执行结果，如果有错误/验证失败。则返回提示信息并回滚数据
很明显，这种实现方式肯定是赶出来的，后续可能用的少，也不会注意到性能问题，但是最适合个位数/十位数的数据。 存在以下明显问题：
第二版：EasyPOI+缓存数据库查询操作+批量插入
针对第一版分析的三个问题，分别采用以下三种方法进行优化
缓存数据，用空间换取时间
逐行查询数据库验证的时间成本主要在往返网络IO上，优化方式也很简单。 将所有参与校验的数据缓存到HashMap中。 直接去HashMap打。
例如：验证行中的房子是否存在，本来需要用地区+楼+单元+房号查询房子表匹配房子ID。 如果找到，则验证通过。 如果通过，则向用户返回一条错误消息。 欠款导入时不会更新房源信息，而且一个小区的房源不多（5000以内）所以我用一个SQL把小区所有的房源存储在HashMap中，面积/楼房/单元/房间号作为键，房屋 ID 作为值。 后续检查只需要命中HashMap
自定义会话映射器
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 getHouseMapByAreaId(Long areaId) {
MapResultHandler handler = new MapResultHandler();
this.getSqlSession().select(BaseUnitMapper.class.getName()+".getHouseMapByAreaId", areaId, handler);
Map map = handler.getMappedResults();
return map;
}
}
将结果集放入 HashMap 的 MapResultHandler 处理程序
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
@Repository
public interface BaseUnitMapper {
// 收费标准绑定 区域楼宇单元房号 - 房屋ID
Map getHouseMapByAreaId(@Param("areaId") Long areaId);
}
示例 Mapper.xml
然后在代码中调用SessionMapper类的对应方法。
使用值批量插入
MySQL insert语句支持使用values(),(),()一次插入多行数据。 通过mybatis foreach结合java集合可以实现批量插入。 代码如下：
insert into table(colom1, colom2)
values
( #{item.colom1}, #{item.colom2})
使用EasyPOI读写Excel
EasyPOI采用基于注解的导入导出，Excel可以通过修改注解进行修改，非常方便，代码也易于维护。
第三版：EasyExcel+缓存数据库查询操作+批量插入
第二个版本采用EasyPOI后，可以轻松导入几千或几万的Excel数据，但需要一点时间（10分钟左右5W数据写入数据库），但由于后续的导入操作基本都在开发the side 一边导入一边看log，没有进一步优化。 但好景不长。 需要搬入一个新小区，账单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个insert。
每次插入1000条后，为了耗尽数据库的CPU，需要利用网络IO的等待时间。 这就需要通过多线程来解决，最简单的多线程可以使用并行流来实现。 然后我将使用代码Parallel stream来测试它：
10w行excel，42w欠单，42w条记录明细，2w条记录，16个线程并行插入数据库，每次1000行。 插入时间为72s，总导入时间为95s。
平行插入实用程序
对于并行插入的代码，我封装了一个函数式编程工具类，提供给大家
/**
* 功能：利用并行流快速插入数据
*
* @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> consumer) {
if (list == null || list.size() < 1) {
return;
}
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);
}
}
这里用到了Java8的大部分API，不知道的朋友可以看我之前的一篇关于Java的博客。方法简单易用
InsertConsumer.insertData(feeList, arrearageMapper::insertList);
其他影响性能的内容日志
避免在循环中打印太多信息登录
在优化过程中，我还发现了一个特别影响性能的东西：info日志，依然使用41w行25列45.5m的数据，数据读取开始到结束之间每隔1000行打印一个info日志，缓存验证数据- 验证后每行打印3+条信息日志，日志框架使用Slf4j。 打印并持久化到磁盘。下面是打印日志和不打印日志的效率区别
打印日志
不打印日志
我以为我选错了Excel文件，又选了一遍，结果还是一样
缓存验证数据——验证后不打印日志耗时仅为打印日志耗时的1/10！
总结
上一篇 
