亲测可用 springboot+mybatis-plus+mysql 动态路由数据源实现分库

    实际业务中会发现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以上。

完成！