Routing engines fall into two main categories:

• Piecewise Routing (Direct Routing, Standard Routing, Cartesian Product Routing)

• Broadcast routing (full database table routing, full database routing, full instance routing, unicast routing, blocking routing)

Specific Routing Type Meaning Reference to Official Network Routing Engine

https://shardingsphere.apache.org/document/current/cn/features/sharding/principle/route/

Mainly analyze query routing

1. Routing ParsingSQLRouter route entry

@RequiredArgsConstructor
public final class ParsingSQLRouter implements ShardingRouter {
    @Override
    public SQLRouteResult route(final SQLStatement sqlStatement, final List<Object> parameters) {
        //Optimize, process conditional placeholder parameters with real data, paging, group by etc.
        OptimizedStatement optimizedStatement = OptimizeEngineFactory.newInstance(shardingRule, shardingMetaData.getTable(), sqlStatement, parameters).optimize();
        boolean needMergeShardingValues = isNeedMergeShardingValues(sqlStatement);
        if (optimizedStatement instanceof ShardingWhereOptimizedStatement && needMergeShardingValues) {
            checkSubqueryShardingValues(sqlStatement, ((ShardingWhereOptimizedStatement) optimizedStatement).getShardingConditions());
            mergeShardingConditions(((ShardingWhereOptimizedStatement) optimizedStatement).getShardingConditions());
        }
        //Routing Entry
        RoutingResult routingResult = RoutingEngineFactory.newInstance(shardingRule, shardingMetaData.getDataSource(), optimizedStatement).route();
        if (needMergeShardingValues) {
            Preconditions.checkState(1 == routingResult.getRoutingUnits().size(), "Must have one sharding with subquery.");
        }
        if (optimizedStatement instanceof ShardingInsertOptimizedStatement) {
            setGeneratedValues((ShardingInsertOptimizedStatement) optimizedStatement);
        }
        SQLRouteResult result = new SQLRouteResult(optimizedStatement);
        result.setRoutingResult(routingResult);
        return result;
    }
    ... ...
}

 

2. Routing factory and routing Routing EngineFactory route #

@NoArgsConstructor(access = AccessLevel.PRIVATE)
public final class RoutingEngineFactory {
    
    /**
     * Create new instance of routing engine.
     * 
     * @param shardingRule sharding rule
     * @param shardingDataSourceMetaData sharding data source meta data
     * @param optimizedStatement optimized statement
     * @return new instance of routing engine
     */
    public static RoutingEngine newInstance(final ShardingRule shardingRule, final ShardingDataSourceMetaData shardingDataSourceMetaData, final OptimizedStatement optimizedStatement) {
        SQLStatement sqlStatement = optimizedStatement.getSQLStatement();
        Collection<String> tableNames = sqlStatement.getTables().getTableNames();
        //Whole database routing
        if (sqlStatement instanceof TCLStatement) {
            return new DatabaseBroadcastRoutingEngine(shardingRule);
        }
        //Whole database table routing
        if (sqlStatement instanceof DDLStatement) {
            return new TableBroadcastRoutingEngine(shardingRule, optimizedStatement);
        }
        //Blocking routing
        if (sqlStatement instanceof DALStatement) {
            return getDALRoutingEngine(shardingRule, sqlStatement, tableNames);
        }
        //Full instance routing
        if (sqlStatement instanceof DCLStatement) {
            return getDCLRoutingEngine(shardingRule, optimizedStatement, shardingDataSourceMetaData);
        }
        //Default library routing
        if (shardingRule.isAllInDefaultDataSource(tableNames)) {
            return new DefaultDatabaseRoutingEngine(shardingRule, tableNames);
        }
        //Whole database routing
        if (shardingRule.isAllBroadcastTables(tableNames)) {
            return sqlStatement instanceof SelectStatement ? new UnicastRoutingEngine(shardingRule, tableNames) : new DatabaseBroadcastRoutingEngine(shardingRule);
        }
        //unicast
        if (optimizedStatement instanceof ShardingWhereOptimizedStatement && ((ShardingWhereOptimizedStatement) optimizedStatement).getShardingConditions().isAlwaysFalse() || tableNames.isEmpty()) {
            return new UnicastRoutingEngine(shardingRule, tableNames);
        }
        Preconditions.checkState(optimizedStatement instanceof ShardingWhereOptimizedStatement);
        //Piecewise routing
        return getShardingRoutingEngine(shardingRule, (ShardingWhereOptimizedStatement) optimizedStatement, tableNames);
    }
    ... ...
    private static RoutingEngine getShardingRoutingEngine(final ShardingRule shardingRule, final ShardingWhereOptimizedStatement optimizedStatement, final Collection<String> tableNames) {
        /// Get fragmented tables based on the parsed logical tables, such as SELECT i.* FROM t_order o, t_order_item i WHERE o.order_id = i.order_id and o.order_id=?
        //shardingTableNames is t_order, t_order_item
        Collection<String> shardingTableNames = shardingRule.getShardingLogicTableNames(tableNames);
        //To meet the following two conditions, take standard routing, otherwise take composite routing
        //1. Is there only one piecewise table?
        //2. Whether the bound logical table (configuration binding table) contains all fragmented tables
        if (1 == shardingTableNames.size() || shardingRule.isAllBindingTables(shardingTableNames)) {
            //Standard routing, get the first table routing, refer to the official network case description
            return new StandardRoutingEngine(shardingRule, shardingTableNames.iterator().next(), optimizedStatement);
        }
        // TODO config for cartesian set
        //Compound Routing
        return new ComplexRoutingEngine(shardingRule, tableNames, optimizedStatement);
    }
}

 

3. Standard Routing Engine route

Standard routing scenario

• T_order and t_order_item are database sub-tables and binding tables; if the second step, line 55, determines that shardingTableNames.size()=1 or shardingTableNames are binding tables, standard routing will be followed.

@RequiredArgsConstructor
public final class StandardRoutingEngine implements RoutingEngine {
​
    //Sub-database and sub-table rules
    private final ShardingRule shardingRule;
    //Logic table t_order
    private final String logicTableName;
    //Results of sql analysis and optimization
    private final ShardingWhereOptimizedStatement optimizedStatement;
    
    @Override
    public RoutingResult route() {
        //insert, update, delete to determine whether a table is a single table
        if (isDMLForModify(optimizedStatement.getSQLStatement()) && !optimizedStatement.getSQLStatement().getTables().isSingleTable()) {
            throw new SQLParsingException("Cannot support Multiple-Table for '%s'.", optimizedStatement.getSQLStatement());
        }
        //Routing data nodes, encapsulating routing results
        return generateRoutingResult(getDataNodes(shardingRule.getTableRule(logicTableName)));
    }
   
    ... ... 
       
    private RoutingResult generateRoutingResult(final Collection<DataNode> routedDataNodes) {
        RoutingResult result = new RoutingResult();
        //Encapsulation of routing unit and table unit according to data node
        for (DataNode each : routedDataNodes) {
            //Routing unit demo_ds_0
            RoutingUnit routingUnit = new RoutingUnit(each.getDataSourceName());
            //Table Unit Logic Table: True Table t_order:t_order_0
            routingUnit.getTableUnits().add(new TableUnit(logicTableName, each.getTableName()));
            result.getRoutingUnits().add(routingUnit);
        }
        return result;
    }
    
    private Collection<DataNode> getDataNodes(final TableRule tableRule) {
        //Judging that database and table fragmentation strategies are both Hint (direct routing)
        if (shardingRule.isRoutingByHint(tableRule)) {
            return routeByHint(tableRule);
        }
        //The database and table fragmentation strategies are not Hint
        if (isRoutingByShardingConditions(tableRule)) {
            //According to the condition and strategy of fragmentation, we route to the corresponding database and table, and judge the fragmentation keys at the same time.
            return routeByShardingConditions(tableRule);
        }
        //One of the database or table fragmentation strategies is Hint
        return routeByMixedConditions(tableRule);
    }
    ... ...
}

 

4. Composite Routing Cartesian Routing Engine route

Complex routing scenarios

• T_order and t_order_item are database sub-tables and binding tables; a new t_user sub-table is added, at which time the fifty-five rows of the second step, shardingTableNames.size()=3, and t_user is not configured as a binding table, which will take a composite route.

@RequiredArgsConstructor
public final class ComplexRoutingEngine implements RoutingEngine {
    //Sub-database and sub-table rules
    private final ShardingRule shardingRule;
    //Logical tables t_order, t_order_item
    private final Collection<String> logicTables;
    //Results of sql analysis and optimization
    private final ShardingWhereOptimizedStatement optimizedStatement;
    
    @Override
    public RoutingResult route() {
        Collection<RoutingResult> result = new ArrayList<>(logicTables.size());
        Collection<String> bindingTableNames = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        //Traversal logic table
        for (String each : logicTables) {
            Optional<TableRule> tableRule = shardingRule.findTableRule(each);
            //Does the table configure the rules for database partitioning
            if (tableRule.isPresent()) {
                // If the binding relationship table has been processed, then no further processing is needed, such as t_order processing, because t_order_item is a binding relationship with it, then no further processing is needed.
                if (!bindingTableNames.contains(each)) {
                    //Building standard routing and routing
                    result.add(new StandardRoutingEngine(shardingRule, tableRule.get().getLogicTable(), optimizedStatement).route());
                }
                //Find all the corresponding binding tables according to the logical table, such as t_order, t_order_item, because t_order and t_order_item are binding tables.
                Optional<BindingTableRule> bindingTableRule = shardingRule.findBindingTableRule(each);
                if (bindingTableRule.isPresent()) {
                    //Add Binding Table
                    bindingTableNames.addAll(Lists.transform(bindingTableRule.get().getTableRules(), new Function<TableRule, String>() {
                        
                        @Override
                        public String apply(final TableRule input) {
                            return input.getLogicTable();
                        }
                    }));
                }
            }
        }
        if (result.isEmpty()) {
            throw new ShardingException("Cannot find table rule and default data source with logic tables: '%s'", logicTables);
        }
        if (1 == result.size()) {
            return result.iterator().next();
        }
        //Cartesian product routing
        return new CartesianRoutingEngine(result).route();
    }
}

 

5. Cartesian product routing

Cartesian product routing scenario

• Cartesian routing is the most complex case. It can not locate fragmentation rules according to the relationship between bound tables. Therefore, the association queries between non-bound tables need to be decomposed into Cartesian product combinations. For example, the newly added t_user sub-database sub-table in the above example, t_user and t_order do not configure the binding table relationship, and the following SQL needs Cartesian product routing
• SELECT * FROM t_user u JOIN t_order o ON u.user_id = o.user_id AND u.user_id in(1 , 2)

@RequiredArgsConstructor
public final class CartesianRoutingEngine implements RoutingEngine {
    
    private final Collection<RoutingResult> routingResults;
    
    @Override
    public RoutingResult route() {
        RoutingResult result = new RoutingResult();
        //Get the set of logical tables corresponding to the data source
        for (Entry<String, Set<String>> entry : getDataSourceLogicTablesMap().entrySet()) {
            //Get the actual table group by the name of the data source and logical table, namely [["t_user_0","t_user_1"],["t_order_0", "t_order_1]]
            List<Set<String>> actualTableGroups = getActualTableGroups(entry.getKey(), entry.getValue());
            //Encapsulating logical table names into TableUnit table units
            //TableUnit contains the data source name, logical table name, and actual table name (through these three attributes you can determine the table that you ultimately access)
            List<Set<TableUnit>> routingTableGroups = toRoutingTableGroups(entry.getKey(), actualTableGroups);
            //Packaging Routing Unit Routing Unit
            //Cartesian Product Calculating Cartesian Product
            result.getRoutingUnits().addAll(getRoutingUnits(entry.getKey(), Sets.cartesianProduct(routingTableGroups)));
        }
        return result;
    }
    
    private Map<String, Set<String>> getDataSourceLogicTablesMap() {
        //Get the intersection of data sources, such as t_user logic table routing to data source demo_ds_0, and t_order table routing to data source ds_demo_0 and demo_ds_1, the intersection of data sources is demo_ds_0.
        //Example SELECT * FROM t_user_0 u JOIN t_order_0 o ON u.user_id = o.user_id WHERE u.user_id in (1,2); t_user and t_order are not binding table relationships
        //Cartesian routing is the most complex case. It can not locate fragmentation rules according to the relationship between bound tables. Therefore, the association queries between non-bound tables need to be decomposed into Cartesian product combinations for execution.
        Collection<String> intersectionDataSources = getIntersectionDataSources();
        Map<String, Set<String>> result = new HashMap<>(routingResults.size());
        //Result set after traversing standard routing
        for (RoutingResult each : routingResults) {
            //Get the mapping relationship between the data source and the logical table by the name of the data source
            for (Entry<String, Set<String>> entry : each.getDataSourceLogicTablesMap(intersectionDataSources).entrySet()) {
                if (result.containsKey(entry.getKey())) {
                    result.get(entry.getKey()).addAll(entry.getValue());
                } else {
                    result.put(entry.getKey(), entry.getValue());
                }
            }
        }
        // Returns the Map composed of the data source-logical table set, where is {"demo_ds_0":["t_user", "t_order"]}
        return result;
    }
    
    private Collection<String> getIntersectionDataSources() {
        Collection<String> result = new HashSet<>();
        for (RoutingResult each : routingResults) {
            if (result.isEmpty()) {
                result.addAll(each.getDataSourceNames());
            }
​
            //intersection
            result.retainAll(each.getDataSourceNames());
        }
        return result;
    }
    ... ...
}

The Cartesian product results are as follows:

 

6. Direct Routing

Direct routing scenario

• It needs to be fragmented by Hint (using Hint API to specify the route directly to the database table), and it can avoid SQL parsing and subsequent result merging only on the premise that the database is not divided into tables.
• If the routing algorithm is value% 2, when a logical library t_order corresponds to two real libraries t_order_0 and t_order_1, the SQL will be executed on t_order_1 after routing. Below is a sample code that uses API:

    String sql = "SELECT * FROM t_order";
    try (
    //Get a Hint instance
    HintManager hintManager = HintManager.getInstance();
    Connection conn = dataSource.getConnection();
    PreparedStatement pstmt = conn.prepareStatement(sql)) {
        //Set the number of data source fragments
        hintManager.setDatabaseShardingValue(3);
        try (ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                //...
            }
        }
    }

@RequiredArgsConstructor
public final class DatabaseHintRoutingEngine implements RoutingEngine {
    //Data Source Cluster
    private final Collection<String> dataSourceNames;
    //Hint data fragmentation strategy
    private final HintShardingStrategy databaseShardingStrategy;
    
    @Override
    public RoutingResult route() {
         //Get the current thread data source fragmentation
        Collection<Comparable<?>> shardingValues = HintManager.getDatabaseShardingValues();
        Preconditions.checkState(!shardingValues.isEmpty());
        Collection<String> routingDataSources;
        //Routing according to fragmentation strategy
        routingDataSources = databaseShardingStrategy.doSharding(dataSourceNames, Collections.<RouteValue>singletonList(new ListRouteValue<>("", "", shardingValues)));
        Preconditions.checkState(!routingDataSources.isEmpty(), "no database route info");
        RoutingResult result = new RoutingResult();
        //Encapsulated Routing Unit
        for (String each : routingDataSources) {
            result.getRoutingUnits().add(new RoutingUnit(each));
        }
        return result;
    }
}