MYSQL系列分库分表（三）：ShardingJDBC实现分库分表落地实践下

系列文档参考 MYSQL系列-整体架构介绍
紧接上文MYSQL系列-分库分表（三）：Sharding-JDBC实现分库分表落地实践-中

详细设计

关键技术点实现

支持读写分离，并且某些不能有主从延迟的SQL强制走写库查询

sharding-jdbc本身支持读写分离，相关说明可以参考官网

• 读写分离配置说明
• 读写分离JAVA API实现

yml文件配置如下

rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds1:
      staticStrategy:
        writeDataSourceName: ds_1
        readDataSourceNames:
          - r_ds_1
      loadBalancerName: coupon_db_random
    readwrite_ds2:
      staticStrategy:
        writeDataSourceName: ds_2
        readDataSourceNames:
          - r_ds_2
      loadBalancerName: coupon_db_random
  loadBalancers:
    coupon_db_random:
      type: RANDOM

针对某些需要走写库的查询SQL，Sharding提供了HintManager强制走写库，示例如下

@Test
public void selectByCouponCode() {
    List couponInfoList = couponInfoMapper.selectByCouponCode("1234567ABCD", "CN");
    Assert.assertTrue(couponInfoList.size() > 0);
    LOGGER.info("couponInfoList={}", couponInfoList);
    HintManager.clear();
    //设置完需要移除
    HintManager.getInstance().setWriteRouteOnly();
    couponInfoList = couponInfoMapper.selectByCouponCode("1234567ABCD", "CN");
    Assert.assertTrue(couponInfoList.size() > 0);
    LOGGER.info("couponInfoList={}", couponInfoList);
    HintManager.clear();
}

支持影子库

sharding-jdbc也支持影子库特性，具体参考官网-影子库
如果使用其特性，配置起来会比较麻烦
实际影子库是为了支撑全链路压测的，为了和现网实际业务隔离开，应该把所有的表都放在新的数据库即影子库上面

采用上下文带影子库标记，如果有则在路由算法地方将数据库偏移到对应的影子库

具体代码如下：
首先提供下上文处理类

public class Context {
    private static final ThreadLocal CONTEXT = new ThreadLocal() {
        @Override
        protected Context initialValue() {
            return new Context();
        }
    };

    /**
     * 是否影子库标记常量
     */
    private static final String SHADOW = "YC";

    /**
     * 影子库标记
     */
    private static final String IS_SHADOW = "1";
    
    private final Map parameters = new HashMap();
    private final Map localValues = new HashMap();
 
    ...
    public static void clear() {
        CONTEXT.remove();
    }

    public static void setShadow() {
        CONTEXT.get().parameters.put(SHADOW, IS_SHADOW);
    }

    public static boolean isShadow() {
        return IS_SHADOW.equals(CONTEXT.get().parameters.get(SHADOW));
    }

}

yml文件多配置一倍数据库实例作为影子库

dataSources:
  ds_1:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://172.17.108.172:3306/point_shard1?autoReconnect=true&characterEncoding=UTF-8&useUnicode=true&connectTimeout=3000&socketTimeout=3000
    username: root
    password: -
  ds_2:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://172.17.108.172:3306/point_shard2?autoReconnect=true&characterEncoding=UTF-8&useUnicode=true&connectTimeout=3000&socketTimeout=3000
    username: root
    password: -
  ds_3:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://172.17.108.172:3306/point_shard1?autoReconnect=true&characterEncoding=UTF-8&useUnicode=true&connectTimeout=3000&socketTimeout=3000
    username: root
    password: -
    initialSize: 0
    minIdle: 0
  ds_4:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://172.17.108.172:3306/point_shard2?autoReconnect=true&characterEncoding=UTF-8&useUnicode=true&connectTimeout=3000&socketTimeout=3000
    username: root
    password: -
    initialSize: 0
    minIdle: 0

- !SHARDING
  tables:
    point_balance:
      actualDataNodes: ds_${1..4}.point_balance${1..2}
      databaseStrategy:
        standard:
          shardingColumn: uid
          shardingAlgorithmName: d_uid_inline
      tableStrategy:
        standard:
          shardingColumn: uid
          shardingAlgorithmName: t_uid_inline
      auditStrategy:
        auditorNames:
          - sharding_key_required_auditor
        allowHintDisable: true

CodeDbSharding和UIDDbSharding获取分库索引时加上偏移量

@Override
protected long getMode(long mode) {
    if (Context.isShadow()) {
        mode += 2;
    }
    return mode;
}

实现效果类似如下

@Test
public void testShadow() {
    Context.setShadow();
    List couponInfoList = couponInfoMapper.selectByCouponCode("1234567ABCD", "CN");
    Assert.assertTrue(couponInfoList.size() > 0);
    Context.clear();
    couponInfoList = couponInfoMapper.selectByCouponCode("1234567ABCD", "CN");
    Assert.assertTrue(couponInfoList.size() > 0);
}

执行结果如下,第一次带上标记落在ds_4,没带上影子库标记落在ds_2

2023-09-24 22:09:05.101|INFO |demo|1|127.0.0.1|1091ceaed1fe445a83a7265a6ef84065|Actual SQL: ds_4 ::: select id,         coupon_code,         rev_uid,         create_time,         modify_time,         country        from coupon_info1 where coupon_code=? and country=? ::: [1234567ABCD, CN]|ShardingSphere-SQL