分库分表设计

新商机分表方案

sell_chance表现状
当前（20190307）数据: 1.8kw
去年（201801-201901）增长数据量: 1.3kw

考虑到公司发展速度，未来的数据量肯定较去年有所上升，按一年3kw的数据量计算，3年的数据量不超过12kw。

随着数据量的不断上升，对单表操作的性能上会带来一定的影响，因此需要制定优化方案来解决此问题。

如何优化？

背景分析

当前新商机表字段为29个，且不存在大字段，不需要进行垂直分表；
弹B当前新商机涉及到的查询操作见sell_chance查询SQL，所有的查询中，只有queryWaitAllocationByCustomer和getByIds查询没有用到shop_code，但queryWaitAllocationByCustomer的查询条件是customerId，getByIds也只是在抢单和回流中用到，均不存在跨店的情况，因此具备分表的条件。

分库、分区 or 分表？

鉴于大风车团队对40kw的轨迹表进行分表改造的成功经验，说明这个数据量在单库应该不会造成单库IO瓶颈，因此暂时可以不用考虑分库，分表即可；
分区会带来单点问题，另外随着数据量不断上升，不方便扩展；
另外若是分库，会带来运维的成本，还会带来跨库分布式事务一致性以及其他一些问题。

综上分析，决定分表。

（暂考虑3年的量，到时候单纯的分表可能已经满足不了需求了，需要优化的时候再进行考虑。分库？冷热数据分离？）

技术选型

当前市面上使用较多较成熟的分表技术有sharding-jdbc及mycat。

sharding-jdbc和mycat该选哪个
sharding-jdbc和mycat使用不同的理念：

sharding-jdbc目前是基于jdbc驱动，无需额外的proxy，因此也无需关注proxy本身的高可用；
Mycat是基于Proxy，它复写了MySQL协议，将Mycat Server伪装成一个MySQL数据库，需要额外部署一台服务器，而Sharding-JDBC是基于JDBC接口的扩展，是以jar包的形式提供轻量级服务的；
业务更偏向于OLTP型，可选择JDBC层的中间件；
sharding-jdbc在社区活跃度及文档的详细程度较mycat好；
团队有对sharding-jdbc进行过调研及源码层面的分析；
大风车团队有成熟的sharding-jdbc使用经验，有利于快速实践；
当前所有查询均不存在sharding-jdbc不支持的sql；（不支持的SQL参见不支持的SQL分析中的第三节）

综上分析：决定使用sharding-jdbc

分表方案

字段选择

上面对当前所有的查询语句进行了分析，shop_code是最适合作为分表的字段；
当前店铺对应的新商机记录数见
sell_chance_shop_count.txt
(106 KB)
，据统计，当前店铺数量为8k+，店铺数量的增长不会那么迅速，按三年后2w家店，三年后预估的数据量12kw，且保证平均一张表300w数据量计算，可分为40张表，一张表平均对应500家店，经过统计，当前数据量靠前的前500家店的新商机总量为730w，加上当前新商机表的索引优化得较好，因此，即使店铺数量最多那些店铺没有散列开，在这种极限情况，依然完全能够hold住；
当前PC端存在跨店的查询，但是通过ES进行查询，后续若还有跨店查询，依然可以走ES，不影响将shop_code作为分表字段的选择。

综上分析：决定使用shop_code作为分表字段，且方案为hash(shop_code)%40

建立分表

创建40张新商机分表

添加sharding-jdbc
修改代码，具体实现

分表算法：

public class StringModeShardingAlgorithm implements PreciseShardingAlgorithm<String> {
    /**
     * availableTargetNames所有子表表名的集合
     * shardingValue分表字段的{名字，值}
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        for (String each : availableTargetNames) {
            if (each.endsWith(shardingValue.getValue().hashCode() % 40 + "")) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }
}

分表配置：

@Configuration
@Component
public class ShardingDataSourceConfig {

    /**
     * 自动注入xml里面配置的数据源
     */
    @Autowired
    public DataSource dataSource;

    @Bean(name = "shardingDataSource")
    DataSource getShardingDataSource() throws SQLException {

        Map<String, DataSource> dataSourceMap = new HashMap<>();
        /**
         * 将dataSource纳入shardingDataSource管理
         */
        dataSourceMap.put("souche_sell_chance", dataSource);
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        /**
         * 定义分表算法
         */
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
        /**
         * 设置逻辑表（逻辑表就是我们写在SQL中的表名）
         */
        tableRuleConfig.setLogicTable("sell_chance");
        /**
         * 设置实际表（最后我们数据落地的表，这里就是sell_chance_0,sell_chance_1...）
         */
        tableRuleConfig.setActualDataNodes("souche_sell_chance.sell_chance_${0..39}");
        /**
         * 设置分表配置，shop_code，算法就是实现的hashcode取余算法
         */
        tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("shop_code", StringModeShardingAlgorithm.class.getName()));
        shardingRuleConfig.getTableRuleConfigs().add(tableRuleConfig);
        return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig);
    }
}

数据迁移方案

在此引出几个需要讨论的问题

Q：分的表是否需要放到新的实例？
建议迁到新的实例

针对我们的业务，拟定以下两种部署方式

双写部署（一）

首先我们引入两个概念：历史数据和增量数据。

我们对sell_chance表进行拆分，因为要进行双写，系统里和sell_chance表有关的业务，加入一段双写的代码，同时往老库和新库中写，然后进行部署。

历史数据：在部署前，数据库表的有关数据，我们称之为历史数据；
增量数据：在部署后，数据库表新产生的数据，我们称之为增量数据。

迁移过程如下：

由于sell_chance表的主键不是自增的，以创建时间作为划分历史数据和增量数据的依据；
在代码中，与sell_chance表有关的业务，多加一条往消息队列中发消息的代码，将操作的sql发送到消息队列中，但只发送写请求的sql，因为只有写请求的sql对恢复数据才有用；(delete和update并不会对数据一致性产生影响)
这个阶段不对消息队列中的数据进行消费，由于只发写请求，消息队列的消息堆积情况不会太严重。（经统计，平均每天增量约为34000+）
系统上线。将历史数据迁移至新库；（具体怎么迁移，写迁移程序还是数据导入？待确定）
历史数据迁移完成后，开始迁移增量数据，订阅程序开始消费消息队列中的数据，将增量数据写入新库；
新老库进行一致性验证，去除双写代码，将与sell_chance相关的操作指向新库。

注意：该方法有个问题是：会造成代码入侵，将非业务代码嵌入进了业务代码，后期删除代码是个问题。

双写部署（二）

迁移过程如下：

将上面代码嵌入的方式改为监听binlog日志；
将历史数据迁移至新库；（这点和上面类似）
消费binlog日志中新增的SQL，订阅程序将增量数据写入新库；
新老库进行一致性验证，订阅程序下线，将与sell_chance相关的操作指向新库。

数据一致性校验

校验数量
可随机选取一些店铺，新老库每次对某个店铺按date_create排序取其中100条，然后取一些关键字段，拼接，然后进行md5加密，最后进行对比，如果一致，则说明一致；若不一致，采用二分法找出不一致的数据，进行订正。

测试

由于测试环境数据量太小，故申请将线上库copy一份用于测试；

性能测试

测试工具：jmeter

测试方式：接口测试，分表前后都测试

接口不涉及其他项目的接口，直接使用该接口；
接口涉及其他项目的接口，添加去除其他项目接口或ctrl层直接调用dao层。

功能性测试

确保业务功能正常使用。