实际业务中会发现mysql单表数据量达到500万以后,性能下降厉害,故如果系统的数据量较大则需要分表分库。我比较偷懒,直接分库,用springboot+mybatis-plus+mysql架构,模拟用户表进行分库.
分库规则:根据用户手机号的最后第二位数字,进行奇偶判断,分为2个库,也可以分成更多的库。
动态路由采用TargetDataSource注解,在service层进行拦截,TargetDataSource可以加在class上,也可以加在具体的方法上。
根据每个entity的特性编写具体的路由规则,切换不同的数据源,不需要依赖任何的中间件,灵活且配置简单。
参考文章:
SpringBoot整合MyBatisPlus配置动态数据源
特别感谢上面文章的作者,让我成功试验出基于DS注解的动态数据源切换。我在此基础上,我做了一些简单的改动。
1、注解类TargetDataSource,有直接指定(value)和或者按手机号动态计算(location)两种方式。我主要用到了按手机号动态计算方式。
@Target({ElementType.TYPE,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
java.lang.String value() default "";
java.lang.Class<?> location() default java.lang.Void.class;
}
2、注解加在service层的实现类中
@Service
@TargetDataSource(location = DsChangableImpl.class)
public class TestUserClassServiceImpl implements TestUserClassService {
@Autowired
private TestUserClassMapper testUserClassMapper;
@Override
public void add(TestUserClass user){
testUserClassMapper.insert(user);
}
@Override
public TestUserClass getByMobile(TestUserReq req){
QueryWrapper<TestUserClass> query=new QueryWrapper<>();
query.eq("mobile",req.getMobile());
return testUserClassMapper.selectOne(query);
}
}
3、DsChangableImpl是具体的计算规则类
BaseBean只有手机号一个属性
public class BaseBean {
private String mobile;
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
}
接口
public interface DsChangable {
java.lang.String getLocationkey(java.lang.Object[] objects);
}
实现类
public class DsChangableImpl implements DsChangable {
public static final Logger LOGGER = LoggerFactory.getLogger(DsChangableImpl.class);
private static final int slaveDsCount = 2;
@Override
public String getLocationkey(Object[] args) {
//判断入参是否存在并继承自BaseBean
if (null == args || args.length == 0 || !(args[0] instanceof BaseBean)) {
return null;
}
String mobile = ((BaseBean) args[0]).getMobile();
if (StringUtils.isNotBlank(mobile) && mobile.length() > 1) {
//取最后第二位
int lastButOne = Integer.valueOf(mobile.substring(mobile.length() - 2, mobile.length() - 1));
LOGGER.debug("change to slave_" + lastButOne % slaveDsCount);
return String.valueOf("slave_" + lastButOne % slaveDsCount);//奇偶计算
}
return null;
}
}
4、下面将启动和切面类进行偷梁换柱,DynamicDataSourceAnnotationAdvisor稍许改造
import lombok.NonNull;
import org.aopalliance.aop.Advice;
import org.springframework.aop.ClassFilter;
import org.springframework.aop.MethodMatcher;
import org.springframework.aop.Pointcut;
import org.springframework.aop.support.AbstractPointcutAdvisor;
import org.springframework.aop.support.AopUtils;
import org.springframework.aop.support.ComposablePointcut;
import org.springframework.aop.support.StaticMethodMatcher;
import org.springframework.aop.support.annotation.AnnotationMatchingPointcut;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.BeanFactoryAware;
import org.springframework.core.annotation.AnnotatedElementUtils;
import org.springframework.util.Assert;
import java.lang.annotation.Annotation;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
public class MyDynamicDataSourceAnnotationAdvisor extends AbstractPointcutAdvisor implements BeanFactoryAware {
private final Advice advice;
private final Pointcut pointcut;
public MyDynamicDataSourceAnnotationAdvisor(@NonNull MyDynamicDataSourceAnnotationInterceptor dynamicDataSourceAnnotationInterceptor) {
this.advice = dynamicDataSourceAnnotationInterceptor;
this.pointcut = buildPointcut();
}
@Override
public Pointcut getPointcut() {
return this.pointcut;
}
@Override
public Advice getAdvice() {
return this.advice;
}
@Override
public void setBeanFactory(BeanFactory beanFactory) throws BeansException {
if (this.advice instanceof BeanFactoryAware) {
((BeanFactoryAware) this.advice).setBeanFactory(beanFactory);
}
}
private Pointcut buildPointcut() {
Pointcut cpc = new AnnotationMatchingPointcut(TargetDataSource.class, true);
Pointcut mpc = new AnnotationMethodPoint(TargetDataSource.class);
return new ComposablePointcut(cpc).union(mpc);
}
/**
* In order to be compatible with the spring lower than 5.0
*/
private static class AnnotationMethodPoint implements Pointcut {
private final Class<? extends Annotation> annotationType;
public AnnotationMethodPoint(Class<? extends Annotation> annotationType) {
Assert.notNull(annotationType, "Annotation type must not be null");
this.annotationType = annotationType;
}
@Override
public ClassFilter getClassFilter() {
return ClassFilter.TRUE;
}
@Override
public MethodMatcher getMethodMatcher() {
return new AnnotationMethodMatcher(annotationType);
}
private static class AnnotationMethodMatcher extends StaticMethodMatcher {
private final Class<? extends Annotation> annotationType;
public AnnotationMethodMatcher(Class<? extends Annotation> annotationType) {
this.annotationType = annotationType;
}
@Override
public boolean matches(Method method, Class<?> targetClass) {
if (matchesMethod(method)) {
return true;
}
// Proxy classes never have annotations on their redeclared methods.
if (Proxy.isProxyClass(targetClass)) {
return false;
}
// The method may be on an interface, so let's check on the target class as well.
Method specificMethod = AopUtils.getMostSpecificMethod(method, targetClass);
return (specificMethod != method && matchesMethod(specificMethod));
}
private boolean matchesMethod(Method method) {
return AnnotatedElementUtils.hasAnnotation(method, this.annotationType);
}
}
}
}
5、重要:DynamicDataSourceAnnotationInterceptor改造
import com.baomidou.dynamic.datasource.processor.DsProcessor;
import com.baomidou.dynamic.datasource.support.DataSourceClassResolver;
import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import org.aopalliance.intercept.MethodInterceptor;
import org.aopalliance.intercept.MethodInvocation;
import java.lang.reflect.Method;
public class MyDynamicDataSourceAnnotationInterceptor implements MethodInterceptor {
private static final String DYNAMIC_PREFIX = "#";
private final DataSourceClassResolver dataSourceClassResolver;
private final DsProcessor dsProcessor;
public MyDynamicDataSourceAnnotationInterceptor(Boolean allowedPublicOnly, DsProcessor dsProcessor) {
dataSourceClassResolver = new DataSourceClassResolver(allowedPublicOnly);
this.dsProcessor = dsProcessor;
}
@Override
public Object invoke(MethodInvocation methodInvocation) throws Throwable {
//获取注解,注意没有判断为空
TargetDataSource targetDataSource = getAnnotation(methodInvocation.getMethod());
//实例化路由计算类
DsChangable dsChangable = (DsChangable) targetDataSource.location().newInstance();
//根据入参计算出DataSource名称
String dsKey= dsChangable.getLocationkey( methodInvocation.getArguments());
//切换数据源
DynamicDataSourceContextHolder.push(dsKey);
try {
return methodInvocation.proceed();
} finally {
DynamicDataSourceContextHolder.poll();
}
}
private String determineDatasourceKey(MethodInvocation invocation) {
String key = dataSourceClassResolver.findDSKey(invocation.getMethod(), invocation.getThis());
return (!key.isEmpty() && key.startsWith(DYNAMIC_PREFIX)) ? dsProcessor.determineDatasource(invocation, key) : key;
}
//主要改动点:优先获取方法上的注解,再获取类上的注解,都没有就返回null,取master数据源
private TargetDataSource getAnnotation(Method method){
if(method.isAnnotationPresent(TargetDataSource.class)){
return method.getAnnotation(TargetDataSource.class);
}
if(method.getDeclaringClass().isAnnotationPresent(TargetDataSource.class)){
return method.getDeclaringClass().getAnnotation(TargetDataSource.class);
}
return null;
}
}
6:、DynamicDataSourceAutoConfiguration稍许改造
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.aop.DynamicLocalTransactionAdvisor;
import com.baomidou.dynamic.datasource.processor.DsHeaderProcessor;
import com.baomidou.dynamic.datasource.processor.DsProcessor;
import com.baomidou.dynamic.datasource.processor.DsSessionProcessor;
import com.baomidou.dynamic.datasource.processor.DsSpelExpressionProcessor;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.YmlDynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceCreatorAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceHealthCheckConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourcePropertiesCustomizer;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.druid.DruidDynamicDataSourceConfiguration;
import com.baomidou.dynamic.datasource.strategy.DynamicDataSourceStrategy;
import lombok.extern.slf4j.Slf4j;
import org.springframework.aop.Advisor;
import org.springframework.aop.aspectj.AspectJExpressionPointcut;
import org.springframework.aop.support.DefaultPointcutAdvisor;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.config.BeanDefinition;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.context.annotation.Role;
import org.springframework.context.expression.BeanFactoryResolver;
import org.springframework.util.CollectionUtils;
import javax.sql.DataSource;
import java.util.List;
@Slf4j
@Configuration
@EnableConfigurationProperties(DynamicDataSourceProperties.class)
@AutoConfigureBefore(value = DataSourceAutoConfiguration.class, name = "com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure")
@Import(value = {DruidDynamicDataSourceConfiguration.class, DynamicDataSourceCreatorAutoConfiguration.class, DynamicDataSourceHealthCheckConfiguration.class})
@ConditionalOnProperty(prefix = DynamicDataSourceProperties.PREFIX, name = "enabled", havingValue = "true", matchIfMissing = true)
public class MyDynamicDataSourceAutoConfiguration implements InitializingBean {
private final DynamicDataSourceProperties properties;
private final List<DynamicDataSourcePropertiesCustomizer> dataSourcePropertiesCustomizers;
//改动点
public MyDynamicDataSourceAutoConfiguration(
DynamicDataSourceProperties properties,
ObjectProvider<List<DynamicDataSourcePropertiesCustomizer>> dataSourcePropertiesCustomizers) {
this.properties = properties;
this.dataSourcePropertiesCustomizers = dataSourcePropertiesCustomizers.getIfAvailable();
}
//改动点
@Bean
public DynamicDataSourceProvider ymlDynamicDataSourceProvider() {
return new YmlDynamicDataSourceProvider(properties.getDatasource());
}
@Bean
@ConditionalOnMissingBean
public DataSource dataSource() {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(properties.getPrimary());
dataSource.setStrict(properties.getStrict());
dataSource.setStrategy(properties.getStrategy());
dataSource.setP6spy(properties.getP6spy());
dataSource.setSeata(properties.getSeata());
return dataSource;
}
@Role(value = BeanDefinition.ROLE_INFRASTRUCTURE)
@Bean
//改动点
public Advisor dynamicDatasourceAnnotationAdvisor(DsProcessor dsProcessor) {
MyDynamicDataSourceAnnotationInterceptor interceptor = new MyDynamicDataSourceAnnotationInterceptor(properties.isAllowedPublicOnly(), dsProcessor);
MyDynamicDataSourceAnnotationAdvisor advisor = new MyDynamicDataSourceAnnotationAdvisor(interceptor);
advisor.setOrder(properties.getOrder());
return advisor;
}
@Role(value = BeanDefinition.ROLE_INFRASTRUCTURE)
@ConditionalOnProperty(prefix = DynamicDataSourceProperties.PREFIX, name = "seata", havingValue = "false", matchIfMissing = true)
@Bean
public Advisor dynamicTransactionAdvisor() {
AspectJExpressionPointcut pointcut = new AspectJExpressionPointcut();
pointcut.setExpression("@annotation(com.baomidou.dynamic.datasource.annotation.DSTransactional)");
return new DefaultPointcutAdvisor(pointcut, new DynamicLocalTransactionAdvisor());
}
@Bean
@ConditionalOnMissingBean
public DsProcessor dsProcessor(BeanFactory beanFactory) {
DsHeaderProcessor headerProcessor = new DsHeaderProcessor();
DsSessionProcessor sessionProcessor = new DsSessionProcessor();
DsSpelExpressionProcessor spelExpressionProcessor = new DsSpelExpressionProcessor();
spelExpressionProcessor.setBeanResolver(new BeanFactoryResolver(beanFactory));
headerProcessor.setNextProcessor(sessionProcessor);
sessionProcessor.setNextProcessor(spelExpressionProcessor);
return headerProcessor;
}
@Override
public void afterPropertiesSet() {
if (!CollectionUtils.isEmpty(dataSourcePropertiesCustomizers)) {
for (DynamicDataSourcePropertiesCustomizer customizer : dataSourcePropertiesCustomizers) {
customizer.customize(properties);
}
}
}
}
排除原有的DynamicDataSourceAutoConfiguration类,加载我的亲信:MyDynamicDataSourceAutoConfiguration
application.properties配置内容,一主二从。其他的entity、controller略
测试成功
curl http://localhost:18088/userTest/addAnnotationOnClass?mobile=13700000041
结果
slave_0 用户表存入了手机号最后第二位是偶数的用户
slave_1的用户表存入了手机号最后第二位是奇数的用户
压力测试:
随便压了一下,写入TPS2000以上。
完成!