MySQL26分区表

什么是分区表

分区表就是按照某种规则将同一张表的数据分段划分到多个位置存储。对数据的分区存储提高了数 据库的性能&＃xff0c;被分区存储的数据在物理上是多个文件&＃xff0c;但在逻辑上仍然是一个表&＃xff0c;对表的任何操作 都跟没分区之前一样。在执行增、删、改、查等操作时&＃xff0c;数据库会自动找到对应的分区&＃xff0c;然后执行 操作。

• MySQL从5.1.3开始支持分区(Partition)。
• 在MySQL 8.0中&＃xff0c;只有InnoDB和NDB两个存储引擎支持分区。

分区表的好处

• 存储更多&＃xff1a;与单个磁盘或文件系统分区相比&＃xff0c;可以存储更多的数据。
• 提高查询吞吐&＃xff1a;通过跨多个磁盘来分散数据查询&＃xff0c;来获得更大的查询吞吐量。
• 便于管理&＃xff1a;很容易根据分区删除失去保存意义的历史数据。
• 并行处理&＃xff1a;涉及到sum()、count()等聚合函数的 查询&＃xff0c;可以很容易进行并行处理。
• 提升查询效率&＃xff1a;一些查询可以极大地优化&＃xff0c;查询仅从某个或某几个分区中获取数据。

分区表的四种类型

MySQL支持的分区类型包括Range、List、Hash和Key&＃xff0c;其中Range最常用。

• Range分区&＃xff1a;允许将数据划分不同范围。例如可以将 一个表通过年份划分成若干个分区。
• List分区&＃xff1a;允许系统通过预定义的列表的值来对数 据进行分割。
• Hash分区&＃xff1a;允许通过对表的一个或多个列的Hash Key进行计算&＃xff0c;最后通过这个Hash码不同数值对应的数据区域进行分区。
• Key分区&＃xff1a;对Hash模式的一种延伸&＃xff0c;这里的Hash Key是MySQL系统产生的。

Range分区

Range分区是基于属于一个给定连续区间的列值&＃xff0c;把多行分配给分区。

#Range分区示例
create table user_range(
id int not null auto_increment,
name varchar(30) ,
age int,
birthday date,
province int,
primary key(id, age)
)
# 主键必须包含分区字段。partition by RANGE(age) (
partition p1 VALUES LESS THAN (20) DATA DIRECTORY &＃61; &＃39;/var/local/data/p1&＃39;,
partition p2 VALUES LESS THAN (40) DATA DIRECTORY &＃61; &＃39;/var/local/data/p2&＃39;,
partition p3 VALUES LESS THAN (60) DATA DIRECTORY &＃61; &＃39;/var/local/data/p3&＃39;,
partition p4 VALUES LESS THAN MAXVALUE DATA DIRECTORY &＃61; &＃39;/var/local/data/p4&＃39;
);


List分区

List分区是基于列值匹配一个离散值集合中的某个值来进行选择。

#List分区示例
create table user_list(
id int not null auto_increment, name varchar(30) ,
age int,
birthday date,
province int,
primary key(id, province)
)
partition by List(province) (
partition p1 VALUES IN (1,3,5,7,9,11,13,15,17,19,21), partition p2 VALUES IN (2,4,6,8,10,12,14,16,18,20,22), partition p3 VALUES IN (23,24,25,26,27,28,29,30,31,32,33,34)
);


Hash分区

Hash分区是基于用户定义的表达式的返回值来进行选择的分区。

#Hash分区示例
create table user_hash(
id int not null auto_increment,
name varchar(30) ,
age int,
birthday date,
province int,
primary key(id, birthday)
)
partition by HASH(YEAR(birthday)) partitions 5;


Key分区

Key分区类似于Hash分区&＃xff0c;但这里的Hash Key是由MySQL系统产生的。

#Key分区示例
create table user_key(
id int not null auto_increment, name varchar(30) ,
age int,
birthday date,
province int,
primary key(id, age)
)
partition by KEY (age) partitions 5;


分区的其他操作

#新增分区
alter table &＃96;user&＃96; add partition(partition p5 VALUES LESS THAN MAXVALUE);
#对已存在的表进行分区
alter table &＃96;user&＃96; partition by RANGE(age) (
partition p1 VALUES LESS THAN (20) DATA DIRECTORY &＃61; &＃39;c:/data/p1&＃39;, partition p2 VALUES LESS THAN (40) DATA DIRECTORY &＃61; &＃39;c:/data/p2&＃39;, partition p3 VALUES LESS THAN (60) DATA DIRECTORY &＃61; &＃39;c:/data/p3&＃39;, partition p4 VALUES LESS THAN MAXVALUE DATA DIRECTORY &＃61; &＃39;c:/data/p4&＃39;
);
#删除分区(分区下的数据也会被删除)
alter table &＃96;user&＃96; drop partition p5;
#移除分区(数据不会被删除)
ALTER TABLE &＃96;user&＃96; REMOVE PARTITIONING ;