MySQL原理(九)：表分区和分库分表-Toy模板网

这篇具有很好参考价值的文章主要介绍了MySQL原理(九)：表分区和分库分表。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

前言

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

表分区

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

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

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

好处：

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

限制：

单张表最多只能创建 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。

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

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