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()一樣的運算法則?！癈REATE 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表分區的操作方法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！