分库分表-shardingsphere+springboot+m
2021-08-06 本文已影响0人
dbasy
1.创建测试类
2.根据id查询
@Test
public void testQueryById(){
orderMapper.selectById(1423300097134886914L);
}
由于只输入了订单id，所以路由无法确定是哪个数据节点查询，所以会到两个数据节点，再根据order_id取模去对应真实表查询
2021-08-05 23:38:27.166 INFO 1242 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,order_name,user_id,insert_date FROM t_order WHERE id=?
2021-08-05 23:38:27.166 INFO 1242 --- [ main] ShardingSphere-SQL : Actual SQL: test0 ::: SELECT id,order_name,user_id,insert_date FROM t_order0 WHERE id=? ::: [1423300097134886914]
2021-08-05 23:38:27.167 INFO 1242 --- [ main] ShardingSphere-SQL : Actual SQL: test1 ::: SELECT id,order_name,user_id,insert_date FROM t_order0 WHERE id=? ::: [1423300097134886914]
3.将user_id也作为条件,通过user_id和id进行查询
@Test
public void testQueryByUserIdAndId() {
LambdaQueryWrapper<Order> queryWrapper = new LambdaQueryWrapper<Order>();
queryWrapper.eq(Order::getUserId, 2).eq(Order::getId, 1423300097134886914L);
orderMapper.selectOne(queryWrapper);
}
显然指定用户id方式性能更高，只要到对应的数据节点，找对应的真实表查询。
2021-08-05 23:49:27.275 INFO 1324 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,order_name,user_id,insert_date FROM t_order
WHERE (user_id = ? AND id = ?)
2021-08-05 23:49:27.276 INFO 1324 --- [ main] ShardingSphere-SQL : Actual SQL: test0 ::: SELECT id,order_name,user_id,insert_date FROM t_order0
WHERE (user_id = ? AND id = ?) ::: [2, 1423300097134886914]
4.全路由查询, 这种方式性能最差，即在sql中没有分片字段的情况
@Test
public void testAllRouter() {
LambdaQueryWrapper<Order> queryWrapper = new LambdaQueryWrapper<Order>();
queryWrapper.eq(Order::getOrderName, "orderName4");
orderMapper.selectOne(queryWrapper);
}
2021-08-05 23:55:40.682 INFO 1356 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,order_name,user_id,insert_date FROM t_order
WHERE (order_name = ?)
2021-08-05 23:55:40.682 INFO 1356 --- [ main] ShardingSphere-SQL : Actual SQL: test0 ::: SELECT id,order_name,user_id,insert_date FROM t_order0
WHERE (order_name = ?) ::: [orderName4]
2021-08-05 23:55:40.682 INFO 1356 --- [ main] ShardingSphere-SQL : Actual SQL: test0 ::: SELECT id,order_name,user_id,insert_date FROM t_order1
WHERE (order_name = ?) ::: [orderName4]
2021-08-05 23:55:40.682 INFO 1356 --- [ main] ShardingSphere-SQL : Actual SQL: test1 ::: SELECT id,order_name,user_id,insert_date FROM t_order0
WHERE (order_name = ?) ::: [orderName4]
2021-08-05 23:55:40.682 INFO 1356 --- [ main] ShardingSphere-SQL : Actual SQL: test1 ::: SELECT id,order_name,user_id,insert_date FROM t_order1
WHERE (order_name = ?) ::: [orderName4]
4.分页查询
@Test
public void testPage() {
LambdaQueryWrapper<Order> queryWrapper = new LambdaQueryWrapper<Order>();
queryWrapper.orderByDesc(Order::getInsertDate);
IPage<Order> orderIPage = orderMapper.selectPage(new Page<Order>(1,10), queryWrapper);
System.out.println(orderIPage.getRecords().size());
}
2021-08-06 00:15:54.083 INFO 1499 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,order_name,user_id,insert_date FROM t_order
ORDER BY insert_date DESC
2021-08-06 00:15:54.083 INFO 1499 --- [ main] ShardingSphere-SQL : Actual SQL: test0 ::: SELECT id,order_name,user_id,insert_date FROM t_order0
ORDER BY insert_date DESC
2021-08-06 00:15:54.083 INFO 1499 --- [ main] ShardingSphere-SQL : Actual SQL: test0 ::: SELECT id,order_name,user_id,insert_date FROM t_order1
ORDER BY insert_date DESC
2021-08-06 00:15:54.083 INFO 1499 --- [ main] ShardingSphere-SQL : Actual SQL: test1 ::: SELECT id,order_name,user_id,insert_date FROM t_order0
ORDER BY insert_date DESC
2021-08-06 00:15:54.083 INFO 1499 --- [ main] ShardingSphere-SQL : Actual SQL: test1 ::: SELECT id,order_name,user_id,insert_date FROM t_order1
ORDER BY insert_date DESC
100
获取到了100条数据，显然不对，预期应该是10条，需要加上mybatus-plus的分页插件
@Configuration
@MapperScan("com.cmx.dbasy.sharding.mapper")
public class MybatisPlusConfigration {
// 最新版
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
再次运行结果,符合预期，显然 数据总数是所有真实表数据之和，前10条数据为所有真实表前10条，然后进行合并处理，这个过程都有shardingsphere-jdbc处理了
2021-08-06 00:18:34.126 INFO 1515 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT COUNT(*) FROM t_order
2021-08-06 00:18:34.126 INFO 1515 --- [ main] ShardingSphere-SQL : Actual SQL: test0 ::: SELECT COUNT(*) FROM t_order0
2021-08-06 00:18:34.127 INFO 1515 --- [ main] ShardingSphere-SQL : Actual SQL: test0 ::: SELECT COUNT(*) FROM t_order1
2021-08-06 00:18:34.127 INFO 1515 --- [ main] ShardingSphere-SQL : Actual SQL: test1 ::: SELECT COUNT(*) FROM t_order0
2021-08-06 00:18:34.127 INFO 1515 --- [ main] ShardingSphere-SQL : Actual SQL: test1 ::: SELECT COUNT(*) FROM t_order1
2021-08-06 00:18:34.185 INFO 1515 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,order_name,user_id,insert_date FROM t_order
ORDER BY insert_date DESC LIMIT ?
2021-08-06 00:18:34.186 INFO 1515 --- [ main] ShardingSphere-SQL : Actual SQL: test0 ::: SELECT id,order_name,user_id,insert_date FROM t_order0
ORDER BY insert_date DESC LIMIT ? ::: [10]
2021-08-06 00:18:34.186 INFO 1515 --- [ main] ShardingSphere-SQL : Actual SQL: test0 ::: SELECT id,order_name,user_id,insert_date FROM t_order1
ORDER BY insert_date DESC LIMIT ? ::: [10]
2021-08-06 00:18:34.186 INFO 1515 --- [ main] ShardingSphere-SQL : Actual SQL: test1 ::: SELECT id,order_name,user_id,insert_date FROM t_order0
ORDER BY insert_date DESC LIMIT ? ::: [10]
2021-08-06 00:18:34.186 INFO 1515 --- [ main] ShardingSphere-SQL : Actual SQL: test1 ::: SELECT id,order_name,user_id,insert_date FROM t_order1
ORDER BY insert_date DESC LIMIT ? ::: [10]
10
shardingsphere分片架构图
image.png
