👉 这是一个或许对你有用的社群

🐱 一对一交流/面试小册/简历优化/求职解惑，欢迎加入「芋道快速开发平台」知识星球。下面是星球提供的部分资料： 

774977074f53ca60c9eaedc58b848ba0.gif

👉这是一个或许对你有用的开源项目

国产 Star 破 10w+ 的开源项目，前端包括管理后台 + 微信小程序，后端支持单体和微服务架构。

功能涵盖 RBAC 权限、SaaS 多租户、数据权限、商城、支付、工作流、大屏报表、微信公众号等等功能：

  • Boot 地址：https://gitee.com/zhijiantianya/ruoyi-vue-pro

  • Cloud 地址：https://gitee.com/zhijiantianya/yudao-cloud

  • 视频教程：https://doc.iocoder.cn

来源：blog.csdn.net/a18505947362
/article/details/123667215

8969e8b893f3463c2145ce6a6d8a6c0e.jpeg

简明：本文记录个人使用MySQL插入大数据总结较实用的方案，通过对常用插入大数据的4种方式进行测试，即for循环单条、拼接SQL、批量插入saveBatch()、循环 + 开启批处理模式，得出比较实用的方案心得。

一、前言

最近趁空闲之余，在对MySQL数据库进行插入数据测试，对于如何快速插入数据的操作无从下手，在仅1W数据量的情况下，竟花费接近47s，实在不忍直视！在不断摸索之后，整理出一些较实用的方案。

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

  • 项目地址：https://github.com/YunaiV/ruoyi-vue-pro

  • 视频教程：https://doc.iocoder.cn/video/

二、准备工作

测试环境：SpringBoot项目、MyBatis-Plus框架、MySQL8.0.24、JDK13

前提：SpringBoot项目集成MyBatis-Plus上述文章有配置过程，同时实现IService接口用于进行批量插入数据操作saveBatch()方法

1、Maven项目中pom.xml文件引入的相关依赖如下

<dependencies>

  <!-- SpringBoot Web模块依赖 -->
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>

  <!-- MyBatis-Plus 依赖 -->
  <dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.1</version>
  </dependency>

  <!-- 数据库连接驱动 -->
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
  </dependency>

  <!-- 使用注解，简化代码-->
  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
  </dependency>

</dependencies>

2、application.yml配置属性文件内容（重点：开启批处理模式）

server:
    端口号 
    port: 8080


> 基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能
>
> * 项目地址：<https://github.com/YunaiV/yudao-cloud>
> * 视频教程：<https://doc.iocoder.cn/video/>

#  MySQL连接配置信息（以下仅简单配置，更多设置可自行查看）
spring:
    datasource:
         连接地址（解决UTF-8中文乱码问题 + 时区校正）
                （rewriteBatchedStatements=true 开启批处理模式）
        url: jdbc:mysql://127.0.0.1:3306/bjpowernode?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
         用户名
        username: root
         密码
        password: xxx
         连接驱动名称
        driver-class-name: com.mysql.cj.jdbc.Driver

3、Entity实体类（测试）

/**
 *   Student 测试实体类
 *   
 *   @Data注解：引入Lombok依赖，可省略Setter、Getter方法
 */
@Data
@TableName(value = "student")
public class Student {
    
    /**  主键  type:自增 */
    @TableId(type = IdType.AUTO)
    private int id;

    /**  名字 */
    private String name;

    /**  年龄 */
    private int age;

    /**  地址 */
    private String addr;

    /**  地址号  @TableField：与表字段映射 */
    @TableField(value = "addr_num")
    private String addrNum;

    public Student(String name, int age, String addr, String addrNum) {
        this.name = name;
        this.age = age;
        this.addr = addr;
        this.addrNum = addrNum;
    }
}

4、数据库student表结构（注意：无索引）

4b9b97dba16bf9b31f4ce1ab47417164.png

三、测试工作

简明：完成准备工作后，即对for循环、拼接SQL语句、批量插入saveBatch()、循环插入+开启批处理模式，该4种插入数据的方式进行测试性能。

注意：测试数据量为5W、单次测试完清空数据表（确保不受旧数据影响）

（ 以下测试内容可能受测试配置环境、测试规范和数据量等诸多因素影响，读者可自行结合参考进行测试 ）

1、for循环插入（单条）（总耗时：177秒）

总结：测试平均时间约是177秒，实在是不忍直视（捂脸），因为利用for循环进行单条插入时，每次都是在获取连接(Connection)、释放连接和资源关闭等操作上，（如果数据量大的情况下）极其消耗资源，导致时间长。

@GetMapping("/for")
public void forSingle(){
    // 开始时间
    long startTime = System.currentTimeMillis();
    for (int i = 0; i < 50000; i++){
        Student student = new Student("李毅" + i,24,"张家界市" + i,i + "号");
        studentMapper.insert(student);
    }
    // 结束时间
    long endTime = System.currentTimeMillis();
    System.out.println("插入数据消耗时间：" + (endTime - startTime));
}

（1）第一次测试结果：190155 约等于 190秒

7518f9333a821bc34ee3159a2e520242.png

（2）第二次测试结果：175926 约等于 176秒（服务未重启）

2136eeb737bb443ceb04efdf1c01c72a.png

（3）第三次测试结果：174726 约等于 174秒（服务重启）

26b089b90b87e934ed30442a0d5dbd83.png

2、拼接SQL语句（总耗时：2.9秒）

简明：拼接格式：insert into student(xxxx) value(xxxx),(xxxx),(xxxxx).......

总结：拼接结果就是将所有的数据集成在一条SQL语句的value值上，其由于提交到服务器上的insert语句少了，网络负载少了，性能也就提上去。但是当数据量上去后，可能会出现内存溢出、解析SQL语句耗时等情况，但与第一点相比，提高了极大的性能。

@GetMapping("/sql")
public void sql(){
    ArrayList<Student> arrayList = new ArrayList<>();
    long startTime = System.currentTimeMillis();
    for (int i = 0; i < 50000; i++){
        Student student = new Student("李毅" + i,24,"张家界市" + i,i + "号");
        arrayList.add(student);
    }
    studentMapper.insertSplice(arrayList);
    long endTime = System.currentTimeMillis();
    System.out.println("插入数据消耗时间：" + (endTime - startTime));
}
// 使用@Insert注解插入：此处为简便，不写Mapper.xml文件
@Insert("<script>" +
        "insert into student (name,age,addr,addr_num) values " +
        "<foreach collection='studentList' item='item' separator=','> " +
        "(#{item.name},{item.age},{item.addr},{item.addrNum}) " +
        "</foreach> " +
        "</script>")
int insertSplice(@Param("studentList") List<Student> studentList);

（1）第一次测试结果：3218 约等于 3.2秒

85f974961970959bdaa8261f2bc34cb4.png

（2）第二次测试结果：2592 约等于 2.6秒（服务未重启）

e9d1eb95d208d0ac776b0412e06c5a88.png

（3）第三次测试结果：3082 约等于 3.1秒（服务重启）

762a1884fd923bfe5f55bc253bf9b80e.png

3、批量插入saveBatch（总耗时：2.7秒）

简明：使用MyBatis-Plus实现IService接口中批处理saveBatch()方法，对底层源码进行查看时，可发现其实是for循环插入，但是与第一点相比，为什么性能上提高了呢？因为利用分片处理（batchSize = 1000） + 分批提交事务的操作，从而提高性能，并非在Connection上消耗性能。

@GetMapping("/saveBatch1")
public void saveBatch1(){
    ArrayList<Student> arrayList = new ArrayList<>();
    long startTime = System.currentTimeMillis();
    // 模拟数据
    for (int i = 0; i < 50000; i++){
        Student student = new Student("李毅" + i,24,"张家界市" + i,i + "号");
        arrayList.add(student);
    }
    // 批量插入
    studentService.saveBatch(arrayList);
    long endTime = System.currentTimeMillis();
    System.out.println("插入数据消耗时间：" + (endTime - startTime));
}

（1）第一次测试结果：2864 约等于 2.9秒

e9a7be256d7f9010a5a9ee7debd7830d.png

（2）第二次测试结果：2302 约等于 2.3秒（服务未重启）

b26b5b2c2dd026a32fffb3ba35d0a528.png

（3）第三次测试结果：2893 约等于 2.9秒（服务重启）

e4e4c70d6e7c2bb4b7f24c070045ff3c.png

重点注意：MySQL JDBC驱动默认情况下忽略saveBatch()方法中的executeBatch()语句，将需要批量处理的一组SQL语句进行拆散，执行时一条一条给MySQL数据库，造成实际上是分片插入，即与单条插入方式相比，有提高，但是性能未能得到实质性的提高。

测试：数据库连接URL地址缺少 rewriteBatchedStatements = true 参数情况

#  MySQL连接配置信息
spring:
    datasource:
         连接地址（未开启批处理模式）
        url: jdbc:mysql://127.0.0.1:3306/bjpowernode?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
         用户名
        username: root
         密码
        password: xxx
         连接驱动名称
        driver-class-name: com.mysql.cj.jdbc.Driver

测试结果：10541 约等于 10.5秒（未开启批处理模式）

694a31e797a73f3e878f0f45436d8171.png

4、循环插入 + 开启批处理模式（总耗时：1.7秒）（重点：一次性提交）

简明：开启批处理，关闭自动提交事务，共用同一个SqlSession之后，for循环单条插入的性能得到实质性的提高；由于同一个SqlSession省去对资源相关操作的耗能、减少对事务处理的时间等，从而极大程度上提高执行效率。（目前个人觉得最优方案）

@GetMapping("/forSaveBatch")
public void forSaveBatch(){
    //  开启批量处理模式 BATCH 、关闭自动提交事务 false
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
    //  反射获取，获取Mapper
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
    long startTime = System.currentTimeMillis();
    for (int i = 0 ; i < 50000 ; i++){
        Student student = new Student("李毅" + i,24,"张家界市" + i,i + "号");
        studentMapper.insertStudent(student);
    }
    // 一次性提交事务
    sqlSession.commit();
    // 关闭资源
    sqlSession.close();
    long endTime = System.currentTimeMillis();
    System.out.println("总耗时： " + (endTime - startTime));
}

（1）第一次测试结果：1831 约等于 1.8秒

59d1a06e70c63098aba9bc5f063ba301.png

（2）第二次测试结果：1382 约等于 1.4秒（服务未重启）

d797338892801d8b96189235a01fe189.png

（3）第三次测试结果：1883 约等于 1.9秒（服务重启）

b75079aad0f4ccf72dac0eb42eb1d3e6.png

四、总结

本文记录个人学习MySQL插入大数据一些方案心得，可得知主要是在获取连接、关闭连接、释放资源和提交事务等方面较耗能，其中最需要注意是开启批处理模式，即URL地址的参数：rewriteBatchedStatements = true，否则也无法发挥作用。对于测试方案的设定、对考虑不周、理解和编写错误的地方等情况，请多指出，共同学习！

欢迎加入我的知识星球，全面提升技术能力。

👉 加入方式，“长按”或“扫描”下方二维码噢：

3270327474b065688a499b9c7b461e37.png

星球的内容包括：项目实战、面试招聘、源码解析、学习路线。

394309964a83e53a93d2a0c17e82f83e.png

47b36b0a74d85af90a83083f7840b20c.png3ae3fa7daa24309e532e830706f6d4d6.png811d624b736926579b643a1d4998847a.pngc5196f84857fea717dc93cf3e9e48884.png

文章有帮助的话，在看，转发吧。
谢谢支持哟 (*^__^*）
# spring boot # 后端 # java # spring
Logo
数据库技术专区

数据库是今天社会发展不可缺少的重要技术，它可以把大量的信息进行有序的存储和管理，为企业的数据处理提供了强大的保障。

更多推荐

数据库中怎么删除表中数据？

drop，truncate，delete

avatar 数据库技术专区

mysql 备份数据库

备份mysql数据库，可通过以下命令的方式：1、备份数据库表结构及数据导出的脚本中，只包含表结构和表中数据，不包含创建数据库语句，即不包含：CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;mysqldump -hlocalhost -uroot -p123456 test

avatar 数据库技术专区
cover

DBeaver连接（人大金仓/达梦）数据库 ，达梦、人大金仓数据库可视化工具

avatar 数据库技术专区