Sharding自定义分片策略

公司分库分表使用用户id，主键后3位拼接用户id后三位，现把相关分片规则自定义简易组件使用

一、参数配置

引用者可以配置主键字段与用户字段命名，配置分片日志记录等

package com.ypshengxian.shardingslice.properties;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;

/**
 * 描述：自动填充配置
 * Created by zjw on 2022/3/18 09:58
 */
@ConfigurationProperties(prefix = "ypsx.sharding-slice.auto-fill")
public class ShardingSliceAutoFillProperties {
    /**
     * 是否开启日志记录
     */
    public static boolean logEnabled = true;
    /**
     * 主键字段名, 数据库字段, 默认为id
     */
    public static String id = "id";
    /**
     * 用户字段名, 数据库字段, 默认为user_id
     */
    public static String userId = "user_id";
    /**
     * 库表是否以0开始
     * 例如：order_0, order_1
     * 反例：order_1, order_2
     */
    public static boolean isZeroStart = true;

    @Value("${logEnabled:true}")
    public void setLogEnabled(boolean logEnabled) {
        this.logEnabled = logEnabled;
    }

    @Value("${id:id}")
    public void setId(String id) {
        this.id = id;
    }

    @Value("${userId:user_id}")
    public void setUserId(String userId) {
        this.userId = userId;
    }

    @Value("${isZeroStart:true}")
    public void setIsZeroStart(boolean isZeroStart) {
        this.isZeroStart = isZeroStart;
    }

    public static String propertiesToString(){
        return "CartDO{" +
                "id=" + id +
                ", userId=" + userId +
                ", logEnabled=" + logEnabled +
                ", isZeroStart=" + isZeroStart +'\'' +
                '}';
    }
}

二、分片算法

需要实现ComplexKeysShardingAlgorithm接口

package com.ypshengxian.shardingslice.config;

import com.ypshengxian.shardingslice.properties.ShardingSliceAutoFillProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.util.*;
import java.util.stream.Collectors;

/**
 * 描述：sharding 分片策略(库表通用)
 * Created by zjw on 2022/3/16 12:42
 */
@Slf4j
public class ShardingSliceComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {


    public ShardingSliceComplexKeysShardingAlgorithm() {}

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
        if (ShardingSliceAutoFillProperties.logEnabled) {
            log.info("ShardingSliceAutoFillProperties:{}", ShardingSliceAutoFillProperties.propertiesToString());
        }
        if (!shardingValue.getColumnNameAndRangeValuesMap().isEmpty()) {
            return new HashSet<>(availableTargetNames);
        }
        if (ShardingSliceAutoFillProperties.logEnabled) {
            log.info("所有ShardingSliceComplexKeysShardingAlgorithm:{}", shardingValue.getColumnNameAndShardingValuesMap());
        }
        // 获取id
        Collection<Long> cartIds = shardingValue.getColumnNameAndShardingValuesMap().getOrDefault(ShardingSliceAutoFillProperties.id, new ArrayList<>(1));
        // 获取用户id
        Collection<Long> customerIds = shardingValue.getColumnNameAndShardingValuesMap().getOrDefault(ShardingSliceAutoFillProperties.userId, new ArrayList<>(1));

        // 整合id和用户id
        List<Long> ids = new ArrayList<>(16);
        ids.addAll(cartIds);
        ids.addAll(customerIds);

        List<String> shardingAlgorithm = ids.stream()
                // 对可用的表名求余数，获取到真实的表的后缀
                .map(idSuffix -> {
                    long suffix = idSuffix % 1000 % availableTargetNames.size();
                    return (ShardingSliceAutoFillProperties.isZeroStart) ? suffix : suffix + 1;
                })
                // 去重
                .distinct()
                // 转换成string
                .map(String::valueOf)
                // 获取到真实的表
                .map(tableSuffix -> availableTargetNames.stream().filter(targetName -> targetName.endsWith(tableSuffix)).findFirst().orElse(null))
                .filter(Objects::nonNull)
                .collect(Collectors.toList());
        if (ShardingSliceAutoFillProperties.logEnabled) {
            log.info("sharding slice result：{}", shardingAlgorithm);
        }

        return shardingAlgorithm;
    }

}

三、spring.factories启动扫描

org.springframework.boot.autoconfigure.EnableAutoConfiguration=\
com.ypshengxian.shardingslice.config.ShardingSliceAutoConfigure

四、使用配置

spring:
  profiles:
    active: dev
  shardingsphere:
    props:
      sql.show: true # 线上环境需要关闭
    datasource:
      names: ds0
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/yipin_cart?serverTimezone=Asia/Shanghai&autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
        username: root
        password: 123456
        maxActive: 100
        maxWait: 1000
    sharding:
      default-data-source-name: ds0
      tables:
        cart:
          actual-data-nodes: ds0.cart_$->{0..7}
          database-strategy:
            complex:
              sharding-columns: user_id
              algorithm-class-name: com.ypshengxian.shardingslice.config.ShardingSliceComplexKeysShardingAlgorithm
          table-strategy:
            complex:
              sharding-columns: id,user_id
              algorithm-class-name: com.ypshengxian.shardingslice.config.ShardingSliceComplexKeysShardingAlgorithm