mysql分区

分区就是将一张表中的数据分散存储在一台或者多台计算机上的多个文件中。

使用show plugins查看是否支持分区操作。查找partition数据。不过这个不一定准，可以直接创建分区表。

同一张表的分区使用的储存引擎必须一致。

create table tbl_partition_innodb(
id int not null,
name varchar(30)
)engine =INNODB
PARTITION BY HASH(id)
PARTITIONS 5 ;
1
2
3
4
5
6

分区的优势

• 储存更多的数据
• 优化查询
• 并行处理
• 快速删除数据
• 更大的数据吞吐量

分区类型

• range分区：根据范围分区
• list分区：根据值列表，将数据储存在不同的分区
• hash分区：hash算法分区
• key分区：hash算法分区（mysql自带）
• columns分区
• 子分区：对分区在进行分区

分区表中可以不存在主键或者唯一键，如果存在必须以其分区。

RANGE分区

创建分区

sql语句执行之后将t_member分成四个区，以group_id范围分区，1 -9为第一区，10-19是第二区，20-29是第三区，30-39是第四区。##

create table t_member(
id int not null,
name varchar(30),
group_id int not null
)
PARTITION by range(group_id)(
PARTITION part0 values less than (10),
PARTITION part1 values less than (20),
PARTITION part2 values less than (30),
PARTITION part3 values less than (40)
);
1
2
3
4
5
6
7
8
9
10
11

查看分区

从information_schema表中查看,table_row表示当前的分区的数据，因为分区是0-39所以group_id 最大只能为39。另外分区名不区分大小写。

SELECT * from information_schema.PARTITIONS
where table_schema = schema()
and table_name = 't_member'
1
2
3

如下所示查找group_id = 30的数据只需要查询分区3的即可。

添加分区

alter table t_members add paritition (
partition part4 values less than MAXVALUE
);

分区已经添加成功

删除分区

删除对应的分区，分区内的数据也会随着删除

alter table table_name drop partition part_name

重定义分区

alter table reorganize partition into

alter table t_member reorganize partition  part3 into (
partition  part31 values less than (35),
partition  part32 values less than (40)
);
1
2
3
4

如下所示，分区已经重定义

LIST分区

使用关键字partition by list语句实现，通过values in(list)定义分区，分区之间不能有相同的值，插入的数据此值也必须在其中列表内。

create table t_member_list(
id int not null,
t_name varchar(30) not null,
group_id int not null
) PARTITION BY list(group_id)(
partition part0 values in(1,3,5),
partition part1 values in(2,6),
partition part2 values in(4,7,9)
)
1
2
3
4
5
6
7
8
9

重定义分区

添加分区和删除分区和range分区一致，重定义分区需要先添加对应的列表值作为分区，然后在全部重新定义分区。

COLUMNS分区

为了解决RANGE分区和LIST分区只支持整数分区的问题，分为RANGE COLUMNS分区和LIST COLUMNS分区。支持整数类型、日期时间类型和字符串类型。

RANGE COLUMNS分区

create table t_member_list(
id int not null,
t_name varchar(30) not null,
group_id int not null,
group_code int not null,
) PARTITION BY range COLUMNS(group_id,group_code)(
partition part0 values less than(1,10),
partition part1 values less than(10,20),
partition part2 values less than(10,30)
)
1
2
3
4
5
6
7
8
9
10

LIST COLUMNS分区

create table t_member_list(
id int not null,
t_name varchar(30) not null,
group_id int not null,
group_code int not null,
) PARTITION BY LIST COLUMNS(group_id,group_code)(
partition part0 values in((1,1),(1,2)),
partition part1 values in((1,4),(2,2)),
partition part2 values in((3,4),(2,3))
)
1
2
3
4
5
6
7
8
9
10

HASH分区

分散数据库中的热点数据，保证数据尽可能平均分配。

create table t_member_hash(
id int not null,
t_name varchar(30) not null DEFAULT '',
group_id int not null
)
partition by hash (group_id)
partitions 4;

1
2
3
4
5
6
7
8

常规hash分区，对插入数据进行求模运算也就是求余。

添加分区

alter table t_member_hash add partition partitions n

合并分区

hash分区不支持合并分区。但是支持删除分区。

alter table t_member_hash coalesce partition n

key分区

如果没有指定分区列，mysql会自动选择非空并且唯一的列进行分区。

create table t_member_key(
id int not null,
t_name varchar(30) not null DEFAULT '',
group_id int not null
)
partition by key(group_id) partitions 8;
1
2
3
4
5
6

NULL值

range分区null会被当做最小值，list分区必须包含null，hash分区null值会被当做0处理。