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/07/02 05:57:54 UTC
[GitHub] [shardingsphere] flyduckforever opened a new issue, #18783: Why did the order details appear in the full table scan when joining
flyduckforever opened a new issue, #18783:
URL: https://github.com/apache/shardingsphere/issues/18783
> For English only, other languages will not accept. Before report a
> bug, make sure you have: Searched open and closed [GitHub
> issues](https://github.com/apache/shardingsphere/issues). Read
> documentation: [ShardingSphere
> Doc](https://shardingsphere.apache.org/document/current/en/overview).
> Please pay attention on issues you submitted, because we maybe need
> more details. If no response anymore and we cannot reproduce it on
> current information, we will close it. Please answer these questions
> before submitting your issue. Thanks!*
## Which version of ShardingSphere did you use?
5.2.1
## Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
## Expected behavior
I segment orders and order details according to the quarter
Logic SQL:
`SELECT count(0) FROM dp_trade_payment_order dp LEFT JOIN dp_trade_payment_record r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'`
Actual SQL:
`
SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2022_03 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'`
## Actual behavior
Logic SQL:
`SELECT count(0) FROM dp_trade_payment_order dp LEFT JOIN dp_trade_payment_record r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'`
Actual SQL:
`
SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2020_01 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'`
`SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2020_02 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'`
`SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2020_03 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'`
`SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2020_04 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'`
`SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2021_01 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'`
.....
## Reason analyze (If you can)
1、My binding table configuration error
2、My custom fragment rule is wrong
## Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
yml：
```yaml
# 数据源配置
spring:
# 配置sharding jdbc分片规则
shardingsphere:
props:
sql-show: true
datasource:
# 连接名称（下面要用这个名称来区分库）
names: ds0
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://123.56.4.237:3306/decard_pay_dev?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8
username: qiqi
password: qiqi
# 配置分片规则
rules:
sharding:
tables:
dp_trade_payment_order:
# 配置数据节点
actual-data-nodes: ds0.dp_trade_payment_order_$->{2020..2025}_0$->{1..4}
# 分表策略
table-strategy:
complex:
sharding-columns: platform_order_no
sharding-algorithm-name: order-algorithm
dp_trade_payment_record:
# 配置数据节点
actual-data-nodes: ds0.dp_trade_payment_record_$->{2020..2025}_0$->{1..4}
# 分表策略
table-strategy:
complex:
sharding-columns: platform_order_no
sharding-algorithm-name: order-algorithm
sharding-algorithms:
order-algorithm:
type: CLASS_BASED
props:
strategy: COMPLEX
algorithmClassName: com.decard.sharding.config.OrderComplexKeysShardingAlgorithm
bindingTables: dp_trade_payment_order,dp_trade_payment_record
```
Custom sharding rules：
```java
package com.decard.sharding.config;
import com.alibaba.fastjson.JSONObject;
import com.decard.sharding.config.parser.OrderTimeParser;
import com.decard.sharding.config.parser.PaySuccessTimeParser;
import com.decard.sharding.config.parser.PlatformOrderNoParser;
import com.decard.sharding.util.DateUtils;
import com.google.common.collect.Range;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingValue;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;
/**
* 复合分片算法
*/
@Slf4j
public class OrderComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm {
/**
* 平台订单号列名
*/
private static final String COLUMN_PLATFORM_ORDER_NO = "platform_order_no";
/**
* 订单时间列名
*/
private static final String COLUMN_ORDER_TIME = "order_time";
/**
* 支付成功时间列名
*/
private static final String COLUMN_PAY_SUCCESS_TIME = "pay_success_time";
private PlatformOrderNoParser platformOrderNoParser = new PlatformOrderNoParser();
private OrderTimeParser orderTimeParser = new OrderTimeParser();
private PaySuccessTimeParser paySuccessTimeParser = new PaySuccessTimeParser();
@Override
public Collection<String> doSharding(Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {
Set<String> tables = new HashSet<>();
log.info("\navailableTargetNames:======>>\n{}", JSONObject.toJSONString(availableTargetNames, true));
log.info("\nshardingValue:======>>\n{}", JSONObject.toJSONString(shardingValue, true));
//逻辑表名
String logicTableName = shardingValue.getLogicTableName();
// 平台订单号
Collection<Comparable> platformOrderNoList = (Collection<Comparable>) shardingValue.getColumnNameAndShardingValuesMap().getOrDefault(COLUMN_PLATFORM_ORDER_NO,new ArrayList<>(1));
Set<String> platformTableSet = platformOrderNoList.stream()
.map(item -> DateUtils.getYearAndQuarter(platformOrderNoParser.apply(item)))
.filter(item -> StringUtils.isNotBlank(item))
.map(item -> logicTableName + "_" + item)
.collect(Collectors.toSet());
Range<Comparable> platformOrderNoRange = (Range<Comparable>) shardingValue.getColumnNameAndRangeValuesMap().get(COLUMN_PLATFORM_ORDER_NO);
Set<String> platformOrderNoRangeSet = getTablesByRange(platformOrderNoParser, platformOrderNoRange, availableTargetNames);
// 订单时间
Collection<Comparable> orderTimeList = (Collection<Comparable>) shardingValue.getColumnNameAndShardingValuesMap().getOrDefault(COLUMN_ORDER_TIME,new ArrayList<>(1));
Set<String> orderTimeSet = orderTimeList.stream()
.map(item -> DateUtils.getYearAndQuarter(orderTimeParser.apply(item)))
.filter(item -> StringUtils.isNotBlank(item))
.map(item -> logicTableName + "_" + item)
.collect(Collectors.toSet());
Range<Comparable> orderTimeRange = (Range<Comparable>) shardingValue.getColumnNameAndRangeValuesMap().get(COLUMN_ORDER_TIME);
Set<String> orderTimeRangeSet = getTablesByRange(orderTimeParser, orderTimeRange, availableTargetNames);
// 支付成功时间
Collection<Comparable> paySuccessTimeList = (Collection<Comparable>) shardingValue.getColumnNameAndShardingValuesMap().getOrDefault(COLUMN_PAY_SUCCESS_TIME,new ArrayList<>(1));
Set<String> paySuccessTimeTableSet = paySuccessTimeList.stream()
.map(item -> DateUtils.getYearAndQuarter(paySuccessTimeParser.apply(item)))
.filter(item -> StringUtils.isNotBlank(item))
.map(item -> logicTableName + "_" + item)
.collect(Collectors.toSet());
Range<Comparable> paySuccessTimeRange = (Range<Comparable>) shardingValue.getColumnNameAndRangeValuesMap().get(COLUMN_PAY_SUCCESS_TIME);
Set<String> paySuccessTimeRangeSet = getTablesByRange(paySuccessTimeParser, paySuccessTimeRange, availableTargetNames);
tables.addAll(platformTableSet);
tables.addAll(platformOrderNoRangeSet);
tables.addAll(orderTimeSet);
tables.addAll(orderTimeRangeSet);
tables.addAll(paySuccessTimeTableSet);
tables.addAll(paySuccessTimeRangeSet);
log.info("\n路由的表:======>>\n{}", JSONObject.toJSONString(tables, true));
return tables;
}
private Set<String> getTablesByRange(Function<Comparable,Date> parser, Range<Comparable> range, Collection<String> availableTargetNames){
Set<String> tables = new HashSet<>();
if(range == null){
return tables;
}
Date beginTime = null;
Date endTime = null;
String begin = "";
String end = "";
if (range.hasLowerBound()) {
Comparable lowerEndpoint = range.lowerEndpoint();
beginTime = parser.apply(lowerEndpoint);
begin = DateUtils.getYearAndQuarter(beginTime);
}
if (range.hasUpperBound()) {
Comparable upperEndpoint = range.upperEndpoint();
endTime = parser.apply(upperEndpoint);
end = DateUtils.getYearAndQuarter(endTime);
}
if(beginTime != null && endTime != null && beginTime.compareTo(endTime) > 0){
return tables;
}
for (String availableTargetName : availableTargetNames) {
String substring = availableTargetName.substring(availableTargetName.length() - 7);
if(beginTime != null && endTime != null){
if (convertInt(substring) >= convertInt(begin) && convertInt(substring) <= convertInt(end)) {
tables.add(availableTargetName);
}
}else if(beginTime != null && convertInt(substring) >= convertInt(begin)){
tables.add(availableTargetName);
}else if(endTime != null && convertInt(substring) <= convertInt(end)){
tables.add(availableTargetName);
}
}
return tables;
}
/**
* @return
*/
public int convertInt(String x){
x = StringUtils.remove(x, "_");
return Integer.parseInt(x);
}
@Override
public Properties getProps() {
return null;
}
@Override
public void init(Properties properties) {
}
}
```
·
## Example codes for reproduce this issue (such as a github link).
--
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] flyduckforever commented on issue #18783: Why did the order details appear in the full table scan when joining
Posted by GitBox <gi...@apache.org>.
flyduckforever commented on issue #18783:
URL: https://github.com/apache/shardingsphere/issues/18783#issuecomment-1177160923
Logical table cannot be followed by multiple underscores, only one can be followed，Amend to read as follows
ds0.dp_trade_payment_order_$->{2020..2025}$->{1..4}
--
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] flyduckforever closed issue #18783: Why did the order details appear in the full table scan when joining
Posted by GitBox <gi...@apache.org>.
flyduckforever closed issue #18783: Why did the order details appear in the full table scan when joining
URL: https://github.com/apache/shardingsphere/issues/18783
--
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