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

javaPOI导入导出excel: 文章链接

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


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


缓存数据，以空间换时间

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

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


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

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

总结

提升Excel导入速度的方法：

  • 使用更快的 Excel 读取框架(推荐使用阿里 EasyExcel)
  • 对于需要与数据库交互的校验、按照业务逻辑适当的使用缓存。用空间换时间
  • 使用 values(),(),() 拼接长 SQL 一次插入多行数据(1000 行)
  • 使用多线程插入数据，利用掉网络IO等待时间(推荐使用并行流，简单易用)
  • 避免在循环中打印无用的日志