MyBatis 性能优化最佳实践：从 SQL 到连接池的全面调优指南

🚀 MyBatis 性能优化最佳实践：从 SQL 到连接池的全面调优指南

⚡ 一、性能优化全景图

💡 MyBatis 性能瓶颈分析

优化目标​​：

• 🚀 降低数据库查询耗时
• 📉 减少网络IO和磁盘IO
• 💾 优化内存使用效率
• 🔄 提高并发处理能力

🗃️ 二、SQL 与查询优化

💡 SQL 优化核心策略

1. 索引优化实践
   ​​场景​​：用户表按状态和创建时间查询

-- 优化前：全表扫描
SELECT * FROM users WHERE status = 1 ORDER BY create_time DESC;

-- 优化后：添加复合索引
CREATE INDEX idx_status_createtime ON users(status, create_time DESC);

-- 优化SQL：利用索引覆盖
SELECT id, name, email FROM users 
WHERE status = 1 
ORDER BY create_time DESC 
LIMIT 100;

​​索引优化建议​​：

2. 解决 N+1 查询问题
   ​​问题场景​​

// N+1查询示例：1次查询用户 + N次查询订单
public List<User> getUsersWithOrders() {
    List<User> users = userMapper.selectAllUsers();
    for (User user : users) {
        // 每次循环都执行一次查询
        List<Order> orders = orderMapper.selectByUserId(user.getId());
        user.setOrders(orders);
    }
    return users;
}

​​解决方案​​：

<!-- 使用连接查询一次性获取 -->
<select id="selectUsersWithOrders" resultMap="userWithOrdersMap">
    SELECT u.*, o.id as order_id, o.amount, o.create_time as order_time
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.status = 1
</select>

<resultMap id="userWithOrdersMap" type="User">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="orders" ofType="Order">
        <id property="id" column="order_id"/>
        <result property="amount" column="amount"/>
        <result property="createTime" column="order_time"/>
    </collection>
</resultMap>

3. 懒加载配置优化

<!-- mybatis-config.xml -->
<settings>
    <!-- 开启懒加载 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="false"/>
    <setting name="lazyLoadTriggerMethods" value=""/>
</settings>

<!-- 特定关联的懒加载配置 -->
<resultMap id="userLazyMap" type="User">
    <collection property="orders" ofType="Order" 
                select="selectOrdersByUserId" column="id"
                fetchType="lazy"/>
</resultMap>

📊 SQL 优化效果对比

📦 三、批量操作优化

💡 批量插入性能对比

1. BatchExecutor 批量操作

// 使用BatchExecutor执行批量操作
public void batchInsertUsers(List<User> users) {
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
    try {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        
        for (int i = 0; i < users.size(); i++) {
            mapper.insertUser(users.get(i));
            
            // 每1000条提交一次，避免内存溢出
            if (i % 1000 == 0 && i > 0) {
                sqlSession.commit();
                sqlSession.clearCache(); // 清空缓存避免OOM
            }
        }
        
        sqlSession.commit();
    } finally {
        sqlSession.close();
    }
}

// 批量更新示例
public void batchUpdateUsers(List<User> users) {
    try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        
        for (User user : users) {
            mapper.updateUser(user);
        }
        
        sqlSession.commit();
    }
}

2. foreach 批量插入

<!-- 批量插入SQL -->
<insert id="batchInsertUsers">
    INSERT INTO users (name, email, status) VALUES
    <foreach item="user" collection="list" separator=",">
        (#{user.name}, #{user.email}, #{user.status})
    </foreach>
</insert>

<!-- 批量更新SQL -->
<update id="batchUpdateUsers">
    <foreach item="user" collection="list" separator=";">
        UPDATE users 
        SET name = #{user.name}, email = #{user.email}
        WHERE id = #{user.id}
    </foreach>
</update>

// 服务层调用
public void batchInsertUsers(List<User> users) {
    // 分批处理，避免SQL过长
    int batchSize = 1000;
    for (int i = 0; i < users.size(); i += batchSize) {
        List<User> batch = users.subList(i, Math.min(i + batchSize, users.size()));
        userMapper.batchInsertUsers(batch);
    }
}

📊 批量操作性能数据

🏊 四、连接池深度调优

💡 HikariCP 优化配置

# application.yml
spring:
  datasource:
    hikari:
      # 连接池大小
      maximum-pool-size: 20
      minimum-idle: 5
      
      # 连接超时与生命周期
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      
      # 性能优化参数
      connection-init-sql: SELECT 1
      connection-test-query: SELECT 1
      validation-timeout: 3000
      
      # 监控相关
      register-mbeans: true
      leak-detection-threshold: 60000

🔧 连接池参数调优指南

📈 连接池监控配置

// 连接池监控端点
@Bean
public MeterRegistryCustomizer<MeterRegistry> metricsCustomizer(DataSource dataSource) {
    return registry -> {
        if (dataSource instanceof HikariDataSource) {
            HikariDataSource hikariDataSource = (HikariDataSource) dataSource;
            
            // 注册监控指标
            new HikariDataSourceMetrics(hikariDataSource, "app-datasource")
                .bindTo(registry);
        }
    };
}

// 健康检查配置
management:
  endpoints:
    web:
      exposure:
        include: health,metrics,info
  endpoint:
    health:
      show-details: always
      probes:
        enabled: true

💾 五、缓存策略优化

💡 多级缓存架构

1. 二级缓存优化配置

<!-- Mapper缓存配置 -->
<cache
  eviction="LRU"
  flushInterval="60000"
  size="1024"
  readOnly="true"
  blocking="false"/>

<!-- 特定语句缓存配置 -->
<select id="selectUserById" resultType="User" useCache="true" flushCache="false">
    SELECT * FROM users WHERE id = #{id}
</select>

2. Redis 分布式缓存集成

// Redis缓存配置
@Configuration
@EnableCaching
public class RedisConfig extends CachingConfigurerSupport {
    
    @Bean
    public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory factory) {
        RedisTemplate<String, Object> template = new RedisTemplate<>();
        template.setConnectionFactory(factory);
        
        // 使用Jackson序列化
        Jackson2JsonRedisSerializer<Object> serializer = 
            new Jackson2JsonRedisSerializer<>(Object.class);
        
        ObjectMapper mapper = new ObjectMapper();
        mapper.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);
        mapper.activateDefaultTyping(mapper.getPolymorphicTypeValidator(), 
            ObjectMapper.DefaultTyping.NON_FINAL);
        
        serializer.setObjectMapper(mapper);
        template.setValueSerializer(serializer);
        template.setKeySerializer(new StringRedisSerializer());
        
        return template;
    }
    
    @Bean
    public CacheManager cacheManager(RedisConnectionFactory factory) {
        RedisCacheConfiguration config = RedisCacheConfiguration.defaultCacheConfig()
            .entryTtl(Duration.ofHours(1)) // 缓存1小时
            .disableCachingNullValues() // 不缓存null值
            .serializeValuesWith(RedisSerializationContext.SerializationPair
                .fromSerializer(new GenericJackson2JsonRedisSerializer()));
        
        return RedisCacheManager.builder(factory)
            .cacheDefaults(config)
            .build();
    }
}

3. 缓存使用示例

// 服务层缓存注解
@Service
public class UserService {
    
    @Cacheable(value = "users", key = "#id")
    public User getUserById(Long id) {
        return userMapper.selectById(id);
    }
    
    @CacheEvict(value = "users", key = "#user.id")
    public void updateUser(User user) {
        userMapper.updateUser(user);
    }
    
    @Caching(evict = {
        @CacheEvict(value = "users", key = "#user.id"),
        @CacheEvict(value = "user-list", allEntries = true)
    })
    public void updateUserWithCache(User user) {
        userMapper.updateUser(user);
    }
}

📊 缓存性能对比

🎯 六、综合实践与监控

💡 全链路性能监控

// SQL执行监控切面
@Aspect
@Component
@Slf4j
public class SqlPerformanceAspect {
    
    private final ThreadLocal<Long> startTime = new ThreadLocal<>();
    
    @Around("execution(* com.example.mapper.*.*(..))")
    public Object monitorSqlPerformance(ProceedingJoinPoint joinPoint) throws Throwable {
        startTime.set(System.currentTimeMillis());
        
        try {
            return joinPoint.proceed();
        } finally {
            long cost = System.currentTimeMillis() - startTime.get();
            String methodName = joinPoint.getSignature().getName();
            
            // 记录慢查询
            if (cost > 1000) {
                log.warn("Slow SQL detected: {} - {}ms", methodName, cost);
                
                // 发送到监控系统
                Metrics.counter("sql.slow.query").increment();
            }
            
            // 记录性能指标
            Metrics.timer("sql.execute.time").record(cost, TimeUnit.MILLISECONDS);
            
            startTime.remove();
        }
    }
}

🛡️ 生产环境配置模板

# 生产环境MyBatis优化配置
mybatis:
  configuration:
    # 性能相关配置
    cache-enabled: true
    lazy-loading-enabled: true
    aggressive-lazy-loading: false
    multiple-result-sets-enabled: true
    use-column-label: true
    use-generated-keys: true
    
    # 执行器配置
    default-executor-type: REUSE
    default-statement-timeout: 30
    
    # 其他优化
    map-underscore-to-camel-case: true
    local-cache-scope: SESSION

# 数据源配置
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      leak-detection-threshold: 60000

📈 性能监控指标体系

🔚 总结与延伸

📚 优化要点回顾

1. SQL优化​​：索引、避免N+1、合理使用连接查询
2. ​​批量操作​​：BatchExecutor、foreach批量插入
3. 连接池​​：HikariCP参数调优、监控配置
4. 缓存策略​​：多级缓存、分布式缓存集成

🚀 持续优化建议

​​优化循环​​：监控 → 分析 → 优化 → 验证 → 持续改进