You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "aligaduo112358 (via GitHub)" <gi...@apache.org> on 2023/03/16 04:54:25 UTC
[GitHub] [shardingsphere] aligaduo112358 opened a new issue, #24640: How to use the bindingTables when I use the hint algorithm?
aligaduo112358 opened a new issue, #24640:
URL: https://github.com/apache/shardingsphere/issues/24640
version：
org.apache.shardingsphere
sharding-jdbc-spring-boot-starter
5.2.1
application.yml
```yml
spring:
shardingsphere:
datasource:
names: db0
db0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://xxx.xxx.xxx.xxx:3306/db?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 123456
data-source-properties:
useLocalSessionState: false
props:
sql-show: true
rules:
bindingTables:
- t_order,t_order_item
sharding:
tables:
t_order:
actual-data-nodes: db0.t_order_$->{[${stores}]}
t_order_item:
actual-data-nodes: db0.t_order_item_$->{[${stores}]}
sharding-algorithms:
table-tenant:
type: table-tenant
props:
strategy: HINT
algorithmClassName: com.xxx.server.domain.base.shardingjdbc.TableHintShardingAlgorithm
defaultTableStrategy:
hint:
sharding-algorithm-name: table-tenant
auditors:
shardingKeyAudit:
type: DML_SHARDING_CONDITIONS
```
store.yml
```yml
stores: "'1113','1000','5225'"
```
TableHintShardingAlgorithm.java
```java
public class TableHintShardingAlgorithm implements HintShardingAlgorithm<String> {
@Override
public Collection<String> doSharding(Collection<String> collection, HintShardingValue<String> hintShardingValue) {
Collection<String> hintShardingValues = hintShardingValue.getValues();
Collection<String> result = new ArrayList<>();
for (String key : hintShardingValues) {
result.add(hintShardingValue.getLogicTableName() + "_" + key);
}
return result;
}
@Override
public Properties getProps() {
return null;
}
@Override
public String getType() {
return "table-tenant";
}
@Override
public void init(Properties properties) {
// Do nothing
}
}
```
Logic SQL:
```sql
select order.* from t_order order,t_order_item item where item.order_id = order.order_id and item.product_code='1111111';
```
I hope to get the following results：
Actual SQL:
```sql
select order.* from t_order_1113 order,t_order_item_1113 item where item.order_id = order.order_id and item.product_code='1111111'
select order.* from t_order_1000 order,t_order_item_1000 item where item.order_id = order.order_id and item.product_code='1111111';
select order.* from t_order_5225 order,t_order_item_5225 item where item.order_id = order.order_id and item.product_code='1111111';
```
but, I got this
```sql
select order.* from t_order_1113 order,t_order_item_1113 item where item.order_id = order.order_id and item.product_code='1111111'
select order.* from t_order_1113 order,t_order_item_1000 item where item.order_id = order.order_id and item.product_code='1111111';
select order.* from t_order_1113 order,t_order_item_5225 item where item.order_id = order.order_id and item.product_code='1111111';
select order.* from t_order_1000 order,t_order_item_1113 item where item.order_id = order.order_id and item.product_code='1111111'
select order.* from t_order_1000 order,t_order_item_1000 item where item.order_id = order.order_id and item.product_code='1111111';
select order.* from t_order_1000 order,t_order_item_5225 item where item.order_id = order.order_id and item.product_code='1111111';
select order.* from t_order_5225 order,t_order_item_1113 item where item.order_id = order.order_id and item.product_code='1111111'
select order.* from t_order_5225 order,t_order_item_1000 item where item.order_id = order.order_id and item.product_code='1111111';
select order.* from t_order_5225 order,t_order_item_5225 item where item.order_id = order.order_id and item.product_code='1111111';
```
I can't find the detailed description of the bindingTables. Can I get help？
How should I configure it to achieve the desired effect？
--
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] aligaduo112358 commented on issue #24640: How to use the bindingTables when I use the hint algorithm?
Posted by "aligaduo112358 (via GitHub)" <gi...@apache.org>.
aligaduo112358 commented on issue #24640:
URL: https://github.com/apache/shardingsphere/issues/24640#issuecomment-1475731491
> Hi @aligaduo112358, there seems to be some problems when the Hint strategy uses the binding table, because the binding table cannot provide the sharding column, so we cannot judge whether the join query uses the sharding column.
>
> When the actual join query does not use the sharding column, it will be regarded as a non-binding table relationship, and a cartesian product will appear.
Thank you for your reply. And does this mean that the Hint strategy does not support the 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] strongduanmu commented on issue #24640: How to use the bindingTables when I use the hint algorithm?
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24640:
URL: https://github.com/apache/shardingsphere/issues/24640#issuecomment-1471433399
Please try to config `HintShardingStrategy` and `shardingColumns`.
--
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] strongduanmu commented on issue #24640: How to use the bindingTables when I use the hint algorithm?
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24640:
URL: https://github.com/apache/shardingsphere/issues/24640#issuecomment-1473409482
Hi @aligaduo112358, there seems to be some problems when the Hint strategy uses the binding table, because the binding table cannot provide the sharding column, so we cannot judge whether the join query uses the sharding column.
When the actual join query does not use the sharding column, it will be regarded as a non-binding table relationship, and a cartesian product will appear.
--
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] aligaduo112358 commented on issue #24640: How to use the bindingTables when I use the hint algorithm?
Posted by "aligaduo112358 (via GitHub)" <gi...@apache.org>.
aligaduo112358 commented on issue #24640:
URL: https://github.com/apache/shardingsphere/issues/24640#issuecomment-1471378755
I have 1500 stores, which means I have 1500 sets of logical tables. It is acceptable to execute 1500 sql, but 1500 * 1500 sql is not acceptable.
--
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] strongduanmu commented on issue #24640: How to use the bindingTables when I use the hint algorithm?
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24640:
URL: https://github.com/apache/shardingsphere/issues/24640#issuecomment-1473413195
The sharding columns of the Hint strategy are all imported from the outside, and it is a reasonable way to use cartesian product routing.
--
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] aligaduo112358 commented on issue #24640: How to use the bindingTables when I use the hint algorithm?
Posted by "aligaduo112358 (via GitHub)" <gi...@apache.org>.
aligaduo112358 commented on issue #24640:
URL: https://github.com/apache/shardingsphere/issues/24640#issuecomment-1473089987
I found this information and made an attempt。
I specified the shard keys to be '1000', but the corresponding binding table is not effective!
Is there a problem with my 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] aligaduo112358 commented on issue #24640: How to use the bindingTables when I use the hint algorithm?
Posted by "aligaduo112358 (via GitHub)" <gi...@apache.org>.
aligaduo112358 commented on issue #24640:
URL: https://github.com/apache/shardingsphere/issues/24640#issuecomment-1471367784
Does not the bindingTables mean that tables with binding relationship use the same shard keys？
--
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