MySQL批量插入数据的四种方案（性能测试对比）

落日映苍穹つ 2023-09-26 15:57 33阅读 0赞

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

![dce1c0824e2c6f733dc7c033cd9f33e4.jpeg][]

#### 一、前言 ####

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

#### 二、准备工作 ####

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

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

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

<dependencies>
     
      
      <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-web</artifactId>
      </dependency>
     
      
      <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
     
    #  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方法
     *   @author LBF
     *   @date 2022/3/18 16:06
     */
    @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表结构（注意：无索引）

![61c59a8ac9e01099d506b53ef181fe4a.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秒

![5acee0f31ed44fe24d605672bf047d56.png][]

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

![a1a3bf19ffa0c6b5e11f7f0b6b32d8dd.png][]

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

![9e1fcc7c00aed8800a5c4d465f4eb13e.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秒

![b9c967c914ff85634052794c497f465a.png][]

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

![fac4ee5261f543e644fc8ff4ad5782bf.png][]

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

![2e404f17bb404bef11ac48dbc7c9be62.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秒

![0dd2d2c4c5e92bbf04206483e8dbd5b1.png][]

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

![2c6b1b932d73222a835569d02ba306b3.png][]

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

![658361498a175a1c46da1cd65c5792ac.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秒（未开启批处理模式）

![35581705487d075a9df316d875107e30.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秒

![b6486c326bf1e4066b63b099dcb133f7.png][]

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

![0d72adeee42b658cfc897e2bde712d48.png][]

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

![a9b6f636c44311ef8fa4c2bcd4602edc.png][]

#### 四、总结 ####

本文记录个人学习MySQL插入大数据一些方案心得，可得知主要是在获取连接、关闭连接、释放资源和提交事务等方面较耗能，其中最需要注意是开启批处理模式，即URL地址的参数：`rewriteBatchedStatements = true`，否则也无法发挥作用。

对于测试方案的设定、对考虑不周、理解和编写错误的地方等情况，请多指出，共同学习！

[dce1c0824e2c6f733dc7c033cd9f33e4.jpeg]: https://img-blog.csdnimg.cn/img_convert/dce1c0824e2c6f733dc7c033cd9f33e4.jpeg
[61c59a8ac9e01099d506b53ef181fe4a.png]: https://img-blog.csdnimg.cn/img_convert/61c59a8ac9e01099d506b53ef181fe4a.png
[5acee0f31ed44fe24d605672bf047d56.png]: https://img-blog.csdnimg.cn/img_convert/5acee0f31ed44fe24d605672bf047d56.png
[a1a3bf19ffa0c6b5e11f7f0b6b32d8dd.png]: https://img-blog.csdnimg.cn/img_convert/a1a3bf19ffa0c6b5e11f7f0b6b32d8dd.png
[9e1fcc7c00aed8800a5c4d465f4eb13e.png]: https://img-blog.csdnimg.cn/img_convert/9e1fcc7c00aed8800a5c4d465f4eb13e.png
[b9c967c914ff85634052794c497f465a.png]: https://img-blog.csdnimg.cn/img_convert/b9c967c914ff85634052794c497f465a.png
[fac4ee5261f543e644fc8ff4ad5782bf.png]: https://img-blog.csdnimg.cn/img_convert/fac4ee5261f543e644fc8ff4ad5782bf.png
[2e404f17bb404bef11ac48dbc7c9be62.png]: https://img-blog.csdnimg.cn/img_convert/2e404f17bb404bef11ac48dbc7c9be62.png
[0dd2d2c4c5e92bbf04206483e8dbd5b1.png]: https://img-blog.csdnimg.cn/img_convert/0dd2d2c4c5e92bbf04206483e8dbd5b1.png
[2c6b1b932d73222a835569d02ba306b3.png]: https://img-blog.csdnimg.cn/img_convert/2c6b1b932d73222a835569d02ba306b3.png
[658361498a175a1c46da1cd65c5792ac.png]: https://img-blog.csdnimg.cn/img_convert/658361498a175a1c46da1cd65c5792ac.png
[35581705487d075a9df316d875107e30.png]: https://img-blog.csdnimg.cn/img_convert/35581705487d075a9df316d875107e30.png
[b6486c326bf1e4066b63b099dcb133f7.png]: https://img-blog.csdnimg.cn/img_convert/b6486c326bf1e4066b63b099dcb133f7.png
[0d72adeee42b658cfc897e2bde712d48.png]: https://img-blog.csdnimg.cn/img_convert/0d72adeee42b658cfc897e2bde712d48.png
[a9b6f636c44311ef8fa4c2bcd4602edc.png]: https://img-blog.csdnimg.cn/img_convert/a9b6f636c44311ef8fa4c2bcd4602edc.png