背景
针对订单表,按照省份进行分片
table结构
CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL COMMENT '订单id',
`price` decimal(10, 2) NOT NULL COMMENT '订单价格',
`user_id` bigint(20) NOT NULL COMMENT '下单用户id',
`status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB ;
standrad策略
建立分片表
分片表扩展province
字段。
DROP TABLE IF EXISTS `t_order_bj`;
CREATE TABLE `t_order_bj` (
`order_id` bigint(20) NOT NULL COMMENT '订单id',
`price` decimal(10, 2) NOT NULL COMMENT '订单价格',
`user_id` bigint(20) NOT NULL COMMENT '下单用户id',
`status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
`province` char(6) NOT NULL COMMENT '所属省份',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB ;
DROP TABLE IF EXISTS `t_order_ah`;
CREATE TABLE `t_order_ah` (
`order_id` bigint(20) NOT NULL COMMENT '订单id',
`price` decimal(10, 2) NOT NULL COMMENT '订单价格',
`user_id` bigint(20) NOT NULL COMMENT '下单用户id',
`status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
`province` char(6) NOT NULL COMMENT '所属省份',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB ;
yaml配置
仅定义ah和bj
两个分片
spring:
shardingsphere:
datasource:
## 定义数据源 多个以逗号分隔开
names: m1
## 定义数据源 m1
m1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: XXXX
username: XXXXX
password: XXXXX
sharding:
tables:
t_order:
## 指定 t_order表的 数据分布情况,配置数据节点
actual-data-nodes: m1.t_order_bj,m1.t_order_ah
## 指定t_order表的主键列,以及主键生成策略为SNOWFLAKE
key-generator:
column: order_id
type: SNOWFLAKE
## 指定t_order的分片策略: 设置分片键和分片算法
table-strategy:
standard:
sharding-column: province
## 指定分片算法
precise-algorithm-class-name: cn.jhs.sharding.chap11.ProvincePreciseShardingAlgorithm
# 打开sharding-jdbc sql输出
props:
sql:
show: true
自定义分片算法
public class ProvincePreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {
private static final ImmutableMap<String, String> MAPPINGS = ImmutableMap.<String, String>builder().put("110000", "bj").put("340000", "ah").build();
/**
* @param availableTargetNames 物理表 - 集合
* @param shardingValue 分偏键盘
* @return
*/
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
String sharding = MAPPINGS.get(shardingValue.getValue());
if(sharding != null) {
for (String table : availableTargetNames) {
if (table.endsWith(sharding)) {
return table;
}
}
}
throw new RuntimeException("未识别的是分偏键{" + sharding + "}");
}
}
dao
@Mapper
@Component
public interface Order11Dao {
@Insert("insert into t_order(price,user_id,status,province) value(#{price},#{userId},#{status},#{province})")
int insertOrder(@Param("price") BigDecimal price, @Param("userId") Long userId,
@Param("status") String status,@Param("province") String province);
@Select({"<script>" +
"select " +
" * " +
" from t_order t" +
"</script>"})
List<Map> selectAll(@Param("province") String province);
@Select({"<script>" +
"select " +
" * " +
" from t_order t" +
" where t.province = #{province} " +
"</script>"})
List<Map> selectSharding(@Param("province") String province);
}
测试用例
insert
@Test
public void testInsertOrder() {
for (int i = 0; i < 10; i++) {
if( i % 2 == 0) {
orderDao.insertOrder(new BigDecimal((i + 1) * 5), 1L, "WAIT_PAY","340000");
}else{
orderDao.insertOrder(new BigDecimal((i + 1) * 5), 1L, "WAIT_PAY","110000");
}
}
}
结果
select-使用分片键
@Order(3)
@Test
public void testSelectOrderbyIds2(){
orderDao.selectSharding("340000").stream().forEach(System.out::println);
}
sql日志
select-不使用分片键
虽然指定了 查询参数province
但是实际执行的sql中,并没有使用该字段作为查询条件
@Order(2)
@Test
public void testAll(){
orderDao.selectAll("340000").stream().forEach(System.out::println);
}
sql日志
全包扫描
Hint强制路由
上文的解决方式,需要在分片表中增加province
字段,实际上该字段并无实际的业务含义(通过表名即可获取省份信息)。
Hint不再从SQL 解析中获取值,而是直接通过hintManager来指定分片键
建立分片表
分片表扩展province
字段。
DROP TABLE IF EXISTS `t_order_bj`;
CREATE TABLE `t_order_bj` (
`order_id` bigint(20) NOT NULL COMMENT '订单id',
`price` decimal(10, 2) NOT NULL COMMENT '订单价格',
`user_id` bigint(20) NOT NULL COMMENT '下单用户id',
`status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB ;
DROP TABLE IF EXISTS `t_order_ah`;
CREATE TABLE `t_order_ah` (
`order_id` bigint(20) NOT NULL COMMENT '订单id',
`price` decimal(10, 2) NOT NULL COMMENT '订单价格',
`user_id` bigint(20) NOT NULL COMMENT '下单用户id',
`status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB ;
yaml配置
####...
table-strategy:
hint:
algorithm-class-name: cn.jhs.sharding.chap12.ProvinceHintShardingKeyAlgorithm
分片策略
public class ProvinceHintShardingKeyAlgorithm implements HintShardingAlgorithm<String> {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<String> shardingValue) {
Collection<String> shardingCollection = shardingValue.getValues();
List<String> result = availableTargetNames.stream().filter(table -> {
for (String sharding : shardingCollection) {
if (table.endsWith(sharding)) {
return true;
}
}
return false;
}).collect(Collectors.toList());
if (result.isEmpty()) {
throw new RuntimeException("未识别的是分偏键{" + shardingCollection + "}");
}
return result;
}
}
dao配置
@Mapper
@Component
public interface Order12Dao {
@Insert("insert into t_order(price,user_id,status) value(#{price},#{userId},#{status})")
int insertOrder(@Param("price") BigDecimal price, @Param("userId") Long userId,
@Param("status") String status);
@Select({"<script>" +
"select " +
" * " +
" from t_order t" +
"</script>"})
List<Map> selectAll();
}
单元测试
public void testInsertOrder() {
//清理
HintManager.clear();
// Hint分片策略必须要使用 HintManager工具类
HintManager hintManager = HintManager.getInstance();
//设置表分片逻辑
hintManager.addTableShardingValue("t_order", "ah");
// hintManager.addDatabaseShardingValue(); //数据库分片逻辑
// hintManager.setMasterRouteOnly(); //在读写分离数据库中,Hint 可以强制读主库(主从复制是存在一定延时,但在业务场景中,可能更需要保证数据的实时性)
orderDao.insertOrder(new BigDecimal(1234), 1L, "WAIT_PAY");
//清理
HintManager.clear();
}
结果
完善AOP
public void before(){
//清理
HintManager.clear();
// Hint分片策略必须要使用 HintManager工具类
HintManager hintManager = HintManager.getInstance();
//设置表分片逻辑
hintManager.addTableShardingValue("t_order", "ah");
}
public void after(){
}
引用 https://blog.csdn.net/womenyiqilalala/article/details/105683828
常见分片策略
Sharding-JDBC中的分片策略有两个维度:
- 数据源分片策略(DatabaseShardingStrategy):数据被分配的目标数据源
- 表分片策略(TableShardingStrategy):数据被分配的目标表
- 两种分片策略API完全相同,但是表分片策略是依赖于数据源分片策略的(即:先分库,然后才有分表)
Sharding分片策略继承自ShardingStrategy,提供了5种分片策略。io.shardingsphere.core.routing.strategy.ShardingStrategy --io.shardingsphere.core.routing.strategy.standard.StandardShardingStrategy --io.shardingsphere.core.routing.strategy.standard.ComplexShardingStrategy --io.shardingsphere.core.routing.strategy.standard.InlineShardingStrategy --io.shardingsphere.core.routing.strategy.standard.HintShardingStrategy --io.shardingsphere.core.routing.strategy.standard.NoneShardingStrategy