Mysql分库分表

一、容量评估

系统是否需要分库分表？分多少个库多少个表不是拍脑袋决定的，决定分库分表方案前，要评估系统未来的数据量，通过容量、资源评估以后决定的，以笔者参与的一个供应链项目为例：

1. 连锁店平均门店在5左右的大约有1W家
2. 连锁店平均门店在20的大约有2W
3. 预计每个门店每天产生一笔订单
4. 平均每个订单预10条明细
5. 预计每行数据0.5k
6. 每台数据库容量300G
7. 预计要满足三年内的数据容量需求
   上面的这些数据需要产品通过市场调研，或者根据现有的数据去预计使用系统的门店数和订单量。

有了上面的数据，我们才可以去做容量的评估。

通过上面的数据，可以计算得出：

一月的订单数量：（（1w * 5） + （2w * 2））* 30 = 1350w

一年的订单数量：1350w * 12 = 16200w

三年的明细数量：16200w * 10 * 3 = 486000w

三年以后的数据容量： (486000w * 0.5) / 1000 /1000 = 2430G （此处按1000k = 1m计算，计算一个大概值，当然除了容量的估计，还要考虑网络，cpu，内存等资源）

需要数据库数量：2430G / 300G = 8.1

需要表的数量：486000w / 1000w = 486

最终分库分表方案：8个库，每个库 64 张表

二、在分库分表设计过程中需要考虑的问题

1. 单库写入写入QPS建议在1500以下，不超过2000
2. 单个表的记录条数不超过 1000w（业界推荐，这个也和btree+ 索引数的高度计算有关，此处就不扩张了）
3. 当个库数据库的容量

三、分片键的选择

笔者参与的供应链项目是一个TO-B的系统，每条数据都归属于一个总部（hq_id，单店认为是一个总部），因此采用hq_id作为分片键进行分片，在对数据库操作的过程中，都要带上
hq_id，分库分表中间件通过hq_id重写SQL。

四、分库分表带来的问题

1. 分库分表以后，数据被分散到不同的表中，因此难以利用Mysql的Join特性。但是在互联网公司中，一般是在业务代码中实现数据的Join。

2. 存在分布式事务问题，将数据划分到多个表或者数据库实例以后，可能会出现跨库操作，这就引入了事务问题，因此在进行设计时，尽量按照分库分表规则，将数据划分到同一个实例中。
   在笔者参与的供应链项目中，按hq_id进行分片，将同一个连锁店的数据划分到了同一个表中，避免了分布式事务。
   如果的确有特殊的情况，可以通过消息异步刷数据，或者监听binlog进行异步的处理。（通过binlog监听进行异步处理的时候，要注意主从延迟问题）

3. 某些系统可能存在数据统计或者排序的功能，分库分表的中间会将数据捞取到内存中，在内存中排序和统计，但是这种方式不推荐。可以将数据导入到ES或者Hive中去完成数据统计和计算工作

4. 存在多数据源管理，假如存在10个数据库实例，和10个应用层，每个应用层连接每个数据库实例的连接池最小16，最大64，那么每个应用层需要管理的数据库连接最小是： 10 * 16 ，最大是 10 * 64，每个数据库的连接个数最小是：10 * 16，最大是：10 * 64，那么多数据源管理是一个很大的问题。

5. 分库分表以后，采用数据库的自增主键不能保证全局唯一，需要单独设计全局唯一主键，可以参考美团leaf方案（https://tech.meituan.com/2017/04/21/mt-leaf.html）

6. 分库分表扩容问题，在分库分表以后，如果涉及的分片已经达到承受的最大数据量，就需要对集群进行扩容，而且一般是成倍的扩容，通用的扩容方法包括的步骤：

• 按新旧规则，对新旧数据库进行双写
• 将双写前的历史数据按照新的分片规则迁移到新的数据库
• 将旧的分片规则查询改为按新的分片规则查询
• 将双写逻辑下线，按照新的分片规则写入数据
• 删除历史数据

在第二步迁移历史数据的时候，由于数据量较大，可能会导致数据的不一致，因此需要做好数据的校验和清洗问题。数据的校验一般包含全量对比和抽样对比。

五、分库分表维度问题

场景一：在电商网站，需要将交易记录保存下来，如果按照买家维度分库分表，那么一个买家的交易记录都被保存到一张表里面，并且买家查询购买记录很方便，但是一个商家要查询自己的销售记录时就比较麻烦，因为一个商家的销售记录被分布在多张表中，需要做数据的聚合，分页。
常见的解决方案：

方案一: 在多个分片中查询后合并数据集，这种查询方式效率是最底下的，不推荐使用该方案。

方案二: 记录两份数据，一份数据按照买家维度，一份数据按照商家维度，可以通过消息后者binlog监听异步写入数据

方案三: 将交易数据导入到ES中，通过ES搜索数据，通过ES搜索可能存在短暂的数据延迟。

六、扩展

除了分库分表方案外，我们一般还会采用读写分离架构，将写流量请求到主库，读流量请求到从库。在笔者参与的供应链系统中，会监听从库的binlog变更，然后通过回调通知到处理数据变更的服务。
这个时候需要注意由于主从延迟，导致的数据不一致问题。主要流程如下图：

image

1. 数据写入到主库
2. 数据同步到slave1
3. binlog监听组件发现数据变更，将数据变更信息通知给数据变更处理服务
4. 回查数据库，读请求请求到了slave2，读取了脏数据
5. 将脏数据写入到缓存或者ES集群中，导致数据不一致问题

解决方案有两种：
方案一：通知binlog变更以后，sleep 1秒，然后处理消息
方案二：第四步查询数据的操作走主库

七、补充

为什么建议分库分表以后，数据量在千万级别？
建议数据量在千万级别和树高有关，下面来简单计算一下B+Tree的树高。
Innodb基于B+tree的数据结构，在叶子节点存储数据，非叶子节点只存储值和指针信息。

前提假设：每行业务数据1k，使用long作为主键8 bytes，非叶子节点指针大小6 bytes

每页可以存储的数据行数为：
16k / 1k = 16

树高为2的时候，可以索引的数据条数计算如下：
非叶子节点的指针数：(16 *1024) / (8 + 6) = 1170
可索引的数据条数：1170 * 16 = 18720

树高为3时，可索引的数据条数：
1170 * 1170 * 16 = 21902400

当树高为3的时候，可以存约2000w的数据。

在Mysql中，将页数据缓存在内存中，加快查询和索引的速度，假如只将第一层数据缓存在内存中，需要16k的内存大小;
假如将第一层和第二次的数据缓存在内存中，需要 16K * 1170 ，约160M的内存，这对于现代的服务器的内存是没有问题。
假如将三层数据都假如到内存中，那么就需要好几个G的内存，如果只有1张表，这个数据量也还好，但是一个数据库有多个表，一个数据库实例还有多个库，所以将第三层的所有数据存储在缓存中是不靠谱的，只能缓存部分数据
但是我们将第一二层的数据完全存储在缓存中是没有问题的，通过在缓存中查询第一二层的数据，找到对应叶子节点的指针，将叶子节点对应的也重磁盘中加载到内存中。在层高为3的时候，最多从磁盘中加载一次数据。