您现在的位置是:首页 >技术教程 >MySQL原理(九):表分区和分库分表网站首页技术教程

MySQL原理(九):表分区和分库分表

贪玩的木木 2024-06-17 10:14:45
简介MySQL原理(九):表分区和分库分表

前言

上一篇介绍了 MySQL 的存储过程和触发器,这一篇将介绍表分区和分库分表相关的内容。

表分区

原本的表文件都是以完整的形式存储在磁盘中,而表分区则是指将一张表的数据拆分成多个磁盘文件,然后放到磁盘中存储。

做了表分区之后,表在逻辑上还是同一张,只是磁盘中会划分为多个文件存储而已,所以表分区并不会影响原有的增删改查操作。

表分区只能进行水平划分,即以行为粒度进行划分,一条记录只能在一个分区中。

好处:

  1. 相较于使用单个文件存储表数据,表分区技术可以打破单个磁盘分区的容量限制。
  2. 对于一些失效数据,如三年前的数据,可以通过快速删除分区的方式清理,效率十分高。
  3. 能够在一定程度上提升磁盘 IO 检索数据的性能,毕竟只需对一小片磁盘表文件做寻道。
  4. 支持聚合函数的并行执行,比如 sum()、count() 这类函数,可以分别统计各分区的数据做汇总。
  5. 带来更好的数据管理性和可用性,当一个表文件受损时,不会影响其他分区文件中的表数据。

限制:

  • 单张表最多只能创建 1024 个分区,MySQL5.6 版本中拓展到 8192 个。
  • MySQL5.1 及之前的版本中,分区键只能选择整数型字段,或支持哈希函数处理的字段。
  • 对一个表做了分区后,后续使用表的过程中,无法对表上的其他字段建立唯一索引。
  • 分区表中无法创建外键,不过一般情况下表也不允许创建外键,都是靠逻辑上维护主外关系。
  • 表中存在主键、唯一键的情况下,分区键的字段必须为主键或唯一键的部分或全部字段。

在 MySQL 中总共支持六种分区类型:range、list、hash、key、sub、columns。

RANGE

按照一个字段范围进行分区,仅支持整数类型字段作为分区键,如果想要以日期字段来做数据分区,需要想将其转换为整数格式的时间戳。

partition by range(r_id)(
	partition p1 values less than (100000),
	partition p2 values less than (200000),
	partition p3 values less than (300000),
	partition p4 values less than maxvalue
);

-- 查询 zz_range 表中不同分区的数据量
select 
    partition_name,table_rows 
from 
    information_schema.partitions 
where 
    table_name = 'zz_range';

LIST

枚举分区,只支持整数字段作为分区键。如果插入的数据在所有分区中找不到对应的值,会直接报错。

partition by list(l_sex)(
	partition p1 values in (0),
	partition p2 values in (1)
);

HASH

哈希分区中支持两种哈希分区法:

  • 常规哈希:基于某个整数型字段,直接做取模,最后根据余数来决定数据的分区。
  • 线性哈希:基于某个字段哈希之后的哈希值,进行取模运算,最后根据余数来决定数据的分区。

常规哈希只能基于整数型字段对数据做划分;线性哈希则可以不限制字段的类型,只要能够通过 MySQL 哈希函数,转换出哈希值的字段类型都可以作为分区键(但本质上 MySQL 中好像没有提供将字符串转换为数值类型的哈希函数)。

-- 选用 h_id 作为分区键,划分为三个分区
partition by hash(h_id)
partitions 3;
-- 使用线性哈希分区
partition by linear hash(lh_id)
partitions 3;

KEY

在 hash 分区中,想要使用一个字段作为分区键,要么这个字段本身是整数类型,要么这个字段经过哈希函数处理后,能够得到一个整数的哈希值才行。但在 key 分区中,除开不支持 text、blob 两种类型外,其他类型的字段都可以作为分区键。

在 key 分区中也可以不显式指定分区键,MySQL 会自动选择,但不管是自己显式声明分区键,亦是 MySQL 自动选取分区键,都会遵循如下规则:

  • 表中只存在主键或唯一字段时,分区键必须为主键/唯一键的部分或全部字段,不允许选择其他字段。
  • 表中主键、唯一字段同时存在时,分区键必须为主键和唯一键共有的部分或全部字段。
  • 当表中不存在主键或唯一键时,分区键可以是除 text、blob 类型外的任意单个或多个字段。
partition by key(k_name)
partitions 3;

SUB

又称子分区,所谓的子分区是指基于表分区后的结果,进一步做分区处理,也就是基于一个分区再做分区,好比一张表可以基于日期中的年份做分区,基于年份做了分区后,还可以基于年分区进一步做月分区。

这种方式要求每个一级分区下的二级分区数量都一致,同时二级分区的类型只能为 hash、key 类型。

partition by range(year(register_time))
subpartition by hash(month(register_time))
(
	partition p1 values less than (2000)(
		subpartition p1_s1,
		subpartition p1_s2
	),
	partition p2 values less than (2020)(
		subpartition p2_s1,
		subpartition p2_s2
	),
	partition p3 values less than maxvalue(
		subpartition p3_s1,
		subpartition p3_s2
	)
);

COLUMNS

cloumns 分区实际上是 range、list 分区的变种,cloumns 分区可以使得 range、list 的分区键由多个字段来组成,同时支持的字段类型也相对更丰富一些,但这种分区法一般用的极少。

分表

垂直分表

当一张表由于字段过多时,会导致表中每行数据的体积变大,一方面会导致磁盘 IO 次数增多,影响数据的读写效率;同时另一方面结果集响应时还会占用大量网络带宽,影响数据的传输效率;再从内存维度来看,单行数据越大,缓冲区中能放下的热点数据页会越少,当读写操作无法在内存中定位到相应的数据页,从而又会产生大量的磁盘 IO。

垂直分表一半可以根据冷热字段对表进行拆分。拆分的表中需要保存外键来建立联系。

由于修改数据时会同时修改多张表,所以需要使用事务来保证原子性。

水平分表

当一张表内的数据量过大(一般要求控制在 500-1200w 之间),查询性能就会下降,从而需要对表进行水平拆分。

水平分表后,多个表的表结构、索引相同,数据不同,每张表中会存储不同范围的数据。

水平分表和表分区十分类似,一般会选用水平分表方案。

由于数据会被存储到多个表中,所以进行增删改查数据前,需要定位到相应的表中再进行操作。且进行聚合操作时,需要从多个表中取出数据,再在后端进行聚合操作,或者依赖 Redis、ES 等第三方中间件来完成。

另外,可能出现多个表中 ID 相同,数据不同的情况,所以要合理设置 ID 规则来避免。比如可以设置交叉增长的 ID;可以利用特殊算法(雪花算法等)生成有序的分布式 ID;利用第三方中间件生成 ID 等。

分库

如果数据库整体负载都很高,那不管再怎么做分表也不能解决问题,此时就需要考虑分库方案。

垂直分库

可以按业务特性将大库拆分为多个业务功能单一的小库,每个小库只为对应的业务提供服务。

一次查询需要的数据如果被分到了多个库中,就需要用额外的手段来获取数据。比如通过广播表/网络表/全局表将对应的表数据直接完全同步一份到相应库中;或者在程序中组装数据再返回。

修改数据时,可能会一次性修改不同库中的数据,所以需要考虑数据一致性问题,即分布式事务问题,就需要分布式锁等额外的手段来保证数据一致性。

垂直分库无法解决部分库的性能瓶颈,所以可能还需要分表或者水平分库。

水平分库

当单个数据库节点的性能达到瓶颈后,除了提升该节点的配置外,还可以通过水平拆分的方案,将数据存储在多个节点上,从而分担压力。

水平分表中存在的问题在水平分库中也会遇到。

最后

本文介绍了 MySQL 的表分区和分库分表。

下一节将介绍 MySQL 的主从架构。

风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。