Spring Boot 3 + MyBatis-Plus + SelectDB整合方案

关注

原创 2025-09-22 00:10:52 415 阅读 AI 写同款· GEO 优化 ›

一次实时数据链路丢数排查：从 MQTT 队列积压到 SQL 统计开销本文通过一次实时数据链路压测，排查了从MQTT到ClickHouse的端到端数据丢失问题。核心方法是为每条消息添加序号，建立独立观察点（MQTT订阅端、Kafka消费者），结合转发服务阶段统计与线程栈分析，定位到下游处理速度不足源于Druid SQL合并统计的高开销。优化措施包括关闭SQL合并、扩容代理队列、增强生产侧失败处理，并验证15分钟内实现全链路对账一致，显著缓解积压与丢包。关键收获：数据可对账、明确各阶段“成功”定义、关注客户端队列状态。阅读详情

生产级Spring Boot 3 + MyBatis-Plus + SelectDB整合方案，满足高并发、高可用、高性能要求的完整实现：

pom.xml


<?xml version="1.0" encoding="UTF-8"?>
<project>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.5</version>
    </parent>
    <dependencies>
        <!-- SelectDB核心依赖 -->
        <dependency>
            <groupId>com.selectdb</groupId>
            <artifactId>selectdb-jdbc-driver</artifactId>
            <version>2.0.12</version>
        </dependency>
        
        <!-- Spring Boot基础组件 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        
        <!-- MyBatis-Plus增强组件 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.2</version>
        </dependency>
        
        <!-- 连接池与监控 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.18</version>
        </dependency>
        
        <!-- 其他生产级组件 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-cache</artifactId>
        </dependency>
        <dependency>
            <groupId>org.redisson</groupId>
            <artifactId>redisson-spring-boot-starter</artifactId>
            <version>3.23.2</version>
        </dependency>
    </dependencies>
</project>


application.yml


spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://selectdb-cluster:9030/prod_db?useSSL=false&useServerPrepStmts=true
    username: ${SELECTDB_USER}
    password: ${SELECTDB_PWD}
    driver-class-name: com.mysql.jdbc.Driver
    druid:
      initial-size: 5
      max-active: 50
      min-idle: 5
      max-wait: 60000
      validation-query: SELECT 1
      test-while-idle: true
      time-between-eviction-runs-millis: 60000

mybatis-plus:
  configuration:
    cache-enabled: true
    default-executor-type: REUSE
  global-config:
    db-config:
      logic-delete-field: deleted
      logic-not-delete-value: 0
      logic-delete-value: 1

management:
  endpoints:
    web:
      exposure:
        include: health,metrics,prometheus



@Configuration
@EnableCaching
public class SelectDBConfig {
    
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

    @Bean
    public RedissonClient redissonClient() {
        Config config = new Config();
        config.useSingleServer()
              .setAddress("redis://redis-cluster:6379")
              .setConnectionMinimumIdleSize(10)
              .setConnectionPoolSize(64);
        return Redisson.create(config);
    }

    @Bean
    public CacheManager cacheManager(RedissonClient redissonClient) {
        return new RedissonSpringCacheManager(redissonClient);
    }
}



public interface BaseMapperExt<T> extends BaseMapper<T> {
    
    @Select("${sql}")
    List<Map<String, Object>> selectByDynamicSQL(@Param("sql") String sql);

    @Update("${sql}")
    int executeUpdate(@Param("sql") String sql);
    
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("${sql}")
    int executeInsert(@Param("sql") String sql);
}



@Service
@Slf4j
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
    
    @Autowired
    private RedissonClient redissonClient;
    
    @Cacheable(value = "userCache", key = "#id")
    @Override
    public User getByIdWithCache(Long id) {
        return getById(id);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean batchInsertWithPipe(List<User> users) {
        RLock lock = redissonClient.getLock("batch_insert_lock");
        try {
            lock.lock(10, TimeUnit.SECONDS);
            // 使用SelectDB Pipe特性批量写入
            String pipeSQL = "INSERT INTO user VALUES " + 
                users.stream()
                    .map(u -> String.format("(%d,'%s')", u.getId(), u.getName()))
                    .collect(Collectors.joining(","));
            return baseMapper.executeUpdate(pipeSQL) > 0;
        } finally {
            lock.unlock();
        }
    }
}



@Component
public class SelectDBHealthIndicator implements HealthIndicator {
    
    @Autowired
    private DataSource dataSource;

    @Override
    public Health health() {
        try (Connection conn = dataSource.getConnection()) {
            boolean valid = conn.isValid(5);
            return valid ? Health.up().build() : 
                   Health.down().withDetail("error", "Connection test failed").build();
        } catch (Exception e) {
            return Health.down(e).build();
        }
    }
}

代码说明：