Springboot整合ShardingJDBC实现分库分表
官网地址:http://shardingsphere.apache.org/document/legacy/2.x/cn/02-guide/configuration/
关于分库分表的相关知识点:
1、垂直分表:按照列进行拆分,将列比较多的表拆分成若干个表,其他的表根据主表ID作为外键
2、水平分表:按照行进行拆分,具体需要按照不同的策略进行拆分
- 根据取值范围,例如用户表,指定每个库存储10000个,则可以根据
一、读写分离,多数据源
1、读写分离实现
(1)添加依赖
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-transaction-2pc-xa</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-transaction-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
(2)添加配置
spring:
shardingsphere:
datasource:
names:
master,slave
master: #主库
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: ${datasource_master_url}
username: ${datasource_master_username}
password: ${datasource_master_password}
slave: #从库
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: ${datasource_slave_url}
username: ${datasource_slave_username}
password: ${datasource_slave_password}
masterslave: # 设置主从关系
load-balance-algorithm-type: round_robin
name: assets_center
master-data-source-name: master
slave-data-source-names: slave
(3)编码方式–强制读主库
//强制读主库
public <T,K> T hintMasterRout(K key, Function<K,T> function){
HintManager instance = HintManager.getInstance();
try {
instance.setMasterRouteOnly();
//当前线程强制走主库的业务代码,数据库操作等
return function.apply(key);
}finally {
instance.close();
}
}
//调用时
public SpecialGiveVO doGiveSpecial(SpecialGiveRequestDTO dto){
//giveSpecial方法为具体实现方法
return hintMasterRout(dto,(dto1)-> giveSpecial(dto1));
}
(4)注解方式–强制读主库
1、定义注解ForceMaster
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface ForceMaster {
}
2、定义数据库元数据处理器
@Slf4j
public abstract class MeteObjectInterceptor {
private Map<String, ClassMetaObject> mapData = null;
protected Map<String, ClassMetaObject> getInitMapperData(MappedStatement mappedStatement) {
if (mapData == null) {
synchronized (this) {
if (mapData == null) {
try {
mapData = mappedStatement.getConfiguration().getMapperRegistry().getMappers()
.stream().collect(Collectors.toMap(Class::getName, WriteReadInterceptor
.ClassMetaObject::new, (a, b) -> a));
} catch (Exception exception) {
if(mappedStatement != null){
log.error(String.format("get,getMethod,mappedStatement.id = [%s]",
mappedStatement.getId()));
}
}
if (mapData == null) {
mapData = new HashMap<>();
}
}
}
}
return mapData;
}
protected Map<String, ClassMetaObject> getMapData() {
return mapData;
}
@Data
protected static class ClassMetaObject {
private Class<?> aClass;
private Map<String, Method> name2MethodMap;
public ClassMetaObject(Class<?> aClass) {
this.aClass = aClass;
}
public Method getMethod(String methodName) {
if (name2MethodMap == null) {
synchronized (this) {
if (name2MethodMap == null) {
try {
name2MethodMap = Stream.of(aClass.getMethods())
.collect(Collectors.toMap(Method::getName, e -> e, (a, b) -> a));
} catch (Exception ex) {
log.error(String.format("get,getMethod,methodName = [%s],aClass=[%s]",
methodName,aClass));
}
if (name2MethodMap == null) {
name2MethodMap = new HashMap<>();
}
}
}
}
return name2MethodMap.get(methodName);
}
}
}
读写分离处理
@Slf4j
@Component
@Intercepts({@Signature(type = Executor.class, method = "update",
args = {MappedStatement.class, Object.class})
, @Signature(type = Executor.class, method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class,
CacheKey.class, BoundSql.class})
, @Signature(type = Executor.class, method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class WriteReadInterceptor extends MeteObjectInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
//handler
Pair<Boolean, Object> handleAnnotation = handleAnnotation(invocation);
Boolean isHandle = handleAnnotation.getKey();
if (isHandle) {
return handleAnnotation.getValue();
}
return invocation.proceed();
}
private Pair<Boolean, Object> handleAnnotation(Invocation invocation) {
Pair<Boolean, Object> noHandle = Pair.of(false, null);
try {
Object[] args = invocation.getArgs();
if (args.length < 1) {
return noHandle;
}
MappedStatement mappedStatement = (MappedStatement) args[0];
Map<String, ClassMetaObject> mapData = getInitMapperData(mappedStatement);
if (mapData == null) {
return noHandle;
}
String id = mappedStatement.getId();
int endIndex = id.lastIndexOf('.');
if(endIndex < 0){
return noHandle;
}
String className = id.substring(0, endIndex);
String methodName = id.substring(endIndex + 1);
ClassMetaObject classMetaObject = mapData.get(className);
if (classMetaObject == null) {
return noHandle;
}
Method method = classMetaObject.getMethod(methodName);
if (method == null) {
return noHandle;
}
ForceMaster annotation = method.getAnnotation(ForceMaster.class);
if (annotation == null) {
return noHandle;
}
if (HintManager.isMasterRouteOnly()) {
return noHandle;
}
HintManager instance = null;
try {
try {
instance = HintManager.getInstance();
instance.setMasterRouteOnly();
} catch (Exception ex) {
//already has Instance but not masterRoute,this is upper user handle HintManager
return noHandle;
}
return Pair.of(true, invocation.proceed());
} finally {
if (instance != null) {
instance.close();
}
}
} catch (Throwable throwable) {
// do nothing
}
return noHandle;
}
}
4、代码中使用注解
- 这个需要写在Maper文件内
@ForceMaster
@Select("select id,a.schedule_id,a.section_id,a.main_link_id,a.child_link_id,a.created_time,a.work_id,a.cover,a.name from bc_homework a where learner_id = #{learner_id} and deleted = 0 AND STATUS IN (1,2)")
List<SimpleHomeWorkDO> selectListMainLink(@Param("learner_id") Integer userId);
2、多数据源切换
参考:https://github.com/zhongjingyun/example
(1)添加配置
sharding:
jdbc:
datasource:
names: db01-master,db01-slave,db02-master,db02-slave
db01-master:
jdbc-url: ${db01_master_url}
username: ${db01_master_username}
password: ${db01_master_password}
driver-class-name: com.mysql.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
db01-slave:
jdbc-url: ${db01_slave_username}
username: ${db01_slave_password}
password: root
driver-class-name: com.mysql.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
db02-master:
jdbc-url: ${db02_master_url}
username: ${db02_master_username}
password: ${db02_master_password}
driver-class-name: com.mysql.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
db02-slave:
jdbc-url: ${db02_slave_url}
username: ${db02_slave_username}
password: ${db02_slave_password}
driver-class-name: com.mysql.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
config:
props:
sql:
show: true
sharding:
master-slave-rules:
ds_db01:
masterDataSourceName: db01-master
slaveDataSourceNames:
- db01-slave
ds_db02:
masterDataSourceName: db02-master
slaveDataSourceNames:
- db02-slave
default-data-source-name: ds_db01
default-database-strategy:
hint:
algorithm-class-name: com.jiyu.conf.MyDatasourceRoutingAlgorithm
#这里自定义强制路由策略
(2)启动类配置
// JtaAutoConfiguration.class 这个必须配置
@SpringBootApplication(exclude = JtaAutoConfiguration.class,scanBasePackages = { "com.jiyu"})
// mybatis配合
@MapperScan(basePackages = "com.jiyu.dao")
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
(3)自定义强制路由
public class MyDatasourceRoutingAlgorithm implements HintShardingAlgorithm {
private static final Logger LOGGER = LoggerFactory.getLogger(MyDatasourceRoutingAlgorithm.class);
/**
* 自定义Hint 实现算法
* 能够保证绕过Sharding-JDBC SQL解析过程
* @param availableTargetNames
* @param shardingValue 不再从SQL 解析中获取值,而是直接通过下面代码参数指定
* HintManager hintManager = HintManager.getInstance();
* hintManager.setDatabaseShardingValue("ds_exchange");
* @return
*/
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ShardingValue shardingValue) {
System.out.println("shardingValue=" + shardingValue);
System.out.println("availableTargetNames=" + availableTargetNames);
List<String> shardingResult = new ArrayList<>();
ListShardingValue<String> tmpSharding = (ListShardingValue<String>) shardingValue;
for(String value : tmpSharding.getValues()){
if(availableTargetNames.contains(value)) {
shardingResult.add(value);
}
}
return shardingResult;
}
}
(4)AOP切换数据源
@Aspect
@Order(1)
@Component
public class DataSourceAop {
public static final Logger logger = LoggerFactory.getLogger(DataSourceAop.class);
/**
* db01库切入点,读写分离又shadingjdbc控制
*/
@Pointcut("execution(* com.jiyu.dao.db01..*.*(..))")
public void switchDataSourceDb01() {
}
@Before("switchDataSourceDb01()")
public void doDb01Before() {
HintManager hintManager = HintManager.getInstance();
hintManager.setDatabaseShardingValue("ds_db01");
}
/**
* 恢复默认数据源
*/
@After("switchDataSourceDb01()")
public void doDb01after() {
//清理掉当前设置的数据源,让默认的数据源不受影响
HintManager hintManager = HintManagerHolder.get();
if (hintManager != null) {
hintManager.close();
}
}
/**
* db02库切入点,读写分离又shadingjdbc控制
*/
@Pointcut("execution(* com.jiyu.dao.db02..*.*(..))")
public void switchDataSourceDb02() {
}
@Before("switchDataSourceDb02()")
public void doDb02Before() {
HintManager hintManager = HintManager.getInstance();
hintManager.setDatabaseShardingValue("ds_db02");
}
/**
* 恢复默认数据源
*/
@After("switchDataSourceDb02()")
public void doDb02after() {
//清理掉当前设置的数据源,让默认的数据源不受影响
HintManager hintManager = HintManagerHolder.get();
if (hintManager != null) {
hintManager.close();
}
}
}
(5)编写事务代码
@Service
public class ShardingTestServiceImpl implements ShardingTestService {
@Autowired
UserMapper userMapper;
@Autowired
AccountMapper accountMapper;
@Override
@ShardingTransactionType(TransactionType.XA)
@Transactional
public void testTransaction() {
int userResult = userMapper.updateUserName("jiyuge", 1);
int accountResult = accountMapper.accountAddBalance(BigDecimal.ONE, 1);
System.out.println("用户表返回数量:" + userResult);
System.out.println("账户表返回数量:"+ accountResult);
// throw new RuntimeException("我是一个异常");
}
}
二、分库分表以及分片策略
1、分库分表实现
(1)多逻辑数据源读写分离加分片配置
spring:
shardingsphere:
datasource:
names:
classroommaster,classroomslave,dscoursemaster
# course主数据源
dscoursemaster:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: ${datasourse_dscoursemaster_url}
username: ${datasourse_dscoursemaster_username}
password: ${datasourse_dscoursemaster_password}
# classroom主数据源
classroommaster:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: ${datasourse_classroommaster_url}
username: ${datasourse_classroommaster_username}
password: ${datasourse_classroommaster_password}
# classroom从数据源
classroomslave:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: ${datasourse_classroomslave_url}
username: ${datasourse_classroomslave_username}
password: ${datasourse_classroomslave_password}
sharding:
#默认选择那个逻辑数据源
default-data-source-name: classroom
#分片策略,那些表什么数据走什么逻辑数据眼
tables:
bc_homework:
actualDataNodes: classroom.bc_homework
master-slave-rules:
classroom:
load-balance-algorithm-type: round_robin # 最终的数据源名称
name: classroom # 主库数据源名称
master-data-source-name: classroommaster # 从库数据源名称列表,多个逗号分隔
slave-data-source-names: classroomslave
course:
load-balance-algorithm-type: round_robin
name: course
master-data-source-name: dscoursemaster
slave-data-source-names: dscoursemaster
props:
sql:
show: false # 开启SQL显示,默认false
2、分片策略
https://www.cnblogs.com/chengxy-nds/p/13919981.html
1、标准分片算法
(1)精准分库
精准分片适用于单个key,适用于=和IN的分片处理
select * from t_order where order_id = ? or order_id in (?,?,?,?);
按照分片key取余的方式进行分片算法
public class CustomerDBShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Long> shardingValue) {
//分片的长度
int size = databaseNames.size();
Long value = shardingValue.getValue();
//按照取余算法找到对应的分片(比较平均)
String pice = Math.floorMod(value,size)+"";
//按照hash取值(比较平均)
String price2 = Math.floorMod(value.hashCode(),size)+"";
//根据数值范围取值(可能不平均,但是表长度平均)
String price3 = Math.floorDiv(value,10000)+"";
for (String databaseName : databaseNames) {
if (databaseName.endsWith(pice)) {
return databaseName;
}
}
throw new IllegalArgumentException();
}
}
添加配置
- sharding-column=order_id 指定分片的key
- precise-algorithm-class-name 指定分片策略的类名
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=order_id
# 分库分片算法
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.sharding.algorithm.CustomerDBShardingAlgorithm
(2)精准分表算法
和精准分库类似
public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {
for (String tableName : tableNames) {
String value = shardingValue.getValue() % tableNames.size() + "";
if (tableName.endsWith(value)) {
return tableName;
}
}
throw new IllegalArgumentException();
}
}
添加配置
# 分表策略
# 分表分片健
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
# 分表算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.xiaofu.sharding.algorithm.tableAlgorithm.MyTablePreciseShardingAlgorithm
2、范围分片算法
范围分片也有类似精准分库和精准分表的算法
适用于
SELECT * FROM t_order where order_id BETWEEN 1 AND 100;
根据范围进行分库分表
public class CustomRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(Collection<String> tableNames, RangeShardingValue<Long> rangeShardingValue) {
Set<String> result = Sets.newLinkedHashSet();
//between的下限值和上限值
Long lower = rangeShardingValue.getValueRange().lowerEndpoint();
Long upper = rangeShardingValue.getValueRange().upperEndpoint();
int size = tableNames.size();
// 逐个值范围进行判断
for(long i = lower ; i<=upper ; i++){
for(String tableName : tableNames){
//根据I计算
if(tableName.endsWith( i % size+"")){
result.add(tableName);
}
}
}
return result;
}
}
添加配置
# 分表分片健
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
# 范围分片算法
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.range-algorithm-class-name=com.xiaofu.sharding.algorithm.CustomRangeShardingAlgorithm
3、复合分片策略
适用于多个key的查询,复合分片适用于数据量巨大的情况,比较少用到
SELECT * FROM t_order where user_id =0 and order_id = 1;
复合分片策略
/**
* @author xiaofu 公众号【程序员内点事】
* @description 自定义复合分库策略
* @date 2020/10/30 13:48
*/
public class MyDBComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Integer> {
@Override
public Collection<String> doSharding(Collection<String> databaseNames, ComplexKeysShardingValue<Integer> complexKeysShardingValue) {
// 得到每个分片健对应的值
Collection<Integer> orderIdValues = this.getShardingValue(complexKeysShardingValue, "order_id");
Collection<Integer> userIdValues = this.getShardingValue(complexKeysShardingValue, "user_id");
List<String> shardingSuffix = new ArrayList<>();
// 对两个分片健同时取模的方式分库
for (Integer userId : userIdValues) {
for (Integer orderId : orderIdValues) {
//这里要求表名的后缀是根据userId和orderId的取模值匹配得到的
//例如 t_order_1_1
String suffix = userId % 2 + "_" + orderId % 2;
for (String databaseName : databaseNames) {
if (databaseName.endsWith(suffix)) {
shardingSuffix.add(databaseName);
}
}
}
}
return shardingSuffix;
}
private Collection<Integer> getShardingValue(ComplexKeysShardingValue<Integer> shardingValues, final String key) {
Collection<Integer> valueSet = new ArrayList<>();
Map<String, Collection<Integer>> columnNameAndShardingValuesMap = shardingValues.getColumnNameAndShardingValuesMap();
if (columnNameAndShardingValuesMap.containsKey(key)) {
valueSet.addAll(columnNameAndShardingValuesMap.get(key));
}
return valueSet;
}
}
添加配置
### 分库策略
# order_id,user_id 同时作为分库分片健
spring.shardingsphere.sharding.tables.t_order.database-strategy.complex.sharding-column=order_id,user_id
# 复合分片算法
spring.shardingsphere.sharding.tables.t_order.database-strategy.complex.algorithm-class-name=com.xiaofu.sharding.algorithm.dbAlgorithm.MyDBComplexKeysShardingAlgorithm
4、表达式分片
采用纯表达式的方式配置即可
- algorithm-expression=ds-$->{order_id % 2} 即表示按照订单ID取模
# 行表达式分片键
sharding.jdbc.config.sharding.tables.t_order.database-strategy.inline.sharding-column=order_id
# 表达式算法
sharding.jdbc.config.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds-$->{order_id % 2}
5、Hint分片策略
相比于上面几种分片策略稍有不同,这种分片策略无需配置分片健,分片健值也不再从 SQL中解析,而是由外部指定分片信息,让 SQL在指定的分库、分表中执行。ShardingSphere 通过 Hint API实现指定操作,实际上就是把分片规则tablerule 、databaserule由集中配置变成了个性化配置。
public class CustomHintShardingAlogrithm implements HintShardingAlgorithm<String> {
@Override
public Collection<String> doSharding(Collection<String> tableNames, HintShardingValue<String> hintShardingValue) {
Collection<String> result = Lists.newArrayList();
int size = tableNames.size();
for (String tableName : tableNames) {
for (String shardingValue : hintShardingValue.getValues()) {
String subfix = String.valueOf(Long.valueOf(shardingValue) % size);
if (tableName.endsWith(subfix)) {
result.add(tableName);
}
}
}
return result;
}
}
代码中指定调用的库
// 清除掉上一次的规则,否则会报错
HintManager.clear();
// HintManager API 工具类实例
HintManager hintManager = HintManager.getInstance();
// 直接指定对应具体的数据库
hintManager.addDatabaseShardingValue("ds",0);
// 设置表的分片健,
hintManager.addTableShardingValue("t_order" , 0);
hintManager.addTableShardingValue("t_order" , 1);
hintManager.addTableShardingValue("t_order" , 2);
// 在读写分离数据库中,Hint 可以强制读主库
hintManager.setMasterRouteOnly();
添加配置
# Hint分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.hint.algorithm-class-name=com.xiaofu.sharding.algorithm.tableAlgorithm.MyTableHintShardingAlgorithm
6、扩展:按照年月日分片策略
按照时间范围分片,注意这里采用的是user_id的雪花算法逆解析得到的时间
public class TimeRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> {
private DateTimeFormatter dateformat = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<String>();
Range<Long> shardingKey = shardingValue.getValueRange();
long startShardingKey = shardingKey.lowerEndpoint();
long endShardingKey = shardingKey.upperEndpoint();
//获取到开始时间戳
String startTimeString = ParaseShardingKeyTool.getYearAndMonth(startShardingKey);
//获取结束时间戳
String endTimeString = ParaseShardingKeyTool.getYearAndMonth(endShardingKey);
Calendar cal = Calendar.getInstance();
//获取开始的年月
//时间戳
LocalDateTime startLocalDate = GenericTool.getLocalDate(startTimeString);
//获取结束的年月
LocalDateTime endLocalDate = GenericTool.getLocalDate(endTimeString);
//进行判断 获取跨月份的表 如201901,201902,201903 三个月的表
//目前只支持同一年内的查询,跨年不支持
int end = Integer.valueOf(dateformat.format(endLocalDate));
int start = Integer.valueOf(dateformat.format(startLocalDate));
while(start <= end){
StringBuffer tableName = new StringBuffer();
tableName.append(shardingValue.getLogicTableName())
.append("_").append(start);
result.add(tableName.toString());
start++;
}
return result;
}
}
public class ParaseShardingKeyTool {
private static DateTimeFormatter yearAndMonth = DateTimeFormatter.ofPattern("yyyyMM");
private static DateTimeFormatter year = DateTimeFormatter.ofPattern("yyyy");
public static String getYearAndMonth(long shardingKey){
Instant instant = Instant.ofEpochMilli(DefaultKeyGenerator.EPOCH+(Long.valueOf(shardingKey+"")>>22));
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
return yearAndMonth.format(localDateTime);
}
public static String getYear(long shardingKey){
Instant instant = Instant.ofEpochMilli(DefaultKeyGenerator.EPOCH+(Long.valueOf(shardingKey+"")>>22));
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
return year.format(localDateTime);
}
public static void main(String[] args) {
DefaultKeyGenerator defaultKeyGenerator = new DefaultKeyGenerator();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMM");
System.out.println(simpleDateFormat.format(System.currentTimeMillis()));
System.out.println(ParaseShardingKeyTool.getYearAndMonth(Long.valueOf(defaultKeyGenerator.generateKey()+"")));
System.out.println(ParaseShardingKeyTool.getYearAndMonth(Long.valueOf(defaultKeyGenerator.generateKey()+"")));
}
}
按照单月的分表算法
public class TimeShardingTableAlgorithm implements PreciseShardingAlgorithm<Long> {
private DateTimeFormatter dateformat = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
StringBuffer tableName = new StringBuffer();
tableName.append(shardingValue.getLogicTableName())
.append("_").append(ParaseShardingKeyTool.getYearAndMonth(shardingValue.getValue()));
return tableName.toString();
}
}
直接按照日期的方式分片
public class USerTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date>{
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> preciseShardingValue) {
StringBuffer tableName = new StringBuffer();
tableName.append(preciseShardingValue.getLogicTableName())
.append("_").append(DateUtil.date2Str(preciseShardingValue.getValue(), DateUtil.YEAR_MONTH_NUMBER));
return tableName.toString();
}
}
三、查询优化
1、数据库连接池设置与需要注意的点
(1)使用durid连接池
spring:
shardingsphere:
datasource:
names: cfdb0, cfdb1, cfdb2
cfdb0:
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 5 #连接池初始化连接数
minIdle: 3 #连接池最小连接数
maxActive: 20 #连接池最大连接数
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://XX.xx.xx.xxx:3306/cfdb_0?useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: xxx
password: xxxx
(2)查询时需要注意的点
即涉及到有分库分表的查询条件中,一定要使用分表键
2、采用流式处理+归并排序避免内存过量占用
3、Sharding-JDBC对仅落至单分片的查询进行进一步优化
四、数据迁移,数据扩容,异构存储
https://blog.csdn.net/beichen8641/article/details/106924189
https://blog.csdn.net/HTslide/article/details/108258562
1、数据迁移
Sharding-Proxy+Sharding-scaling实现不停服数据迁移
sharding-proxy 的相关理论&使用文档参考官网(https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-proxy/)
sharding-scaling 的相关理论&使用文档参考官网(https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-scaling/)
相关步骤:
1、确认数据库是mysql