MySQL分库分表

青旅半醒 2022-01-30 23:11 389阅读 0赞

## 一、概述：数据切分 ##

关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。

数据库分布式核心内容无非就是数据切分（Sharding），以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题，从而达到提升数据库操作性能的目的。

数据切分根据其切分类型，可以分为两种方式：垂直（纵向）切分和水平（横向）切分

## 二、垂直切分 ##

### 2.1 垂直分表 ###

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70]

小结  
在字段很多的情况下，拆分开确实更便于开发和维护（之前入职的一家公司，一个大表(product)中有 100 多列）。某种意义上也能避免“跨页”的问题（MySQL、MSSQL 底层都是通过“数据页”来存储的，“跨页”问题可能会造成额外的性能开销，这里不展开，感兴趣的朋友可以自行查阅相关资料进行研究）。

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

### 2.2 垂直分库 ###

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 1]

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

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

垂直切分的优点：

*  解决业务系统层面的耦合，业务清晰
 *  与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
 *  高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：

*  部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
 *  分布式事务处理复杂
 *  依然存在单表数据量过大的问题（需要水平切分）

## 三、水平切分 ##

### 3.1 水平分表 ###

水平分表也称为横向分表，比较容易理解，就是将表中不同的数据行按照一定规律分布到不同的数据库表中（这些表保存在同一个数据库中），这样来降低单表数据量，优化查询性能。最常见的方式就是通过主键或者时间等字段进行 Hash 和取模后拆分。如下图所示：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 2]

小结  
水平分表，能够降低单表的数据量，一定程度上可以缓解查询性能瓶颈。但本质上这些表还保存在同一个库中，所以库级别还是会有 IO 瓶颈。所以，一般不建议采用这种做法。

### 3.2 水平分库分表 ###

水平分库分表与上面讲到的水平分表的思想相同，唯一不同的就是将这些拆分出来的表保存在不同的数据库中。这也是很多大型互联网公司所选择的做法。如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 3]

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

### 3.3 几种典型的数据分片规则 ###

#### 3.3.1 根据数值范围 ####

按照时间区间或ID区间来切分。例如：按日期将不同月甚至是日的数据分散到不同的库中；将userId为1 ~ 9999的记录分到第一个库，10000~ 20000的分到第二个库，以此类推。某种意义上，某些系统中使用的"冷热数据分离"，将一些使用较少的历史数据迁移到其他库中，业务功能上只提供热点数据的查询，也是类似的实践。

这样的优点在于：

*  单表大小可控
 *  天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移
 *  使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。

缺点：

*  热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 4]

#### 3.3.2 根据数值取模 ####

一般采用hash取模mod的切分方式，例如：将 Customer 表根据 cusno 字段切分到4个库中，余数为0的放到第一个库，余数为1的放到第二个库，以此类推。这样同一个用户的数据会分散到同一个库中，如果查询条件带有cusno字段，则可明确定位到相应库去查询。

优点：

*  数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈

缺点：

*  后期分片集群扩容时，需要迁移旧的数据（使用一致性hash算法能较好的避免这个问题）
 *  容易面临跨分片查询的复杂问题。比如上例中，如果频繁用到的查询条件中不带cusno时，将会导致无法定位数据库，从而需要同时向4个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

## 四、分库分表带来的问题及挑战 ##

### 4.1 跨库 join ###

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

跨库 Join 的几种解决思路  
1、全局表

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

2、字段冗余

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

举个电商业务中很简单的场景：

“订单表”中保存“卖家 Id”的同时，将卖家的“Name”字段也冗余，这样查询订单详情的时候就不需要再去查询“卖家用户表”。

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

3、数据同步

定时 A 库中的 tab\_a 表和 B 库中 tbl\_b 有关联，可以定时将指定的表做同步。当然，同步本来会对数据库带来一定的影响，需要性能影响和数据时效性中取得一个平衡。这样来避免复杂的跨库查询。可以使用ETL工具进行同步。

4、系统层组装

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

5、ER分片

关系型数据库中，如果可以先确定表之间的关联关系，并将那些存在关联关系的表记录存放在同一个分片上，那么就能较好的避免跨分片join问题。在1:1或1:n的情况下，通常按照主表的ID主键切分。如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 5]  
这样一来，Data Node1上面的order订单表与orderdetail订单详情表就可以通过orderId进行局部的关联查询了，Data Node2上也一样。

### 4.2 列表查询带条件过滤的情况 ###

在上述例子中，都是简单的字段组装，而不存在条件过滤。看拆分前的 SQL：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 6]

这种连接查询并且还带条件过滤的情况，想在代码层面组装数据其实是非常复杂的（尤其是左表和右表都带条件过滤的情况会更复杂），不能像之前例子中那样简单的进行组装了。试想一下，如果像上面那样简单的进行组装，造成的结果就是返回的数据不完整，不准确。

有如下几种解决思路：

1.  查出所有的问答数据，然后调用用户服务进行拼装数据，再根据过滤字段 state 字段进行过滤，最后进行排序和分页并返回。这种方式能够保证数据的准确性和完整性，但是性能影响非常大，不建议使用。
2.  查询出 state 字段符合 / 不符合的 UserId，在查询问答数据的时候使用 in/not in 进行过滤，排序，分页等。过滤出有效的问答数据后，再调用用户服务获取数据进行组装。这种方式明显更优雅点。

### 4.3 跨节点分页、排序、函数问题 ###

跨节点多库进行查询时，会出现limit分页、order by排序等问题。分页需要按照指定字段进行排序，当排序字段就是分片字段时，通过分片规则就比较容易定位到指定的分片；当排序字段非分片字段时，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给用户。如图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 7]  
上图中只是取第一页的数据，对性能影响还不是很大。但是如果取得页数很大，情况则变得复杂很多，因为各分片节点中的数据可能是随机的，为了排序的准确性，需要将所有节点的前N页数据都排序好做合并，最后再进行整体的排序，这样的操作时很耗费CPU和内存资源的，所以页数越大，系统的性能也会越差。

在使用Max、Min、Sum、Count之类的函数进行计算的时候，也需要先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终将结果返回。如图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 8]

### 4.4 全局主键避免重复问题 ###

在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库自生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。有一些常见的主键生成策略：

#### 4.4.1 利用数据库自增ID ####

优点：最简单。 缺点：单点风险、单机性能瓶颈。

#### 4.4.2 UUID ####

UUID标准形式包含32个16进制数字，分为5段，形式为8-4-4-4-12的36个字符，例如：550e8400-e29b-41d4-a716-446655440000

UUID是主键是最简单的方案，本地生成，性能高，没有网络耗时。但缺点也很明显，由于UUID非常长，会占用大量的存储空间；另外，作为主键建立索引和基于索引进行查询时都会存在性能问题，在InnoDB下，UUID的无序性会引起数据位置频繁变动，导致分页。

#### 4.4.3 利用数据库集群并设置相应的步长（Flickr方案） ####

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

#### 4.4.4 Twitter Snowflake ####

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

#### 4.4.5 结合业务法 ####

带有业务属性的方案： > 时间戳+用户标识码+随机数  
有下面几个好处：

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

当然也有一些缺点，比如长度稍长，性能要比int/bigint的稍差等。

### 4.3 分布式事务问题 ###

这是一个庞大的问题，参见[博文“分布式事务”，传送门][Link 1]

## 五、实践案例 ##

[https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html][https_tech.meituan.com_2016_11_18_dianping-order-db-sharding.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70]: /images/20220131/32ef28201f764ee2bd868229f49b0c19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 1]: /images/20220131/537e4767cff54215aa4c934c8bb16501.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 2]: /images/20220131/3b403011b9dd483f8cb17ec7908f651a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 3]: /images/20220131/55124850d4b14432b9256f69e0863caa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 4]: /images/20220131/6a092e34583f4ae397096c8ad7258686.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 5]: /images/20220131/975c0ed394ac4e89b50771200f013790.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 6]: /images/20220131/9cbac467d60749d786820832c98bd7d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 7]: /images/20220131/246ecec509664225afcc159c5f17786e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 8]: /images/20220131/5472bbfd4d374abb9284f53847c0a7e5.png
[Link 1]: https://blog.csdn.net/zhaohong_bo/article/details/90449118
[https_tech.meituan.com_2016_11_18_dianping-order-db-sharding.html]: https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html