ShardingSphere-分库分表

lijunyi2023-02-02ShardingSphereShardingSphere

分库分表代码示例

4.x官网配置示例open in new window

一般yml配置

spring:
  shardingsphere:
    #显示sql
    props:
      sql:
        show: true
    #配置数据源
    datasource:
      # 取别名
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://115.236.38.101:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: admin123
        maxPoolSize: 100
        minPoolSize: 5
      #配置从库ds2
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://115.236.38.120:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: admin123
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      default-data-source-name: ds1
      #配置分表的规则
      tables:
        #ksd_user 逻辑表名,库中实际不存在
        ksd_user:
          #数据节点:数据源$->{0..N}.逻辑表名$->{0..N}
          actual-data-nodes: ds$->{0..1}.ksd_user$->{0..1}
          # 拆分库策略，也就是什么样的数据放到哪个数据库中
          database-strategy:
            inline:
              sharding-column: age
              algorithm-expression: ds$->{age % 2}
          table-strategy:
            inline:
              sharding-column: age #分片字段（分片键）
              algorithm-expression: ksd_user$->{age % 2} # 分片算法表达式
#需求:对用户1000w的数据进行分表和分库的操作。
#根据年龄单数存储在ksd_userl 偶数ksd_user0;同时age单数存储在ds1偶数ds0中

自定义分片策略处理类

public class BirthdayAlgorithm implements PreciseShardingAlgorithm<Date>
{
    List<Date> dateList = new ArrayList<>();
    {
        Calendar calendar2 = Calendar.getInstance();
        calendar2.set(2021,1,1,0,0,0);
        Calendar calendar3 = Calendar.getInstance();
        calendar3.set(2022,1,1,0,0,0);
        // 数据源有几个，这里就写几个，否则下面循环会报错
        dateList.add(calendar2.getTime());
        dateList.add(calendar3.getTime());
    }

    /**
     * 进行切分
     *
     * @param collection           数据源集合(ds0 ds1)
     * @param preciseShardingValue 精确的分片值
     * @return {@link String}
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        // 获取属性数据库的值
        Date date = preciseShardingValue.getValue();
        // 获取数据源的名称信息列表
        Iterator<String> iterator = collection.iterator();
        String target = null;
        for (Date s : dateList)
        {
            target = iterator.next();
            // 如果数据晚于指定的日期直接返回
            if (date.before(s))
            {
                break;
            }
        }
        return target;
    }
}

yml指定分片策略

spring:
  shardingsphere:
    #显示sql
    props:
      sql:
        show: true
    #配置数据源
    datasource:
      # 取别名
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://115.236.38.101:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: admin123
        maxPoolSize: 100
        minPoolSize: 5
      #配置从库ds2
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://115.236.38.120:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: admin123
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      default-data-source-name: ds1
      #配置分表的规则
      tables:
        #ksd_user 逻辑表名,库中实际不存在
        ksd_user:
          #数据节点:数据源$->{0..N}.逻辑表名$->{0..N}
          actual-data-nodes: ds$->{0..1}.ksd_user$->{0..1}
          # 拆分库策略，也就是什么样的数据放到哪个数据库中
          database-strategy:
            standard:
              shardingColumn: year
              preciseAlgorithmClassName: com.example.shardingjdbc.config.BirthdayAlgorithm
          table-strategy:
            inline:
              sharding-column: age #分片字段（分片键）
              algorithm-expression: ksd_user$->{age % 2} # 分片算法表达式

按年月分库分表

自定义按年月分库分表分片算法

public class YearMonthShardingAlgorithm implements PreciseShardingAlgorithm<String>
{
    private static final String SPLITTER = "_";
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        String tbName = preciseShardingValue.getLogicTableName() + "_" + preciseShardingValue.getValue();
        System.out.println("Sharding input :" + preciseShardingValue.getLogicTableName() + ", output:{}" + tbName);
        return tbName;
    }
}

yml配置

# 按年月分库分表实现
spring:
  shardingsphere:
    #显示sql
    props:
      sql:
        show: true
    #配置数据源
    datasource:
      # 取别名
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://115.236.38.101:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: admin123
        maxPoolSize: 100
        minPoolSize: 5
      #配置从库ds2
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://115.236.38.120:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: admin123
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      default-data-source-name: ds0
      #配置分表的规则
      tables:
        ksd_user_order:
          #数据节点:数据源$->{0..N}.逻辑表名$->{0..N}
          actual-data-nodes: ds$->{0..1}.ksd_user_order$->{2021..2022}${(1..3).collect{t -> t.toString().padLeft(2,'0')}
          key-generator:
            column: orderid
            type: SNOWFLAKE
          database-strategy:
            inline:
              sharding-column: orderid
              algorithm-expression: ds$->{orderid % 2}
          table-strategy:
            standard:
              shardingColumn: yearmonth
              preciseAlgorithmClassName: com.example.shardingjdbc.config.YearMonthShardingAlgorithm
#            inline:
#              sharding-column: yearmonth
#              algorithm-expression: ksd_user_order_$->{yearmonth} # 分片算法表达式

分布式主键配置

ShardingSphere 提供灵活的配置分布式主键生成策略方式。在分片规则配置模块克配置每个表的主键生成策略。默认使用雪花算法。(snowflake)生成64bit的长整型数据。支持两种方式配置(注意：主键列不能自增长，数据类型是: bigint(20) ):

snowflake
UUID

yml配置

spring:
  shardingsphere:
    sharding:
      tables:
        ksd_user:
          # 分布式主键的设置
          key-generator:
            # 主键的列名
            column: id
            type: SNOWFLAKE

分页和统计

public interface UserDao{

    /**
     * 用户分页
     *  ksd_user  对应yml的逻辑表
     * @param pageNo   页面
     * @param pageSize 页面大小
     * @return {@link List<UserModel>}
     */
    @Select("select * from ksd_user limit #{pageNo},#{pageSize}")
    List<UserModel> userLimit(@Param("pageNo") Integer pageNo,@Param("pageSize") Integer pageSize);

    /**
     * 数用户
     * shardingJdbc 会把你规则的所有可能性形成一个笛卡尔积
     * @return int
     */
    @Select("select count(1) from ksd_user")
    int countUser();

}

ShardingSphere-分库分表

ON THIS PAGE