本文基于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>