标准分片策略
原创大约 5 分钟
前面一直使用的都是称为inline（行表达式）分片策略，但除此之外，ShardingSphere还有其他四类分片策略：Standard（标准分片策略）、Composite（复合分片策略）、Hint（Hint分片策略）和不分片策略。
精准分片算法
Standard（标准分片策略）只支持单字段分片键，通过PreciseShardingAlgorithm实现精准分片。
如果指定标准分片策略，那么PreciseShardingAlgorithm就是必须要实现的。
先创建一个自定义的CustomPreciseShardingAlgorithm类，让它实现PreciseShardingAlgorithm<Long>接口。
package com.itechthink.strategy;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
/**
* 自定义分库分表策略：精准分片算法
*
*/
public class CustomPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* @param dataSourceNames 数据源集合
* 在分库时值为所有分片库的集合 databaseNames
* 分表时为对应分片库中所有分片表的集合 tablesNames
* @param preciseShardingValue 分片属性
* logicTableName 逻辑表名
* columnName 分片健
* value 从SQL中解析出的分片健值
*/
@Override
public String doSharding(Collection<String> dataSourceNames, PreciseShardingValue<Long> preciseShardingValue) {
for (String dsn : dataSourceNames) {
String value = String.valueOf(preciseShardingValue.getValue() % dataSourceNames.size());
// 库名或表名结尾
if (dsn.endsWith(value)) {
return dsn;
}
}
return null;
}
}然后修改配置文件application.properties，内容如下。
spring.application.name=mysql-sharding-jdbc
server.port=8080
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true
# 数据源
spring.shardingsphere.datasource.names=ds0,ds1,ds2
# 第一个数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://172.16.185.176:3306/itechthink_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
# 第二个数据源
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://172.16.185.176:3306/itechthink_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
# 第三个数据源
spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.jdbc-url=jdbc:mysql://172.16.185.176:3306/itechthink_order_2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123456
# 指定workId
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=1
# id生成策略
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 配置广播表，如果有多个表用,分隔
spring.shardingsphere.sharding.broadcast-tables=t_config
spring.shardingsphere.sharding.tables.t_config.key-generator.column=id
spring.shardingsphere.sharding.tables.t_config.key-generator.type=SNOWFLAKE
# 指定 t_order 表的数据节点
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order_$->{0..1},ds1.t_order_$->{0..1},ds2.t_order_$->{0..1}
# 指定精准分片算法（水平分表）
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.itechthink.strategy.CustomPreciseShardingAlgorithm清除之前所有的测试数据。
> USE itechthink_order_0;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;
> USE itechthink_order_1;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;
> USE itechthink_order_2;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;最后执行ShardingJDBCTest中的测试方法testSaveOrder()。
执行后结果显示，在itechthink_order_0、itechthink_order_1和itechthink_order_2这三个库中，每张表（t_order_0和t_order_1）都有5条数据。
这是因为仅执行了分表，而没有做分库操作。
> SELECT * FROM t_order_0;
+---------------------+--------+------------------------------+-------+--------+---------------------+
| id | userid | tradeno | state | money | createtime |
+---------------------+--------+------------------------------+-------+--------+---------------------+
| 1823296890561683458 | 0 | 5b884ee973a54b2197e9b3380274 | 1 | 168.00 | 2023-06-12 21:53:49 |
| 1823296893476724738 | 3 | d99060bb5605449cb5a2ee20bc16 | 1 | 168.00 | 2023-06-12 21:53:49 |
| 1823296893522862082 | 4 | 4aff3b46400d4531baef10a658c0 | 1 | 168.00 | 2023-06-12 21:53:49 |
| 1823296893573193730 | 5 | 80935209a18d47949b7870824c97 | 1 | 168.00 | 2023-06-12 21:53:49 |
| 1823296893615136770 | 6 | a0a4d4c313c544a583008e73ff9d | 1 | 168.00 | 2023-06-12 21:53:49 |
+---------------------+--------+------------------------------+-------+--------+---------------------+
5 rows in set (0.01 sec)
> SELECT * FROM t_order_1;
+---------------------+--------+------------------------------+-------+--------+---------------------+
| id | userid | tradeno | state | money | createtime |
+---------------------+--------+------------------------------+-------+--------+---------------------+
| 1823296893384450049 | 1 | 9380bd79fb844e4491be85d9899f | 1 | 168.00 | 2023-06-12 21:53:49 |
| 1823296893430587393 | 2 | fd1d793e3fab4c5caa733b0f6e8a | 1 | 168.00 | 2023-06-12 21:53:49 |
| 1823296893652885505 | 7 | b656ede451dd4666abdabc952fdd | 1 | 168.00 | 2023-06-12 21:53:49 |
| 1823296893694828545 | 8 | e358132731ef493e9ed4e81fba0b | 1 | 168.00 | 2023-06-12 21:53:49 |
| 1823296893732577281 | 9 | 50d6792ed4fc417aa9b92af1eb05 | 1 | 168.00 | 2023-06-12 21:53:49 |
+---------------------+--------+------------------------------+-------+--------+---------------------+
5 rows in set (0.00 sec)如果想分库分表，只需要修改配置文件application.properties，在其中加入以下内容即可。
# 分库分片健
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=userid
# 分库分片算法
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.itechthink.strategy.CustomPreciseShardingAlgorithm在执行自定义的分库分表时，使用的精准分片算法都是同一个类CustomPreciseShardingAlgorithm。
清除之前的数据后再次执行测试方法就能看到分库分表的效果了。
范围分片算法
依据指定的分片健的范围值处理分库分表逻辑。
创建一个自定义的CustomRangeShardingAlgorithm类，让它实现RangeShardingAlgorithm<Long>接口，其内容和CustomPreciseShardingAlgorithm类差别不大。
package com.itechthink.strategy;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.util.Collection;
import java.util.LinkedHashSet;
import java.util.Set;
/**
* 自定义分库分表策略：范围分片算法
*
*/
public class CustomRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> {
/**
* @param dataSourceNames 数据源集合
* 在分库时值为所有分片库的集合 databaseNames
* 分表时为对应分片库中所有分片表的集合 tablesNames
* @param rangeShardingValue 分片属性
* logicTableName 逻辑表名
* columnName 分片健
* value 从SQL中解析出的分片健值
*/
@Override
public Collection<String> doSharding(Collection<String> dataSourceNames, RangeShardingValue<Long> rangeShardingValue) {
Set<String> result = new LinkedHashSet<>();
// 开始值
Long lower = rangeShardingValue.getValueRange().lowerEndpoint();
// 结束值
Long upper = rangeShardingValue.getValueRange().upperEndpoint();
for (long i = lower; i <= upper; i++) {
for (String datasource : dataSourceNames) {
String value = i % dataSourceNames.size() + "";
if (datasource.endsWith(value)) {
result.add(datasource);
}
}
}
return result;
}
}然后修改配置文件application.properties，增加如下内容。
# 在精准水平分表下，增加一个范围分片
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.range-algorithm-class-name=com.itechthink.strategy.CustomRangeShardingAlgorithm清除之前所有的测试数据。
> USE itechthink_order_0;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;
> USE itechthink_order_1;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;
> USE itechthink_order_2;
> TRUNCATE TABLE t_order_0;
> TRUNCATE TABLE t_order_1;最后在ShardingJDBCTest中增加一个测试方法testBetween()并执行。
@Test
public void testBetween() {
orderMapper.selectList(new QueryWrapper<Order>().between("id", 1L, 3L));
}执行测试后，ShardingSphere执行了6条SQL。
Logic SQL: SELECT id,userid,tradeno,state,money,createtime FROM t_order WHERE (id BETWEEN ? AND ?)
SQLStatement: SelectStatementContext......
Actual SQL: ds0 ::: SELECT id,userid,tradeno,state,money,createtime FROM t_order_0 WHERE (id BETWEEN ? AND ?) ::: [1, 3]
Actual SQL: ds0 ::: SELECT id,userid,tradeno,state,money,createtime FROM t_order_1 WHERE (id BETWEEN ? AND ?) ::: [1, 3]
Actual SQL: ds1 ::: SELECT id,userid,tradeno,state,money,createtime FROM t_order_0 WHERE (id BETWEEN ? AND ?) ::: [1, 3]
Actual SQL: ds1 ::: SELECT id,userid,tradeno,state,money,createtime FROM t_order_1 WHERE (id BETWEEN ? AND ?) ::: [1, 3]
Actual SQL: ds2 ::: SELECT id,userid,tradeno,state,money,createtime FROM t_order_0 WHERE (id BETWEEN ? AND ?) ::: [1, 3]
Actual SQL: ds2 ::: SELECT id,userid,tradeno,state,money,createtime FROM t_order_1 WHERE (id BETWEEN ? AND ?) ::: [1, 3]显然，如果把测试的范围改为between("id", 1L, 1L))，那么它将只会执行3条SQL。
感谢支持
更多内容，请移步《超级个体》。