sharding-jdbc路由的原理及應(yīng)用

這篇文章主要介紹“sharding-jdbc路由的原理及應(yīng)用”，在日常操作中，相信很多人在sharding-jdbc路由的原理及應(yīng)用問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”sharding-jdbc路由的原理及應(yīng)用”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

路由引擎主要分為兩大類：

• 分片路由（直接路由、標準路由、笛卡爾積路由）

• 廣播路由（全庫表路由、全庫路由、全實例路由、單播路由、阻斷路由）

具體路由類型含義參考官網(wǎng)路由引擎

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

主要分析查詢路由

1.路由ParsingSQLRouter#route入口

@RequiredArgsConstructor
public final class ParsingSQLRouter implements ShardingRouter {
    @Override
    public SQLRouteResult route(final SQLStatement sqlStatement, final List<Object> parameters) {
        //優(yōu)化，處理條件占位符參數(shù)與真實數(shù)據(jù)、分頁、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());
        }
        //路由入口
        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.路由工廠并路由RoutingEngineFactory#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();
        //全庫路由
        if (sqlStatement instanceof TCLStatement) {
            return new DatabaseBroadcastRoutingEngine(shardingRule);
        }
        //全庫表路由
        if (sqlStatement instanceof DDLStatement) {
            return new TableBroadcastRoutingEngine(shardingRule, optimizedStatement);
        }
        //阻斷路由
        if (sqlStatement instanceof DALStatement) {
            return getDALRoutingEngine(shardingRule, sqlStatement, tableNames);
        }
        //全實例路由
        if (sqlStatement instanceof DCLStatement) {
            return getDCLRoutingEngine(shardingRule, optimizedStatement, shardingDataSourceMetaData);
        }
        //默認庫路由
        if (shardingRule.isAllInDefaultDataSource(tableNames)) {
            return new DefaultDatabaseRoutingEngine(shardingRule, tableNames);
        }
        //全庫路由
        if (shardingRule.isAllBroadcastTables(tableNames)) {
            return sqlStatement instanceof SelectStatement ? new UnicastRoutingEngine(shardingRule, tableNames) : new DatabaseBroadcastRoutingEngine(shardingRule);
        }
        //單播路由
        if (optimizedStatement instanceof ShardingWhereOptimizedStatement && ((ShardingWhereOptimizedStatement) optimizedStatement).getShardingConditions().isAlwaysFalse() || tableNames.isEmpty()) {
            return new UnicastRoutingEngine(shardingRule, tableNames);
        }
        Preconditions.checkState(optimizedStatement instanceof ShardingWhereOptimizedStatement);
        //分片路由
        return getShardingRoutingEngine(shardingRule, (ShardingWhereOptimizedStatement) optimizedStatement, tableNames);
    }
    ... ...
    private static RoutingEngine getShardingRoutingEngine(final ShardingRule shardingRule, final ShardingWhereOptimizedStatement optimizedStatement, final Collection<String> tableNames) {
        ///根據(jù)解析出來邏輯表獲取分片表，如:SELECT i.* FROM t_order o, t_order_item i WHERE o.order_id = i.order_id and o.order_id = ?
        //則shardingTableNames為t_order，t_order_item
        Collection<String> shardingTableNames = shardingRule.getShardingLogicTableNames(tableNames);
        //滿足以下兩個條件走標準路由，否則走復(fù)合路由
        //1、是否只有一張分片表
        //2、綁定的邏輯表（配置綁定表）是否包含所有分片表
        if (1 == shardingTableNames.size() || shardingRule.isAllBindingTables(shardingTableNames)) {
            //標準路由，獲取第一張表路由即可，參考官網(wǎng)案例說明
            return new StandardRoutingEngine(shardingRule, shardingTableNames.iterator().next(), optimizedStatement);
        }
        // TODO config for cartesian set
        //復(fù)合路由
        return new ComplexRoutingEngine(shardingRule, tableNames, optimizedStatement);
    }
}

3.標準路由StandardRoutingEngine#route

標準路由場景

• t_order和t_order_item是分庫分表并且是綁定表；如第二步55行判斷shardingTableNames.size()=1或者shardingTableNames都是綁定表時，這時會走標準路由

@RequiredArgsConstructor
public final class StandardRoutingEngine implements RoutingEngine {

    //分庫分表規(guī)則
    private final ShardingRule shardingRule;
    //邏輯表 t_order
    private final String logicTableName;
    //sql解析并優(yōu)化后的結(jié)果
    private final ShardingWhereOptimizedStatement optimizedStatement;
    
    @Override
    public RoutingResult route() {
        //insert、update、delete判斷表是否是單表
        if (isDMLForModify(optimizedStatement.getSQLStatement()) && !optimizedStatement.getSQLStatement().getTables().isSingleTable()) {
            throw new SQLParsingException("Cannot support Multiple-Table for '%s'.", optimizedStatement.getSQLStatement());
        }
        //路由數(shù)據(jù)節(jié)點、封裝路由結(jié)果
        return generateRoutingResult(getDataNodes(shardingRule.getTableRule(logicTableName)));
    }
   
    ... ... 
       
    private RoutingResult generateRoutingResult(final Collection<DataNode> routedDataNodes) {
        RoutingResult result = new RoutingResult();
        //根據(jù)數(shù)據(jù)節(jié)點封裝路由單元、表單元
        for (DataNode each : routedDataNodes) {
            //路由單元 demo_ds_0
            RoutingUnit routingUnit = new RoutingUnit(each.getDataSourceName());
            //表單元 邏輯表:真實表 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) {
        //判斷database、table分片策略同時是Hint(直接路由)
        if (shardingRule.isRoutingByHint(tableRule)) {
            return routeByHint(tableRule);
        }
        //database、table分片策略都不是Hint
        if (isRoutingByShardingConditions(tableRule)) {
            //根據(jù)分片條件、策略路由到對應(yīng)的database、table，同對分片鍵判斷
            return routeByShardingConditions(tableRule);
        }
        //database或table分片策略有一個是Hint
        return routeByMixedConditions(tableRule);
    }
    ... ...
}

4.復(fù)合路由CartesianRoutingEngine#route

復(fù)雜路由場景

• t_order和t_order_item是分庫分表并且是綁定表；新增一個t_user分庫分表，這時第二步的55行，shardingTableNames.size()=3，且t_user未配置成綁定表，這時會走復(fù)合路由

@RequiredArgsConstructor
public final class ComplexRoutingEngine implements RoutingEngine {
    //分庫分表規(guī)則
    private final ShardingRule shardingRule;
    //邏輯表t_order、t_order_item
    private final Collection<String> logicTables;
    //sql解析并優(yōu)化后的結(jié)果
    private final ShardingWhereOptimizedStatement optimizedStatement;
    
    @Override
    public RoutingResult route() {
        Collection<RoutingResult> result = new ArrayList<>(logicTables.size());
        Collection<String> bindingTableNames = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        //遍歷邏輯表
        for (String each : logicTables) {
            Optional<TableRule> tableRule = shardingRule.findTableRule(each);
            //表是否配置了分庫分表規(guī)則
            if (tableRule.isPresent()) {
                // 如果綁定關(guān)系表已經(jīng)處理過，那么不需要再處理，如t_order處理過，由于t_order_item與其是綁定關(guān)系，那么不需要再處理；
                if (!bindingTableNames.contains(each)) {
                    //構(gòu)建標準路由并路由
                    result.add(new StandardRoutingEngine(shardingRule, tableRule.get().getLogicTable(), optimizedStatement).route());
                }
                //根據(jù)邏輯表查找對應(yīng)的所有綁定表，如根據(jù)t_order就能查詢到t_order、t_order_item，因為t_order和t_order_item是綁定表
                Optional<BindingTableRule> bindingTableRule = shardingRule.findBindingTableRule(each);
                if (bindingTableRule.isPresent()) {
                    //添加綁定表
                    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();
        }
        //笛卡爾積路由
        return new CartesianRoutingEngine(result).route();
    }
}

5.笛卡爾積路由

笛卡爾積路由場景

• 笛卡爾路由是最復(fù)雜的情況，它無法根據(jù)綁定表的關(guān)系定位分片規(guī)則，因此非綁定表之間的關(guān)聯(lián)查詢需要拆解為笛卡爾積組合執(zhí)行。如上面事例新增的t_user分庫分表，t_user和t_order未配置綁定表關(guān)系，如下SQL就需要走笛卡爾積路由

• 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();
        //獲取數(shù)據(jù)源對應(yīng)的邏輯表集合
        for (Entry<String, Set<String>> entry : getDataSourceLogicTablesMap().entrySet()) {
            //通過數(shù)據(jù)源名稱和邏輯表的名稱獲取實際的表組，即[["t_user_0","t_user_1"],["t_order_0", "t_order_1]]
            List<Set<String>> actualTableGroups = getActualTableGroups(entry.getKey(), entry.getValue());
            //把邏輯表名封裝成TableUnit表單元
            //TableUnit包含數(shù)據(jù)源名稱、邏輯表名、實際表名（通過這三個屬性可以確定最終訪問的表）
            List<Set<TableUnit>> routingTableGroups = toRoutingTableGroups(entry.getKey(), actualTableGroups);
            //封裝RoutingUnit路由單元
            //cartesianProduct計算笛卡爾積
            result.getRoutingUnits().addAll(getRoutingUnits(entry.getKey(), Sets.cartesianProduct(routingTableGroups)));
        }
        return result;
    }
    
    private Map<String, Set<String>> getDataSourceLogicTablesMap() {
        //獲取數(shù)據(jù)源的交集，如t_user邏輯表路由到數(shù)據(jù)源demo_ds_0，而t_order表路由到數(shù)據(jù)源ds_demo_0和demo_ds_1，數(shù)據(jù)源交集就是demo_ds_0
        //事例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和t_order不是綁定表關(guān)系
        //笛卡爾路由是最復(fù)雜的情況，它無法根據(jù)綁定表的關(guān)系定位分片規(guī)則，因此非綁定表之間的關(guān)聯(lián)查詢需要拆解為笛卡爾積組合執(zhí)行
        Collection<String> intersectionDataSources = getIntersectionDataSources();
        Map<String, Set<String>> result = new HashMap<>(routingResults.size());
        //遍歷標準路由后的結(jié)果集
        for (RoutingResult each : routingResults) {
            //通過數(shù)據(jù)源的名稱獲取數(shù)據(jù)源和邏輯表之間的映射關(guān)系
            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());
                }
            }
        }
        // 返回數(shù)據(jù)源-邏輯表集合組成的Map，這里就是{"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());
            }

            //交集
            result.retainAll(each.getDataSourceNames());
        }
        return result;
    }
    ... ...
}

笛卡爾積結(jié)果如下：

6.直接路由

直接路由場景

• 滿足直接路由的條件相對苛刻，它需要通過Hint（使用HintAPI直接指定路由至庫表）方式分片，并且是只分庫不分表的前提下，則可以避免SQL解析和之后的結(jié)果歸并

• 假如路由算法為value % 2，當一個邏輯庫t_order對應(yīng)2個真實庫t_order_0和t_order_1時，路由后SQL將在t_order_1上執(zhí)行。下方是使用API的代碼樣例:

    String sql = "SELECT * FROM t_order";
    try (
    //獲取Hint實例
    HintManager hintManager = HintManager.getInstance();
    Connection conn = dataSource.getConnection();
    PreparedStatement pstmt = conn.prepareStatement(sql)) {
        //設(shè)置數(shù)據(jù)源分片個數(shù)
        hintManager.setDatabaseShardingValue(3);
        try (ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                //...
            }
        }
    }

@RequiredArgsConstructor
public final class DatabaseHintRoutingEngine implements RoutingEngine {
    //數(shù)據(jù)源集群
    private final Collection<String> dataSourceNames;
    //Hint數(shù)據(jù)分片策略
    private final HintShardingStrategy databaseShardingStrategy;
    
    @Override
    public RoutingResult route() {
         //獲取當前線程數(shù)據(jù)源分片
        Collection<Comparable<?>> shardingValues = HintManager.getDatabaseShardingValues();
        Preconditions.checkState(!shardingValues.isEmpty());
        Collection<String> routingDataSources;
        //根據(jù)分片策略路由
        routingDataSources = databaseShardingStrategy.doSharding(dataSourceNames, Collections.<RouteValue>singletonList(new ListRouteValue<>("", "", shardingValues)));
        Preconditions.checkState(!routingDataSources.isEmpty(), "no database route info");
        RoutingResult result = new RoutingResult();
        //封裝路由單元
        for (String each : routingDataSources) {
            result.getRoutingUnits().add(new RoutingUnit(each));
        }
        return result;
    }
}

到此，關(guān)于“sharding-jdbc路由的原理及應(yīng)用”的學習就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>