接着上一篇,InlineShardingStrategyConfiguration不支持范围分片操作。看看文档
选用StandardShardingStrategy 标准分片策略。
修改原来的分库分表策略代码:
// 配置分库 + 分表策略
//orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
//orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));
//替换为StandardShardingStrategyConfiguration分片策略
orderTableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id",
new PreciseShardingAlgorithm() {
//collection 就是实际库的集合,preciseShardingValue是 逻辑表 和 字段名 和 分片健(这里的user_id)的值
@Override
public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
return null;
}
}, new RangeShardingAlgorithm() {
@Override
public Collection<String> doSharding(Collection collection, RangeShardingValue rangeShardingValue) {
return null;
}
}));
断点调试下:
对于等值查询执行 PreciseShardingAlgorithm()
范围查询执行 RangeShardingAlgorithm()
可以看出,collection 就是实际库的集合,preciseShardingValue是 逻辑表 和 字段名 和 分片健(这里的user_id)
修改后的配置:
// 配置分库 + 分表策略
//orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
//orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));
//替换为StandardShardingStrategyConfiguration分片策略
//PreciseShardingAlgorithm是必选的,用于处理=和IN的分片
//collection 就是实际库的集合,preciseShardingValue是 逻辑表 和 字段名 和 分片健(这里的user_id)
//RangeShardingAlgorithm是可选的,用于处理BETWEEN AND, >, <, >=, <=分片
//分库策略
orderTableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id",
(PreciseShardingAlgorithm<Long>) (collection, preciseShardingValue) -> {
//这里返回 满足条件的一个库。按照原来user_id % 2的方式,就应该是这样
Long currentUserId = preciseShardingValue.getValue();
long result = currentUserId % 2;
//过滤出以取模结果结尾的库
return collection.stream().filter(dbName -> dbName.endsWith(String.valueOf(result))).findFirst().get();
}, (collection, rangeShardingValue) -> {
//根据user_id % 2的方式确定不了具体范围,直接返回所有库
return collection;
}));
//分表策略
orderTableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id",
(PreciseShardingAlgorithm<Long>) (collection, preciseShardingValue) -> {
//也按照原来user_id % 2的方式
Long currentUserId = preciseShardingValue.getValue();
long result = currentUserId % 2;
return collection.stream().filter(dbName -> dbName.endsWith(String.valueOf(result))).findFirst().get();
}, (collection, rangeShardingValue) -> {
return collection;
}));
全局搜索下日志配置:
配置日志打印:
执行测试方法:范围查询 select * from `t_order` where user_id > #{id} 3
查看打印sql:
范围查询,返回的全部数据库,所以查询全库全表。
再看等值查询:select * from `t_order` where user_id = #{id} 3
等值查询执行的:取模判断返回的数据库。