You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@shardingsphere.apache.org by tomakemyself <27...@qq.com> on 2019/11/18 12:56:49 UTC
绑定表配置-出现笛卡尔积现象-麻烦回复，微信群要求提邮箱，但是邮箱也没人回复吗？
大佬，这个绑定表貌似没用啊！可以给解答一下吗？
以下是我的配置，这个配置只有一个数据源：db实例下表被切分成两步分
t_order_0，t_order_1；t_order_item_0，t_order_item_1
# 配置ds0 和ds1两个数据源
spring.shardingsphere.datasource.names=ds0
#ds0 配置
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.241.198:3306/shop_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root
#绑定表配置
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
spring.shardingsphere.sharding.broadcast-tables=t_address
# 具体分表策略
# 节点 ds0.t_order_0,ds0.t_order_1,ds1.t_order_0,ds1.t_order_1
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order_$->{0..1}
# 分表字段id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
# 分表策略 根据id取模,确定数据最终落在那个表中
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{order_id % 2}
# 使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123
# 节点
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds0.t_order_item_$->{0..1}
# 分表字段id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
# 分表策略 根据id取模,确定数据最终落在那个表中
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2}
# 使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=123执行sql语句；SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id = 0 or o.order_id = 1或者SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in(0,1)shardingsphere版本：<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC2</version>
</dependency>最终执行查询结果如下：2019-11-17 15:15:29.569 INFO 25344 --- [nio-8080-exec-1] ShardingSphere-SQL : Rule Type: sharding 2019-11-17 15:15:29.569 INFO 25344 --- [nio-8080-exec-1] ShardingSphere-SQL : Logic SQL: SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id = 0 or o.order_id = 1 2019-11-17 15:15:29.570 INFO 25344 --- [nio-8080-exec-1] ShardingSphere-SQL : SQLStatement: ShardingSelectOptimizedStatement(tables=Tables(tables=[Table(name=t_order, alias=Optional.of(o)), Table(name=t_order_item, alias=Optional.of(i))], schema=Optional.absent()), groupBy=org.apache.shardingsphere.core.optimize.sharding.segment.select.groupby.GroupBy@da3e8a8, orderBy=org.apache.shardingsphere.core.optimize.sharding.segment.select.orderby.OrderBy@72768b84, selectItems=SelectItems(startIndex=7, stopIndex=9, distinctRow=false, items=[ShorthandSelectItem(owner=Optional.of(i))], tables=[TableSegment(startIndex=16, stopIndex=22, name=t_order, quoteCharacter=NONE, owner=Optional.absent(), alias=Optional.of(o)), TableSegment(startIndex=31, stopIndex=42, name=t_order_item, quoteCharacter=NONE, owner=Optional.absent(), alias=Optional.of(i))]), pagination=org.apache.shardingsphere.core.optimize.sharding.segment.select.pagination.Pagination@2b7a3dfe, containsSubquery=false) 2019-11-17 15:15:29.570 INFO 25344 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id = 0 or o.order_id = 1 2019-11-17 15:15:29.570 INFO 25344 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id = 0 or o.order_id = 1 2019-11-17 15:15:29.570 INFO 25344 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id = 0 or o.order_id = 1 2019-11-17 15:15:29.570 INFO 25344 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id = 0 or o.order_id = 1这里依然出现了笛卡尔积，我的理解是这里不应该出现笛卡尔积现象。请问为什么会出现这种情况？
Re: 绑定表配置-出现笛卡尔积现象-麻烦回复，微信群要求提邮箱，但是邮箱也没人回复吗？
Posted by "zhangliang@apache.org" <zh...@apache.org>.
Hello, for English only, please translate, thank you.
------------------
Liang Zhang (John)
Apache ShardingSphere & Dubbo
tomakemyself <27...@qq.com> 于2019年11月18日周一 下午10:10写道：
> 大佬，这个绑定表貌似没用啊！可以给解答一下吗？
> 以下是我的配置，这个配置只有一个数据源：db实例下表被切分成两步分
> t_order_0，t_order_1；t_order_item_0，t_order_item_1
> # 配置ds0 和ds1两个数据源
> spring.shardingsphere.datasource.names=ds0
> #ds0 配置
>
> spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
>
> spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
> spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://
> 192.168.241.198:3306/shop_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
> spring.shardingsphere.datasource.ds0.username=root
> spring.shardingsphere.datasource.ds0.password=root
> #绑定表配置
> spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
> spring.shardingsphere.sharding.broadcast-tables=t_address
>
> # 具体分表策略
> # 节点 ds0.t_order_0,ds0.t_order_1,ds1.t_order_0,ds1.t_order_1
>
> spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order_$->{0..1}
> # 分表字段id
>
> spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
> # 分表策略 根据id取模,确定数据最终落在那个表中
> spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression
> = t_order_$->{order_id % 2}
> # 使用SNOWFLAKE算法生成主键
> spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
> spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
> spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id
> =123
> # 节点
>
> spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds0.t_order_item_$->{0..1}
> # 分表字段id
>
> spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
> # 分表策略 根据id取模,确定数据最终落在那个表中
> spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id
> % 2}
> # 使用SNOWFLAKE算法生成主键
>
> spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
>
> spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
>
> spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=123执行sql语句；SELECT
> i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE
> o.order_id = 0 or o.order_id = 1或者SELECT i.* FROM t_order o JOIN
> t_order_item i ON o.order_id=i.order_id WHERE o.order_id
> in(0,1)shardingsphere版本：<dependency>
> <groupId>org.apache.shardingsphere</groupId>
> <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
> <version>4.0.0-RC2</version>
> </dependency>最终执行查询结果如下：2019-11-17 15:15:29.569 INFO 25344 ---
> [nio-8080-exec-1] ShardingSphere-SQL : Rule Type:
> sharding 2019-11-17 15:15:29.569 INFO 25344 --- [nio-8080-exec-1]
> ShardingSphere-SQL : Logic SQL: SELECT i.* FROM
> t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id = 0
> or o.order_id = 1 2019-11-17 15:15:29.570 INFO 25344 --- [nio-8080-exec-1]
> ShardingSphere-SQL : SQLStatement:
> ShardingSelectOptimizedStatement(tables=Tables(tables=[Table(name=t_order,
> alias=Optional.of(o)), Table(name=t_order_item, alias=Optional.of(i))],
> schema=Optional.absent()),
> groupBy=org.apache.shardingsphere.core.optimize.sharding.segment.select.groupby.GroupBy@da3e8a8,
> orderBy=org.apache.shardingsphere.core.optimize.sharding.segment.select.orderby.OrderBy@72768b84,
> selectItems=SelectItems(startIndex=7, stopIndex=9, distinctRow=false,
> items=[ShorthandSelectItem(owner=Optional.of(i))],
> tables=[TableSegment(startIndex=16, stopIndex=22, name=t_order,
> quoteCharacter=NONE, owner=Optional.absent(), alias=Optional.of(o)),
> TableSegment(startIndex=31, stopIndex=42, name=t_order_item,
> quoteCharacter=NONE, owner=Optional.absent(), alias=Optional.of(i))]),
> pagination=org.apache.shardingsphere.core.optimize.sharding.segment.select.pagination.Pagination@2b7a3dfe,
> containsSubquery=false) 2019-11-17 15:15:29.570 INFO 25344 ---
> [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL:
> ds0 ::: SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON
> o.order_id=i.order_id WHERE o.order_id = 0 or o.order_id = 1 2019-11-17
> 15:15:29.570 INFO 25344 --- [nio-8080-exec-1] ShardingSphere-SQL
> : Actual SQL: ds0 ::: SELECT i.* FROM t_order_0 o JOIN
> t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id = 0 or
> o.order_id = 1 2019-11-17 15:15:29.570 INFO 25344 --- [nio-8080-exec-1]
> ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT i.*
> FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE
> o.order_id = 0 or o.order_id = 1 2019-11-17 15:15:29.570 INFO 25344 ---
> [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL:
> ds0 ::: SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON
> o.order_id=i.order_id WHERE o.order_id = 0 or o.order_id =
> 1这里依然出现了笛卡尔积，我的理解是这里不应该出现笛卡尔积现象。请问为什么会出现这种情况？