第7章：广播表和绑定表配置实战

最新推荐文章于 2025-12-10 21:25:44 发布

原创最新推荐文章于 2025-12-10 21:25:44 发布 · 162 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#java

技术栈同时被 2 个专栏收录

20 篇文章

订阅专栏

ShardingJDBC

9 篇文章

订阅专栏

广播表介绍和配置实战

什么是广播表？

指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。使用与数据量不大且需要与海量数据的表解析关联查询的场景。

注意点：分库分表中间件，对应数据库字段，不能是sql的关键子，否则容易出现问题且报错不明显。

新增数据源ds1

添加ad_config表

CREATE TABLE `ad_config` (
  `id` bigint unsigned NOT NULL COMMENT '主键id',
  `config_key` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置key',
  `config_value` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置value',
  `type` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


@Data
@EqualsAndHashCode(callSuper = false)
@TableName("ad_config")
public class AdConfigDO {
    
    private Integer id;
    private String configKey;
    private String configValue;
    private String type;
}

配置文件编写

# 配置广播表
spring.shardingsphere.sharding.broadcast-tables=ad_config
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE

实战

    @Test
    public void testSaveAndConfig(){
        AdConfigDO adConfigDO = new AdConfigDO();
        adConfigDO.setConfigKey("banner");
        adConfigDO.setConfigValue("https://www.baidu.com");
        adConfigDO.setType("ad");
        adConfigMapper.insert(adConfigDO);
    }

2025-12-01 18:54:52.528  INFO 50100 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO ad_config  ( id,
config_key,
config_value,
type )  VALUES  ( ?,
?,
?,
? )
2025-12-01 18:54:52.528  INFO 50100 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@53917c92, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1fa796a4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1fa796a4, columnNames=[id, config_key, config_value, type], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=73, stopIndex=73, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=76, stopIndex=76, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=79, stopIndex=79, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=82, stopIndex=82, parameterMarkerIndex=3)], parameters=[1995446493471535105, banner, https://www.baidu.com, ad])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=id, generated=false, generatedValues=[1995446493471535105])])
2025-12-01 18:54:52.529  INFO 50100 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO ad_config  ( id,
config_key,
config_value,
type )  VALUES  (?, ?, ?, ?) ::: [1995446493471535105, banner, https://www.baidu.com, ad]
2025-12-01 18:54:52.529  INFO 50100 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: INSERT INTO ad_config  ( id,
config_key,
config_value,
type )  VALUES  (?, ?, ?, ?) ::: [1995446493471535105, banner, https://www.baidu.com, ad]

水分分库分表配置

配置文件如下

spring.application.name=shardingJdbc
server.port=8080

# 打印sql语句
spring.shardingsphere.props.sql.show =  true

spring.shardingsphere.datasource.names=ds0,ds1
# 数据源 ds0
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3308/shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# 数据源 ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://127.0.0.1:3308/shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

# 分库规则
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}
# 分表策略
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id % 2}

# 配置workId
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1

# id生成策略
spring.shardingsphere.sharding.tables.product_order.key-generator.column=${workId}
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

# 配置广播表
spring.shardingsphere.sharding.broadcast-tables=ad_config
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE

ShardinJDBC绑定表介绍和配置实战

什么是绑定表：指分片规则一致的主表和子表。比如product_order表和product_order_item表，均安装order_id分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关系，关联查询效率将大大提升。

添加绑定表product_order_item_0，product_order_item_1

CREATE TABLE `product_order_item` (
  `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;

添加配置


# 分库策略 默认
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

实战

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();
}

有绑定表配置

2025-12-01 18:35:32.944  INFO 44728 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select  * from product_order_0 o left join product_order_item_0 i on o.id = i.product_order_id
2025-12-01 18:35:32.944  INFO 44728 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select  * from product_order_1 o left join product_order_item_1 i on o.id = i.product_order_id
2025-12-01 18:35:32.944  INFO 44728 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select  * from product_order_0 o left join product_order_item_0 i on o.id = i.product_order_id
2025-12-01 18:35:32.944  INFO 44728 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select  * from product_order_1 o left join product_order_item_1 i on o.id = i.product_order_id

无绑定表配置

2025-12-01 18:50:02.787  INFO 28600 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select  * from product_order_1 o left join product_order_item_0 i on o.id = i.product_order_id
2025-12-01 18:50:02.787  INFO 28600 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select  * from product_order_1 o left join product_order_item_1 i on o.id = i.product_order_id
2025-12-01 18:50:02.787  INFO 28600 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select  * from product_order_0 o left join product_order_item_0 i on o.id = i.product_order_id
2025-12-01 18:50:02.787  INFO 28600 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select  * from product_order_0 o left join product_order_item_1 i on o.id = i.product_order_id
2025-12-01 18:50:02.787  INFO 28600 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select  * from product_order_1 o left join product_order_item_0 i on o.id = i.product_order_id
2025-12-01 18:50:02.787  INFO 28600 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select  * from product_order_1 o left join product_order_item_1 i on o.id = i.product_order_id
2025-12-01 18:50:02.787  INFO 28600 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select  * from product_order_0 o left join product_order_item_0 i on o.id = i.product_order_id
2025-12-01 18:50:02.787  INFO 28600 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select  * from product_order_0 o left join product_order_item_1 i on o.id = i.product_order_id