Mysql表分区的操作方法

这篇文章主要讲解了“Mysql表分区的操作方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Mysql表分区的操作方法”吧！

Mysql 表分区操作及相关操作 

将ID设为自增长ID 
alter table pw_trade_record_temp change ID ID int(4) auto_increment; 

分区字段必需为主键，修改主键 

ALTER TABLE `pw_trade_record` 
DROP PRIMARY KEY, 
ADD PRIMARY KEY (`ID`, `TRADE_TIME`); 

直接在建表时创建表分区 

创建range分区 

create table emp 

(empno varchar(20) not null , 

empname varchar(20), 

deptno int, 

birthdate date, 

salary int 

) 

partition by range(salary) 

( 

partition p1 values less than (1000), 

partition p2 values less than (2000), 

partition p3 values less than maxvalue 

); 

以员工工资为依据做范围分区。 

create table emp 

(empno varchar(20) not null , 

empname varchar(20), 

deptno int, 

birthdate date not null, 

salary int 

) 

partition by range(year(birthdate)) 

( 

partition p1 values less than (1980), 

partition p2 values less than (1990), 

partition p3 values less than maxvalue 

); 

以year(birthdate)表达式（计算员工的出生日期）作为范围分区依据。这里最值得注意的是表达式必须有返回值下载地址   。 

创建list分区 

create table emp 

(empno  varchar(20) not null , 

empname varchar(20), 

deptno  int, 

birthdate date not null, 

salary int 

) 

partition by list(deptno) 

( 

partition p1 values in  (10), 

partition p2 values in  (20), 

partition p3 values  in  (30) 

); 

以部门作为分区依据，每个部门做一分区。 

创建hash分区 

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪 个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量下载地址 。 

create table emp 

(empno varchar(20) not null , 

empname varchar(20), 

deptno int, 

birthdate date not null, 

salary int 

) 

partition by hash(year(birthdate)) 

partitions 4; 

创建key分区 

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。“CREATE TABLE ...PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个 列名的一个列表下载地址 。 

create table emp 

(empno varchar(20) not null , 

empname varchar(20), 

deptno int, 

birthdate date not null, 

salary int 

) 

partition by key(birthdate) 

partitions 4; 


创建复合分区 


range - hash(范围哈希)复合分区 

create table emp 

(empno varchar(20) not null , 

empname varchar(20), 

deptno int, 

birthdate date not null, 

salary int 

) 

partition by range(salary) 

subpartition by hash(year(birthdate)) 

subpartitions 3 

( 

partition p1 values less than (2000), 

partition p2 values less than maxvalue 

); 

range- key复合分区 

create table emp 

(empno varchar(20) not null , 

empname varchar(20), 

deptno int, 

birthdate date not null, 

salary int 

) 

partition by range(salary) 

subpartition by key(birthdate) 

subpartitions 3 

( 

partition p1 values less than (2000), 

partition p2 values less than maxvalue 

); 

list - hash复合分区 

CREATE TABLE emp ( 

empno varchar(20) NOT NULL, 

empname varchar(20) , 

deptno int, 

birthdate date NOT NULL, 

salary int 

) 

PARTITION BY list (deptno) 

subpartition by hash(year(birthdate)) 

subpartitions 3 

( 

PARTITION p1 VALUES in  (10), 

PARTITION p2 VALUES in  (20) 

) 

; 

list - key 复合分区 

CREATE TABLE empk ( 

empno varchar(20) NOT NULL, 

empname varchar(20) , 

deptno int, 

birthdate date NOT NULL, 

salary int 

) 

PARTITION BY list (deptno) 

subpartition by key(birthdate) 

subpartitions 3 

( 

PARTITION p1 VALUES in  (10), 

PARTITION p2 VALUES in  (20) 

) 


修改现有未分区的表，给表做分区 

ALTER TABLE `pw_trade_record` 
PARTITION BY RANGE(UNIX_TIMESTAMP(TRADE_TIME)) 
( 
PARTITION p20160501 VALUES LESS THAN (UNIX_TIMESTAMP('2016-05-01')), 
PARTITION pmax VALUES LESS THAN MAXVALUE); 


增加分区 
alter table pw_trade_record add partition (partition p20160518 values in (UNIX_TIMESTAMP('2016-05-18'))); 

删除分区(一次性可删除多个分区) 
alter table pw_trade_record drop partition p20140101,p20160501,...; 

删除表的所有分区 
Alter table emp removepartitioning;--不会丢失数据 

合并表分区 
示例：将表分区p20140101,p20160501合并到p20160501分区中 
alter table pw_trade_record reorganize partition p20140101,p20160501 into 
(partition p20160501 values less than (UNIX_TIMESTAMP('2016-05-18')), 
PARTITION pmax VALUES LESS THAN MAXVALUE 
); 

分解表分区 
示例：将表分区pmax分解成p20160518和pmax两个分区 
alter table pw_trade_record reorganize partition pmax into 
(partition p20160518 values less than (UNIX_TIMESTAMP('2016-05-18')), 
PARTITION pmax VALUES LESS THAN MAXVALUE 
); 

查看分区表状态 
select table_schema,table_name,partition_name,partition_ordinal_position,partition_method,partition_expression,partition_description,table_rows,avg_row_length,data_length from INFORMATION_SCHEMA.PARTITIONS where table_name='pw_trade_record'; 


重新定义hash分区表: 

Alter table emp partition by hash(salary)partitions 7;  ----不会丢失数据 

重新定义range分区表: 

Alter table emp partitionbyrange(salary) 

( 

partition p1 values less than (2000), 

partition p2 values less than (4000) 

); ----不会丢失数据 


重建分区： 

这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。 

ALTER TABLE emp rebuild partitionp1,p2; 


优化分区： 

如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。 

ALTER TABLE emp optimize partition p1,p2; 

分析分区： 

读取并保存分区的键分布。 

ALTER TABLE emp analyze partition p1,p2; 

修补分区： 

修补被破坏的分区。 

ALTER TABLE emp repairpartition p1,p2; 

检查分区： 

可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。 

ALTER TABLE emp CHECK partition p1,p2; 

这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。 

感谢各位的阅读，以上就是“Mysql表分区的操作方法”的内容了，经过本文的学习后，相信大家对Mysql表分区的操作方法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！