vitess分库分表 分库分表插件

1、什么时候需要分库分表？

1. 数据量大，并发量高，数据库压力较大（这个没什么好说的，到这一步分库分表是很自然的事情）
2. 一个系统，多种数据源：前两天接到产品经理一个需求，要给现有的一个非常基础又较为庞大的老爷服务加个功能：要能够给另外一套模拟生产的服务提供完全一样的接口，内部使用完全一样的逻辑，然后要数据隔离，最好还能对原有代码低入侵，接口加个参数判断下就能拿到两种数据。

作为一个程序员，我的宗旨是对于老古董，能不看就不看。所以经过我的琢磨，加上最近刚好在学习分库分表这块的东西，那就分库分表吧。

2、如何实现分库分表

2.1、原理

根据请求动态路由到不同的数据库，不同的表。

2.2、实现

2.2.1、配置多数据源

实现自己的MultiDataSource，继承AbstractRoutingDataSource抽象类，实现determineCurrentLookupKey()方法。

public class MultiDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return MultiDataSourceHolder.getDataSourceKey();
    }
}

public class MultiDataSourceHolder {
    
    private static final ThreadLocal<String> dataSourceHolder = new ThreadLocal<>();
    private static final ThreadLocal<String> tableIndexHolder = new ThreadLocal<>();

    /**
     * 保存数据源的key
     * @param dsKey
     */
    public static void setDataSourceHolder(String dsKey){
        dataSourceHolder.set(dsKey);
    }

    /**
     * 从threadLocal中取出key
     * @return
     */
    public static String getDataSourceKey(){
        return dataSourceHolder.get();
    }

    /**
     * 清除key
     */
    public static void clearDataSourceKey(){
        dataSourceHolder.remove();
    }

    /**
     * 保存表索引
     * @param tableIndex
     */
    public static void setTableIndexHolder(String tableIndex){
        tableIndexHolder.set(tableIndex);
    }

    /**
     * 从threadLocal中取出索引
     * @return
     */
    public static String getTableIndex(){
        return tableIndexHolder.get();
    }

    /**
     * 清除表索引
     */
    public static void clearTableIndex(){
        tableIndexHolder.remove();
    }
}

2.2.2、配置mybatis

• 配置mybatis的SqlSessionFactory，指定数据源为自己配置的多数据源
• 配置SqlSessionTemplate,指定数据SqlSessionFactory的工程

@EnableConfigurationProperties({DsRoutingSetProperties.class, DruidProperties.class})
@Configuration
public class DataSourceConfig {
    @Autowired
    private DsRoutingSetProperties dsRoutingSetProperties;

    @Autowired
    private DruidProperties druidProperties;

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.druid00")
    public DataSource dataSource00(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUsername(druidProperties.getDruid00username());
        dataSource.setPassword(druidProperties.getDruid00password());
        dataSource.setUrl(druidProperties.getDruid00jdbcUrl());
        dataSource.setDriverClassName(druidProperties.getDruid00driverClass());

        return dataSource;
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.druid01")
    public DataSource dataSource01(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUsername(druidProperties.getDruid01username());
        dataSource.setPassword(druidProperties.getDruid01password());
        dataSource.setUrl(druidProperties.getDruid01jdbcUrl());
        dataSource.setDriverClassName(druidProperties.getDruid01driverClass());

        return dataSource;
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.druid02")
    public DataSource dataSource02(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUsername(druidProperties.getDruid02username());
        dataSource.setPassword(druidProperties.getDruid02password());
        dataSource.setUrl(druidProperties.getDruid02jdbcUrl());
        dataSource.setDriverClassName(druidProperties.getDruid02driverClass());

        return dataSource;
    }

    // 注入我们自己的datasource
    @Bean("multiDataSource")
    public MultiDataSource multiDataSource(){
        MultiDataSource multiDataSource = new MultiDataSource();

        Map<Object,Object> targetDataSources = new HashMap<>();
        targetDataSources.put("dataSource00",dataSource00());
        targetDataSources.put("dataSource01",dataSource01());
        targetDataSources.put("dataSource02",dataSource02());

        multiDataSource.setTargetDataSources(targetDataSources);

        multiDataSource.setDefaultTargetDataSource(dataSource00());

        Map<Integer,String> setMappings = new HashMap<>();
        setMappings.put(0,"dataSource00");
        setMappings.put(1,"dataSource01");
        setMappings.put(2,"dataSource02");
        dsRoutingSetProperties.setDataSourceKeysMapping(setMappings);

        return multiDataSource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("multiDataSource") MultiDataSource multiDataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(multiDataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mybatis/mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public DataSourceTransactionManager transactionManager(@Qualifier("multiDataSource") MultiDataSource multiDataSource){
        return new DataSourceTransactionManager(multiDataSource);
    }
}

2.2.3、配置分库分表策略

• 根据配置文件的allen.dsroutingset.routingStrategy 属，选择要装配的策略的Bean；
• 调用AbstractRouting.afterPropertiesSet()方法对策略配置进行检查

// 装配不同策略的配置类
@Configuration
public class RoutingStrategyConfig {
    @Bean
    @ConditionalOnProperty(prefix = "allen.dsroutingset",name = "routingStrategy",havingValue = "ROUTING_DS_TABLE_STRATEGY")
    public Routing routingDsAndTbStrategy(){
        return new RoutingDsAndTableStrategy();
    }

    @Bean
    @ConditionalOnProperty(prefix = "allen.dsroutingset",name = "routingStrategy",havingValue = "ROUTING_DS_STRATEGY")
    public Routing routingDsStrategy(){
        return new RoutingDsStrategy();
    }

    @Bean
    @ConditionalOnProperty(prefix = "allen.dsroutingset",name = "routingStrategy",havingValue = "ROUTING_TABLE_STRATEGY")
    public Routing routingTableStrategy(){
        return new RoutingTbStrategy();
    }
}

// 检查配置策略与配置参数是否匹配
@Slf4j
@Data
public abstract class AbstractRouting implements Routing, InitializingBean {

    @Autowired
    private DsRoutingSetProperties dsRoutingSetProperties;

    public Integer getRoutingFieldHashCode(String routingField) {
        return Math.abs(routingField.hashCode());
    }

    public String genFormatTableSuffix(Integer tableIndex) {
        StringBuilder stringBuffer = new StringBuilder(dsRoutingSetProperties.getTableSuffixConnect());

        try {
            stringBuffer.append(String.format(dsRoutingSetProperties.getTableSuffixStyle(),tableIndex));
        } catch (Exception e) {
            log.error("格式化表后缀异常:{}",dsRoutingSetProperties.getTableSuffixStyle());
            throw new FormatTableSuffixException();
        }
        return stringBuffer.toString();
    }

    public void afterPropertiesSet() throws LoadRoutingStrategyUnMatchException {
        switch (dsRoutingSetProperties.getRoutingStrategy()) {
            case RoutingConstant.ROUTING_DS_TABLE_STRATEGY:
                checkRoutingDsTableStrategyConfig();
                break;
            case RoutingConstant.ROUTING_DS_STRATEGY:
                checkRoutingDsStrategyConfig();
                break;
            case RoutingConstant.ROUTING_TABLE_STRATEGY:
                checkRoutingTableStrategyConfig();
                break;
        }
    }

    /**
     * 检查多库 多表配置
     */
    private void checkRoutingDsTableStrategyConfig() {
        if(dsRoutingSetProperties.getTableNum()<=1 ||dsRoutingSetProperties.getDataSourceNum()<=1){
            log.error("你的配置项routingStrategy:{}是多库多表配置,数据库个数>1," +
                            "每一个库中表的个数必须>1,您的配置:数据库个数:{},表的个数:{}",dsRoutingSetProperties.getRoutingStrategy(),
                    dsRoutingSetProperties.getDataSourceNum(),dsRoutingSetProperties.getTableNum());
            throw new LoadRoutingStrategyUnMatchException();
        }
    }

    /**
     * 检查多库一表的路由配置项
     */
    private void checkRoutingDsStrategyConfig() {
        if(dsRoutingSetProperties.getTableNum()!=1 ||dsRoutingSetProperties.getDataSourceNum()<=1){
            log.error("你的配置项routingStrategy:{}是多库一表配置,数据库个数>1," +
                            "每一个库中表的个数必须=1,您的配置:数据库个数:{},表的个数:{}",dsRoutingSetProperties.getRoutingStrategy(),
                    dsRoutingSetProperties.getDataSourceNum(),dsRoutingSetProperties.getTableNum());
            throw new LoadRoutingStrategyUnMatchException();
        }
    }

    /**
     * 检查一库多表的路由配置项
     */
    private void checkRoutingTableStrategyConfig() {
        if(dsRoutingSetProperties.getTableNum()<=1 ||dsRoutingSetProperties.getDataSourceNum()!=1){
            log.error("你的配置项routingStrategy:{}是一库多表配置,数据库个数=1," +
                            "每一个库中表的个数必须>1,您的配置:数据库个数:{},表的个数:{}",dsRoutingSetProperties.getRoutingStrategy(),
                    dsRoutingSetProperties.getDataSourceNum(),dsRoutingSetProperties.getTableNum());
            throw new LoadRoutingStrategyUnMatchException();
        }
    }
}

2.2.4、AOP 拦截器

• 创建一个注解@Router
• 编写该注解的增强器(@Before,@After…)

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface Router {
    String routingField() default RoutingConstant.DEFAULT_ROUTING_FIELD;
}

@Component
@Slf4j
@Aspect
public class RoutingAspect {

    @Autowired
    private Routing routing;

    @Pointcut("@annotation(cn.allen.multidatasource.mdscomponent.annotation.Router)")
    public void pointCut(){}

    @Before("pointCut()")
    public void before(JoinPoint joinPoint) throws ParamsNotContainsRoutingFieldException,LoadRoutingStrategyUnMatchException, RoutingFieldArgsIsNullException,
            IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        // 获取方法调用名称
        Method method = getInvokeMethod(joinPoint);

        // 获取方法指定的注解
        Router router = method.getAnnotation(Router.class);
        // 获取指定的路由key
        String routingField = router.routingField();

        // 获取方法入参
        Object[] args = joinPoint.getArgs();

        boolean havingRoutingField = false;

        if(args!=null && args.length>0){
            for (Object arg : args) {
                String routingFieldValue = BeanUtils.getProperty(arg, routingField);
                if (!StringUtils.isEmpty(routingFieldValue)) {
                    String dbKey = routing.calDataSourceKey(routingFieldValue);
                    String tableIndex = routing.calTableKey(routingFieldValue);
                    log.info("选择的dbKey是:{},tableKey是:{}", dbKey, tableIndex);
                    havingRoutingField = true;
                    break;
                }
            }

            if (!havingRoutingField){
                log.warn("入参{}中没有包含路由字段:{}",args,routingField);
                throw new ParamsNotContainsRoutingFieldException();
            }
        }
    }

    private Method getInvokeMethod(JoinPoint joinPoint){
        Signature signature = joinPoint.getSignature();
        MethodSignature methodSignature = (MethodSignature) signature;
        return methodSignature.getMethod();
    }

    /**
     * 清除线程缓存
     * @param joinPoint
     */
    @After("pointCut()")
    public void methodAfter(JoinPoint joinPoint){
        MultiDataSourceHolder.clearDataSourceKey();
        MultiDataSourceHolder.clearTableIndex();
    }
}

2.3、使用分库分表插件

首先编写一个简单的一个web项目，这里不再赘述。

2.4.1、获取表索引

将需要分库分表的数据的实体继承一个base类，引入表后缀字段，用于拼装成对应的数据库

public class BaseDomain {
    private String tableSuffix;

    public String getTableSuffix(){
        this.tableSuffix = MultiDataSourceHolder.getTableIndex();
        return tableSuffix;
    }

    public void setTableSuffix(String tableSuffix){
        this.tableSuffix = tableSuffix;
    }
}

2.4.2、修改mybatis的xml文件

将xml文件的数据库表名修改为可拼接后缀的。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.allen.multidatasource.fund.dao.FundMapper">


    <insert id="add" parameterType="cn.allen.multidatasource.fund.entity.Fund">
        insert into fund${tableSuffix}(fund_code,fund_name) values (#{fundCode},#{fundName})
    </insert>
    <delete id="delete" parameterType="java.lang.String">
        delete from fund${tableSuffix} where fund_code = #{fundCode}
    </delete>
    <update id="update" parameterType="cn.allen.multidatasource.fund.entity.Fund">
        update fund${tableSuffix} set fund_name = #{fundName} where fund_code = #{fundCode}
    </update>
    <select id="get" parameterType="java.lang.String">
        select * from fund${tableSuffix} where fund_code = #{fundCode}
    </select>

</mapper>

3、总结

至此，该插件的原理以及部分实现已经讲完了，想要了解详细的实现细节，可以参照我的项目demo。

另外，附上一张该插件启动，及工作的流程图。