登录 注册

Spring Boot + MyBatis Plus 性能优化指南：从SQL调优到缓存策略全解析

Kevin468
Kevin468 2026-02-12T14:19:11+08:00
0 0 0

引言

在现代企业级应用开发中，性能优化是确保系统稳定运行和用户体验的关键因素。Spring Boot作为主流的Java开发框架，结合MyBatis Plus的ORM工具，为开发者提供了便捷的数据访问能力。然而，随着业务复杂度的增加，系统性能瓶颈往往出现在数据库访问层面。本文将深入探讨Spring Boot + MyBatis Plus项目中的性能优化策略，从SQL调优到缓存策略，提供一套完整的性能优化解决方案。

一、性能瓶颈分析与识别

1.1 常见性能问题识别

在Spring Boot项目中，数据库访问往往是性能瓶颈的主要来源。通过监控工具可以发现以下典型问题：

  • 慢SQL查询：未优化的SQL语句导致查询时间过长
  • 数据库连接池问题：连接数不足或配置不当导致连接等待
  • 重复查询：相同数据多次查询，未使用缓存机制
  • 分页查询效率低：大数据量分页查询性能差
  • N+1查询问题：关联查询导致的额外数据库访问

1.2 性能监控工具推荐

# application.yml
management:
  endpoints:
    web:
      exposure:
        include: health,info,metrics,prometheus
  metrics:
    web:
      server:
        request:
          metrics:
            enabled: true

二、MyBatis Plus SQL优化技巧

2.1 基础查询优化

MyBatis Plus提供了丰富的查询API，合理使用可以显著提升查询效率：

// ❌ 不推荐：全表查询
List<User> allUsers = userMapper.selectList(null);

// ✅ 推荐：指定查询字段
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.select("id", "name", "email"); // 只查询必要字段
List<User> users = userMapper.selectList(wrapper);

// ✅ 推荐：使用Lambda表达式
LambdaQueryWrapper<User> lambdaWrapper = new LambdaQueryWrapper<>();
lambdaWrapper.select(User::getId, User::getName, User::getEmail)
             .eq(User::getStatus, 1)
             .between(User::getCreateTime, startDate, endDate);

2.2 索引优化策略

-- 创建复合索引优化查询
CREATE INDEX idx_user_status_create_time ON user(status, create_time);

-- 为经常用于WHERE条件的字段创建索引
CREATE INDEX idx_user_email ON user(email);
CREATE INDEX idx_user_phone ON user(phone);

2.3 避免N+1查询问题

// ❌ N+1查询问题
List<User> users = userMapper.selectList(null);
for (User user : users) {
    // 每次循环都执行一次查询
    List<Order> orders = orderMapper.selectByUserId(user.getId());
}

// ✅ 使用关联查询优化
@Select("SELECT u.*, o.id as order_id, o.amount FROM user u LEFT JOIN order o ON u.id = o.user_id")
List<UserWithOrder> selectUserWithOrders();

2.4 批量操作优化

// ❌ 逐条插入
for (User user : users) {
    userMapper.insert(user);
}

// ✅ 批量插入
userMapper.insertBatchSomeColumn(users);

// ✅ 批量更新
userMapper.updateBatchById(users);

三、数据库连接池配置优化

3.1 HikariCP连接池配置

HikariCP是Spring Boot 2.x默认的连接池实现，性能优异：

# application.yml
spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      # 连接池名称
      pool-name: MyHikariCP
      # 最小空闲连接数
      minimum-idle: 10
      # 最大连接数
      maximum-pool-size: 50
      # 连接超时时间
      connection-timeout: 30000
      # 空闲连接超时时间
      idle-timeout: 600000
      # 连接池最大存活时间
      max-lifetime: 1800000
      # 验证连接是否有效的查询语句
      validation-timeout: 5000
      # 自动提交
      auto-commit: true
      # 连接测试
      connection-test-query: SELECT 1

3.2 连接池监控配置

@Configuration
public class DataSourceConfig {
    
    @Bean
    @Primary
    public DataSource dataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        // 配置连接池参数
        dataSource.setMaximumPoolSize(50);
        dataSource.setMinimumIdle(10);
        dataSource.setConnectionTimeout(30000);
        dataSource.setIdleTimeout(600000);
        dataSource.setMaxLifetime(1800000);
        
        // 添加监控
        dataSource.setRegisterMbeans(true);
        return dataSource;
    }
}

四、Redis缓存策略优化

4.1 缓存设计原则

@Component
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    // 缓存策略：读写分离，先读缓存，后读数据库
    public User getUserById(Long id) {
        String key = "user:" + id;
        
        // 先从缓存读取
        User user = (User) redisTemplate.opsForValue().get(key);
        if (user != null) {
            return user;
        }
        
        // 缓存未命中，查询数据库
        user = userMapper.selectById(id);
        if (user != null) {
            // 写入缓存，设置过期时间
            redisTemplate.opsForValue().set(key, user, 30, TimeUnit.MINUTES);
        }
        
        return user;
    }
}

4.2 缓存穿透防护

public User getUserByIdWithProtection(Long id) {
    String key = "user:" + id;
    
    // 先从缓存读取
    Object cacheValue = redisTemplate.opsForValue().get(key);
    if (cacheValue != null) {
        if (cacheValue instanceof String && "NULL".equals(cacheValue)) {
            // 缓存空值，防止缓存穿透
            return null;
        }
        return (User) cacheValue;
    }
    
    // 缓存未命中，查询数据库
    User user = userMapper.selectById(id);
    if (user == null) {
        // 将空值写入缓存，设置较短过期时间
        redisTemplate.opsForValue().set(key, "NULL", 5, TimeUnit.MINUTES);
    } else {
        redisTemplate.opsForValue().set(key, user, 30, TimeUnit.MINUTES);
    }
    
    return user;
}

4.3 缓存雪崩解决方案

@Component
public class CacheService {
    
    private static final String LOCK_KEY = "cache_lock:";
    private static final String CACHE_KEY_PREFIX = "user_cache:";
    
    public User getUserWithLock(Long id) {
        String key = CACHE_KEY_PREFIX + id;
        String lockKey = LOCK_KEY + id;
        
        // 获取分布式锁
        Boolean lock = redisTemplate.opsForValue().setIfAbsent(lockKey, "locked", 10, TimeUnit.SECONDS);
        if (!lock) {
            // 等待其他线程处理完
            try {
                Thread.sleep(100);
                return getUserWithLock(id);
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
            }
        }
        
        try {
            // 先从缓存读取
            User user = (User) redisTemplate.opsForValue().get(key);
            if (user != null) {
                return user;
            }
            
            // 缓存未命中，查询数据库
            user = userMapper.selectById(id);
            if (user != null) {
                redisTemplate.opsForValue().set(key, user, 30, TimeUnit.MINUTES);
            }
            
            return user;
        } finally {
            // 释放锁
            redisTemplate.delete(lockKey);
        }
    }
}

4.4 缓存更新策略

public void updateUser(User user) {
    String key = "user:" + user.getId();
    
    // 更新数据库
    userMapper.updateById(user);
    
    // 更新缓存
    redisTemplate.opsForValue().set(key, user, 30, TimeUnit.MINUTES);
    
    // 或者删除缓存，让下次查询时重新加载
    // redisTemplate.delete(key);
}

五、分页查询优化

5.1 MyBatis Plus分页插件配置

@Configuration
public class MybatisPlusConfig {
    
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 分页插件
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setOverflow(true);
        paginationInnerInterceptor.setMaxLimit(1000L);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        return interceptor;
    }
}

5.2 分页查询优化策略

@Service
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    // 优化前：全量查询后分页
    public IPage<User> getUserPage(int current, int size) {
        // ❌ 不推荐：先查所有数据再分页
        Page<User> page = new Page<>(current, size);
        return userMapper.selectPage(page, null);
    }
    
    // 优化后：使用合理分页策略
    public IPage<User> getUserPageOptimized(int current, int size) {
        // ✅ 推荐：使用条件分页
        Page<User> page = new Page<>(current, size);
        
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select("id", "name", "email", "create_time")
               .orderByDesc("create_time");
        
        return userMapper.selectPage(page, wrapper);
    }
    
    // 优化后：大数据量分页优化
    public IPage<User> getUserPageLargeData(int current, int size) {
        Page<User> page = new Page<>(current, size);
        
        // 使用游标分页优化大数据量查询
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select("id", "name", "email", "create_time")
               .orderByAsc("id") // 使用主键排序
               .last("LIMIT " + size); // 限制查询数量
        
        return userMapper.selectPage(page, wrapper);
    }
}

5.3 游标分页实现

public class CursorPageResult<T> {
    private List<T> data;
    private String cursor;
    private boolean hasMore;
    
    // getter/setter
}

public CursorPageResult<User> getUserCursorPage(Long lastId, int size) {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    if (lastId != null) {
        wrapper.gt("id", lastId);
    }
    wrapper.select("id", "name", "email", "create_time")
           .orderByAsc("id")
           .last("LIMIT " + size);
    
    List<User> users = userMapper.selectList(wrapper);
    
    CursorPageResult<User> result = new CursorPageResult<>();
    result.setData(users);
    result.setHasMore(users.size() == size);
    
    if (!users.isEmpty()) {
        result.setCursor(String.valueOf(users.get(users.size() - 1).getId()));
    }
    
    return result;
}

六、查询缓存优化实践

6.1 基于注解的缓存管理

@Component
public class CacheManager {
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    @Cacheable(value = "userCache", key = "#id")
    public User getUserById(Long id) {
        return userMapper.selectById(id);
    }
    
    @CacheEvict(value = "userCache", key = "#user.id")
    public void updateUser(User user) {
        userMapper.updateById(user);
    }
    
    @CacheEvict(value = "userCache", allEntries = true)
    public void clearUserCache() {
        // 清空所有用户缓存
    }
}

6.2 自定义缓存策略

public class CustomCacheService {
    
    private static final Map<String, CacheEntry> cacheMap = new ConcurrentHashMap<>();
    private static final int MAX_CACHE_SIZE = 1000;
    
    public User getUserWithCustomCache(Long id) {
        String key = "user:" + id;
        CacheEntry entry = cacheMap.get(key);
        
        if (entry != null && !entry.isExpired()) {
            return entry.getValue();
        }
        
        // 缓存未命中，查询数据库
        User user = userMapper.selectById(id);
        if (user != null) {
            cacheMap.put(key, new CacheEntry<>(user, System.currentTimeMillis() + 1800000)); // 30分钟过期
        }
        
        return user;
    }
    
    // 缓存清理
    public void clearCache() {
        cacheMap.entrySet().removeIf(entry -> entry.getValue().isExpired());
    }
    
    static class CacheEntry<T> {
        private T value;
        private long expireTime;
        
        public CacheEntry(T value, long expireTime) {
            this.value = value;
            this.expireTime = expireTime;
        }
        
        public T getValue() {
            return value;
        }
        
        public boolean isExpired() {
            return System.currentTimeMillis() > expireTime;
        }
    }
}

七、性能监控与调优

7.1 SQL执行监控

@Aspect
@Component
public class SqlMonitorAspect {
    
    private static final Logger logger = LoggerFactory.getLogger(SqlMonitorAspect.class);
    
    @Around("execution(* com.example.mapper.*.*(..))")
    public Object monitorSqlExecution(ProceedingJoinPoint joinPoint) throws Throwable {
        long startTime = System.currentTimeMillis();
        String methodName = joinPoint.getSignature().getName();
        
        try {
            Object result = joinPoint.proceed();
            long endTime = System.currentTimeMillis();
            long duration = endTime - startTime;
            
            if (duration > 1000) { // 超过1秒的SQL记录日志
                logger.warn("Slow SQL execution: {} took {}ms", methodName, duration);
            }
            
            return result;
        } catch (Exception e) {
            logger.error("SQL execution error: {}", methodName, e);
            throw e;
        }
    }
}

7.2 数据库连接监控

@Component
public class DatabaseMonitor {
    
    @Autowired
    private HikariDataSource dataSource;
    
    public void monitorConnectionPool() {
        HikariPoolMXBean poolBean = dataSource.getHikariPoolMXBean();
        
        logger.info("Connection Pool Status:");
        logger.info("Active connections: {}", poolBean.getActiveConnections());
        logger.info("Idle connections: {}", poolBean.getIdleConnections());
        logger.info("Total connections: {}", poolBean.getTotalConnections());
        logger.info("Waiting connections: {}", poolBean.getThreadsAwaitingConnection());
    }
}

八、最佳实践总结

8.1 性能优化优先级

  1. SQL优化：这是最基础也是最重要的优化点
  2. 缓存策略：合理使用缓存可以显著提升性能
  3. 连接池配置：优化数据库连接管理
  4. 分页策略：针对大数据量的分页查询优化
  5. 监控告警：建立完善的性能监控体系

8.2 代码规范建议

// ✅ 推荐的代码规范
@Service
@Transactional
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    // 使用适当的查询字段
    public User getUserById(Long id) {
        String key = "user:" + id;
        User user = (User) redisTemplate.opsForValue().get(key);
        
        if (user == null) {
            user = userMapper.selectById(id);
            if (user != null) {
                redisTemplate.opsForValue().set(key, user, 30, TimeUnit.MINUTES);
            }
        }
        
        return user;
    }
    
    // 批量操作优化
    public void batchInsertUsers(List<User> users) {
        if (CollectionUtils.isEmpty(users)) {
            return;
        }
        
        // 分批处理，避免内存溢出
        int batchSize = 1000;
        for (int i = 0; i < users.size(); i += batchSize) {
            int endIndex = Math.min(i + batchSize, users.size());
            List<User> batch = users.subList(i, endIndex);
            userMapper.insertBatchSomeColumn(batch);
        }
    }
}

8.3 性能测试建议

@PerformanceTest
public class UserServicePerformanceTest {
    
    @Test
    public void testUserQueryPerformance() {
        // 准备测试数据
        List<User> users = generateTestData(10000);
        userMapper.insertBatchSomeColumn(users);
        
        // 测试查询性能
        long startTime = System.currentTimeMillis();
        for (int i = 0; i < 1000; i++) {
            User user = userMapper.selectById(i);
        }
        long endTime = System.currentTimeMillis();
        
        logger.info("Average query time: {}ms", (endTime - startTime) / 1000.0);
    }
}

结语

Spring Boot + MyBatis Plus项目的性能优化是一个系统工程，需要从多个维度进行综合考虑。通过本文介绍的SQL优化、缓存策略、连接池配置、分页查询优化等技术手段，可以显著提升应用性能。然而，性能优化是一个持续的过程，需要结合实际业务场景，建立完善的监控体系，持续跟踪和优化系统性能。

记住，任何优化都应该基于实际的性能测试数据，避免过度优化。在实施优化策略时，要充分考虑系统的可维护性和可扩展性，确保优化措施能够在保证系统稳定运行的前提下提升性能表现。

# mybatis # Spring # Boot
相关推荐
广告位招租

相似文章

    评论 (0)

    0/2000