12 MyBatis‑Plus Optimization Tips for Efficient Database Operations

When it comes to database ORM, the author likens coding to cooking a flavorful lamb soup, emphasizing that MyBatis‑Plus can turn ordinary queries into a smooth, high‑performance experience. The following twelve "secret recipes" demonstrate how to write cleaner, faster, and more maintainable code.

Avoid Using isNull Checks

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

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

Reasons:

Improves readability and maintainability.

NULL values invalidate indexes, preventing MySQL from optimizing the query.

NULL comparisons require special handling, increasing CPU overhead.

NULL values occupy extra storage, affecting compression efficiency.

Specify Select Fields Explicitly

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

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

Reasons:

Avoids unnecessary network transfer of unused columns.

Enables index‑only scans, avoiding table look‑ups.

Reduces database parsing and serialization overhead.

Lowers memory consumption, especially for large result sets.

Replace Loops with Batch Operations

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

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

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

Reasons:

Reduces the overhead of creating and destroying database connections.

Batch updates can be executed within a single transaction, improving data consistency.

The database can optimize the execution plan for batch operations.

Significantly cuts network round‑trips, boosting throughput.

Use EXISTS Subquery Instead of IN

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

Reasons:

EXISTS leverages indexes for fast look‑ups.

EXISTS stops scanning after the first match.

IN subqueries must load all data into memory before comparison.

When the outer table is large, EXISTS shows clear performance advantages.

Replace last() with orderBy()

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

// ❌ Not recommended: direct string concatenation
wrapper.last("ORDER BY FIELD(status, 'active', 'pending', 'inactive')");

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

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

Reasons:

Direct string concatenation can lead to SQL injection.

Dynamic SQL may break SQL semantics.

Impacts maintainability and readability of the statement.

last() bypasses MyBatis‑Plus safety checks.

Use LambdaQueryWrapper for Type‑Safe Queries

// ❌ Not recommended: field name may be missed after refactor
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 style
userService.lambdaQuery()
    .eq(User::getName, "张三")
    .gt(User::getAge, 18)
    .list();

Reasons:

Compile‑time type checking prevents misspelled field names.

IDE offers better code completion.

Refactoring automatically updates field references.

Improves code readability and maintainability.

Replace ge() and le() with between()

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

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

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

Reasons:

Generated SQL is more concise, reducing parsing overhead.

The optimizer can handle range queries more efficiently.

Code becomes clearer and easier to read.

Reduces repetitive field name declarations.

Pay Attention to Indexes When Sorting

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

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

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

Reasons:

Indexes inherently provide ordering, avoiding extra sort operations.

Sorting without an index forces file‑based sorting, severely hurting performance.

Large data sets may cause memory overflow during sort.

Index‑based ordering enables streaming reads.

Set Pagination Parameters Properly

// ❌ Not recommended
wrapper.last("limit 1000"); // fetches too many rows at once

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

Reasons:

Controls the amount of data per query, preventing memory overflow.

Improves first‑screen load speed and user experience.

Reduces network transmission pressure.

Leverages database resources more efficiently.

Handle Null Values Gracefully in Conditions

// ❌ 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 business defaults
wrapper.eq(StringUtils.isNotBlank(name), User::getName, name)
       .eq(Objects.nonNull(age), User::getAge, age)
       .eq(User::getDeleted, false) // default to non‑deleted records
       .orderByDesc(User::getCreateTime);

Reasons:

Elegant handling of empty values avoids generating useless conditions.

Reduces if‑else boilerplate.

Improves code readability.

Prevents redundant SQL clauses.

Query Performance Tracking

// ❌ Not recommended: manual timing, verbose
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;
}

// ✅ Recommended: try‑with‑resources auto‑timing
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);
            }
        }
    }
}

Reasons:

Business logic stays separate from performance monitoring.

try‑with‑resources guarantees timing is recorded even on exceptions.

No manual start/stop management needed.

Cleaner and more elegant implementation.

Enum Mapping

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

// Entity example
public class User {
    private UserStatusEnum status;
}

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

Reasons:

Type safety.

Automatic conversion between database values and enums.

Avoids magic numbers.

Improves code readability.

Automatic Logical Deletion

@TableLogic
private Integer deleted;

// ✅ Recommended: automatically filter deleted records
public List
getActiveUsers() {
    return userMapper.selectList(null); // automatically excludes deleted = 1
}

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

Reasons:

Data is not physically removed.

Queries automatically skip logically deleted rows.

Supports data recovery.

Reduces boilerplate delete logic.

Optimistic Locking for Updates

public class Product {
    @Version // optimistic lock version field
    private Integer version;
}

// ✅ Recommended: version handled automatically during update
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);
}

Reasons:

Prevents concurrent update conflicts.

Version field is managed automatically.

Simplifies concurrent update logic.

Ensures data consistency.

Increment and Decrement: setIncrBy and setDecrBy

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

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

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

Reasons:

Type safety.

Avoids manual SQL concatenation, preventing injection.

Code is more maintainable and clearer.

Conclusion

Writing code is like cooking a delicate soup: it requires careful ingredients, proper heat, and respect for the process. The twelve MyBatis‑Plus optimization tips are a programmer’s way of honoring the craft, turning ordinary statements into elegant, high‑performance solutions.

Just as a grandmother’s soup needs love and attention, excellent code demands meticulous detail. With MyBatis‑Plus as a reliable kitchen assistant, developers can focus on creativity while the framework handles the tedious work.

Mastering these techniques transforms code from a simple command list into a graceful poem or symphony, leaving a lasting, delightful impression.

May every developer use MyBatis‑Plus to brew their own "secret broth" of clean, efficient, and elegant code.