mysql 分区
分区:数据量很大时,可根据指定列将数据分散存储在使用多张表中,如果表有主键或者唯一索引,分区键应包含在主键中
*************************
range 分区:整数分区,可为自定义表达式,表达式的返回结果为整数
创建分区:
drop table if exists t1;
create table t1(
id int not null primary key,
num int not null
)
partition by range(id)(
partition p0 values less than (5),
partition p1 values less than (10),
partition p2 values less than (maxvalue)
)
注意:value值5、10要用括号括起来,否则会报错
删除分区:alter table table_name drop partition partition_name
新建分区:alter table table_name add partition(partition prtition_name values less than (value)),注意新建分区value不能小于原来分区的最大值
合并分区:alter table table_name reorganize partition p0,p1 into(partition new_partition values less than (value))
拆分分区:
alter table t1 reorganize partition new_partition into(
partition p0 values less than (5),
partition p1 values less than (10)
);
*************************
list 分区:整数分区,可为自定义表达式,表达式的返回结果为整数
创建分区
drop table if exists t1;
create table t1(
id int not null primary key,
num int not null
)
partition by list(id)(
partition p0 values in (1,2,3),
partition p1 values in (4,5,6),
partition p2 values in (7,8,9)
)
说明:如果插入的值不再分区列值中,会报错
删除分区:alter table table_name drop partition partition_name
新增分区:alter table table_name add partition(partition partition_name values in (value1,value2,...))
合并分区:alter table table_name reorganize partition p0,p1 into partition(partition p0 values in (value1,value2,...)),新分区的值不能与未合并的分区值重复
拆分分区:拆分后新的分区值可变少,但不能和现有分区值重复
alter table t1 reorganize partition p0 into(
partition p0 values in (1,2,3),
partition p1 values in (4,5,6)
);
*************************
columns 分区:支持整数、字符串、日期分区,不支持blob、text类型
columns 分区可分为range columns、list columns分区
创建分区
drop table if exists t1;
create table t1(
id int not null primary key,
num int not null
)
partition by range columns(id)(
partition p0 values less than (5),
partition p1 values less than (10),
partition p3 values less than (maxvalue)
);
删除分区:alter table table_name drop partition partition_name
增加分区:alter table table_name add partition(partition partition_name values less than (value))
合并分区:alter table table_name reorganize partition p0,p1 into(partition new_partition_name values less than (value))
拆分分区:
alter table t1 reorganize partition p0 into(
partition p0 values less than (5),
partition p1 values less than (10)
);
*************************
hash 分区:对整数hash,可自定义表达式返回整数
创建分区:
drop table if exists t1;
create table t1(
id int not null primary key,
num int not null
)
partition by hash(id) partitions 4;
增加分区:alter table table_name add partition partitions 6,在原有分区基础上新增6个分区
删减分区:alter table table_name coalsece partition 6,在原有分区基础上减去6个分区
*************************
key 分区:与hash分区类似,不支持自定义表达式,可对整数、字符串、日期格式的列进行分区
创建分区:
drop table if exists t1;
create table t1(
id int not null primary key,
num int not null
)
partition by key(id) partitions 4;
增加分区:alter table table_name add pertition partitions 6,在原有分区基础上增加6个分区
删减分区:alter table table_name coalsece partition 6,在原有分区基础上减去6个分区
*************************
子分区:对分区再进行分区
创建分区:
drop table if exists t1;
create table t1(
id int not null,
num int not null
)
partition by range (id)
subpartition by hash(num)(
partition p0 values less than (5),
partition p1 values less than (10),
partition p2 values less than (maxvalue)
);
说明:如果有主键,子分区的分区键也要包含在主键当中
增加分区:alter table table_name add aprtition(partition partition_name values less than (value))
删除分区:alter table table_name drop partition partition_name
合并分区:alter table table_name reorganize partition p0,p1 into (partition p0 values less than (value))
拆分分区:
alter table t1 reorganize partition p0 into(
partition p0 values less than (5),
partition p1 values less than (10)
);
说明:分区的增删针对的是第一层分区,不对子分区进行操作