Sharding jdbc 分库分表
一共有三个库,m(mian),ds0,ds1,其中m库是不分库分表的,ds0,ds1是走分库分表的,每个库三张表,也就是2库6表
数据库结构
├─192.168.43.200
│ ├─dsm
│ │ └─leader
│ ├─ds0
│ │ └─order_000
│ │ └─order_001
│ │ └─order_002
│ ├─ds1
│ │ └─order_000
│ │ └─order_001
│ │ └─order_002
│ │ │
依赖
pom.xml
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.0.0.M3</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
如果启动有冲突,配置文件加这个
spring.main.allow-bean-definition-overriding=true
行表达式分片策略
对应InlineShardingStrategy。使用Groovy的表达式,提供对SQL语句中的=和IN的分片操作支持,只支持单分片键。对于简单的分片算法,可以通过简单的配置使用,从而避免繁琐的Java代码开发,如: t_order_000$->{order_id % 3}
表示t_order_000
表根据order_id
模3
,而分成3张表
,表名称为t_order_0000
到t_order_0002
。
只需要编写配置文件即可
###########################Sharding Jdbc配置###########################
sharding.jdbc.datasource.names=m,ds0,ds1
sharding.jdbc.datasource.m.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.m.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.m.url=jdbc:mysql://192.168.79.131:3306/grp_leader
sharding.jdbc.datasource.m.username=root
sharding.jdbc.datasource.m.password=123456
sharding.jdbc.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://192.168.79.131:3306/grp_manage_01
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=123456
sharding.jdbc.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://192.168.79.131:3306/grp_manage_02
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=123456
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_000$->{0..1}
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_000$->{order_id % 3}
sharding.jdbc.config.sharding.tables.leader.actual-data-nodes=m.leader
sharding.jdbc.config.sharding.tables.leader.table-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.tables.leader.table-strategy.inline.algorithm-expression=leader
sharding.jdbc.config.sharding.props.sql.show=true
标准分片策略
对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键,提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的,用于处理=和IN的分片。RangeShardingAlgorithm是可选的,用于处理BETWEEN AND, >, <, >=, <=分片,如果不配置RangeShardingAlgorithm,SQL中的BETWEEN AND将按照全库路由处理。
PreciseShardingAlgorithm
自定义精准分库分表策略
这里分库分表都是取mod运算 和上面的行表达式分片策略一样(分库分表可以设置两个策略)
/**
* sharding jdbc 精准 `分库` 策略
* */
@Slf4j
public class PreciseShardingDatabaseAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* @param collection 拆分的所有库名 比如 [ds0,ds1]
* @param preciseShardingValue database-strategy.standard.sharding-column
* */
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
log.info("[Database PreciseShardingAlgorithm] collection:{} ,preciseShardingValue: {}",
JSON.toJSONString(collection),JSON.toJSONString(preciseShardingValue));
for (String name : collection) {
// 分库的规则
if (name.endsWith(preciseShardingValue.getValue() % collection.size() + "")) {
log.info("[Database PreciseShardingAlgorithm ] return database name:{}",name);
return name;
}
}
throw new UnsupportedOperationException();
}
}
/**
* sharding jdbc 精准`分表`策略
* */
@Slf4j
public class PreciseShardingTableAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* @param collection 拆分的所有表 比如 [t_order0000,t_order0001,t_order0002]
* @param preciseShardingValue table-strategy.standard.sharding-column
* */
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
log.info("[Table PreciseShardingAlgorithm ] collection:{} ,preciseShardingValue: {}",
JSON.toJSONString(collection),JSON.toJSONString(preciseShardingValue));
for (String name : collection) {
// 分表的规则
if (name.endsWith(preciseShardingValue.getValue() % collection.size() + "")) {
log.info("[Table PreciseShardingAlgorithm ] return table name:{}",name);
return name;
}
}
throw new UnsupportedOperationException();
}
}
配置文件
###########################Sharding Jdbc配置###########################
sharding.jdbc.datasource.names=m,ds0,ds1
sharding.jdbc.datasource.m.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.m.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.m.url=jdbc:mysql://192.168.79.131:3306/grp_leader
sharding.jdbc.datasource.m.username=root
sharding.jdbc.datasource.m.password=123456
sharding.jdbc.datasource.m.initialSize=${initialSize}
sharding.jdbc.datasource.m.minIdle=${minIdle}
sharding.jdbc.datasource.m.maxActive=${maxActive}
sharding.jdbc.datasource.m.maxWait=${maxWait}
sharding.jdbc.datasource.m.validationQuery=SELECT 1 FROM DUAL
sharding.jdbc.datasource.m.timeBetweenEvictionRunsMillis=${timeBetweenEvictionRunsMillis}
sharding.jdbc.datasource.m.minEvictableIdleTimeMillis=${minEvictableIdleTimeMillis}
sharding.jdbc.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://192.168.79.131:3306/grp_manage_01
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=123456
sharding.jdbc.datasource.ds0.initialSize=${initialSize}
sharding.jdbc.datasource.ds0.minIdle=${minIdle}
sharding.jdbc.datasource.ds0.maxActive=${maxActive}
sharding.jdbc.datasource.ds0.maxWait=${maxWait}
sharding.jdbc.datasource.ds0.validationQuery=SELECT 1 FROM DUAL
sharding.jdbc.datasource.ds0.timeBetweenEvictionRunsMillis=${timeBetweenEvictionRunsMillis}
sharding.jdbc.datasource.ds0.minEvictableIdleTimeMillis=${minEvictableIdleTimeMillis}
sharding.jdbc.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://192.168.79.131:3306/grp_manage_02
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=123456
sharding.jdbc.datasource.ds1.initialSize=${initialSize}
sharding.jdbc.datasource.ds1.minIdle=${minIdle}
sharding.jdbc.datasource.ds1.maxActive=${maxActive}
sharding.jdbc.datasource.ds1.maxWait=${maxWait}
sharding.jdbc.datasource.ds1.validationQuery=SELECT 1 FROM DUAL
sharding.jdbc.datasource.ds1.timeBetweenEvictionRunsMillis=${timeBetweenEvictionRunsMillis}
sharding.jdbc.datasource.ds1.minEvictableIdleTimeMillis=${minEvictableIdleTimeMillis}
#分库配置
sharding.jdbc.config.sharding.default-database-strategy.standard.sharding-column=order_id
sharding.jdbc.config.sharding.default-database-strategy.standard.precise-algorithm-class-name=cn.hujinbo.sharding.config.database.PreciseShardingDatabaseAlgorithm
sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_000$->{0..2}
sharding.jdbc.config.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=cn.hujinbo.sharding.config.database.PreciseShardingTableAlgorithm
sharding.jdbc.config.sharding.tables.leader.actual-data-nodes=m.leader
sharding.jdbc.config.sharding.tables.leader.table-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.tables.leader.table-strategy.inline.algorithm-expression=leader
sharding.jdbc.config.sharding.props.sql.show=true