状态：原创
应用场景：（单库）数据库中有5张表，分别是a，b，c，d，e。现在仅对a，b，c进行分表，其余两张表d，e不分表。我的问题是：只包含d，e两张表的sql查询能不能不走shardingsphere的解析器，想直接避开无关表的解析。现在的情况是都走了解析器。
即在单库应用的情况下解决该问题，直接避开无关表的解析。因为很多sql的实际使用都是带子查询的，并且这些参与查询的数据表也是很多部分表的。
ShardingSphere中间件sql解析拦截器配置或改写，尽量减少数据库中间件带来的影响，此功能暂未实现，此处笔记记录下，解决后即在此处继续分享。
解决方案：
在项目中采取注解的方式，控制是否走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依赖如下：
com.baomidou dynamic-datasource-spring-boot-starter 3.4.1application-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.(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)