ShardingSphere-多表关联

qq_39093474

于 2024-05-11 22:56:49 发布

阅读量228

点赞数 1

分类专栏：数据库文章标签：数据库

数据库专栏收录该内容

27 篇文章 2 订阅

订阅专栏

文章目录

一、多表关联

一、多表关联

1.1 数据库规划

在server-order0和server-order1服务器中分别创建两张订单详情表t_order_item0，t_order_ item1和订单表t_order0，t_order1。

我们希望同一个用户的订单表和订单详情表中的数据都在同一个数据源中，避免跨库关联，因此这两张表我们使用相同的分片策略。

server-order0的db_order上有t_order0、t_order1、t_order_item0、t_order_ item1。

server-order1的db_order上有t_order0、t_order1、t_order_item0、t_order_ item1。

根据user_id选择是哪台服务器的数据库，在根据order_id选择到具体的哪张表。order表、order_item表的分片规则是一样的。

1.2 创建表

那么在t_order_item中我们也需要创建order_no和user_id这两个分片键。

CREATE DATABASE db_order;

CREATE TABLE t_order0(
  `id` bigint NOT NULL,
  `order_no` VARCHAR(30) NOT NULL,
  `user_id` bigint NULL DEFAULT NULL,
  `amount` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE t_order1(
  `id` bigint NOT NULL,
  `order_no` VARCHAR(30) NOT NULL,
  `user_id` bigint NULL DEFAULT NULL,
  `amount` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);


CREATE TABLE `t_order_item0` (
  `order_item_id` bigint NOT NULL,
   `order_no` VARCHAR(30) NOT NULL,
  `user_id` bigint NOT NULL,
  `price` decimal(10, 2) NOT NULL,
  `count` int NOT NULL,
  PRIMARY KEY (`order_item_id`)
 );
 
 CREATE TABLE `t_order_item1` (
  `order_item_id` bigint NOT NULL,
   `order_no` VARCHAR(30) NOT NULL,
  `user_id` bigint NOT NULL,
  `price` decimal(10, 2) NOT NULL,
  `count` int NOT NULL,
  PRIMARY KEY (`order_item_id`)
 );

1.3 创建实体类

@TableName("t_order") //逻辑表名
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order {
    @TableId(type = IdType.ASSIGN_ID) //分布式id
    private Long id;
    private String  orderNO;
    private Long userId;
    private BigDecimal amount;
}

@TableName("t_order_item") //逻辑表名
@Data
@AllArgsConstructor
@NoArgsConstructor
public class OrderItem {

    //当配置了ShardingSphere-jdbc的分布式序列时，自动使用ShardingSphere-jdbc的分布式序列
    //当没有配置了ShardingSphere-jdbc的分布式序列时，自动依赖数据库的自增策略
    @TableId(type = IdType.AUTO)
    //@TableId(type = IdType.ASSIGN_ID) //分布式id(默认值)
    private Long order_item_id;
    private String  orderNo;
    private Long userId;
    private BigDecimal price;
    private Integer count;
}

1.4 创建mapper

@Mapper
public interface OrderItemMapper extends BaseMapper<OrderItem> {
}

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}

1.5 配置关联表

t_order配置看我之前的文章ShardingSphere-mysql水平分片

t_order_item的分片表，分片策略，分布式序列策略和t_order一致。

#-------------------标准分片表配置(数据节点配置)
spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=server-order$->{0..1}.t_order_item$->{0..1}
#=================分库策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-column=user_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-algorithm-name=my_inline_user_id

# =================分表策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-column=order_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-name=alg_hash_mod
# 分布式序列策略配置
# 分布式序列列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.column=order_item_id
# 分布式序列算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.key-generator-name=alg_snowflake

1.6 插入测试数据

@SpringBootTest
public class ShardTest {

    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private OrderItemMapper orderItemMapper;
    /**
     * 水平分片:分表插入数据测试
     */
    @Test
    public  void testInsertOrderAndOrderItem(){

        for (long i = 1; i <5 ; i++) {
            Order order=new Order();
            order.setOrderNo("ATGUIGU"+i);//分表键
            order.setUserId(1l);//分库键
            orderMapper.insert(order);

            for (int j = 0; j <3 ; j++) {
                OrderItem orderItem=new OrderItem();
                orderItem.setOrderNo("ATGUIGU"+i);//分表键
                orderItem.setUserId(1l);//分库键
                orderItem.setPrice(new BigDecimal(10));
                orderItem.setCount(2);
                orderItemMapper.insert(orderItem);

            }
        }
        for (long i = 5; i <9 ; i++) {
            Order order=new Order();
            order.setOrderNo("ATGUIGU"+i);//分表键
            order.setUserId(2l);//分库键
            orderMapper.insert(order);
            for (int j = 0; j <3 ; j++) {
                OrderItem orderItem=new OrderItem();
                orderItem.setOrderNo("ATGUIGU"+i);//分表键
                orderItem.setUserId(2l);//分库键
                orderItem.setPrice(new BigDecimal(3));
                orderItem.setCount(3);
                orderItemMapper.insert(orderItem);

            }
        }
    }
}

1.7 绑定表

需求：查询每个订单的订单号和总订单金额

1.7.1 创建VO对象

@Data
public class OrderVo {
    private String orderNo;
    private BigDecimal amount;
}

1.7.2 修改mapper

@Mapper
public interface OrderMapper extends BaseMapper<Order> {

    List<OrderVo> selectAllOrderVo();
}

1.7.3 增加xml

在resources下创建mapper文件夹，在mapper下创建OrderMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE mapper
     PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
     "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <mapper namespace="com.example.readwrite.mapper.OrderMapper">
 
   <select id="selectAllOrderVo" resultType="com.example.readwrite.model.OrderVo">
     select o.order_id, sum(oi.amount) as amount
     from t_order as o join t_order_item as oi on o.order_id = oi.order_id
     group by o.order_id
   </select>
 
 </mapper>

1.8 测试

@Test
    public  void testGetOrderAmount(){

        List<OrderVo> orderVos = orderMapper.selectAllOrderVo();
        orderVos.forEach(System.out::println);
    }

2024-05-11 22:39:50.104  INFO 2076 --- [           main] ShardingSphere-SQL                       : Logic SQL: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order as o join t_order_item as oi on o.order_no = oi.order_no
     group by o.order_no
2024-05-11 22:39:50.104  INFO 2076 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2024-05-11 22:39:50.104  INFO 2076 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order0 as o join t_order_item0 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
2024-05-11 22:39:50.104  INFO 2076 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order1 as o join t_order_item0 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
2024-05-11 22:39:50.104  INFO 2076 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order0 as o join t_order_item1 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
2024-05-11 22:39:50.104  INFO 2076 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order1 as o join t_order_item1 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
2024-05-11 22:39:50.104  INFO 2076 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order0 as o join t_order_item0 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
2024-05-11 22:39:50.104  INFO 2076 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order1 as o join t_order_item0 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
2024-05-11 22:39:50.104  INFO 2076 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order0 as o join t_order_item1 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
2024-05-11 22:39:50.104  INFO 2076 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order1 as o join t_order_item1 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
OrderVo(orderNo=ATGUIGU1, amount=60.00)
OrderVo(orderNo=ATGUIGU2, amount=60.00)
OrderVo(orderNo=ATGUIGU3, amount=60.00)
OrderVo(orderNo=ATGUIGU4, amount=60.00)
OrderVo(orderNo=ATGUIGU5, amount=27.00)
OrderVo(orderNo=ATGUIGU6, amount=27.00)
OrderVo(orderNo=ATGUIGU7, amount=27.00)
OrderVo(orderNo=ATGUIGU8, amount=27.00)

可以看到日志中出现8个实际的Actual SQL，那个t_order和t_order_item会出现笛卡尔积关联。从而影响查询效率。

绑定表：指分片规则一致的一组分片表。使用绑定表进行多表关联查询时，必须使用分片键进行关联，否则会出现笛卡尔积关联或跨库关联，从而影响查询效率。

例如：t_order 表和 t_order_item 表，均按照 order_no分片，并且使用 order_no进行关联，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。

1.9 绑定表配置

在原来水平分片配置的基础上添加如下配置：

#----------------绑定表
# 绑定表规则列表
spring.shardingsphere.rules.sharding.binding-tables[0]=t_order,t_order_item

配置完绑定表后再次进行关联查询的测试：

如果不配置绑定表：测试的结果为8个SQL。多表关联查询会出现笛卡尔积关联。
如果配置绑定表：测试的结果为4个SQL。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。

2024-05-11 22:55:07.649  INFO 3300 --- [           main] ShardingSphere-SQL                       : Logic SQL: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order as o join t_order_item as oi on o.order_no = oi.order_no
     group by o.order_no
2024-05-11 22:55:07.649  INFO 3300 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2024-05-11 22:55:07.649  INFO 3300 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order0 as o join t_order_item0 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
2024-05-11 22:55:07.649  INFO 3300 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order1 as o join t_order_item1 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
2024-05-11 22:55:07.650  INFO 3300 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order0 as o join t_order_item0 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
2024-05-11 22:55:07.650  INFO 3300 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: select o.order_no, sum(oi.price*oi.count) as amount
     from t_order1 as o join t_order_item1 as oi on o.order_no = oi.order_no
     group by o.order_no ORDER BY o.order_no ASC 
OrderVo(orderNo=ATGUIGU1, amount=60.00)
OrderVo(orderNo=ATGUIGU2, amount=60.00)
OrderVo(orderNo=ATGUIGU3, amount=60.00)
OrderVo(orderNo=ATGUIGU4, amount=60.00)
OrderVo(orderNo=ATGUIGU5, amount=27.00)
OrderVo(orderNo=ATGUIGU6, amount=27.00)
OrderVo(orderNo=ATGUIGU7, amount=27.00)
OrderVo(orderNo=ATGUIGU8, amount=27.00)

qq_39093474

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
ShardingSphere-多表关联

例如：t_order 表和 t_order_item 表，均按照 order_no分片，并且使用 order_no进行关联，则此两张表互为绑定表关系。server-order0的db_order上有t_order0、t_order1、t_order_item0、t_order_ item1。server-order1的db_order上有t_order0、t_order1、t_order_item0、t_order_ item1。order表、order_item表的分片规则是一样的。
复制链接

扫一扫

专栏目录