数据分片-库内分表实践

序

由于疫情不再进行防控，项目的基本业务是扫码乘坐公交车，订单表的增量相比过去有了较大的增加， 目前系统中存在订单表保存3个月的数据，数据在1300W左右，按现在日增数据为30W，月增量为900W 那么未来某个时刻的数据会达到峰值2700W（3个月），整体数据量翻倍增长。

分析与实践

前置知识

分库分表

	垂直分表	水平分表	垂直分库	水平分库
概念	以字段为依据，按照活跃度，将表中的数据拆分到不同表（主表和子表）	以字段为依据，按照一定的策略，将一个表中的数据拆分到多个表中	以表为依据，按照业务归属不同将不同表拆分到不同库中	以字段为依据，按照一定策略，将一个库中的数据拆分到多个库中
结果	每个表的结构，数据都不同，子表存在和主表的关联字段；相关的表的并集是全量数据	每个表的结构相同，数据不同；所有表的并集是全量数据；	每个库的结构，数据不同；所有库的并集是全量数据；	每个库的结构相同，数据不同；所有库的并集是全量数据；
场景	系统并发量不大，表的字段多，单行数据空间占用大，表中字段存在热点和非热点数据；	系统并发量不大，仅是单表数据过多，影响了SQL效率，加重CPU负担；	系统并发量较大，业务模块划分清晰；	系统并发量较大，分表难以解决问题；
图示

常用架构模式

分库分表架构主要有两种模式：client 客户端模式 和 proxy 代理模式

客户模式

client模式指分库分表的逻辑都在你的系统应用内部进行控制，应用会将拆分后的SQL直连多个数据库进行操作，然后本地进行数据的合并汇总等操作。

代理模式

proxy代理模式将应用程序与MySQL数据库隔离，业务方的应用不在需要直连数据库，而是连接proxy代理服务，代理服务实现了MySQL的协议，对业务方来说代理服务就是数据库，它会将SQL分发到具体的数据库进行执行，并返回结果。该服务内有分库分表的配置，根据配置自动创建分片表。

区别

	客户模式	代理模式
性能	性能方面表现的稍好一些，它是直接连接MySQL执行命令	代理服务则将整个执行链路延长了，应用->代理服务->MySQL，可能导致性能有一些损耗
复杂度	在开发使用通常引入一个jar可以	需要搭建单独的服务，有一定的维护成本，既然是服务那么就要考虑高可用，毕竟应用的所有SQL都要通过它转发至MySQL
升级	分库分表一般是依赖基础架构团队的Jar包，一旦有版本升级或者Bug修改，所有应用到的项目都要跟着升级小规模的团队服务少升级问题不大，如果是大公司服务规模大，且涉及到跨多部门，那么升级一次成本就比较高	在升级方面优势很明显，发布新功能或者修复Bug，只要重新部署代理服务集群即可，业务方是无感知的，但要保证发布过程中服务的可用性
治理，监控	由于是内嵌在应用内，应用集群部署不太方便统一处理	在对SQL限流、读写权限控制、监控、告警等服务治理方面更优雅一些。
开源组件	sharding-jdbc ，zebra	sharding-jdbc，MyCAT，DBProxy，cobar，Atlas

目前情况

• 根据序中阐述的内容未来的订单表存放三个月的数据将会存放2700W左右的数据量甚至更多，数据量上单表已经存在了一些压力，数据库RDS系统中查询到的慢SQL中存在订单表的慢查询，但分析上数据库语句已经无法优化了；
• 订单表作为业务的核心表来说已经做了 冷热数据分离（即针对3个月之前的数据进行数据归档存放到订单备份表中）；
• 业务存在明显的高峰期，但基于MQ做了削峰填谷，不会出现连接数不够；

基于上述分析：仅仅是单表内数据量过大，且针对目前的体量来说，对订单表数据分片进行库内水平分表是一种比较合适的方式。

问题分析

基本方向已确定，那么接下来就是针对库内分表所要面临的问题进行分析。

1. 水平分表以字段为依据，那么哪个字段作为分表的键比较合适？
2. 水平分表具体要分多少张表？
3. 分片算法或者说分片策略是什么？

4. 业务改造方案？数据如何平滑迁移？

   选择分片键

   对上述问题我们先看下第一个问题

   水平分表以字段为依据，那么哪个字段作为分表的键比较合适？考虑的因素主要有哪些？

既然是对订单表分片，那么首先分析下订单表的字段，订单本质上是一种交易双方的合约，那么必然存在买方和卖方，字段上包含如下：

• oid：order_id 订单表id或订单号
• uid：user_id 用户id
• mid：merchant_id 商户id

• price，time等其他字段

  这里的买方是用户，花钱买公交服务，卖方是公交公司，提供公交车营运的业务。

其次我们再分析下业务场景，通过具体业务场景来具体分析解决方案。

1. 谁在用这个系统？他们的查询维度是什么？

系统用户大体上可分为两类：用户侧 运营侧，用户侧包含乘客（用户）和公交公司（商户），运营侧包含 产品经理、运营、开发等。用户侧的查询需求仅仅是查询自己相关的数据，并发量大，且对服务查询质量容忍度低；运营侧的查询需求是查询大多是多个商户的数据，并发量低，且对服务查询质量相对较高。
常用查询需求：

• 用户侧

  • 乘客

    • 订单列表：根据 uid 查询自己一定创建时间内的订单列表
    • 订单详情：根据 oid 查询具体订单

  • 公交公司

    • 订单列表：多维度（时间，线路，设备等）根据mid查询商户的订单列表
    • 订单详情：根据oid查询具体订单

• 运营侧：

  • 管理员

    • 订单列表：后台多维度，多条件的分页查询

基于上述我们来看一下按照各个字段来作为分片键会产生什么样的效果？

字段	解决的问题	未解决的问题
uid	根据uid进行分片，查询中存在uid的sql能够快速查询，如查询乘客订单列表等等	没有uid的查询则需要通过其他方法来进行查询
oid	根据oid进行分片，查询中存在oid的sql能够快速查询，如查询订单详情等等	没有oid的查询则需要通过其他方法来进行查询
mid	根据mid进行分片，查询中存在mid的sql能够快速查询，如查询商户订单列表等等	没有mid的查询则需要通过其他方法来进行查询
time	根据time进行分片，查询中存在time的sql能够快速查询，如管理员查看的订单列表等	没有time的查询则需要通过其他方法来进行查询

从上述的分析表格当中可以明显看出，无论选择哪个字段作为分片键都会存在如果该查询不存在该分片键，那么这个查询都会需要通过其他方法来进行处理，存在一定局限性。这个其他方法通常有哪些呢？

• 遍历法

  • 遍历所有数据分片的表进行查询

• 索引表法

  • 所需要查询的字段与分片键建立关联关系，在查询之前先查询关系再定位到对应的分片，缺点则是需要多查询一次

• 缓存映射法

  • 同样是建立查询需要的字段与分片键建立关联关系，若不存在则通过遍历法获取然后放到cache，之后直接通过cache来查询对应关系，缺点仍然是需要多查询一次

• 基因法

  • 是指将某个字段融入到分片键中，比如oid中融入uid，这样通过uid或者oid都能通过快速匹配到对应数据分片

选择这个分片键，哪些因素是我们需要考虑的？

• 业务优先级

  • 为什么要分析查询需求，就是要确定哪些查询是重要的，哪些查询是可以妥协的

• 字段可变性

  • 分片键是不可改变的，如果可以变化，就会出现数据存在但查询不到的情况

业务优先级通常是用户侧比较重要，毕竟是客户来源，其次我们这个系统本质是个saas系统，那么对应的优先级最高的应该是用户侧中的商户，且系统中大部分的查询都存在mid这个字段，所以选择mid作为我们的分片键是比较合适的，假设是2c的业务，那么选择uid作为分片键也是比较常见的方案；对于字段可变性来说，上述选择的分片键都是不可变的，所以都是可以选择的。
既然选择了mid作为分片键，虽然对于用户侧（商户）的查询是比较方便的，但是对于用户侧（乘客）的查询就不那么方便，用户侧（乘客）又是直接使用公司小程序的，如果查询缓慢必然会引起一些客诉等等。由于是库内分表，且系统中对于用户侧（乘客）的查询较少且查询基本为oid+uid或单独为uid，在建立好对应的索引且分表数量不是很多的情况下使用 UNION ALL来查询也是一个较为合适的折中方案。
假设是分库分表呢？

有一种方案是利用空间换时间的，就是冗余表数据，将订单数据分为两份，一份数据使用商户的分片键来分表，一份数据用乘客的分片键来分表，即商户的用mid作为分片键，用户的用uid作为分片键，这样不同的查询就使用不同的数据支持，但是由此也带来了一些问题：

• 由于是数据冗余，为了防止双方同时修改订单状态，所以修改时要订单上锁（分布式锁），防止两端同时同步；
• 由于是数据冗余，数据是同步的，那么必然存在数据不一致的时间窗口；
• 加大了系统的整体复杂度；

上面把用户侧的查询需求基本都解决了，那么接下来的就是运营侧的查询需求，运营侧的查询需求查询多个商户之间的数据，那么这里对于使用mid作为数据分片键的话就无法提供较好的支持了，通常运营侧的查询也是公司内部人员在使用，我们为了减少系统整体的复杂度对运营侧的查询页面做了一些妥协，针对大部分情况下运营侧必须选择商户才能查询，但小部分比较常用的查询则无需必填商户查询，例如（订单号，手机号，uid等等），这类查询同样是使用UNION ALL支持。

此处也有一种方案是将将数据数据灌入es解决，通过es支持多条件分页查询，如果es数据量过大，可以配合hive数据结合使用，es只落入关键数据，hive落入全部数据，每次进行条件查询获取rowId，然后根据再到hive中查询所有数据。

分片数量

选择好了分片键，接下来看下第二个问题

水平分表具体要分多少张表？

通常在设计分片数量时要考虑业务未来的增长量，这样主要是为了避免或减少扩容次数。那么当前数据库容量为1300W（3个月）

• 按照目前的增量 每日30W左右，月增量为3030=900W，未来某个时间点订单表为 9003=2700W（3个月）；
• 由于产品对未来拓展商户持保守态度，则未来5年我们按照50%的增量算，30(1+0.5)=45W, 月增量为4530=1350W，未来某个时间点订单表为 1350*3 = 4050W；

这里我们预估未来5年内的数据在4000W左右，那么每张分片的数量我们姑且按照阿里巴巴Java开发手册中的500W来作为节点，那么4000/500 = 8， 分片的数量为8，可以支撑未来5年内的数据增长。

真的是500W为标准吗？
MySQL 单表数据最大不要超过多少行？为什么？

分片策略

选择完了分片键和确定分片数量之后，接下来看看第三个问题

分片算法或者说分片策略是什么？

分片策略本质上就是说数据通过一定的策略知道自己到哪个分片去，先看看主流的分片策略分别有哪些

分片策略	描述	优点	缺点
范围（Range）	将数据按照范围划分为不同分片，例如 id，时间；	单表数据可控，方便扩容	热点问题：比如按照时间划分，由于数据是连续的，那么最近的数据就会频繁读写
取模（Hash）	分片键取模（对hash结果取余数 hash(分片键) mod N)，N为数据库实例数或子表数量）	实现简单，有效避免数据倾斜	扩容麻烦，若发生扩容，数据则需要迁移，所以建议提前规划好分片规模或分表实例为2的N次方再或者使用一致性hash；
范围+取模（Range+Hash）	将数据先按照范围划分到不同分片库，再在分片库内使用取模将数据划分到分片表，常用在分库分表场景	包含了上述两者的优点，解决了hash的扩容麻烦	好像没啥缺点
预定义（List）	对分片键进行预定义列表划分，直接将某类数据路由到指定数据分片	实现简单，方便扩容	需要提前将数据划分到对应的分片规则，后续若有新增分片键需要修改配置

基于我们上述的分析，已经明确了分片键和分片数量的场景下，目前能够选择的只有取模或者预定义，那么假设使用取模的话，这里就存在一个问题，因为我们是通过mid进行分片，每一个mid的数据量不同，有的商户数据量大，有的商户数据量小，hash取模之后分到的数据分片的数据量可能存在数据倾斜的情况，而预定义列表我们可以通过已知的订单数据来将商户进行分组，划分到不同的表，可以做到数据尽量不倾斜，实现上也相对简单，对后期的扩容也不会有太大的影响。

业务改造

业务改造方案？数据如何平滑迁移？

对于数据分片我们已经完成了一大半的工作量，剩下的就是实际操作要面临的一些问题了，诸如代码改造数据的读写（插入，查询，连表查询等），历史数据迁移等等。

代码改造

首先基于读写需要提供一个工具类在读写之前能够根据mid获取对应的数据分片（表名），以下是一个示例：

public class TableNameRouter {
    
    // key 为 mid, value 为表名
    private static final Map<String, String> tableRouter;

    private static final String[] allOrderInfoTables = {
            "order_1","order_2","order_3","order_4",
            "order_5","order_6","order_7","order_8"
    };
    
    static {
        tableRouter = new ConcurrentHashMap<>();
        loadConfig();
    }

    /**
    *  加载配置
    */
    private static void loadConfig(){
        //从配置文件读取 省略···
        //此处写个固定代码表示示例
        tableRouter.put("mid1","order_1");
        tableRouter.put("mid2","order_2");
        tableRouter.put("mid3","order_3");
        tableRouter.put("mid4","order_4");
        tableRouter.put("mid5","order_5");
        tableRouter.put("mid6","order_6");
        tableRouter.put("mid7","order_7");
        tableRouter.put("mid8","order_8");
        tableRouter.put("mid9","order_1");
        //略···
    }

    /**
    *  根据mid获取表名
    */
    public static String getTableNameByMid(String mid){
        //校验逻辑 判空 自定义逻辑省略···        
        return tableRouter.get(mid);
    }

    public static List<String> getAllOrderInfoTable() {
        return Arrays.asList(allOrderInfoTables);
    }
}

有了上述这个工具类，那么在当写入数据时就可以通过mid获取到表名，进而插入到正确的表；对于插入和更新时都存在这mid，所以改造写操作还是很简单的，只要注意不漏改了就行；那么当读操作时情况就会稍微复杂了，我们上面也提及过当存在mid时直接同样使用和写操作一样的方法去获取表名即可，当部分查询没有mid时则是通过UNION ALL 来兼容这种情况，下面给个根据订单号查询示例作为参考：

<select id="findByOrderNo" resultMap="BaseResultMap">
    <foreach collection="allOrderList" item="table" index="index" separator="UNION ALL" >
        SELECT * FROM ${table} WHERE oid = #{oid}
    </foreach>
</select>

查询操作还有一种是联表查询，因为联表查询中不存在mid，所以需要将联表查询拆开，首先根据主表获取关联的数据，再通过UNION ALL的形式来将数据查出，最后在程序中匹配数据。

平滑迁移

数据迁移就是将已存在表里的数据分别按照之前定义好的分片键和分片策略将数据分别汇入到对应的数据分片表里。
数据迁移过程中要满足的以下几个目标：

• 迁移应该是在线的迁移，也就是在迁移的同时还会有数据的写入；
• 数据应该保证完整性，也就是说在迁移之后需要保证新的库和旧的库的数据是一致的；
• 迁移的过程需要做到可以回滚，这样一旦迁移的过程中出现问题，可以立刻回滚，不会对系统的可用性造成影响。

一般来说，通常的做法有以下几种：

订阅binlog

1. 通过订阅binlog日志同步的方式，同步完成之后在业务低峰期切换读写新数据源，具体步骤如下：

   1. 改造代码，将数据的增删改查改为到新数据源，添加开关配置，默认关
   2. 通过MQ消费binlog日志（存量数据+增量数据）将数据分到新数据源中
   3. 新数据源在同步追上旧数据源时（MQ内基本上无消息，不可能做到完全同步除非停止对外提供接口，但仍然可能有一些进入）做数据验证，以老数据为基准校准新数据源的数据，确保消费正确
   4. 业务低峰期上线新代码（尽量减少数据的写操作【如关闭定时任务，数据归档等】），并打开写新数据源开关
   5. 流量已经切到新数据源后，即MQ无消费消息后，关闭消费者，再做数据验证，保证数据一致性

优点：上线周期短
缺点：1.上线时间有要求 2. 会出现不满足回滚的要求，因为当你上线之后，一旦出现问题，数据已经写入新的数据源（分表或分库）但没有写入旧的数据源（旧表或旧库），不可能再将数据源改回旧的数据源

双写

2. 通过双写的方案，此处的双写就是新旧数据源都写，具体步骤如下：

   1. 将老的数据源的数据同步到新的数据源（通过订阅binlog或者代码）

   2. 改造业务代码，数据写入的时候，不仅要写入旧数据源，也要写入新数据源

      1. 这里使用同步（损耗性能）或异步（通过MQ）都可以，只要保证写入成功即可
      2. 写入新数据源失败的情况下需要记录下该日志，方便后续对这些失败的数据补写，保证一致性
   3. 业务低峰期上线，上线新业务代码前需要断开同步，为了防止数据冲突
   4. 数据验证，可以抽取部分数据，具体数据量依据总体数据量而定，只要保证这些数据是一致就可以
   5. 业务代码对数据的读取进行灰度，一部分数据从新数据源读取，并持续观察
   6. 观察没问题后，全量放开新数据源的读取
   7. 关闭双写，关闭旧数据源的写入，只写入新数据源

tips: 断掉同步和开启双写时数据有可能在不断的写入，如果在这个时间窗口期内出现数据写入 则需要通过后续的数据校验和补写，保证数据一致性
优点：迁移的过程可以随时回滚，将迁移的风险降到了最低
缺点：时间周期长，存在改造成本

渐进式双读

3. 读新旧数据源来保证程序运行，主要代码存在大量兼容的逻辑：

对于read操作：先从新数据源中读，如果没有的话再去旧数据源中读，如果从旧数据源中读到了数据，则将数据写入到新数据源中
对于update操作：使用类似的逻辑，如果在旧数据源中有数据，而新数据源中没有，则将数据迁到新数据源中，再进行update
对于write操作：写入到新数据源，然后将旧数据源中的数据删除
等到旧数据源中的数据全部迁移完成，改为直接读写新数据源
优点：比较简单
缺点：迁移的过程完全依赖于应用程序对数据的访问，存在一部分数据长时间没有被访问，那么这些数据也就没有机会被迁移到新数据源，方案不能像双写方案那样随时回滚到旧数据源，因为数据只会存在一份，要么在新数据源，要么在旧数据源，一旦从旧数据源迁移到新数据源，就没有方法迁移回来了

数据校验: MD5是一种比较快的方式

上述几种方式，比较稳妥的就是双写方案。但无论任何一种方案，数据一致性都是重中之重，因为我们就是围绕数据在提供服务。在具体实施时综合考虑下使用了binlog方案，也属于是铤而走险的一种方案，毕竟该方案是没有办法回滚操作的，这就需要我们对整体项目有了比较清晰的认知，将对项目产生的影响到最小。

事后思考

是否可以通过业务妥协来避免分表操作？ 例如只保存最近一个月的数据

数据分片毕竟是比较大的数据改动，如果可在业务上妥协也未必不是一个好的方案，因为实际上用户的数据实际上未必需要保留三个月，毕竟乘公交车这种较时效性的订单，用户大体不太会关注之前的数据，而对于商户来说，每个月的月初生成对账单也足以支撑相应的业务。

总结

数据分片的基本步骤：选择分片键-确定分片数量-决定分片策略-业务改造（代码改造，平滑迁移）。
前期分片键的选择，分片数量的确定，决定分片策略都需要基于项目的过去和项目的未来综合考虑，找到适合项目本身的方案；后期的业务改造主要考虑项目在运行过程中切换数据源的平滑性以及保证数据一致性。拆分来看，数据分片并不是多难的一个技术且市面上又有很多类似的项目实战，但难就难在数据分片是具体问题具体分析，所以切记前期分析不要着急，做好充足准备，实战时注意细节，才能保证万无一失！

参考链接

用uid分库，uname上的查询怎么办？
分库分表经典 15 连问
数据迁移时该如何做
如何做在线数据迁移
分库分表， 用图来说明清晰多了