学习资料
学习shardingsphere基本原理，看这一篇博客就差不多了，大佬整理的很详细
https://www.cnblogs.com/donleo123/p/17295667.html
实操参考
https://www.cnblogs.com/threee3/p/19085836
https://juejin.cn/post/7371423114381557760
照虎画猫
路由策略就是根据租户号 tenan_id 分库，pv_realtime_data 表根据业务日期 collect_time 按照日期分表
pom配置
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>5.3.2</version>
</dependency>
<!-- 数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-spring-boot3-starter</artifactId>
<version>3.5.5</version>
</dependency>
yml配置
spring:
application:
name: db_server
datasource:
driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
url: jdbc:shardingsphere:classpath:shardingsphere-config.yaml
mybatis-plus:
configuration:
# ???sql??????????????; logback?????
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
shardingsphere-config配置, 实现根据租户号 tenant_id 字段分库；pv_realtime_data 表再根据 collect_time 字段按日分表
dataSources:
# 默认数据源（用于存储租户信息、配置等）
ds_default:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://localhost:3306/default_db
username: root
password: 123456
ds_1000:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://localhost:3306/tenant_1000_db
username: root
password: 123456
# 租户数据源（每个租户独立数据库）
ds_1001:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://localhost:3306/tenant_1001_db
username: root
password: 123456
ds_1002:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://localhost:3306/tenant_1002_db
username: root
password: 123456
# 2. 分片规则配置
rules:
- !SHARDING
tables:
pv_power_station:
# 实际数据节点：动态库 + 动态表
actualDataNodes: ds_100$->{['0','1','2','3']}.pv_power_station
# 分库策略
databaseStrategy:
standard:
shardingColumn: tenant_id
shardingAlgorithmName: database-by-tenant
pv_device:
# 实际数据节点：动态库 + 动态表
actualDataNodes: ds_100$->{['0','1','2','3']}.pv_device
# 分库策略
databaseStrategy:
standard:
shardingColumn: tenant_id
shardingAlgorithmName: database-by-tenant
pv_realtime_data:
# 实际数据节点：动态库 + 动态表
actualDataNodes: ds_100$->{['0','1','2','3']}.pv_realtime_data_$->{20250101..20251231}
databaseStrategy:
standard:
shardingColumn: tenant_id
shardingAlgorithmName: database-by-tenant
tableStrategy:
standard:
shardingColumn: collect_time
shardingAlgorithmName: table-by-day
# 3. 分片算法定义
shardingAlgorithms:
# 3.1 分库算法：按租户ID映射到具体数据库
database-by-tenant:
type: INLINE
props:
algorithm-expression: ds_100${tenant_id % 1000}
allow-range-query-with-inline-sharding: true
# 按日分表算法（备选）
table-by-day:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: com.jslx.db_server.shardingAlgorithm.DayRangeShardingAlgorithm
# 关键配置：自动创建表
props:
sql-show: true
# 是否在启动时检查分表元数据一致性
check-table-metadata-enabled: false
按日分表的算法 DayRangeShardingAlgorithm 的代码，我的 collect_time 定义的是 LocalDateTime 类型，所以这里的反省也是 LocalDateTime
public class DayRangeShardingAlgorithm extends ShardingAlgorithmTool implements StandardShardingAlgorithm<LocalDateTime> {
private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {
// 精确分片逻辑（单日查询）
String tableSuffix = shardingValue.getValue().format(DATE_FORMATTER);
String tableName = shardingValue.getLogicTableName() + "_" + tableSuffix;
// 检查表是否存在
if (!availableTargetNames.contains(tableName)) {
throw new IllegalArgumentException("无效的表名: " + tableName);
}
// shardingTablesCheckAndCreatAndReturn(shardingValue.getLogicTableName(), tableName);
return tableName;
}
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<LocalDateTime> shardingValue) {
// 范围查询分片逻辑
Set<String> result = new LinkedHashSet<>();
// 1. 获取查询范围
Range<LocalDateTime> range = shardingValue.getValueRange();
LocalDateTime lower = range.hasLowerBound() ? range.lowerEndpoint() : null;
LocalDateTime upper = range.hasUpperBound() ? range.upperEndpoint() : null;
// 2. 如果没有范围（全表查询），返回所有可用表（或根据业务限制）
if (lower == null && upper == null) {
return availableTargetNames;
}
// 3. 如果是单日查询（不跨日）
if (isSameDay(lower, upper)) {
LocalDateTime targetDate = lower != null ? lower : upper;
String tableName = shardingValue.getLogicTableName() + "_" + targetDate.format(DATE_FORMATTER);
if (availableTargetNames.contains(tableName)) {
result.add(tableName);
}
return result;
}
// 4. 处理跨日查询
LocalDateTime startDate = lower != null ? lower : upper;
LocalDateTime endDate = upper != null ? upper : null;
// 获取日期范围的所有表名
Set<String> tablesInRange = getAllDatesInRange(startDate, endDate, shardingValue.getLogicTableName());
// 过滤出可用的表
for (String tableName : tablesInRange) {
if (availableTargetNames.contains(tableName)) {
result.add(tableName);
}
}
return result;
}
/**
* 判断两个 LocalDateTime 是否是同一天
*/
private boolean isSameDay(LocalDateTime d1, LocalDateTime d2) {
if (d1 == null || d2 == null) return false;
return d1.toLocalDate().isEqual(d2.toLocalDate());
}
/**
* 生成日期范围内的所有表名
*/
private Set<String> getAllDatesInRange(LocalDateTime start, LocalDateTime end, String logicTableName) {
Set<String> tables = new LinkedHashSet<>();
// 如果只有开始日期没有结束日期，假设查询今天
if (end == null) {
tables.add(logicTableName + "_" + start.format(DATE_FORMATTER));
return tables;
}
LocalDate startDate = start.toLocalDate();
LocalDate endDate = end.toLocalDate();
// 遍历日期范围
LocalDate currentDate = startDate;
while (!currentDate.isAfter(endDate)) {
tables.add(logicTableName + "_" + currentDate.format(DATE_FORMATTER));
currentDate = currentDate.plusDays(1);
}
return tables;
}
/**
* 可选：支持按日期范围查询，带有限制条件防止查询范围过大
*/
private Set<String> getAllDatesInRangeWithLimit(LocalDateTime start, LocalDateTime end,
String logicTableName, int maxDays) {
Set<String> tables = new LinkedHashSet<>();
LocalDate startDate = start.toLocalDate();
LocalDate endDate = end.toLocalDate();
// 检查日期范围是否过大
long daysBetween = java.time.temporal.ChronoUnit.DAYS.between(startDate, endDate);
if (daysBetween > maxDays) {
throw new IllegalArgumentException("查询日期范围不能超过 " + maxDays + " 天");
}
// 遍历日期范围
LocalDate currentDate = startDate;
while (!currentDate.isAfter(endDate)) {
tables.add(logicTableName + "_" + currentDate.format(DATE_FORMATTER));
currentDate = currentDate.plusDays(1);
}
return tables;
}
}
踩坑
版本问题不说了
启动时有个报错
java.lang.NoSuchMethodError: org.yaml.snakeyaml.representer.Representer: method 'void <init>()' not found
Representer 的无参构造不存在, 按照大佬的方法重写一下Representer，注意包名
https://github.com/yanzhisishui/sharding-jdbc-demo
浙公网安备 33010602011771号