Java高并发系统设计-MySQL分库分表

1 为什么要分库分表

业务飞速发展导致了数据规模的急速膨胀，单机数据库已经无法适应互联网业务的发展。

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

• 性能 由于MySQL采用 B+树索引，数据量超过阈值时，索引深度的增加也将使得磁盘访问的 IO 次数增加，进而导致查询性能的下降；高并发访问请求也使得集中式数据库成为系统的最大瓶颈。
• 可用性 服务化的无状态型，能够达到较小成本的随意扩容，这必然导致系统的最终压力都落在数据库之上。而单一的数据节点，或者简单的主从架构，已经越来越难以承担。从运维成本方面考虑，当一个数据库实例中的数据达到阈值以上，数据备份和恢复的时间成本都将随着数据量的大小而愈发不可控。

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

影响：

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

从读写分离到数据库拆分

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

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

2 概念辨析

分库和分表是两码事，可能光分库不分表，也可能光分表不分库。 比如业务发展迅猛，注册用户数达到了2000万！每天活跃用户数100万！每天单表数据量10万条！高峰期每秒最大请求达到1000！感觉压力大。 因为每天多10万条数据，一个月就多300万条数据，现在咱们单表已经几百万数据了，马上就破千万了。但是勉强还能撑着。高峰期请求现在是1000，咱们线上部署了几台机器，负载均衡搞了一下，数据库撑1000 QPS也还行。但现在开始担心了，接下来咋整？

此时每天活跃用户数上千万，每天单表新增数据达50万，目前一个表总数据量都已经达到了两三千万了！扛不住！数据库磁盘容量不断消耗！高峰期并发达到5000~8000！你的系统绝对支撑不到现在，已经挂掉了！

所以分库分表实际上是跟着公司业务发展走的，你公司业务发展越好，用户就越多，数据量越大，请求量越大，那你单个数据库一定扛不住。

比如你单表都几千万数据了，你确定你能抗住么？ 绝对不行，单表数据量太大，会极大影响你的 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 集合去产品库查询所有的产品信息，最后再业务代码里进行组装把一个有很多字段的表给拆分成多个表或库。每个库表的结构都不一样，每个库表都包含部分字段。

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

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

8 分库分表的方式

按range分

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

• 好处 后面扩容的时候，就很容易，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了
• 缺点 但是大部分的请求，都是访问最新的数据。实际生产用range，要看场景，你的用户不是仅仅访问最新的数据，而是均匀的访问现在的数据以及历史的数据

按某字段hash

均匀分散，最为常用。

• 好处 可以平均分配没给库的数据量和请求压力
• 坏处 扩容起来比较麻烦，会有一个数据迁移的过程

1 Sharding

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

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

2 垂直切分/水平切分

2.1 MySQL的扩展方案

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

2.2 MySQL的Sharding策略

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

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

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区间切分。

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

4.1.3 Hash切分（重点）

• 一般都是采用hash切分。

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

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

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

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

数据库性能达到瓶颈

1. 现有规则不变，可直接扩展到32个数据库集群。

1. 如果32个集群也无法满足需求，那么将分库分表规则调整为(32*2n)*(32⁄2n)，可以达到最多1024个集群。

单表容量达到瓶颈

或1024都无法满足。

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

选择分片键

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

如何存储无需分片的表

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

在节点上部署分片

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

5 分库分表后的难题

5.1 全局唯一ID生成方案

5.1.1 数据库自增ID

使用

• auto_increment_increment
• auto_increment_offset

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

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

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

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

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

5.1.3 Redis缓存

避免了MySQL性能低的问题。

5.1.4 Snowflake（雪花算法）

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

5.1.5 各种GUID、Random算法

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

5.1.6 业务字段（美团实践方案）

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

优点

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

缺点

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

事务

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

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

跨库跨表的join问题

分库分表之后，难免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次询能够完成的业务，可能需要多次查询才能完成。

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

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

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

总结

并非所有表都需要水平拆分，要看增长的类型和速度，水平拆分是大招，拆分后会增加开发的复杂度，不到万不得已不使用。 拆分维度的选择很重要，要尽可能在解决拆分前问题的基础上，便于开发。

参考

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