你一定用得上的MySQL批量插入技巧

2024-02-27 11:50

本文主要是介绍你一定用得上的MySQL批量插入技巧,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

写在文章开头的一段话

文章的稿子是写在2023年的12月,累觉不爱,忙碌的工作一直无法抽身来梳理这篇文章,终于在2024的1月的深夜开始着手整理这篇文章。

在这里插入图片描述

你好,我叫sharkchili,目前还是在一线奋斗的Java开发,经历过很多有意思的项目,也写过很多有意思的文章,是CSDN Java领域的博客专家,也是Java Guide的维护者之一,非常欢迎你关注我的公众号:写代码的SharkChili,这里面会有笔者精心挑选的并发、JVM、MySQL数据库专栏,也有笔者日常分享的硬核技术小文。

在这里插入图片描述

关于MySQL批量插入的一些问题

MySQL一直是我们互联网行业比较常用的数据,当我们使用半ORM框架进行MySQL大批量插入操作时,你是否考虑过这些问题:

  1. 进行大数据量插入时,是否需要进行分批次插入,一次插入多少合适?有什么判断依据?
  2. 使用foreach进行大数据量的插入存在什么问题?
  3. 如果插入批量插入过程中,因为服务器宕机等原因导致插入失败要怎么办?

基于此类问题,笔者以自己日常的开发手段作为依据演示一下MySQL批量插入的技巧。

常见的3种插入方式演示

实验样本数据

为了演示,这里给出一张示例表,除了id以外,有10个varchar字段,也就是说全字段写满的话一条数据差不多1k左右:

CREATE TABLE `batch_insert_test` (`id` int NOT NULL AUTO_INCREMENT,`fileid_1` varchar(100) DEFAULT NULL,`fileid_2` varchar(100) DEFAULT NULL,`fileid_3` varchar(100) DEFAULT NULL,`fileid_4` varchar(100) DEFAULT NULL,`fileid_5` varchar(100) DEFAULT NULL,`fileid_6` varchar(100) DEFAULT NULL,`fileid_7` varchar(100) DEFAULT NULL,`fileid_8` varchar(100) DEFAULT NULL,`fileid_9` varchar(100) DEFAULT NULL,`fileid_10` varchar(100) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COMMENT='测试批量插入,一行数据1k左右';

使用逐行插入

我们首先采用逐行插入方式分别插入300010w条的数据,这里为了保证实验的准确性,提前进行代码预热,先插入5条数据,然后在进行大批量的插入:

/*** 逐行插入*/@Testvoid rowByRowInsert() {//预热先插入5条数据performCodeWarmUp(5);//生成10w条数据List<BatchInsertTest> testList = generateBatchInsertTestData();long start = System.currentTimeMillis();for (BatchInsertTest test : testList) {batchInsertTestMapper.insert(test);}long end = System.currentTimeMillis();log.info("逐行插入{}条数据耗时:{}", BATCH_INSERT_SIZE, end - start);}

输出结果如下,可以看到当进行3000条数据的逐条插入时耗时在3s左右:

逐行插入3000条数据耗时:3492

而逐行插入10w条的耗时将其2min,插入表现可以说是非常差劲:

05.988 INFO  c.s.w.WebTemplateApplicationTests:55   main                    逐行插入100000条数据耗时:119678

使用foreach语法实现批量插入

Mybatis为我们提供了foreach语法实现数据批量插入,从语法上不难看出,它会遍历我们传入的集合,生成一条批量插入语句,其语法格式大抵如下所示:

 insert into batch_insert_test (id, fileid_1, fileid_2, fileid_3, fileid_4, fileid_5, fileid_6, fileid_7, fileid_8, fileid_9, fileid_10) values (1, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),(2, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),(3, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10');

批量插入代码如下所示:

 /*** foreach插入*/@Testvoid forEachInsert() {/*** 代码预热*/performCodeWarmUp(5);List<BatchInsertTest> testList = generateBatchInsertTestData();long start = System.currentTimeMillis();batchInsertTestMapper.batchInsertTest(testList);long end = System.currentTimeMillis();log.info("foreach{}条数据耗时:{}", BATCH_INSERT_SIZE, end - start);}

对应xml配置如下:

<!-- 插入数据 --><insert id="batchInsertTest" parameterType="java.util.List">INSERT INTO batch_insert_test (fileid_1, fileid_2, fileid_3, fileid_4, fileid_5, fileid_6, fileid_7, fileid_8, fileid_9, fileid_10)VALUES<foreach collection="list" item="item" separator=",">(#{item.fileid1}, #{item.fileid2}, #{item.fileid3}, #{item.fileid4}, #{item.fileid5},#{item.fileid6}, #{item.fileid7}, #{item.fileid8}, #{item.fileid9}, #{item.fileid10})</foreach></insert>

实验结果如下,使用foreach进行插入3000条的数据耗时不到1s:

10.496 INFO  c.s.w.WebTemplateApplicationTests:79   main                    foreach3000条数据耗时:403

当我们进行10w条的数据插入时,受限于max_allowed_packet配置的大小,max_allowed_packet定义了服务器和客户端之间传输的最大数据包大小。该参数用于限制单个查询或语句可以传输的最大数据量,默认情况下为4M左右,所以这也最终导致了这10w条数据的插入直接失败了。

Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (106,100,142 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.

使用批处理完成插入

再来看看笔者最推荐的一种插入方式——批处理插入,在正式介绍这种插入方式前,读者先确认自己的链接配置是否添加了这条配置语句,只有在MySQL连接参数后面增加这一项配置才会使得MySQL5.1.13以上版本的驱动批量提交你的插入语句。

rewriteBatchedStatements=true

完成连接配置后,我们还需要对于批量插入的编码进行一定调整,Mybatis默认情况下执行器为Simple,这种执行器每次执行创建的都是一个全新的语句,也就是创建一个全新的PreparedStatement对象,这也就意味着每次提交的SQL语句的插入请求都无法缓存,每次调用时都需要重新解析SQL语句。
而我们的批处理则是将ExecutorType改为BATCH,执行时Mybatis会先将插入语句进行一次预编译生成PreparedStatement对象,发送一个网络请求进行数据解析和优化,因为ExecutorType改为BATCH,所以这次预编译之后,后续的插入的SQLDBMS时,就无需在进行预编译,可直接一次网络IO将批量插入的语句提交到MySQL上执行。


@Autowiredprivate SqlSessionFactory sqlSessionFactory;/*** session插入*/@Testvoid batchInsert() {/*** 代码预热*/performCodeWarmUp(5);List<BatchInsertTest> testList = generateBatchInsertTestData();SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);BatchInsertTestMapper sqlSessionMapper = sqlSession.getMapper(BatchInsertTestMapper.class);long start = System.currentTimeMillis();for (BatchInsertTest batchInsertTest : testList) {sqlSessionMapper.insert(batchInsertTest);}sqlSession.commit();long end = System.currentTimeMillis();log.info("批处理插入{}条数据耗时:{}", BATCH_INSERT_SIZE, end - start);}

可以看到进行3000条数据插入时,耗时也只需只需2ms左右:

05.226 INFO  c.s.w.WebTemplateApplicationTests:108  main                    批处理插入3000条数据耗时:179

而进行10w条数据批处理插入的时机只需4s左右,效率非常可观。

04.771 INFO  c.s.w.WebTemplateApplicationTests:108  main                    批处理插入100000条数据耗时:4635

原因分析

针对上述三种方式,笔者来解释一下为什么在能够确保不出错的情况下,批处理插入的效率最高,我们都知道MySQL进行插入操作时整体的耗时比例如下:

链接耗时 (30%)
发送query到服务器 (20%)
解析query (20%)
插入操作 (10% * 词条数目)
插入index (10% * Index的数目)
关闭链接 (10%)

由此可知,进行SQL插入操作时,最耗时的操作是链接,这也就是为什么在进行3000条数据插入时,foreach批处理插入的性能的性能表现最出色。因为逐行插入提交时,每一条插入操作都会进行至少两次的网络返回(如果生成的是stament对象则是两次,PreparedStatement则还要加上预编译的网络往返),在大量的插入情况下,所有的语句都需要经历一次最耗时的链接操作,性能自然是下降了不少。

在这里插入图片描述

我们再来说说为什么批处理比foreach高效的原因,明明同样是3000条语句的插入,foreach传输的数据包大小也小于批处理,为什么批处理的性能却要好于foreach插入操作呢?

我们在上文讲批处理的时候提到,Mybatis在默认情况下,执行器是为SIMPLE,这就意味每次提交的插入操作的SQL语句都是相当于全新的PreparedStatement,都是需要进行预编译的,所以一条插入的SQL则是需要经历预编译和执行两次的网络往返,对应的代码也相当于下面这段JDBC代码:

		  // 创建Statement对象PreparedStatement statement = connection.createStatement();// 批量插入的数据String[] names = {"John Doe", "Jane Smith", "Mike Johnson"};int[] ages = {30, 25, 35};String[] cities = {"New York", "London", "Paris"};// 构建批量插入的SQL语句StringBuilder insertQuery = new StringBuilder("INSERT INTO mytable (name, age, city) VALUES ");for (int i = 0; i < names.length; i++) {insertQuery.append("('").append(names[i]).append("', ").append(ages[i]).append(", '").append(cities[i]).append("')");if (i < names.length - 1) {insertQuery.append(", ");}}// 执行批量插入操作statement.executeUpdate(insertQuery.toString());// 关闭连接和Statementstatement.close();connection.close();

可以看到在每一次使用foreach进行插入操作时,都需要重新创建一个PreparedStatement构建出一个SQL语句,每次提交时MySQL都需要进行一次预编译,这意味着用户每次使用foreach插入时,都需要进行一次预编译的网络IO,也正是这个原因使得其性能相较于批处理会逊色一些。

在这里插入图片描述

而批处理则不同,在我们的代码中,我们手动将ExecutorType改为BATCH,这样一来,每次进行批量插入时,Mybatis会先拿着我们的SQL语句创建成一个PreparedStatement提交到MySQL上进行预编译,这样一来本次会话所有相同的SQL语句直接提交时,就无需经过编译检查的操作,后续批量插入效率显著提升。

在这里插入图片描述

更高效的插入方式

因为Mybatis对于原生批处理操作做了很多的封装,其中涉及很多校验检查和解析等繁琐的流程,所以通过使用原生JDBC Batch来避免这些繁琐的解析、动态拦截等操作,对于MySQL批量插入也会有显著的提升。感兴趣的读者可以自行尝试,笔者这里就不多做演示了。

一次插入多少数据量合适

明确要使用批处理进行批量插入之后,我们再来了解下一个问题,一次性批量插入多少条SQL语句比较合适?

对此我们基于100w的数据,分别按照每次1050010002000080000条压测,最终实验结果如下

80000的数据,每次插入10条,耗时:14555
80000的数据,每次插入500条,耗时:5001
80000的数据,每次插入1000条,耗时:3960
80000的数据,每次插入2000条,耗时:3788
80000的数据,每次插入3000条,耗时:3993
80000的数据,每次插入4000条,耗时:3847

在经过笔者的压测实验时发现,在2000条差不多2M大小的情况下插入时的性能最出色。这一点笔者也在网上看到一篇文章提到MySQL的全局变量max_allowed_packet,它限制了每条SQL语句的大小,默认情况下为4M,而这位作者的实验则是插入数据的大小在max_allowed_packet的一半情况下性能最佳。

show variables like 'max_allowed_packet%';  

当然并不一定只有上述条件影响批量插入的性能,影响批量插入的性能原因还有:

  1. 插入缓存:对于innodb存储引擎来说,插入是需要耗费缓冲池内存的,如果在写密集的情况下,插入缓存会占用过多的缓冲池内存,若插入操作占用大小超过缓冲池的一半,则会影响操其他的操作。

关于缓冲池的大小,可以通过下面这条SQL查看,默认情况下为134M:

show variables like 'innodb_buffer_pool_size';
  1. 索引的维护:这点相信读者比较熟悉,如果每次插入涉及大量无序且多个索引的维护,导致B+tree进行节点分裂合并等处理,则会消耗大量的计算资源,从而间接影响插入效率。

注意事项

批处理就是将一批操作提交至MySQL服务器一次性操作,但无法保证事务的原子性,所以读者在使用批处理操作时,若需要保证操作原子性则需要考虑一下事务问题。

小结

整篇文章的篇幅不算很大,可以看到笔者针对此类问题常见的做法是:

  1. 明确问题和要解决的问题,以批量插入为例,首要问题就是现有方案中可以有几种插入方式和如何提高这些插入技术的性能。
  2. 将问题切割成无数个子问题,笔者将批量插入按步骤分为:如何插入和插入多少的子问题。
  3. 搜索常见的解决方案,即笔者上述的的逐条插入、foreach、批处理3种插入方式。
  4. 基于现成方案采用不同量级的样本进行求证,为避免偶然性,笔者将插入的量级设置为几千甚至几万不等。
  5. 基于实验样本复盘总结,在明确批量插入技术之后,继续查阅资料寻找插入量级,并继续实验从而得出最终研究成果。
  6. 进阶,对于上述成果继续加以求证了解工作原理,并对后续可能存在的问题查阅更多资料进行兜底。

更多

这篇文章并不代表的批量插入方案止步于此,后续可能存在大事务回滚和失败重试的需求优化,笔者也都有对这些问题进行针对性的兜底方案,这些兜底的技术方案,笔者也会在后续的文章中不断进行迭代补充,如果你想实时收到这些文章的更新可以通过下方二维码关注一下笔者公众号和笔者保持交流:

我是sharkchili,CSDN Java 领域博客专家,开源项目—JavaGuide contributor,我想写一些有意思的东西,希望对你有帮助,如果你想实时收到我写的硬核的文章也欢迎你关注我的公众号:
写代码的SharkChili,同时我的公众号也有我精心整理的并发编程、JVM、MySQL数据库个人专栏导航。

在这里插入图片描述

参考资料

求求你们了,MyBatis 批量插入别再乱用 foreach 了,5000 条数据花了 14 分钟。。:https://mp.weixin.qq.com/s/HjrFuGRFG-gw4tzopCwjXQ

10万条数据批量插入,到底怎么做才快?
https://juejin.cn/post/7025876113943445518

jdbc批量插入的4种方式【百万条数据插入只需几秒】
:https://blog.csdn.net/C3245073527/article/details/122071045

Java数据库JDBC——prepareStatement的用法和解释:https://blog.csdn.net/nnzhuilian/article/details/86241787

MySQL一次插入多少条数据合适:https://cloud.tencent.com/developer/article/1975261

这篇关于你一定用得上的MySQL批量插入技巧的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



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

相关文章

SpringBoot整合mybatisPlus实现批量插入并获取ID详解

《SpringBoot整合mybatisPlus实现批量插入并获取ID详解》这篇文章主要为大家详细介绍了SpringBoot如何整合mybatisPlus实现批量插入并获取ID,文中的示例代码讲解详细... 目录【1】saveBATch(一万条数据总耗时:2478ms)【2】集合方式foreach(一万条数

快速修复一个Panic的Linux内核的技巧

《快速修复一个Panic的Linux内核的技巧》Linux系统中运行了不当的mkinitcpio操作导致内核文件不能正常工作,重启的时候,内核启动中止于Panic状态,该怎么解决这个问题呢?下面我们就... 感谢China编程(www.chinasem.cn)网友 鸢一雨音 的投稿写这篇文章是有原因的。为了配置完

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Python ZIP文件操作技巧详解

《PythonZIP文件操作技巧详解》在数据处理和系统开发中,ZIP文件操作是开发者必须掌握的核心技能,Python标准库提供的zipfile模块以简洁的API和跨平台特性,成为处理ZIP文件的首选... 目录一、ZIP文件操作基础三板斧1.1 创建压缩包1.2 解压操作1.3 文件遍历与信息获取二、进阶技

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1