Boost MyBatis‑Plus CRUD Performance on Tens of Millions of Records

Background

After 8 years of Java development using Hibernate, JPA and MyBatis, I now mainly use MyBatis‑Plus (MP). I have experienced many pitfalls, especially that CRUD seems simple but performance degrades dramatically with large data volumes.

In a recent project with tables exceeding ten million rows, each CRUD operation became risky and triggered database alarms. This article shares practical experience on how to optimize MyBatis‑Plus CRUD in such massive‑data scenarios.

1. MyBatis‑Plus Overview

MP is an enhancement tool for MyBatis, emphasizing “non‑intrusive, low‑threshold, strong enhancement”, and provides an elegant CRUD wrapper suitable for middle‑back‑office systems. However, performance tuning still requires understanding the underlying SQL and proper scenario judgment.

2. Challenges of Ten‑Million‑Level Data

Common problems include slow queries, pagination lag, risky bulk updates/deletes, difficult data migration, unreasonable index strategies, and concurrency issues when optimistic/pessimistic locks are not used.

3. Key Strategies for CRUD Optimization

We examine the four dimensions—select, insert, update, delete—and illustrate MP‑based optimizations.

1. Query Optimization (Select)

✅ Use pagination plugin + index optimization

Page<User> page = new Page<>(1, 10);
IPage<User> result = userMapper.selectPage(page,
    new QueryWrapper<User>()
        .eq("status", "active")
        .orderByDesc("create_time"));

Create composite index (status, create_time) to avoid file sorting.

Set reasonable limit range; avoid deep pagination (prefer cursor pagination).

✅ Cursor pagination (Keyset) example:

QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.lt("id", lastId)
       .orderByDesc("id")
       .last("limit 100");
List<User> users = userMapper.selectList(wrapper);

Keyset pagination is much faster than traditional OFFSET pagination and suits bulk export or loading.

2. Insert Optimization (Insert)

✅ Batch insert instead of single rows

List<User> userList = new ArrayList<>();
for (int i = 0; i < 1000; i++) {
    userList.add(new User("user" + i));
}
userMapper.insertBatchSomeColumn(userList); // custom method or MyBatis extension

Keep each batch under 1000 rows to avoid overly long SQL.

Use native JDBC batch processing for better performance.

3. Update Optimization (Update)

✅ Avoid full‑table updates

userMapper.update(null,
    new UpdateWrapper<User>()
        .eq("status", "inactive"));

Correct approach:

UpdateWrapper<User> wrapper = new UpdateWrapper<>();
wrapper.eq("status", "active")
       .set("status", "inactive");
userMapper.update(null, wrapper);

Remember: always add conditions to UPDATE.

✅ Optimistic lock for concurrent updates

@TableField(fill = FieldFill.UPDATE)
@Version
private Integer version;
user.setVersion(3);
userMapper.updateById(user); // MP adds version check automatically

4. Delete Optimization (Delete)

✅ Logical delete instead of physical delete

@TableLogic
private Integer isDeleted;
userMapper.deleteById(123L); // actually performs UPDATE

Avoid accidental deletions.

Retain data for audit.

Compatible with recycle‑bin mechanisms.

Note: add an index to the logical‑delete column.

4. Batch and Asynchronous Processing

In massive‑data scenarios, batch processing combined with asynchronous execution is essential. Use Stream for chunked handling, Spring Batch or custom thread pools for async tasks, and scheduled jobs (e.g., XXL‑JOB) for time‑windowed processing.

5. Database‑Level Recommendations

Build appropriate indexes; avoid excessive or duplicate ones.

Consider vertical/horizontal sharding (ShardingSphere, MyCat).

Cache hot data with middleware such as Redis.

Prefer JOIN over views/subqueries when possible.

6. Conclusion

MyBatis‑Plus offers elegant APIs, but once data scales, the framework is only a tool; the real performance hinges on underlying SQL design and system architecture. Proper modeling, disciplined MP usage, and database tuning enable systems to handle tens of millions of records gracefully.

Performance is designed, not merely tuned.