12 MyBatis‑Plus Optimization Tips for Efficient Database Operations

MyBatis‑Plus, a powerful Java ORM framework, can greatly simplify database interactions, but using it wisely is essential for performance and maintainability. This guide shares twelve optimization "recipes" that turn ordinary queries into efficient, readable, and safe operations.

Avoid 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: default 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‑only 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: custom batch size constant
userService.saveBatch(userList, BatchConstants.BATCH_SIZE);

Batching cuts connection creation overhead, groups updates in a single transaction, lets the DB optimize execution plans, and dramatically reduces round‑trips.

Use EXISTS Instead of IN Sub‑queries

// ❌ 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 after the first match, while IN loads all rows into memory, making EXISTS faster on large tables.

Replace last() with orderBy

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

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

Using Lambda ordering avoids injection, preserves SQL semantics, and keeps the query under MyBatis‑Plus safety checks.

Use LambdaQuery for Type‑Safety

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

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

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

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

Replace ge/le with between

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

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

// ✅ Better: conditional range
wrapper.between(ageStart != null && ageEnd != null,
    User::getAge, ageStart, ageEnd);

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

Mind Indexes When Sorting

// ❌ Not recommended: sorting on non‑indexed column
wrapper.orderByDesc(User::getLastLoginTime);

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

// ✅ Better: combine indexed columns
wrapper.orderByDesc(User::getStatus) // status has index
       .orderByDesc(User::getId); // primary key

Indexed columns are already sorted, avoiding costly file‑based sorts and memory overflow.

Set Pagination Parameters Properly

// ❌ Not recommended: fetch too many rows
wrapper.last("limit 1000");

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

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

Limiting rows per query prevents OOM, speeds up first‑page load, reduces network load, and uses DB resources efficiently.

Handle Null Values Gracefully

// ❌ 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);

// ✅ Better with default filters
wrapper.eq(StringUtils.isNotBlank(name), User::getName, name)
       .eq(Objects.nonNull(age), User::getAge, age)
       .eq(User::getDeleted, false)
       .orderByDesc(User::getCreateTime);

Conditional wrappers avoid generating useless SQL, keep code concise, and improve readability.

Query Performance Tracking

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

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

// Performance tracker utility (simplified)
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 exec = System.currentTimeMillis() - tracker.startTime;
            if (exec > 500) log.warn("慢查询告警：方法 {} 耗时 {}ms", tracker.methodName, exec);
        }
    }
}

Separating business logic from timing code ensures consistent measurement even on exceptions.

Enum Type Mapping

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

// Entity field
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;

// Query active users (deleted = 0 is filtered automatically)
public List
getActiveUsers() {
    return userMapper.selectList(null);
}

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

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

Optimistic Locking

public class Product {
    @Version
    private Integer version;
}

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);
}

Version field prevents concurrent updates from overwriting each other, ensuring data consistency.

Increment/Decrement Helpers

// ❌ 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 helpers are type‑safe, avoid manual SQL concatenation, and protect against injection.

By applying these twelve tips, developers can write MyBatis‑Plus code that is as smooth and flavorful as a well‑crafted soup—readable, efficient, and pleasant to maintain.