大厂原来都这么对MySQL分库分表!

1 何时分库分表

业务飞速发展，数据规模急速膨胀，单机DB已无法满足业务发展。

传统的将数据集中存储至单一数据节点的解决方案，在容量、性能、可用性和运维成本这三方面难满足海量数据场景。在单库单表数据量超过一定容量水位的情况下，索引树层级增加，磁盘I/O也很可能出现压力，会导致很多问题。

• 性能
  由于MySQL采用 B+树索引，数据量超过阈值时，索引深度的增加也将使得磁盘访问的 IO 次数增加，进而导致查询性能的下降；高并发访问请求也使得集中式数据库成为系统的最大瓶颈。

• 可用性
  服务化的无状态型，能够达到较小成本的随意扩容，这必然导致系统的最终压力都落在数据库之上。而单一的数据节点，或者简单的主从架构，已经越来越难以承担。从运维成本方面考虑，当一个数据库实例中的数据达到阈值以上，数据备份和恢复的时间成本都将随着数据量的大小而愈发不可控。

• 业务数据
  不同模块的数据，比如用户数据和用户关系数据，全都存储在一个主库中，一旦主库发生故障，所有的模块儿都会受到影响。

主从复制只能减轻读压力，但无法解决容量问题。

影响：

• 无法执行 DDL，比如添加一列，或者增加索引，都会直接影响线上业务，导致长时间的数据库无响应。
• 无法备份，与上面类似，备份会自动先 lock 数据库的所有表，然后导出数据，量大了就没法执行了
• 影响性能与稳定性，系统越来越慢，随时可能会出现主库延迟高，主从延迟很高，且不可控，对业务系统有极大的破坏性影响。

在 4 核 8G 的云服务器上对 MySQL5.7 做 Benchmark，大概可以支撑 500TPS 和 10000QPS，MySQL对于写入性能要弱于数据查询的能力，那么随着系统写入请求量的增长，数据库系统如何来处理更高的并发写请求呢？

这些问题你可以归纳成，数据库的写入请求量大造成的性能和可用性方面的问题，要解决这些问题，你所采取的措施就是对数据进行分片，对数据进行分片，可很好分摊数据库的读写压力，也可突破单机的存储瓶颈，而常见的一种方式就是“分库分表”。

如何使用正确的分库分表呢？很多人会在查询时不使用分区键或在查询时使用大量连表查询。用好分库分表没你想的那么容易。

从读写分离到数据库拆分

主从结构解决了高可用，读扩展，但单机容量不变，单机写性能无法解决。

提升容量 =》分库分表，分布式，多个数据库，作为数据分片的集群提供服务。
降低单个存储节点的写压力。提升整个系统的数据容量上限。

分库分表的好处

• 分库分表后，每个节点只保存部分的数据，这样可以有效地减少单个数据库节点和单个数据表中存储的数据量，在解决了数据存储瓶颈的同时也能有效的提升数据查询的性能
• 数据被分配到多个数据库节点上，那么数据的写入请求也从请求单一主库变成了请求多个数据分片节点，在一定程度上也会提升并发写入的性能

2 概念辨析

分库和分表是两码事，可能光分库不分表，也可能光分表不分库。

分库分表是跟着公司业务发展走，业务发展越好，用户越多，数据量越大，请求量越大，那单个数据库一定扛不住。因为单表数据量太大，会极大影响你的 SQL执行性能，到了后面你的SQL可能就跑的很慢。经验来看，单表到几百万时性能就会相对差点，就该分表。

分表

把一个表的数据放到多个表中，然后查询时，就查一个表。

比如按用户id分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在200万以内。

分库

单库一般达到2000并发，亟需扩容，合适的单库并发值推荐在1000/s。可将一个库的数据拆分到多个库，访问时就访问一个库。

• 分库分表的由来
  

3 分库分表中间件

cobar

阿里b2b团队开发，proxy层方案，但已停止维护。

• Github地址
  
  不支持读写分离、存储过程、跨库join和分页。

TDDL

淘宝团队开发，client层方案

• Github地址
  

不支持join、多表查询等语法，支持读写分离。
使用的也不多，因为还依赖淘宝的diamond配置管理系统，而且已被阿里云商用，不再开源。

atlas

• Github地址
  

360开源的，proxy层方案，但六年前就不维护了。

sharding-jdbc（shardingsphere）

• Github地址
  
  最初由当当开源，client层方案。
  SQL语法支持较多，支持分库分表、读写分离、分布式id生成、柔性事务（最大努力送达型事务、TCC事务）。被大量公司使用，我司也在用。现在已经升级为Apache组织的项目。

这种client层方案的优点：
不用部署，运维成本低，无需代理层的二次转发请求，性能很高
但遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合sharding-jdbc的依赖。

mycat

• Github地址
  
  基于cobar改造，proxy层方案，支持的功能非常完善，社区活跃。但相比sharding jdbc年轻一些。

proxy层方案的缺点：
需要部署，自己及运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。

选型

推荐使用sharding-jdbc和mycat：

• 小型公司选用sharding-jdbc，client层方案轻便，而且维护成本低，不需要额外增派人手，而且中小型公司系统复杂度会低一些，项目也没那么多
• 中大型公司最好还是选用mycat这类proxy层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护mycat，然后大量项目直接透明使用即可

4 微服务架构理论：扩展立方体

• X 轴：clone 整个系统无差别复制，集群
  针对全部数据，常见的比如数据库复制，即主从结构，备份和高可用
• Y 轴：解耦不同功能复制，业务拆分
  针对业务分类数据，比如垂直的分库分表，即分布式服务化、微服务架构
• Z 轴：拆分不同数据扩展，数据分片
  针对任意数据，比如水平分库分表，即分布式架构、任意扩容
  

5 数据库拆分

• 数据库如何拆分
  

5.1 水平拆分

把一个表的数据给弄到多个库的多个表里，但每个库的表结构都一样，只不过每个库中表放的数据不同，所有库表的数据加起来就是全部数据。关注点在数据的特点。

水平拆分的意义

• 将数据均匀放更多的库，然后用多个库抗更高并发
• 多个库的存储进行扩容

5.2 垂直拆分（拆库）

解决问题

• 服务不能复用
• 连接数不够

将一个数据库，拆分成多个提供不同业务数据处理能力的数据库，关注点在于业务相关性。

例如拆分所有订单的数据和产品的数据，变成两个独立的库，数据结构发生了变化，SQL 和关联关系也必随之改变。
原来一个复杂 SQL 直接把一批订单和相关的产品都查了出来，现在得改写 SQL 和程序。

• 先查询订单库数据，拿到这批订单对应的所有产品 id
• 再根据产品 id list去产品库查询所有产品信息
• 最后再业务代码里进行组装把一个有很多字段的表给拆分成多个表或库

每个库表的结构都不一样，每个库表都包含部分字段。

一般将较少的访问频率很高的字段放到一个表，然后将较多的访问频率很低的字段放到另外一个表。
因为数据库有缓存，访问频率高的行字段越少，可在缓存里缓存更多行，性能就越好。这个一般在表这个层面做的较多。

现有中间件都可实现分库分表后，根据你指定的某个字段值，比如userid，自动路由到对应库，然后再自动路由到对应表。

8 分库分表的方式

按range分

就是每个库一段连续的数据，一般按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了

• 好处
  后面扩容的时候，就很容易，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了

• 缺点
  但是大部分的请求，都是访问最新的数据。实际生产用range，要看场景，你的用户不是仅仅访问最新的数据，而是均匀的访问现在的数据以及历史的数据

按某字段hash

均匀分散，最为常用。

• 好处
  可以平均分配没给库的数据量和请求压力

• 坏处
  扩容起来比较麻烦，会有一个数据迁移的过程

1 Sharding

把数据库横向扩展到多个物理节点的一种有效方式，主要是为了突破数据库单机服务器的 I/O 瓶颈，解决数据库扩展问题。

Sharding可简单定义为将大数据库分布到多个物理节点上的一个分区方案。每一个分区包含数据库的某一部分，称为一个shard，分区方式可以是任意的，并不局限于传统的水平分区和垂直分区。
一个shard可以包含多个表的内容甚至可以包含多个数据库实例中的内容。每个shard被放置在一个数据库服务器上。一个数据库服务器可以处理一个或多个shard的数据。系统中需要有服务器进行查询路由转发，负责将查询转发到包含该查询所访问数据的shard或shards节点上去执行。

垂直切分/水平切分

MySQL的扩展方案

• Scale Out 水平扩展
  一般对数据中心应用，添加更多机器时，应用仍可很好利用这些资源提升自己的效率从而达到很好的扩展性
• Scale Up 垂直扩展
  一般对单台机器，Scale Up指当某个计算节点添加更多的CPU Cores，存储设备，使用更大的内存时，应用可以很充分的利用这些资源来提升自己的效率从而达到很好的扩展性

MySQL的Sharding策略

1. 垂直切分：按功能模块拆分，以解决表与表之间的I/O竞争
   e.g. 将原来的老订单库，切分为基础订单库和订单流程库。数据库之间的表结构不同
   

2. 水平切分：将同个表的数据分块，保存至不同的数据库
   以解决单表中数据量增长压力。这些数据库中的表结构完全相同

2.3 表结构设计案例

垂直切分

1. 大字段
   单独将大字段建在另外的表中，提高基础表的访问性能，原则上在性能关键的应用中应当避免数据库的大字段
2. 按用途
   例如企业物料属性，可以按照基本属性、销售属性、采购属性、生产制造属性、财务会计属性等用途垂直切分
3. 按访问频率
   例如电子商务、Web 2.0系统中，如果用户属性设置非常多，可以将基本、使用频繁的属性和不常用的属性垂直切分开

水平切分

1. 比如在线电子商务网站，订单表数据量过大，按照年度、月度水平切分
2. 网站注册用户、在线活跃用户过多，按照用户ID范围等方式，将相关用户以及该用户紧密关联的表做水平切分
3. 论坛的置顶帖，因为涉及到分页问题，每页都需显示置顶贴，这种情况可以把置顶贴水平切分开来，避免取置顶帖子时从所有帖子的表中读取

3 分表和分区

分表：把一张表分成多个小表；
分区：把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上。

3.1 分表和分区的区别

• 实现方式
  MySQL的一张表分成多表后，每个小表都是完整的一张表，都对应三个文件（MyISAM引擎：.MYD数据文件，.MYI索引文件，.frm表结构文件）
• 数据处理
  • 分表后数据都存放在分表里，总表只是个外壳，存取数据发生在一个个的分表里
  • 分区则不存在分表的概念，分区只不过把存放数据的文件分成许多小块，分区后的表还是一张表，数据处理还是自己完成。
• 性能
  • 分表后，单表的并发能力提高了，磁盘I/O性能也提高了。分表的关键是存取数据时，如何提高 MySQL并发能力
  • 分区突破了磁盘I/O瓶颈，想提高磁盘的读写能力，来增加MySQL性能
• 实现成本
  • 分表的方法有很多，用merge来分表，是最简单的一种。这种方式和分区难易度差不多，并且对程序代码透明，如果用其他分表方式就比分区麻烦
  • 分区实现比较简单，建立分区表，跟建平常的表没区别，并且对代码端透明

3.2 分区适用场景

1. 一张表的查询速度慢到影响使用
2. 表中的数据是分段的
3. 对数据的操作往往只涉及一部分数据，而不是所有的数据
   

3.3 分表适用场景

1. 一张表的查询速度慢到影响使用
2. 频繁插入或连接查询时，速度变慢
3. 分表的实现需要业务结合实现和迁移，较为复杂

4 分库

分表能解决单表数据量过大带来的查询效率下降问题，但无法给数据库的并发处理能力带来质的提升。面对高并发的读写访问，当数据库主服务器无法承载写压力，不管如何扩展从服务器，都没有意义了。
换个思路，对数据库进行拆分，提高数据库写性能，即分库。

4.1 分库的解决方案

一个MySQL实例中的多个数据库拆到不同MySQL实例中：

• 缺陷
  有的节点还是无法承受写压力。

4.1.1 查询切分

• 将key和库的映射关系单独记录在一个数据库。
  
• 优点
  key和库的映射算法可以随便自定义
• 缺点
  引入了额外的单点

4.1.2 范围切分

按某字段的区间拆分，比较常用的是时间字段。

在内容表里有“创建时间”的字段，而我们也是按时间来查看一个人发布的内容。我们可能会要看昨天的内容，也可能会看一个月前发布的内容，这时即可按创建时间的区间来分库分表。比如可以把一个月的数据放入一张表，查询时即可根据创建时间先定位数据存储在哪个表，再按查询条件查询。

• 按照时间区间或ID区间切分。
  

适用场景

一般列表数据可使用这种拆分方式。
比如一个人某时间段的订单、发布的内容。但这种方式可能存在明显热点，因为肯定会更关注最近用户买了啥，发了啥，所以查询的 QPS 会更多，对性能有一定影响。
使用这种拆分规则后，数据表要提前建立好，否则如果时间到了次年元旦，DBA却忘记了建表，那么次年的数据就没有库表可写了。

• 优点
  单表容量可控，水平扩展很方便。
• 缺点
  无法解决集中写入的瓶颈问题。

4.1.3 Hash切分

适用场景

按照某字段的哈希值做拆分，适用于实体表，比如用户表，内容表，一般按这些实体表的 ID 字段拆分。
比如把用户表拆分成 16 个库，64 张表，可先对用户 ID 做哈希将 ID 尽量打散，然后再对 16 取余，这样就得到了分库后的索引值；对 64 取余，就得到了分表后的索引值。

• 一般都是采用hash切分。
  

数据水平切分后我们希望易于水平扩展，所以推荐采用mod 2^n这种一致性Hash。

比如一个订单库，分库分表方案是32*32，即通过UserId后四位mod 32分到32个库中，同时再将UserId后四位Div 32 Mod 32将每个库分为32个表，共计分为1024张表。

线上部署情况为8个集群(主从)，每个集群4个库。

为什么说这易于水平扩展？分析如下场景：

数据库性能达到瓶颈

1. 现有规则不变，可直接扩展到32个数据库集群。
   
2. 如果32个集群也无法满足需求，那么将分库分表规则调整为(32*2^n)*(32⁄2n)，可以达到最多1024个集群。
   

单表容量达到瓶颈

或1024都无法满足。

假如单表都突破200G，200*1024=200T
没关系，32 * (32 * 2^n)，这时分库规则不变，单库里的表再裂变，当然，在目前订单这种规则下（用userId后四位 mod）还是有极限的，因为只有四位，所以最多拆8192个表。

选择分片键

分库分表的问题就是引入了分库分表键，即分片键，也就是我们对数据库做分库分表所依据的字段。
无论是哈希拆分还是区间拆分，都要选取一个字段，这带来一个问题：之后所有查询都需要带上该字段，才能找到数据所在的库和表，否则就只能向所有的数据库和数据表发送查询命令。如果拆成了 16 个库和 64 张表，那么一次数据的查询会变成 16*64=1024 次查询，查询的性能肯定是极差的。

解决方案

比如在用户库中使用 ID 作为分区键，这时若需按昵称查询用户，可按昵称作为分区键再做次拆分，但这样会极大增加存储成本，若以后还需要按注册时间查询，怎么办呢，再做次拆分？
所以最合适的是建立一个昵称和 ID 的映射表，在查询时先通过昵称查询到 ID，再通过 ID 查询完整数据，这个表也可是分库分表的，也需占用一定存储空间，但因为表中只有两个字段，所以相比重新做次拆分还是省不少空间。

• 尽量避免跨分区查询的发生(无法完全避免)
• 尽量使各个分片中的数据平均

如何存储无需分片的表

• 每个分片中存储一份相同的数据
  对于数据量不大且并不经常被更新的字典类表，经常需要和分区表一起关联查询，每个分片中存储一份冗余的数据可以更好提高查询效率，维护其一致性就很重要了。
• 使用额外的节点统一存储
  没有冗余问题,但是查询效率较差,需要汇总

在节点上部署分片

• 每个分片使用单一数据库,并且数据库名也相同
  结构也保持相同,和单一节点时的一致
• 将多个分片表存储在一个数据库中,并在表名上加入分片号后缀
• 在一个节点中部署多个数据库,每个数据库包含一个切片

5 分库分表后的难题

5.1 全局唯一ID生成方案

数据库自增ID

使用

• auto_increment_increment
• auto_increment_offset

系统变量让MySQL以期望的值和偏移量来增加auto_increment列的值。

• 优点
  最简单，不依赖于某节点，较普遍采用，但需要非常仔细的配置服务器哦！
• 缺点
  单点风险、单机性能瓶颈。不适用于一个节点包含多个分区表的场景。

数据库集群并设置相应步长（Flickr方案）

在一个全局数据库节点中创建一个包含auto_increment列的表，应用通过该表生成唯一数字。

• 优点
  高可用、ID较简洁。
• 缺点
  需要单独的数据库集群。

Redis缓存

避免了MySQL性能低的问题。

Snowflake（雪花算法）

• 优点
  高性能高可用、易拓展
• 缺点
  需要独立的集群以及ZK

各种GUID、Random算法

• 优点
  简单
• 缺点
  生成ID较长，且有重复几率

业务字段

为减少运营成本并减少额外风险，排除所有需要独立集群的方案，采用了带有业务属性的方案： 时间戳+用户标识码+随机数

优点

• 方便、成本低
• 基本无重复的可能
• 自带分库规则，这里的用户标识码即为userID的后四位，在查询场景，只需订单号即可匹配到相应库表而无需用户ID，只取四位是希望订单号尽可能短，评估后四位已足。
• 可排序，因为时间戳在最前

缺点

• 长度稍长，性能要比int/bigint的稍差。

事务

分库分表后，由于数据存到了不同库，数据库事务管理出现困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

• 解决方案
  比如美团，是将整个订单领域聚合体切分，维度一致，所以对聚合体的事务是支持的。

数据库特性

多表的 join 在单库时可通过一个 SQL 完成，但拆分到多个数据库后就无法跨库执行 SQL，好在 join 语法一般都被禁止使用，都是把两个表的数据取出后在业务代码里做筛选。
分库分表后，难免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次询能够完成的业务，可能需要多次查询才能完成。

• 解决方案
  垂直切分后，就跟join说拜拜了；水平切分后，查询的条件一定要在切分的维度内。
  比如查询具体某个用户下的订单等；
  禁止不带切分的维度的查询，即使中间件可以支持这种查询，可以在内存中组装，但是这种需求往往不应该在在线库查询，或者可以通过其他方法转换到切分的维度来实现。

在未分库分表前，查询数据总数时只需 SQL 执行 count()，现在数据被分散到多个库表，就要考虑其他方案，比方说将计数的数据单独存储在一张表或记录在 Redis。

额外的数据管理和运算压力

额外的数据管理负担，最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序解决，但必然引起额外的逻辑运算。
例如，对于一个记录用户成绩的用户数据表userTable，业务要求查出成绩最好的100位，在进行分表前，只需一个order by即可。但分表后，将需要n个order by语句，分别查出每一个分表前100名用户数据，然后再对这些数据进行合并计算，才能得出结果。

总结

并非所有表都需要水平拆分，要看增长的类型、速度，水平拆分是大招，拆分后会增加开发复杂度，不到万不得已不用。

拆分维度的选择很重要，要尽可能在解决拆分前问题的基础上，便于开发。

参考

• https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html
• 《Java工程师面试突击第1季》