mysql分区--类型

最新推荐文章于 2024-09-22 15:18:13 发布

daicooper

最新推荐文章于 2024-09-22 15:18:13 发布

阅读量2.2k

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/daicooper/article/details/80058630

版权

mysql 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

分区类型

mysql主要的分区类型

RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区。
LIST分区：类似于RANGE分区，区别在于 LIST分区是基于枚举出的值列表分区。RANGE是基于给定的连续区间范围分区。
HASH分区：基于给定的分区个数，把数据分配到不同的分区。
KEY分区：类似于HASH分区。
无论是哪种 MySQL分区类型，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其它字段分区。
例如： staff表的主键为id字段，在尝试通过department 字段分区的时候，MySQL会提示返回失败

去掉主键约束后，创建表就会成功：

分区的命名

分区的名字基本上遵循MySQL标识符的原则。分区的名字是不区分大小写的。
这里写图片描述

Range 分区

按照 RANGE 分区的表是利用取值范围将数据分成分区，区间要连续并且不能互相重叠，使用 VALUES LESS THAN 操作符进行分区定义。
例如，员工表中按部门进行RANGE分区：
这里写图片描述
按照这种分区方案，在1~9部门工作的员工相对应的所有行被保存在分区p0中，10~19的雇员保存在p1中，依次类推。每个分区都是按顺序进行定义的，从最低到最高，这是PARTITION BY RANGE语法的要求。如果这个时候增加部门ID大于等于30的行，就会出现错误。因为没有规则包含了部门ID大于等于30的行，服务器不知道应该把记录保存在哪里。

这里写图片描述
可以在设置分区的时候使用 VALUES LESS THAN MAXVALUE 子句，该子句提供给所有大于明确指定的最高值得值，MAXVALUE 表示最大的可能的整数值。

MySQL 支持在 VALUES LESS THAN 子句中使用表达式，比如，以日期作为RANGE分区的分区列：
这里写图片描述
注意：在RANGE分区中，分区键如果是 NULL 值会被当做最小值来处理。
RANGE分区功能适用情况

当需要删除过期的数据时，只需要简单的 alter table staff drop partition p0
来删除p0分区中的数据，对于有上百万条记录的表来说，删除分区要比运行一个delete语句有效的多。
经常运行包含分区键的查询，MySQL
可以很快地确定只有某一个或者某些分区需要扫描，因为其他分区不可能包含有符合该where子句的任何记录。例如，检索部门ID大于等于25的记录，mysql只需要扫描p2分区即可。

List 分区

LIST分区是建立离散的值列表告诉数据库特定的值属于哪个分区，LIST分区在很多方面类似于RANGE分区，区别在LIST分区是从属于一个枚举列表的值的集合，RANGE分区是从属于一个连续区间值的集合。
LIST分区通过使用 PARTITION BY LIST(expr)子句来实现，expr是某列值或是一个基于某列值返回一个整数值的表达式，然后通过 VALUES IN(value_list)的方式来定义分区，其中 value_list是一个逗号分隔的整数列表。与RANGE分区不同，LIST分区不必声明任何特定的顺序，例如：
这里写图片描述
注意：视图插入的列值必须要包含在分区值列表中，否则插入会报错。

Columns 分区

Columns分区解决 RANGE分区和 LIST分区只支持整数分区问题,Columns分区可以细分为 LIST Columns和 RANGE Columns.
这两种分区都支持整数、日期时间、字符串三大类型

整型：tinyint, smallint, mediumint, int和 bigint;其它数值类型都不支持。
日期时间类型：date , datetime.
字符类型：char, varchar,binary,varbinary;不支持text,blob类型作为分区键。

Columns多列分区
创建一个根据字段a,b组合的RANGE COLUMNS分区：

RANGE Cloumns分区键的比较是基于元组的比较，也就是基于字段组的比较，先根据a字段排序再根据b字段排序，更具排序结果来区分存放数据。和RANGE单字段分区排序的规则实际上是一致的。

Hash 分区

HASH 分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能的平均分布。对一个表执行HASH分区时，MySQL会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪个分区。
MySQL支持两种HASH分区：

常规HASH分区:使用取模算法。
线性HASH（LINEAR HASH）分区：使用线性的2的幂的运算。

这里写图片描述

Key分区

key分区和Hash分区非常类似，但是Hash分区允许使用用户自定义的表达式，而Key分区不允许使用用户自定义的表达式，需要使用Mysql服务器提供的Hash函数；同时Hash分区只支持整数分区，而Key分区支持使用除BLOB or Text类型外的其他类型的列作为分区键。
使用 PARTITION BY KEY(expr)子句来创建一个Key分区表，expr是零个或者多个字段名的列表。
这里写图片描述
与Hash分区不同，创建Key分区的时候，可以不指定分区键。默认会首先选择使用主键作为分区键。

如果没有主键，会选择非空唯一键作为分区键：
注意：作为分区键的唯一键必须是非空的，如果不是非空的，依然会报错。
这里写图片描述

Mysql分区处理NULL值的方式

MySQL 不禁止在分区键值上使用NULL，分区键可能是一个字段或者是一个用户定义的表达式。一般情况下，MySQL的分区把NULL当作零值，或是一个最小的值进行处理。

RANGE分区中，NULL值会被当做最小值来处理。
LIST分区中，NULL值必须出现在枚举列表中，否则不被接受。
HASH/KEY 分区中，NULL值会被当作零值来处理。

分区的管理

RANGE 和LIST 分区管理

分区删除：
ALTER TABLE table_name DROP PARTITION pn

分区添加：
ALTER TABLE table_name ADD PARTITION （partition pn values less than( xxxx)）;
ALTER TABLE table_name ADD PARTITION （partition pn values in(‘xxxx’,’xxxx’)）;

HASH和KEY分区管理

分区删除：
ALTER TABLE table_name COALESCE PARTITION n
比如要减少 emp 表的HASH分区的数量，从4个分区变为2个分区:
alter table emp coalesce partition 2;
注意：COALESCE不能用来增加分区的数量，否则会报错。

增加分区：
ALTER TABLE table_name ADD PARTITION PARTITIONS n;
比如将刚才合并的 emp 分区增加到8个分区：
alter table emp add partition partitions 8;
通过 ALTER TABLE ADD PARTITION PARTITIONS n 语句新增 HASH 分区或者 KEY 分区时，其实是对原表新增 n个分区，而不是增加到 n个分区。