MySQL-表分区

Author Avatar
丁起男 03月 23,2022
  • 在其它设备中阅读本文章

MySQL-表分区

mysql数据库支持的分区类型为水平分区,它并不支持垂直分区

分区的限制

  • 一个表最多只能有1024个分区
  • 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来
  • 分区表无法使用外键约束
  • null会使分区过滤无效
  • 所有分区必须使用相同的存储引擎

查看是否支持表分区

在MySQL5.6.1之前,使用show variables like '%have_partitioning%'查看,如果have_partitioning的值为YES,则表示支持分区

在MySQL5.6.1之后,使用SHOW PLUGINS查看,如果partition的STATUS列的值为ACTIVE,则表示支持分区

range分区

range分区比较简单,就是根据某一个字段的值进行分区。不过这个字段有一个要求,就是必须是主键或者是联合索引中的某个字段

语法

建表语句
partition by range(字段或返回整数的表达式)(
	partition 分区名 values less than(范围值),
    ...
);

list分区

list分区和range分区类似,区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择,而非连续的。比如用户表根据性别进行分区

语法

建表语句
partition by list(字段或返回整数的表达式)(
	partition 分区名 values in (分区值),
    ...
);

hash分区

hash分区的目的是将数据均匀的分布到预先定义的各个分区中,保证各个分区的数据量大致都是一样的

在range和list分区中,必须明确指定一个给定的列值或列值应该保存在哪个分区中;而hash分区中,MySQL自动完成这些工作,用户所要做的只是基于要进行哈希分区的列指定一个表达式,和分区的数量

语法

建表语句
partition by hash(字段或返回整数的表达式) partitions 分区数;

如果没有指定分区数,那么默认分区数量为1

hash分区不能删除

key分区

key分区和hash分区类似,但是key分区支持除了text和blob之外的所有数据类型的分区,而hash分区只支持数字分区

key分区不允许使用用户自定义的表达式进行分区,key分区使用系统提供的hash函数进行分区

当表中存在主键或者唯一索引时,如果创建key分区没有指定字段,系统默认会首选主键列作为分区字段,如果不存在主键列会选择非空唯一索引列作为分区字段

语法

建表语句
partition by key(分区字段) partitions 分区数;

columns分区

column分区是MySQL5.5引入的功能,有range column和list column两种分区方式;支持整形、日期、字符串等(不支持text、blob、decimal和float);这种分区方式和range、list的方式非常相似

和range、list的区别

  • 针对日期字段的分区不需要再使用函数进行转换
  • column分区支持多个字段作为分区键,但是不支持表达式

语法

建表语句
partition by range|list columns(分区键)(
	partition 分区名 values in (分区值),
    ...
)

常见操作

添加分区

#range分区
alter table 表名 add partition (partition 分区名 values less than(分区值));
#list分区
alter table 表名 add patition (partition 分区名 values in (分区值));

删除分区

alter table 表名 drop partition 分区名;

注意:这种方式会删除这个分区上的数据

删除所有分区

alter table 表名 remove partitioning;

这种方式并不会丢失数据

重新定义分区

#range分区
alter table 表名 partition by range(分区键)(
	partition 分区名 values less than (分区值),
    ...
)
#hash分区
alter table 表名 partition by hash(分区键) partitions 分区数;

合并分区

alter table 表名 reorganize partition 分区1,分区2 into(partition 分区名 values less than(分区值));