ShardingSphere5.0.0-beta-jdbc-sql解析和sql路由源码（一）

yanbing007

已于 2022-04-05 20:48:44 修改

阅读量1k

点赞数 1

分类专栏： shardingsphere 文章标签： sql spring java

于 2021-08-29 16:36:38 首次发布

本文链接：https://blog.csdn.net/qq_37869243/article/details/119923675

版权

shardingsphere 专栏收录该内容

6 篇文章 2 订阅

订阅专栏

ShardingSphere5.0.0-beta-jdbc-sql解析和sql路由源码（一）

jdbc核心原理

当Sharding-JDBC接受到一条SQL语句时，会陆续执行 SQL解析 => 查询优化 => SQL路由 => SQL改写 => SQL执行 =>结果归并，最终返回执行结果，源码初探，我们按着这个思路debug跟源码
在这里插入图片描述

demo入口

测试背景：两个库：ds0,ds1，每个库四个表：t_order_0，t_order_1，t_order_2，t_order_3

查询demo
调用栈（按照标记顺序依次解读）
1 查询入口
2 spring的aop动态代理拦截方式，生成拦截器PersistenceExceptionTranslationInterceptor
3 通过责任链式机制有序执行各种拦截器

    @Nullable
    public Object proceed() throws Throwable {
        if (this.currentInterceptorIndex == this.interceptorsAndDynamicMethodMatchers.size() - 1) {
        //全部执行后，会执行invokeJoinpoint，即真正的方法
            return this.invokeJoinpoint();
        } else {
            Object interceptorOrInterceptionAdvice = this.interceptorsAndDynamicMethodMatchers.get(++this.currentInterceptorIndex);
            if (interceptorOrInterceptionAdvice instanceof InterceptorAndDynamicMethodMatcher) {
                InterceptorAndDynamicMethodMatcher dm = (InterceptorAndDynamicMethodMatcher)interceptorOrInterceptionAdvice;
                Class<?> targetClass = this.targetClass != null ? this.targetClass : this.method.getDeclaringClass();
                return dm.methodMatcher.matches(this.method, targetClass, this.arguments) ? dm.interceptor.invoke(this) : this.proceed();
            } else {
                return ((MethodInterceptor)interceptorOrInterceptionAdvice).invoke(this);
            }
        }
    }

4 mybatisplus执行器
在这里插入图片描述
5 ibatis执行器
6 PreparedStatementHandler#query

    public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
        PreparedStatement ps = (PreparedStatement)statement;
        //步骤7执行
        ps.execute();
        //执行完后，对结果统一归并处理
        return this.resultSetHandler.handleResultSets(ps);
    }

7 ShardingSpherePreparedStatement#execute
ShardingSpherePreparedStatement是PreparedStatement的实现，PreparedStatement是原生jdbc的核心类，shardingsphere-jdbc在PreparedStatement基础上进一步实现，我们看下ShardingSpherePreparedStatement#execute的源码

    public boolean execute() throws SQLException {
        boolean var2;
        try {
            this.clearPrevious();
            //核心方法：sql解析、sql路由、sql改写都在这个方法里
            this.executionContext = this.createExecutionContext();
            if (this.metaDataContexts.getDefaultMetaData().getRuleMetaData().getRules().stream().anyMatch((each) -> {
                return each instanceof RawExecutionRule;
            })) {
                Collection<ExecuteResult> executeResults = this.rawExecutor.execute(this.createRawExecutionGroupContext(), this.executionContext.getSqlStatementContext(), new RawSQLExecutorCallback());
                var2 = executeResults.iterator().next() instanceof QueryResult;
                return var2;
            }

            if (this.executionContext.getRouteContext().isFederated()) {
                List<QueryResult> queryResults = this.executeFederatedQuery();
                var2 = !queryResults.isEmpty();
                return var2;
            }
            //根据数据库初级
            ExecutionGroupContext<JDBCExecutionUnit> executionGroupContext = this.createExecutionGroupContext();
            this.cacheStatements(executionGroupContext.getInputGroups());
            //sql执行
            var2 = this.driverJDBCExecutor.execute(executionGroupContext, this.executionContext.getSqlStatementContext(), this.executionContext.getRouteContext().getRouteUnits(), this.createExecuteCallback());
        } finally {
            this.clearBatch();
        }

        return var2;
    }

在这里插入图片描述

SQL解析

ShardingSpherePreparedStatement#createExecutionContext

    private ExecutionContext createExecutionContext() {
        //
        LogicSQL logicSQL = this.createLogicSQL();
        SQLCheckEngine.check(logicSQL.getSqlStatementContext().getSqlStatement(), logicSQL.getParameters(), this.metaDataContexts.getDefaultMetaData().getRuleMetaData().getRules(), "logic_db", this.metaDataContexts.getMetaDataMap(), (Grantee)null);
        //sql,配置规则元数据，属性值生成ExecutionContext 对象
        ExecutionContext result = this.kernelProcessor.generateExecutionContext(logicSQL, this.metaDataContexts.getDefaultMetaData(), this.metaDataContexts.getProps());
        //判断主键是否自动生成，如果是则生成主键
        this.findGeneratedKey(result).ifPresent((generatedKey) -> {
            this.generatedValues.addAll(generatedKey.getGeneratedValues());
        });
        return result;
    }

    private LogicSQL createLogicSQL() {
        List<Object> parameters = new ArrayList(this.getParameters());
        ShardingSphereSchema schema = this.metaDataContexts.getDefaultMetaData().getSchema();
        SQLStatementContext<?> sqlStatementContext = SQLStatementContextFactory.newInstance(schema, parameters, this.sqlStatement);
        return new LogicSQL(sqlStatementContext, this.sql, parameters);
    }

createLogicSQL解析获取到了sql：

SELECT  order_id,item,user_id  FROM t_order  
 WHERE order_id = ?

在这里插入图片描述

this.kernelProcessor.generateExecutionContext

   public ExecutionContext generateExecutionContext(LogicSQL logicSQL, ShardingSphereMetaData metaData, ConfigurationProperties props) {
        //sql路由
        RouteContext routeContext = this.route(logicSQL, metaData, props);
        //sql改写
        SQLRewriteResult rewriteResult = this.rewrite(logicSQL, metaData, props, routeContext);
        ExecutionContext result = this.createExecutionContext(logicSQL, metaData, routeContext, rewriteResult);
        this.logSQL(logicSQL, props, result);
        return result;
    }

SQL路由

执行：this.route(logicSQL, metaData, props) 依次调用到ShardingSQLRouter#createRouteContext

    public RouteContext createRouteContext(LogicSQL logicSQL, ShardingSphereMetaData metaData, ShardingRule rule, ConfigurationProperties props) {
        RouteContext result = new RouteContext();
        SQLStatement sqlStatement = logicSQL.getSqlStatementContext().getSqlStatement();
        Optional<ShardingStatementValidator> validator = ShardingStatementValidatorFactory.newInstance(sqlStatement);
        validator.ifPresent((optional) -> {
            optional.preValidate(rule, logicSQL.getSqlStatementContext(), logicSQL.getParameters(), metaData.getSchema());
        });
        
        //获取分片查询条件： ShardingConditions(conditions=[ShardingCondition(values=[t_order.order_id = 1])])
        ShardingConditions shardingConditions = this.createShardingConditions(logicSQL, metaData, rule);
        boolean needMergeShardingValues = this.isNeedMergeShardingValues(logicSQL.getSqlStatementContext(), rule);
        if (sqlStatement instanceof DMLStatement && needMergeShardingValues) {
            this.mergeShardingConditions(shardingConditions);
        }
       //真实的sql路由
        ShardingRouteEngineFactory.newInstance(rule, metaData, logicSQL.getSqlStatementContext(), shardingConditions, props).route(result, rule);
        validator.ifPresent((v) -> {
            v.postValidate(rule, logicSQL.getSqlStatementContext(), result, metaData.getSchema());
        });
        return result;
    }

创建ShardingStandardRoutingEngine
ShardingRouteEngineFactory.newInstance(rule, metaData, logicSQL.getSqlStatementContext(), shardingConditions, props)

   SQLStatement sqlStatement = sqlStatementContext.getSqlStatement();
        if (sqlStatement instanceof TCLStatement) {
            return new ShardingDatabaseBroadcastRoutingEngine();
        } else if (sqlStatement instanceof DDLStatement) {
            return getDDLRoutingEngine(shardingRule, metaData, sqlStatementContext);
        } else if (sqlStatement instanceof DALStatement) {
            return getDALRoutingEngine(shardingRule, metaData, sqlStatementContext);
        } else if (sqlStatement instanceof DCLStatement) {
            return getDCLRoutingEngine(shardingRule, metaData, sqlStatementContext);
        } else {
            Collection<String> tableNames = sqlStatementContext.getTablesContext().getTableNames();
            return getDQLRoutingEngine(shardingRule, sqlStatementContext, shardingConditions, props, sqlStatement, tableNames);
        }
      		  .....	  .....
     	      .....   .....
           if (shardingRule.isAllBroadcastTables(tableNames)) {
            return (ShardingRouteEngine)(sqlStatement instanceof SelectStatement ? new ShardingUnicastRoutingEngine(tableNames) : new ShardingDatabaseBroadcastRoutingEngine());
        } else if ((!(sqlStatementContext.getSqlStatement() instanceof DMLStatement) || !shardingConditions.isAlwaysFalse()) && !tableNames.isEmpty()) {
            if (!shardingRule.tableRuleExists(tableNames)) {
                return new SingleTablesRoutingEngine(tableNames, sqlStatement);
            } else if ((!shardingRule.isAllShardingTables(tableNames) || 1 != tableNames.size()) && !shardingRule.isAllBindingTables(tableNames)) {
                return (ShardingRouteEngine)(isShardingFederatedQuery(sqlStatementContext, tableNames, shardingRule) ? new ShardingFederatedRoutingEngine(tableNames) : new ShardingComplexRoutingEngine(tableNames, shardingConditions, props));
            } else {
            //由这里创建ShardingStandardRoutingEngine
                return new ShardingStandardRoutingEngine((String)tableNames.iterator().next(), shardingConditions, props);
            }
        } else {
            return new ShardingUnicastRoutingEngine(tableNames);
        }

ShardingRouteEngineFactory.newInstance(rule, metaData, logicSQL.getSqlStatementContext(), shardingConditions, props).route(result, rule)调用ShardingStandardRoutingEngine#route方法

    public void route(RouteContext routeContext, ShardingRule shardingRule) {
    	//获取DataNode节点集合，参数为表规则配置
        Collection<DataNode> dataNodes = this.getDataNodes(shardingRule, shardingRule.getTableRule(this.logicTableName));
        routeContext.getOriginalDataNodes().addAll(this.originalDataNodes);
        Iterator var4 = dataNodes.iterator();
 		//遍历dataNodes，封装为路由单元对象RouteUnit
        while(var4.hasNext()) {
            DataNode each = (DataNode)var4.next();
            routeContext.getRouteUnits().add(new RouteUnit(new RouteMapper(each.getDataSourceName(), each.getDataSourceName()), Collections.singleton(new RouteMapper(this.logicTableName, each.getTableName()))));
        }
    }

this.getDataNodes(shardingRule, shardingRule.getTableRule(this.logicTableName))

    private Collection<DataNode> getDataNodes(ShardingRule shardingRule, TableRule tableRule) {
        ShardingStrategy databaseShardingStrategy = this.createShardingStrategy(shardingRule.getDatabaseShardingStrategyConfiguration(tableRule), shardingRule.getShardingAlgorithms());
        ShardingStrategy tableShardingStrategy = this.createShardingStrategy(shardingRule.getTableShardingStrategyConfiguration(tableRule), shardingRule.getShardingAlgorithms());
        if (this.isRoutingByHint(shardingRule, tableRule)) {
            return this.routeByHint(tableRule, databaseShardingStrategy, tableShardingStrategy);
        } else {
        //是否根据分片条件路由
        //数据库和表的分片策略都是不是Hint分片方式走this.routeByShardingConditions方法
            return this.isRoutingByShardingConditions(shardingRule, tableRule) ? this.routeByShardingConditions(shardingRule, tableRule, databaseShardingStrategy, tableShardingStrategy) : this.routeByMixedConditions(shardingRule, tableRule, databaseShardingStrategy, tableShardingStrategy);
        }
    }
	#########################################################################
	//数据库和表的分片策略都是不是Hint分片方式
    private boolean isRoutingByShardingConditions(ShardingRule shardingRule, TableRule tableRule) {
        return !(shardingRule.getDatabaseShardingStrategyConfiguration(tableRule) instanceof HintShardingStrategyConfiguration) && !(shardingRule.getTableShardingStrategyConfiguration(tableRule) instanceof HintShardingStrategyConfiguration);
    }
    
    #########################################################################
    private Collection<DataNode> routeByShardingConditions(ShardingRule shardingRule, TableRule tableRule, ShardingStrategy databaseShardingStrategy, ShardingStrategy tableShardingStrategy) {
 	    //查询是否不存在条件，直接走 this.route0，否则走this.routeByShardingConditionsWithCondition
        return this.shardingConditions.getConditions().isEmpty() ? this.route0(tableRule, databaseShardingStrategy, Collections.emptyList(), tableShardingStrategy, Collections.emptyList()) : this.routeByShardingConditionsWithCondition(shardingRule, tableRule, databaseShardingStrategy, tableShardingStrategy);
    }
    
    #########################################################################
    private Collection<DataNode> routeByShardingConditionsWithCondition(ShardingRule shardingRule, TableRule tableRule, ShardingStrategy databaseShardingStrategy, ShardingStrategy tableShardingStrategy) {
        Collection<DataNode> result = new LinkedList();
        Iterator var6 = this.shardingConditions.getConditions().iterator();

        while(var6.hasNext()) {
            ShardingCondition each = (ShardingCondition)var6.next();
            Collection<DataNode> dataNodes = this.route0(tableRule, databaseShardingStrategy, this.getShardingValuesFromShardingConditions(shardingRule, databaseShardingStrategy.getShardingColumns(), each), tableShardingStrategy, this.getShardingValuesFromShardingConditions(shardingRule, tableShardingStrategy.getShardingColumns(), each));
            result.addAll(dataNodes);
            this.originalDataNodes.add(dataNodes);
        }

        return result;
    }

    #########################################################################
    private Collection<DataNode> routeByShardingConditionsWithCondition(ShardingRule shardingRule, TableRule tableRule, ShardingStrategy databaseShardingStrategy, ShardingStrategy tableShardingStrategy) {
        Collection<DataNode> result = new LinkedList();
        Iterator var6 = this.shardingConditions.getConditions().iterator();
      
      //查询条件遍历过滤封装DataNode节点集合
        while(var6.hasNext()) {
            ShardingCondition each = (ShardingCondition)var6.next();
            Collection<DataNode> dataNodes = this.route0(tableRule, databaseShardingStrategy, this.getShardingValuesFromShardingConditions(shardingRule, databaseShardingStrategy.getShardingColumns(), each), tableShardingStrategy, this.getShardingValuesFromShardingConditions(shardingRule, tableShardingStrategy.getShardingColumns(), each));
            result.addAll(dataNodes);
            this.originalDataNodes.add(dataNodes);
        }

        return result;
    }


    #########################################################################
    private Collection<DataNode> route0(TableRule tableRule, ShardingStrategy databaseShardingStrategy, 
    List<ShardingConditionValue> databaseShardingValues, ShardingStrategy tableShardingStrategy, List<ShardingConditionValue> tableShardingValues) {
        //获取到路由数据库名称集合  ds_0,ds_1
        Collection<String> routedDataSources = this.routeDataSources(tableRule, databaseShardingStrategy, databaseShardingValues);
        Collection<DataNode> result = new LinkedList();
        Iterator var8 = routedDataSources.iterator();

        while(var8.hasNext()) {
            String each = (String)var8.next();
            result.addAll(this.routeTables(tableRule, each, tableShardingStrategy, tableShardingValues));
        }

        return result;
    }

    #########################################################################
    private Collection<DataNode> routeTables(TableRule tableRule, String routedDataSource, ShardingStrategy tableShardingStrategy, List<ShardingConditionValue> tableShardingValues) {
        //获取到真实的表集合 t_order_0,t_order_1,t_order_2,t_order_3
        Collection<String> availableTargetTables = tableRule.getActualTableNames(routedDataSource);
        //获取到路由后的表集合
        Collection<String> routedTables = new LinkedHashSet(tableShardingValues.isEmpty() ? availableTargetTables : tableShardingStrategy.doSharding(availableTargetTables, tableShardingValues, this.properties));
        Collection<DataNode> result = new LinkedList();
        Iterator var8 = routedTables.iterator();

        while(var8.hasNext()) {
            String each = (String)var8.next();
            result.add(new DataNode(routedDataSource, each));
        }

        return result;
    }


    #########################################################################
    #标准分片策略StandardShardingStrategy#doSharding
    public Collection<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingConditionValue> shardingConditionValues, ConfigurationProperties props) {
        ShardingConditionValue shardingConditionValue = (ShardingConditionValue)shardingConditionValues.iterator().next();
        Collection<String> shardingResult = shardingConditionValue instanceof ListShardingConditionValue ? this.doSharding(availableTargetNames, (ListShardingConditionValue)shardingConditionValue) : this.doSharding(availableTargetNames, (RangeShardingConditionValue)shardingConditionValue);
        Collection<String> result = new TreeSet(String.CASE_INSENSITIVE_ORDER);
        result.addAll(shardingResult);
        return result;
    }
    #########################################################################


    private Collection<String> doSharding(Collection<String> availableTargetNames, ListShardingConditionValue<?> shardingValue) {
        Collection<String> result = new LinkedList();
        Iterator var4 = shardingValue.getValues().iterator();

        while(true) {
            while(var4.hasNext()) {
                Comparable<?> each = (Comparable)var4.next();
                //核心方法
                String target = this.shardingAlgorithm.doSharding(availableTargetNames, new PreciseShardingValue(shardingValue.getTableName(), shardingValue.getColumnName(), each));
                if (null != target && availableTargetNames.contains(target)) {
                    result.add(target);
                } else if (null != target && !availableTargetNames.contains(target)) {
                    throw new ShardingSphereException(String.format("Route table %s does not exist, available actual table: %s", target, availableTargetNames), new Object[0]);
                }
            }

            return result;
        }
    }

this.shardingAlgorithm.doSharding(availableTargetNames, new PreciseShardingValue(shardingValue.getTableName(), shardingValue.getColumnName(), each))
官方提供七种分片策略，自定义实现StandardShardingAlgorithm，就可完成自己定义分分片策略

当前使用InlineShardingAlgorithm#doSharding，处理完后t_order_0,t_order_1,t_order_2,t_order_3，只剩t _order_1，如下截图

    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Comparable<?>> shardingValue) {
        Closure<?> closure = this.createClosure();
        closure.setProperty(shardingValue.getColumnName(), shardingValue.getValue());
        return closure.call().toString();
    }

在这里插入图片描述

SQL改写

sql路由后返回对象RouteContext，
SQLRewriteResult rewriteResult = this.rewrite(logicSQL, metaData, props, routeContext);

    public SQLRewriteResult rewrite(String sql, List<Object> parameters, SQLStatementContext<?> sqlStatementContext, RouteContext routeContext) {
    //获取到sql信息： SELECT  order_id,item,user_id  FROM t_order   WHERE order_id = ?
        SQLRewriteContext sqlRewriteContext = this.createSQLRewriteContext(sql, parameters, sqlStatementContext, routeContext);
        //sql重写引擎处理
        return (SQLRewriteResult)(routeContext.getRouteUnits().isEmpty() ? (new GenericSQLRewriteEngine()).rewrite(sqlRewriteContext) : (new RouteSQLRewriteEngine()).rewrite(sqlRewriteContext, routeContext));
    }

    #########################################################################
    public RouteSQLRewriteResult rewrite(SQLRewriteContext sqlRewriteContext, RouteContext routeContext) {
        Map<RouteUnit, SQLRewriteUnit> result = new LinkedHashMap(routeContext.getRouteUnits().size(), 1.0F);
        Iterator var4 = routeContext.getRouteUnits().iterator();
       //路由数据片遍历改写sql
        while(var4.hasNext()) {
            RouteUnit each = (RouteUnit)var4.next();
            result.put(each, new SQLRewriteUnit((new RouteSQLBuilder(sqlRewriteContext, each)).toSQL(), this.getParameters(sqlRewriteContext.getParameterBuilder(), routeContext, each)));
        }

        return new RouteSQLRewriteResult(result);
    }

核心方法: new SQLRewriteUnit((new RouteSQLBuilder(sqlRewriteContext, each)).toSQL()
真实改写逻辑

    public final String toSQL() {
        if (this.context.getSqlTokens().isEmpty()) {
            return this.context.getSql();
        } else {
            Collections.sort(this.context.getSqlTokens());
            StringBuilder result = new StringBuilder();
            result.append(this.context.getSql(), 0, ((SQLToken)this.context.getSqlTokens().get(0)).getStartIndex());
            Iterator var2 = this.context.getSqlTokens().iterator();

            while(var2.hasNext()) {
                SQLToken each = (SQLToken)var2.next();
                result.append(each instanceof ComposableSQLToken ? this.getComposableSQLTokenText((ComposableSQLToken)each) : this.getSQLTokenText(each));
                result.append(this.getConjunctionText(each));
            }

            return result.toString();
        }
    }

yanbing007

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
ShardingSphere5.0.0-beta-jdbc-sql解析和sql路由源码（一）

https://blog.csdn.net/gonghaiyu/article/details/112081053https://www.jianshu.com/p/d927ad6a1b3e
复制链接

扫一扫