SQL router

An important module in the sub-database sub-table is data segmentation, which distributes data in multiple DB nodes according to certain rules. Then this process involves routing, that is, a CERTAIN DB node is calculated according to the rules (sharding algorithm) of the SHARding key in SQL. This process is called SQL routing.

The source code

Sharding – JDBC routing entry in shardingStatement and ShardingPreparedStatement unity, analysis shardingStatement source code

    private void shard(final String sql) {
        ShardingRuntimeContext runtimeContext = connection.getRuntimeContext();
        // Routing engine, which handles non-precompiled SQL. All non-precompiled SQL routes are processed here
        SimpleQueryShardingEngine shardingEngine = new SimpleQueryShardingEngine(runtimeContext.getRule(), 
                runtimeContext.getProps(), runtimeContext.getMetaData(), runtimeContext.getDatabaseType(), runtimeContext.getParseEngine());
 // Perform routing according to the routing engine  sqlRouteResult = shardingEngine.shard(sql, Collections.emptyList());  } Copy the code

SqlRouteResult is a data structure built from the result of the route, and is passed as a parameter throughout the execution process.

The shard method performs the routing logic

    public SQLRouteResult shard(final String sql, final List<Object> parameters) {
        // Copy the precompiled parameters to prevent changes to the original values during routing
        List<Object> clonedParameters = cloneParameters(parameters);
        //route
        SQLRouteResult result = executeRoute(sql, clonedParameters);
 Acct_1, accT_2, acct_1,acct_2, acct_1,acct_2, acct_1,acct_2, acct_1,acct_2  result.getRouteUnits().addAll(HintManager.isDatabaseShardingOnly() ? convert(sql, clonedParameters, result) : rewriteAndConvert(sql, clonedParameters, result));  // Outputs routing results in log format, including SQL and DB node information, which facilitates problem tracking  if (shardingProperties.getValue(ShardingPropertiesConstant.SQL_SHOW)) {  boolean showSimple = shardingProperties.getValue(ShardingPropertiesConstant.SQL_SIMPLE);  SQLLogger.logSQL(sql, showSimple, result.getOptimizedStatement(), result.getRouteUnits());  }  return result;  } Copy the code

ExecuteRoute method

    private SQLRouteResult executeRoute(final String sql, final List<Object> clonedParameters) {
        //routingHook is a hook method that is injected to retrieve information during routing. SPI extension technology is used here. Users can use SPI to inject specific hook implementations to implement their own logic, such as route execution time
        routingHook.start(sql);
        try {
            / / routing
 SQLRouteResult result = route(sql, clonedParameters);  Execute the finishSuccess method in the hook  routingHook.finishSuccess(result, metaData.getTable());  return result;  // CHECKSTYLE:OFF  } catch (final Exception ex) {  // CHECKSTYLE:ON  // Execute the hook finishFailure method  routingHook.finishFailure(ex);  throw ex;  }  } Copy the code

The route method

/ / abstract methods, the implementation class has two SimpleQueryShardingEngine and PreparedQueryShardingEngine, a processing precompiled routing, another process precompiled SQL routing
protected  abstract SQLRouteResult route(String sql, List<Object> parameters);

Copy the code

We analyze the precompiled SQL routing SimpleQueryShardingEngine

    public SimpleQueryShardingEngine(final ShardingRule shardingRule, final ShardingProperties shardingProperties, 
                                     final ShardingMetaData metaData, final DatabaseType databaseType, final SQLParseEngine sqlParseEngine) {
        super(shardingRule, shardingProperties, metaData);
        //StatementRoutingEngine Specifies the routing engine
        routingEngine = new StatementRoutingEngine(shardingRule, metaData, databaseType, sqlParseEngine);
 }   @Override  protected List<Object> cloneParameters(final List<Object> parameters) {  return Collections.emptyList();  }   @Override  protected SQLRouteResult route(final String sql, final List<Object> parameters) {  // Perform routing  return routingEngine.route(sql);  } Copy the code
   public SQLRouteResult route(final String logicSQL) {
       / / SQL parsing
        SQLStatement sqlStatement = shardingRouter.parse(logicSQL, false);
       // shardingrouter. route Routes based on SQL parsing results
        return masterSlaveRouter.route(shardingRouter.route(logicSQL, Collections.emptyList(), sqlStatement));
 } Copy the code
    public SQLRouteResult route(final String logicSQL, final List<Object> parameters, final SQLStatement sqlStatement) {
        OptimizedStatement optimizedStatement = ShardingOptimizeEngineFactory.newInstance(sqlStatement).optimize(shardingRule, shardingMetaData.getTable(), logicSQL, parameters, sqlStatement);
        // Whether we need to merge the sub-query shard value with the external query shard value, the analysis of the sharding-JDB neutron query has been analyzed in another article, if you are interested, you can go to see
        boolean needMergeShardingValues = isNeedMergeShardingValues(optimizedStatement);
        if (optimizedStatement instanceof ShardingConditionOptimizedStatement && needMergeShardingValues) {
 // Check the shard key of the subquery to determine whether it is the same as the external shard key  checkSubqueryShardingValues(optimizedStatement, ((ShardingConditionOptimizedStatement) optimizedStatement).getShardingConditions());  mergeShardingConditions(((ShardingConditionOptimizedStatement) optimizedStatement).getShardingConditions());  }  RoutingEngineFactory creates the routing algorithm and then executes the route, returning a RoutingResult  RoutingResult routingResult = RoutingEngineFactory.newInstance(shardingRule, shardingMetaData.getDataSource(), optimizedStatement).route();  if (needMergeShardingValues) {  // Cross-slice queries are not supported  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;  } Copy the code

It mainly constructs a specific routing algorithm implementation class according to the routing policy configured in the table

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0.. 1}.t_order
      Table routing policy
 tableStrategy:  Type of routing algorithm  inline:  # Algorithm parameters  shardingColumn: order_id  algorithmExpression: t_order_${order_id % 2}  databaseStrategy:  inline:  shardingColumn: order_id  algorithmExpression: ds_${order_id % 2} Copy the code

Routing algorithm types can be selected from various types, such as Complex and Standard, etc. Similarly, you can implement a custom algorithm by adding a custom route implementation class to the algorithm parameters.

Sharding-jdbc also supports multi-field routing (multi-dimensional routing)

conclusion

Sharding-jdbc SQL router is relatively simple, and the overall process has been introduced. The difference between the precompiled SQL router and the precompiled SQL router is that the precompiled SQL router needs to extract? Parameter, interested can read the source code, the subsequent SQL result set and do analysis, including memory merge and streaming merge, this relatively complex.