概述
最近在使用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所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复