MySQL分库分表计算模型方法论

• 2023-03-20
• 作者： 王海波
• 分类：MySQL
• 访问量：173

拆分需求：

1、单机存储容量瓶颈无法扩展，另外大表DDL时间长、风险高

2、高QPS瓶颈导致的数据库资源产生瓶颈（cpu、mem、io）

拆分模式：

1、垂直（纵向）切分：把单一的表拆分成多个表，并分散到不同的数据库实例上

2、水平（横向）切分：根据表中数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库实例上。

以下重点说水平切分下的方法论：

水平切分的优点如下：

• 单库单表的数据保持在一定数据量级，有助于性能的提高。

• 切分表结构相同，应用层改造较少，只需要提前设计或者增加路由规则即可。

• 提高业务系统的稳定性和吞吐能力。

水平切分的缺点如下：

• 依据切分规则切分后，数据相对分散，但跨库Join性能较差，跨节点join，中间层计算损耗大

• 分布式事务解决方案成本高或者妥协解决。

• 数据库扩容，运维成本高。

分库分表拆分方法：

1、按库分，如mycat

2、按库内分表，如kingshard

3、既分库又分表，如DRDS，Sharding Shpere......

分库分表常用规则：

1、range切分 优点：单表大小可控，天然水平扩展 缺点：产生热点 ，写入数据倾斜

2、hash切分，一般采用mod来切分 优点：数据均匀，无热点 缺点：重新计算hash，扩容缩容麻烦

分库分表基本概念：

物理分库：一个MySQL实例下包含多个库

物理分表：MySQL实例下每个库下多个表

逻辑库：中间件实例下的数据库，对应用透明，后边由一批物理分库组成

逻辑表：就是业务表，比如订单表、优惠券表，后边由一批物理分表组成

拆分多少分表合适，万能公式？

总分表数（ N ） = 总物理实例数（ X ）＊ 每个实例下的分库数（ Y ）＊ 每个分库下的分表数（ Z )

• 实例数考虑QPS的吞吐能力

• 分库数考虑扩容拆分

• 分表数考虑数据分布，性能提升

业界一般：常规循序渐进扩容：短期（X小，Y大） 中期（X大，Y小） 长期 （X和Y适中 资源使用均衡），一般提前规划好总分表数，避免扩容对业务的影响，16<=总分表数（ N ）<=4096

拆分多少实例合适，万能公式？

物理QPS = ∑ 物理实例 i 的 QPS ， i ∊ (1, X )

业界一般：总实例数是2的幂，需要看中间层的产品支持情况

分片表计算公式参考：

分片键选择，同一分片键走本地事务，跨分片-分布式事务需要业务酌情考虑，考虑合适的解决方案

分片表多少？一般根据总数据量大小，依据分片表数据类型可计算每行size，进而可以推倒出该表存储容量大小，一般建议，单个物理分表的容量不超过500万行数据。

物理分表数 = 向上取整(估算的总数据量 / (实例数 * 物理分库) / 建议的分表容量)，如1亿行，后端实例数为4，物理分库为8，单个分表建议容量500万，计算如下：

物理分库上的物理分表数 = CEILING(100,000,000 / ( 4 * 8 ) / 5,000,000) = CEILING(0.625) = 1

结果为1，那么只分库即可，即每个物理分库上1个物理分表。

通常建议：当计算出的物理分表数等于1时，分库即可，无需再进一步分表，即每个物理分库上一个物理分表；若计算结果大于1，则建议既分库又分表，即每个物理分库上再建立多个物理分表

分库分表参考方案：

备注：运维角度考虑，分库一般也是2或者2个倍数，相对分表对半拆扩容，分库对半拆更加容易些，也决定扩容的次数，考虑扩容缩容风险高，运维成本高，最好一开始就确定好分库数和总分表数，当然有牛逼的中间件可以突破2的拆分，这需要重新数据分布和数据计算，这个另说

线性扩展能力与实际核心SQL和高频SQL有关：

分片键：与业务场景有关，需找到业务逻辑实体，核心SQL围绕该字段进行

带分片键影响：一般指SQL落在一个实例上，如等值查询、范围查询、in查询

不带分片键影响：一般指SQL落在多个后端实例上

数据容量计算预估模型：

以上是预估数据，SQL质量不同，会影响QPS吞吐，还是需要实际线上性能压测为准。

建议水平拆分，充分考虑未来容量增长，比如未来10年的容量规划，优先考虑按库分，数据容量过大，再考虑分库分表，架构简单就是美。

其他-分库分表带来的常见问题及解决方案参考：

分库分表避免/禁止跨库join解决方案：

1、创建全局数据字典表，数据很少变化，每个数据库创建一张，本地事务join

2、反范式设计，设计冗余字段，空间换时间，适合于依赖字段较少的场景

3、创建父子关联表，比如主订单表-子订单表，带分片键

备注：互联网业务一般都是简单查询，尽可能减少join操作，超过2个以上join建议从设计上考虑整改

分库分表多维度查询解决方案：

1、创建异步索引表，二次查找查询

2、创建以另一维度+分片键的全量数据copy，通过空间换时间，

3、通过实时数据同步工具，推送到另一数据库集群/大数据存储系统，满足运营/统计/客服大数据组的在线和离线需求，达到在线查询和离线查询的隔离

分布式事务解决方案，业界目前有如下三种主流解决方案 ：

两阶段提交协议

优点：最大限度保证事务的原子性，最大限度保证数据一致性

缺点：由于事务管理者需要协调事务参与者，无法水平伸缩，属于阻塞协议，性能较差，极端情况下，不能快速反应请求方需求，对高并发性能要求高的忽略，适合于确定性的短事务的业务

最大努力TCC模式

适用场景：高性能分布式事务解决方案，适用于对性能有很高要求的场景。

1、Try阶段-检测预留资源，预执行

2、Confirm阶段-正式完成所有业务逻辑的执行

3、Cancel阶段-回滚Confirm阶段执行的所有操作，预留资源释放

备注：本地事务一个接口变成分布式事务的三个服务接口

最终补偿SAGA协议

适用场景：冲正补偿服务，适合于业务流程长、业务流程多，追求高性能、高吞吐，保证最终一致性

1、每个参与者本地提交事务，每个参与者需要提供正向操作和逆向回滚操作

2、分布式事务执行过程中，依次执行参与者的正向操作，正向操作成功，则分布式事务提交

3、如果正向操作中任何一个发生失败，依次执行参与者的回滚操作，回滚已提交的参与者，回归到原始状态

基于消息实现的分布式事务

适用场景：此场景除单机事务优先考虑使用的，适用于流程序列化业务，有事务发起方和事务跟随方的场景

1、创建本地事务，提交本地事务，主动推送消息/提交事务日志表

2、实时读取事务日志表，发送到MQ中

3、事务消费方从MQ取出，及时消费

--------EOF---------
微信分享/微信扫码阅读