高效方案:30万条数据插入 MySQL 仅需13秒

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 高效方案:30万条数据插入 MySQL 仅需13秒

article/details/129665307


本文主要讲述通过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='用户信息表';

话不多说,开整!

基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

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

User实体

/**
 * <p>用户实体</p>
 *
 * @Author zjq
 */
@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>

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

不分批次直接梭哈

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占比飙升,一直处于高位。

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

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

two thousand year later …

控制台输出如下:

总共执行了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 数据库,并执行批处理操作插入数据。具体实现步骤如下:

  • 获取数据库连接。
  • 创建 Statement 对象。
  • 定义 SQL 语句,使用 PreparedStatement 对象预编译 SQL 语句并设置参数。
  • 执行批处理操作。
  • 处理剩余的数据。
  • 关闭 Statement 和 Connection 对象。

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

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

总结

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

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

  • 在循环插入时带有适当的等待时间和批处理大小,从而避免内存占用过高等问题:
  • 设置适当的批处理大小:批处理大小指在一次插入操作中插入多少行数据。如果批处理大小太小,插入操作的频率将很高,而如果批处理大小太大,可能会导致内存占用过高。通常,建议将批处理大小设置为1000-5000行,这将减少插入操作的频率并降低内存占用。
  • 采用适当的等待时间:等待时间指在批处理操作之间等待的时间量。等待时间过短可能会导致内存占用过高,而等待时间过长则可能会延迟插入操作的速度。通常,建议将等待时间设置为几秒钟到几十秒钟之间,这将使操作变得平滑且避免出现内存占用过高等问题。
  • 可以考虑使用一些内存优化的技巧,例如使用内存数据库或使用游标方式插入数据,以减少内存占用。
  • 总的来说,选择适当的批处理大小和等待时间可以帮助您平稳地进行插入操作,避免出现内存占用过高等问题。

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

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

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


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
29天前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
1月前
|
SQL 关系型数据库 MySQL
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。
|
1月前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
139 6
|
1月前
|
SQL 关系型数据库 MySQL
mysql分页读取数据重复问题
在服务端开发中,与MySQL数据库进行数据交互时,常因数据量大、网络延迟等因素需分页读取数据。文章介绍了使用`limit`和`offset`参数实现分页的方法,并针对分页过程中可能出现的数据重复问题进行了详细分析,提出了利用时间戳或确保排序规则绝对性等解决方案。
|
2月前
|
关系型数据库 MySQL 数据库
GBase 数据库如何像MYSQL一样存放多行数据
GBase 数据库如何像MYSQL一样存放多行数据
|
2月前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
78 14
|
4天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
54 0
|
1月前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
61 3
|
1月前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
78 3
|
1月前
|
SQL 关系型数据库 MySQL
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE &#39;log_%&#39;;`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
94 2