Boosting MyBatis-Plus Batch Insert Performance by 2000% with rewriteBatchedStatements and Pre‑Generated IDs

1. Introduction

In high‑concurrency, large‑data Java applications, batch inserting with MyBatis‑Plus often fails to meet performance expectations. The article reveals how configuring rewriteBatchedStatements=true and pre‑generating IDs can boost batch insert performance by 2000%.

2. Background: Performance Challenges of Batch Inserts

2.1 Scenario Description

Typical use cases such as exam systems, order processing, and log storage require inserting massive amounts of data across multiple related tables (exam, question, option).

2.2 Bottlenecks

Row‑by‑row insertion incurs high network and parsing overhead.

Foreign‑key handling forces extra queries to obtain generated IDs.

Default saveBatch provides limited speed gains.

3. Exploring MyBatis‑Plus saveBatch

3.1 Method Overview

boolean saveBatch(Collection<T> entityList); boolean saveBatch(Collection<T> entityList, int batchSize);

entityList : collection of entities to insert.

batchSize (optional): size of each batch; default inserts all at once.

3.2 Common Scenarios

Bulk data insertion.

Improving write throughput.

Preventing memory overflow by splitting into batches.

3.3 Limitations of the Default Implementation

Does not merge multiple SQL statements.

Limited performance improvement.

Primary‑key generation may require extra round‑trips.

Complex foreign‑key handling.

Lacks flexibility for conditional inserts or upserts.

4. Deep Dive into rewriteBatchedStatements=true

4.1 JDBC Batch Mechanism

JDBC batches allow sending many SQL statements in a single round‑trip, reducing client‑server interactions.

// Create PreparedStatement
PreparedStatement pstmt = conn.prepareStatement(sql);
for (Data data : dataList) {
    // Add each statement to the batch
    pstmt.addBatch();
}
// Execute the batch
pstmt.executeBatch();

4.2 Default MySQL JDBC Behavior

Without rewriting, the driver sends each statement individually.

This creates a performance bottleneck due to frequent network and parsing overhead.

4.3 Effect of Enabling Rewrite

Multiple identical statements are merged into a single SQL batch.

Network round‑trips are reduced.

MySQL parses the statement only once, improving execution efficiency.

Overall resource consumption is lower despite a larger SQL string.

SQL before enabling rewrite:

INSERT INTO question (exam_id, content) VALUES (?, ?);
INSERT INTO question (exam_id, content) VALUES (?, ?);
INSERT INTO question (exam_id, content) VALUES (?, ?);

SQL after enabling rewrite:

INSERT INTO question (exam_id, content) VALUES (?, ?), (?, ?), (?, ?);

5. Pre‑Generating IDs to Solve Foreign‑Key Issues

5.1 Problem Analysis

Options must reference the primary key of their question; waiting for the database to generate IDs prevents true batch processing.

5.2 Solution

Use a custom ID generator (e.g., zzidc ) that creates globally unique, locally generated IDs, supporting bulk generation.

5.3 Implementation

5.3.1 Adding zzidc Dependency

<dependency>
    <groupId>com.bj58.zhuanzhuan.idc</groupId>
    <artifactId>contract</artifactId>
    <version>${com.bj58.zhuanzhuan.idc.version}</version>
</dependency>

5.3.2 Business Logic Example

public Boolean createExamPaper(HeroExamRequest
request) throws BusinessException {
    // Build question data
    Question question = new Question();
    question.setId(questionId);
    question.setExamId(examId);
    // ...
    // Build option data
    Option option = new Option();
    option.setQuestionId(questionId);
    // ...
}

6. Comprehensive Optimization Practice (2000% Speedup)

6.1 Enabling rewriteBatchedStatements=true

6.1.1 Connection String Modification

Append rewriteBatchedStatements=true to the JDBC URL, e.g., jdbc:mysql://localhost:3306/db_name?rewriteBatchedStatements=true .

6.1.2 Precautions

Use MySQL driver version 5.1.13 or newer.

Separate multiple parameters with '&'.

6.2 Full Code Example

@Service
public class ExamServiceImpl implements ExamService {

    @Autowired
    private ExamMapper examMapper;
    @Autowired
    private QuestionService questionService;
    @Autowired
    private OptionService optionService;

    private static final int BATCH_SIZE = 2000;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void createExam(Exam exam, int questionCount, int optionCountPerQuestion) {
        long examId = zzidc.nextId();
        exam.setId(examId);
        examMapper.insert(exam);

        List
questionList = new ArrayList<>();
        List
allOptionList = new ArrayList<>();

        for (int i = 0; i < questionCount; i++) {
            long questionId = zzidc.nextId();
            Question question = new Question();
            question.setId(questionId);
            question.setExamId(examId);
            question.setContent("题目内容" + i);
            questionList.add(question);

            for (int j = 0; j < optionCountPerQuestion; j++) {
                Option option = new Option();
                option.setQuestionId(questionId);
                option.setContent("选项内容" + j);
                allOptionList.add(option);
            }
        }

        questionService.saveBatch(questionList, BATCH_SIZE);
        optionService.saveBatch(allOptionList, BATCH_SIZE);
    }
}

6.3 Performance Test

6.3.1 Test Data

Insert 100 questions, each with 3 options.

6.3.2 Test Scenarios

Unoptimized: row‑by‑row inserts.

Only saveBatch (no rewrite, no pre‑generated IDs).

Comprehensive optimization (rewrite + pre‑generated IDs + batch).

6.3.3 Results

Scenario

Time (ms)

Improvement

Unoptimized

4023

-

Only saveBatch

2744

↑ 30%

Comprehensive

149

↑ 2700%

6.3.4 Analysis

Row‑by‑row inserts suffer from high network latency.

Simple saveBatch reduces round‑trips but does not exploit driver rewrite.

Full optimization merges SQL, cuts network traffic, and eliminates extra ID queries, delivering massive speed gains.

7. Multithreaded Concurrent Insertion

7.1 Issues

SqlSession is not thread‑safe.

Transaction isolation per thread is required.

Excessive threads can exhaust the connection pool.

7.2 Correct Multithreading Approach

7.2.1 Using @Async

@Configuration
@EnableAsync
public class AsyncConfig {
    @Bean(name = "taskExecutor")
    public Executor taskExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(4);
        executor.setMaxPoolSize(8);
        executor.setQueueCapacity(100);
        executor.setThreadNamePrefix("AsyncExecutor-");
        executor.initialize();
        return executor;
    }
}

7.2.2 Async Batch Method

@Service
public class QuestionServiceImpl implements QuestionService {

    @Autowired
    private QuestionMapper questionMapper;

    @Override
    @Async("taskExecutor")
    @Transactional(rollbackFor = Exception.class)
    public CompletableFuture
saveBatchAsync(List
questionList) {
        saveBatch(questionList, BATCH_SIZE);
        return CompletableFuture.completedFuture(null);
    }
}

7.2.3 Invoking Async Methods

public void createExam(Exam exam, int questionCount, int optionCountPerQuestion) {
    // ... data preparation omitted ...
    List
> questionBatches = Lists.partition(questionList, BATCH_SIZE);
    List
> optionBatches = Lists.partition(allOptionList, BATCH_SIZE);
    List
> futures = new ArrayList<>();
    for (List
batch : questionBatches) {
        futures.add(questionService.saveBatchAsync(batch));
    }
    for (List
batch : optionBatches) {
        futures.add(optionService.saveBatchAsync(batch));
    }
    CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join();
}

7.2.4 Precautions

Ensure each async method has its own SqlSession and transaction.

Configure thread‑pool size according to CPU and DB capacity.

Monitor and handle CompletableFuture results.

8. Database‑Level Optimizations

8.1 Adjust Connection Pool

Increase pool size for high concurrency.

Set appropriate min/max values to avoid exhaustion or waste.

8.2 Set MyBatis Executor to BATCH

<configuration>
  <settings>
    <setting name="defaultExecutorType" value="BATCH"/>
  </settings>
</configuration>

Note: Using BATCH executor requires manual sqlSession.flushStatements() handling.

9. Monitoring and Tuning

9.1 Monitor Async Tasks

Return CompletableFuture to await completion.

Log start/end timestamps.

@Async("taskExecutor")
@Transactional(rollbackFor = Exception.class)
public CompletableFuture
saveBatchAsync(List
questionList) {
    long start = System.currentTimeMillis();
    saveBatch(questionList, BATCH_SIZE);
    long end = System.currentTimeMillis();
    logger.info("Inserted batch of {} questions in {} ms", questionList.size(), (end - start));
    return CompletableFuture.completedFuture(null);
}

9.2 Tune Thread‑Pool Parameters

Adjust core and max pool sizes based on CPU cores and DB load.

Set queue capacity to prevent OOM.

10. Best‑Practice Summary

10.1 Comprehensive Optimization Strategy

Enable rewriteBatchedStatements=true to let the driver merge SQL.

Pre‑generate IDs to break foreign‑key constraints.

Use asynchronous methods for safe multithreaded batch inserts.

Resize connection and thread pools appropriately.

Continuously monitor async tasks and DB performance.

10.2 Caveats

Ensure thread safety of all resources.

Maintain transaction isolation per async task.

Avoid over‑provisioning threads that could exhaust resources.

Conclusion

Understanding the impact of rewriteBatchedStatements=true , combined with pre‑generated IDs, proper multithreading, and database tuning, can increase MyBatis‑Plus batch insert performance by roughly 2000%, a technique applicable to many high‑throughput data‑processing scenarios.