Fastest Ways to Insert Massive Data into MySQL with SpringBoot & MyBatis-Plus

1. Introduction

While testing MySQL data insertion, inserting just 10,000 rows took nearly 47 seconds, prompting a systematic exploration of faster methods. Four common approaches were evaluated: single‑row for‑loop, concatenated SQL, batch insertion via saveBatch(), and loop insertion with batch mode enabled.

2. Preparation

Test environment : SpringBoot project, MyBatis‑Plus, MySQL 8.0.24, JDK 13.

Prerequisite : SpringBoot project already configured with MyBatis‑Plus and an IService implementation for batch operations.

Dependencies (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>
  <!-- MySQL driver -->
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
  </dependency>
  <!-- Lombok -->
  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
  </dependency>
</dependencies>

application.yml (key part: enable batch mode) :

server:
  port: 8080
spring:
  datasource:
    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

Entity class (Student) :

@Data
@TableName("student")
public class Student {
    @TableId(type = IdType.AUTO)
    private int id;
    private String name;
    private int age;
    private String addr;
    @TableField("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;
    }
}

Database table student (no indexes):

3. Test Execution

Four insertion methods were benchmarked with 50,000 rows, clearing the table before each run.

1) Single‑row for‑loop insertion

@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));
}

Results: ~174‑190 seconds (≈3 minutes). The overhead of acquiring and releasing a connection for each row dominates the cost.

2) Concatenated SQL batch

All rows are combined into a single INSERT ... VALUES (...),(...) statement.

@GetMapping("/sql")
public void sql() {
    ArrayList<Student> list = new ArrayList<>();
    long start = System.currentTimeMillis();
    for (int i = 0; i < 50000; i++) {
        list.add(new Student("李毅" + i, 24, "张家界市" + i, i + "号"));
    }
    studentMapper.insertSplice(list);
    long end = System.currentTimeMillis();
    System.out.println("插入数据消耗时间：" + (end - start));
}

@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);

Results: ~2.6‑3.2 seconds. Fewer round‑trips to the server dramatically improve speed, though very large statements may cause memory pressure.

3) MyBatis‑Plus saveBatch()

Uses the IService batch method with a default batchSize = 1000, which internally splits the list and executes batches.

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

Results: ~2.3‑2.9 seconds. Performance gains come from batch size control, but the underlying executeBatch() still sends statements one by one.

4) Loop insertion with batch mode enabled

Opens a MyBatis SqlSession in ExecutorType.BATCH and disables auto‑commit, committing once after the loop.

@GetMapping("/forSaveBatch")
public void forSaveBatch() {
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    long start = System.currentTimeMillis();
    for (int i = 0; i < 50000; i++) {
        Student s = new Student("李毅" + i, 24, "张家界市" + i, i + "号");
        mapper.insertStudent(s);
    }
    sqlSession.commit();
    sqlSession.close();
    long end = System.currentTimeMillis();
    System.out.println("总耗时： " + (end - start));
}

Results: ~1.4‑1.9 seconds, the fastest among all tested methods because the same session reuses the connection and commits only once.

Note: All tests used 50,000 rows and cleared the table before each run; actual performance may vary with environment and data size.

4. Conclusion

The experiments show that connection acquisition, resource release, and transaction handling dominate insertion cost. Enabling batch mode via the JDBC URL parameter rewriteBatchedStatements=true is essential for achieving high throughput. Among the four methods, the loop with explicit batch mode (single session, manual commit) delivered the best performance, making it the recommended approach for large‑scale MySQL inserts.