You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2021/08/16 14:16:28 UTC
[shardingsphere] branch master updated: optimize performance for
delete, update and insert statement (#11847)
This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 35ff166 optimize performance for delete, update and insert statement (#11847)
35ff166 is described below
commit 35ff1665fc69ac520be3481278a124eadab9d5ee
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Mon Aug 16 22:15:52 2021 +0800
optimize performance for delete, update and insert statement (#11847)
* optimize performance for delete, update and insert statement
* optimize multi tables check logic
* fix test case
---
.../validator/dml/ShardingDMLStatementValidator.java | 19 ++++++-------------
.../dml/impl/ShardingDeleteStatementValidator.java | 2 +-
.../dml/impl/ShardingInsertStatementValidator.java | 2 +-
.../dml/impl/ShardingUpdateStatementValidator.java | 2 +-
.../dml/ShardingDeleteStatementValidatorTest.java | 7 ++++---
.../dml/ShardingInsertStatementValidatorTest.java | 7 +++----
.../dml/ShardingUpdateStatementValidatorTest.java | 6 +++---
.../infra/binder/segment/table/TablesContext.java | 3 +--
.../binder/statement/dml/DeleteStatementContext.java | 12 +++++++-----
.../binder/statement/dml/InsertStatementContext.java | 17 ++++++-----------
.../binder/statement/dml/UpdateStatementContext.java | 12 +++++++-----
11 files changed, 40 insertions(+), 49 deletions(-)
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingDMLStatementValidator.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingDMLStatementValidator.java
index 5445a26..c1eb2da 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingDMLStatementValidator.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingDMLStatementValidator.java
@@ -34,7 +34,6 @@ import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
import org.apache.shardingsphere.sql.parser.sql.common.util.SafeNumberOperationUtil;
import java.util.Collection;
-import java.util.LinkedList;
import java.util.Optional;
/**
@@ -43,27 +42,21 @@ import java.util.Optional;
public abstract class ShardingDMLStatementValidator<T extends SQLStatement> implements ShardingStatementValidator<T> {
/**
- * Validate sharding multiple table.
+ * Validate multiple table.
*
* @param shardingRule sharding rule
* @param sqlStatementContext sqlStatementContext
*/
- protected void validateShardingMultipleTable(final ShardingRule shardingRule, final SQLStatementContext<T> sqlStatementContext) {
+ protected void validateMultipleTable(final ShardingRule shardingRule, final SQLStatementContext<T> sqlStatementContext) {
Collection<String> tableNames = sqlStatementContext.getTablesContext().getTableNames();
- Collection<String> shardingTableNames = shardingRule.getShardingLogicTableNames(tableNames);
- if ((1 == shardingTableNames.size() || shardingRule.isAllBindingTables(shardingTableNames)) && !isAllValidTables(shardingRule, tableNames)) {
+ boolean isAllShardingTables = shardingRule.isAllShardingTables(tableNames) && (1 == tableNames.size() || shardingRule.isAllBindingTables(tableNames));
+ boolean isAllBroadcastTables = shardingRule.isAllBroadcastTables(tableNames);
+ boolean isAllSingleTables = !shardingRule.tableRuleExists(tableNames);
+ if (!(isAllShardingTables || isAllBroadcastTables || isAllSingleTables)) {
throw new ShardingSphereException("Cannot support Multiple-Table for '%s'.", tableNames);
}
}
- private boolean isAllValidTables(final ShardingRule shardingRule, final Collection<String> tableNames) {
- Collection<String> allTableNames = new LinkedList<>(tableNames);
- allTableNames.removeAll(shardingRule.getShardingLogicTableNames(tableNames));
- allTableNames.removeAll(shardingRule.getBroadcastTables());
- // TODO validate other single table scenario
- return allTableNames.isEmpty();
- }
-
protected boolean checkSubqueryShardingValues(final ShardingRule shardingRule, final SQLStatementContext sqlStatementContext, final ShardingConditions shardingConditions) {
for (String each : sqlStatementContext.getTablesContext().getTableNames()) {
Optional<TableRule> tableRule = shardingRule.findTableRule(each);
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingDeleteStatementValidator.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingDeleteStatementValidator.java
index 79bd574..87cd470 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingDeleteStatementValidator.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingDeleteStatementValidator.java
@@ -36,7 +36,7 @@ public final class ShardingDeleteStatementValidator extends ShardingDMLStatement
@Override
public void preValidate(final ShardingRule shardingRule, final SQLStatementContext<DeleteStatement> sqlStatementContext,
final List<Object> parameters, final ShardingSphereSchema schema) {
- validateShardingMultipleTable(shardingRule, sqlStatementContext);
+ validateMultipleTable(shardingRule, sqlStatementContext);
}
@Override
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingInsertStatementValidator.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingInsertStatementValidator.java
index 851dd2f..69a502f 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingInsertStatementValidator.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingInsertStatementValidator.java
@@ -51,7 +51,7 @@ public final class ShardingInsertStatementValidator extends ShardingDMLStatement
public void preValidate(final ShardingRule shardingRule, final SQLStatementContext<InsertStatement> sqlStatementContext,
final List<Object> parameters, final ShardingSphereSchema schema) {
if (null == ((InsertStatementContext) sqlStatementContext).getInsertSelectContext()) {
- validateShardingMultipleTable(shardingRule, sqlStatementContext);
+ validateMultipleTable(shardingRule, sqlStatementContext);
}
InsertStatement sqlStatement = sqlStatementContext.getSqlStatement();
Optional<OnDuplicateKeyColumnsSegment> onDuplicateKeyColumnsSegment = InsertStatementHandler.getOnDuplicateKeyColumnsSegment(sqlStatement);
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingUpdateStatementValidator.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingUpdateStatementValidator.java
index 4f9b6d3..2f95141 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingUpdateStatementValidator.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/impl/ShardingUpdateStatementValidator.java
@@ -46,7 +46,7 @@ public final class ShardingUpdateStatementValidator extends ShardingDMLStatement
@Override
public void preValidate(final ShardingRule shardingRule, final SQLStatementContext<UpdateStatement> sqlStatementContext,
final List<Object> parameters, final ShardingSphereSchema schema) {
- validateShardingMultipleTable(shardingRule, sqlStatementContext);
+ validateMultipleTable(shardingRule, sqlStatementContext);
UpdateStatement sqlStatement = sqlStatementContext.getSqlStatement();
String tableName = sqlStatementContext.getTablesContext().getTables().iterator().next().getTableName().getIdentifier().getValue();
for (AssignmentSegment each : sqlStatement.getSetAssignment().getAssignments()) {
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingDeleteStatementValidatorTest.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingDeleteStatementValidatorTest.java
index 6636a8d..c2f43ab 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingDeleteStatementValidatorTest.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingDeleteStatementValidatorTest.java
@@ -40,6 +40,7 @@ import org.mockito.junit.MockitoJUnitRunner;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
+import java.util.HashSet;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.when;
@@ -82,9 +83,9 @@ public final class ShardingDeleteStatementValidatorTest {
tableSegment.getActualDeleteTables().add(new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("order_item"))));
sqlStatement.setTableSegment(tableSegment);
DeleteStatementContext sqlStatementContext = new DeleteStatementContext(sqlStatement);
- Collection<String> shardingTableNames = Arrays.asList("order", "order_item");
- when(shardingRule.getShardingLogicTableNames(sqlStatementContext.getTablesContext().getTableNames())).thenReturn(shardingTableNames);
- when(shardingRule.isAllBindingTables(shardingTableNames)).thenReturn(true);
+ Collection<String> tableNames = new HashSet<>(Arrays.asList("user", "order", "order_item"));
+ when(shardingRule.isAllShardingTables(tableNames)).thenReturn(false);
+ when(shardingRule.tableRuleExists(tableNames)).thenReturn(true);
new ShardingDeleteStatementValidator().preValidate(shardingRule, sqlStatementContext, Collections.emptyList(), mock(ShardingSphereSchema.class));
}
}
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingInsertStatementValidatorTest.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingInsertStatementValidatorTest.java
index 62880ce..b29dac8 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingInsertStatementValidatorTest.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingInsertStatementValidatorTest.java
@@ -48,7 +48,6 @@ import org.junit.runner.RunWith;
import org.mockito.Mock;
import org.mockito.junit.MockitoJUnitRunner;
-import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.LinkedList;
@@ -73,9 +72,9 @@ public final class ShardingInsertStatementValidatorTest {
@Test(expected = ShardingSphereException.class)
public void assertValidateInsertModifyMultiTables() {
SQLStatementContext<InsertStatement> sqlStatementContext = createInsertStatementContext(Collections.singletonList(1), createInsertStatement());
- Collection<String> shardingTableNames = Arrays.asList("order", "order_item");
- when(shardingRule.getShardingLogicTableNames(sqlStatementContext.getTablesContext().getTableNames())).thenReturn(shardingTableNames);
- when(shardingRule.isAllBindingTables(shardingTableNames)).thenReturn(true);
+ Collection<String> tableNames = sqlStatementContext.getTablesContext().getTableNames();
+ when(shardingRule.isAllShardingTables(tableNames)).thenReturn(false);
+ when(shardingRule.tableRuleExists(tableNames)).thenReturn(true);
new ShardingInsertStatementValidator(shardingConditions).preValidate(shardingRule, sqlStatementContext, Collections.emptyList(), mock(ShardingSphereSchema.class));
}
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingUpdateStatementValidatorTest.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingUpdateStatementValidatorTest.java
index 0cf5067..19a3141 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingUpdateStatementValidatorTest.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/dml/ShardingUpdateStatementValidatorTest.java
@@ -63,9 +63,9 @@ public final class ShardingUpdateStatementValidatorTest {
joinTableSegment.setRight(new SimpleTableSegment(new TableNameSegment(0, 0, new IdentifierValue("order"))));
updateStatement.setTableSegment(joinTableSegment);
SQLStatementContext<UpdateStatement> sqlStatementContext = new UpdateStatementContext(updateStatement);
- Collection<String> tableNames = Arrays.asList("order", "order_item");
- when(shardingRule.getShardingLogicTableNames(sqlStatementContext.getTablesContext().getTableNames())).thenReturn(tableNames);
- when(shardingRule.isAllBindingTables(tableNames)).thenReturn(true);
+ Collection<String> tableNames = sqlStatementContext.getTablesContext().getTableNames();
+ when(shardingRule.isAllShardingTables(tableNames)).thenReturn(false);
+ when(shardingRule.tableRuleExists(tableNames)).thenReturn(true);
new ShardingUpdateStatementValidator().preValidate(shardingRule, sqlStatementContext, Collections.emptyList(), mock(ShardingSphereSchema.class));
}
diff --git a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/table/TablesContext.java b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/table/TablesContext.java
index 2cb71ed..abd12c5 100644
--- a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/table/TablesContext.java
+++ b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/table/TablesContext.java
@@ -72,8 +72,7 @@ public final class TablesContext {
* @return table names
*/
public Collection<String> getTableNames() {
- return tables.stream().map(each -> each.getTableName().getIdentifier().getValue()).collect(
- Collectors.toSet());
+ return tables.stream().map(each -> each.getTableName().getIdentifier().getValue()).collect(Collectors.toSet());
}
/**
diff --git a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/DeleteStatementContext.java b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/DeleteStatementContext.java
index dc8fa9a..6779ab9 100644
--- a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/DeleteStatementContext.java
+++ b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/DeleteStatementContext.java
@@ -40,19 +40,21 @@ public final class DeleteStatementContext extends CommonSQLStatementContext<Dele
public DeleteStatementContext(final DeleteStatement sqlStatement) {
super(sqlStatement);
- TableExtractor tableExtractor = new TableExtractor();
- tableExtractor.extractTablesFromDelete(sqlStatement);
- tablesContext = new TablesContext(tableExtractor.getRewriteTables());
+ tablesContext = new TablesContext(getAllSimpleTableSegments());
}
- @Override
- public Collection<SimpleTableSegment> getAllTables() {
+ private Collection<SimpleTableSegment> getAllSimpleTableSegments() {
TableExtractor tableExtractor = new TableExtractor();
tableExtractor.extractTablesFromDelete(getSqlStatement());
return tableExtractor.getRewriteTables();
}
@Override
+ public Collection<SimpleTableSegment> getAllTables() {
+ return tablesContext.getOriginalTables();
+ }
+
+ @Override
public Optional<WhereSegment> getWhere() {
return getSqlStatement().getWhere();
}
diff --git a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/InsertStatementContext.java b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/InsertStatementContext.java
index dfbb9af..1ddd6b9 100644
--- a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/InsertStatementContext.java
+++ b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/InsertStatementContext.java
@@ -75,7 +75,7 @@ public final class InsertStatementContext extends CommonSQLStatementContext<Inse
insertValueContexts = getInsertValueContexts(parameters, parametersOffset);
insertSelectContext = getInsertSelectContext(metaDataMap, parameters, parametersOffset, defaultSchemaName).orElse(null);
onDuplicateKeyUpdateValueContext = getOnDuplicateKeyUpdateValueContext(parameters, parametersOffset).orElse(null);
- tablesContext = getTablesContext(sqlStatement);
+ tablesContext = new TablesContext(getAllSimpleTableSegments());
ShardingSphereSchema schema = getSchema(metaDataMap, defaultSchemaName);
List<String> insertColumnNames = getInsertColumnNames();
columnNames = useDefaultColumns() ? schema.getAllColumnNames(sqlStatement.getTable().getTableName().getIdentifier().getValue()) : insertColumnNames;
@@ -91,13 +91,10 @@ public final class InsertStatementContext extends CommonSQLStatementContext<Inse
return metaData.getSchema();
}
- private TablesContext getTablesContext(final InsertStatement sqlStatement) {
- List<SimpleTableSegment> result = new LinkedList<>();
- result.add(sqlStatement.getTable());
- if (sqlStatement.getInsertSelect().isPresent()) {
- result.addAll(insertSelectContext.getSelectStatementContext().getAllTables());
- }
- return new TablesContext(result);
+ private Collection<SimpleTableSegment> getAllSimpleTableSegments() {
+ TableExtractor tableExtractor = new TableExtractor();
+ tableExtractor.extractTablesFromInsert(getSqlStatement());
+ return tableExtractor.getRewriteTables();
}
private List<InsertValueContext> getInsertValueContexts(final List<Object> parameters, final AtomicInteger parametersOffset) {
@@ -182,9 +179,7 @@ public final class InsertStatementContext extends CommonSQLStatementContext<Inse
@Override
public Collection<SimpleTableSegment> getAllTables() {
- TableExtractor tableExtractor = new TableExtractor();
- tableExtractor.extractTablesFromInsert(getSqlStatement());
- return tableExtractor.getRewriteTables();
+ return tablesContext.getOriginalTables();
}
/**
diff --git a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/UpdateStatementContext.java b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/UpdateStatementContext.java
index a7c8d88..a572902 100644
--- a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/UpdateStatementContext.java
+++ b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/UpdateStatementContext.java
@@ -40,19 +40,21 @@ public final class UpdateStatementContext extends CommonSQLStatementContext<Upda
public UpdateStatementContext(final UpdateStatement sqlStatement) {
super(sqlStatement);
- TableExtractor tableExtractor = new TableExtractor();
- tableExtractor.extractTablesFromUpdate(sqlStatement);
- tablesContext = new TablesContext(tableExtractor.getRewriteTables());
+ tablesContext = new TablesContext(getAllSimpleTableSegments());
}
- @Override
- public Collection<SimpleTableSegment> getAllTables() {
+ private Collection<SimpleTableSegment> getAllSimpleTableSegments() {
TableExtractor tableExtractor = new TableExtractor();
tableExtractor.extractTablesFromUpdate(getSqlStatement());
return tableExtractor.getRewriteTables();
}
@Override
+ public Collection<SimpleTableSegment> getAllTables() {
+ return tablesContext.getOriginalTables();
+ }
+
+ @Override
public Optional<WhereSegment> getWhere() {
return getSqlStatement().getWhere();
}