12 MyBatis‑Plus Optimization Tips for Efficient Database Operations

When working with database ORM frameworks, writing code can feel like cooking a bland soup; MyBatis‑Plus acts like a master chef, handling tedious preparation so that queries become smooth and performant. Below are twelve "secret recipes" for optimizing MyBatis‑Plus usage.

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, reduces CPU overhead, and saves storage.

Specify Select Fields Explicitly

// ❌ 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 transfer, enables index covering, lowers parsing/serialization cost, and saves memory.

Replace Loops with Batch Operations

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

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

// ✅ Better: use 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 Instead of In‑SQL

// ❌ 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: LambdaQueryWrapper
wrapper.exists(orderService.lambdaQuery()
    .gt(Order::getAmount, 1000)
    .apply("order.user_id = user.id"));

EXISTS leverages indexes, stops at the first match, and avoids loading all rows into memory.

Replace last() with orderBy for Safe Sorting

// ❌ Direct SQL concatenation (SQL‑injection risk)
wrapper.last("ORDER BY " + sortField + " " + sortOrder);

// ✅ Safe Lambda sorting
wrapper.orderBy(true, true, User::getStatus);
wrapper.orderByAsc(User::getStatus)
       .orderByDesc(User::getCreateTime);

Using the built‑in orderBy methods prevents injection, keeps SQL semantics intact, and maintains readability.

Ensure Type Safety with LambdaQueryWrapper

// ❌ String‑based field names may miss updates
QueryWrapper
wrapper1 = new QueryWrapper<>();
wrapper1.eq("name", "张三").gt("age", 18);

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

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

Lambda wrappers provide compile‑time checks, IDE completion, automatic refactoring, and clearer code.

Replace ge/le with between

// ❌ Separate greater‑equal and less‑equal
wrapper.ge(User::getAge, 18)
       .le(User::getAge, 30);

// ✅ Use between
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 better, and improves readability.

Pay Attention to Indexes When Sorting

// ❌ Sorting on a non‑indexed column
wrapper.orderByDesc(User::getLastLoginTime);

// ✅ Sort by primary key (indexed)
wrapper.orderByDesc(User::getId);

// ✅ Composite index sorting
wrapper.orderByDesc(User::getStatus) // status indexed
       .orderByDesc(User::getId); // primary key

Index‑based sorting avoids extra file sorting, reduces memory usage, and enables streaming reads.

Set Pagination Parameters Properly

// ❌ Using raw limit (may fetch too many rows)
wrapper.last("limit 1000");

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

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

Proper pagination controls memory, improves first‑screen load, reduces network pressure, and uses DB resources efficiently.

Handle Null Values Gracefully in Conditions

// ❌ Manual if‑else checks
if (StringUtils.isNotBlank(name)) {
    wrapper.eq("name", name);
}
if (age != null) {
    wrapper.eq("age", age);
}

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

This approach eliminates redundant checks, keeps code clean, and prevents generating useless SQL.

Query Performance Tracking

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

// ✅ 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 execTime = System.currentTimeMillis() - tracker.startTime;
            if (execTime > 500) {
                log.warn("Slow query: method {} took {}ms", tracker.methodName, execTime);
            }
        }
    }
}

Separating business logic from monitoring ensures reliable timing even on exceptions.

Enum Mapping for Type Safety

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

// Entity uses enum directly
public class User {
    private UserStatusEnum status;
}

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

Enums provide compile‑time safety, automatic DB‑enum conversion, and avoid magic numbers.

Automatic Logical Deletion

@TableLogic
private Integer deleted;

// ✅ Automatically filter deleted rows
public List
getActiveUsers() {
    return userMapper.selectList(null); // deleted=1 rows excluded
}

// ✅ Logical delete via service
userService.removeById(1L); // updates deleted flag

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

Optimistic Locking for Concurrency Control

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 automatically manages version numbers.

Increment/Decrement Helpers: setIncrBy and setDecrBy

// ❌ Direct SQL string
userService.lambdaUpdate()
    .setSql("integral = integral + 10")
    .update();

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

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

These methods ensure type safety, avoid SQL injection, and make code more maintainable.

Conclusion

Writing code is like cooking a delicate soup; attention to detail and the right tools produce a flavorful result. The twelve MyBatis‑Plus tips presented here help developers craft clean, efficient, and maintainable database interactions, turning ordinary queries into a refined, tasty experience.