MYSQL数据库批量新增十万条数据

irresistible handsom

于 2025-03-28 15:44:14 发布

阅读量658

点赞数 21

文章标签：数据库 mysql spring boot mybatis

本文链接：https://blog.youkuaiyun.com/sdcet255/article/details/146600918

版权

本篇文章基于SpringBoot项目结合MyBatisPlus向MYSQL数据库批量新增十万条数据的几种方案，研究并讨论这几种方案的优缺点和执行效率。

方案一：循环十万次，逐条插入：

循环插入十万条数据，每一条数据单独插入，数据库连接每次执行一条SQL语句，一共向数据库提交十万次网络请求，十万次数据库连接，十万次数据库IO操作。

StudentDTO studentDTO = new StudentDTO();
long B = System.currentTimeMillis();
for (int i=0; i<=100000; i++) {
    studentDTO.setId("1000"+i);
    studentDTO.setName("user_"+i);
    studentDTO.setAge(LocalDateTime.now());
    studentDTO.setSex(i%2==0? "男" : "女");
    this.save(studentDTO);
}
long E = System.currentTimeMillis();
System.out.println("耗时：" + (E-B));

耗时125159毫秒约等于120秒，十万条数据耗费两分钟的时间。

优点：简单

缺点：效率非常差

方案二：基于MybatisPlus分批次插入：

插入十万条数据，分批次插入，每批1000条，共100次（向数据库传递数据包有上限大小限制，过大传输不过去，则设置每批1000）。

JDBC 预编译List集合，编译成List.size条SQL语句，执行save时一次性提交到数据库从而数据库一次执行大批量SQL语句。

共发送了100次数据库连接网络请求，相较于上一个方案性能得到了细微的提高。

List<StudentDTO> studentsList = new ArrayList<>(1000);
long B = System.currentTimeMillis();
for (int i=0; i<=100000; i++) {
    StudentDTO studentDTO = new StudentDTO();
    studentDTO.setId("1000"+i);
    studentDTO.setName("user_"+i);
    studentDTO.setAge(LocalDateTime.now());
    studentDTO.setSex(i%2==0? "男" : "女");
    studentsList.add(studentDTO);
    if (i % 1000 == 0) {
        this.saveBatch(studentsList);
        studentsList.clear();
    }
}
    long E = System.currentTimeMillis();
    System.out.println("耗时：" + (E-B));

耗时：102264毫秒约等于102秒，将近两分钟的时间

优点：效率得到了细微的提高

缺点：每一批次的1000条SQL是单独执行的，效率可以进一步得到提高

问题：虽然大幅度减少了数据库的连接次数，但是每次1000条数据共有1000条SQL语句，数据库每条SQL语句都是单独执行的，有没有什么办法让这1000条insert语句合并成1条insert语句呢？

方案三：分批次插入的升级版：

在方案二中我们提出了如何将每批次的1000条SQL语句合并成一条SQL语句的问题，我们只需在yml配置文件的数据源URL添加 rewriteBatchedStatements=true 参数，这样将JDBC批处理的SQL语句全都拼接成一整条执行，而不是多条SQL语句，JDBC只执行一条SQL语句即可完成批处理操作。

Java代码还是方案二的通过List存储每批次插入的数据集合，调用MybatisPlus的IService的saveBatch方法。

耗时6928毫秒约等于7秒，十万条SQL语句执行效率得到的质的飞跃，大幅度的提高。

优点：效率高

缺点：无缺点

方案四：分批次插入，基于Mapper.xml的ForEach：

Java代码还是基于方案二的代码，不同的是mapper接口调用不基于IService的saveBatch方法，而是基于Mybatis手动定义Mapper接口，手动书写mapper.xml文件，通过<foreach>将1000条SQL语句拼接成一条SQL执行。

<insert id="saveBatchData" parameterType="java.util.List">
    INSERT INTO STUDENT(ID, NAME, AGE, SEX)
    VALUES
    <foreach collection="studentsList" item="student" separator=",">
        (#{student.id},#{student.name},#{student.age},#{student.sex})
    </foreach>
</insert>