美文网首页分库分表
分库分表-shardingsphere+springboot+m

分库分表-shardingsphere+springboot+m

作者: dbasy | 来源:发表于2021-08-06 00:22 被阅读0次
    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

    相关文章

      网友评论

        本文标题:分库分表-shardingsphere+springboot+m

        本文链接:https://www.haomeiwen.com/subject/ssavvltx.html