基于shardingsphere实现分库分表

数据按某个关键字段做分库分表，比如按userid，此处以userId为例，分为16个库（1-16），16个表（1-16）。

分库分表逻辑为：

分库：userId% 16+1（此处1-16所以要加1，如果不加1就是0-15）

分表：CRC32(userId) % 16 + 1

添加maven依赖

<!-- 依赖sharding 分库分表 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-core-common</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.2.1</version>
</dependency>

配置多数据源

spring:
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss
  mybatis-plus:
    configuration:
      # 这个配置会将执行的sql打印出来，在开发或测试的时候可以用
      log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  autoconfigure:
    exclude:
      - com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
  # 迁移需要链接的数据库
  datasource:
    dynamic:
      primary: slave
      datasource:
        slave:
          url: jdbc:mysql://xxx:63306/gac_order?useUnicode=true&characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull
          username: xxx
          password: xxx
          type: com.alibaba.druid.pool.DruidDataSource
          druid:
            #initialSize: 30
            #maxActive: 20
            minIdle: 5
            maxWait: 1000
            minEvictableIdleTimeMillis: 300000
            useGlobalDataSourceStat: true
            timeBetweenEvictionRunsMillis: 60000
            validationQuery: select 'x'
            testOnBorrow: true
            testOnReturn: true
            testWhileIdle: true
        write:
          url: jdbc:mysql://xxx:63306/gac_order?useUnicode=true&characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull
          username: xxx
          password: xxx
          type: com.alibaba.druid.pool.DruidDataSource
          druid:
            #initialSize: 30
            #maxActive: 20
            minIdle: 5
            maxWait: 1000
            minEvictableIdleTimeMillis: 300000
            useGlobalDataSourceStat: true
            timeBetweenEvictionRunsMillis: 60000
            validationQuery: select 'x'
            testOnBorrow: true
            testOnReturn: true
            testWhileIdle: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql.show: true
    # 配置数据源
    datasource:
      names: ds1,ds2,ds3     
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://xxx:63306/order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://xxx:63306/order_2?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://xxx:63306/order_3?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      
    sharding:
      tables:
        order_info:
          actual-data-nodes: ds$->{1..3}.order_info_$->{1..3}
          table-strategy:
            standard:
              sharding-column: user_id
              precise-algorithm-class-name: com.com.xxx.sharding.algorithm.table.PreciseShardingUserIdAlgorithm
          database-strategy:
            standard:
              sharding-column: user_id
              precise-algorithm-class-name: com.xxx.sharding.algorithm.db.DbPreciseShardingUserIdAlgorithm
        order_info_ext:
          actual-data-nodes: ds$->{1..3}.order_info_ext_$->{1..3}
          table-strategy:
            standard:
              sharding-column: user_id
              precise-algorithm-class-name: com.xxx.sharding.algorithm.table.PreciseShardingUserIdAlgorithm
          database-strategy:
            standard:
              sharding-column: user_id
              precise-algorithm-class-name: com.xxx.sharding.algorithm.db.DbPreciseShardingUserIdAlgorithm
      # 设置绑定表 (必须按相同分片健进行分片的表才能互为成绑定表，在联合查询时就能避免出现笛卡尔积查询)
      binding-tables: order_info, order_info_ext

编写分库工具类

public class DbPreciseShardingUserIdAlgorithm implements PreciseShardingAlgorithm<Long> {
    private final static int SHARDING_BASE = 16;
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        Long userId = preciseShardingValue.getValue();
        log.info("preciseSharding db start, userId:{}", userId);
        String useDb = null;
        String keyStr=String.valueOf(userId % SHARDING_BASE + 1);
        if (Objects.nonNull(userId)) {
            for (String key : collection) {
                if (key.endsWith(keyStr)) {
                    log.info("key-result:{}", key);
                    useDb = key;
                    break;
                }
            }
        }
        log.info("ShardingUserId use db: {}", useDb);
        return useDb;
    }
}

 编写分表工具类

@Slf4j
public class PreciseShardingUserIdAlgorithm implements PreciseShardingAlgorithm<Long> {
    private final static int SHARDING_BASE = 16;


    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        Long userId = preciseShardingValue.getValue();
        CRC32 crc32 = new CRC32();
        crc32.update(String.valueOf(userId).getBytes());
        log.info("preciseSharding table start, userId:{}, crc32UserId:{}", userId, crc32.getValue());
        String useTable = null;
        //此处也可以不用crc32，也可以用(userId/ SHARDING_BASE)% SHARDING_BASE+1
        String keyStr = String.valueOf(crc32.getValue() % SHARDING_BASE + 1);
        if (Objects.nonNull(userId)) {
            for (String key : collection) {
                if (key.endsWith(keyStr)) {
                    useTable = key;
                    break;
                }
            }
        }
        log.info("ShardingUserId use table: {}", useTable);
        return useTable;
    }
}

多数分库分表的时候涉及到数据迁移，可以写一个单独的程序做全量数据迁移

然后新数据通过双写存入旧库和分库

采取灰度方式，实现先双写读旧，双写读新，单写读新的过渡