我是靠谱客的博主 光亮柜子,最近开发中收集的这篇文章主要介绍数据库拆分3--使用sharding-jdbc 支持子查询sql,觉得挺不错的,现在分享给大家,希望可以做个参考。

概述

最近在使用sharding-jdbc来改造项目的时候遇到了一些问题,主要是有关子查询的,记录一下。

结论

4.0.0-RC1版本是有限制支持子查询的 主查询和子查询必须保证相同的分片键;

4.4.1可以支持子查询  子查询判断条件恒为false;

5.0版本 Federation 执行引擎支持子查询;

4.0.0-RC1版本是有限制支持子查询

在某一个库中新建两张表

CREATE TABLE `user_t` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(8) DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1601873255239520258 DEFAULT CHARSET=utf8;

CREATE TABLE `log_t` (
  `id` bigint(20) NOT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `log_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

模拟了一段子查询sql

<select id="findUserByLog2" resultMap="BaseResultMap">
   SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE log_date > '2022-12-13') and name = 'zhangsan'
</select>

依赖版本

 <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

配置文件如下:

server.servlet.context-path=/mutildb


mybatis-plus.configuration.log-impl = org.apache.ibatis.logging.stdout.StdOutImpl
logging.level.root=DEBUG


#shardingjdbc
spring.shardingsphere.datasource.names=wim-user,wim-order

spring.shardingsphere.datasource.wim-user.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.wim-user.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.wim-user.url=jdbc:mysql://127.0.0.1:3306/wim-user?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.wim-user.username=root
spring.shardingsphere.datasource.wim-user.password=123456

spring.shardingsphere.datasource.wim-order.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.wim-order.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.wim-order.url=jdbc:mysql://127.0.0.1:3306/wim-order?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.wim-order.username=root
spring.shardingsphere.datasource.wim-order.password=123456

spring.shardingsphere.sharding.tables.user_t.actual-data-nodes=wim-user.user_t
spring.shardingsphere.sharding.tables.log_t.actual-data-nodes=wim-user.log_t

spring.shardingsphere.sharding.tables.user_t.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.user_t.table-strategy.inline.algorithm-expression=user_t

spring.shardingsphere.sharding.tables.log_t.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.log_t.table-strategy.inline.algorithm-expression=log_t


spring.shardingsphere.sharding.default-data-source-name=wim-order
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

调用发现报错如下:

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2e4f7a0d] was not registered for synchronization because synchronization is not active
2022-12-15 21:09:41.574 DEBUG 20200 --- [nio-8080-exec-2] o.s.jdbc.datasource.DataSourceUtils      : Fetching JDBC Connection from DataSource
JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@208881b4] will not be managed by Spring
==>  Preparing: SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE log_date > '2022-12-13') and name = 'zhangsan'
==> Parameters: 
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2e4f7a0d]
2022-12-15 21:09:47.028 DEBUG 20200 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet        : Failed to complete request: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalStateException: Must have sharding column with subquery.
### The error may exist in file [D:codestudymuitlDataSourcetargetclassesmapperUserMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE log_date > '2022-12-13') and name = 'zhangsan'
### Cause: java.lang.IllegalStateException: Must have sharding column with subquery.
2022-12-15 21:09:47.030 ERROR 20200 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/mutildb] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalStateException: Must have sharding column with subquery.
### The error may exist in file [D:codestudymuitlDataSourcetargetclassesmapperUserMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE log_date > '2022-12-13') and name = 'zhangsan'
### Cause: java.lang.IllegalStateException: Must have sharding column with subquery.] with root cause

java.lang.IllegalStateException: Must have sharding column with subquery.
	at com.google.common.base.Preconditions.checkState(Preconditions.java:173) ~[guava-18.0.jar:na]
	at org.apache.shardingsphere.core.route.router.sharding.ParsingSQLRouter.checkSubqueryShardingValues(ParsingSQLRouter.java:136) ~[sharding-core-route-4.0.0-RC1.jar:4.0.0-RC1]
	at org.apache.shardingsphere.core.route.router.sharding.ParsingSQLRouter.route(ParsingSQLRouter.java:103) ~[sharding-core-route-4.0.0-RC1.jar:4.0.0-RC1]
	at org.apache.shardingsphere.core.route.PreparedStatementRoutingEngine.route(PreparedStatementRoutingEngine.java:66) ~[sharding-core-route-4.0.0-RC1.jar:4.0.0-RC1]
	at org.apache.shardingsphere.core.PreparedQueryShardingEngine.route(PreparedQueryShardingEngine.java:60) ~[sharding-core-entry-4.0.0-RC1.jar:4.0.0-RC1]
	at org.apache.shardingsphere.core.BaseShardingEngine.shard(BaseShardingEngine.java:64) ~[sharding-core-entry-4.0.0-RC1.jar:4.0.0-RC1]
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.shard(ShardingPreparedStatement.java:224) ~[sharding-jdbc-core-4.0.0-RC1.jar:4.0.0-RC1]
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:170) ~[sharding-jdbc-core-4.0.0-RC1.jar:4.0.0-RC1]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_272]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_272]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_272]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_272]
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.5.9.jar:3.5.9]
	at com.sun.proxy.$Proxy74.execute(Unknown Source) ~[na:na]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.9.jar:3.5.9]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_272]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_272]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_272]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_272]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar:2.0.6]
	at com.sun.proxy.$Proxy58.selectList(Unknown Source) ~[na:na]
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224) ~[mybatis-spring-2.0.6.jar:2.0.6]
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166) ~[mybatis-plus-core-3.5.1.jar:3.5.1]
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77) ~[mybatis-plus-core-3.5.1.jar:3.5.1]
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) ~[mybatis-plus-core-3.5.1.jar:3.5.1]
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) ~[mybatis-plus-core-3.5.1.jar:3.5.1]
	at com.sun.proxy.$Proxy62.findUserByLog2(Unknown Source) ~[na:na]
	at com.chen.rest.UserRest.queryUserByLog2(UserRest.java:25) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_272]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_272]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_272]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_272]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:626) ~[tomcat-embed-core-9.0.41.jar:4.0.FR]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) ~[tomcat-embed-core-9.0.41.jar:4.0.FR]

主要报错:Must have sharding column with subquery  顾名思义 子查询必须得有分片键

看了一下源码:

public SQLRouteResult route(String logicSQL, List<Object> parameters, SQLStatement sqlStatement) {
        Optional<GeneratedKey> generatedKey = sqlStatement instanceof InsertStatement ? GeneratedKey.getGenerateKey(this.shardingRule, parameters, (InsertStatement)sqlStatement) : Optional.absent();
        SQLRouteResult result = new SQLRouteResult(sqlStatement, (GeneratedKey)generatedKey.orNull());
        OptimizeResult optimizeResult = OptimizeEngineFactory.newInstance(this.shardingRule, sqlStatement, parameters, (GeneratedKey)generatedKey.orNull()).optimize();
        if (generatedKey.isPresent()) {
            this.setGeneratedKeys(result, (GeneratedKey)generatedKey.get());
        }

        boolean needMerge = false;
        if (sqlStatement instanceof SelectStatement) {
            needMerge = this.isNeedMergeShardingValues((SelectStatement)sqlStatement);
        }

        if (needMerge) {
            this.checkSubqueryShardingValues(sqlStatement, optimizeResult.getShardingConditions());
            this.mergeShardingValues(optimizeResult.getShardingConditions());
        }

        RoutingResult routingResult = RoutingEngineFactory.newInstance(this.shardingRule, this.shardingMetaData.getDataSource(), sqlStatement, optimizeResult).route();
        if (sqlStatement instanceof SelectStatement && null != ((SelectStatement)sqlStatement).getLimit() && !routingResult.isSingleRouting()) {
            result.setLimit(this.getProcessedLimit(parameters, (SelectStatement)sqlStatement));
        }

        if (needMerge) {
            Preconditions.checkState(1 == routingResult.getTableUnits().getTableUnits().size(), "Must have one sharding with subquery.");
        }

        result.setRoutingResult(routingResult);
        result.setOptimizeResult(optimizeResult);
        return result;
    }
checkSubqueryShardingValues
 private void checkSubqueryShardingValues(SQLStatement sqlStatement, ShardingConditions shardingConditions) {
        Iterator var3 = sqlStatement.getTables().getTableNames().iterator();

        while(var3.hasNext()) {
            String each = (String)var3.next();
            Optional<TableRule> tableRule = this.shardingRule.findTableRule(each);
            if (tableRule.isPresent() && this.shardingRule.isRoutingByHint((TableRule)tableRule.get()) && !HintManager.getDatabaseShardingValues(each).isEmpty() && !HintManager.getTableShardingValues(each).isEmpty()) {
                return;
            }
        }

        Preconditions.checkState(null != shardingConditions.getShardingConditions() && !shardingConditions.getShardingConditions().isEmpty(), "Must have sharding column with subquery.");
        if (shardingConditions.getShardingConditions().size() > 1) {
            Preconditions.checkState(this.isSameShardingCondition(shardingConditions), "Sharding value must same with subquery.");
        }

    }

可知 如果子查询没有分片键 则会报错:

Must have sharding column with subquery.

如果子查询分片键和主查询分片键不一致  也会报错:

Sharding value must same with subquery.

因此在sharding-jdbc中使用子查询条件是非常有限制的,子查询查询条件必须待分片键 并且和主查询得保持一直。

sql改写如下:

<select id="findUserByLog" resultMap="BaseResultMap">
		SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE user_id = 1) and name = 'zhangsan'
	</select>
2022-12-15 21:21:22.791 DEBUG 20200 --- [alina-utility-2] org.apache.catalina.session.ManagerBase  : Start expire sessions StandardManager at 1671110482790 sessioncount 0
2022-12-15 21:21:23.365 DEBUG 20200 --- [alina-utility-2] org.apache.catalina.session.ManagerBase  : End expire sessions StandardManager processingTime 1 expired sessions: 0
2022-12-15 21:21:23.366  INFO 20200 --- [nio-8080-exec-4] ShardingSphere-SQL                       : Rule Type: sharding
2022-12-15 21:21:23.366  INFO 20200 --- [nio-8080-exec-4] ShardingSphere-SQL                       : Logic SQL: SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE user_id = 1) and name = 'zhangsan'
2022-12-15 21:21:23.366  INFO 20200 --- [nio-8080-exec-4] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=user_t, alias=Optional.absent()), Table(name=log_t, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=user_t), operator=EQUAL, compareOperator==, positionValueMap={0=1}, positionIndexMap={})]), AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=user_t), operator=EQUAL, compareOperator==, positionValueMap={0=1}, positionIndexMap={})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=user_t, quoteCharacter=NONE, schemaNameLength=0), TableToken(tableName=log_t, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE user_id = 1) and name = 'zhangsan')), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=user_t), operator=EQUAL, compareOperator==, positionValueMap={0=1}, positionIndexMap={})])])])
2022-12-15 21:21:23.366  INFO 20200 --- [nio-8080-exec-4] ShardingSphere-SQL                       : Actual SQL: wim-user ::: SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE user_id = 1) and name = 'zhangsan'
2022-12-15 21:21:23.376 DEBUG 20200 --- [nio-8080-exec-4] o.a.c.loader.WebappClassLoaderBase       :     findClass(com.mysql.jdbc.MySQLConnection)
2022-12-15 21:21:23.377 DEBUG 20200 --- [nio-8080-exec-4] o.a.c.loader.WebappClassLoaderBase       :     --> Returning ClassNotFoundException
2022-12-15 21:21:23.381  WARN 20200 --- [nio-8080-exec-4] c.a.druid.pool.DruidAbstractDataSource   : discard long time none received connection. , jdbcUrl : jdbc:mysql://127.0.0.1:3306/wim-user?serverTimezone=UTC&useSSL=false, version : 1.2.8, lastPacketReceivedIdleMillis : 706768
2022-12-15 21:21:23.381 DEBUG 20200 --- [nio-8080-exec-4] com.alibaba.druid.pool.DruidDataSource   : skip not validate connection.
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@676a183f]

此时sql正常运行。

查看一下官方文档:

子查询 子查询和外层查询同时指定分片键,且分片键的值保持一致时,由内核提供稳定支持。

例如: SELECT * FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 1;

用于分页的子查询,由内核提供稳定支持。

例如: SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT * FROM t_order) row_ WHERE rownum ?;

子查询和外层查询未同时指定分片键,或分片键的值不一致时,由 Federation 执行引擎提供支持。

例如:

SELECT * FROM (SELECT * FROM t_order) o; SELECT * FROM (SELECT * FROM t_order) o WHERE o.order_id = 1;

SELECT * FROM (SELECT * FROM t_order WHERE order_id = 1) o;

SELECT * FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 2;

4.1.1版本支持子查询

代码中有大量子查询 如果进行改造代价太大,本来想研究一下sharding-jdbc5对于子查询的支持,结果升级了一下版本 4.1.1 (之前版本是4.0.0.RC1)发现也是可以支持的。

升级依赖:

 <dependency>
           <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
</dependency>

相同的SQL

<select id="findUserByLog2" resultMap="BaseResultMap">
   SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE log_date > '2022-12-13') and name = 'zhangsan'
</select>

测试如下:

2022-12-18 11:49:25.007 DEBUG 12316 --- [alina-utility-2] org.apache.catalina.session.ManagerBase  : Start expire sessions StandardManager at 1671335365007 sessioncount 0
2022-12-18 11:49:25.007 DEBUG 12316 --- [alina-utility-2] org.apache.catalina.session.ManagerBase  : End expire sessions StandardManager processingTime 0 expired sessions: 0
2022-12-18 11:50:25.069 DEBUG 12316 --- [alina-utility-2] org.apache.catalina.session.ManagerBase  : Start expire sessions StandardManager at 1671335425069 sessioncount 0
2022-12-18 11:50:25.069 DEBUG 12316 --- [alina-utility-2] org.apache.catalina.session.ManagerBase  : End expire sessions StandardManager processingTime 0 expired sessions: 0
2022-12-18 11:50:50.377 DEBUG 12316 --- [o-8080-Acceptor] o.apache.tomcat.util.threads.LimitLatch  : Counting up[http-nio-8080-Acceptor] latch=1
2022-12-18 11:50:50.377 DEBUG 12316 --- [o-8080-Acceptor] o.apache.tomcat.util.threads.LimitLatch  : Counting up[http-nio-8080-Acceptor] latch=2
2022-12-18 11:50:50.379 DEBUG 12316 --- [nio-8080-exec-4] o.a.coyote.http11.Http11InputBuffer      : Before fill(): [true], parsingRequestLine: [true], parsingRequestLinePhase: [0], parsingRequestLineStart: [0], byteBuffer.position() [0]
2022-12-18 11:50:50.379 DEBUG 12316 --- [nio-8080-exec-4] o.a.tomcat.util.net.SocketWrapperBase    : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@44e0302f:org.apache.tomcat.util.net.NioChannel@4a7e9e7f:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:62717]], Read from buffer: [0]
2022-12-18 11:50:50.379 DEBUG 12316 --- [nio-8080-exec-4] org.apache.tomcat.util.net.NioEndpoint   : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@44e0302f:org.apache.tomcat.util.net.NioChannel@4a7e9e7f:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:62717]], Read direct from socket: [711]
2022-12-18 11:50:50.379 DEBUG 12316 --- [nio-8080-exec-4] o.a.coyote.http11.Http11InputBuffer      : Received [GET /mutildb/user/queryUserByLog2 HTTP/1.1
Host: 127.0.0.1:8080
Connection: keep-alive
Cache-Control: max-age=0
sec-ch-ua: "Not?A_Brand";v="8", "Chromium";v="108", "Google Chrome";v="108"
sec-ch-ua-mobile: ?0
sec-ch-ua-platform: "Windows"
Upgrade-Insecure-Requests: 1
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9
Sec-Fetch-Site: none
Sec-Fetch-Mode: navigate
Sec-Fetch-User: ?1
Sec-Fetch-Dest: document
Accept-Encoding: gzip, deflate, br
Accept-Language: zh-CN,zh;q=0.9

]
2022-12-18 11:50:50.379 DEBUG 12316 --- [nio-8080-exec-4] o.a.c.authenticator.AuthenticatorBase    : Security checking request GET /mutildb/user/queryUserByLog2
2022-12-18 11:50:50.379 DEBUG 12316 --- [nio-8080-exec-4] org.apache.catalina.realm.RealmBase      :   No applicable constraints defined
2022-12-18 11:50:50.379 DEBUG 12316 --- [nio-8080-exec-4] o.a.c.authenticator.AuthenticatorBase    : Not subject to any constraint
2022-12-18 11:50:50.380 DEBUG 12316 --- [nio-8080-exec-4] org.apache.tomcat.util.http.Parameters   : Set encoding to UTF-8
2022-12-18 11:50:50.380 DEBUG 12316 --- [nio-8080-exec-4] o.s.web.servlet.DispatcherServlet        : GET "/mutildb/user/queryUserByLog2", parameters={}
2022-12-18 11:50:50.380 DEBUG 12316 --- [nio-8080-exec-4] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to com.chen.rest.UserRest#queryUserByLog2()
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a27d6c3] was not registered for synchronization because synchronization is not active
2022-12-18 11:50:50.380 DEBUG 12316 --- [nio-8080-exec-4] o.s.jdbc.datasource.DataSourceUtils      : Fetching JDBC Connection from DataSource
JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@4e185b5f] will not be managed by Spring
==>  Preparing: SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE log_date > '2022-12-13') and name = 'zhangsan'
==> Parameters: 
2022-12-18 11:50:50.381  INFO 12316 --- [nio-8080-exec-4] ShardingSphere-SQL                       : Logic SQL: SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE log_date > '2022-12-13') and name = 'zhangsan'
2022-12-18 11:50:50.381  INFO 12316 --- [nio-8080-exec-4] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@2895ff16, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b1adcf8), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b1adcf8, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[ColumnProjection(owner=null, name=user_id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=age, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@4ebef4fc, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@3a5bb226, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@3615c491, containsSubquery=false)
2022-12-18 11:50:50.381  INFO 12316 --- [nio-8080-exec-4] ShardingSphere-SQL                       : Actual SQL: wim-user ::: SELECT * FROM user_t WHERE user_id in (SELECT user_id FROM log_t WHERE log_date > '2022-12-13') and name = 'zhangsan'
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a27d6c3]
2022-12-18 11:50:50.387  INFO 12316 --- [nio-8080-exec-4] com.chen.rest.UserRest                   : query user []
2022-12-18 11:50:50.388 DEBUG 12316 --- [nio-8080-exec-4] m.m.a.RequestResponseBodyMethodProcessor : Using 'text/html', given [text/html, application/xhtml+xml, image/avif, image/webp, image/apng, application/xml;q=0.9, application/signed-exchange;v=b3;q=0.9, */*;q=0.8] and supported [text/plain, */*, text/plain, */*, application/json, application/*+json, application/json, application/*+json]
2022-12-18 11:50:50.388 DEBUG 12316 --- [nio-8080-exec-4] m.m.a.RequestResponseBodyMethodProcessor : Writing ["cannot find user"]
2022-12-18 11:50:50.389 DEBUG 12316 --- [nio-8080-exec-4] o.s.web.servlet.DispatcherServlet        : Completed 200 OK
2022-12-18 11:50:50.389 DEBUG 12316 --- [nio-8080-exec-4] o.a.coyote.http11.Http11InputBuffer      : Before fill(): [true], parsingRequestLine: [true], parsingRequestLinePhase: [0], parsingRequestLineStart: [0], byteBuffer.position() [0]
2022-12-18 11:50:50.389 DEBUG 12316 --- [nio-8080-exec-4] o.a.tomcat.util.net.SocketWrapperBase    : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@44e0302f:org.apache.tomcat.util.net.NioChannel@4a7e9e7f:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:62717]], Read from buffer: [0]
2022-12-18 11:50:50.389 DEBUG 12316 --- [nio-8080-exec-4] org.apache.tomcat.util.net.NioEndpoint   : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@44e0302f:org.apache.tomcat.util.net.NioChannel@4a7e9e7f:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:62717]], Read direct from socket: [0]
2022-12-18 11:50:50.389 DEBUG 12316 --- [nio-8080-exec-4] o.a.coyote.http11.Http11InputBuffer      : Received []
2022-12-18 11:50:50.390 DEBUG 12316 --- [nio-8080-exec-4] o.apache.coyote.http11.Http11Processor   : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@44e0302f:org.apache.tomcat.util.net.NioChannel@4a7e9e7f:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:62717]], Status in: [OPEN_READ], State out: [OPEN]
2022-12-18 11:50:50.390 DEBUG 12316 --- [nio-8080-exec-4] org.apache.tomcat.util.net.NioEndpoint   : Registered read interest for [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@44e0302f:org.apache.tomcat.util.net.NioChannel@4a7e9e7f:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:62717]]
2022-12-18 11:51:25.109 DEBUG 12316 --- [alina-utility-2] org.apache.catalina.session.ManagerBase  : Start expire sessions StandardManager at 1671335485109 sessioncount 0
2022-12-18 11:51:25.109 DEBUG 12316 --- [alina-utility-2] org.apache.catalina.session.ManagerBase  : End expire sessions StandardManager processingTime 0 expired sessions: 0

可知不同的版本对于sql兼容性还是有很大区别的。

这是为啥 看一下源码

 public RouteContext decorate(RouteContext routeContext, ShardingSphereMetaData metaData, ShardingRule shardingRule, ConfigurationProperties properties) {
        SQLStatementContext sqlStatementContext = routeContext.getSqlStatementContext();
        List<Object> parameters = routeContext.getParameters();
        ShardingStatementValidatorFactory.newInstance(sqlStatementContext.getSqlStatement()).ifPresent((validator) -> {
            validator.validate(shardingRule, sqlStatementContext.getSqlStatement(), parameters);
        });
        ShardingConditions shardingConditions = this.getShardingConditions(parameters, sqlStatementContext, metaData.getSchema(), shardingRule);
        boolean needMergeShardingValues = this.isNeedMergeShardingValues(sqlStatementContext, shardingRule);
        if (sqlStatementContext.getSqlStatement() instanceof DMLStatement && needMergeShardingValues) {
            this.checkSubqueryShardingValues(sqlStatementContext, shardingRule, shardingConditions);
            this.mergeShardingConditions(shardingConditions);
        }

        ShardingRouteEngine shardingRouteEngine = ShardingRouteEngineFactory.newInstance(shardingRule, metaData, sqlStatementContext, shardingConditions, properties);
        RouteResult routeResult = shardingRouteEngine.route(shardingRule);
        if (needMergeShardingValues) {
            Preconditions.checkState(1 == routeResult.getRouteUnits().size(), "Must have one sharding with subquery.");
        }

        return new RouteContext(sqlStatementContext, parameters, routeResult);
    }

debug一下  可知 包含子查询判断条件containsSubquery为false  因此没有走入到之前校验逻辑

 SQLStatementContext对象是如何生成的

  private RouteContext createRouteContext(String sql, List<Object> parameters, boolean useCache) {
        SQLStatement sqlStatement = this.parserEngine.parse(sql, useCache);

        try {
            SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(this.metaData.getSchema(), sql, parameters, sqlStatement);
            return new RouteContext(sqlStatementContext, parameters, new RouteResult());
        } catch (IndexOutOfBoundsException var6) {
            return new RouteContext(new CommonSQLStatementContext(sqlStatement), parameters, new RouteResult());
        }
    }

一层一层找

public static SQLStatementContext newInstance(SchemaMetaData schemaMetaData, String sql, List<Object> parameters, SQLStatement sqlStatement) {
        if (sqlStatement instanceof DMLStatement) {
            return getDMLStatementContext(schemaMetaData, sql, parameters, (DMLStatement)sqlStatement);
        } else if (sqlStatement instanceof DDLStatement) {
            return getDDLStatementContext((DDLStatement)sqlStatement);
        } else if (sqlStatement instanceof DCLStatement) {
            return getDCLStatementContext((DCLStatement)sqlStatement);
        } else {
            return (SQLStatementContext)(sqlStatement instanceof DALStatement ? getDALStatementContext((DALStatement)sqlStatement) : new CommonSQLStatementContext(sqlStatement));
        }
    }
 private static SQLStatementContext getDMLStatementContext(SchemaMetaData schemaMetaData, String sql, List<Object> parameters, DMLStatement sqlStatement) {
        if (sqlStatement instanceof SelectStatement) {
            return new SelectStatementContext(schemaMetaData, sql, parameters, (SelectStatement)sqlStatement);
        } else if (sqlStatement instanceof UpdateStatement) {
            return new UpdateStatementContext((UpdateStatement)sqlStatement);
        } else if (sqlStatement instanceof DeleteStatement) {
            return new DeleteStatementContext((DeleteStatement)sqlStatement);
        } else if (sqlStatement instanceof InsertStatement) {
            return new InsertStatementContext(schemaMetaData, parameters, (InsertStatement)sqlStatement);
        } else {
            throw new UnsupportedOperationException(String.format("Unsupported SQL statement `%s`", sqlStatement.getClass().getSimpleName()));
        }
    }
 public SelectStatementContext(SchemaMetaData schemaMetaData, String sql, List<Object> parameters, SelectStatement sqlStatement) {
        super(sqlStatement);
        this.tablesContext = new TablesContext(sqlStatement.getSimpleTableSegments());
        this.groupByContext = (new GroupByContextEngine()).createGroupByContext(sqlStatement);
        this.orderByContext = (new OrderByContextEngine()).createOrderBy(sqlStatement, this.groupByContext);
        this.projectionsContext = (new ProjectionsContextEngine(schemaMetaData)).createProjectionsContext(sql, sqlStatement, this.groupByContext, this.orderByContext);
        this.paginationContext = (new PaginationContextEngine()).createPaginationContext(sqlStatement, this.projectionsContext, parameters);
        this.containsSubquery = this.containsSubquery();
    }
this.containsSubquery()方法 注意看
 private boolean containsSubquery() {
        return false;
    }

返回了常量false

4.0.0.RC1是如何判断的

核心方法

public SQLRouteResult route(String logicSQL, List<Object> parameters, SQLStatement sqlStatement) {
        Optional<GeneratedKey> generatedKey = sqlStatement instanceof InsertStatement ? GeneratedKey.getGenerateKey(this.shardingRule, parameters, (InsertStatement)sqlStatement) : Optional.absent();
        SQLRouteResult result = new SQLRouteResult(sqlStatement, (GeneratedKey)generatedKey.orNull());
        OptimizeResult optimizeResult = OptimizeEngineFactory.newInstance(this.shardingRule, sqlStatement, parameters, (GeneratedKey)generatedKey.orNull()).optimize();
        if (generatedKey.isPresent()) {
            this.setGeneratedKeys(result, (GeneratedKey)generatedKey.get());
        }

        boolean needMerge = false;
        if (sqlStatement instanceof SelectStatement) {
            needMerge = this.isNeedMergeShardingValues((SelectStatement)sqlStatement);
        }

        if (needMerge) {
            this.checkSubqueryShardingValues(sqlStatement, optimizeResult.getShardingConditions());
            this.mergeShardingValues(optimizeResult.getShardingConditions());
        }

        RoutingResult routingResult = RoutingEngineFactory.newInstance(this.shardingRule, this.shardingMetaData.getDataSource(), sqlStatement, optimizeResult).route();
        if (sqlStatement instanceof SelectStatement && null != ((SelectStatement)sqlStatement).getLimit() && !routingResult.isSingleRouting()) {
            result.setLimit(this.getProcessedLimit(parameters, (SelectStatement)sqlStatement));
        }

        if (needMerge) {
            Preconditions.checkState(1 == routingResult.getTableUnits().getTableUnits().size(), "Must have one sharding with subquery.");
        }

        result.setRoutingResult(routingResult);
        result.setOptimizeResult(optimizeResult);
        return result;
    }
isNeedMergeShardingValues
   private boolean isNeedMergeShardingValues(SelectStatement selectStatement) {
        return !selectStatement.getSubqueryConditions().isEmpty() && !this.shardingRule.getShardingLogicTableNames(selectStatement.getTables().getTableNames()).isEmpty();
    }
selectStatement.getSubqueryConditions

后续方法比较复杂  主要涉及到SQLStatement对象生成

public void fill(SubqueryConditionSegment sqlSegment, SQLStatement sqlStatement) {
        SelectStatement selectStatement = (SelectStatement)sqlStatement;
        OrConditionFiller orConditionFiller = new OrConditionFiller(this.shardingRule, this.shardingTableMetaData);
        Iterator var5 = sqlSegment.getOrConditions().iterator();

        while(var5.hasNext()) {
            OrConditionSegment each = (OrConditionSegment)var5.next();
            selectStatement.getSubqueryConditions().add(orConditionFiller.buildCondition(each, sqlStatement, this.shardingRule, this.shardingTableMetaData));
        }

    }

赋值是在解析sql时赋值的

Federation 执行引擎支持子查询

 Federation 执行引擎是啥

Apache ShardingSphere 5.0.0 内核优化及升级指南 - 知乎

Federation 执行引擎是 5.0.0 GA 版内核的又一大亮点功能,目标是支持那些在 4.1.1 GA 版中无法执行的分布式查询语句,例如:跨数据库实例的关联查询及子查询。Federation 执行引擎的出现,使得业务研发人员不必再关心 SQL 的使用范围,能够专注于业务功能开发,减少了业务层面的功能限制。

上图展示了 Federation 执行引擎的处理流程,总体上来看,仍然是遵循着 SQL 解析、SQL 路由、SQL 改写、SQL 执行这几个步骤,唯一的区别是 Federation 执行引擎额外引入了 SQL 优化,对分布式查询语句进行 RBO(Rule Based Optimizer) 和 CBO(Cost Based Optimizer) 优化,从而得到代价最小的执行计划。在 SQL 路由阶段,路由引擎会根据 SQL 语句是否跨多个数据库实例,来决定 SQL 是否通过 Federation 执行引擎来执行。

Federation 执行引擎目前处于快速开发中,仍然需要大量的优化,还是一个实验性的功能,因此默认是关闭的,如果想要体验 Federation 执行引擎,可以通过配置 sql-federation-enabled: true 来开启该功能。

Federation 执行引擎主要用来支持跨多个数据库实例的关联查询和子查询,以及部分内核不支持的聚合查询。下面我们通过具体的场景,来了解下 Federation 执行引擎支持的语句。

  • 跨库关联查询:当关联查询中的多个表分布在不同的数据库实例上时,由 Federation 执行引擎提供支持。

例如,在下面的数据分片配置中,t_order 和 t_order_item 表是多数据节点的分片表,并且未配置绑定表规则,t_user 和 t_user_role 则是分布在不同的数据库实例上的单表。

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_item_inline

由于跨多个数据库实例,下面这些常用的 SQL,会使用 Federation 执行引擎进行关联查询。

SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.order_id = 1;
SELECT * FROM t_order o INNER JOIN t_user u ON o.user_id = u.user_id WHERE o.user_id = 1;
SELECT * FROM t_order o LEFT JOIN t_user_role r ON o.user_id = r.user_id WHERE o.user_id = 1;
SELECT * FROM t_order_item i LEFT JOIN t_user u ON i.user_id = u.user_id WHERE i.user_id = 1;
SELECT * FROM t_order_item i RIGHT JOIN t_user_role r ON i.user_id = r.user_id WHERE i.user_id = 1;
SELECT * FROM t_user u RIGHT JOIN t_user_role r ON u.user_id = r.user_id WHERE u.user_id = 1;
  • 子查询:Apache ShardingSphere 的 Simple Push Down 引擎能够支持分片条件一致的子查询,以及路由到单个分片的子查询。对于子查询和外层查询未同时指定分片键,或分片键的值不一致的场景,需要由 Federation 执行引擎来提供支持。

下面展示了一些由 Federation 执行引擎支持的子查询场景:

SELECT * FROM (SELECT * FROM t_order) o;
SELECT * FROM (SELECT * FROM t_order) o WHERE o.order_id = 1;
SELECT * FROM (SELECT * FROM t_order WHERE order_id = 1) o;
SELECT * FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 2;
  • 聚合查询:对于 Apache ShardingSphere Simple Push Down 引擎暂不支持的一些聚合查询,我们也同样通过 Federation 执行引擎提供了支持。
SELECT user_id, SUM(order_id) FROM t_order GROUP BY user_id HAVING SUM(order_id) > 10;
SELECT (SELECT MAX(user_id) FROM t_order) a, order_id FROM t_order;
SELECT COUNT(DISTINCT user_id), SUM(order_id) FROM t_order;

Federation 执行引擎的出现,使得 Apache ShardingSphere 分布式查询能力得到明显增强,未来 Apache ShardingSphere 将持续优化,有效降低 Federation 执行引擎的内存占用,不断提升分布式查询的能力。这个后面研究一下

最后

以上就是光亮柜子为你收集整理的数据库拆分3--使用sharding-jdbc 支持子查询sql的全部内容,希望文章能够帮你解决数据库拆分3--使用sharding-jdbc 支持子查询sql所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错,欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
点赞(40)

评论列表共有 0 条评论

立即
投稿
返回
顶部