Mysql集群篇：分表分表总结

type

status

date

slug

summary

1、概述

MySQL分库分表是一种数据库分割和划分数据表的技术。它将原本存储在单个数据库中的数据分散到多个数据库中，将原本单个表中的数据分散到多个数据表中。通过这种方式，可以提高数据库的查询性能和扩展性，同时降低数据库的负载。

1.1 分表

一般有垂直拆分和水平拆分两种方案：

垂直分表：指数据表列的拆分，把一张列比较多的表拆分为多张表，垂直分表一般是数据表设计之初就执行的步骤。例如：

把不常用的字段单独放在一张表；
把text，blob等大字段拆分出来放在附表中；
经常组合查询的列放在一张表中。

水平分表：指数据表行的拆分，表的行数超过 2000 万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。这种方式在后期数据量起来之后比较常用。

1.2 分库

Mysql 单库有性能瓶颈，承受的操作峰值应该在 2000 左右，超过的话应该分库，将数据拆分到不同的库中。拆分方案和分表类似，也分为垂直拆分和水平拆分两种方案：

垂直分库：将原本存储在一个数据库中的数据根据表的关系分散到多个数据库中，一般是数据表设计之初就执行的步骤。例如按功能分库，将不同功能的数据分散到不同的数据库中。比如将用户信息和订单信息分别存储在不同的数据库中，实现单功能模块单库。

水平分库：类似于水平分表，也会遇到水平分表的技术难点。

2、分库分表需要解决的问题

分库分表的技术难点：

水平切分策略。

多库结果集合并。

分布式唯一主键。

数据迁移。

跨库事务。

2.1 水平切分策略

水平切分策略是指水平分库分表后，数据被分到哪个数据库的哪张数据表的策略。一般我们把用来路由的字段称作 shardingkey，比如订单表按照用户 ID 作为 shardingkey。

生成 shardingkey 小技巧：例如我们在订单号的生成规则带上用户ID，落具体表的时候根据订单号中 10 位用户 ID hash 取模，这样无论根据订单号还是用户 ID 查询效果都是一样的。

一般有以下解决方案

2.1.1 hash取模

最常使用的方案，对 shardingkey 取模来切分。例如 user_id%2=0 即路由到 0 库， user_id%2=1 即路由到 1 库。

一般表数为 2^n，hash 算法为 mod2^n，便于后期扩展。

优点：实现简单

缺点：扩容时需要迁移历史数据

2.1.2 数据分段

对 shardingkey 分段处理放到不同的库。例如 user_id 属于 [0, 1亿] 为 0 库，属于 [1亿, 2亿] 为 2 库。

优点：单表大小可控，天然水平扩展

缺点：无法解决集中写入瓶颈的问题

2.1.3 Mapping

将 shardingkey 和库的 Mapping 关系记录在一个单独的库中。

优点：ID 和库的 Mapping 算法可以随意更改

缺点：引入额外的单点

2.2 多库结果集合并

2.2.1 非shardingkey的查询问题

一般情况下我们使用的 sharding key 都是针对C端的用户，这是数据库的主要流量。而非 shardingkey 一般都是针对B端用户的，例如来自商户端或者后台的查询，这种查询流量不大而且可以接受少量延迟，一般解决方案有：

双写，双写就是下单的数据落两份，C 端和 B 端的各自保存一份，C 端用你可以用单号、用户 ID 做 shardingkey 都行，B端就用商家卖家的 ID 作为 shardingkey 就好了。对于 B 端来说轻微的延迟是可以接受的，所以可以采取异步的方式去落 B 端订单。

走离线数仓或者ES查询。订单数据落库之后，不管你通过 binlog 还是 MQ 消息的都形式，把数据同步到数仓或者ES，他们支持的数量级对于这种查询条件来说就很简单了。同样这种方式肯定是稍微有延迟的，但是这种可控范围的延迟是可以接受的。这种解决方案还可以解决比如运营、业务、产品等复杂的查询条件。

2.2.2 排序分页问题

如何满足“跨越多个水平切分数据库，且分库依据与排序依据为不同属性，并需要进行分页”的查询需求，实现 select * from T order by time offset X limit Y 的跨库分页SQL？这个问题目前仍是业界难题，常用有以下解决方案：

方法	步骤	优点	缺点
全局视野法	1. 将 order by time offset X limit Y，改写成 order by time offset 0 limit X+Y 2. 服务层将改写后的SQL语句发往各个分库 3. 假设共分为N个库，服务层将得到N(X+Y)条数据 4. 服务层对得到的N(X+Y)条数据进行内存排序，内存排序后再取偏移量X后的Y条记录，就是全局视野所需的一页数据	通过服务层修改SQL语句，扩大数据召回量，能够得到全局视野，业务无损，精准返回所需数据。	• 每个分库需要返回更多的数据，增大了网络传输量（耗网络）； • 除了数据库按照time进行排序，服务层还需要进行二次排序，增大了服务层的计算量（耗CPU）； • 最致命的，这个算法随着页码的增大，性能会急剧下降，这是因为SQL改写后每个分库要返回X+Y行数据：返回第3页，offset中的X=200；假如要返回第100页，offset中的X=9900，即每个分库要返回100页数据，数据量和排序量都将大增，性能平方级下降。
业务折衷法-禁止跳页查询	很多产品并不提供“直接跳到指定页面”的功能，而只提供“下一页”的功能。这种设计可以大大降低难度： 1. 用全局视野法取得第一页数据，并得到第一页记录的time_max。 2. 每次翻页，将order by time offset X limit Y，改写成order by time where time>$time_max limit Y，以保证每次每个库只返回一页数据，性能为常量。	数据的传输量和排序的数据量不会随着不断翻页而导致性能下降。	需要业务配合
业务折衷法-允许模糊数据	允许数据精度损失：原理是使用shardingkey进行分库，在数据量较大，数据分布足够随机的情况下，各分库所有非patition key属性，在各个分库上的数据分布，统计概率情况是一致的。利用这一原理： 1. 将order by time offset X limit Y，改写成order by time offset X/N limit Y/N，每个库返回的都是精度不够准确的数据。	大大降低了技术方案的复杂度，既不需要返回更多的数据，也不需要进行服务内存排序了。	返回的数据不够准确
二次查询法	这个方法要求数据均摊到各分表，就是说不会有一个分表的数据特别多或特别少。这个方案不适用数据分段切分的表。 1. 将order by time offset X limit Y，改写成order by time offset X/N limit Y 2. 找到最小值time_min 3. between二次查询，order by time between $time_min and $time_i_max，第二次查询条件放宽了，故第二次查询会返回比第一次查询结果集更多的数据 4. 找到time_min在各个分库的offset，从而得到time_min在全局的offset 5. 得到了time_min在全局的offset，自然得到了全局的offset X limit Y	即能够满足业务的精确需要，无需业务折衷，又高性能	需要二次查找数据库

2.3 多数据源问题

从多个库读取数据表，一般通过中间件解决，有应用层和代理层两种解决方式。

2.3.1 应用层

应用层一般是基于JDBC的扩展，以 jar 包的形式提供给客户端使用，例如当当 sharding-jdbc 或者阿里 TDDL。

2.3.2 代理层

代理层是一个独立运行的中间件 Proxy，常用的有 Amoeba（开创性产品）、阿里Cobar、MyCAT（基于Cobar）、360 Atlas（基于Mysql Proxy）。

2.4 跨库事务

尽量避免跨库事务，例如避免在一个事务中修改 db0 中的表的时候同时修改 db1 中的表。否则就需要用到分布式事务框架。

2.5 分布式唯一主键

方案	描述	优点	缺点
业务自定义	yyMMddHHmm[10] + uid后四位[4] + server Id[2] + 随机数[4]	ㅤ	ㅤ
uuid	32个16进制数字，以连字号分为五段，形式为8-4-4-4-12的36个字符	• 性能非常高，本地生成，没有网络消耗。	• UUID太长，不适宜存储 • 不适合作为mysql主键
snowflake算法	64-bit分别划分成4段 1-41-10-12的64位字符串 • 1bit不用 • 41-bit：时间戳，可以表示69年的时间 • 10-bit：workerID，可以表示1024台机器，也可以5-bit给IDC，分5-bit给工作机器。这样就可以表示32个IDC，每个IDC下可以有32台机器 • 12-bit：自增序列号	• 趋势递增 • 不依赖第三方库	• 强依赖机器时钟，如果机器上时钟回拨，会导致发号重复或者服务会处于不可用状态。
数据库生成	给字段设置auto_increment_increment和auto_increment_offset来保证ID自增，每次业务使用下列SQL读写MySQL得到ID号。	• 简单易实现	• 强依赖DB，当DB异常时整个系统不可用 • 受msyql的读写性能限制
美团leaf	分为Leaf-segment和Leaf-snowflake模式 1、Leaf-segment：对数据库生成方案的优化 • segment分段取id，减少mysql的IO次数 • 双buffer：号码消耗到一定程度（10%）就开启异步线程去取新的id 2、Leaf-snowflake：对snowflake方案的优化 • 依赖zk生成workerID	• 高可用 • 解决了时钟回拨的问题	• 依赖zk
滴滴Tinyid	基于美团 Leaf-segment 算法的扩展	ㅤ	ㅤ
百度（uid-generator）	基于 snowflake 算法的扩展	ㅤ	ㅤ