1. 背景
最近参与一个项目，涉及到多数据源，然后再对某一数据源进行分表操作；分表按月份进行分表，查询也只能查询出近3个月内的数据（相当于需要动态改变shardingjdbc的分表配置）；
2. shardingjdbc分表实现
2.1 代码方式配置数据源
@Configuration
@Component
@MapperScan(basePackages = "com.xxx.db.mapper.mysql", sqlSessionTemplateRef = "mysqlSqlSessionTemplate")
public class MysqlDatabaseConfiguration {
@Bean(name = "mysqlDataSourceOriginal")
@ConfigurationProperties(prefix = "spring.datasource.mysql")
public DataSource dataSource() {
DataSource build = DataSourceBuilder.create().build();
return build;
}
@Bean(name = "mysqlDataSourceShardingJdbc")
@Primary
public DataSource shardingJdbcDataSource(@Qualifier("mysqlDataSourceOriginal") DataSource dataSource) {
ShardingJdbcConfig shardingJdbcConfig = new ShardingJdbcConfig();
return shardingJdbcConfig.dataSourceWarp(dataSource);
}
@Bean(name = "mysqlSqlSessionFactory")
@Primary
public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSourceShardingJdbc") DataSource dataSource) throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(resolver.getResources("classpath:mapper/mysql/**/*.xml"));
sessionFactory.setConfigLocation(resolver.getResource("classpath:mybatis-config.xml"));
sessionFactory.setTypeAliasesPackage("com.xx.xx.xx.xx.db.entity.mysql.*");
return sessionFactory.getObject();
}
@Bean(name = "mysqlDataSourceTransactionManager")
@Primary
public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("mysqlDataSourceShardingJdbc") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "mysqlSqlSessionTemplate")
@Primary
public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
public class ShardingJdbcConfig {
private static final String MYSQL_DATA_SOURCE = "mysqlDbMaster";
private static final String TEMP_TABLE = "d_table";
private static final String TEMP_ROW_KEY = "create_time";
public DataSource dataSourceWarp(DataSource dataSource) {
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put(MYSQL_DATA_SOURCE, dataSource);
String tempActualDataNodes = buildTableActualDataNodes();
TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration(TEMP_TABLE, tempActualDataNodes);
tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(TEMP_ROW_KEY,
new CurstomPreciseStrategy(), new CurstomRangeStrategy()));
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(tableRuleConfig);
// shardingRuleConfig.setDefaultDataSourceName(MYSQLDATASOURCE);
// shardingRuleConfig.setBindingTableGroups(Arrays.asList("t_table_temp"));
DataSource shardingDataSource = null;
try {
shardingDataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
} catch (SQLException e) {
e.printStackTrace();
}
return shardingDataSource;
}
private String buildTableActualDataNodes() {
String actualDataNodes = "mysqlDbMaster.d_table$->{2021..2099}0$->{1..9},mysqlDbMaster.d_table$->{2021..2099}1$->{0..2}";
return actualDataNodes;
}
}
2.2 自定义分表策略
// 区间
public class CurstomRangeStrategy implements RangeShardingAlgorithm<Date> {
private static final String TABLE_FORMAT = "_yyyyMM";
@Override
public Collection<String> doSharding(Collection<String> tableNames, RangeShardingValue<Date> rangeShardingValue) {
Range<Date> rangeDate = rangeShardingValue.getValueRange();
Date startDate = rangeDate.lowerEndpoint();
Date endDate = rangeDate.upperEndpoint();
List<String> yearsAndMonths = null;
try {
yearsAndMonths = DateUtil.getMonthBetween(startDate, endDate,TABLE_FORMAT,rangeShardingValue.getLogicTableName());
} catch (Exception e) {
e.printStackTrace();
}
return yearsAndMonths;
}
}
// 精确分片
public class CurstomPreciseStrategy implements PreciseShardingAlgorithm<Date> {
@Override
public String doSharding(final Collection<String> tableNames, final PreciseShardingValue<Date> shardingValue) {
Date date = shardingValue.getValue();
String logicTableName = shardingValue.getLogicTableName()+"_" + String.format("%tY", date) + String.format("%tm", date);
for (String each : tableNames) {
if (each.equals(logicTableName)) {
return each;
}
}
throw new UnsupportedOperationException();
}
}
RangeShardingAlgorith：使用的过程中发现，只有between and的时候才会走这个分片策略，而> < 并没有走此算法（希望得到大佬的指点！）；
3. shardingjdbc服务治理
3.1 理解shardingjdbc配置/注册中心
配置中心的作用及功能
配置中心：配置中心的作用是将shardingjdbc的配置统一化管理，可以起到修改配置自动分发到对应机器做动态修改，它的功能就是管理shardingjdbc分库分表的配置；
注册中心的作用及功能
注册中心：注册中心又是干嘛的呢？相对于配置中心管理配置数据，注册中心存放运行时的动态/临时状态数据！注意：是运行时的数据，比如：某数据源出现问题，你想在不停服的情况下进行数据源的切换。这些操作都是需要用到注册中心！
以上两份文档中对于配置中心和注册中心的作用描述的很清楚，但是，如何用，怎么用好像并没有说太多，接下来以zookeeper为例进行演示使用方式；
3.2 基于zookeeper的配置中心
需要额外再引入相关的依赖包：
版本我使用的是：4.0.0-RC1
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-orchestration</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!--若使用zookeeper, 请加入下面Maven坐标-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-orchestration-center-zookeeper-curator</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
3.2.1 代码配置方式
不同的shardingjdbc版本，API差距很大，本文使用的是 4.0.0-RC1
// 创建注册中心配置，type=zookeeper表示使用zookeeper做用注册和配置中心；
RegistryCenterConfiguration regConfig =new RegistryCenterConfiguration("zookeeper");
regConfig.setServerLists("zookeeper_ip:2181,zookeeper_ip:2182,zookeeper_ip:2183");
// sharding-sphere-orchestration：zookeeper根节点名称，可随意改；
regConfig.setNamespace("sharding-sphere-orchestration");
regConfig.setOperationTimeoutMilliseconds(50000);
// 配置治理，orchestration-sharding-data-source：固定；
OrchestrationConfiguration orchConfig =new OrchestrationConfiguration("orchestration-sharding-data-source", regConfig,false);
shardingDataSource = OrchestrationShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, p,
orchConfig);
3.2.2 yml配置方式
orchestration:
name: #治理实例名称
overwrite: #本地配置是否覆盖注册中心配置。如果可覆盖，每次启动都以本地配置为准
registry: #注册中心配置
type: #配置中心类型。如：zookeeper
serverLists: #连接注册中心服务器的列表。包括IP地址和端口号。多个地址用逗号分隔。如: host1:2181,host2:2181
namespace: #注册中心的命名空间
digest: #连接注册中心的权限令牌。缺省为不需要权限验证
operationTimeoutMilliseconds: #操作超时的毫秒数，默认500毫秒
maxRetries: #连接失败后的最大重试次数，默认3次
retryIntervalMilliseconds: #重试间隔毫秒数，默认500毫秒
timeToLiveSeconds: #临时节点存活秒数，默认60秒
配置好zookeeper之后，启动服务，会发现在zookeeper中会创建出相应节点以及具体的配置值：
shardingjdbc会监听这些value的update事件，如果更新了，则会动态更新shardingjdbc的配置到服务中；
查了shardingjdbc官网，上面也说支持了Apollo，但orchestration模块里面并没有一些其它的配置/注册中心；
里面有orchestration-zookeeper，但没有其它的实现，所以我觉得如果有其它的配置中心，则需要我们通过SPI机制自己去实现；
3.2.3 zk做为shardingjdbc注册中心原理
- 找到sharding-orchestration-center-zookeeper-curator.jar包，里面有一项SPI配置：
- 查看org.apache.shardingsphere.orchestration.reg.api.RegistryCenter文件内容：
org.apache.shardingsphere.orchestration.reg.zookeeper.curator.CuratorZookeeperRegistryCenter
里面配置了个CuratorZookeeperRegistryCenter的全路径；
- 查看CuratorZookeeperRegistryCenter的类内容：
// shardingjdbc使用zookeeper作为注册/配置中心
public final class CuratorZookeeperRegistryCenter implements RegistryCenter {
// 这个类会监控ZK的一个路径，路径下所有的节点变动，数据变动都会被响应
private final Map<String, TreeCache> caches = new HashMap();
private CuratorFramework client;
private Properties properties = new Properties();
public CuratorZookeeperRegistryCenter() {
}
// 注册中心/配置中心初始化方法，此类实例化时会先调用此方法，它这里对前端传过来的一些zookeeper配置信息做CuratorFramework实例化；
// sharding-orchestration-reg-zookeeper-curator 使用Curator作为zookeeper客户端；
@Autowired
public void init(RegistryCenterConfiguration config) {
this.client = this.buildCuratorClient(config);
this.initCuratorClient(config);
}
private CuratorFramework buildCuratorClient(RegistryCenterConfiguration config) {
Builder builder = CuratorFrameworkFactory.builder().connectString(config.getServerLists()).retryPolicy(new ExponentialBackoffRetry(config.getRetryIntervalMilliseconds(), config.getMaxRetries(), config.getRetryIntervalMilliseconds() * config.getMaxRetries())).namespace(config.getNamespace());
if (0 != config.getTimeToLiveSeconds()) {
builder.sessionTimeoutMs(config.getTimeToLiveSeconds() * 1000);
}
if (0 != config.getOperationTimeoutMilliseconds()) {
builder.connectionTimeoutMs(config.getOperationTimeoutMilliseconds());
}
if (!Strings.isNullOrEmpty(config.getDigest())) {
builder.authorization("digest", config.getDigest().getBytes(Charsets.UTF_8)).aclProvider(new ACLProvider() {
public List<ACL> getDefaultAcl() {
return Ids.CREATOR_ALL_ACL;
}
public List<ACL> getAclForPath(String path) {
return Ids.CREATOR_ALL_ACL;
}
});
}
return builder.build();
}
private void initCuratorClient(RegistryCenterConfiguration config) {
this.client.start();
try {
if (!this.client.blockUntilConnected(config.getRetryIntervalMilliseconds() * config.getMaxRetries(), TimeUnit.MILLISECONDS)) {
this.client.close();
throw new OperationTimeoutException();
}
} catch (OperationTimeoutException | InterruptedException var3) {
CuratorZookeeperExceptionHandler.handleException(var3);
}
}
// 以上3个方法就是为了初始化CuratorFramework client实例；
// =========================================================
// 从注册中心通过key查询对应的value
// 它是从本地先获取对应的节点，如果没有，再去zk中获取；
public String get(String key) {
TreeCache cache = this.findTreeCache(key);
if (null == cache) {
return this.getDirectly(key);
} else {
ChildData resultInCache = cache.getCurrentData(key);
if (null != resultInCache) {
return null == resultInCache.getData() ? null : new String(resultInCache.getData(), Charsets.UTF_8);
} else {
return this.getDirectly(key);
}
}
}
private TreeCache findTreeCache(String key) {
Iterator var2 = this.caches.entrySet().iterator();
Entry entry;
do {
if (!var2.hasNext()) {
return null;
}
entry = (Entry)var2.next();
} while(!key.startsWith((String)entry.getKey()));
return (TreeCache)entry.getValue();
}
// 从注册中心通过key查询对应的value，直接从zk中获取；
public String getDirectly(String key) {
try {
return new String((byte[])this.client.getData().forPath(key), Charsets.UTF_8);
} catch (Exception var3) {
CuratorZookeeperExceptionHandler.handleException(var3);
return null;
}
}
// 判断节点是否存在
public boolean isExisted(String key) {
try {
return null != this.client.checkExists().forPath(key);
} catch (Exception var3) {
CuratorZookeeperExceptionHandler.handleException(var3);
return false;
}
}
// 获取子节点
public List<String> getChildrenKeys(String key) {
try {
List<String> result = (List)this.client.getChildren().forPath(key);
Collections.sort(result, new Comparator<String>() {
public int compare(String o1, String o2) {
return o2.compareTo(o1);
}
});
return result;
} catch (Exception var3) {
CuratorZookeeperExceptionHandler.handleException(var3);
return Collections.emptyList();
}
}
// 创建节点
public void persist(String key, String value) {
try {
if (!this.isExisted(key)) {
((ACLBackgroundPathAndBytesable)this.client.create().creatingParentsIfNeeded().withMode(CreateMode.PERSISTENT)).forPath(key, value.getBytes(Charsets.UTF_8));
} else {
this.update(key, value);
}
} catch (Exception var4) {
CuratorZookeeperExceptionHandler.handleException(var4);
}
}
// 更新节点
public void update(String key, String value) {
try {
((CuratorTransactionBridge)((CuratorTransactionBridge)this.client.inTransaction().check().forPath(key)).and().setData().forPath(key, value.getBytes(Charsets.UTF_8))).and().commit();
} catch (Exception var4) {
CuratorZookeeperExceptionHandler.handleException(var4);
}
}
// 创建临时节点
public void persistEphemeral(String key, String value) {
try {
if (this.isExisted(key)) {
this.client.delete().deletingChildrenIfNeeded().forPath(key);
}
((ACLBackgroundPathAndBytesable)this.client.create().creatingParentsIfNeeded().withMode(CreateMode.EPHEMERAL)).forPath(key, value.getBytes(Charsets.UTF_8));
} catch (Exception var4) {
CuratorZookeeperExceptionHandler.handleException(var4);
}
}
// 监听，这个方法重要；
public void watch(String key, final DataChangedEventListener dataChangedEventListener) {
String path = key + "/";
if (!this.caches.containsKey(path)) {
this.addCacheData(key);
}
TreeCache cache = (TreeCache)this.caches.get(path);
// 为路径添加zookeeper监听；
cache.getListenable().addListener(new TreeCacheListener() {
public void childEvent(CuratorFramework client, TreeCacheEvent event) throws UnsupportedEncodingException {
ChildData data = event.getData();
if (null != data && null != data.getPath()) {
// 获取事件类型
ChangedType changedType = CuratorZookeeperRegistryCenter.this.getChangedType(event);
if (ChangedType.IGNORED != changedType) {
// UPDATED , DELETED 进行更新；
dataChangedEventListener.onChange(new DataChangedEvent(data.getPath(), null == data.getData() ? null : new String(data.getData(), "UTF-8"), changedType));
}
}
}
});
}
private ChangedType getChangedType(TreeCacheEvent event) {
switch(event.getType()) {
case NODE_UPDATED:
return ChangedType.UPDATED;
case NODE_REMOVED:
return ChangedType.DELETED;
default:
return ChangedType.IGNORED;
}
}
private void addCacheData(String cachePath) {
TreeCache cache = new TreeCache(this.client, cachePath);
try {
cache.start();
} catch (Exception var4) {
CuratorZookeeperExceptionHandler.handleException(var4);
}
this.caches.put(cachePath + "/", cache);
}
// 关闭时，进行释放资源；
public void close() {
Iterator var1 = this.caches.entrySet().iterator();
while(var1.hasNext()) {
Entry<String, TreeCache> each = (Entry)var1.next();
((TreeCache)each.getValue()).close();
}
this.waitForCacheClose();
CloseableUtils.closeQuietly(this.client);
}
private void waitForCacheClose() {
try {
Thread.sleep(500L);
} catch (InterruptedException var2) {
Thread.currentThread().interrupt();
}
}
// type=zookeeper，所以你在创建RegistryCenterConfiguration时，指定的是zookeeper的值；
public Properties getProperties() {
return this.properties;
}
public void setProperties(Properties properties) {
this.properties = properties;
}
}
读完CuratorZookeeperRegistryCenter之后，是不是迫不及待的想自己实现一个shardingjdbc的注册中心功能了；
CuratorZookeeperRegistryCenter思路：通过zookeeper节点变更通知机制，当节点内容变更时，发出通知，shardingjdbc提供dataChangedEventListener.onChange进行更新相关配置；
zookeeper里面保存的shardingjdbc内容是yml格式的，可以读出相关的配置进行修改后，就会触发通知；
3.2.4 代码配置zk为shardingjdbc注册中心
RegistryCenterConfiguration regConfig =new RegistryCenterConfiguration("zookeeper");
regConfig.setServerLists("zookeeper_ip:2181,zookeeper_ip:2182,zookeeper_ip:2183");
regConfig.setNamespace("sharding-sphere-orchestration"); // namespace zk根节点；
regConfig.setOperationTimeoutMilliseconds(50000);
OrchestrationConfiguration orchConfig =new OrchestrationConfiguration("orchestration-sharding-data-source", regConfig,false);
shardingDataSource = OrchestrationShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, p,
orchConfig);
3.3 基于SPI自定义配置中心
目前，ShardingSphere内部支持Zookeeper和etcd这种常用的配置中心/注册中心，如果你需要替换成其它的配置中心，则需要通过shardingSphere进行自定义；这里我以数据为例进行演示自定义shardingjdbc的注册中心；代码仅供个人学习，bug有很多，只是为了说明配置流程！
3.3.1 配置shardingjdbc SPI
org.apache.shardingsphere.orchestration.reg.api.RegistryCenter
com.xxx.xx.xx.xx.db.config.CustromShardJdbcRegistryCenter
3.3.2 重新配置shardingdatasource
修改datasource配置
// 实现基于DB的注册中心
Properties properties = new Properties();
properties.setProperty("jdbc-url","");
properties.setProperty("username","");
properties.setProperty("password","");
properties.setProperty("driverClassName","");
RegistryCenterConfiguration dbRegConfig =new RegistryCenterConfiguration("db",properties);
// overwrite:启动时是否从远程拉取配置
OrchestrationConfiguration orchConfig =new OrchestrationConfiguration("orchestration-sharding-data-source", dbRegConfig,true);
shardingDataSource = OrchestrationShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, p,
orchConfig);
在配置datasource时，我们将数据库的信息通过properties传递到注册中心；注意：type=db，这里是我们自己定义的；
3.3.3 创建数据库注册表
| id | key | value |
|---|
3.3.4 CustromShardJdbcRegistryCenter
代码有很多BUG，这里只是为了说明定义sharding注册中心的流程；
public class CustromShardJdbcRegistryCenter implements RegistryCenter {
private Properties properties;
private DBTemplate dbTemplate;
private RegistryCenterConfiguration config;
private Map<String, String> localCaches = new HashMap<>();
private Map<String, DataChangedEventListener> listeners = new HashMap<>();
private Timer timer = new Timer();
private boolean timerStartFlag = false;
@Override
public void init(RegistryCenterConfiguration config) {
this.config = config;
this.properties = config.getProperties();
// 获取数据库信息;
String url = properties.getProperty("jdbc-url", "");
String username = properties.getProperty("username", "");
String password = properties.getProperty("password", "");
String driverClassName = properties.getProperty("driverClassName", "");
// 完成初始化
dbTemplate = new DBTemplate(url, username, password, driverClassName);
}
@Override
public String get(String key) {
if (localCaches.containsKey(key)) {
return localCaches.get(key);
}
return dbTemplate.getByKey(key);
}
@Override
public String getDirectly(String key) {
return dbTemplate.getByKey(key);
}
@Override
public boolean isExisted(String key) {
return dbTemplate.existed(key);
}
@Override
public List<String> getChildrenKeys(String key) {
// 模糊查询即可；
return dbTemplate.getChildrenKeys(key);
}
@Override
public void persist(String key, String value) {
localCaches.put(key, value);
dbTemplate.save(key);
}
@Override
public void update(String key, String value) {
localCaches.put(key, value);
dbTemplate.update(key);
}
@Override
public void persistEphemeral(String key, String value) {
localCaches.put(key, value);
dbTemplate.update(key);
}
@Override
public void watch(String key, DataChangedEventListener dataChangedEventListener) {
if (null != dataChangedEventListener) {
// zk的方式这里是直接进行监听节点就行了，而用数据库的话，没有办法进行监控数据库变化 ，直接把listener缓存起来；
listeners.put(key, dataChangedEventListener);
// 开启一个线程，定时监控 localCaches与数据库中的配置信息是否一致，如果不一致，则更新对应的Listener
if (!timerStartFlag) {
timer.schedule(new TimerTask() {
public void run() {
// key：配置名称，value：新值；
Map<String, String> changeRuleMap = dbTemplate.contrast(localCaches);
for (Map.Entry<String, String> entry : changeRuleMap.entrySet()) {
listeners.get(entry.getKey())
.onChange(new DataChangedEvent(entry.getKey(), entry.getValue(),
DataChangedEvent.ChangedType.UPDATED));
localCaches.put(entry.getKey(), entry.getValue());
}
}
}, 20000, 1000);
timerStartFlag = true;
}
}
}
@Override
public void close() {
if (null != dbTemplate) {
try {
dbTemplate.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public String getType() {
// 和shardingjdbc配置中的type保持一致；
return "db";
}
@Override
public Properties getProperties() {
return properties;
}
@Override
public void setProperties(Properties properties) {
this.properties = properties;
}
private Connection initDbDataSource(String url, String username, String password, String driverClassName) {
if (null != conn) {
return null;
}
try {
Class.forName(driverClassName);
} catch (Exception ex) {
throw new RuntimeException(ex);
}
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException ex) {
if (null != conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
throw new RuntimeException(ex);
}
return conn;
}
}
扫一扫
587
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
