Comprehensive Guide to Fastest Batch Update Techniques in MyBatis

1. Overview

Batch updates are a common performance bottleneck in application development. Different implementations vary significantly in execution speed, maintainability, and database compatibility. This article analyzes six MyBatis‑based batch‑update solutions, measures their performance on a table tb_user with 5 million rows, and provides guidance for selecting the optimal approach.

2. Preparation

The tb_user table schema is created with typical fields (id, user_no, name, email, phone, gender, birthday, etc.). A dataset of over 5 million rows is inserted for testing.

3. Batch Update Implementations

3.1 Loop‑by‑Single Update

Each entity is updated individually inside a Java forEach loop, invoking userDAO.updateById for every record.

@Test
public void testBatchUpdateByFor() {
    List<User> users = listUsers();
    long start = System.currentTimeMillis();
    users.forEach(user -> {
        userDAO.updateById(user);
    });
    long end = System.currentTimeMillis();
    System.out.println("Execution time: " + (end - start) + "ms");
}

SQL generated per record:

UPDATE tb_user SET gender=?, address=?, name=?, update_time=?, updater=? WHERE id=?

. Measured execution time for 10 000 rows: 3846 ms . This method creates N separate SQL statements, causing network I/O proportional to the data volume and is unsuitable for large batches.

3.2 foreach Multiple SQL

Uses MyBatis XML <foreach> to concatenate several UPDATE statements into a single request.

public interface UserDAO extends BaseMapperX<User> {
    int batchUpdateByForeach(@Param("userList") List<User> userList);
}

<update id="batchUpdateByForeach">
    <foreach collection="userList" item="u" separator=";">
        UPDATE tb_user SET update_time = now(),
        name = #{u.name}, address = #{u.address}, gender = #{u.gender}
        WHERE id = #{u.id}
    </foreach>
</update>

Execution time for 10 000 rows (split into 500‑row batches): 1417 ms . Although a single request contains many statements, each row is still updated individually.

3.3 CASE WHEN Expression

Generates a single UPDATE with CASE WHEN clauses for each column, reducing the number of statements to one.

<update id="batchUpdateByCaseWhen">
    UPDATE tb_user SET
        update_time = now(),
        name = CASE
            <foreach collection="userList" item="item">
                WHEN id = #{item.id} AND #{item.name} IS NOT NULL THEN #{item.name}
            </foreach>
            ELSE name END,
        address = CASE … END,
        gender = CASE … END
    WHERE id IN (…);
</update>

Execution time for 10 000 rows: 988 ms . This approach offers excellent performance but requires careful handling of SQL length limits.

3.4 ON DUPLICATE KEY UPDATE

MySQL‑specific syntax that inserts rows and updates existing ones on primary‑key or unique‑key conflict.

<insert id="batchUpdateOnDuplicate">
    INSERT INTO tb_user(user_no, name, phone, address, gender, org_id) VALUES
    <foreach collection="userList" item="item" separator=",">
        (#{item.userNo}, #{item.name}, #{item.phone}, #{item.address}, #{item.gender}, #{item.orgId})
    </foreach>
    ON DUPLICATE KEY UPDATE
        name = VALUES(name), org_id = VALUES(org_id)
</insert>

Execution time for 10 000 rows: 1080 ms . Provides fast up‑sert capability but is limited to MySQL.

3.5 REPLACE INTO

Similar to ON DUPLICATE KEY UPDATE but performs a delete followed by an insert, generating two binlog events and potentially altering primary‑key values.

<insert id="batchUpdateReplace">
    REPLACE INTO tb_user(user_no, name, phone, address, gender, org_id) VALUES
    <foreach collection="userList" item="item" separator=",">
        (#{item.userNo}, #{item.name}, #{item.phone}, #{item.address}, #{item.gender}, #{item.orgId})
    </foreach>
</insert>

Execution time for 10 000 rows: 6705 ms . The delete‑then‑insert semantics can break foreign‑key relationships and are generally risky.

3.6 MyBatis‑Plus Batch Update

Leverages MyBatis‑Plus updateById with a batch size parameter.

@Test
public void testBatchUpdateByMybatisPlus() {
    List<User> users = listUsers();
    long start = System.currentTimeMillis();
    userDAO.updateById(users, 500);
    long end = System.currentTimeMillis();
    System.out.println("Execution time: " + (end - start) + "ms");
}

Execution time for 10 000 rows: 1730 ms . Internally uses a for‑loop, so performance is comparable to the simple loop method.

4. Performance Comparison

For‑loop single update : 3‑4 s, network I/O = N, SQL parse = N, suitable for < 100 rows, fully compatible.

foreach multiple SQL : 1‑2 s, network I/O = 1, SQL parse = N, suitable for 100‑5000 rows, requires configuration.

MyBatis‑Plus : 1‑2 s, network I/O = 1, SQL parse = 1, suitable for 100‑5000 rows, fully compatible.

CASE WHEN : 0.5‑1 s, network I/O = 1, SQL parse = 1, suitable for > 1000 rows, fully compatible.

ON DUPLICATE KEY UPDATE : 0.5‑1 s, network I/O = 1, SQL parse = 1, suitable for > 1000 rows, MySQL only.

REPLACE INTO : 4‑7 s, network I/O = 1, SQL parse = N, suitable for 100‑3000 rows, fully compatible.

5. Conclusion

Choosing a batch‑update strategy requires balancing database type, data volume, system architecture, and team expertise. For most MySQL workloads, ON DUPLICATE KEY UPDATE offers the best trade‑off between performance and maintainability. When multi‑database support is needed, the CASE WHEN expression is a portable alternative. Regardless of the chosen method, batch processing, connection‑parameter tuning, and proper monitoring are essential to achieve optimal production performance.