Shulou(Shulou.com)06/02 Report--

This article mainly introduces "the principle and application of sharding-jdbc routing". In daily operation, I believe many people have doubts about the principle and application of sharding-jdbc routing. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the principle and application of sharding-jdbc routing". Next, please follow the editor to study!

Routing engines fall into two main categories:

Fragmentation routing (direct routing, standard routing, Cartesian product routing)

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

For the meaning of specific routing types, refer to the official website routing engine.

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

Mainly analyze query routing

1. Routing ParsingSQLRouter#route entrances

@ RequiredArgsConstructorpublic final class ParsingSQLRouter implements ShardingRouter {@ Override public SQLRouteResult route (final SQLStatement sqlStatement, final List parameters) {/ / optimize, handle conditional placeholder parameters and 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;}.}.

two。 Route factory and route 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 tableNames = sqlStatement.getTables () .getTableNames () / / full-library routing if (sqlStatement instanceof TCLStatement) {return new DatabaseBroadcastRoutingEngine (shardingRule);} / full-library 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);} / / full library routing if (shardingRule.isAllBroadcastTables (tableNames)) {return sqlStatement instanceof SelectStatement? New UnicastRoutingEngine (shardingRule, tableNames): new DatabaseBroadcastRoutingEngine (shardingRule);} / / unicast routing if (optimizedStatement instanceof ShardingWhereOptimizedStatement & & ((ShardingWhereOptimizedStatement) optimizedStatement). GetShardingConditions (). IsAlwaysFalse () | | tableNames.isEmpty ()) {return new UnicastRoutingEngine (shardingRule, tableNames);} Preconditions.checkState (optimizedStatement instanceof ShardingWhereOptimizedStatement); / / fragment routing return getShardingRoutingEngine (shardingRule, (ShardingWhereOptimizedStatement) optimizedStatement, tableNames) }...... Private static RoutingEngine getShardingRoutingEngine (final ShardingRule shardingRule, final ShardingWhereOptimizedStatement optimizedStatement, final Collection tableNames) {/ / obtain the sharding table according to the parsed logic table, such as: SELECT i.* FROM t_order o, t_order_item I WHERE o.order_id = i.order_id and o.order_id =? / / then shardingTableNames is tasking orderthecommerce item Collection shardingTableNames = shardingRule.getShardingLogicTableNames (tableNames) / / follow the standard route if the following two conditions are met, otherwise take the composite route / / 1, whether there is only one sharding table / / 2, and whether the bound logical table (configuration binding table) contains all sharding tables if (1 = = shardingTableNames.size () | | shardingRule.isAllBindingTables (shardingTableNames)) {/ / standard route, and you can get the first table route. Refer to the official website 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 StandardRoutingEngine#route

Standard routing scenario

T_order and t_order_item are sub-library sub-tables and bound tables. For example, if line 55 in the second step determines that shardingTableNames.size () = 1 or shardingTableNames is both bound tables, the standard route will be taken.

@ RequiredArgsConstructorpublic final class StandardRoutingEngine implements RoutingEngine {/ / sublibrary and table rules private final ShardingRule shardingRule; / / logical table t_order private final String logicTableName; / / sql parsed and optimized result private final ShardingWhereOptimizedStatement optimizedStatement @ Override public RoutingResult route () {/ / insert, update, delete determine whether the table is a single table if (isDMLForModify (optimizedStatement.getSQLStatement ()) & &! optimizedStatement.getSQLStatement (). GetTables (). IsSingleTable ()) {throw new SQLParsingException ("Cannot support Multiple-Table for'% slots.", optimizedStatement.getSQLStatement ()) } / / routing data node, encapsulating routing result return generateRoutingResult (getDataNodes (shardingRule.getTableRule (logicTableName);}. Private RoutingResult generateRoutingResult (final Collection routedDataNodes) {RoutingResult result = new RoutingResult (); / / encapsulates the routing unit and table unit for (DataNode each: routedDataNodes) {/ / routing unit demo_ds_0 RoutingUnit routingUnit = new RoutingUnit (each.getDataSourceName ()) according to the data node / / Table unit logical table: real table t_order:t_order_0 routingUnit.getTableUnits (). Add (new TableUnit (logicTableName, each.getTableName ()); result.getRoutingUnits (). Add (routingUnit);} return result } private Collection getDataNodes (final TableRule tableRule) {/ / determine that database and table sharding strategy are also Hint (direct routing) if (shardingRule.isRoutingByHint (tableRule)) {return routeByHint (tableRule) } / / database and table sharding policies are not Hint if (isRoutingByShardingConditions (tableRule)) {/ / routing to the corresponding database and table according to sharding conditions and policies, and judging return routeByShardingConditions (tableRule) with the same pair of sharding keys;} / / database or table sharding strategy has one Hint return routeByMixedConditions (tableRule);}.}.

4. Compound routing CartesianRoutingEngine#route

Complex routing scenario

T_order and t_order_item are sub-library sub-tables and bound tables; add a t_user sub-library sub-table, then the 55 lines of the second step, shardingTableNames.size () = 3, and t_user is not configured as a binding table, then a compound route will be taken.

@ RequiredArgsConstructorpublic final class ComplexRoutingEngine implements RoutingEngine {/ / private final ShardingRule shardingRule; / / logical table t_order, t_order_item private final Collection logicTables; / / sql parses and optimizes the result private final ShardingWhereOptimizedStatement optimizedStatement; @ Override public RoutingResult route () {Collection result = new ArrayList (logicTables.size ()); Collection bindingTableNames = new TreeSet (String.CASE_INSENSITIVE_ORDER) / / traversing the logical table for (String each: logicTables) {Optional tableRule = shardingRule.findTableRule (each) / / whether the table is configured with if (tableRule.isPresent ()) {/ / if the binding relationship table has been processed, it does not need to be processed, such as t_order. Since t_order_item is bound to it, it does not need to be processed. If (! bindingTableNames.contains (each)) {/ / build standard routes and route result.add (new StandardRoutingEngine (shardingRule, tableRule.get (). GetLogicTable (), optimizedStatement). Route ()) } / / find all the corresponding binding tables according to the logical table. For example, you can query t_order and t_order_item according to t_order, because t_order and t_order_item are binding tables Optional bindingTableRule = shardingRule.findBindingTableRule (each). If (bindingTableRule.isPresent ()) {/ / add binding table bindingTableNames.addAll (Lists.transform (bindingTableRule.get (). GetTableRules ()) New Function () {@ 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. Cartesian product routing

Cartesian product routing scenario

For example, in the new t_user sub-database table in the above example, t_user and t_order are not configured with bound table relations. For the following SQL, you need to take 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)

@ RequiredArgsConstructorpublic final class CartesianRoutingEngine implements RoutingEngine {private final Collection routingResults; @ Override public RoutingResult route () {RoutingResult result = new RoutingResult () / / get the set of logical tables corresponding to the data source for (Entry entry: getDataSourceLogicTablesMap (). EntrySet ()) {/ / obtain the actual table group through the data source name and logical table name, that is, [["t_user_0", "t_user_1"], ["t_order_0", "t_order_1]] List actualTableGroups = getActualTableGroups (entry.getKey (), entry.getValue ()) / / encapsulate the logical table name as a TableUnit table unit / / TableUnit contains the data source name, logical table name, and actual table name (the final accessed table can be determined by these three attributes) List routingTableGroups = toRoutingTableGroups (entry.getKey (), actualTableGroups) / / encapsulate RoutingUnit routing unit / / cartesianProduct calculates Cartesian product result.getRoutingUnits (). AddAll (getRoutingUnits (entry.getKey (), Sets.cartesianProduct (routingTableGroups);} return result } private Map getDataSourceLogicTablesMap () {/ / get the intersection of data sources, such as t_user logical table routing to data source demo_ds_0, while t_order table routing to data sources ds_demo_0 and demo_ds_1, data source intersection is demo_ds_0 / / case 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 bound table relations / / Cartesian routing is the most complex case, it cannot locate sharding rules according to the relationship of bound tables, so the association query between unbound tables needs to be disassembled into a combination of Cartesian products to execute Collection intersectionDataSources = getIntersectionDataSources (); Map result = new HashMap (routingResults.size ()) / / the result set for (RoutingResult each: routingResults) after traversing the standard route {/ / obtain the mapping relationship between the data source and the logical table through the name of the data source for (Entry 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 data source-logical table collection. Here is {"demo_ds_0": ["t_user", "t_order"]} return result. } private Collection getIntersectionDataSources () {Collection result = new HashSet (); for (RoutingResult each: routingResults) {if (result.isEmpty ()) {result.addAll (each.getDataSourceNames ());} / / intersection result.retainAll (each.getDataSourceNames ());} return result }......}

The result of Cartesian product is as follows:

6. Direct routing

Direct routing scenario

The conditions for direct routing are relatively stringent. It needs to be sliced through Hint (routing to the database table directly using HintAPI), and under the premise that only the library is divided into tables, then SQL parsing and subsequent merging of results can be avoided.

If the routing algorithm is value% 2, when a logical library t_order corresponds to two real libraries t_order_0 and tdistribuorder1, the routed SQL will be executed on t_order_1. The following is a code example that uses API:

String sql = "SELECT * FROM t_order"; try (/ / get Hint instance HintManager hintManager = HintManager.getInstance (); Connection conn = dataSource.getConnection (); PreparedStatement pstmt = conn.prepareStatement (sql)) {/ / set the number of data source shards hintManager.setDatabaseShardingValue (3) Try (ResultSet rs = pstmt.executeQuery ()) {while (rs.next ()) {/ /...} @ RequiredArgsConstructorpublic final class DatabaseHintRoutingEngine implements RoutingEngine {/ / data source cluster private final Collection dataSourceNames; / / Hint data fragmentation policy private final HintShardingStrategy databaseShardingStrategy @ Override public RoutingResult route () {/ / get the current thread data source sharding Collection

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.