Insert 300K Records in 13 Seconds: The Right Way to Batch Insert

Table Schema

The test uses a simple t_user table with columns id (auto-increment primary key), username (varchar(64)), and age (int).

Entity, Mapper, and Configuration

User Entity

@Data
public class User {
    private int id;
    private String username;
    private int age;
}

Mapper Interface

public interface UserMapper {
    /**
     * Batch insert users
     * @param userList
     */
    void batchInsertUser(@Param("list") List<User> userList);
}

Mapper XML (Batch Insert)

<insert id="batchInsertUser" parameterType="java.util.List">
insert into t_user(username,age) values
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.username},
        #{item.age}
        )
    </foreach>
</insert>

JDBC Properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root

MyBatis Config (sqlMapConfig.xml)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties"/>
    <typeAliases>
        <typeAlias type="com.zjq.domain.User" alias="user"/>
    </typeAliases>
    <environments default="developement">
        <environment id="developement">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/zjq/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

Attempt 1: All-at-Once Batch Insert (No Batching)

Inserting all 300,000 records in a single MyBatis batchInsertUser call fails with:

Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (27759038 > 4194304). You can change this value on the server by setting the max_allowed_packet variable.

The packet size (≈27 MB) exceeds the default max_allowed_packet (4 MB). Increasing this parameter is not viable for such a large payload.

Attempt 2: Loop Insert One by One

Code iterates 300,000 times, creating a User object and calling session.insert("insertUser", user) followed by session.commit() each iteration.

Result: Disk I/O spikes and stays high. Execution takes 14,909,367 ms (≈4 hours 8 minutes) . Too slow for production.

Attempt 3: MyBatis Batched Insert with Batching

Strategy: accumulate 1,000 records in a list, call session.insert("batchInsertUser", userList), commit, clear list, and optionally sleep.

Version A: Batch Size 1000, Wait 10 Seconds Between Batches

int waitTime = 10;
for (int i = 1; i <= 300000; i++) {
    userList.add(user);
    if (i % 1000 == 0) {
        session.insert("batchInsertUser", userList);
        session.commit();
        userList.clear();
        Thread.sleep(waitTime * 1000);
    }
}
// insert remaining
if (!CollectionUtils.isEmpty(userList)) {
    session.insert("batchInsertUser", userList);
    session.commit();
}

Result: ≈50 minutes , mostly spent waiting.

Version B: Batch Size 1000, No Wait

Same code but without Thread.sleep.

Result: 24 seconds . CPU and disk usage spike briefly.

Version C: Batch Size 5000, No Wait

Change condition to i % 5000 == 0.

Result: 13 seconds . Best performance observed.

Attempt 4: JDBC Batched Insert

Uses PreparedStatement with addBatch() and executeBatch() every 1,000 records (note: article text says 10,000 but code uses 1,000). Auto-commit disabled, manual commit after each batch.

connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement(
    "INSERT INTO t_user (username, age) VALUES (?, ?)");
for (int i = 1; i <= 300000; i++) {
    ps.setString(1, "共饮一杯无 " + i);
    ps.setInt(2, random.nextInt(100));
    ps.addBatch();
    if (i % 1000 == 0) {
        ps.executeBatch();
        connection.commit();
    }
}
ps.executeBatch();
connection.commit();

Performance comparable to MyBatis batched approach (exact time not shown, but described as fast).

Summary of Optimization Strategies

The author recommends combining the following:

Batch Processing : Use PreparedStatement.addBatch() / executeBatch() or MyBatis <foreach> to reduce network round-trips and statement parsing overhead. Choose batch size 1,000–5,000 rows; too small increases frequency, too large risks memory pressure. Optional short sleeps between batches can smooth resource usage.

Index Management : Drop indexes before bulk load, recreate after. Avoids per-row index maintenance cost.

Connection Pooling : Reuse connections to eliminate connect/close overhead. Always close resources in finally blocks if not pooled.

Database Parameter Tuning : Increase MySQL buffer sizes (e.g., innodb_buffer_pool_size), configure high-performance disks and I/O subsystems, adjust max_allowed_packet if needed.