分库分表
shardingjdbc的核心功能是数据分片和读写分离,通过ShardingJDBC,应用可以
透明的使用JDBC访问已经分库分表、读写分离的多个数据源,而不用关心数据源的
数量以及数据如何分布
- 先了解几个概念
逻辑表:水平拆分的数据库的相同逻辑和数据结构表的总称
真实表:在分片的数据库中真实存在的物理表。
数据节点:数据分片的最小单元。由数据源名称和数据表组成
绑定表:分片规则一致的主表和子表。
广播表:也叫公共表,指素有的分片数据源中都存在的表,表结构和表中的数据
在每个数据库中都完全一致。例如字典表。
分片键:用于分片的数据库字段,是将数据库(表)进行水平拆分的关键字段。
SQL中若没有分片字段,将会执行全路由,性能会很差。
分片算法:通过分片算法将数据进行分片,支持通过=、BETWEEN和IN分片。
分片算法需要由应用开发者自行实现,可实现的灵活度非常高。
分片策略:真正用于进行分片操作的是分片键+分片算法,也就是分片策略。在
ShardingJDBC中一般采用基于Groovy表达式的inline分片策略,通过一个包含
分片键的算法表达式来制定分片策略,如t_user_$->{u_id%8}标识根据u_id模
8,分成8张表,表名称为t_user_0到t_user_7。
基础配置
在一个库中将一张表的数据按照奇数和偶数分到两个表中
#配置数据源
spring.shardingsphere.datasource.names=m1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://115.29.197.4:3306/masterdemo?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
#配置真实表分布 逻辑表 m1.course_$->{1..2} 表示course表的真实分布 course_1 和 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
#主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
#雪花算法
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
#默认的配置 可以不配
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#配置分表策略 分片建是cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
#分片算法 偶数就会进去course_1 奇数就会加入到course_2
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
#其他运行属性
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
分库操作
#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://115.29.197.4:3306/course1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://115.29.197.4:3306/course2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
#真实表分布,分库,分表
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
#可以不配置使用默认既可
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#策略1:
#inline分片策略只支持精确查找 where id = ...这种
#spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{cid%2+1}
#策略2:
#standard标准分片策略 只支持一个分片建,也就是一个字段,支持精确查询和范围查询
#standard支持precise和range(精确的 where id = ... 和 范围查询where id between ... and ...)需要配置策略类
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.precise-algorithm-class-name=com.roy.shardingDemo.algorithem.MyPreciseTableShardingAlgorithm
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.range-algorithm-class-name=com.roy.shardingDemo.algorithem.MyRangeTableShardingAlgorithm
#spring.shardingsphere.sharding.tables.course.database-strategy.standard.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.database-strategy.standard.precise-algorithm-class-name=com.roy.shardingDemo.algorithem.MyPreciseDSShardingAlgorithm
#spring.shardingsphere.sharding.tables.course.database-strategy.standard.range-algorithm-class-name=com.roy.shardingDemo.algorithem.MyRangeDSShardingAlgorithm
#策略3:
#complex复杂分片策略 支持多字段的分库分表,例如:(where id = ... and name = ... and status in(..) )需要配置策略类
#spring.shardingsphere.sharding.tables.course.table-strategy.complex.sharding-columns= cid, user_id
#spring.shardingsphere.sharding.tables.course.table-strategy.complex.algorithm-class-name=com.roy.shardingDemo.algorithem.MyComplexTableShardingAlgorithm
#
#spring.shardingsphere.sharding.tables.course.database-strategy.complex.sharding-columns=cid, user_id
#spring.shardingsphere.sharding.tables.course.database-strategy.complex.algorithm-class-name=com.roy.shardingDemo.algorithem.MyComplexDSShardingAlgorithm
#策略4:
#hint强制路由策略 只需要配置算法类,不需要配置分片建 ,这里只指定了分表的策略 分库的策略默认是全部,也可以指定其他策略
spring.shardingsphere.sharding.tables.course.table-strategy.hint.algorithm-class-name=com.roy.shardingDemo.algorithem.MyHintTableShardingAlgorithm
#广播表配置 t_dict广播表 广播表的概念就是每个库中都保存全量的数据
spring.shardingsphere.sharding.broadcast-tables=t_dict
#配置主键生成策略
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
#雪花算法 work-id可选属性可以不配置
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
- 注意多种策略不能同时使用,否则会冲突。
- 广播表的概念: 每个库中都保存全量的数据
以下贴出策略类
精确查找或范围查找的表策略
public class MyPreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
//select * from course where cid = ? or cid in (?,?)
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
String logicTableName = shardingValue.getLogicTableName();
String cid = shardingValue.getColumnName();
Long cidValue = shardingValue.getValue();
//实现 course_$->{cid%2+1) 找表,和之前分表存储的时候一样,偶数去course_1表找,奇数去course_2表找,要实现相同的算法
BigInteger shardingValueB = BigInteger.valueOf(cidValue);
BigInteger resB = (shardingValueB.mod(new BigInteger("2"))).add(new BigInteger("1"));
String key = logicTableName+"_"+resB;
if(availableTargetNames.contains(key)){
return key;
}
//couse_1, course_2
throw new UnsupportedOperationException("route "+ key +" is not supported ,please check your config");
}
}
精确查找或范围查找的数据库策略
public class MyPreciseDSShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
//select * from course where cid = ? or cid in (?,?)
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
String logicTableName = shardingValue.getLogicTableName();
String cid = shardingValue.getColumnName();
Long cidValue = shardingValue.getValue();
//实现 course_$->{cid%2+1)
BigInteger shardingValueB = BigInteger.valueOf(cidValue);
BigInteger resB = (shardingValueB.mod(new BigInteger("2"))).add(new BigInteger("1"));
String key = "m"+resB;
if(availableTargetNames.contains(key)){
return key;
}
//couse_1, course_2
throw new UnsupportedOperationException("route "+ key +" is not supported ,please check your config");
}
}
范围查找的表策略
public class MyRangeTableShardingAlgorithm implements RangeShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
//select * from course where cid between 1 and 100;
// 上限
Long upperVal = shardingValue.getValueRange().upperEndpoint();//100
// 下限
Long lowerVal = shardingValue.getValueRange().lowerEndpoint();//1
String logicTableName = shardingValue.getLogicTableName();
// 两个表都要查询
return Arrays.asList(logicTableName+"_1",logicTableName+"_2");
}
}
范围查找的数据库策略
public class MyRangeDSShardingAlgorithm implements RangeShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
//select * from course where cid between 1 and 100;
Long upperVal = shardingValue.getValueRange().upperEndpoint();//100
Long lowerVal = shardingValue.getValueRange().lowerEndpoint();//1
String logicTableName = shardingValue.getLogicTableName();
return Arrays.asList("m1","m2");
}
}
复杂分片表策略类
public class MyComplexDSShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
// SELECT cid,cname,user_id,cstatus FROM course
// WHERE cid BETWEEN ? AND ? AND user_id = ?
// 这里返回值是什么类型的就构建什么数据类型
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
Range<Long> cidRange = shardingValue.getColumnNameAndRangeValuesMap().get("cid");
// 因为user_id 不是一个主键所以查询结果是一个集合,所以也支持in查询
Collection<Long> userIdCol = shardingValue.getColumnNameAndShardingValuesMap().get("user_id");
// 上限和下限
Long upperVal = cidRange.upperEndpoint();
Long lowerVal = cidRange.lowerEndpoint();
List<String> res = new ArrayList<>();
for(Long userId: userIdCol){
// 针对user_id 进行查找
//course_{userID%2+1}
BigInteger userIdB = BigInteger.valueOf(userId);
BigInteger target = (userIdB.mod(new BigInteger("2"))).add(new BigInteger("1"));
res.add("m"+target);
}
return res;
}
}
复杂分片的类策略类
public class MyComplexDSShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
// SELECT cid,cname,user_id,cstatus FROM course
// WHERE cid BETWEEN ? AND ? AND user_id = ?
// 这里返回值是什么类型的就构建什么数据类型
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
Range<Long> cidRange = shardingValue.getColumnNameAndRangeValuesMap().get("cid");
// 因为user_id 不是一个主键所以查询结果是一个集合,所以也支持in查询
Collection<Long> userIdCol = shardingValue.getColumnNameAndShardingValuesMap().get("user_id");
// 上限和下限
Long upperVal = cidRange.upperEndpoint();
Long lowerVal = cidRange.lowerEndpoint();
List<String> res = new ArrayList<>();
for(Long userId: userIdCol){
// 针对user_id 进行查找
//course_{userID%2+1}
BigInteger userIdB = BigInteger.valueOf(userId);
BigInteger target = (userIdB.mod(new BigInteger("2"))).add(new BigInteger("1"));
res.add("m"+target);
}
return res;
}
}
**简单理解就是:按照一定的策略将数据存到指定的库或者表中,在查找时候再按照对应的策略到指定的库或表中查找 **
关于多表关联下绑定表的配置
spring.shardingsphere.datasource.names=m1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://115.29.197.4:3306/masterdemo?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
#逻辑表
spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=m1.t_dict_$->{1..2}
#分片建
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.algorithm-expression=t_dict_$->{ustatus.toInteger()%2+1}
#逻辑表
spring.shardingsphere.sharding.tables.user.actual-data-nodes=m1.t_user_$->{1..2}
#分片建
spring.shardingsphere.sharding.tables.user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.user.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=t_user_$->{ustatus.toInteger()%2+1}
#绑定表示例 user和t_dict分别是逻辑表的名称,默认1会和1绑定2会和2绑定,这里是一个数组,可以指定多组绑定,绑定的原因下面会说明
spring.shardingsphere.sharding.binding-tables[0]=user,t_dict
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
@Select("select u.user_id,u.username,d.uvalue ustatus from user u left join t_dict d on u.ustatus = d.ustatus")
public List<User> queryUserStatus();
如果不进行绑定执行以上sql会出现下面的场景—出现笛卡尔积的查询
2021-09-25 00:00:45.515 INFO 85580 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_2 u left join t_dict_1 d on u.ustatus = d.ustatus
2021-09-25 00:00:45.515 INFO 85580 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_2 u left join t_dict_2 d on u.ustatus = d.ustatus
2021-09-25 00:00:45.515 INFO 85580 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_1 u left join t_dict_1 d on u.ustatus = d.ustatus
2021-09-25 00:00:45.515 INFO 85580 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_1 u left join t_dict_2 d on u.ustatus = d.ustatus
User{userId=648309825893044225, username='user No 1', ustatus='null', uage=0}
User{userId=648309827151335425, username='user No 3', ustatus='null', uage=0}
User{userId=648309828405432321, username='user No 5', ustatus='null', uage=0}
User{userId=648309829634363393, username='user No 7', ustatus='null', uage=0}
User{userId=648309830963957761, username='user No 9', ustatus='null', uage=0}
User{userId=648309825893044225, username='user No 1', ustatus='??', uage=0}
User{userId=648309827151335425, username='user No 3', ustatus='??', uage=0}
User{userId=648309828405432321, username='user No 5', ustatus='??', uage=0}
User{userId=648309829634363393, username='user No 7', ustatus='??', uage=0}
User{userId=648309830963957761, username='user No 9', ustatus='??', uage=0}
User{userId=648309825247121408, username='user No 0', ustatus='???', uage=0}
User{userId=648309826517995520, username='user No 2', ustatus='???', uage=0}
User{userId=648309827772092416, username='user No 4', ustatus='???', uage=0}
User{userId=648309828950691840, username='user No 6', ustatus='???', uage=0}
User{userId=648309830305452032, username='user No 8', ustatus='???', uage=0}
User{userId=648309825247121408, username='user No 0', ustatus='null', uage=0}
User{userId=648309826517995520, username='user No 2', ustatus='null', uage=0}
User{userId=648309827772092416, username='user No 4', ustatus='null', uage=0}
User{userId=648309828950691840, username='user No 6', ustatus='null', uage=0}
User{userId=648309830305452032, username='user No 8', ustatus='null', uage=0}
加上绑定之后可以看到t_user_1只和t_dict_1关联
2021-09-25 00:03:20.027 INFO 85617 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_1 u left join t_dict_1 d on u.ustatus = d.ustatus
2021-09-25 00:03:20.027 INFO 85617 --- [ main] ShardingSphere-SQL : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_2 u left join t_dict_2 d on u.ustatus = d.ustatus
User{userId=648309825247121408, username='user No 0', ustatus='???', uage=0}
User{userId=648309826517995520, username='user No 2', ustatus='???', uage=0}
User{userId=648309827772092416, username='user No 4', ustatus='???', uage=0}
User{userId=648309828950691840, username='user No 6', ustatus='???', uage=0}
User{userId=648309830305452032, username='user No 8', ustatus='???', uage=0}
User{userId=648309825893044225, username='user No 1', ustatus='??', uage=0}
User{userId=648309827151335425, username='user No 3', ustatus='??', uage=0}
User{userId=648309828405432321, username='user No 5', ustatus='??', uage=0}
User{userId=648309829634363393, username='user No 7', ustatus='??', uage=0}
User{userId=648309830963957761, username='user No 9', ustatus='??', uage=0}
主从 读写分离配置
`#配置主从数据源,要基于MySQL主从架构。
spring.shardingsphere.datasource.names=m0,s0
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/slave_test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=rootroot
spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://115.29.197.4:3306/master_test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=root
#读写分离规则, m0 主库,s0 从库
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names[0]=s0
#基于读写分离的表分片
spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=ds0.dict
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.props.worker.id=1
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true