ShardingSphere-JDBC sharding routing engine

preface

Shardingsphere-jdbc sharding parsing engine above introduces the parsing engine in the sharding process, focusing on the core component of the parsing engine ANTLR; This paper continues to introduce the routing engine in the sharding process. The routing engine can be said to be the core module of the whole sharding process. The user-defined sharding algorithm is executed in the routing engine.

Open the log

To view routing logs clearly and in detail, enable the SQL_SHOW function:

• Import the log4J-related JAR and the log4j.xml configuration file

• Set the SQL_SHOW attribute to enable:

  Properties prop = new Properties();
  prop.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);
  Copy the code

Route decorator

The routing engine has a RouteDecorator wrapped around it. The reason for this is that in addition to our algorithm-based routing engine, Shardingsphere-JDBC also provides read-write separation, which is actually routing to some extent. And the two routing modes can be superimposed; So a layer of abstraction is provided, and the implementation classes include:

• MasterSlaveRouteDecorator: reading and writing separate routing decorator;
• ShardingRouteDecorator: a ShardingRouteDecorator containing various routing engines;

Decorators can be stacked, so the priority function OrderAware is provided, and each decorator has a corresponding rule, roughly as follows:

A decorator – RouteDecorator	Configuration – a Configuration	Rules – BaseRule	Priority Order
MasterSlaveRouteDecorator	MasterSlaveRuleConfiguration	MasterSlaveRule	10
ShardingRouteDecorator	ShardingRuleConfiguration	ShardingRule	0

According to the priority first perform ShardingRouteDecorator, can know the routing result to perform MasterSlaveRouteDecorator; Part of the startup class code is shown in DataNodeRouter as follows:

private final Map<BaseRule, RouteDecorator> decorators = new LinkedHashMap<>();
private RouteContext executeRoute(final String sql, final List<Object> parameters, final boolean useCache) {
      RouteContext result = createRouteContext(sql, parameters, useCache);
      for (Entry<BaseRule, RouteDecorator> entry : decorators.entrySet()) {
          result = entry.getValue().decorate(result, metaData, entry.getKey(), properties);
      }
      return result;
}
Copy the code

Decorators decide whether to start decorators based on the user’s configuration, as shown in the table above. Here are the two decorators in order of priority;

Fragment route decorator

ShardingRule; ShardingRule; ShardingRule; ShardingRule; ShardingRule; With these two core parameters, fragment routing can be roughly divided into the following steps:

• Get sharding conditionsShardingConditions
• Get the specific sharding engineShardingRouteEngine
• Perform route processing to obtain the route result

Before introducing each step in detail, first introduce the following core parameters RouteContext and ShardingRule;

Core parameter

Focus on the two core parameters RouteContext and ShardingRule;

RouteContext

Routing context parameters, including the following parameters:

public final class RouteContext {
    private final SQLStatementContext sqlStatementContext;
    private final List<Object> parameters;
    private final RouteResult routeResult;
}
Copy the code

• SqlStatementContext: obtained by the parsing engineSQLStatement;
• The parameters:PreparedStatementTo perform an INSERT operationsetXxxInstead of?;
• RouteResult: stores the routeResult after the route.

ShardingRule

Fragmentation rules, the main parameters as follows, this actually and ShardingRuleConfiguration the same, just to do a packaging;

public class ShardingRule implements BaseRule {
    private final ShardingRuleConfiguration ruleConfiguration;
    private final ShardingDataSourceNames shardingDataSourceNames;
    private final Collection<TableRule> tableRules;
    private final Collection<BindingTableRule> bindingTableRules;
    private final Collection<String> broadcastTables;
    private final ShardingStrategy defaultDatabaseShardingStrategy;
    private final ShardingStrategy defaultTableShardingStrategy;
    private final ShardingKeyGenerator defaultShardingKeyGenerator;
    private final Collection<MasterSlaveRule> masterSlaveRules;
    private final EncryptRule encryptRule;
Copy the code

• RuleConfiguration: Configuration of a routing ruleShardingRuleThe original file of;
• ShardingDataSourceNames: data source name of the sharding.
• TableRules: table routing rules that correspond to user-configured rulesTableRuleConfiguration;
• BindingTableRules: binding table configuration, sharding rules of the same primary table and child tables;
• BroadcastTables: broadcastTables that exist in all fragmented data sources
• DefaultDatabaseShardingStrategy: the default library shard strategy;
• DefaultTableShardingStrategy: the default table shard strategy;
• DefaultShardingKeyGenerator: the primary key generation strategy;
• MasterSlaveRules: master and slave rule configuration, used to achieve read and write separation, can configure a master table multiple slave tables;
• EncryptRule: Encryption rule configuration that encrypts sensitive data.

Get sharding conditions

Before obtaining the specific routing engine and performing routing operations, we need to obtain sharding conditions. Common sharding conditions are mainly after Insert statements and Where statements. Part of the source code to obtain sharding conditions is as follows:

    private ShardingConditions getShardingConditions(final List<Object> parameters, 
                                                     final SQLStatementContext sqlStatementContext, final SchemaMetaData schemaMetaData, final ShardingRule shardingRule) {
        if (sqlStatementContext.getSqlStatement() instanceof DMLStatement) {
            if (sqlStatementContext instanceof InsertStatementContext) {
                return new ShardingConditions(newInsertClauseShardingConditionEngine(shardingRule).createShardingConditions((InsertStatementContext) sqlStatementContext,  parameters)); }return new ShardingConditions(new WhereClauseShardingConditionEngine(shardingRule, schemaMetaData).createShardingConditions(sqlStatementContext, parameters));
        }
        return new ShardingConditions(Collections.emptyList());
    }
Copy the code

The SQL statement type is DMLStatement, which is the most common SQL statement type. Next check whether it is an Insert statement and use a different fragment conditional generation engine:

• InsertClauseShardingConditionEngine: handle Insert statement shard conditions;
• WhereClauseShardingConditionEngine: processing the Insert statement Where clause shard conditions;

InsertClauseShardingConditionEngine

Insert statements contain sharding conditions in two main places:

• TableRules of ShardingRule configure sharding keys for sharding columns, as shown in the following table:

  insert into t_order (user_id,order_id) values(? ,?)Copy the code

  This step checks whether user_id and order_id are configured with the component slice key in a ShardingRule.

• If the primary key generation policy is configured, it is also necessary to check whether tableRules of ShardingRule is configured with related fields as sharding keys.

  tableRuleConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE"."id"));
  Copy the code

The generated result is ShardingConditions, which contains multiple ShardingConditions:

public final class ShardingConditions {
    private final List<ShardingCondition> conditions;
}

public class ShardingCondition {
    private final List<RouteValue> routeValues = new LinkedList<>();
}
Copy the code

The RouteValue implementation here includes ListRouteValue, RangeRouteValue, AlwaysFalseRouteValue:

• ListRouteValue: It can be understood that the shard key corresponds to a specific value, which can be a single or multiple value.
• RangeRouteValue: The shard key corresponds to an interval value;
• AlwaysFalseRouteValue: indicates the route value that always fails.

WhereClauseShardingConditionEngine

A common Select statement can contain multiple conditions after the Where statement. Each condition also needs to check whether tableRules of ShardingRule are configured with related fields as sharding keys. Slightly different, Where conditions need to be merged, such as:

String sql = "select user_id,order_id from t_order where order_id = 101 and order_id = 101";
String sql = "select user_id,order_id from t_order where order_id = 101 and order_id in(101)";
Copy the code

If order_id = 101, merge order_id = 101, merge order_id = 101

String sql = "select user_id,order_id from t_order where order_id = 101 and order_id = 102";
Copy the code

Returns an AlwaysFalseRouteValue indicating that this condition cannot be true;

Getting the Routing Engine

Shardingsphere-jdbc provides 10 routing engines based on the SQLStatement.

The flow chart

Flow chart is roughly as shown above, specific see ShardingRouteEngineFactory can; Each routing engine is described in detail below;

The routing engine

ShardingDatabaseBroadcastRoutingEngine

Full-library routing engine: Used to handle operations on the database, including database management commands of type SET for library Settings and transaction control statements such as TCL. In this case, the command is executed in the real library through all the real libraries that match the name of the logical library.

1. Belong to DALStatement

At the database access layer, common commands include: set, reset, show databases;

show databases;
Copy the code

The above SQL will be the whole library routing, routing SQL is as follows:

Actual SQL: ds0 ::: show databases;
Actual SQL: ds1 ::: show databases;
Copy the code

2. Logical tables belong to broadcast tables

insert into t_config (k,v) values(? ,?)Copy the code

T_config configudes a broadcast table and inserts data into all libraries. Of course, you need to configure the broadcast table:

Collection<String> broadcastTables = new LinkedList<>();
broadcastTables.add("t_config");
shardingRuleConfig.setBroadcastTables(broadcastTables);
Copy the code

The route log is as follows:

Actual SQL: ds0 ::: insert into t_config (k,v) values (?, ?) ::: [aa1, 1111]
Actual SQL: ds1 ::: insert into t_config (k,v) values (?, ?) ::: [aa1, 1111]
Copy the code

3. Belong to TCLStatement

Transaction control language, including setting savepoints, rollback, etc.

SET autocommit=0
Copy the code

The route log is as follows:

Actual SQL: ds0 ::: SET autocommit=0;
Actual SQL: ds1 ::: SET autocommit=0;
Copy the code

ShardingTableBroadcastRoutingEngine

Full-library table routing is used to process the operation of all real tables related to its logical tables in the database, including DQL and DML without sharding keys, as well as DDL, etc.

1. Belong to DDLStatement

Database definition language, including creating, modifying, deleting tables, etc.

ALTER  TABLE t_order MODIFY  COLUMN user_id  BIGINT(50) NOT NULL;
Copy the code

The log is as follows:

Actual SQL: ds0 ::: ALTER  TABLE t_order0 MODIFY  COLUMN user_id  BIGINT(50) NOT NULL;
Actual SQL: ds0 ::: ALTER  TABLE t_order1 MODIFY  COLUMN user_id  BIGINT(50) NOT NULL;
Actual SQL: ds1 ::: ALTER  TABLE t_order0 MODIFY  COLUMN user_id  BIGINT(50) NOT NULL;
Actual SQL: ds1 ::: ALTER  TABLE t_order1 MODIFY  COLUMN user_id  BIGINT(50) NOT NULL;
Copy the code

2. Belong to DCLStatement

Database control language, including authorization, role control, etc. Grant, deny, etc

grant select on ds.t_order to root@The '%'
Copy the code

Grant select privileges to user root. Select * from user root.

Actual SQL: ds0 ::: grant select on t_order0 to root@The '%'
Actual SQL: ds0 ::: grant select on t_order1 to root@The '%'
Actual SQL: ds1 ::: grant select on t_order0 to root@The '%'
Actual SQL: ds1 ::: grant select on t_order1 to root@The '%'
Copy the code

ShardingIgnoreRoutingEngine

Blocking routes shield SQL operations on the database.

1.DALStatement

The use command in DALStatement is used to block routes.

use ds0
Copy the code

ShardingDefaultDatabaseRoutingEngine

Default database route, you need to configure the default data source name, including the following situations:

1. Belong to DALStatement

show create table t_order1
Copy the code

Note: TableRuleConfiguration is a real table. You need to configure the default data source name.

shardingRuleConfig.setDefaultDataSourceName("ds0");
Copy the code

The log is as follows:

Actual SQL: ds0 ::: show create table t_order1
Copy the code

2. Logical tables belong to default data sources

select user_id,order_id from t_order0 where user_id = 102
Copy the code

Note: TableRuleConfiguration is a real table and cannot be configured as a broadcast table.

Actual SQL: ds0 ::: select user_id,order_id from t_order0 where user_id = 102
Copy the code

3. Belong to DMLStatement

select 2+2
Copy the code

This DMLStatement does not have a table name and needs to specify a default data source name.

Actual SQL: ds0 ::: select 2+2
Copy the code

ShardingUnicastRoutingEngine

Unicast routing is used to obtain the information of a real table. It only needs to obtain data from any real table in any library.

1. Belong to DALStatement

desc t_order
Copy the code

The log is as follows:

Actual SQL: ds1 ::: desc t_order0
Copy the code

2. Logical tables belong to broadcast tables

Broadcast table A table that exists in a sharded data source, usually a dictionary table:

select * from t_config
Copy the code

To configure the broadcast table:

Collection<String> broadcastTables = new LinkedList<>();
broadcastTables.add("t_config");
shardingRuleConfig.setBroadcastTables(broadcastTables);
Copy the code

The log is as follows:

Actual SQL: ds0 ::: select * from t_config
Copy the code

3. Belong to DMLStatement

Belong to DMLStatement for AlwaysFalseShardingCondition shard conditions at the same time, or does not specify the name of the table, or no configuration table rules;

select user_id,order_id from t_order where order_id = 101 and order_id = 102
Copy the code

Behind the where the specified condition can lead to fragmentation of AlwaysFalseShardingCondition

Actual SQL: ds1 ::: select user_id,order_id from t_order0 where order_id = 101 and order_id = 102
Copy the code

ShardingDataSourceGroupBroadcastRoutingEngine

Data source group broadcast, randomly select a data source from the data source group;

1. Belong to DALStatement

SHOW STATUS
Copy the code

DALStatement subclasses include use, set, reset, and show databases. Almost all use this engine;

Actual SQL: ds1 ::: SHOW STATUS
Copy the code

ShardingMasterInstanceBroadcastRoutingEngine

Full-instance routing is used for DCL operations, and authorization statements are used for database instances. No matter how many schemas are contained in an instance, each database instance is executed only once;

1. Belong to DCLStatement

CREATE USER customer@ 127. 0. 01. identified BY '123'
Copy the code

Note: the primary instance of Mysql will be checked between instances, so it cannot have the same hostname and the same port. The local test is performed on the same Mysql library, and the hostname configuration is inconsistent, such as localhost and 127.0.0.1.

ShardingStandardRoutingEngine

Standard routing is the most common sharding method. After filtering by the preceding routing engines, the remaining SQLStatement will be used by the remaining two engines.

1. Query a single table

select user_id,order_id from t_order where order_id = 101
Copy the code

The above uses the general configuration, and the two data sources are DS0 and DS1 respectively. User_id as the database key, order_id as the table key;

Actual SQL: ds0 ::: select user_id,order_id from t_order1 where order_id = 101
Actual SQL: ds1 ::: select user_id,order_id from t_order1 where order_id = 101
Copy the code

101 The physical table T_order1 was located through the fragmentation algorithm, but the database could not be located, so the two libraries were respectively executed.

2. Associated query

select a.user_id,a.order_id from t_order a left join t_order_item b ON a.order_id=b.order_id where a.order_id = 101
Copy the code

In associative query, the standard route is used only when the binding relationship is configured.

Collection<String> bindingTables = new LinkedList<>();
bindingTables.add("t_order,t_order_item");
shardingRuleConfig.setBindingTableGroups(bindingTables);
Copy the code

Two tables are configured as binding tables. Associated query has the same complexity and performance as single-table query, and Cartesian routing is not implemented.

Actual SQL: ds0 ::: select a.user_id,a.order_id from t_order1 a left join t_order1 b ON a.order_id=b.order_id where a.order_id = 101
Actual SQL: ds1 ::: select a.user_id,a.order_id from t_order1 a left join t_order1 b ON a.order_id=b.order_id where a.order_id = 101
Copy the code

ShardingComplexRoutingEngine

Cartesian routing is the most complex case, because it cannot locate sharding rules according to the relationship between bound tables, so the associated query between unbound tables needs to be disassembled into Cartesian product combination.

1. Associated query

If no binding relationship is configured in the above SQL, cartesian routing will be performed. The routing log is as follows:

Actual SQL: ds0 ::: select a.user_id,a.order_id from t_order1 a left join t_order_item0 b ON a.order_id=b.order_id where a.order_id = 101
Actual SQL: ds0 ::: select a.user_id,a.order_id from t_order1 a left join t_order_item1 b ON a.order_id=b.order_id where a.order_id = 101
Actual SQL: ds1 ::: select a.user_id,a.order_id from t_order1 a left join t_order_item0 b ON a.order_id=b.order_id where a.order_id = 101
Actual SQL: ds1 ::: select a.user_id,a.order_id from t_order1 a left join t_order_item1 b ON a.order_id=b.order_id where a.order_id = 101
Copy the code

Perform routing processing

After the preceding process, the routing engine corresponding to the SQLStatement has been obtained. You only need to run the routing engine to obtain the routing result.

public interface ShardingRouteEngine {
    RouteResult route(ShardingRule shardingRule);
}
Copy the code

The ShardingRule rule is passed in and the result RouteResult is returned. The following uses standard routes as an example to analyze how route processing is performed. GetDataNodes ShardingStandardRoutingEngine core method is shown below:

    private Collection<DataNode> getDataNodes(final ShardingRule shardingRule, final TableRule tableRule) {
        if (isRoutingByHint(shardingRule, tableRule)) {
            return routeByHint(shardingRule, tableRule);
        }
        if (isRoutingByShardingConditions(shardingRule, tableRule)) {
            return routeByShardingConditions(shardingRule, tableRule);
        }
        return routeByMixedConditions(shardingRule, tableRule);
    }
Copy the code

There are three routing modes: Hint routing, fragment conditional routing, and mixed conditional routing. The following are introduced separately;

Hint routing

Determine whether to use forced routing:

    private boolean isRoutingByHint(final ShardingRule shardingRule, final TableRule tableRule) {
        return shardingRule.getDatabaseShardingStrategy(tableRule) instanceof HintShardingStrategy && shardingRule.getTableShardingStrategy(tableRule) instanceof HintShardingStrategy;
    }
Copy the code

Need to library and table are HintShardingStrategy routing strategy, the only need to configure TableRuleConfiguration configuration database and table strategies are respectively for HintShardingStrategyConfiguration can;

 private Collection<DataNode> route0(final ShardingRule shardingRule, final TableRule tableRule, final List<RouteValue> databaseShardingValues, final List<RouteValue> tableShardingValues) {
        Collection<String> routedDataSources = routeDataSources(shardingRule, tableRule, databaseShardingValues);
        Collection<DataNode> result = new LinkedList<>();
        for (String each : routedDataSources) {
            result.addAll(routeTables(shardingRule, tableRule, each, tableShardingValues));
        }
        return result;
    }
Copy the code

The next step is to execute the routing library and routing table separately. Both the routing library and the routing table require two core parameters: the currently available target library or table, and the current shard value.

• Currently available target libraries or tables: this is the initialized library and table, such as target libraries ds0, ds1, target tables t_order0, T_order1;
• Current shard value: The current hint is passedHintManagerThe configured fragment value;

In fact, the other two routing methods are similar, but the way to obtain the fragment value is different. With these two values, ShardingAlgorithm defined by ourselves will be called. In this way, the library table after routing will be returned and the results will be saved to DataNode:

public final class DataNode {
    private final String dataSourceName;
    private final String tableName;
}
Copy the code

A real library corresponds to a real table; Finally, encapsulate the DataNode to the RouteResult.

Fragment conditional routing

Also, first determine whether to use fragment conditional routing:

    private boolean isRoutingByShardingConditions(final ShardingRule shardingRule, final TableRule tableRule) {
        return! (shardingRule.getDatabaseShardingStrategy(tableRule)instanceof HintShardingStrategy || shardingRule.getTableShardingStrategy(tableRule) instanceof HintShardingStrategy);
    }
Copy the code

Sharding conditional routing is implemented only when the library and table routing strategies are not HintShardingStrategy.

    private Collection<DataNode> routeByShardingConditions(final ShardingRule shardingRule, final TableRule tableRule) {
        return shardingConditions.getConditions().isEmpty()
                ? route0(shardingRule, tableRule, Collections.emptyList(), Collections.emptyList()) : routeByShardingConditionsWithCondition(shardingRule, tableRule);
    }
Copy the code

Then it will judge whether there are ShardingConditions. The above chapter will specifically introduce ShardingConditions. If there is no ShardingConditions, then the full library table will be routed. If there are ShardingConditions, then the library table sharding value will be retrieved from ShardingConditions.

Hybrid conditional routing

The mixed mode is that neither the library nor the table routing policies are all HintShardingStrategy. Either the table uses forced routing or the library uses forced routing.

    private Collection<DataNode> routeByMixedConditions(final ShardingRule shardingRule, final TableRule tableRule) {
        return shardingConditions.getConditions().isEmpty() ? routeByMixedConditionsWithHint(shardingRule, tableRule) : routeByMixedConditionsWithCondition(shardingRule, tableRule);
    }
Copy the code

It will judge whether there is ShardingConditions. If there is no indication of library or table routing, one uses HintShardingStrategy and the other does not. Otherwise, Hint and Condition are mixed; Hint (); Hint (); Hint ();

   private List<RouteValue> getDatabaseShardingValues(final ShardingRule shardingRule, final TableRule tableRule, final ShardingCondition shardingCondition) {
        ShardingStrategy dataBaseShardingStrategy = shardingRule.getDatabaseShardingStrategy(tableRule);
        return isGettingShardingValuesFromHint(dataBaseShardingStrategy)
                ? getDatabaseShardingValuesFromHint() : getShardingValuesFromShardingConditions(shardingRule, dataBaseShardingStrategy.getShardingColumns(), shardingCondition);
    }
Copy the code

If the Hint fragment value is available, use the Hint value, otherwise get it from Condition;

Read/write split route decorator

Through fragmentation routing decorators on the processing, according to the priority, if the configuration the separation will perform, speaking, reading and writing, speaking, reading and writing are separated decorator MasterSlaveRouteDecorator; The general process is as follows:

Read/write Separation Configuration

List<String> slaveDataSourceNames0 = new ArrayList<String>();
slaveDataSourceNames0.add("ds01");
MasterSlaveRuleConfiguration masterSlaveRuleConfiguration0 = new MasterSlaveRuleConfiguration("ds0"."ds0",
				slaveDataSourceNames0);
shardingRuleConfig.getMasterSlaveRuleConfigs().add(masterSlaveRuleConfiguration0);
		
List<String> slaveDataSourceNames1 = new ArrayList<String>();
slaveDataSourceNames1.add("ds11");
MasterSlaveRuleConfiguration masterSlaveRuleConfiguration1 = new MasterSlaveRuleConfiguration("ds1"."ds1",
				slaveDataSourceNames1);
shardingRuleConfig.getMasterSlaveRuleConfigs().add(masterSlaveRuleConfiguration1);
Copy the code

A read/write separation policy must be configured for ds0 standby library ds01 and DS1 standby library DS11.

Library name matching

The library name of RouteUnit generated by fragmented routes matches the name configured in MasterSlaveRule.

Read and write routing

    public String route(final SQLStatement sqlStatement) {
        if (isMasterRoute(sqlStatement)) {
            MasterVisitedManager.setMasterVisited();
            return masterSlaveRule.getMasterDataSourceName();
        }
        return masterSlaveRule.getLoadBalanceAlgorithm().getDataSource(
                masterSlaveRule.getName(), masterSlaveRule.getMasterDataSourceName(), new ArrayList<>(masterSlaveRule.getSlaveDataSourceNames()));
    }
Copy the code

SQLStatement (); SQLStatement ();

private boolean isMasterRoute(final SQLStatement sqlStatement) {
        returncontainsLockSegment(sqlStatement) || ! (sqlStatementinstanceof SelectStatement) || MasterVisitedManager.isMasterVisited() || HintManager.isMasterRouteOnly();
    }
    
    private boolean containsLockSegment(final SQLStatement sqlStatement) {
        return sqlStatement instanceof SelectStatement && ((SelectStatement) sqlStatement).getLock().isPresent();
    }
Copy the code

• SelectStatementContains locks
• nonSelectStatement
• Configure theMasterVisitedManager, internally managed using ThreadLocal
• Configure theHintManager, internally managed using ThreadLocal

In the preceding cases, the master table is used. In other cases, the standby database is used. If multiple standby databases exist, load balancing is performed.

Alternative routing library

After read/write split route processing, the library name that needs to be replaced is obtained.

conclusion

This article mainly introduces shardingSphere-JDBC sharding routing engine. The key and difficulty is that different types of SQLStatement use different routing engines. There are many routing engines. The routing engine will fetch the routing result and now we’re going to rewrite the SQL, rewrite it into a real library table, so that the database can execute it.

reference

Shardingsphere.apache.org/document/cu…

Thank you for attention

You can pay attention to the wechat public account “Roll back the code”, read the first time, the article continues to update; Focus on Java source code, architecture, algorithms, and interviews.