You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/12/28 01:09:02 UTC
[GitHub] [shardingsphere] pengxianggui opened a new issue, #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
pengxianggui opened a new issue, #23128:
URL: https://github.com/apache/shardingsphere/issues/23128
I have a question, take the official demo for example.
I used the binding table to avoid the Cartesian product, so I must use orderId as the sharding field, but in reality, I need to sharding by year, so I should take order_time as the sharding field. Because there are only interval conditions for orderTime in the paging query condition.
How can I avoid Cartesian products in associative table queries while using order_time sharding?
Thanks for your answer!
我有一个问题，以官方的demo为例。
我使用绑定表避免笛卡尔积，因此我必须使用orderId作为分片字段，但是实际情况，我需要按年份分片，我需要取order_time作为分片字段。因为分页查询条件里只有针对orderTime的区间条件。
我如何在使用orderTime分片的同时，又能在关联表查询中避免笛卡尔积呢？
感谢您的解答！
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #23128:
URL: https://github.com/apache/shardingsphere/issues/23128#issuecomment-1366432518
You may set `sql-show: true` to log SQL and check whether the actual SQL perform cartesian product.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] pengxianggui closed issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
pengxianggui closed issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
URL: https://github.com/apache/shardingsphere/issues/23128
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] pengxianggui commented on issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
pengxianggui commented on issue #23128:
URL: https://github.com/apache/shardingsphere/issues/23128#issuecomment-1369414899
> Hi @pengxianggui Could you try the following configuration?
>
> ```yaml
> bindingTables:
> - t_alarm,t_sensor_alarm,t_video_alarm,t_device_offline_alarm,t_alarm_handle
> ```
It works! Very much!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] pengxianggui commented on issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
pengxianggui commented on issue #23128:
URL: https://github.com/apache/shardingsphere/issues/23128#issuecomment-1369311624
> Please show us your configuration.
```yml
rules:
- !SHARDING
broadcastTables:
- t_alarm_domain # 广播表规则列表
bindingTables: # 绑定表规则列表。见: https://github.com/apache/shardingsphere/issues/21002
- t_alarm,t_sensor_alarm
- t_alarm,t_video_alarm
- t_alarm,t_device_offline_alarm
- t_alarm,t_alarm_handle
tables: # 单分片键的标准分片场景
t_alarm:
actualDataNodes: db0.t_alarm_$->{2022..2025}
tableStrategy:
standard:
shardingColumn: alarm_time
shardingAlgorithmName: time-inline
t_sensor_alarm:
actualDataNodes: db0.t_sensor_alarm_$->{2022..2025}
tableStrategy:
standard:
shardingColumn: alarm_time
shardingAlgorithmName: time-inline
t_video_alarm:
actualDataNodes: db0.t_video_alarm_$->{2022..2025}
tableStrategy:
standard:
shardingColumn: alarm_time
shardingAlgorithmName: time-inline
t_device_offline_alarm:
actualDataNodes: db0.t_device_offline_alarm_$->{2022..2025}
tableStrategy:
standard:
shardingColumn: alarm_time
shardingAlgorithmName: time-inline
t_alarm_handle:
actualDataNodes: db0.t_alarm_handle_$->{2022..2025}
tableStrategy:
standard:
shardingColumn: alarm_time
shardingAlgorithmName: time-inline
shardingAlgorithms:
time-inline:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: cn.com.asoco.alarm_platform.server.sharding.AlarmTimeShardingAlgorithm # 使用自定义类实现分片逻辑处理
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #23128:
URL: https://github.com/apache/shardingsphere/issues/23128#issuecomment-1366417170
If you don't need to shard tables by `order_id`, and all tables sharding by `order_time`, then these tables could be binding tables.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] pengxianggui commented on issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
pengxianggui commented on issue #23128:
URL: https://github.com/apache/shardingsphere/issues/23128#issuecomment-1366430239
> If you don't need to shard tables by `order_id`, and all tables sharding by `order_time`, then these tables could be binding tables.
As you said, does the following sql have cartesian product problems?
> The only sharding field for both tables is order_time, and the binding table is configured correctly. Notice that I haven't added a time condition to the left join clause. I'm using version ShardingSphere-JDBC 5.1.1
```sql
select o.* from t_order o
left join t_order_item oi on o.order_id = oi.order_id
where o.order_time > xxx and o.order_time < yyyy
```
如你所说，下面这条sql会出现笛卡尔积问题吗?
> 两个表的分片字段都是order_time, 并且正确配置了绑定表。注意, 我并没有在left join 子句里添加时间条件。我正在使用的版本是ShardingSphere-JDBC 5.1.1
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] pengxianggui commented on issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
pengxianggui commented on issue #23128:
URL: https://github.com/apache/shardingsphere/issues/23128#issuecomment-1367062228
> You may set `sql-show: true` to log SQL and check whether the actual SQL perform cartesian product.
Hi @TeslaCN , I turned on sql printing and It showed a very interesting phenomenon. The sql looks like the follow:
```
select o.* from t_order o
left join t_order_item oi on o.order_id = oi.order_id
left join t_order_item1 oi1 on o.order_id = oi1.order_id
where o.order_time > xxx and o.order_time < yyyy
```
> order_time is the only sharding field
**The result shows that the first left join still associates all the real tables, and the second left join avoids the Cartesian product**
The above sql is for the convenience of describing the simplified results. In the following log, you can see the actual sql:
```
2022-12-29 11:56:15.534 [[TID: N/A]] [http-nio-8082-exec-1] INFO 56625 ShardingSphere-SQL -Logic SQL: select
a.id id, a.report_time, a.alarm_time, a.level, a.type,
a.name, a.remark, a.longitude, a.latitude, a.address, a.origin_data,
a.responsible, a.responsible_phone, a.contact, a.contact_phone,
a.from_where, a.from_where_code, a.from_where_remark, a.if_pre, a.domain_code,
h.id handle_id, h.handler, h.handle_time, h.handle_type, h.handle_advice,
h.if_enable_emergency_res, h.emergency_res_review, h.attachment_url, h.handle_source,
sa.origin_id, sa.alarm_id, sa.sensor_name, sa.sensor_target_code, sa.alarm_value,
sa.upper_value, sa.lower_value, sa.sensor_unit, sa.credit_code, sa.enterprise_name,
sa.device, sa.materials, sa.hazard_source_code, sa.hazard_source_name, sa.hazard_source_level,
sa.hazard_source_location
from t_alarm a
left join t_sensor_alarm sa on a.id = sa.alarm_id
left join t_alarm_handle h on a.id = h.alarm_id
where 1 = 1 and a.alarm_time >= ? and a.alarm_time <= ? and a.if_pre = ? and (a.origin_data -> '$.normalDate' is not null)
order by a.alarm_time desc LIMIT ?
2022-12-29 11:56:15.535 [[TID: N/A]] [http-nio-8082-exec-1] INFO 56625 ShardingSphere-SQL -Actual SQL: db0 ::: select
a.id id, a.report_time, a.alarm_time, a.level, a.type,
a.name, a.remark, a.longitude, a.latitude, a.address, a.origin_data,
a.responsible, a.responsible_phone, a.contact, a.contact_phone,
a.from_where, a.from_where_code, a.from_where_remark, a.if_pre, a.domain_code,
h.id handle_id, h.handler, h.handle_time, h.handle_type, h.handle_advice,
h.if_enable_emergency_res, h.emergency_res_review, h.attachment_url, h.handle_source,
sa.origin_id, sa.alarm_id, sa.sensor_name, sa.sensor_target_code, sa.alarm_value,
sa.upper_value, sa.lower_value, sa.sensor_unit, sa.credit_code, sa.enterprise_name,
sa.device, sa.materials, sa.hazard_source_code, sa.hazard_source_name, sa.hazard_source_level,
sa.hazard_source_location
from t_alarm_2022 a
left join t_sensor_alarm_2022 sa on a.id = sa.alarm_id
left join t_alarm_handle_2022 h on a.id = h.alarm_id
where 1 = 1 and a.alarm_time >= ? and a.alarm_time <= ? and a.if_pre = ? and (a.origin_data -> '$.normalDate' is not null)
order by a.alarm_time desc LIMIT ? ::: [2022-06-29T11:56:10.437, 2022-12-29T11:56:10.437, false, 10]
2022-12-29 11:56:15.535 [[TID: N/A]] [http-nio-8082-exec-1] INFO 56625 ShardingSphere-SQL -Actual SQL: db0 ::: select
a.id id, a.report_time, a.alarm_time, a.level, a.type,
a.name, a.remark, a.longitude, a.latitude, a.address, a.origin_data,
a.responsible, a.responsible_phone, a.contact, a.contact_phone,
a.from_where, a.from_where_code, a.from_where_remark, a.if_pre, a.domain_code,
h.id handle_id, h.handler, h.handle_time, h.handle_type, h.handle_advice,
h.if_enable_emergency_res, h.emergency_res_review, h.attachment_url, h.handle_source,
sa.origin_id, sa.alarm_id, sa.sensor_name, sa.sensor_target_code, sa.alarm_value,
sa.upper_value, sa.lower_value, sa.sensor_unit, sa.credit_code, sa.enterprise_name,
sa.device, sa.materials, sa.hazard_source_code, sa.hazard_source_name, sa.hazard_source_level,
sa.hazard_source_location
from t_alarm_2022 a
left join t_sensor_alarm_2023 sa on a.id = sa.alarm_id
left join t_alarm_handle_2022 h on a.id = h.alarm_id
where
1 = 1
and a.alarm_time >= ?
and a.alarm_time <= ?
and a.if_pre = ?
and
(
a.origin_data -> '$.normalDate' is not null
)
order by a.alarm_time desc LIMIT ? ::: [2022-06-29T11:56:10.437, 2022-12-29T11:56:10.437, false, 10]
2022-12-29 11:56:15.535 [[TID: N/A]] [http-nio-8082-exec-1] INFO 56625 ShardingSphere-SQL -Actual SQL: db0 ::: select
a.id id, a.report_time, a.alarm_time, a.level, a.type,
a.name, a.remark, a.longitude, a.latitude, a.address, a.origin_data,
a.responsible, a.responsible_phone, a.contact, a.contact_phone,
a.from_where, a.from_where_code, a.from_where_remark, a.if_pre, a.domain_code,
h.id handle_id, h.handler, h.handle_time, h.handle_type, h.handle_advice,
h.if_enable_emergency_res, h.emergency_res_review, h.attachment_url, h.handle_source,
sa.origin_id, sa.alarm_id, sa.sensor_name, sa.sensor_target_code, sa.alarm_value,
sa.upper_value, sa.lower_value, sa.sensor_unit, sa.credit_code, sa.enterprise_name,
sa.device, sa.materials, sa.hazard_source_code, sa.hazard_source_name, sa.hazard_source_level,
sa.hazard_source_location
from t_alarm_2022 a
left join t_sensor_alarm_2024 sa on a.id = sa.alarm_id
left join t_alarm_handle_2022 h on a.id = h.alarm_id
where
1 = 1
and a.alarm_time >= ?
and a.alarm_time <= ?
and a.if_pre = ?
and
(
a.origin_data -> '$.normalDate' is not null
)
order by a.alarm_time desc LIMIT ? ::: [2022-06-29T11:56:10.437, 2022-12-29T11:56:10.437, false, 10]
2022-12-29 11:56:15.535 [[TID: N/A]] [http-nio-8082-exec-1] INFO 56625 ShardingSphere-SQL -Actual SQL: db0 ::: select
a.id id, a.report_time, a.alarm_time, a.level, a.type,
a.name, a.remark, a.longitude, a.latitude, a.address, a.origin_data,
a.responsible, a.responsible_phone, a.contact, a.contact_phone,
a.from_where, a.from_where_code, a.from_where_remark, a.if_pre, a.domain_code,
h.id handle_id, h.handler, h.handle_time, h.handle_type, h.handle_advice,
h.if_enable_emergency_res, h.emergency_res_review, h.attachment_url, h.handle_source,
sa.origin_id, sa.alarm_id, sa.sensor_name, sa.sensor_target_code, sa.alarm_value,
sa.upper_value, sa.lower_value, sa.sensor_unit, sa.credit_code, sa.enterprise_name,
sa.device, sa.materials, sa.hazard_source_code, sa.hazard_source_name, sa.hazard_source_level,
sa.hazard_source_location
from t_alarm_2022 a
left join t_sensor_alarm_2025 sa on a.id = sa.alarm_id
left join t_alarm_handle_2022 h on a.id = h.alarm_id
where
1 = 1
and a.alarm_time >= ?
and a.alarm_time <= ?
and a.if_pre = ?
and
(
a.origin_data -> '$.normalDate' is not null
)
order by a.alarm_time desc LIMIT ? ::: [2022-06-29T11:56:10.437, 2022-12-29T11:56:10.437, false, 10]
```
The Actual sql displays that t_sensor_alarm exist cartesian product problem and t_alarm_handle avoids cartesian product problem. I guess if I left join the third table, it turns out that only the third table avoids the Cartesian product problem. I don't know if this is a bug or an expected result?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] pengxianggui commented on issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
pengxianggui commented on issue #23128:
URL: https://github.com/apache/shardingsphere/issues/23128#issuecomment-1367064618
>
I try to remove the second left join, But it doesn't, as I expected, avoid the Cartesian product in the first left join...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #23128:
URL: https://github.com/apache/shardingsphere/issues/23128#issuecomment-1367229133
Please show us your configuration.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] pengxianggui commented on issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
pengxianggui commented on issue #23128:
URL: https://github.com/apache/shardingsphere/issues/23128#issuecomment-1369312322
> Please show us your configuration.
```yml
rules:
- !SHARDING
broadcastTables:
- t_alarm_domain # 广播表规则列表
bindingTables: # 绑定表规则列表。见: https://github.com/apache/shardingsphere/issues/21002
- t_alarm,t_sensor_alarm
- t_alarm,t_video_alarm
- t_alarm,t_device_offline_alarm
- t_alarm,t_alarm_handle
tables: # 单分片键的标准分片场景
t_alarm:
actualDataNodes: db0.t_alarm_$->{2022..2025}
tableStrategy:
standard:
shardingColumn: alarm_time
shardingAlgorithmName: time-inline
t_sensor_alarm:
actualDataNodes: db0.t_sensor_alarm_$->{2022..2025}
tableStrategy:
standard:
shardingColumn: alarm_time
shardingAlgorithmName: time-inline
t_video_alarm:
actualDataNodes: db0.t_video_alarm_$->{2022..2025}
tableStrategy:
standard:
shardingColumn: alarm_time
shardingAlgorithmName: time-inline
t_device_offline_alarm:
actualDataNodes: db0.t_device_offline_alarm_$->{2022..2025}
tableStrategy:
standard:
shardingColumn: alarm_time
shardingAlgorithmName: time-inline
t_alarm_handle:
actualDataNodes: db0.t_alarm_handle_$->{2022..2025}
tableStrategy:
standard:
shardingColumn: alarm_time
shardingAlgorithmName: time-inline
shardingAlgorithms:
time-inline:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: cn.com.asoco.alarm_platform.server.sharding.AlarmTimeShardingAlgorithm # 使用自定义类实现分片逻辑处理
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23128: In the official demo, if I have to use order_time as the sharding field, how do I use the binding table to avoid the Cartesian product?
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #23128:
URL: https://github.com/apache/shardingsphere/issues/23128#issuecomment-1369345175
Hi @pengxianggui
Could you try the following configuration?
```yaml
bindingTables:
- t_alarm,t_sensor_alarm,t_video_alarm,t_device_offline_alarm,t_alarm_handle
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org