基于 SpringBoot + (ShardingSphere)Sharding-JDBC + Flyway + JOOQ + Gradle 进行水平分表的问题汇总

• Sharding-JDBC 的性能问题
• ShardingSphere 与 JOOQ 的版本兼容问题
• 多数据源与 Flyway 的结合问题
• Sharding-JDBC 与 JOOQ 的兼容问题
• 多数据源下的 DSLContext 配置
• 多数据源下的言七墨事务问题
• 数据迁移问题
• …

上面提到的几个问题，是我在进行分表之前及测试过程中遇到的几个问题，下面一一进行解决。

请参考：Sharding-JDBC VS JDBC 性能测试

默认情况下，JOOQ的语法解析成SQL会是下面这个样子：

select `message`.`id`,  `message`.`session_id`, `message`.`content`, `message`.`status`, `message`.`created`, `message`.`updated` from `message` where `message`.`session_id` = ?

但是，compile group: 'org.apache.shardingsphere', name: 'sharding-jdbc-spring-boot-starter', version: '4.0.0-RC1'对上面这种字段前缀带有表名的SQL兼容性不好，比如，在路由的时候，上面的SQL会被路由成下面这个https://qimok.cn样子：

select `message`.`id`,  `message`.`session_id`, `message`.`content`, `message`.`status`, `message`.`created`, `message`.`updated` from `message_10` where `message_10e`.`session_id` = ?

可以看到，where message_10e.session_id中被莫名拼接了个字符e，最后将shardingsphere的版本升级到4.0.1，问题得以解决。(不过也有其它解决办法，比如通过JOOQ的配置将字段前的表名去掉)

以下是gradle的配置：

// 如果使用了多个数据源，需要明确指出 Flyway 使用哪个数据源
flyway {
    url = 'jdbc:mysql://110.gz.cdb.myqcloud.com:3450/sharding_jdbc_test?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true'
    locations = ['filesystem:src/main/resources/db/migration']
    user = 'root'
    password = '123456'
    schemas = ['sharding_jdbc_test']
}

谷歌、百度了很多技术博客，又翻了很多github上的开源代码，Sharding-JDBC与JOOQ结合的文章或代码太少了，在一些社群也听到过说，Sharding-JDBC与JOOQ兼容的不是特别好，具体怎么不好，有可能是ShardingSphere 与 JOOQ 的版本兼容问题提到的问题，也有可能是别的问题，但是也没有详细列出，只能自己动手测试，可参考：Sharding-JDBC 与 JOOQ 的兼容性测试

多数据源下，使用DSLContext进行操作时，需要显式指定数据源：

// 非分片表
@Primary
@Bean(name = "noShardDsl")
@Qualifier("noShardDsl")
public DSLContext noShardDsl(@Qualifier("getNoShardingDataSource") DataSource getNoShardingDataSource) {
    return DSL.using(new DefaultConfiguration()
            .set(getNoShardingDataSource)
            .set(SQLDialect.MYSQL));
}

// 分片表
@Bean(name = "shardDsl")
@Qualifier("shardDsl")
public DSLContext shardDsl(@Qualifier("getShardingDataSource") DataSource getShardingDataSource) {
    return DSL.using(new DefaultConfiguration()
            .set(getShardingDataSource)
            .set(new Settings().withRenderSchema(false)) // 去掉 SQL 字段前的 schema，否则不会进行 SQL 路由（SQL 路由默认的 schema 应该是显式指定的分片数据源）
            .set(SQLDialect.MYSQL));
}

首先，为什么会出现这个问题，需要先搞清楚在多数据源情况下，如果加了Spring事务，不能动态切换数据源的原因：

public Connection getConnection() throws SQLException {
    // 通过数据源获取连接
    // 比如我们配置了多数据源，此时还会正常切换
    if (this.connection == null) {
        openConnection();
    }
    return this.connection;
}

我们看openConnection()方法，它的作用是从数据源中获取一个Connection连接。如果我们配置了多数据源，此时是可以正常切换的。如果加了事务，之所以没有切换数据源，是因为第二次调用时，this.connection != null，返回的还是上一次的连接。这是因为，在第二次获取SqlSession的时候，当前线程是从ThreadLocal中拿到的，所以不会重复获取Connection连接，故在事务内，是无法动态切换数据源的。

那么，同一事务内，如果即包含对分片表的操作，又包含对非分片表的操作，如何保证事务？

1. 统一使用分片的事务管理器【@Transactional(“shardingTransactionManager”)】和 分片的 DSL【@Resource(name = “shardDsl”) private final DSLContext shardDsl;】
2. 可能出现的问题：

• 当使用分言七墨片的数据源（DSL）操作的时候，非分片的表操作有可能出现 SQL 不兼容的问题。此时，可以通过 SQL 改写完成，如果无法改写，或者业务也无法变更，可以考虑通过AOP + 两阶段提交保证事务（考虑到性能，也可根据具体的业务言七墨场景，自己去实现分布式事务，从而保证最终一致性或强一致性），AOP + 两阶段提交具体代码可参见：MultiDataSourceTransactionAspect.java

// 非分片表的事务配置
@Bean(name = "noShardingTransactionManager")
@Primary
public PlatformTransactionManager noShardingTransactionManager(@Qualifier("getNoShardingDataSource")
                                                                       DataSource getNoShardingDataSource) {
    return new DataSourceTransactionManager(getNoShardingDataSource);
}

// 分片表的事务配置
@Bean(name = "shardingTransactionManager")
public PlatformTransactionManager shardingTransactionManager(@Qualifier("getShardingDataSource")
                                                                         DataSource getShardingDataSource) {
    return new DataSourceTransactionManager(getShardingDataSource);
}

将原来的大表数据迁移到各个分表，如何一步到位呢？请参考：DataMigrate 项目的 DataMigrateSubTableExecutingService.java

• 基于 SpringBoot + ShardingSphere（Sharding-JDBC）+ Flyway + JOOQ + Gradle 的水平分表测试项目
• 数据迁移项目