ShardingSphere生产实战
由于业务发展,数据库中某几张核心表未来半年单表数据能达到20亿左右。也因为外部的因素,只能用mysql来存储数据。所以考虑分库分表,最终选型为ShardingSphere
。
方案选型可见文章:Mysql大数据量解决方案
考虑拆分后数据不会绝对均匀,按前期每表存400万来算,大约需512张表。8个库,那每库有64张表。团队采用的单分片键,按什么分片可自行约定,比如按地区,按业务标识等。
配置的分片策略为:
-
库:sharding_column % 8
-
表:sharding_column / 8 % 64
本地用了两个版本测试,线上用的是5.2.0版本。
ShardingSphere 5.0.0-alpha
5.0.0-alpha 和 5.2.0配置文件有差异。
配置文件中common项可以配置数据源的公共配置。5.2.0版本公共项只能在每个数据源下都配置一遍。
maven版本:
<shardingsphere-starter.version>5.0.0-alpha</shardingsphere-starter.version>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${shardingsphere-starter.version}</version>
</dependency>
<!-- druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
application.yml:
spring:
shardingsphere:
datasource:
common:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
initial-size: 6
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
#Oracle需要打开注释
#validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
wall:
multi-statement-allow: true
names: ds0, ds_1, ds_2, ds_3, ds4, ds_5, ds_6, ds_7
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1/test?autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8
username: root
password: root
ds1:
......
省略
rules:
sharding:
tables:
# 逻辑表名
t_record:
actualDataNodes: ds_$->{0..7}.t_record_$->{0..63}
# 配置表分片策略
tableStrategy:
standard:
shardingColumn: record_id
shardingAlgorithmName: t-record-inline
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
defaultShardingColumn: record_id
#绑定表
bindingTables:
- t_record
defaultTableStrategy:
none:
defaultDatabaseStrategy:
standard:
shardingColumn: record_id
shardingAlgorithmName: database-inline
#分片算法配置
sharding-algorithms:
t-record-inline:
type: INLINE
props:
algorithm-expression: t_record_$->{record_id.intdiv(8) % 64}
database-inline:
type: INLINE
props:
algorithm-expression: ds_$->{record_id % 8}
default-key-generate-strategy:
column: id
key-generator-name: snowflake
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
props:
sql-show: true
配置Druid监控代码:
@Configuration
public class DruidConfig {
@Bean
public ServletRegistrationBean statViewServlet() {
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
Map<String, String> initParams = new HashMap<>();
initParams.put("loginUsername", "root");
initParams.put("loginPassword", "root");
//initParams.put("", true);
//默认就是允许所有访问
initParams.put("allow", "127.0.0.1");
//黑名单IP
initParams.put("deny", "192.168.1.1");
bean.setInitParameters(initParams);
return bean;
}
@Bean
public FilterRegistrationBean webStatFilter() {
WebStatFilter webStatFilter = new WebStatFilter();
FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>(webStatFilter);
filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
ShardingSphere 5.2.0线上配置
maven版本:
<shardingsphere-starter.version>5.2.0</shardingsphere-starter.version>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${shardingsphere-starter.version}</version>
</dependency>
<!-- druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.18</version>
</dependency>
application.yml:
spring:
shardingsphere:
datasource:
names: ds0, ds_1, ds_2, ds_3, ds4, ds_5, ds_6, ds_7
ds_0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1/prod?autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8
username: root
password: root
maxWait: 60000
maxActive: 100
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: true
timeBetweenEvictionRunsMillis: 300000
minEvictableIdleTimeMillis: 3600000
useUnfairLock: true
ds_1:
......
省略
rules:
sharding:
tables:
# 逻辑表名
t_record:
actualDataNodes: ds_${0..7}.t_record_${0..63}
# 配置表分片策略
tableStrategy:
standard:
shardingColumn: record_id
shardingAlgorithmName: t-record-inline
keyGenerateStrategy:
column: id
keyGeneratorName: UUID
defaultShardingColumn: record_id
#绑定表
bindingTables:
- t_record
defaultTableStrategy:
none:
defaultDatabaseStrategy:
standard:
shardingColumn: record_id
shardingAlgorithmName: database-inline
#分片算法配置
sharding-algorithms:
t-record-inline:
type: INLINE
props:
algorithm-expression: t_record_${record_id.intdiv(8) % 64}
database-inline:
type: INLINE
props:
algorithm-expression: ds_${record_id % 8}
default-key-generate-strategy:
column: id
key-generator-name: UUID
key-generators:
UUID:
type: UUID
props:
sql-show: false
Groovy中相除结果为浮点数
record_id/8有时会出现0.5的结果,则表名为t_record_0.5,会报错。
原因是Groovy不提供专用的整数除法运算符符号,需将表达式中 record_id/8 修改为 record_id.intdiv(8)
在application.yml 下加入Druid监控配置会报错
spring:
shardingsphere:
datasource:
names: ds0, ds_1, ds_2, ds_3, ds4, ds_5, ds_6, ds_7
ds_0:
filters: stat
可见github上issues:https://github.com/apache/shardingsphere/issues/21211
报错
Caused by: org.yaml.snakeyaml.constructor.ConstructorException: Can't construct a java object for tag:yaml.org,2002:com.alibaba.druid.filter.stat.StatFilter; exception=Class is not accepted: com.alibaba.druid.filter.stat.StatFilter
in 'string', line 76, column 5:
- !!com.alibaba.druid.filter.stat. ...
5.2.0自动审计配置不生效,配置审计是为了拦截没有走分片键的sql,避免全路由。
排查后是因为 ShardingSphereAutoConfiguration
自动配置没有把审计的实现类注入。于是乎本地手动注入,具体实现为配置一个后置处理器BeanPostProcessor 即可。
具体案例可参考之前文章:策略模式、模板模式实战
@Override
public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
//加载审计配置,ShardingRuleSpringBootConfiguration自动配置类未加载,所以手动加载
if (bean instanceof AlgorithmProvidedShardingRuleConfiguration) {
PropertySource source = ((ConfigurableEnvironment) environment).getPropertySources().get("applicationConfig: [classpath:/application.yml]");
Object val = source.getProperty("spring.shardingsphere.rules.sharding.auditors.sharding_key_required_auditor.type");
//简单实现
if (val != null) {
Map<String, ShardingAuditAlgorithm> auditors = new LinkedHashMap<>();
auditors.put("sharding-key-required-auditor", new DMLShardingConditionsShardingAuditAlgorithm());
((AlgorithmProvidedShardingRuleConfiguration) bean).setAuditors(auditors);
}
}
return bean;
}
5.2.0版本测试问题
-
5.2.0版本中配置
ShardingSphere
主键生成策略不生效,与MybatisPlus
主键生成策略冲突,猜测原因是5.2.0版本问题。
所以代码中主键生成用MybatispPlus
内置的雪花算法实现。
-
除了分片表之外,
ShardingSphere
连接的数据源中,不能有重复的表。ShardingSphere
启动时,会把对应的数据源和表的映射关系放在Map
中,如果重复,则一个表会对应多个数据源。表关联会报错(必须指定相同的数据源)。
-
分片表和普通表可以join查询,但必须指定相同的数据源。
-
ShardingSphere
支持跨库事务更新,如果是代码逻辑异常则会回滚。