13 秒插入 30 万条数据，这才是 Java 批量插入正确的姿势！

2024-03-18 23:12
文章标签 java 数据 正确 批量 插入 30 13 姿势 这才 万条

本文主要是介绍13 秒插入 30 万条数据，这才是 Java 批量插入正确的姿势！，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

本文主要讲述通过MyBatis、JDBC等做大数据量数据插入的案例和结果。

30万条数据插入插入数据库验证

  • 实体类、mapper和配置文件定义

    • User实体

    • mapper接口

    • mapper.xml文件

    • jdbc.properties

    • sqlMapConfig.xml

  • 不分批次直接梭哈

  • 循环逐条插入

  • MyBatis实现插入30万条数据

  • JDBC实现插入30万条数据

  • 总结

验证的数据库表结构如下：

CREATE TABLE `t_user` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',`username` varchar(64) DEFAULT NULL COMMENT '用户名称',`age` int(4) DEFAULT NULL COMMENT '年龄',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

话不多说，开整！

实体类、mapper和配置文件定义

User实体
/*** <p>用户实体</p>** @Author zjq* @Date 2021/8/3*/
@Data
public class User {private int id;private String username;private int age;}
mapper接口
public interface UserMapper {/*** 批量插入用户* @param userList*/void batchInsertUser(@Param("list") List<User> userList);
}
mapper.xml文件
<!-- 批量插入用户信息 -->
<insert id="batchInsertUser" parameterType="java.util.List">insert into t_user(username,age) values<foreach collection="list" item="item" index="index" separator=",">(#{item.username},#{item.age})</foreach>
</insert>
jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root
sqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><!--通过properties标签加载外部properties文件--><properties resource="jdbc.properties"></properties><!--自定义别名--><typeAliases><typeAlias type="com.zjq.domain.User" alias="user"></typeAlias></typeAliases><!--数据源环境--><environments default="developement"><environment id="developement"><transactionManager type="JDBC"></transactionManager><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></dataSource></environment></environments><!--加载映射文件--><mappers><mapper resource="com/zjq/mapper/UserMapper.xml"></mapper></mappers></configuration>

不分批次直接梭哈

MyBatis直接一次性批量插入30万条，代码如下：

@Test
public void testBatchInsertUser() throws IOException {InputStream resourceAsStream =Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession session = sqlSessionFactory.openSession();System.out.println("===== 开始插入数据 =====");long startTime = System.currentTimeMillis();try {List<User> userList = new ArrayList<>();for (int i = 1; i <= 300000; i++) {User user = new User();user.setId(i);user.setUsername("共饮一杯无 " + i);user.setAge((int) (Math.random() * 100));userList.add(user);}session.insert("batchInsertUser", userList); // 最后插入剩余的数据session.commit();long spendTime = System.currentTimeMillis()-startTime;System.out.println("成功插入 30 万条数据,耗时："+spendTime+"毫秒");} finally {session.close();}
}

可以看到控制台输出：

Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (27759038 >yun 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.

图片

超出最大数据包限制了，可以通过调整max_allowed_packet限制来提高可以传输的内容，不过由于30万条数据超出太多，这个不可取，梭哈看来是不行了 😅😅😅

既然梭哈不行那我们就一条一条循环着插入行不行呢

循环逐条插入

mapper接口和mapper文件中新增单个用户新增的内容如下:

/*** 新增单个用户* @param user*/
void insertUser(User user);

<!-- 新增用户信息 -->
<insert id="insertUser" parameterType="user">insert into t_user(username,age) values(#{username},#{age})
</insert>

调整执行代码如下：

@Test
public void testCirculateInsertUser() throws IOException {InputStream resourceAsStream =Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession session = sqlSessionFactory.openSession();System.out.println("===== 开始插入数据 =====");long startTime = System.currentTimeMillis();try {for (int i = 1; i <= 300000; i++) {User user = new User();user.setId(i);user.setUsername("共饮一杯无 " + i);user.setAge((int) (Math.random() * 100));// 一条一条新增session.insert("insertUser", user);session.commit();}long spendTime = System.currentTimeMillis()-startTime;System.out.println("成功插入 30 万条数据,耗时："+spendTime+"毫秒");} finally {session.close();}
}

执行后可以发现磁盘IO占比飙升，一直处于高位。

图片

等啊等等啊等，好久还没执行完

图片

先不管他了太慢了先搞其他的，等会再来看看结果吧。

控制台输出如下：

图片

总共执行了14909367毫秒，换算出来是4小时八分钟。太慢了。。

图片

还是优化下之前的批处理方案吧

MyBatis实现插入30万条数据

先清理表数据，然后优化批处理执行插入：

-- 清空用户表
TRUNCATE table  t_user;

以下是通过 MyBatis 实现 30 万条数据插入代码实现：

/*** 分批次批量插入* @throws IOException*/
@Test
public void testBatchInsertUser() throws IOException {InputStream resourceAsStream =Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession session = sqlSessionFactory.openSession();System.out.println("===== 开始插入数据 =====");long startTime = System.currentTimeMillis();int waitTime = 10;try {List<User> userList = new ArrayList<>();for (int i = 1; i <= 300000; i++) {User user = new User();user.setId(i);user.setUsername("共饮一杯无 " + i);user.setAge((int) (Math.random() * 100));userList.add(user);if (i % 1000 == 0) {session.insert("batchInsertUser", userList);// 每 1000 条数据提交一次事务session.commit();userList.clear();// 等待一段时间Thread.sleep(waitTime * 1000);}}// 最后插入剩余的数据if(!CollectionUtils.isEmpty(userList)) {session.insert("batchInsertUser", userList);session.commit();}long spendTime = System.currentTimeMillis()-startTime;System.out.println("成功插入 30 万条数据,耗时："+spendTime+"毫秒");} catch (Exception e) {e.printStackTrace();} finally {session.close();}
}

使用了 MyBatis 的批处理操作，将每 1000 条数据放在一个批次中插入，能够较为有效地提高插入速度。同时请注意在循环插入时要带有合适的等待时间和批处理大小，以防止出现内存占用过高等问题。此外，还需要在配置文件中设置合理的连接池和数据库的参数，以获得更好的性能。

图片

在上面的示例中，我们每插入1000行数据就进行一次批处理提交，并等待10秒钟。这有助于控制内存占用，并确保插入操作平稳进行。

图片

五十分钟执行完毕，时间主要用在了等待上。

如果低谷时期执行，CPU和磁盘性能又足够的情况下，直接批处理不等待执行：

/*** 分批次批量插入* @throws IOException*/
@Test
public void testBatchInsertUser() throws IOException {InputStream resourceAsStream =Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession session = sqlSessionFactory.openSession();System.out.println("===== 开始插入数据 =====");long startTime = System.currentTimeMillis();int waitTime = 10;try {List<User> userList = new ArrayList<>();for (int i = 1; i <= 300000; i++) {User user = new User();user.setId(i);user.setUsername("共饮一杯无 " + i);user.setAge((int) (Math.random() * 100));userList.add(user);if (i % 1000 == 0) {session.insert("batchInsertUser", userList);// 每 1000 条数据提交一次事务session.commit();userList.clear();}}// 最后插入剩余的数据if(!CollectionUtils.isEmpty(userList)) {session.insert("batchInsertUser", userList);session.commit();}long spendTime = System.currentTimeMillis()-startTime;System.out.println("成功插入 30 万条数据,耗时："+spendTime+"毫秒");} catch (Exception e) {e.printStackTrace();} finally {session.close();}
}

则24秒可以完成数据插入操作：

图片

图片

可以看到短时CPU和磁盘占用会飙高。

把批处理的量再调大一些调到5000，在执行：

图片

13秒插入成功30万条，直接芜湖起飞🛫🛫🛫

JDBC实现插入30万条数据

JDBC循环插入的话跟上面的mybatis逐条插入类似，不再赘述。

以下是 Java 使用 JDBC 批处理实现 30 万条数据插入的示例代码。请注意，该代码仅提供思路，具体实现需根据实际情况进行修改。

/*** JDBC分批次批量插入* @throws IOException*/
@Test
public void testJDBCBatchInsertUser() throws IOException {Connection connection = null;PreparedStatement preparedStatement = null;String databaseURL = "jdbc:mysql://localhost:3306/test";String user = "root";String password = "root";try {connection = DriverManager.getConnection(databaseURL, user, password);// 关闭自动提交事务，改为手动提交connection.setAutoCommit(false);System.out.println("===== 开始插入数据 =====");long startTime = System.currentTimeMillis();String sqlInsert = "INSERT INTO t_user ( username, age) VALUES ( ?, ?)";preparedStatement = connection.prepareStatement(sqlInsert);Random random = new Random();for (int i = 1; i <= 300000; i++) {preparedStatement.setString(1, "共饮一杯无 " + i);preparedStatement.setInt(2, random.nextInt(100));// 添加到批处理中preparedStatement.addBatch();if (i % 1000 == 0) {// 每1000条数据提交一次preparedStatement.executeBatch();connection.commit();System.out.println("成功插入第 "+ i+" 条数据");}}// 处理剩余的数据preparedStatement.executeBatch();connection.commit();long spendTime = System.currentTimeMillis()-startTime;System.out.println("成功插入 30 万条数据,耗时："+spendTime+"毫秒");} catch (SQLException e) {System.out.println("Error: " + e.getMessage());} finally {if (preparedStatement != null) {try {preparedStatement.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}
}

图片

图片

上述示例代码中，我们通过 JDBC 连接 MySQL 数据库，并执行批处理操作插入数据。具体实现步骤如下：

  1. 获取数据库连接。

  2. 创建 Statement 对象。

  3. 定义 SQL 语句，使用 PreparedStatement 对象预编译 SQL 语句并设置参数。

  4. 执行批处理操作。

  5. 处理剩余的数据。

  6. 关闭 Statement 和 Connection 对象。

使用setAutoCommit(false) 来禁止自动提交事务，然后在每次批量插入之后手动提交事务。每次插入数据时都新建一个 PreparedStatement 对象以避免状态不一致问题。在插入数据的循环中，每 10000 条数据就执行一次 executeBatch() 插入数据。

另外，需要根据实际情况优化连接池和数据库的相关配置，以防止连接超时等问题。

总结

实现高效的大量数据插入需要结合以下优化策略（建议综合使用）：

1.批处理： 批量提交SQL语句可以降低网络传输和处理开销，减少与数据库交互的次数。在Java中可以使用Statement或者PreparedStatement的addBatch()方法来添加多个SQL语句，然后一次性执行executeBatch()方法提交批处理的SQL语句。

在循环插入时带有适当的等待时间和批处理大小，从而避免内存占用过高等问题：

  • 设置适当的批处理大小： 批处理大小指在一次插入操作中插入多少行数据。如果批处理大小太小，插入操作的频率将很高，而如果批处理大小太大，可能会导致内存占用过高。通常，建议将批处理大小设置为1000-5000行，这将减少插入操作的频率并降低内存占用。

  • 采用适当的等待时间： 等待时间指在批处理操作之间等待的时间量。等待时间过短可能会导致内存占用过高，而等待时间过长则可能会延迟插入操作的速度。通常，建议将等待时间设置为几秒钟到几十秒钟之间，这将使操作变得平滑且避免出现内存占用过高等问题。

  • 可以考虑使用一些内存优化的技巧： 例如使用内存数据库或使用游标方式插入数据，以减少内存占用。

总的来说，选择适当的批处理大小和等待时间可以帮助您平稳地进行插入操作，避免出现内存占用过高等问题。

2.索引: 在大量数据插入前暂时去掉索引，最后再打上，这样可以大大减少写入时候的更新索引的时间。

3.数据库连接池： 使用数据库连接池可以减少数据库连接建立和关闭的开销，提高性能。在没有使用数据库连接池的情况，记得在finally中关闭相关连接。

4.数据库参数调整： 增加MySQL数据库缓冲区大小、配置高性能的磁盘和I/O等。

最后说一句(求关注!别白嫖！)

如果这篇文章对您有所帮助，或者有所启发的话，求一键三连：点赞、转发、在看。

关注公众号：woniuxgg，在公众号中回复：笔记  就可以获得蜗牛为你精心准备的java实战语雀笔记，回复面试、开发手册、有超赞的粉丝福利！

这篇关于13 秒插入 30 万条数据，这才是 Java 批量插入正确的姿势！的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


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

相关文章

JVM 的类初始化机制

JVM 的类初始化机制

前言 当你在 Java 程序中new对象时，有没有考虑过 JVM 是如何把静态的字节码（byte code）转化为运行时对象的呢，这个问题看似简单，但清楚的同学相信也不会太多，这篇文章首先介绍 JVM 类初始化的机制，然后给出几个易出错的实例来分析，帮助大家更好理解这个知识点。 JVM 将字节码转化为运行时对象分为三个阶段，分别是：loading 、Linking、initialization
阅读更多...
Spring Security 基于表达式的权限控制

Spring Security 基于表达式的权限控制

前言 spring security 3.0已经可以使用spring el表达式来控制授权，允许在表达式中使用复杂的布尔逻辑来控制访问的权限。 常见的表达式 Spring Security可用表达式对象的基类是SecurityExpressionRoot。 表达式描述hasRole([role])用户拥有制定的角色时返回true （Spring security默认会带有ROLE_前缀）,去
阅读更多...
浅析Spring Security认证过程

浅析Spring Security认证过程

类图 为了方便理解Spring Security认证流程，特意画了如下的类图，包含相关的核心认证类 概述 核心验证器 AuthenticationManager 该对象提供了认证方法的入口，接收一个Authentiaton对象作为参数; public interface AuthenticationManager {Authentication authenticate(Authenti
阅读更多...
Spring Security--Architecture Overview

Spring Security--Architecture Overview

1 核心组件 这一节主要介绍一些在Spring Security中常见且核心的Java类，它们之间的依赖，构建起了整个框架。想要理解整个架构，最起码得对这些类眼熟。 1.1 SecurityContextHolder SecurityContextHolder用于存储安全上下文（security context）的信息。当前操作的用户是谁，该用户是否已经被认证，他拥有哪些角色权限…这些都被保
阅读更多...
Spring Security基于数据库验证流程详解

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

Spring Security 校验流程图 相关解释说明（认真看哦） AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证，则会调用 #attemptAuthentica
阅读更多...
Spring Security 从入门到进阶系列教程

Spring Security 从入门到进阶系列教程

Spring Security 入门系列 《保护 Web 应用的安全》 《Spring-Security-入门（一）：登录与退出》 《Spring-Security-入门（二）：基于数据库验证》 《Spring-Security-入门（三）：密码加密》 《Spring-Security-入门（四）：自定义-Filter》 《Spring-Security-入门（五）：在 Sprin
阅读更多...
Java架构师知识体认识

Java架构师知识体认识

源码分析 常用设计模式 Proxy代理模式Factory工厂模式Singleton单例模式Delegate委派模式Strategy策略模式Prototype原型模式Template模板模式 Spring5 beans 接口实例化代理Bean操作 Context Ioc容器设计原理及高级特性Aop设计原理Factorybean与Beanfactory Transaction 声明式事物
阅读更多...
大模型研发全揭秘：客服工单数据标注的完整攻略

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

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

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

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

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

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

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2