12 MyBatisPlus Optimization Techniques for Efficient Database Operations

Preface

When it comes to ORM frameworks, I often think of my grandmother's delicious lamb soup. Writing code can feel the same way: using plain MyBatis is like a bland soup, while MyBatisPlus adds the rich flavor that makes the code smooth and performant.

MyBatisPlus acts like a skilled sous‑chef, handling tedious preparation so developers can focus on the core logic. In the following sections I share twelve "secret recipes" for MyBatisPlus optimization.

Avoid Using isNull Checks

// ❌ Not recommended
LambdaQueryWrapper
wrapper1 = new LambdaQueryWrapper<>();
wrapper1.isNull(User::getStatus);

// ✅ Recommended: use a concrete default value
LambdaQueryWrapper
wrapper2 = new LambdaQueryWrapper<>();
wrapper2.eq(User::getStatus, UserStatusEnum.INACTIVE.getCode());

Using explicit default values improves readability, prevents index loss caused by NULL, reduces CPU overhead, and saves storage.

Specify Select Fields

// ❌ Not recommended (select all fields)
List
users1 = userMapper.selectList(null);

// ✅ Recommended: specify needed columns
LambdaQueryWrapper
wrapper = new LambdaQueryWrapper<>();
wrapper.select(User::getId, User::getName, User::getAge);
List
users2 = userMapper.selectList(wrapper);

Limiting selected columns reduces network traffic, enables index‑covering scans, lowers parsing/serialization cost, and saves memory.

Batch Operations Instead of Loops

// ❌ Not recommended
for (User user : userList) {
    userMapper.insert(user);
}

// ✅ Recommended
userService.saveBatch(userList, 100); // process 100 records per batch

// ✅ Better: use a constant batch size
userService.saveBatch(userList, BatchConstants.BATCH_SIZE);

Batching reduces connection overhead, improves transaction consistency, lets the DB optimize execution plans, and cuts network round‑trips.

Use EXISTS Sub‑query

// ❌ Not recommended
wrapper.inSql("user_id", "select user_id from order where amount > 1000");

// ✅ Recommended
wrapper.exists("select 1 from order where order.user_id = user.id and amount > 1000");

// ✅ Better with LambdaQueryWrapper
wrapper.exists(orderService.lambdaQuery()
    .gt(Order::getAmount, 1000)
    .apply("order.user_id = user.id"));

EXISTS leverages indexes and stops scanning after the first match, whereas IN loads all rows into memory.

Replace last() with orderBy

// ❌ Not recommended: SQL injection risk
wrapper.last("ORDER BY " + sortField + " " + sortOrder);

// ❌ Not recommended: hard‑coded FIELD order
wrapper.last("ORDER BY FIELD(status, 'active', 'pending', 'inactive')");

// ✅ Recommended: safe Lambda ordering
wrapper.orderBy(true, true, User::getStatus);

// ✅ Multi‑field example
wrapper.orderByAsc(User::getStatus)
       .orderByDesc(User::getCreateTime);

Direct string concatenation can cause injection, break SQL semantics, and bypass MyBatis‑Plus safety checks.

Use LambdaQuery for Type Safety

// ❌ Not recommended: raw column names
QueryWrapper
wrapper1 = new QueryWrapper<>();
wrapper1.eq("name", "张三").gt("age", 18);

// ✅ Recommended
LambdaQueryWrapper
wrapper2 = new LambdaQueryWrapper<>();
wrapper2.eq(User::getName, "张三")
        .gt(User::getAge, 18);

// ✅ Fluent style
userService.lambdaQuery()
    .eq(User::getName, "张三")
    .gt(User::getAge, 18)
    .list();

Lambda queries provide compile‑time checks, IDE completion, and automatic refactoring support.

Use between Instead of ge/le

// ❌ Not recommended
wrapper.ge(User::getAge, 18)
       .le(User::getAge, 30);

// ✅ Recommended
wrapper.between(User::getAge, 18, 30);

// ✅ Dynamic condition
wrapper.between(ageStart != null && ageEnd != null,
                User::getAge, ageStart, ageEnd);

Between generates simpler SQL, lets the optimizer handle range queries efficiently, and reduces duplication.

Pay Attention to Indexes When Sorting

// ❌ Not recommended (no index on lastLoginTime)
wrapper.orderByDesc(User::getLastLoginTime);

// ✅ Recommended (primary key)
wrapper.orderByDesc(User::getId);

// ✅ Better: composite index
wrapper.orderByDesc(User::getStatus) // status has an index
       .orderByDesc(User::getId); // primary key

Using indexed columns for ordering avoids costly file sorts and enables streaming reads.

Set Pagination Parameters Properly

// ❌ Not recommended: limit 1000 in a single query
wrapper.last("limit 1000");

// ✅ Recommended
Page
page = new Page<>(1, 10);
userService.page(page, wrapper);

// ✅ With conditions
Page
result = userService.lambdaQuery()
    .eq(User::getStatus, "active")
    .page(new Page<>(1, 10));

Proper pagination controls memory usage, improves first‑page latency, and reduces network load.

Handle Null Values in Condition Construction

// ❌ Not recommended
if (StringUtils.isNotBlank(name)) {
    wrapper.eq("name", name);
}
if (age != null) {
    wrapper.eq("age", age);
}

// ✅ Recommended
wrapper.eq(StringUtils.isNotBlank(name), User::getName, name)
       .eq(Objects.nonNull(age), User::getAge, age);

// ✅ Advanced example
wrapper.eq(StringUtils.isNotBlank(name), User::getName, name)
       .eq(Objects.nonNull(age), User::getAge, age)
       .eq(User::getDeleted, false) // filter out logically deleted rows
       .orderByDesc(User::getCreateTime);

This approach elegantly skips null conditions, reduces boilerplate, and prevents redundant SQL.

Performance Tracking for Queries

// ❌ Not recommended: manual timing
public List
listUsers(QueryWrapper
wrapper) {
    long startTime = System.currentTimeMillis();
    List
users = userMapper.selectList(wrapper);
    long endTime = System.currentTimeMillis();
    log.info("Query took: {}ms", (endTime - startTime));
    return users;
}

// ✅ Recommended: try‑with‑resources timer
public List
listUsersWithPerfTrack(QueryWrapper
wrapper) {
    try (PerfTracker.TimerContext ignored = PerfTracker.start()) {
        return userMapper.selectList(wrapper);
    }
}

// Performance tracker utility
@Slf4j
public class PerfTracker {
    private final long startTime;
    private final String methodName;
    private PerfTracker(String methodName) {
        this.startTime = System.currentTimeMillis();
        this.methodName = methodName;
    }
    public static TimerContext start() {
        return new TimerContext(Thread.currentThread().getStackTrace()[2].getMethodName());
    }
    public static class TimerContext implements AutoCloseable {
        private final PerfTracker tracker;
        private TimerContext(String methodName) {
            this.tracker = new PerfTracker(methodName);
        }
        @Override
        public void close() {
            long executeTime = System.currentTimeMillis() - tracker.startTime;
            if (executeTime > 500) {
                log.warn("Slow query alert: method {} took {}ms", tracker.methodName, executeTime);
            }
        }
    }
}

Separating business logic from performance monitoring ensures consistent timing even on exceptions.

Enum Type Mapping

// Define enum
public enum UserStatusEnum {
    NORMAL(1, "正常"),
    DISABLED(0, "禁用");
    @EnumValue // MyBatis‑Plus annotation
    private final Integer code;
    private final String desc;
}

// ✅ Automatic mapping
public class User {
    private UserStatusEnum status;
}

// Query example
userMapper.selectList(
    new LambdaQueryWrapper
()
        .eq(User::getStatus, UserStatusEnum.NORMAL)
);

Enum mapping provides type safety, automatic conversion, and eliminates magic numbers.

Automatic Logical Deletion

@TableLogic
private Integer deleted;

// ✅ Automatic filtering of deleted rows
public List
getActiveUsers() {
    return userMapper.selectList(null); // deleted=1 rows are filtered out automatically
}

// Manual delete (actually updates deleted flag)
userService.removeById(1L);

Logical deletion preserves data, supports recovery, and reduces manual delete logic.

Optimistic Locking

public class Product {
    @Version
    private Integer version;
}

// ✅ Update with version check
public boolean reduceStock(Long productId, Integer count) {
    LambdaUpdateWrapper
wrapper = new LambdaUpdateWrapper<>();
    wrapper.eq(Product::getId, productId)
           .ge(Product::getStock, count);
    Product product = new Product();
    product.setStock(product.getStock() - count);
    return productService.update(product, wrapper);
}

Optimistic locking prevents concurrent conflicts and maintains data consistency.

Increment and Decrement: setIncrBy / setDecrBy

// ❌ Not recommended: raw SQL string
userService.lambdaUpdate()
    .setSql("integral = integral + 10")
    .update();

// ✅ Recommended: setIncrBy
userService.lambdaUpdate()
    .eq(User::getId, 1L)
    .setIncrBy(User::getIntegral, 10)
    .update();

// ✅ Recommended: setDecrBy
userService.lambdaUpdate()
    .eq(User::getId, 1L)
    .setDecrBy(User::getStock, 5)
    .update();

These methods are type‑safe, avoid manual SQL concatenation, and prevent injection.

Conclusion

Writing code is like cooking a fine soup: attention to detail and the right ingredients produce a delightful result. The twelve MyBatisPlus tips presented here help developers craft clean, efficient, and maintainable backend code, turning ordinary queries into a flavorful experience.