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

2024-05-12 16:44

本文主要是介绍厉害了!12秒将百万数据通过EasyExcel导入MySQL数据库中,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一、写在开头

我们在上一篇文章中提到了通过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;@Resourceprivate ThreadPoolExecutor threadPoolExecutor;@Resourceprivate UserMapper userMapper;@Overridepublic 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;@Resourceprivate ThreadPoolExecutor threadPoolExecutor;@Resourceprivate UserMapper userMapper;@Overridepublic 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文件,打开都需要七八秒呢!
在这里插入图片描述


五、总结

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

六、结尾彩蛋

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

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

在这里插入图片描述

这篇关于厉害了!12秒将百万数据通过EasyExcel导入MySQL数据库中的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/983177

相关文章

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明(认真看哦) AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证,则会调用 #attemptAuthentica

大模型研发全揭秘:客服工单数据标注的完整攻略

在人工智能(AI)领域,数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者,掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中,工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注,不仅能够帮助提升客服自动化系统的智能化水平,还能优化客户服务流程,提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

关于数据埋点,你需要了解这些基本知识

产品汪每天都在和数据打交道,你知道数据来自哪里吗? 移动app端内的用户行为数据大多来自埋点,了解一些埋点知识,能和数据分析师、技术侃大山,参与到前期的数据采集,更重要是让最终的埋点数据能为我所用,否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式,可以区分为: 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理:自动程度高的,能解决通用统计,便于统一化管理,但个性化定

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

python: 多模块(.py)中全局变量的导入

文章目录 global关键字可变类型和不可变类型数据的内存地址单模块(单个py文件)的全局变量示例总结 多模块(多个py文件)的全局变量from x import x导入全局变量示例 import x导入全局变量示例 总结 global关键字 global 的作用范围是模块(.py)级别: 当你在一个模块(文件)中使用 global 声明变量时,这个变量只在该模块的全局命名空

使用SecondaryNameNode恢复NameNode的数据

1)需求: NameNode进程挂了并且存储的数据也丢失了,如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2)故障模拟 (1)kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 (2)删除NameNode存储的数据(/opt/module/hadoop-3.1.4/data/tmp/dfs/na

异构存储(冷热数据分离)

异构存储主要解决不同的数据,存储在不同类型的硬盘中,达到最佳性能的问题。 异构存储Shell操作 (1)查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies (2)为指定路径(数据存储目录)设置指定的存储策略 hdfs storagepolicies -setStoragePo

Hadoop集群数据均衡之磁盘间数据均衡

生产环境,由于硬盘空间不足,往往需要增加一块硬盘。刚加载的硬盘没有数据时,可以执行磁盘数据均衡命令。(Hadoop3.x新特性) plan后面带的节点的名字必须是已经存在的,并且是需要均衡的节点。 如果节点不存在,会报如下错误: 如果节点只有一个硬盘的话,不会创建均衡计划: (1)生成均衡计划 hdfs diskbalancer -plan hadoop102 (2)执行均衡计划 hd