mysql_分库、分表、分区详解

野性酷女 2024-03-27 08:48 99阅读 0赞

## 1、 读写分离 & 一主多备 ##

> 在实际的应用中，绝大部分情况都是读远大于写。Mysql提供了读写分离的机制，所有的写操作都必须对应到Master，读操作可以在 Master和Slave机器上进行，Slave与Master的结构完全一样，一个Master可以有多个Slave，甚至Slave下还可以挂 Slave，通过此方式可以有效的提高DB集群的 QPS。

> 所有的写操作都是先在Master上操作，然后同步更新到Slave上，所以从Master同步到Slave机器有一定的延迟，当系统很繁忙的时候，延迟问题会更加严重，Slave机器数量的增加也会使这个问题更加严重。

> 注意：所有的写操作都是先在Master上操作，所以Master是集群的瓶颈，当写操作过多，会严重影响到Master的稳定性，如果Master挂掉，整个集群都将不能正常工作。  
> 当读压力很大的时候，可以考虑添加Slave机器的分式解决  
> 但是当Slave机器达到一定的数量就得考虑分库了。  
> 当写压力很大的时候，就必须得进行分库操作。

## 2. 分库分表 ##

### 2.1. 分库分表的基本原理 ###

> 为什么要分库分表  
> – 用户请求量太，但是单服务器 TPS，内存，IO 都是有限的；  
> – 单库太大，单库所在服务器上磁盘空间不足，单库处理能力有限，存在 IO 瓶颈；  
> – 单表太大，索引膨胀，查询超时；

> 分库分表的目标  
> – 分表，可以解决单表海量数据的读写性能问题；  
> – 分库，可以解决单台数据库的并发访问压力问题。

> 拆分思路  
> – 垂直拆分，垂直分库、垂直分表  
> – 水平拆分，水平分库、水平分表

> 垂直拆分：是指按功能模块拆分，垂直拆分解决表与表之间的 I/O 竞争。比如分为订单库、商品库、用户库…这种方式多个数据库之间的表结构不同。

> 水平拆分：将同一个表的数据进行分块保存到不同的数据库中，水平拆分解决单表中数据量增长出现的压力。这些数据库中的表结构完全相同。

> 分库分表的顺序  
> 分库分表的顺序应该是先垂直分，后水平分。因为垂直分更简单，更符合我们处理现实世界问题的方式

### 2.2. 垂直分表 ###

> 垂直分表，是基于列（字段）对表格进行横向拆分。  
> 垂直分表规则：  
> – 数据较大的字段，单独存表；  
> – 不常用的的字段，单独存表；  
> – 经常一起使用的字段，放在一起存表；  
> – 长度较长（比如text类型字段）的字段，单独存表；

> 垂直分表，一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。  
> 分表能够解决单表数据量过大带来的查询效率下降的问题，但是，却无法给数据库的并发处理能力带来质的提升。面对高并发的读写访问，

> 当数据库master服务器无法承载写操作压力时，不管如何扩展slave服务器，此时都没有意义了。因此，我们必须换一种思路，对数据库进行拆分，从而提高数据库写入能力，这就是所谓的分库。

### 2.3. 垂直分库 ###

> 垂直分库，是针对一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。

> 垂直切分后，一般放在多个服务器上，而不是一个服务器上。这是因为，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前， 全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。

> 按垂直分库后，如果还是放在一个数据库服务器上， 随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。 所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。

> 数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。数据库业务层面的拆分，能对不同业务的数据分别的进行管理，维护，监控，扩展等。

### 2.4. 水平分表（不单独使用） ###

> 针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。

> 水平分表的问题：  
> – 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。  
> – 无法进行跨表直连连接查询；  
> – 统计数据不方便；  
> – 如果数据持续增长，达到现有分表的瓶颈，需要增加分表时、会出现数据重新排列的情况。  
> 生产环境中，水平分表一般不单独使用，而是和水平分库一起使用，做水平分库分表。

### 2.5. 水平分库（一般与水平分表同时使用） ###

> 将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

> 水平分库分表策略：  
> – 用户 id 取模；  
> – 如果不是整数，可以进行 hash 获取到整数；  
> – 按照地理区域拆分；  
> – 按照时间拆分；  
> – 按照【冷热数据】拆分；

> 分库分表的标准  
> 什么样的情况需要分库分表：  
> – 表体积大于 2G，行数大于 1000 万；  
> – 表中含有 text、blob、varchar(1000)；  
> – 数据有时效性，可以单独拿出来归档；  
> 分库分表的大小多少合适  
> 分表最大分1024，一般分100左右比较适合。

### 2.6. 分库分表存在的问题 ###

> 维度的问题  
> 假如用户购买了商品,需要将交易记录保存取来，如果按照用户的纬度分表，则每个用户的交易记录都保存在同一表中，所以很快很方便的查找到某用户的 购买情况，但是某商品被购买的情况则很有可能分布在多张表中，查找起来比较麻烦。反之，按照商品维度分表，可以很方便的查找到此商品的购买情况，但要查找 到买人的交易记录比较麻烦。  
> 维度的问题解决办法：记录【两份数据】，一份按照用户纬度分表，一份按照商品维度分表。

> 联合查询的问题  
> 联合查询基本不可能，因为关联的表有可能不在同一数据库中。这是因为分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

> 联合查询问题的解决方法：  
> – 全局表：基础数据，所有库都拷贝一份；  
> – 字段冗余：这样有些字段就不用join去查询了；  
> – 系统层组装：分别查询出所有，然后组装起来，较复杂。

> 跨库事务问题  
> 避免在一个事务中修改db0中的表的时候、同时修改db1中的表，一个是操作起来更复杂，效率也会有一定影响。  
> 分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

> 数据依赖问题  
> 例如卖家a的商品和交易信息都放到db0中，当db1挂了的时候，卖家a相关的东西可以正常使用。尽量把同一组数据放到同一DB服务器上，避免数据库中的数据依赖另一数据库中的数据。

### 2.7. 分库分表产品方案 ###

> MySQL Proxy；  
> Amoeba；  
> Hibernate Shards；  
> sharding-jdbc；

## 3. 分区分片 ##

### 3.1. 分区原理 ###

> 表分区，就是将数据量比较大的表，在物理上分成若干个小表，从逻辑来看还是一个大表。MySQL 5 之后才有了数据表分区功能。  
> 数据库的应用分为两类：一类是OLTP（在线事务处理），如Blog、电子商务、网络游戏等；另一类是OLAP（在线分析处理），如数据仓库、数据集市。

> 对于OLAP的应用，分区的确是可以很好地提高查询的性能。  
> 因为OLAP应用大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据。如果按时间戳进行分区，则只需要扫描相应的分区即可。

> 对于OLTP的应用，分区应该非常小心。  
> 在这种应用下，通常不可能会获取一张大表10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2~3次的磁盘IO。因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。  
> MySQL的分区字段，必须包含在主键字段内。

### 3.2. 分区策略 ###

> RANGE分区  
> 最常用的一种分区类型，基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。

> LIST分区  
> – LIST分区和RANGE分区类似，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择，而非连续的。  
> – LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value\_list)”的方式来定义每个分区，其中“value\_list”是一个通过逗号分隔的整数列表。  
> – 不同于RANGE分区中定义的VALUES LESS THAN语句，LIST分区使用VALUES IN，因为每个分区的值是离散的，因此只能定义值。

> HASH分区  
> – HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据量大致都是一样的。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分隔成的分区数量。  
> – 要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量，如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。

### 3.3. 分区使用场景： ###

> 一张表的查询速度已经慢到影响使用的时候；  
> 表中的数据是分段的；  
> 对数据的操作往往只涉及一部分数据，而不是所有的数据；  
> 数据库比较多、并发不多，可以采用表分区；  
> 数据量比较大、并发较高，可以采用分库分表和分区相结合；

### 3.4. 分表和分区的区别 ###

> 实现方式  
> mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完正的一张表，都对应三个文件（MyISAM引擎：一个.MYD数据文件，.MYI索引文件，.frm表结构文件）。  
> 数据处理  
> 分表后数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。分区则不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表还是一张表，数据处理还是由自己来完成。  
> 提高性能  
> 分表后，单表的并发能力提高了，磁盘I/O性能也提高了。分区突破了磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。 在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

> 实现的难易度  
> 分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式和分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。 分区实现是比较简单的，建立分区表，跟建平常的表没什么区别，并且对代码端来说是透明的。

### 3.5. 分片策略 ###

> 分片，是把数据库横向扩展（Scale Out）到多个物理节点上的一种有效的方式。其主要目的，是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。Shard这个词的意思是“碎片”。如果将一个数据库当作一块大玻璃，将这块玻璃打碎，那么每一小块都称为数据库的碎片（DatabaseShard）。将整个数据库打碎的过程就叫做分片，可以翻译为分片。  
> 形式上，分片可以简单定义为将大数据库分布到多个物理节点上的一个分区方案。每一个分区包含数据库的某一部分，称为一个片，分区方式可以是任意的，并不局限于传统的水平分区和垂直分区。一个分片可以包含多个表的内容甚至可以包含多个数据库实例中的内容。每个分片被放置在一个数据库服务器上。一个数据库服务器可以处理一个或多个分片的数据。系统中需要有服务器进行查询路由转发，负责将查询转发到包含该查询所访问数据的分片或分片集合节点上去执行。

### 3.6. 分片和分区的区别 ###

> 分片和分区，有很多的相似之处。有的时候，分片（Sharding） 也被近似等同于水平分区（Horizontal Partitioning），网上很多地方也用水平分区来指代 分片（Sharding）。