ddl mysql 分库分表 mysql分库分表原理

一、为什么要分库分表

首先说明，这里所说的分库分表是指把数据库数据的物理拆分到多个实例或者多台机器上去，而不是类似分区表的原地切分。

关系型数据库本身比较容易成为系统性能瓶颈，单机存储容量、连接数、处理能力等都很有限，数据库本身的“有状态性”导致了它并不像 Web 和应用服务器那么容易扩展。在互联网行业海量数据和高并发访问的考验下，聪明的技术人员提出了分库分表技术（有些地方也称为 Sharding、分片。例如 MongoDB、ElasticSearch 等）。同时，流行的分布式系统中间件，均自身友好支持 Sharding，其原理和思想都是大同小异的。

目前针对海量数据的优化，其分库分表是 MySQL 永远的话题，一般情况下认为 MySQL 是个简单的数据库，在数据量大到一定程度之后处理查询的效率降低，如果需要继续保持高性能运转的话，必须分库或者分表了。关于数据量达到多少大是个极限这个事儿，本文先不讨论，研究源码的同学已经证实 MySQL 或者 Innodb 内部的锁粒度太大的问题大大限制了 MySQL 提供 QPS 的能力或者处理大规模数据的能力。在这点上，一般的使用者只好坐等官方不断推出的优化版本了。

二、分库分表原则

原则0：能不分就不分

是的，MySQL 是关系数据库，数据库表之间的关系从一定的角度上映射了业务逻辑。任何分库分表的行为都会在某种程度上提升业务逻辑的复杂度，数据库除了承载数据的存储和访问外，协助业务更好的实现需求和逻辑也是其重要工作之一。分库分表会带来数据的合并，查询或者更新条件的分离，事务的分离等等多种后果，业务实现的复杂程度往往会翻倍或者指数级上升。所以，在分库分表之前，不要为分而分，去做其他力所能及的事情吧，例如升级硬件，升级，升级网络，升级数据库版本，读写分离，负载均衡等等。所有分库分表的前提是，这些你已经尽力了。

原则1：数据量太大，正常的运维影响正常业务访问

这里说的运维，例如：

• 对数据库的备份。如果单表或者单个实例太大，在做备份的时候需要大量的磁盘 IO 或者网络 IO 资源。例如 1T 的数据，网络传输占用 50MB 的时候，需要 20000 秒才能传输完毕，在此整个过程中的维护风险都是高于平时的。我们在 Qunar 的做法是给所有的数据库机器添加第二块网卡，用来做备份，或者 SST，Group Communication 等等各种内部的数据传输。1T 的数据的备份，也会占用大量的磁盘 IO，如果是 SSD 还好，当然这里忽略某些厂商的产品在集中 IO 的时候会出一些 BUG 的问题。如果是普通的物理磁盘，则在不限流的情况下去执行 xtrabackup，该实例基本不可用。
• 对数据表的修改。如果某个表过大，对此表做 DDL 的时候，MySQL 会锁住全表，这个时间可能很长，在这段时间业务不能访问此表，影响甚大。解决的办法有类似腾讯游戏 DBA 自己改造的可以在线秒改表，不过他们目前也只是能添加字段而已，对别的 DDL 还是无效；或者使用 pt-online-schema-change，当然在使用过程中，它需要建立触发器和影子表，同时也需要很长很长的时间，在此操作过程中的所有时间，都可以看做是风险时间。把数据表切分，总量减小，有助于改善这种风险。
• 整个表热点，数据访问和更新频繁，经常有锁等待，你又没有能力去修改源码，降低锁的粒度，那么只会把其中的数据物理拆开，用空间换时间，变相降低访问压力。

原则2：表设计不合理，需要对某些字段垂直拆分

这里举一个例子，如果你有一个用户表，在最初设计时，是这样：

• id bigint #用户的 ID
• name varchar #用户的名字
• last_login_time datetime #最近登录时间
• personal_info text #私人信息 xxxxx #其他信息字段

设想情况一：你的业务中彩了，用户数从 100w 飙升到 10 个亿。你为了统计活跃用户，在每个人登录的时候都会记录一下他的最近登录时间。并且的用户活跃得很，不断的去更新这个 login_time，搞的你的这个表不断的被 update，压力非常大。那么，在这个时候，只要考虑对它进行拆分，站在业务的角度，最好的办法是先把 last_login_time 拆分出去，我们叫它 user_time。这样做，业务的代码只有在用到这个字段的时候修改一下就行了。如果你不这么做，直接把 users 表水平切分了，那么，所有访问 users 表的地方，都要修改。或许你会说，我有 proxy，能够动态 merge 数据。到目前为止我还从没看到谁家的 proxy 不影响性能的。

设想情况二：personal_info 这个字段本来没啥用，你就是让用户注册的时候填一些个人爱好而已，基本不查询。一开始的时候有它没它无所谓。但是到后来发现两个问题，一，这个字段占用了大量的空间，因为是 text 嘛，有很多人喜欢长篇大论地介绍自己。更糟糕的是二，不知道哪天哪个产品经理心血来潮，说允许个人信息公开吧，以方便让大家更好的相互了解。那么在所有人猎奇窥私心理的影响下，对此字段的访问大幅度增加。数据库压力瞬间抗不住了，这个时候，只好考虑对这个表的垂直拆分了。

原则3：某些数据表出现了无穷增长

例子很好举，各种的评论，消息，日志记录。这个增长不是跟人口成比例的，而是不可控的，例如微博的 feed 的广播，我发一条消息，会扩散给很多很多人。虽然主体可能只存一份，但不排除一些索引或者路由有这种存储需求。这个时候，增加存储，提升机器配置已经苍白无力了，水平切分是最佳实践。拆分的标准很多，按用户的，按时间的，按用途的，不在一一举例。

原则4：安全性和可用性的考虑

这个很容易理解，鸡蛋不要放在一个篮子里，我不希望我的数据库出问题，但我希望在出问题的时候不要影响到 100%的用户，这个影响的比例越少越好，那么，水平切分可以解决这个问题，把用户，库存，订单等等本来同统一的资源切分掉，每个小的数据库实例承担一小部分业务，这样整体的可用性就会提升。这对 Qunar 这样的业务还是比较合适的，人与人之间，某些库存与库存之间，关联不太大，可以做一些这样的切分。

原则5：业务耦合性考虑

这个跟上面有点类似，主要是站在业务的层面上，我们的火车票业务和烤羊腿业务是完全无关的业务，虽然每个业务的数据量可能不太大，放在一个 MySQL 实例中完全没问题，但是很可能烤羊腿业务的 DBA 或者开发人员水平很差，动不动给你出一些幺蛾子，直接把数据库搞挂。这个时候，火车票业务的人员虽然技术很优秀，工作也很努力，照样被老板打屁股。解决的办法很简单:惹不起，躲得起。

三、分库分表方案

垂直拆分

垂直拆分常见有 垂直分库 和 垂直分表 两种。

垂直分表在日常开发和设计中比较常见，通俗的说法叫做“大表拆小表”，拆分是基于关系型数据库中的“列”（字段）进行的。通常情况，某个表中的字段比较多，可以新建立一张“扩展表”，将不经常使用或者长度较大的字段拆分出去放到“扩展表”中，如下图所示：

在字段很多的情况下，拆分开确实更便于开发和维护（笔者曾见过某个遗留系统中，一个大表中包含 100 多列的）。某种意义上也能避免“跨页”的问题（MySQL、MSSQL 底层都是通过“数据页”来存储的，“跨页”问题可能会造成额外的性能开销，这里不展开，感兴趣的朋友可以自行查阅相关资料进行研究）。

拆分字段的操作建议在数据库设计阶段就做好。如果是在发展过程中拆分，则需要改写以前的查询语句，会额外带来一定的成本和风险，建议谨慎。

垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联。

另外，在“微服务”盛行的今天已经非常普及了，按照业务模块来划分出不同的数据库，也是一种垂直拆分。而不是像早期一样将所有的数据表都放到同一个数据库中。如下图：

垂直拆分优点：

• 可以使得行数据变小，一个数据块 (Block) 就能存放更多的数据，在查询时就会减少 I/O 次数 (每次查询时读取的 Block 就少)。
• 可以达到最大化利用 Cache 的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起。
• 数据维护简单。

垂直拆分缺点：

• 主键出现冗余，需要管理冗余列。
• 会引起表连接 JOIN 操作（增加 CPU 开销）可以通过在业务服务器上进行 join 来减少数据库压力。
• 依然存在单表数据量过大的问题（需要水平拆分）。
• 事务处理复杂。

垂直拆分小结：

• 系统层面的“服务化”拆分操作，能够解决业务系统层面的耦合和性能瓶颈，有利于系统的扩展维护。而数据库层面的拆分，道理也是相通的。与服务的“治理”和“降级”机制类似，我们也能对不同业务类型的数据进行“分级”管理、维护、监控、扩展等。

众所周知，数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于 Web 和应用服务器来讲，是比较难实现“横向扩展”的。数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破 IO、连接数及单机硬件资源的瓶颈，是大型分布式系统中优化数据库架构的重要手段。

然后，很多人并没有从根本上搞清楚为什么要拆分，也没有掌握拆分的原则和技巧，只是一味的模仿大厂的做法。导致拆分后遇到很多问题（例如：跨库 join，分布式事务等）。

水平拆分

水平拆分是通过某种策略将数据分片来存储，分为库内分表和分库分表两部分，每片数据会分散到不同的 MySQL 表或库，达到分布式的效果，能够支持非常大的数据量。

库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻 MySQL 服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的 IO、CPU、网络，这个就要通过分库分表来解决。

最常见的方式就是通过主键或者时间等字段进行 Hash 和取模后拆分。如下图所示：

当下分表有静态分表和动态分表两种：

静态分表：

事先估算出表能达到的量，然后根据每一个表需要存多少数据直接算出需要创建表的数量。如：1 亿数据每一个表 100W 条数据那就要建 100 张表，然后通过一定的 hash 算法计算每一条数据存放在那张表。其实就有点像是使用 partition table 一样。静态分表有一个毙命就是当分的那么多表还不满足时，需要再扩展难度和成本就会很高。

动态分表：

同样也是对大数据量的表进行拆分，他可以避免静态分表带来的后遗症。当然也需要在设计上多一些东西（这往往是我们能接受的）。

某种意义上来讲，有些系统中使用的“冷热数据分离”（将一些使用较少的历史数据迁移到其他的数据库中。而在业务功能上，通常默认只提供热点数据的查询），也是类似的实践。在高并发和海量数据的场景下，分库分表能够有效缓解单机和单库的性能瓶颈和压力，突破 IO、连接数、硬件资源的瓶颈。当然，投入的硬件成本也会更高。同时，这也会带来一些复杂的技术问题和挑战（例如：跨分片的复杂查询，跨分片事务等）。

水平拆分优点:

• 不存在单库大数据和高并发的性能瓶颈。
• 应用端改造较少。
• 提高了系统的稳定性和负载能力。

水平拆分缺点：

• 分片事务一致性难以解决。
• 跨节点 Join 性能差，逻辑复杂。
• 数据多次扩展难度跟维护量极大。

四、分库分表难点

垂直分库带来的问题和解决思路：

1. 跨库 join 的问题

在拆分之前，系统中很多列表和详情页所需的数据是可以通过 sql join 来完成的。而拆分后，数据库可能是分布式在不同实例和不同的主机上，join 将变得非常麻烦。而且基于架构规范，性能，安全性等方面考虑，一般是禁止跨库 join 的。那该怎么办呢？首先要考虑下垂直分库的设计问题，如果可以调整，那就优先调整。如果无法调整的情况，下面笔者将结合以往的实际经验，总结几种常见的解决思路，并分析其适用场景。

跨库 Join 的几种解决思路

全局表

所谓全局表，就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。为了避免跨库 join 查询，我们可以将这类表在其他每个数据库中均保存一份。同时，这类数据通常也很少发生修改（甚至几乎不会），所以也不用太担心“一致性”问题。

字段冗余

这是一种典型的反范式设计，在互联网行业中比较常见，通常是为了性能来避免 join 查询。

举个电商业务中很简单的场景：“订单表”中保存“卖家 Id”的同时，将卖家的“Name”字段也冗余，这样查询订单详情的时候就不需要再去查询“卖家用户表”。

字段冗余能带来便利，是一种“空间换时间”的体现。但其适用场景也比较有限，比较适合依赖字段较少的情况。最复杂的还是数据一致性问题，这点很难保证，可以借助数据库中的触发器或者在业务代码层面去保证。当然，也需要结合实际业务场景来看一致性的要求。就像上面例子，如果卖家修改了 Name 之后，是否需要在订单信息中同步更新呢？

数据同步

定时 A 库中的 tab_a 表和 B 库中 tbl_b 有关联，可以定时将指定的表做同步。当然，同步本来会对数据库带来一定的影响，需要性能影响和数据时效性中取得一个平衡。这样来避免复杂的跨库查询。笔者曾经在项目中是通过 ETL 工具来实施的。

系统层组装

在系统层面，通过调用不同模块的组件或者服务，获取到数据并进行字段拼装。说起来很容易，但实践起来可真没有这么简单，尤其是数据库设计上存在问题但又无法轻易调整的时候。具体情况通常会比较复杂。

2. 跨库事务（分布式事务）问题

按业务拆分数据库之后，不可避免的就是“分布式事务”的问题。想要了解分布式事务，就需要了解“XA 接口”和“两阶段提交”。值得提到的是，MySQL5.5x 和 5.6x 中的 xa 支持是存在问题的，会导致主从数据不一致。直到 5.7x 版本中才得到修复。Java 应用程序可以采用 Atomikos 框架来实现 XA 事务（J2EE 中 JTA）。感兴趣的读者可以自行参考《分布式事务一致性解决方案》

五、常见分片规则和策略

分布式全局唯一 ID

在很多中小项目中，我们往往直接使用数据库自增特性来生成主键 ID，这样确实比较简单。而在分库分表的环境中，数据分布在不同的分片上，不能再借助数据库自增长特性直接生成，否则会造成不同分片上的数据表主键会重复。简单介绍下使用和了解过的几种 ID 生成算法。

1. Twitter 的 Snowflake（又名“雪花算法”）
2. UUID/GUID（一般应用程序和数据库均支持）
3. MongoDB ObjectID（类似 UUID 的方式）
4. Ticket Server（数据库生存方式，Flickr 采用的就是这种方式）

其中，Twitter 的 Snowflake 算法是近几年在分布式系统项目中使用最多的，未发现重复或并发的问题。该算法生成的是 64 位唯一 Id（由 41 位的 timestamp+10 位自定义的机器码+13 位累加计数器组成）。这里不做过多介绍，感兴趣的读者可自行查阅相关资料。

分片字段该如何选择

在开始分片之前，我们首先要确定分片字段（也可称为“片键”）。很多常见的例子和场景中是采用 ID 或者时间字段进行拆分。这也并不绝对的，我的建议是结合实际业务，通过对系统中执行的 sql 语句进行统计分析，选择出需要分片的那个表中最频繁被使用，或者最重要的字段来作为分片字段。

常见的分片策略有随机分片和连续分片这两种，如下图所示：

当需要使用分片字段进行范围查找时，连续分片可以快速定位分片进行高效查询，大多数情况下可以有效避免跨分片查询的问题。后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移。但是，连续分片也有可能存在数据热点的问题，就像图中按时间字段分片的例子，有些节点可能会被频繁查询压力较大，热数据节点就成为了整个集群的瓶颈。而有些节点可能存的是历史数据，很少需要被查询到。

随机分片其实并不是随机的，也遵循一定规则。通常，我们会采用 Hash 取模的方式进行分片拆分，所以有些时候也被称为离散分片。随机分片的数据相对比较均匀，不容易出现热点和并发访问的瓶颈。但是，后期分片集群扩容起来需要迁移旧的数据。使用一致性 Hash 算法能够很大程度的避免这个问题，所以很多中间件的分片集群都会采用一致性 Hash 算法。离散分片也很容易面临跨分片查询的复杂问题。

数据迁移，容量规划，扩容等问题

很少有项目会在初期就开始考虑分片设计的，一般都是在业务高速发展面临性能和存储的瓶颈时才会提前准备。因此，不可避免的就需要考虑历史数据迁移的问题。一般做法就是通过程序先读出历史数据，然后按照指定的分片规则再将数据写入到各个分片节点中。

此外，我们需要根据当前的数据量和 QPS 等进行容量规划，综合成本因素，推算出大概需要多少分片（一般建议单个分片上的单表数据量不要超过 1000W）。

如果是采用随机分片，则需要考虑后期的扩容问题，相对会比较麻烦。如果是采用的范围分片，只需要添加节点就可以自动扩容。

六、跨分片技术问题

跨分片的排序分页

一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。如下图所示：

上面图中所描述的只是最简单的一种情况（取第一页数据），看起来对性能的影响并不大。但是，如果想取出第 10 页数据，情况又将变得复杂很多，如下图所示：

有些读者可能并不太理解，为什么不能像获取第一页数据那样简单处理（排序取出前 10 条再合并、排序）。其实并不难理解，因为各分片节点中的数据可能是随机的，为了排序的准确性，必须把所有分片节点的前 N 页数据都排序好后做合并，最后再进行整体的排序。很显然，这样的操作是比较消耗资源的，用户越往后翻页，系统性能将会越差。

跨分片的函数处理

在使用 Max、Min、Sum、Count 之类的函数进行统计和计算的时候，需要先在每个分片数据源上执行相应的函数处理，然后再将各个结果集进行二次处理，最终再将处理结果返回。如下图所示：