Apache ShardingSphere数据库中间件配置对未分片的表不做sql检测

应用场景：数据库中间件版本是ShardingSphere-4.1.1。（单库）数据库中有5张表，分别是a，b，c，d，e。现在仅对a，b，c进行分表，其余两张表d，e不分表。我的问题是：只包含d，e两张表的sql查询能不能不走shardingsphere的解析器，想直接避开无关表的解析。现在的情况是都走了解析器。

即在单库应用的情况下解决该问题，直接避开无关表的解析。因为很多sql的实际使用都是带子查询的，并且这些参与查询的数据表也是很多部分表的。

相关问题：https://gitee.com/Sharding-Sphere/sharding-sphere/issues/I1GVZ1

ShardingSphere中间件sql解析拦截器配置或改写，尽量减少数据库中间件带来的影响，此功能暂未实现，此处笔记记录下，解决后即在此处继续分享。

倒腾了几天，也咨询了他们的开发，通过ShardingSphere的控制暂时是实现不了这个需求的。

解决方案：

在项目中采取注解的方式，控制是否走ShardingSphere的数据源（托管数据源），即引入多数据源，实现多数据源管理。ShardingSphere虽然是Apache的顶级项目，但也不是对有所SQL兼容，使用“多数据源 + ShardingSphere”应该能跳过很多ShardingSphere的不足。

注：mysql数据库master/slave主从复制此处不作配置说明，Mysql 8.x关键配置参数

master库：

[mysqld]
server-id=1
log-bin=master-mysql-bin
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
default_authentication_plugin=mysql_native_password

slave库：

[mysqld]
server-id=2
log-bin=slave-mysql-bin
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
default_authentication_plugin=mysql_native_password

项目用到了ShardingSphere的数据分表和读写分离功能，在需要分表或读写分离的的service方法上加上@DS("sharding")，即可切换为ShardingSphere数据源。使用了开源的多数据源的组件：dynamic-datasource-spring-boot-starter，集成后，使用@DS注解就可以切换数据源，非常方便。pom依赖如下：

<!--多数据源切换-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.4.1</version>
</dependency>

application-datasource.yml 

## dynamic-datasource
spring:
  datasource:
    dynamic:
      primary: master #设置默认的数据源或者数据源组,默认值即为master
      strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      datasource:
        master:
          url: jdbc:mysql://127.0.0.1:33016/shardingds1?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf-8
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave:
          url: jdbc:mysql://127.0.0.1:33017/shardingds2?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf-8
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
## jpa print sql in console
  jpa:
    show-sql: true
    properties:
        hibernate:
          format_sql: true
## mybatis print sql in console
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

 关键代码配置：

/**
 * 分表数据源名称
 */
private static final String SHARDING_DATASOURCE_NAME = "sharding";

/**
 * 动态数据源配置项
 */
@Autowired
private DynamicDataSourceProperties properties;

@Lazy
@Resource(name = "shardingDataSource")
AbstractDataSourceAdapter shardingDataSource;

@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
    Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
    return new AbstractDataSourceProvider() {
        @Override
        public Map<String, DataSource> loadDataSources() {
            Map<String, DataSource> map = createDataSourceMap(datasourceMap);
            // 这里将shardingjdbc管理的数据源交给dynamic-datasource动态数据源去管理
            map.put(SHARDING_DATASOURCE_NAME, shardingDataSource);
            return map;
        }
    };
}

@Primary
@Bean
public DataSource dataSource() {
    DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
    dataSource.setPrimary(SHARDING_DATASOURCE_NAME);
    dataSource.setStrict(properties.getStrict());
    dataSource.setStrategy(properties.getStrategy());
    dataSource.setP6spy(properties.getP6spy());
    dataSource.setSeata(properties.getSeata());
    return dataSource;
}

 @DS("数据源名称key")注解使用：

@Autowired
private CommonJPAService jpaService;

@Autowired
private CommonJDBCTemplateService jdbcTemplateService;

@Override
@DS("master")
public List getResultByComplexSQL(String sql, int curPage, int rows) {
    return this.jpaService.findPageResultBySQL(sql, curPage, rows);
}

@Override
@DS("slave")
public List getSlaveResultByComplexSQL(String sql, int curPage, int rows) {
    return this.jpaService.findPageResultBySQL(sql, curPage, rows);
}

@Override
@DS("sharding")
public List getResultBySimpleSQL(String sql, int curPage, int rows) {
    return this.jpaService.findPageResultBySQL(sql, curPage, rows);
}

“Can not find owner from table”错误日志，用于调试跟踪参考：

java.lang.IllegalStateException: Can not find owner from table.
    at org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.find(ProjectionsContextEngine.java:197)
    at org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.findShorthandProjection(ProjectionsContextEngine.java:139)
    at org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.containsItemWithOwnerInShorthandProjections(ProjectionsContextEngine.java:135)
    at org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.containsItemInShorthandProjection(ProjectionsContextEngine.java:121)
    at org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.containsProjection(ProjectionsContextEngine.java:105)
    at org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.getDerivedOrderColumns(ProjectionsContextEngine.java:96)
    at org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.getDerivedOrderByColumns(ProjectionsContextEngine.java:88)
    at org.apache.shardingsphere.sql.parser.binder.segment.select.projection.engine.ProjectionsContextEngine.createProjectionsContext(ProjectionsContextEngine.java:71)
    at org.apache.shardingsphere.sql.parser.binder.statement.dml.SelectStatementContext.<init>(SelectStatementContext.java:99)
    at org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory.getDMLStatementContext(SQLStatementContextFactory.java:103)
    at org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:87)
    at org.apache.shardingsphere.underlying.route.DataNodeRouter.createRouteContext(DataNodeRouter.java:99)
    at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:89)
    at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
    at org.apache.shardingsphere.underlying.pluggble.prepare.SimpleQueryPrepareEngine.route(SimpleQueryPrepareEngine.java:54)
    at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
    at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
    at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingStatement.prepare(ShardingStatement.java:224)
    at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingStatement.executeQuery(ShardingStatement.java:87)
    at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:441)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:464)
    at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:497)