厉害了！12秒将百万数据通过EasyExcel导入MySQL数据库中

JavaBuild

发布于 2024-05-27 15:25:50

2650

发布于 2024-05-27 15:25:50

一、写在开头

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

二、性能瓶颈分析

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

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

三、针对耗时1进行优化

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

【优化前代码片段】

@Slf4j
@Service
public class EasyExcelImportHandler implements ReadListener<User> {
    /*成功数据*/
    private final CopyOnWriteArrayList<User> successList = new CopyOnWriteArrayList<>();
    /*单次处理条数*/
    private final static int BATCH_COUNT = 20000;
    @Resource
    private ThreadPoolExecutor threadPoolExecutor;
    @Resource
    private UserMapper userMapper;



    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        if(StringUtils.isNotBlank(user.getName())){
            successList.add(user);
            return;
        }
        if(successList.size() >= BATCH_COUNT){
            log.info("读取数据：{}", successList.size());
            saveData();
        }
    }
    ///
    ///
}

【优化后代码片段】

@Slf4j
@Service
public class EasyExcelImportHandler implements ReadListener<User> {
    /*成功数据*/
   // private final CopyOnWriteArrayList<User> successList = new CopyOnWriteArrayList<>();
    private final List<User> successList =  new ArrayList<>();
    /*单次处理条数，有原来2万变为10万*/
    private final static int BATCH_COUNT = 100000;
    @Resource
    private ThreadPoolExecutor threadPoolExecutor;
    @Resource
    private UserMapper userMapper;


    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        if (StringUtils.isNotBlank(user.getName())) {
            successList.add(user);
            return;
        }
        //size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入
        if (successList.size() >= BATCH_COUNT) {
            log.info("读取数据：{}", successList.size());
            saveData();
            //清理集合便于GC回收
            successList.clear();
        }
    }
    ///
    ///
 }

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

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

四、针对耗时3进行优化

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

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

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

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

public class JdbcConnectUtil {

    private static  String driver;
    private static  String url;
    private static  String name;
    private static  String password;

    /**
     * 创建数据Properties集合对象加载加载配置文件
     */
    static {
        Properties properties = new Properties();
        try {
            properties.load(JdbcConnectUtil.class.getClassLoader().getResourceAsStream("generator.properties"));
            driver = properties.getProperty("jdbc.driverClass");
            url = properties.getProperty("jdbc.connectionURL");
            name = properties.getProperty("jdbc.userId");
            password = properties.getProperty("jdbc.password");
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接对象
     * @return
     * @throws Exception
     */
    public static Connection getConnect() throws Exception {
        return DriverManager.getConnection(url, name, password);
    }

    /**
     * 关闭数据库相关资源
     * @param conn
     * @param ps
     * @param rs
     */
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (conn != null) conn.close();
            if (ps != null) ps.close();
            if (rs != null) rs.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static void close(Connection conn, PreparedStatement ps) {
        close(conn, ps, null);
    }
    public static void close(Connection conn, ResultSet rs) {
        close(conn, null, rs);
    }
}

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

 /**
     * jdbc+事务处理
     */
    public void import4Jdbc(){

        //分批读取+JDBC分批插入+手动事务控制
        Connection conn = null;
        //JDBC存储过程
        PreparedStatement ps = null;
        try {
            //建立jdbc数据库连接
            conn = JdbcConnectUtil.getConnect();
            //关闭事务默认提交
            conn.setAutoCommit(false);
            String sql = "insert into user (id,name, phone_num, address) values";
            sql += "(?,?,?,?)";
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < successList.size(); i++) {
                User user = new User();
                ps.setInt(1,successList.get(i).getId());
                ps.setString(2,successList.get(i).getName());
                ps.setString(3,successList.get(i).getPhoneNum());
                ps.setString(4,successList.get(i).getAddress());
                //将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }
            //执行批处理
            ps.executeBatch();
            //手动提交事务
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
         //记得关闭连接
            JdbcConnectUtil.close(conn,ps);
        }
    }

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

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

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

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

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

【XML中手动批量插入】

 <insert id="insertSelective" parameterType="java.util.List">
    insert into user
    (id,name, phone_num, address
      )
    values
    <foreach collection="list" item="item" separator=",">
        (#{item.id},#{item.name},#{item.phoneNum},#{item.address})
    </foreach>
  </insert>

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

/**
     * 采用多线程读取数据
     */
    private void saveData() {
        List<List<User>> lists = ListUtil.split(successList, 1000);
        CountDownLatch countDownLatch = new CountDownLatch(lists.size());
        for (List<User> list : lists) {
            threadPoolExecutor.execute(() -> {
                try {
                    userMapper.insertSelective(list.stream().map(o -> {
                        User user = new User();
                        user.setName(o.getName());
                        user.setId(o.getId());
                        user.setPhoneNum(o.getPhoneNum());
                        user.setAddress(o.getAddress());
                        return user;
                    }).collect(Collectors.toList()));
                } catch (Exception e) {
                    log.error("启动线程失败,e:{}", e.getMessage(), e);
                } finally {
                    //执行完一个线程减1,直到执行完
                    countDownLatch.countDown();
                }
            });
        }
        // 等待所有线程执行完
        try {
            countDownLatch.await();
        } catch (Exception e) {
            log.error("等待所有线程执行完异常,e:{}", e.getMessage(), e);
        }
        // 提前将不再使用的集合清空，释放资源
        successList.clear();
        lists.clear();
    }

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