公司原先使用的数据库中间件是mycat,但是mycat由于很早就开始不维护了,所以我们再使用过程中出现了很多问题都无法的到解决,如果各位同学有兴趣可以再压测时观察下mycat的堆栈活动,ygc是非常频繁的,并且很不稳定,当时考虑切换到Sharding-Proxy上面去,同样是数据库中间件这种方案我们内部认为是改动最小的。
可是事实证明我们错了,Sharding-Proxy由于也是刚开源不久的东西,其实也存在不少BUG,虽然社区活跃但是官方文档实在太不全了,导致我们遇到每一个问题都需要花很大的精力去排查、定位、解决,所以我们最终还是放弃了Sharding-Proxy,准备使用Shardingjdbc,但是作为一款客户端直连的框架性能一定是最好的,只是业务可感知的改动量肯定也是最明显的,例如是分片规则的定制化、多数据源切换(并非多分片的数据源,可能包含不需要shardingjdbc管理的数据源)、mycat原先的全局表、复杂子查询的支持等等...
在这样一个前提下,我们决定深度定制下shardingjdbc,让业务无感知的直接从mycat切换到shardingjdbc上面
启动初始化流程如下,由于YML配置无法满足我们的需求,我们采用了Java代码注入的方式:
之后源码贴出来大家会发现我们apollo的配置也是非常简洁,完全看不出和shardingjdbc有任何关系,这么做也是为了做到业务最小感知,当时也做了backup的方案,按项目一个个切换shardingjdbc,如果出问题那么久通过apollo一个最简单的开关完成回滚,整个项目加载流程如下:
下面贴出来整个的核心代码吧,全部源码实在是有点多无法一一列举了,先看下我们封装后的Apollo数据源配置吧:
#数据源,可通配,如果不存在10号分库启动时会自动过滤,所以前期配置可以放大一点,避免每次加一个分库都需要去修改依次apollo
jdbc:mysql://xxxx/db_wh_shard_${01-10}
#backup 方案,是否开启接入sharding
com.xxx.sharding.jdbc.datasource.isopen = false
#是否过滤小于当前日期的日志表不加入shardingjbdc管理,节省内存,优化启动时间
com.xxx.sharding.jdbc.table.log = true
初始化启动核心类:
/**
* <p>Title:主数据源交给spring管理</p>
* <p>Description:infoSource</p>
*
* @return javax.sql.DataSource
* @throws
* @author QIQI
* @params [shardingDruidProperties]
* @date 2020/04/20 16:02
*/
@Bean(name = "dataSourceForSpring")
@Conditional(value = DataSardingConditional.class)
public LinkedHashMap<String, DataSource> dataSourceForSpring() {
shardingJDBCConfigEngine.getShardingJdbcDatasourceMap();
LinkedHashMap<String, DataSource> linkedHashMap = new LinkedHashMap<>();
linkedHashMap.putAll( shardingJDBCConfigEngine.SHARDING_JDBC_DATASOURCE_MAP() );
linkedHashMap.entrySet().removeIf( entry -> entry.getKey().contains( ShardingConstant.SHARD_SOURCE ) );
try {
//添加全局表数据源,兼容原先@DataSource 注解框架
linkedHashMap.forEach( (key, val) -> DynamicDataSourceContextHolder.dataSourceIds.add( key ) );
return linkedHashMap;
} catch (Exception e) {
throw new WmsShardingException( new ExceptionMessageImpl( ErrorMessage.DATASOURCE_ERROR.getCode() ) );
}
}
@Bean("dynamicDataSource")
@Conditional(value = DataSardingConditional.class)
public DynamicRoutingDataSource shardingDataSource(@Qualifier("dataSourceForSpring") LinkedHashMap<String, DataSource> dataSourceForSpring) throws SQLException {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
DataSourceModel dataSourceModel = new DataSourceModel( shardingJDBCConfigEngine.SHARDING_JDBC_DATASOURCE_MAP(), false );
DataSourceChain.initHandler().handleWork( dataSourceModel );
databaseTableChain.initHandler().getShardingRuleConfiguration( WmsShardSourceEngine.getShardingDataSource() );
ShardingRuleConfiguration ruleConfiguration = DatabaseTableChain.shardingRuleConfiguration;
DatabaseTableChain.shardingRuleConfiguration = null; //释放全局变量内存
//shardingDataSource create success
DataSource shardingDataSource = ShardingDataSourceFactory.createDataSource( WmsShardSourceEngine.getShardingDataSource(), ruleConfiguration, new Properties() );
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put( ShardingConstant.SHARD_SOURCE, shardingDataSource );
targetDataSources.putAll( dataSourceForSpring );
targetDataSources.put( ShardingConstant.DEFAULT_SOURCE, targetDataSources.get( ShardingConstant.SHARD_SOURCE ) );
WmsShardSourceEngine.getShardingDataSource().putAll( dataSourceForSpring );
dataSource.setDataSourceMap( targetDataSources );
log.info( ShardingConstant.getWmsAppName() + "Shardingjdbc init And DataSourceMap init is Success " );
return dataSource;
}
@Bean
@ConfigurationProperties(prefix = "mybatis")
@Conditional(value = DataSardingConditional.class)
public MybatisSqlSessionFactoryBean sqlSessionFactory(@Qualifier("dynamicDataSource") DynamicRoutingDataSource dynamicRoutingDataSource) throws Exception {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource( dynamicRoutingDataSource );
bean.setMapperLocations( new PathMatchingResourcePatternResolver().getResources( mybatisPlusMapper ) );
GlobalConfig globalConfig = new GlobalConfig();
globalConfig.setSqlInjector( new WmsSqlInjector() );
MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
mybatisConfiguration.setCacheEnabled( cache );
mybatisConfiguration.setLocalCacheScope( localCacheScope );
bean.setConfiguration( mybatisConfiguration );
bean.setGlobalConfig( globalConfig );
bean.setPlugins( new PaginationInterceptor(),new OptimisticLockerInterceptor() );
return bean;
}
@Bean(name = "sqlSessionTemplate")
@Conditional(value = DataSardingConditional.class)
@Primary
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate( sqlSessionFactory );
}
@Bean
@Conditional(value = DataSardingConditional.class)
public DataSourceTransactionManager transactitonManager(@Qualifier("dynamicDataSource") DynamicRoutingDataSource dynamicRoutingDataSource) {
return new DataSourceTransactionManager( dynamicRoutingDataSource );
}
Shardingjdbc 常用类说明
期望实现 | 类 | 代码摘要 |
如何添加分片配置信息 | StandardShardingStrategyConfiguration | 代码摘要V1.1 |
单分片查询下,如何支持复杂SQL,包含子查询(其实shardingjdbc原生支持的,但是默认是按照多分片解析的,所以你会发现你复杂的子查询会报错) |
| 代码摘要V1.2 |
shardingjdbc中怎么设置广播表 | setBroadcastTables | 代码摘要V1.3 |
代码摘要V1.1:
/**
* <p>Title:简化单库下面所有表应用的分片规则配置</p>
* <p>Description:目前单库所有表规则都是一致的,就不需要循环所有配置了</p>
*
* @return org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration
* @throws
* @author QIQI
* @params [map]
* @date 2020/04/14 14:15
*/
public void getShardingRuleConfiguration(final Map<String, DataSource> map) throws SQLException {
List<TableRuleConfiguration> configurationList = new ArrayList<>();
StandardShardingStrategyConfiguration standardShardingStrategyConfiguration =
new StandardShardingStrategyConfiguration( ShardingConstant.OUID, new WmsShardPreciseShardingAlgorithm() );
//表结构信息,优化启动时间,只取出第一个符合条件的数据源即可
Optional<Map.Entry<String, DataSource>> sourceEntry = map.entrySet().stream().
filter( entry -> entry.getKey().contains( ShardingConstant.SHARD_SOURCE ) ).findFirst();
final List<TableRouteModel> tableShardRouteModelList = new ArrayList<>();
sourceEntry.ifPresent( entry -> {
DruidDataSource druidDataSource = (DruidDataSource) sourceEntry.get().getValue();
try {
tableShardRouteModelList.addAll( tableRouteService.getShardTablesList( druidDataSource,
WmsShardCustomizationEngine.getNotInSql( shardingjdbcGlobalProperties.getGlobal() ) ) );
WmsShardCustomizationEngine.logTableReduce( tableShardRouteModelList );
//执行分片表逻辑添加
tableShardRouteModelList.forEach( v -> {
TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration( v.getTableName() );
tableRuleConfiguration.setDatabaseShardingStrategyConfig( standardShardingStrategyConfiguration );
configurationList.add( tableRuleConfiguration );
} );
DatabaseTableChain.shardingRuleConfiguration.setTableRuleConfigs( configurationList );
log.info( ShardingConstant.getWmsAppName() + "ShardDatabaseTablesHandler ShardDatabaseTablesHandler init is success" );
} catch (SQLException e) {
log.warn( "ShardDatabaseTablesHandler getShardingRuleConfiguration is error", e );
}
} );
next( map );
}
代码摘要V1.2:
public static List<String> bindWmsShardingTable(List<String> logicTableNameList){
List<String> tableRules = ImmutableList.of(
String.join( ",",logicTableNameList )
);
return tableRules;
}
//单片分组绑定
DatabaseTableChain.shardingRuleConfiguration.setBindingTableGroups
( WmsShardCustomizationEngine.bindWmsShardingTable( logicTables ) );
代码摘要V1.3:
DatabaseTableChain.shardingRuleConfiguration.setBroadcastTables
( WmsShardCustomizationEngine.broadcastTables( shardingjdbcGlobalProperties.getGlobal() ) );