基于Sharding Sphere 4.1.1生产过程中动态建表并自动刷新Node

本文基于JDK17和sharding sphere 4.1.1版本实现，主要解决sharding sphere不提供动态建表功能，需要事先创建好所有的分表。如果数据库存在大量无用的空表，在SQL无法命中分表字段的前提下sharding会触发所有分表查询，这样是程序上和效率上都是无法接受的。如果不事先建立分表，查询命中未建立的分就会报数据库异常。diss死他，sharding缺少这样的功能实在是太难受了。

1. 说明

此方案支持dynamic-datasource、sharding-sphere-jdbc和mybatis-plus等框架操作数据库。

同时在sharding-sphere-jdbc框架基础上增强了分库分表功能，主要功能为：

• 增加单字段精确分表基础类。
• 增加单字段区域分表基础类。
• 增加复合字段精确分表基础类。
• 强制分表基础类。

在上述基础类的的基础上扩展了在程序运行时无须提前创建子表，所有子表都是在程序运行根据自定义分表算法动态创建的，在分布式集群中程序定时扫描数据库中表实时刷新sharding的缓存信息。从而弥补sharding的不足，也实现了在集群环境下的高可用。下面介绍一下功能核心类：

sharding jdbc 自动装配
实现将核心控制加载并随着项目一起启动。

@Configuration
@AutoConfigureAfter({DataSourceConfig.class, DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class ShardingConfigure {

    @Lazy
    @Resource(name = "shardingDataSource")
    private AbstractDataSourceAdapter shardingDataSource;

    @Bean
    public AutoConfigDataNodes createAutoConfigDataNodes() throws Exception {
        AutoConfigDataNodes autoConfigDataNodes = new AutoConfigDataNodes(shardingDataSource);
        return autoConfigDataNodes;
    }
}

动态数据源与Sharding集成

@Configuration
@ComponentScan(basePackages = {"org.apache.shardingsphere.shardingjdbc"})
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
@ConditionalOnProperty(prefix = "spring.shardingsphere",
        name = {"enabled"},
        havingValue = "true",
        matchIfMissing = true)
public class DataSourceConfig {

    @Value("${spring.shardingsphere.enabled:true}")
    private boolean shardingEnabled;

    @Value("${spring.datasource.dynamic.primary:}")
    private String primaryDataSource;

    @Resource
    private DynamicDataSourceProperties properties;

    @Lazy
    @Resource(name = "shardingDataSource")
    private AbstractDataSourceAdapter shardingDataSource;

    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                if (shardingEnabled) {
                    dataSourceMap.put("sharding-data-source", shardingDataSource);
                }
                return dataSourceMap;
            }
        };
    }

    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(properties.getPrimary());
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setP6spy(properties.getP6spy());
        dataSource.setSeata(properties.getSeata());
        if (StringUtils.isBlank(primaryDataSource) && shardingEnabled) {
            dataSource.setPrimary("sharding-data-source");
        }
        return dataSource;
    }
}

核心控制类
实现了将数据库中所有的表定时同步刷新到sharding缓存中，并随着开者自定义算法动态创建表和刷新缓存。

注：此类为10秒从数据库中同步一次表，并不是真正意义上的实时，如果有比较高的需求可将同步时间缩短，或使用消息通知和分布式锁等技术自行实现。

@Slf4j
public class AutoConfigDataNodes {

    private final AbstractDataSourceAdapter dataSource;

    private ShardingRuntimeContext runtimeContext;

    private ScheduledExecutorService executorService = new ScheduledThreadPoolExecutor(1,
            new BasicThreadFactory.Builder().namingPattern("sharding-schedule-pool-%d").daemon(true).build());

    public AutoConfigDataNodes(AbstractDataSourceAdapter dataSource) {
        log.info("开启sharding分表配置任务");
        this.dataSource = dataSource;
        executorService.scheduleWithFixedDelay(() -> run(), 0, 10, TimeUnit.SECONDS);
    }

    public void run() {
        log.debug("进行自动更新sharding中物理表配置");
        ShardingRuntimeContext runtimeContext = getRuntimeContext();
        Collection<MasterSlaveRule> ruleCollection = runtimeContext.getRule().getMasterSlaveRules();
        MasterSlaveRule masterSlaveRule = ruleCollection.stream().findFirst().get();
        String nodeName = masterSlaveRule.getRuleConfiguration().getName();
        Set<String> tablesInDBSet = queryTables();

        List<TableRule> tableRuleList = (List<TableRule>) runtimeContext.getRule().getTableRules();
        for (TableRule tableRule : tableRuleList) {
            refreshTableRule(tableRule, nodeName, tablesInDBSet);
            refreshShardingAlgorithm(tableRule, nodeName);
        }
    }

    /**
     * 刷新sharding缓存
     *
     * @param tableRule
     * @param nodeName
     * @param tablesInDBSet
     * @return: void
     * @author: 幸福的小雨
     * @time: 2023/6/13 10:09
     */
    protected void refreshTableRule(TableRule tableRule, String nodeName, Set<String> tablesInDBSet) {
        // sharding缓存的表名，目前不知道哪里再用，先当缓存吧
        Set<String> actualTableSets = getActualTables(tableRule);
        // 刷新分库分表时的缓存
        List<String> newList = matchNewList(actualTableSets, tablesInDBSet);
        setDatasourceToTablesMap(tableRule, nodeName, newList);
    }

    /**
     * 刷新分片算法内的属性
     *
     * @param tableRule
     * @param nodeName
     * @return: void
     * @author: 幸福的小雨
     * @time: 2023/6/13 11:33
     */
    protected void refreshShardingAlgorithm(TableRule tableRule, String nodeName) {
        // 获取分库分表时真正使用的表名
        Map<String, Set<String>> datasourceToTablesMap = getDatasourceToTablesMap(tableRule);
        Set<String> tables = datasourceToTablesMap.get(nodeName);
        ShardingStrategy shardingStrategy = tableRule.getTableShardingStrategy();
        if (shardingStrategy instanceof ComplexShardingStrategy) {
            ShardingAlgorithm algorithm = getObjectField(shardingStrategy, "shardingAlgorithm");
            setValueToBaseAlgorithm(tableRule, algorithm, nodeName, tables);
        } else if (shardingStrategy instanceof HintShardingStrategy) {
            ShardingAlgorithm algorithm = getObjectField(shardingStrategy, "shardingAlgorithm");
            setValueToBaseAlgorithm(tableRule, algorithm, nodeName, tables);
        } else if (shardingStrategy instanceof StandardShardingStrategy) {
            ShardingAlgorithm preciseAlgorithm = getObjectField(shardingStrategy, "preciseShardingAlgorithm");
            setValueToBaseAlgorithm(tableRule, preciseAlgorithm, nodeName, tables);
            ShardingAlgorithm rangeAlgorithm = getObjectField(shardingStrategy, "rangeShardingAlgorithm");
            setValueToBaseAlgorithm(tableRule, rangeAlgorithm, nodeName, tables);
        }
    }

    /**
     * 向基础类中设备必要的参数
     *
     * @param algorithm
     * @param tables
     * @return: void
     * @author: Shuai.Zhang 210744334
     * @time: 2023/6/13 13:27
     */
    private void setValueToBaseAlgorithm(TableRule tableRule, ShardingAlgorithm algorithm, String nodeName, Set<String> tables) {
        if (algorithm != null && algorithm instanceof BaseShardingAlgorithm) {
            BaseShardingAlgorithm baseShardingAlgorithm = (BaseShardingAlgorithm) algorithm;
            baseShardingAlgorithm.setLogicTable(tableRule.getLogicTable());
            baseShardingAlgorithm.setTables(tables);
            baseShardingAlgorithm.setTableRule(tableRule);
            baseShardingAlgorithm.setNodeName(nodeName);
        }
    }

    private List<String> matchNewList(Set<String> set1, Set<String> set2) {
        List<String> newList = new ArrayList<>();
        for (String s1 : set1) {
            for (String s2 : set2) {
                if (StringUtils.equals(s1, s2)) {
                    newList.add(s2);
                }
            }
        }
        return newList;
    }

    /**
     * 从存储中查询出所有的表
     *
     * @return: java.util.Set<java.lang.String>
     * @author: 幸福的小雨
     * @time: 2023/6/13 13:25
     */
    protected Set<String> queryTables() {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        Set<String> tables = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery("select tablename from pg_tables t where t.schemaname = 'public'");
            tables = new LinkedHashSet<>();
            while (rs.next()) {
                tables.add(rs.getString(1));
            }
        } catch (SQLException e) {
            log.error("获取数据库连接失败！", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error("关闭数据连接失败", e);
            }
        }
        return tables;
    }

    protected boolean createTable(TableRule tableRule, String nodeName, Collection<String> tableNames) {
        if (CollectionUtils.isEmpty(tableNames)) {
            return false;
        }
        // 检查表的合法性
        Set<String> tableSets = getActualTables(tableRule);
        for (String tableName : tableNames) {
            if (!CollectionUtils.containsAny(tableSets, tableName)) {
                final String exec = MessageFormat.format("[{0}]表名称不合法", tableNames);
                throw new ValidationException(exec);
            }
        }

        // 以下为创建表和刷新缓存
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = dataSource.getConnection();
            List<String> list = new LinkedList<>();
            for (String tableName : tableNames) {
                try {
                    final String sql = MessageFormat.format("CREATE TABLE {0} (LIKE {1})", tableName, tableRule.getLogicTable());
                    statement = conn.prepareStatement(sql);
                    int rs = statement.executeUpdate();
                    log.info("创建[{}]表, 状态 : {}", tableName, rs);
                } catch (SQLException e) {
                    if ("42P07".equals(e.getSQLState())) {
                        log.warn("数据库中存在{}表，程序将自动加入到缓存中。", tableNames);
                    } else {
                        throw e;
                    }
                }
                list.add(tableName);
            }
            addDatasourceToTablesMap(tableRule, nodeName, list);
            Set<String> tables = getDatasourceToTablesMap(tableRule).get(nodeName);
            refreshTableRule(tableRule, nodeName, tables);
            return true;
        } catch (SQLException e) {
            log.error("获取数据库连接失败！", e);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error("关闭数据连接失败", e);
            }
        }

        return false;
    }

    protected static <T> T getObjectField(Object object, String fieldName) {
        try {
            Field field = object.getClass().getDeclaredField(fieldName);
            field.setAccessible(true);
            return (T) field.get(object);
        } catch (NoSuchFieldException e) {
            log.error("因为sharding版本问题", e);
        } catch (IllegalAccessException e) {
            log.error("因为sharding版本问题", e);
        }
        return null;
    }

    /**
     * 获取sharing运行时的上下文
     * 这个方法太坑了，项目启动时不能在主线程中使用，会产生循环依赖问题（试了各种办法），最后神奇的是放到子线程里就OK了
     *
     * @return: org.apache.shardingsphere.shardingjdbc.jdbc.core.context.ShardingRuntimeContext
     * @author: 幸福的小雨
     * @time: 2023/6/13 11:34
     */
    protected ShardingRuntimeContext getRuntimeContext() {
        try {
            if (runtimeContext == null) {
                Method getRuntimeContextMethod = dataSource.getClass().getDeclaredMethod("getRuntimeContext");
                getRuntimeContextMethod.setAccessible(true);
                runtimeContext = (ShardingRuntimeContext) getRuntimeContextMethod.invoke(dataSource, null);
            }
        } catch (IllegalAccessException e) {
            log.error("因为sharding版本问题", e);
        } catch (InvocationTargetException e) {
            log.error("因为sharding版本问题", e);
        } catch (NoSuchMethodException e) {
            log.error("因为sharding版本问题", e);
        }
        return runtimeContext;
    }

    protected Set<String> getActualTables(TableRule tableRule) {
        Set<String> tables = getObjectField(tableRule, "actualTables");
        return tables == null ? new LinkedHashSet<>() : tables;
    }

    protected Map<DataNode, Integer> getDataNodeIndexMap(TableRule tableRule) {
        Map<DataNode, Integer> nodeMap = getObjectField(tableRule, "dataNodeIndexMap");
        return nodeMap == null ? new HashMap<>(0) : nodeMap;
    }

    protected Map<String, Set<String>> getDatasourceToTablesMap(TableRule tableRule) {
        Map<String, Set<String>> tablesMap = getObjectField(tableRule, "datasourceToTablesMap");
        return tablesMap == null ? new HashMap<>(0) : tablesMap;
    }

    protected void setDatasourceToTablesMap(TableRule tableRule, String nodeName, List<String> newTableList) {
        synchronized (tableRule) {
            // 获取分库分表时真正使用的表名
            Map<String, Set<String>> datasourceToTablesMap = getDatasourceToTablesMap(tableRule);
            Set<String> tables = datasourceToTablesMap.get(nodeName);
            Collections.sort(newTableList);
            tables.clear();
            tables.addAll(newTableList);
        }
    }

    protected void addDatasourceToTablesMap(TableRule tableRule, String nodeName, List<String> tablesList) {
        List<String> list = new ArrayList<>();
        synchronized (tableRule) {
            // 获取分库分表时真正使用的表名
            Map<String, Set<String>> datasourceToTablesMap = getDatasourceToTablesMap(tableRule);
            Set<String> tables = datasourceToTablesMap.get(nodeName);
            list.addAll(tables);
            list.addAll(tablesList);
            Collections.sort(list);
            tables.clear();
            tables.addAll(list);
        }
    }

}

基础分库分表算法类
此类实现与核心控制类的交互与表名的校验和创建。

@Setter
@Getter
@Slf4j
public abstract class BaseShardingAlgorithm {

    /**
     * 数据库内所有表
     */
    private Set<String> tables;

    /**
     * 数据节点名称
     */
    private String nodeName;

    /**
     * 逻辑表名
     */
    private String logicTable;

    /**
     * 表的权限缓存
     */
    private TableRule tableRule;

    /**
     * 自动配置数据节点类
     */
    private AutoConfigDataNodes autoConfigDataNodes;

    public BaseShardingAlgorithm() {
        this.autoConfigDataNodes = ApplicationContextUtils.getBean(AutoConfigDataNodes.class);
    }

    /**
     * 动态获取表，如果表不在数据库中在数据中则创建该表
     *
     * @param tableNames
     * @return: java.lang.String
     * @author: 幸福的小雨
     * @time: 2023/6/12 10:40
     */
    protected boolean checkTable(Collection<String> tableNames) {
        if (CollectionUtils.isEmpty(tableNames)) {
            return false;
        }
        // 执行合法的业务流程
        if (tables == null) {
            autoConfigDataNodes.createTable(tableRule, nodeName, tableNames);
        }
        List<String> list = new ArrayList<>();
        for (String tableName : tableNames) {
            if (!CollectionUtils.containsAny(tables, tableName)) {
                list.add(tableName);
            }
        }
        autoConfigDataNodes.createTable(tableRule, nodeName, list);
        return true;
    }

    protected Set<String> getTables() {
        return tables;
    }
}

精确单字段分片基础类
用于替代PreciseShardingAlgorithm接口，实现sharding算法接口和基础分库分表算法类的交互，抽象一个用于子类实现的doSharding算法接口。

public abstract class BasePreciseShardingAlgorithm<T extends Comparable<?>> extends BaseShardingAlgorithm implements PreciseShardingAlgorithm<T> {

    /**
     * sharding分库分表基础类，处理框架在发送查询请求时的表
     *
     * @param availableTargetNames
     * @param shardingValue
     * @return: java.lang.String
     * @author: 幸福的小雨
     * @time: 2023/6/12 10:09
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<T> shardingValue) {
        final String tableName = doSharding(getLogicTable(), availableTargetNames, shardingValue);
        checkTable(Collections.singletonList(tableName));
        return tableName;
    }

    /**
     * 计算表名称
     *
     * @param availableTargetNames
     * @param shardingValue
     * @return: java.lang.String
     * @author: 幸福的小雨
     * @time: 2023/6/12 10:08
     */
    public abstract String doSharding(String logicTable, Collection<String> availableTargetNames, PreciseShardingValue<T> shardingValue);
}

精确复合字段分片基础类
用于替代ComplexKeysShardingAlgorithm接口，实现sharding算法接口和基础分库分表算法类的交互，抽象一个用于子类实现的doSharding算法接口。

public abstract class BaseComplexKeysShardingAlgorithm<T extends Comparable<?>> extends BaseShardingAlgorithm implements ComplexKeysShardingAlgorithm<T> {

    /**
     * sharding分库分表基础类，处理框架在发送查询请求时的表
     *
     * @param availableTargetNames
     * @param shardingValue
     * @return: java.lang.String
     * @author: 幸福的小雨
     * @time: 2023/6/12 10:09
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<T> shardingValue) {
        final Collection<String> tableNames = doSharding(getLogicTable(), availableTargetNames, shardingValue);
        checkTable(tableNames);
        return tableNames;
    }

    /**
     * 计算表名称
     *
     * @param availableTargetNames
     * @param shardingValue
     * @return: java.lang.String
     * @author: 幸福的小雨
     * @time: 2023/6/12 10:08
     */
    public abstract Collection<String> doSharding(String logicTable, Collection<String> availableTargetNames, ComplexKeysShardingValue<T> shardingValue);
}

强制分片基础类
用于替代HintShardingAlgorithm接口，实现sharding算法接口和基础分库分表算法类的交互，抽象一个用于子类实现的doSharding算法接口。

public abstract class BaseHintShardingAlgorithm<T extends Comparable<?>> extends BaseShardingAlgorithm implements HintShardingAlgorithm<T> {

    /**
     * sharding分库分表基础类，处理框架在发送查询请求时的表
     *
     * @param availableTargetNames
     * @param shardingValue
     * @return: java.lang.String
     * @author: 幸福的小雨
     * @time: 2023/6/12 10:09
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<T> shardingValue) {
        final Collection<String> tableNames = doSharding(getLogicTable(), availableTargetNames, shardingValue);
        checkTable(tableNames);
        return tableNames;
    }

    /**
     * 计算表名称
     *
     * @param availableTargetNames
     * @param shardingValue
     * @return: java.lang.String
     * @author: 幸福的小雨
     * @time: 2023/6/12 10:08
     */
    public abstract Collection<String> doSharding(String logicTable, Collection<String> availableTargetNames, HintShardingValue<T> shardingValue);
}

3. 使用

• 在JDK17环境中，部分未命名的扩展插件没有加载，需要在启动参数中增加如下信息加载插件--add-opens java.base/java.lang=ALL-UNNAMED --add-opens java.base/java.util=ALL-UNNAMED --add-opens java.base/java.lang.invoke=ALL-UNNAMED

sharding基础配置和使用方法可参照官方文档使用，下面只对增强部分说明。

分表算法基础类

• 单字段精确分表基础类(BasePreciseShardingAlgorithm)
• 复合字段精确分表基础类(BaseComplexKeysShardingAlgorithm)
• 强制分表基础类(BaseHintShardingAlgorithm)

基础类使用

根据实际分表使用场景，新建自定义算法类并继承上述相应的基础类，实现doSharding方法。doSharding方法有logicTable(逻辑表名)、availableTargetNames(可用子表)和shardingValue(分表字段及字段值)三个参数。开发人员可根据这三个参数编写此操作要操作哪张表的名称，最后将表名称返回即可。
例如：

public class CommandComplexKeysAlgorithmConfiguration extends BaseComplexKeysShardingAlgorithm<String> {

    @Override
    public Collection<String> doSharding(String logicTable, Collection<String> availableTargetNames, ComplexKeysShardingValue<String> shardingValue) {
        List<String> list = new ArrayList<>();
        Collection<String> guidValList = shardingValue.getColumnNameAndShardingValuesMap().get("guid");
        guidValList.forEach(id -> list.add(logicTable + "_" + id));
        return list;
    }

}

配置文件

每张需要分表的表需要在application.properties文件中进行相应配置，需要配置的内容为：

• 逻辑表名称(logicTable)：所有子表的基础表。子表需要根据逻辑表结构动态复制产生真实存储数据的表。逻辑表内不存储数据也做CRUD操作，只根据SQL中的逻辑表名称动态匹配子表使用。
• 真实数据节点(actualDataNodes)：此配置为通过sharding表达式抽象表达所有真实的子表。
• 分表字段(sharding-columns)：告诉sharding以哪些字段作为分表字段，多个字段以逗号分割。在CRUD操作中如果包含分表字段中的某一个，sharding就会使用自定义算法查找指定表查询，否则就查询所有子表。
• 分表算法类(algorithm-class-name)：根据分库分表类型指定自定义分表算法类，如果自定义分表类继承了增加强型BaseXXXAlgorithm基础类，此逻辑中无须事先建立所有分表。

# 逻辑表名
spring.shardingsphere.sharding.tables.t_student.logicTable=t_student
# 此表达式需要在JVM启动参数中增加 --add-opens java.base/java.lang=ALL-UNNAMED --add-opens java.base/java.util=ALL-UNNAMED
# 真实数据节点
spring.shardingsphere.sharding.tables.t_student.actualDataNodes=command-center.t_student_${2023..2024}${(1..12).collect{t -> t.toString().padLeft(2, '0')}}
# 指定主键
spring.shardingsphere.sharding.tables.t_student.keyGenerator.column=guid
# 主键生成策略
spring.shardingsphere.sharding.tables.t_student.keyGenerator.type=CommandIdSeq
# 分表字段(复合字段算法)
spring.shardingsphere.sharding.tables.t_student.table-strategy.complex.sharding-columns=guid,create_time
# 分表算法类
spring.shardingsphere.sharding.tables.t_student.table-strategy.complex.algorithm-class-name=com.example.demo.configure.CommandComplexKeysAlgorithmConfiguration

POM依赖

<properties>
    <java.version>17</java.version>
    <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
    <postgresql-driver.version>42.6.0</postgresql-driver.version>
</properties>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${sharding-jdbc.version}</version>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>${postgresql-driver.version}</version>
</dependency>