articleList

24-Sharding-Jdbc绑定表介绍和配置实战

2025/03/13 posted in  ShardingJDBC
Tags: 

  • 什么是绑定表 - 指分片规则一致的主表和子表
    • 比如product_order表和product_order_item表，均按照order_id分片，则此两张表互为绑定表关系
    • 绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升
      image-20211125120921994
  • 表讲解
CREATE TABLE `product_order_item_0` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_order_id` bigint DEFAULT NULL COMMENT '订单号',
  `product_id` bigint DEFAULT NULL COMMENT '产品id',
  `product_name` varchar(128) DEFAULT NULL COMMENT '商品名称',
  `buy_num` int DEFAULT NULL COMMENT '购买数量',
  `user_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  • 库表数量
    image-20211126153249522
  • 配置实战
#分库策略 默认
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{user_id % 2}

# 指定product_order_item表的数据分布情况
spring.shardingsphere.sharding.tables.product_order_item.actual-data-nodes=ds$->{0..1}.product_order_item_$->{0..1}
spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.sharding-column=product_order_id
spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.algorithm-expression=product_order_item_$->{product_order_id % 2}

#绑定表
spring.shardingsphere.sharding.binding‐tables[0] = product_order,product_order_item
  • 测试
@Data
@TableName("product_order_item")
@EqualsAndHashCode(callSuper = false)
public class ProductOrderItemDO {

    private Long id;

    private Long productOrderId;

    private Long productId;

    private String productName;

    private Integer buyNum;

    private Long userId;

}

public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {
    @Select("select * from product_order o left join product_order_item i on o.id=i.product_order_id")
    List<Object> listProductOrderDetail();
}

    @Test
    public void testBingding(){
        List<Object> list = productOrderMapper.listProductOrderDetail();
        System.out.println(list);
    }
  • 未使用绑定表的SQL
    image-20211126153213404
  • 使用绑定表后的SQL
    image-20211126153103842