随着系统用户量的增加,某些业务表的数据会急剧上升,超过1千万以上,这时就要考虑对表进行水平拆分。
表的水平拆分
将一个表拆分成N个表,拆分成多个子表,如user表,拆分user_0,user_1,…user_4
application.properties
spring.shardingsphere.datasource.names=master
# 数据源
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/ds_2?characterEncoding=utf-8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=
# 分表配置
spring.shardingsphere.sharding.tables.user.actual-data-nodes=master.user_${0..4} #配置分表信息,这边用的inline表达式
#分表策略
# inline 表达式
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id #分表的字段,这边用id分表
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_${id.longValue() % 5} #分表算法行表达式,需符合groovy语法,上面的配置就是用id进行取模分片
#可自定义分表策略
#spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=id
#spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=
# 显示SQL
spring.shardingsphere.props.sql.show=true
自定义分表策略类
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
for (String tableName : availableTargetNames) {
if (tableName.endsWith(shardingValue.getValue() % 4 + "")) {
return tableName;
}
}
throw new IllegalArgumentException();
}
}