经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » MySQL » 查看文章
厉害了！12秒将百万数据通过EasyExcel导入MySQL数据库中
来源：cnblogs　　作者：JavaBuild　　时间：2024/5/13 8:53:47　　对本文有异议

一、写在开头

我们在上一篇文章中提到了通过EasyExcel处理Mysql百万数据的导入功能（一键看原文），当时我们经过测试数据的反复测验，100万条放在excel中的数据，4个字段的情况下，导入数据库，平均耗时500秒，这对于我们来说肯定难以接受，今天我们就来做一次性能优化。

image

二、性能瓶颈分析

一般的大数据量excel入库的场景中，耗时大概在如下几点里：

  • 耗时1： 百万数据读取，字段数量，sheet页个数，文件体积；针对这种情况，我们要选择分片读取，选择合适的集合存储。
  • 耗时2： 百万数据的校验，逐行分字段校验；这种情况的耗时会随着字段个数逐渐增加，目前我们的案例中不设计，暂不展开。
  • 耗时3： 百万数据的写入；选择合适的写入方式，如Mybatis-plus的分批插入，采用多线程处理等。

三、针对耗时1进行优化

耗时2的场景我们在案例中并未用到，耗时1中针对百万级数据的读取，我们必然要选择分片读取，分片处理，这在我们上一篇文章中就已经采用了该方案，这里通过实现EasyExcel的ReadListener页面读取监听器，实现其invoke方法，在方法中我们增加BATCH_COUNT（单次读取条数）配置，来进行分片读取。读取完后，我们一定要选择合适的集合容器存放临时数据，不同集合之间的增加数据性能存在差异这里我们选择ArrayList。

【优化前代码片段】

  1. @Slf4j
  2. @Service
  3. public class EasyExcelImportHandler implements ReadListener<User> {
  4.     /*成功数据*/
  5.     private final CopyOnWriteArrayList<User> successList = new CopyOnWriteArrayList<>();
  6.     /*单次处理条数*/
  7.     private final static int BATCH_COUNT = 20000;
  8.     @Resource
  9.     private ThreadPoolExecutor threadPoolExecutor;
  10.     @Resource
  11.     private UserMapper userMapper;
  15.     @Override
  16.     public void invoke(User user, AnalysisContext analysisContext) {
  17.         if(StringUtils.isNotBlank(user.getName())){
  18.             successList.add(user);
  19.             return;
  20.         }
  21.         if(successList.size() >= BATCH_COUNT){
  22.             log.info("读取数据：{}", successList.size());
  23.             saveData();
  24.         }
  25.     }
  26.     ///
  27.     ///
  28. }

【优化后代码片段】

  1. @Slf4j
  2. @Service
  3. public class EasyExcelImportHandler implements ReadListener<User> {
  4.     /*成功数据*/
  5.    // private final CopyOnWriteArrayList<User> successList = new CopyOnWriteArrayList<>();
  6.     private final List<User> successList =  new ArrayList<>();
  7.     /*单次处理条数，有原来2万变为10万*/
  8.     private final static int BATCH_COUNT = 100000;
  9.     @Resource
  10.     private ThreadPoolExecutor threadPoolExecutor;
  11.     @Resource
  12.     private UserMapper userMapper;
  15.     @Override
  16.     public void invoke(User user, AnalysisContext analysisContext) {
  17.         if (StringUtils.isNotBlank(user.getName())) {
  18.             successList.add(user);
  19.             return;
  20.         }
  21.         //size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入
  22.         if (successList.size() >= BATCH_COUNT) {
  23.             log.info("读取数据：{}", successList.size());
  24.             saveData();
  25.             //清理集合便于GC回收
  26.             successList.clear();
  27.         }
  28.     }
  29.     ///
  30.     ///
  31.  }

这里面我们主要做了2点优化，1）将原来的线程安全的CopyOnWriteArrayList换为ArrayList，前者虽然可保线程安全，但存储数据性能很差；2）将原来单批次2000调整为100000，这个参数是因电脑而异的，并没有最佳数值。

【注】：本文中的代码仅针对优化点贴出，完整代码参考文首中的上一篇文章连接哈！

四、针对耗时3进行优化

针对耗时3的处理方案，我们这里准备了2个：JDBC分批插入+手动事务控制、多线程+Mybatis-Plus批量插入。

4.1 JDBC分批插入+手动事务控制

很多博文中都说mybatis批量插入性能低，有人建议使用原生的JDBC进行处理，那咱们就采用这种方案来测试一下。

首先我们既然要通过jdbc连接数据库进行操作，那就先准备一个连接工具类吧

  1. public class JdbcConnectUtil {
  3.     private static  String driver;
  4.     private static  String url;
  5.     private static  String name;
  6.     private static  String password;
  8.     /**
  9.      * 创建数据Properties集合对象加载加载配置文件
  10.      */
  11.     static {
  12.         Properties properties = new Properties();
  13.         try {
  14.             properties.load(JdbcConnectUtil.class.getClassLoader().getResourceAsStream("generator.properties"));
  15.             driver = properties.getProperty("jdbc.driverClass");
  16.             url = properties.getProperty("jdbc.connectionURL");
  17.             name = properties.getProperty("jdbc.userId");
  18.             password = properties.getProperty("jdbc.password");
  19.             Class.forName(driver);
  20.         } catch (IOException | ClassNotFoundException e) {
  21.             e.printStackTrace();
  22.         }
  23.     }
  25.     /**
  26.      * 获取数据库连接对象
  27.      * @return
  28.      * @throws Exception
  29.      */
  30.     public static Connection getConnect() throws Exception {
  31.         return DriverManager.getConnection(url, name, password);
  32.     }
  34.     /**
  35.      * 关闭数据库相关资源
  36.      * @param conn
  37.      * @param ps
  38.      * @param rs
  39.      */
  40.     public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
  41.         try {
  42.             if (conn != null) conn.close();
  43.             if (ps != null) ps.close();
  44.             if (rs != null) rs.close();
  45.         } catch (SQLException e) {
  46.             throw new RuntimeException(e);
  47.         }
  48.     }
  49.     public static void close(Connection conn, PreparedStatement ps) {
  50.         close(conn, ps, null);
  51.     }
  52.     public static void close(Connection conn, ResultSet rs) {
  53.         close(conn, null, rs);
  54.     }
  55. }

有了工具类后，我们就可以在EasyExcelImportHandler类中进行JDBC导入逻辑的实现啦。

  1.  /**
  2.      * jdbc+事务处理
  3.      */
  4.     public void import4Jdbc(){
  6.         //分批读取+JDBC分批插入+手动事务控制
  7.         Connection conn = null;
  8.         //JDBC存储过程
  9.         PreparedStatement ps = null;
  10.         try {
  11.             //建立jdbc数据库连接
  12.             conn = JdbcConnectUtil.getConnect();
  13.             //关闭事务默认提交
  14.             conn.setAutoCommit(false);
  15.             String sql = "insert into user (id,name, phone_num, address) values";
  16.             sql += "(?,?,?,?)";
  17.             ps = conn.prepareStatement(sql);
  18.             for (int i = 0; i < successList.size(); i++) {
  19.                 User user = new User();
  20.                 ps.setInt(1,successList.get(i).getId());
  21.                 ps.setString(2,successList.get(i).getName());
  22.                 ps.setString(3,successList.get(i).getPhoneNum());
  23.                 ps.setString(4,successList.get(i).getAddress());
  24.                 //将一组参数添加到此 PreparedStatement 对象的批处理命令中。
  25.                 ps.addBatch();
  26.             }
  27.             //执行批处理
  28.             ps.executeBatch();
  29.             //手动提交事务
  30.             conn.commit();
  31.         } catch (Exception e) {
  32.             e.printStackTrace();
  33.         } finally {
  34.         	//记得关闭连接
  35.             JdbcConnectUtil.close(conn,ps);
  36.         }
  37.     }

这里我们通过PreparedStatement的addBatch()和executeBatch()实现JDBC的分批插入，然后用import4Jdbc()替换原来的savaData()即可。

经过多次导入测试，这种方案的平均耗时为140秒。相比之前的500秒确实有了大幅度提升，但是2分多钟仍然感觉有点慢。

image

4.2 多线程+Mybatis-Plus批量插入

我们知道Mybatis-Plus的IService中提供了saveBatch的批量插入方法，但经过查看日志发现Mybatis-Plus的saveBatch在最后还是循环调用的INSERT INTO语句！

这种情况下，测试多线程速度和单线程相差不大，所以需要实现真正的批量插入语句，两种方式，一种是通过给Mybatis-Plus注入器，增强批量插入，一种是在xml文件中自己拼接SQL语句，我们在这里选用后一种，因为我们只做一个表，直接手写xml很方便，如果是在企业开发时建议使用sql注入器实现（自定义SQL注入器实现DefaultSqlInjector，添加InsertBatchSomeColumn方法，通过使用InsertBatchSomeColumn方法批量插入。）。

【XML中手动批量插入】

  1.  <insert id="insertSelective" parameterType="java.util.List">
  2.     insert into user
  3.     (id,name, phone_num, address
  4.       )
  5.     values
  6.     <foreach collection="list" item="item" separator=",">
  7.         (#{item.id},#{item.name},#{item.phoneNum},#{item.address})
  8.     </foreach>
  9.   </insert>

在在EasyExcelImportHandler类中的saveData()方法中实现多线程批量插入。

  1. /**
  2.      * 采用多线程读取数据
  3.      */
  4.     private void saveData() {
  5.         List<List<User>> lists = ListUtil.split(successList, 1000);
  6.         CountDownLatch countDownLatch = new CountDownLatch(lists.size());
  7.         for (List<User> list : lists) {
  8.             threadPoolExecutor.execute(() -> {
  9.                 try {
  10.                     userMapper.insertSelective(list.stream().map(o -> {
  11.                         User user = new User();
  12.                         user.setName(o.getName());
  13.                         user.setId(o.getId());
  14.                         user.setPhoneNum(o.getPhoneNum());
  15.                         user.setAddress(o.getAddress());
  16.                         return user;
  17.                     }).collect(Collectors.toList()));
  18.                 } catch (Exception e) {
  19.                     log.error("启动线程失败,e:{}", e.getMessage(), e);
  20.                 } finally {
  21.                     //执行完一个线程减1,直到执行完
  22.                     countDownLatch.countDown();
  23.                 }
  24.             });
  25.         }
  26.         // 等待所有线程执行完
  27.         try {
  28.             countDownLatch.await();
  29.         } catch (Exception e) {
  30.             log.error("等待所有线程执行完异常,e:{}", e.getMessage(), e);
  31.         }
  32.         // 提前将不再使用的集合清空，释放资源
  33.         successList.clear();
  34.         lists.clear();
  35.     }

经过多次导入测试，100万数据量导入耗时平均在20秒，这就是一个很客观且友好用户的导入功能啦，毕竟100万的xlsx文件，打开都需要七八秒呢！
image

五、总结

OK！以上就是SpringBoot项目下，通过阿里开源的EasyExcel技术进行百万级数据的导入功能的优化步骤啦，由原来的500秒优化到20秒！

六、结尾彩蛋

如果本篇博客对您有一定的帮助，大家记得留言+点赞+收藏呀。原创不易，转载请联系Build哥！

image

如果您想与Build哥的关系更近一步，还可以关注“JavaBuild888”，在这里除了看到《Java成长计划》系列博文，还有提升工作效率的小笔记、读书心得、大厂面经、人生感悟等等，欢迎您的加入！

image

原文链接：https://www.cnblogs.com/JavaBuild/p/18187977

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MySQL热门文章

  • 大项目之网上书城（九）——订单Demo
  • CentOS7下MySQL服务启动失败原因及解决方法
  • MySQL从删库到跑路_高级（六）——索引
  • 55 道MySQL基础题
  • MySQL与PHP的基础与应用专题之自连接
  • 树形结构的菜单表设计与查询
  • MYSQL语句
  • 《高性能MySQL（第3版）》PDF免费下载
  • 《MySQL必知必会》知识汇总二
  • 【必知必会的MySQL知识】④DCL语言

    • MySQL推荐文章

  • 如何让SQL Server像MySQL一样拥有慢查询日志（Slow Query Log慢日志）
  • MySQL中为什么要使用索引合并(Index Merge)？
  • MySQL派生表合并优化的原理和实现
  • 使用EF 连接 数据库 SQLserver、MySql 实现 CodeFirst
  • 升级到 MySQL 8.4，MySQL 启动报错：io_setup() failed with EAGAIN
  • TiKV 源码分析之 PointGet
  • 异构数据源同步之数据同步 → DataX 使用细节
  • 一文搞懂 MySQL 日志
  • 浅析MySQL 8.0直方图原理
  • 分布式任务调度内的 MySQL 分页查询优化

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号