数据按某个关键字段做分库分表,比如按userid,此处以userId为例,分为16个库(1-16),16个表(1-16)。
分库分表逻辑为:
分库:userId% 16+1(此处1-16所以要加1,如果不加1就是0-15)
分表:CRC32(userId) % 16 + 1
添加maven依赖
<!-- 依赖sharding 分库分表 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-core-common</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.2.1</version>
</dependency>
配置多数据源
spring:
jackson:
time-zone: GMT+8
date-format: yyyy-MM-dd HH:mm:ss
mybatis-plus:
configuration:
# 这个配置会将执行的sql打印出来,在开发或测试的时候可以用
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
autoconfigure:
exclude:
- com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
# 迁移需要链接的数据库
datasource:
dynamic:
primary: slave
datasource:
slave:
url: jdbc:mysql://xxx:63306/gac_order?useUnicode=true&characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull
username: xxx
password: xxx
type: com.alibaba.druid.pool.DruidDataSource
druid:
#initialSize: 30
#maxActive: 20
minIdle: 5
maxWait: 1000
minEvictableIdleTimeMillis: 300000
useGlobalDataSourceStat: true
timeBetweenEvictionRunsMillis: 60000
validationQuery: select 'x'
testOnBorrow: true
testOnReturn: true
testWhileIdle: true
write:
url: jdbc:mysql://xxx:63306/gac_order?useUnicode=true&characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull
username: xxx
password: xxx
type: com.alibaba.druid.pool.DruidDataSource
druid:
#initialSize: 30
#maxActive: 20
minIdle: 5
maxWait: 1000
minEvictableIdleTimeMillis: 300000
useGlobalDataSourceStat: true
timeBetweenEvictionRunsMillis: 60000
validationQuery: select 'x'
testOnBorrow: true
testOnReturn: true
testWhileIdle: true
shardingsphere:
# 参数配置,显示sql
props:
sql.show: true
# 配置数据源
datasource:
names: ds1,ds2,ds3
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://xxx:63306/order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: xxx
password: xxx
druid:
initialSize: 30
maxActive: 100
minIdle: 5
maxWait: 1000
minEvictableIdleTimeMillis: 300000
useGlobalDataSourceStat: true
timeBetweenEvictionRunsMillis: 60000
validationQuery: select 'x'
testOnBorrow: true
testOnReturn: true
testWhileIdle: true
ds2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://xxx:63306/order_2?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: xxx
password: xxx
druid:
initialSize: 30
maxActive: 100
minIdle: 5
maxWait: 1000
minEvictableIdleTimeMillis: 300000
useGlobalDataSourceStat: true
timeBetweenEvictionRunsMillis: 60000
validationQuery: select 'x'
testOnBorrow: true
testOnReturn: true
testWhileIdle: true
ds3:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://xxx:63306/order_3?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: xxx
password: xxx
druid:
initialSize: 30
maxActive: 100
minIdle: 5
maxWait: 1000
minEvictableIdleTimeMillis: 300000
useGlobalDataSourceStat: true
timeBetweenEvictionRunsMillis: 60000
validationQuery: select 'x'
testOnBorrow: true
testOnReturn: true
testWhileIdle: true
sharding:
tables:
order_info:
actual-data-nodes: ds$->{1..3}.order_info_$->{1..3}
table-strategy:
standard:
sharding-column: user_id
precise-algorithm-class-name: com.com.xxx.sharding.algorithm.table.PreciseShardingUserIdAlgorithm
database-strategy:
standard:
sharding-column: user_id
precise-algorithm-class-name: com.xxx.sharding.algorithm.db.DbPreciseShardingUserIdAlgorithm
order_info_ext:
actual-data-nodes: ds$->{1..3}.order_info_ext_$->{1..3}
table-strategy:
standard:
sharding-column: user_id
precise-algorithm-class-name: com.xxx.sharding.algorithm.table.PreciseShardingUserIdAlgorithm
database-strategy:
standard:
sharding-column: user_id
precise-algorithm-class-name: com.xxx.sharding.algorithm.db.DbPreciseShardingUserIdAlgorithm
# 设置绑定表 (必须按相同分片健进行分片的表才能互为成绑定表,在联合查询时就能避免出现笛卡尔积查询)
binding-tables: order_info, order_info_ext
编写分库工具类
public class DbPreciseShardingUserIdAlgorithm implements PreciseShardingAlgorithm<Long> {
private final static int SHARDING_BASE = 16;
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
Long userId = preciseShardingValue.getValue();
log.info("preciseSharding db start, userId:{}", userId);
String useDb = null;
String keyStr=String.valueOf(userId % SHARDING_BASE + 1);
if (Objects.nonNull(userId)) {
for (String key : collection) {
if (key.endsWith(keyStr)) {
log.info("key-result:{}", key);
useDb = key;
break;
}
}
}
log.info("ShardingUserId use db: {}", useDb);
return useDb;
}
}
编写分表工具类
@Slf4j
public class PreciseShardingUserIdAlgorithm implements PreciseShardingAlgorithm<Long> {
private final static int SHARDING_BASE = 16;
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
Long userId = preciseShardingValue.getValue();
CRC32 crc32 = new CRC32();
crc32.update(String.valueOf(userId).getBytes());
log.info("preciseSharding table start, userId:{}, crc32UserId:{}", userId, crc32.getValue());
String useTable = null;
//此处也可以不用crc32,也可以用(userId/ SHARDING_BASE)% SHARDING_BASE+1
String keyStr = String.valueOf(crc32.getValue() % SHARDING_BASE + 1);
if (Objects.nonNull(userId)) {
for (String key : collection) {
if (key.endsWith(keyStr)) {
useTable = key;
break;
}
}
}
log.info("ShardingUserId use table: {}", useTable);
return useTable;
}
}
多数分库分表的时候涉及到数据迁移,可以写一个单独的程序做全量数据迁移
然后新数据通过双写存入旧库和分库
采取灰度方式,实现先双写读旧,双写读新,单写读新的过渡