万亿级企业MySQL海量存储分库分表设计实践

前言

关系型数据库以MySQL为例，单机的存储能力、连接数是有限的，它自身就很容易会成为系统的瓶颈。当单表数据量在百万以里时，我们还可以通过添加从库、优化索引提升性能。一旦数据量朝着千万以上趋势增长，再怎么优化数据库，很多操作性能仍下降严重。

移动互联网时代，海量的用户每天产生海量的数量，比如：

用户表
订单表
交易流水表

以支付宝用户为例，8亿；微信用户更是10亿。订单表更夸张，比如美团外卖，每天都是几千万的订单。淘宝的历史订单总量应该百亿，甚至千亿级别，这些海量数据远不是一张表能Hold住的。事实上MySQL单表可以存储10亿级数据，只是这时候性能比较差，业界公认MySQL单表容量在1KW量级是最佳状态，因为这时它的BTREE索引树高在3~5之间。

既然一张表无法搞定，那么就想办法将数据放到多个地方，目前比较普遍的方案有3个：

分区；
分库分表；
NoSQL/NewSQL；

说明：只分库，或者只分表，或者分库分表融合方案都统一认为是分库分表方案，因为分库，或者分表只是一种特殊的分库分表而已。NoSQL比较具有代表性的是MongoDB，es。NewSQL比较具有代表性的是TiDB。

为什么要分库分表

最后要介绍的就是目前互联网行业处理海量数据的通用方法：分库分表。

自己开发分库分表工具的工作量是巨大的，好在业界已经有了很多比较成熟的分库分表中间件，我们可

以将更多的时间放在业务实现上

sharding-jdbc（当当）
TSharding（蘑菇街）
Atlas（奇虎360）
Cobar（阿里巴巴）
MyCAT（基于Cobar）
Oceanus（58同城）
Vitess（谷歌）

但是这么多的分库分表中间件全部可以归结为两大类型：

CLIENT模式；
PROXY模式；

CLIENT模式代表有阿里的TDDL，开源社区的sharding-jdbc（sharding-jdbc的3.x版本即sharding-sphere已经支持了proxy模式）。架构如下：

PROXY模式代表有阿里的cobar，民间组织的MyCAT。架构如下：

但是，无论是CLIENT模式，还是PROXY模式。几个核心的步骤是一样的：SQL解析，重写，路由，执行，结果归并。

笔者比较倾向于CLIENT模式，架构简单，性能损耗较小，运维成本低。

接下来，以几个常见的大表为案例，说明分库分表如何落地！

表设计原则

主键选择：前面我们已经对比分析过业务主键和自增主键的优缺点，结论是业务主键更符合业务的查询需求，而互联网业务大多都符合读多写少的特性，所以所有线上业务都使用业务主键；

索引个数：由于过多的索引会造成索引文件过大，所以要求索引数不多于5个；

列类型选择：通常越小、越简单越好，例如：BOOL字段统一使用TINYINT，枚举字段统一使用TINYINT，交易金额统一使用LONG。因为BOOL和枚举类型使用TINYINT可以很方便的扩展，针对金额数据，虽然InnoDB提供了支持精确计算的DECIMAL类型，但DECIMAL是存储类型不是数据类型，不支持CPU原声计算，效率会低一些，所以我们简单处理将小数转换为整数用LONG存储。

分表策略：首先要明确数据库出现性能问题一般在数据量到达一定程度后！所以要求我们提前做好预估，不要等需要拆分时再拆，一般把表的数据量控制在千万级别；常用分表策略有两种：按key取模，读写均匀；按时间分，冷热数据明确；

实战案例

分库分表第一步也是最重要的一步，即sharding column的选取，sharding column选择的好坏将直接决定整个分库分表方案最终是否成功。而sharding column的选取跟业务强相关，笔者认为选择sharding column的方法最主要分析你的API流量，优先考虑流量大的API，将流量比较大的API对应的SQL提取出来，将这些SQL共同的条件作为sharding column。例如一般的OLTP系统都是对用户提供服务，这些API对应的SQL都有条件用户ID，那么，用户ID就是非常好的sharding column。

再以几张实际表为例，说明如何分库分表。

用户表

用户表几个核心字段一般如下：

uid为主键，业务上有按uid和mobile两种查询需求，所以要在moblie上创建索引。switch列比较特殊，类型为BIGINT，用来保存用户的BOOL类型的属性，每一位可以保存用户的一个属性，例如我们用第一位保存是否接收推送，第二位保存是否保存离线消息等等。

这种设计有很高的扩展性(因为BIGINT有64位，可以保存64个状态，一般情况很难用满)，但是同时也带来一些问题，switch有很高的查询频率。由于InnoDB是行存储，要找查询switch需要把正行数据取出来。

这对上述场景，我们在表设计上可以做哪些优化呢？常用的方案是把表垂直查分，这种很常见我们不做过多讨论。

还有一种方案我们可以利用InnoDB覆盖索引的特性，在uid和switch两列上创建联合索引，这样在二级索引上包含uid和switch两列的值，这样用uid查询switch时，只通过二级所以就能找到switch，不需要访问记录，甚至不需要到二级索引的叶子节点就可以找到要查询的switch值，查询效率非常高。

另外有一点需要考虑，可以想象switch的变更也是相当频繁的，switch值得改变会导致联合索引的变更吗(这里的变更指索引节点分裂或顺序调整)？

答案是不会！因为联合索引的第一列uid是唯一且不会变的，所以uid就已经决定了索引的顺序，switch列的改变只会改变索引节点上第二个key的值，不会改变索引结构。

一般用户登录场景既可以通过mobile_no，又可以通过email，还可以通过username进行登录。但是一些用户相关的API，又都包含user_id，那么可能需要根据这4个column都进行分库分表，即4个列都是sharding-column。

账户表

账户表几个核心字段一般如下：

与账户表相关的API，一般条件都有acc_no，所以以acc_no作为sharding-column即可。

复杂查询

上面提到的都是条件中有sharding column的SQL执行。但是，总有一些查询条件是不包含sharding column的，同时，我们也不可能为了这些请求量并不高的查询，无限制的冗余分库分表。那么这些条件中没有sharding column的SQL怎么处理？以sharding-jdbc为例，有多少个分库分表，就要并发路由到多少个分库分表中执行，然后对结果进行合并。具体如何合并，可以看笔者sharding-jdbc系列文章，有分析源码讲解合并原理。

这种条件查询相对于有sharding column的条件查询性能很明显会下降很多。如果有几十个，甚至上百个分库分表，只要某个表的执行由于某些因素变慢，就会导致整个SQL的执行响应变慢，这非常符合木桶理论。

更有甚者，那些运营系统中的模糊条件查询，或者上十个条件筛选。这种情况下，即使单表都不好创建索引，更不要说分库分表的情况下。那么怎么办呢？这个时候大名鼎鼎的elasticsearch，即es就派上用场了。将分库分表所有数据全量冗余到es中，将那些复杂的查询交给es处理。

淘宝我的所有订单页面如下，筛选条件有多个，且商品标题可以模糊匹配，这即使是单表都解决不了的问题（索引满足不了这种场景），更不要说分库分表了：

具体情况具体分析：多sharding column不到万不得已的情况下最好不要使用，成本较大。因为用户表有一个很大的特点就是它的上限是肯定的，即使全球70亿人全是你的用户，这点数据量也不大，所以笔者更建议采用单sharding column + es的模式简化架构。

总结

最后，对几种方案总结如下（sharding column简称为sc）：

-	单个sc	多个sc	sc+es
适用场景	单一	一般	比较广泛
查询及时性	及时	及时	比较及时
存储能力	一般	一般	较大
代码成本	很小	较大	一般
架构复杂度	简单	一般	较难

总之，对于海量数据，且有一定的并发量的分库分表，绝不是引入某一个分库分表中间件就能解决问题，而是一项系统的工程。需要分析整个表相关的业务，让合适的中间件做它最擅长的事情。例如有sharding column的查询走分库分表，一些模糊查询，或者多个不固定条件筛选则走es，海量存储则交给HBase。

做了这么多事情后，后面还会有很多的工作要做，比如数据同步的一致性问题，还有运行一段时间后，某些表的数据量慢慢达到单表瓶颈，这时候还需要做冷数据迁移。总之，分库分表是一项非常复杂的系统工程。任何海量数据的处理，都不是简单的事情，做好战斗的准备吧！