Speeding Up 100k MySQL Inserts: From 5 Minutes to 3 Seconds in Java

Problem Statement

A data‑migration task required inserting 100,000 rows from a legacy system into a MySQL table. The naïve implementation used a simple for (User user : userList) { userMapper.insert(user); } loop, which took about five minutes (≈300 s) because each row caused a separate network round‑trip, SQL parsing, and transaction commit.

Initial Implementation (≈5 minutes)

// Loop insert (slowest)
for (User user : userList) {
    userMapper.insert(user);
}

Assuming ~3 ms per insert, 100 k rows ≈ 300 s.

First Optimization – Batch SQL (≈30 seconds)

Use a single INSERT … VALUES statement that inserts many rows at once via MyBatis. The list is split into batches of 1,000 records to keep the SQL size reasonable.

<!-- Mapper.xml -->
<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);
}

Runtime drops to ~30 seconds (≈10× faster).

Second Optimization – JDBC Batch Processing (≈8 seconds)

Enable MySQL driver option rewriteBatchedStatements=true so that multiple INSERT statements are merged into a single statement. Combine this with MyBatis’s batch executor.

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

Runtime drops to ~8 seconds (≈37× faster than the baseline).

Third Optimization – Multithreaded Parallelism (≈3 seconds)

Split the workload across multiple threads (typically matching the DB connection‑pool size). Each thread processes its own sub‑list using the batch‑executor method.

public void parallelBatchInsert(List<User> userList) {
    int threadCount = 4; // adjust to pool size
    int batchSize = userList.size() / threadCount;
    ExecutorService executor = Executors.newFixedThreadPool(threadCount);
    List<Future<?>> futures = new ArrayList<>();
    for (int i = 0; i < threadCount; i++) {
        int start = i * batchSize;
        int end = (i == threadCount - 1) ? userList.size() : (i + 1) * batchSize;
        List<User> subList = userList.subList(start, end);
        futures.add(executor.submit(() -> batchInsertWithExecutor(subList)));
    }
    for (Future<?> f : futures) {
        try { f.get(); } catch (Exception e) { throw new RuntimeException(e); }
    }
    executor.shutdown();
}

Runtime drops to ~3 seconds (≈100× faster than the original loop).

Performance Comparison

Loop insert: 300 seconds (baseline)

Batch SQL: 30 seconds (10× faster)

JDBC batch: 8 seconds (≈37× faster)

Multithreaded parallel: 3 seconds (≈100× faster)

Key Pitfalls

Too many rows per <foreach> can exceed max_allowed_packet. Split into 500‑1000 row batches. rewriteBatchedStatements has no effect if the URL parameter is missing, the executor type isn’t BATCH, or an old MySQL driver is used.

When rewriteBatchedStatements=true, auto‑generated keys may be lost; use MySQL driver 8.0.17+.

Loading all rows into memory can cause OOM. Use pagination (e.g., 10,000 rows per page) and batch‑insert each page.

Thread count must not exceed the DB connection‑pool size, and parallel inserts are unsuitable when strict transaction consistency is required.

Final High‑Performance Implementation

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

    /** High‑performance batch insert for ~100k rows (~3 seconds) */
    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();
        }
    }
}

Key Takeaways

Reduce network round‑trips by inserting many rows per SQL statement.

Enable JDBC batch mode ( rewriteBatchedStatements=true) together with MyBatis ExecutorType.BATCH so the driver merges statements.

Parallelize batch work across threads, respecting the DB connection‑pool size.

Watch for packet‑size limits, driver version compatibility, and memory consumption when loading large datasets.