参考: (49条消息) Sharding-Sphere:SpringBoot 2 读写分离、水平分表结合Druid,分库分表-基础篇_琦彦的博客-CSDN博客
一. 升级版本有坑
由于开发环境用的组合是shardJDBC 3.1.0 + Druid 1.2.6 + JPA 2.1.13, mysql DB, 详细版本如下,本来想着升级一下ShardingJDBC到5.X最新版本,却遇到各种不兼容问题,退回到4.X也不能解决版本问题,无奈之下还是保留以下版本组合,另外,升级到5.X之后,保含的JAR可以用巨量来形容,因为项目只用到ShardJDBC的分表分库功能,所以JAR包需要一个个
dependencies { implementation 'org.springframework.boot:spring-boot-starter-web:2.1.3.RELEASE' providedRuntime 'org.springframework.boot:spring-boot-starter-tomcat' testImplementation 'org.springframework.boot:spring-boot-starter-test:2.1.3.RELEASE'// mysql驱动 implementation 'mysql:mysql-connector-java:8.0.21' implementation 'org.springframework.boot:spring-boot-starter-data-jpa:2.1.13.RELEASE' implementation 'com.alibaba:druid-spring-boot-starter:1.2.6' //数据库与读写分离 implementation ('io.shardingsphere:sharding-jdbc-spring-boot-starter:3.1.0') implementation 'io.shardingsphere:sharding-jdbc-spring-namespace:3.1.0' }
二. 主写从读+ 不分库,只分表,YAML配置
下面配置分表用了两种方式,分别是inline 和 Standard
server: port: 8080 servlet: context-path: /druid spring: application: name: druid main: allow-bean-definition-overriding: true jpa: show-sql: true properties: hibernate: temp: use_jdbc_metadata_defaults: false format_sql: true naming: # implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl sharding: jdbc: dataSource: names: db-master,db-slave1 # 配置主库 db-master: type: com.alibaba.druid.pool.DruidDataSource url: jdbc:mysql://localhost:3306/world?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true username: ivy password: 201129Gnn driver-class-name: com.mysql.cj.jdbc.Driver db-slave1: # 配置第一个从库 type: com.alibaba.druid.pool.DruidDataSource url: jdbc:mysql://localhost:3306/world?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true username: root password: 123456 driver-class-name: com.mysql.cj.jdbc.Driver config: masterslave: # 配置读写分离 load-balance-algorithm-type: round-robin # 从库选择规则 //random 随机 //round_robin 轮询 sharding: master-slave-rules: sdkcms: master-data-source-name: db-master slave-data-source-names: db-slave1 tables: t_order: actual-data-nodes: sdkcms.t_order$->{0..1} table-strategy: inline: sharding-column: id algorithm-expression: t_order$->{id % 2} t_user: actual-data-nodes: sdkcms.t_user$->{0..1} table-strategy: standard: sharding-column: id precise-algorithm-class-name: com.example.demo.user.UserPreciseTableShardingAlgorithm props: sql: show: true # 开启SQL显示,默认值: false,注意:仅配置读写分离时不会打印日志!!!
PreciseShardingAlgorithm 实现类如下
package com.example.demo.user; import java.util.Collection; import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue; import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm; public class UserPreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<String>{ public UserPreciseTableShardingAlgorithm() { } @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) { for (String each :availableTargetNames) { if (each.endsWith(String.valueOf((Integer.parseInt(shardingValue.getValue())) % 2))) { return each; } } throw new UnsupportedOperationException(); } }
三. 当Entity 中的id 是String类型,而YML中配置的分表键为id%2, 会报下面的错误
algorithmExpression: t_order${id % 2}
@Table(name = "t_order") public class OrderEntity { @Id private String id; private String name; public OrderEntity() { }}
解决方案:
1. 将OrderEntity 中的id改为int, 这样Groovy 表达就可以正确执行mod操作
2. 自定分表的类UserPreciseTableShardingAlgorithm,在类中转化类型后解决
groovy.lang.MissingMethodException: No signature of method: java.lang.String.mod() is applicable for argument types: (java.lang.Integer) values: [2] Possible solutions: drop(int), any(), find(), use([Ljava.lang.Object;), is(java.lang.Object), find(java.util.regex.Pattern) at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:58) ~[groovy-2.4.5-indy.jar:2.4.5] at org.codehaus.groovy.runtime.callsite.PojoMetaClassSite.call(PojoMetaClassSite.java:49) ~[groovy-2.4.5-indy.jar:2.4.5] at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48) ~[groovy-2.4.5-indy.jar:2.4.5] at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113) ~[groovy-2.4.5-indy.jar:2.4.5] at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125) ~[groovy-2.4.5-indy.jar:2.4.5] at Script2$_run_closure1.doCall(Script2.groovy:1) ~[na:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_191-1-redhat] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_191-1-redhat] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_191-1-redhat] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_191-1-redhat] at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:93) ~[groovy-2.4.5-indy.jar:2.4.5] at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:325) ~[groovy-2.4.5-indy.jar:2.4.5] at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:294) ~[groovy-2.4.5-indy.jar:2.4.5] at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1019) ~[groovy-2.4.5-indy.jar:2.4.5] at groovy.lang.Closure.call(Closure.java:426) ~[groovy-2.4.5-indy.jar:2.4.5] at groovy.lang.Closure.call(Closure.java:420) ~[groovy-2.4.5-indy.jar:2.4.5] at io.shardingsphere.core.routing.strategy.inline.InlineShardingStrategy.execute(InlineShardingStrategy.java:86) ~[sharding-core-3.1.0.jar:na]
四、有一些语法不支持
sharding-jdbc不支持distinct
,单表可使用group by
进行替代
sharding-jdbc不支持having,可使用嵌套子查询进行替代
sharding-jdbc不支持union(all),可拆分成多个查询,在程序拼接
严禁无切分键的深分页!因为会对SQL进行以下解释,然后在内存运行。
select * from a limit 10 offset 1000
Actual SQL:db0 ::: select * from a limit 1010 offset 0
五、sharding-jdbc 分片策略分片策略
包含分片键和分片算法,由于分片算法的独立性,将其独立抽离。真正可用于分片操作的是分片键 + 分片算法,也就是分片策略。目前提供5种分片策略。
标准分片策略
对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键,提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的,用于处理=和IN的分片。RangeShardingAlgorithm是可选的,用于处理BETWEEN AND, >, <, >=, <=分片,如果不配置RangeShardingAlgorithm,SQL中的BETWEEN AND将按照全库路由处理。
复合分片策略
对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键,由于多分片键之间的关系复杂,因此并未进行过多的封装,而是直接将分片键值组合以及分片操作符透传至分片算法,完全由应用开发者实现,提供最大的灵活度。
行表达式分片策略
对应InlineShardingStrategy。使用Groovy的表达式,提供对SQL语句中的=和IN的分片操作支持,只支持单分片键。对于简单的分片算法,可以通过简单的配置使用,从而避免繁琐的Java代码开发,如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8,而分成8张表,表名称为t_user_0到t_user_7。
Hint分片策略
对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。
不分片策略
对应NoneShardingStrategy。不分片的策略。