How to Reduce MyBatis Batch Insert from 5 Minutes to 3 Seconds? Three Key Optimizations

Initial implementation (5 minutes)

Iterating over 100,000 User objects and calling userMapper.insert(user) for each row triggers a network round‑trip, SQL parsing, and transaction commit per row. Assuming 3 ms per insert, the total time is 300 seconds (5 minutes).

// naive loop insertion (slowest)
for (User user : userList) {
    userMapper.insert(user);
}

First optimization – batch SQL (30 seconds)

Replace the loop with a single INSERT … VALUES statement that inserts many rows. MyBatis <foreach> generates the value list, and the list is split into batches of 1,000 rows to keep the SQL length reasonable.

<insert id="batchInsert">
    INSERT INTO user (name, age, email) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.age}, #{item.email})
    </foreach>
</insert>

int batchSize = 1000;
for (int i = 0; i < userList.size(); i += batchSize) {
    int end = Math.min(i + batchSize, userList.size());
    List<User> batch = userList.subList(i, end);
    userMapper.batchInsert(batch);
}

Time drops from 300 s to 30 s (≈10× speed‑up) because only one SQL statement is sent per batch.

Second optimization – JDBC batch (8 seconds)

Enable the MySQL driver parameter rewriteBatchedStatements=true and use MyBatis ExecutorType.BATCH. The driver rewrites multiple INSERT statements into a single multi‑row INSERT, and MyBatis buffers statements until flushStatements() is called.

jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

@Autowired
private SqlSessionFactory sqlSessionFactory;

public void batchInsertWithExecutor(List<User> userList) {
    try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int batchSize = 1000;
        for (int i = 0; i < userList.size(); i++) {
            mapper.insert(userList.get(i));
            if ((i + 1) % batchSize == 0) {
                sqlSession.flushStatements();
                sqlSession.clearCache();
            }
        }
        sqlSession.flushStatements();
        sqlSession.commit();
    }
}

Time improves to 8 seconds (≈37× faster than the baseline). The driver merges the batched statements into a single INSERT.

Third optimization – multithreaded parallelism (3 seconds)

Split the data across multiple threads (typically matching the DB connection‑pool size). Each thread runs the batch‑insert logic independently, and a CountDownLatch waits for all threads to finish.

public void parallelBatchInsert(List<User> userList) {
    int threadCount = 4; // adjust to pool size
    int batchSize = userList.size() / threadCount;
    ExecutorService executor = Executors.newFixedThreadPool(threadCount);
    CountDownLatch latch = new CountDownLatch(threadCount);
    for (int i = 0; i < threadCount; i++) {
        int start = i * batchSize;
        int end = (i == threadCount - 1) ? userList.size() : (i + 1) * batchSize;
        List<User> subList = new ArrayList<>(userList.subList(start, end));
        executor.submit(() -> {
            try {
                batchInsertWithExecutor(subList);
            } finally {
                latch.countDown();
            }
        });
    }
    latch.await();
    executor.shutdown();
}

Execution time falls to about 3 seconds (≈100× faster than the original) by utilizing CPU cores and the DB connection pool concurrently.

Important considerations

Do not set the thread count higher than the database connection‑pool size.

If transactional consistency across all rows is required, the parallel approach may be unsuitable.

Be aware of possible primary‑key conflicts when inserting in parallel.

Common pitfalls

SQL too long : Inserting too many rows per batch can exceed max_allowed_packet. Split batches to 500‑1000 rows.

rewriteBatchedStatements not effective : Verify the URL contains rewriteBatchedStatements=true, ensure ExecutorType.BATCH is used, and use a recent MySQL driver (≥ 8.0.17).

Auto‑increment key retrieval : With rewriteBatchedStatements=true, generated keys may be lost; upgrade the driver to 8.0.17+.

Out‑of‑memory : Loading all 100k rows at once can cause OOM. Use pagination (e.g., pageSize = 10000) and batch‑insert each page.

int pageSize = 10000;
int total = countTotal();
for (int i = 0; i < total; i += pageSize) {
    List<User> page = selectByPage(i, pageSize);
    batchInsertWithExecutor(page);
}

Final high‑performance implementation (~3 seconds for 100k rows)

@Service
public class BatchInsertService {
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    public void highPerformanceBatchInsert(List<User> userList) {
        if (userList == null || userList.isEmpty()) return;
        int threadCount = Math.min(4, Runtime.getRuntime().availableProcessors());
        int batchSize = (int) Math.ceil((double) userList.size() / threadCount);
        ExecutorService executor = Executors.newFixedThreadPool(threadCount);
        CountDownLatch latch = new CountDownLatch(threadCount);
        for (int i = 0; i < threadCount; i++) {
            int start = i * batchSize;
            int end = Math.min((i + 1) * batchSize, userList.size());
            if (start >= userList.size()) { latch.countDown(); continue; }
            List<User> subList = new ArrayList<>(userList.subList(start, end));
            executor.submit(() -> {
                try { doBatchInsert(subList); } finally { latch.countDown(); }
            });
        }
        try { latch.await(); } catch (InterruptedException e) { Thread.currentThread().interrupt(); }
        executor.shutdown();
    }

    private void doBatchInsert(List<User> userList) {
        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            for (int i = 0; i < userList.size(); i++) {
                mapper.insert(userList.get(i));
                if ((i + 1) % 1000 == 0) {
                    sqlSession.flushStatements();
                    sqlSession.clearCache();
                }
            }
            sqlSession.flushStatements();
            sqlSession.commit();
        }
    }
}

Core principle

Reduce network round‑trips, minimize transaction overhead, and exploit parallelism.