10万条数据批量插入，到底怎么做才快？

2023-10-25 14:10
文章标签 数据 怎么 到底 批量 插入 万条

本文主要是介绍10万条数据批量插入，到底怎么做才快？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

    • 1. 思路分析
    • 2. 数据测试
      • 2.1 方案一测试
      • 2.2 方案二测试
      • 2.3 对比分析
    • 3. MP 怎么做的？
    • 4. 小结

上周松哥转载了一个数据批量插入的文章，里边和大家聊了一下数据批量插入的问题，批量插入到底怎么做才快。

有个小伙伴看了文章后提出了不同的意见：

松哥认真和 BUG 同学聊了下，基本上明白了这个小伙伴的意思，于是我自己也写了个测试案例，重新整理了今天这篇文章，希望和小伙伴们一起探讨这个问题，也欢迎小伙伴们提出更好的方案。

1. 思路分析

批量插入这个问题，我们用 JDBC 操作，其实就是两种思路吧：

  1. 用一个 for 循环，把数据一条一条的插入（这种需要开启批处理）。
  2. 生成一条插入 sql，类似这种 insert into user(username,address) values('aa','bb'),('cc','dd')...。

到底哪种快呢？

我们从两方面来考虑这个问题：

  1. 插入 SQL 本身执行的效率。
  2. 网络 I/O。

先说第一种方案，就是用 for 循环循环插入：

  • 这种方案的优势在于，JDBC 中的 PreparedStatement 有预编译功能，预编译之后会缓存起来，后面的 SQL 执行会比较快并且 JDBC 可以开启批处理，这个批处理执行非常给力。
  • 劣势在于，很多时候我们的 SQL 服务器和应用服务器可能并不是同一台，所以必须要考虑网络 IO，如果网络 IO 比较费时间的话，那么可能会拖慢 SQL 执行的速度。

再来说第二种方案，就是生成一条 SQL 插入：

  • 这种方案的优势在于只有一次网络 IO，即使分片处理也只是数次网络 IO，所以这种方案不会在网络 IO 上花费太多时间。
  • 当然这种方案有好几个劣势，一是 SQL 太长了，甚至可能需要分片后批量处理；二是无法充分发挥 PreparedStatement 预编译的优势，SQL 要重新解析且无法复用；三是最终生成的 SQL 太长了，数据库管理器解析这么长的 SQL 也需要时间。

所以我们最终要考虑的就是我们在网络 IO 上花费的时间，是否超过了 SQL 插入的时间？这是我们要考虑的核心问题。

2. 数据测试

接下来我们来做一个简单的测试，批量插入 5 万条数据看下。

首先准备一个简单的测试表：

CREATE TABLE `user` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`username` varchar(255) DEFAULT NULL,`address` varchar(255) DEFAULT NULL,`password` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

接下来创建一个 Spring Boot 工程，引入 MyBatis 依赖和 MySQL 驱动，然后 application.properties 中配置一下数据库连接信息：

spring.datasource.username=root
spring.datasource.password=123
spring.datasource.url=jdbc:mysql:///batch_insert?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true

大家需要注意，这个数据库连接 URL 地址中多了一个参数 rewriteBatchedStatements，这是核心。

MySQL JDBC 驱动在默认情况下会无视 executeBatch() 语句，把我们期望批量执行的一组 sql 语句拆散，一条一条地发给 MySQL 数据库，批量插入实际上是单条插入，直接造成较低的性能。将 rewriteBatchedStatements 参数置为 true, 数据库驱动才会帮我们批量执行 SQL。

OK，这样准备工作就做好了。

2.1 方案一测试

首先我们来看方案一的测试，即一条一条的插入（实际上是批处理）。

首先创建相应的 mapper，如下：

@Mapper
public interface UserMapper {Integer addUserOneByOne(User user);
}

对应的 XML 文件如下：

<insert id="addUserOneByOne">insert into user (username,address,password) values (#{username},#{address},#{password})
</insert>

service 如下：

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IUserService {private static final Logger logger = LoggerFactory.getLogger(UserService.class);@AutowiredUserMapper userMapper;@AutowiredSqlSessionFactory sqlSessionFactory;@Transactional(rollbackFor = Exception.class)public void addUserOneByOne(List<User> users) {SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);UserMapper um = session.getMapper(UserMapper.class);long startTime = System.currentTimeMillis();for (User user : users) {um.addUserOneByOne(user);}session.commit();long endTime = System.currentTimeMillis();logger.info("一条条插入 SQL 耗费时间 {}", (endTime - startTime));}
}

这里我要说一下：

虽然是一条一条的插入，但是我们要开启批处理模式（BATCH），这样前前后后就只用这一个 SqlSession，如果不采用批处理模式，反反复复的获取 Connection 以及释放 Connection 会耗费大量时间，效率奇低，这种效率奇低的方式松哥就不给大家测试了。

接下来写一个简单的测试接口看下：

@RestController
public class HelloController {private static final Logger logger = getLogger(HelloController.class);@AutowiredUserService userService;/*** 一条一条插入*/@GetMapping("/user2")public void user2() {List<User> users = new ArrayList<>();for (int i = 0; i < 50000; i++) {User u = new User();u.setAddress("广州：" + i);u.setUsername("张三：" + i);u.setPassword("123：" + i);users.add(u);}userService.addUserOneByOne(users);}
}

写个简单的单元测试：

/*** * 单元测试加事务的目的是为了插入之后自动回滚，避免影响下一次测试结果* 一条一条插入*/
@Test
@Transactional
void addUserOneByOne() {List<User> users = new ArrayList<>();for (int i = 0; i < 50000; i++) {User u = new User();u.setAddress("广州：" + i);u.setUsername("张三：" + i);u.setPassword("123：" + i);users.add(u);}userService.addUserOneByOne(users);
}

可以看到，耗时 901 毫秒，5w 条数据插入不到 1 秒。

2.2 方案二测试

方案二是生成一条 SQL 然后插入。

mapper 如下：

@Mapper
public interface UserMapper {void addByOneSQL(@Param("users") List<User> users);
}

对应的 SQL 如下：

<insert id="addByOneSQL">insert into user (username,address,password) values<foreach collection="users" item="user" separator=",">(#{user.username},#{user.address},#{user.password})</foreach>
</insert>

service 如下：

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IUserService {private static final Logger logger = LoggerFactory.getLogger(UserService.class);@AutowiredUserMapper userMapper;@AutowiredSqlSessionFactory sqlSessionFactory;@Transactional(rollbackFor = Exception.class)public void addByOneSQL(List<User> users) {long startTime = System.currentTimeMillis();userMapper.addByOneSQL(users);long endTime = System.currentTimeMillis();logger.info("合并成一条 SQL 插入耗费时间 {}", (endTime - startTime));}
}

然后在单元测试中调一下这个方法：

/*** 合并成一条 SQL 插入*/
@Test
@Transactional
void addByOneSQL() {List<User> users = new ArrayList<>();for (int i = 0; i < 50000; i++) {User u = new User();u.setAddress("广州：" + i);u.setUsername("张三：" + i);u.setPassword("123：" + i);users.add(u);}userService.addByOneSQL(users);
}

可以看到插入 5 万条数据耗时 1805 毫秒。

可以看到，生成一条 SQL 的执行效率还是要差一点。

另外还需要注意，第二种方案还有一个问题，就是当数据量大的时候，生成的 SQL 将特别的长，MySQL 可能一次性处理不了这么大的 SQL，这个时候就需要修改 MySQL 的配置或者对待插入的数据进行分片处理了，这些操作又会导致插入时间更长。

2.3 对比分析

很明显，方案一更具优势。当批量插入十万、二十万数据的时候，方案一的优势会更加明显（方案二则需要修改 MySQL 配置或者对待插入数据进行分片）。

3. MP 怎么做的？

小伙伴们知道，其实 MyBatis Plus 里边也有一个批量插入的方法 saveBatch，我们来看看它的实现源码：

@Transactional(rollbackFor = Exception.class)
@Override
public boolean saveBatch(Collection<T> entityList, int batchSize) {String sqlStatement = getSqlStatement(SqlMethod.INSERT_ONE);return executeBatch(entityList, batchSize, (sqlSession, entity) -> sqlSession.insert(sqlStatement, entity));
}

可以看到，这里拿到的 sqlStatement 就是一个 INSERT_ONE，即一条一条插入。

再来看 executeBatch 方法，如下：

public static <E> boolean executeBatch(Class<?> entityClass, Log log, Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {Assert.isFalse(batchSize < 1, "batchSize must not be less than one");return !CollectionUtils.isEmpty(list) && executeBatch(entityClass, log, sqlSession -> {int size = list.size();int i = 1;for (E element : list) {consumer.accept(sqlSession, element);if ((i % batchSize == 0) || i == size) {sqlSession.flushStatements();}i++;}});
}

这里注意 return 中的第三个参数，是一个 lambda 表达式，这也是 MP 中批量插入的核心逻辑，可以看到，MP 先对数据进行分片（默认分片大小是 1000），分片完成之后，也是一条一条的插入。继续查看 executeBatch 方法，就会发现这里的 sqlSession 其实也是一个批处理的 sqlSession，并非普通的 sqlSession。

综上，MP 中的批量插入方案跟我们 2.1 小节的批量插入思路其实是一样的。

4. 小结

好啦，经过上面的分析，现在小伙伴们知道了批量插入该怎么做了吧？

松哥提供了一个测试案例，公众号后台回复批量插入测试获取案例地址，案例中有三个单元测试方法，直接运行，就可以看到批量插入的时间差异（数据库脚本在 resources 目录下）。

感兴趣的小伙伴不妨试试~

最后再次感谢 BUG 童鞋提出的意见~

这篇关于10万条数据批量插入，到底怎么做才快？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/283111。

相关文章

AI绘图怎么变现？想做点副业的小白必看！

AI绘图怎么变现？想做点副业的小白必看！

在科技飞速发展的今天，AI绘图作为一种新兴技术，不仅改变了艺术创作的方式，也为创作者提供了多种变现途径。本文将详细探讨几种常见的AI绘图变现方式，帮助创作者更好地利用这一技术实现经济收益。 更多实操教程和AI绘画工具，可以扫描下方，免费获取 定制服务：个性化的创意商机 个性化定制 AI绘图技术能够根据用户需求生成个性化的头像、壁纸、插画等作品。例如，姓氏头像在电商平台上非常受欢迎，
阅读更多...
大模型研发全揭秘：客服工单数据标注的完整攻略

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

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

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

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

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

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

W外链微信推广短连接怎么做？

制作微信推广链接的难点分析 一、内容创作难度 制作微信推广链接时，首先需要创作有吸引力的内容。这不仅要求内容本身有趣、有价值，还要能够激起人们的分享欲望。对于许多企业和个人来说，尤其是那些缺乏创意和写作能力的人来说，这是制作微信推广链接的一大难点。 二、精准定位难度 微信用户群体庞大，不同用户的需求和兴趣各异。因此，制作推广链接时需要精准定位目标受众，以便更有效地吸引他们点击并分享链接
阅读更多...
使用SecondaryNameNode恢复NameNode的数据

使用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集群数据均衡之磁盘间数据均衡

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

生产环境，由于硬盘空间不足，往往需要增加一块硬盘。刚加载的硬盘没有数据时，可以执行磁盘数据均衡命令。（Hadoop3.x新特性） plan后面带的节点的名字必须是已经存在的，并且是需要均衡的节点。 如果节点不存在，会报如下错误： 如果节点只有一个硬盘的话，不会创建均衡计划： （1）生成均衡计划 hdfs diskbalancer -plan hadoop102 （2）执行均衡计划 hd
阅读更多...
电脑桌面文件删除了怎么找回来？别急，快速恢复攻略在此

电脑桌面文件删除了怎么找回来？别急，快速恢复攻略在此

在日常使用电脑的过程中，我们经常会遇到这样的情况：一不小心，桌面上的某个重要文件被删除了。这时，大多数人可能会感到惊慌失措，不知所措。 其实，不必过于担心，因为有很多方法可以帮助我们找回被删除的桌面文件。下面，就让我们一起来了解一下这些恢复桌面文件的方法吧。 一、使用撤销操作 如果我们刚刚删除了桌面上的文件，并且还没有进行其他操作，那么可以尝试使用撤销操作来恢复文件。在键盘上同时按下“C
阅读更多...
【Prometheus】PromQL向量匹配实现不同标签的向量数据进行运算

【Prometheus】PromQL向量匹配实现不同标签的向量数据进行运算

✨✨ 欢迎大家来到景天科技苑✨✨ 🎈🎈 养成好习惯，先赞后看哦~🎈🎈 🏆 作者简介：景天科技苑 🏆《头衔》：大厂架构师，华为云开发者社区专家博主，阿里云开发者社区专家博主，CSDN全栈领域优质创作者，掘金优秀博主，51CTO博客专家等。 🏆《博客》：Python全栈，前后端开发，小程序开发，人工智能，js逆向，App逆向，网络系统安全，数据分析，Django，fastapi
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2