基于拦截器的水平分表实现

水平分表的场景

当单表数据激增，比如mysql单表数据量超过千万级别,查询速度会变慢。当数据继续增长，就需要使用到分库分表。
根据业务量增加表的数量，来提升查询效率。这里提到是水平分表，只考虑数据量增长，当字段较长，可以考虑垂直分表，按照字段将一张表拆成多张表进行存储。

水平分表实现原理

如图，分表的是根据sql请求中参数，或参数组合进行数据标识，我见过流水业务频繁的按照日期分表，他就根据创建时间来分表，分片策略算法，一般按需实现 。在sharding-jdbc中实现分片接口，doSharding()的方法。就通过上个节点中得到的标识进行运算，获取表名。

基于拦截器的分表实现

基于上述流程，通过mybatis的拦截器，便可以实现。在执行sql前，获取到数据标识。在执行sql前进行拦截，
将sql拦截后，替换当中的表。然后再进行重新绑定sql，推进调用链。

定义拦截器

/**
 * 分表拦截器
 *
 * @author machenike
 */
@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}))
public class ShardingInterceptorPlugin implements Interceptor {

    private static final Logger logger = LoggerFactory.getLogger(ShardingInterceptorPlugin.class);

    private static final String META_OBJECT_KEY_BOUND_SQL = "delegate.boundSql.sql";

    private static ThreadLocal<Object> SHARDINGG_SIGN = new ThreadLocal<Object>();

    private Set<String> shardingTables = new HashSet<String>();

    private ShardingStrategy shardingStrategy;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //获取命令handler取得
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        //原来应该执行的sql吧
        String sql = statementHandler.getBoundSql().getSql();


        // 获取statementHandler包装类
        MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler);

        // 分离代理对象链
        while (MetaObjectHandler.hasGetter("h")) {
            Object obj = MetaObjectHandler.getValue("h");
            MetaObjectHandler = SystemMetaObject.forObject(obj);
        }
        while (MetaObjectHandler.hasGetter("target")) {
            Object obj = MetaObjectHandler.getValue("target");
            MetaObjectHandler = SystemMetaObject.forObject(obj);
        }

        //sql不满足分片请求时
        if (!isMatch(sql) || shardingTables.isEmpty()) {
            return invocation.proceed();
        }
        logger.debug("tableName match,start intercept");
        logger.debug(">> originSql:{}", sql);

        String shardingSql = buildSql(sql, SHARDINGG_SIGN.get());
        logger.debug(">> shardingSql:{}", shardingSql);
        //重新分表sql
        MetaObjectHandler.setValue(META_OBJECT_KEY_BOUND_SQL, shardingSql);
        //推进拦截器调用链
        return invocation.proceed();
    }

    private String buildSql(String sql, Object sigin) {
        Set<String> tableSet = getSqlTables(sql);
        for (String table : tableSet) {
            sql = sql.replaceAll(table, shardingStrategy.doSharding(table, SHARDINGG_SIGN.get()));
        }
        return sql;
    }

    private boolean isMatch(String sql) {
        for (String tableName : shardingTables) {
            if (sql.contains(tableName)) {
                return true;
            }
        }
        return false;
    }


    private Set<String> getSqlTables(String sql) {
        Set<String> set = new HashSet<String>();
        for (String table : shardingTables) {
            if (sql.contains(table)) {
                set.add(table);
            }
        }
        return set;
    }

    public static void setCurrentSign(Object sigin) {
        SHARDINGG_SIGN.set(sigin);
    }

    public ShardingInterceptorPlugin() {
    }

    public ShardingInterceptorPlugin(Set<String> shardingTables, ShardingStrategy shardingStrategy) {
        this.shardingTables = shardingTables;
        this.shardingStrategy = shardingStrategy;
    }

    public Set<String> getShardingTables() {
        return shardingTables;
    }

    public void setShardingTables(Set<String> shardingTables) {
        this.shardingTables = shardingTables;
    }

    public ShardingStrategy getShardingStrategy() {
        return shardingStrategy;
    }

    public void setShardingStrategy(ShardingStrategy shardingStrategy) {
        this.shardingStrategy = shardingStrategy;
    }
}

分片策略接口

public interface ShardingStrategy<T> {

    String doSharding(String table,T sign);

}

帮助类（借鉴pageHelper）

public class ShardingHelper {

    public static void setSign(Object o){
        ShardingInterceptorPlugin.setCurrentSign(o);
    }

}

配置类

@Configuration
public class ShardingConfig {
    
    @Bean
    public Interceptor initShardingInterceptor() {
        Set<String> tables = new HashSet<>();
        tables.add("test");
        Interceptor shardingInterceptor = new ShardingInterceptorPlugin(tables, new ShardingStrategy() {
            @Override
            public String doSharding(String o, Object s) {
                int code = s.hashCode();
                code = code > 0 ? code : -code;
                return o + "_" + (code % 3);
            }
        });
        return shardingInterceptor;
    }
}

这里的ShardingStrategy接口可以按需实现
tables 中装的是需要分表的原始表名 建议将配置抽出
此处的3 是表数量

使用方式

    @Autowired
    private TestDao testDao;

    @Test
    public void test(){
        TestEntity testEntity = new TestEntity();
        testEntity.setText("dadadad");
        //设定数据标识
        ShardingHelper.setSign(testEntity.getText());
        testDao.queryByText(testEntity);
    }

源码地址