Mysql表分區(qū)的操作方法

這篇文章主要講解了“Mysql表分區(qū)的操作方法”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“Mysql表分區(qū)的操作方法”吧！

Mysql 表分區(qū)操作及相關(guān)操作 

將ID設(shè)為自增長ID 
alter table pw_trade_record_temp change ID ID int(4) auto_increment; 

分區(qū)字段必需為主鍵，修改主鍵 

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

直接在建表時(shí)創(chuàng)建表分區(qū) 

創(chuàng)建range分區(qū) 

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 

); 

以員工工資為依據(jù)做范圍分區(qū)。 

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)表達(dá)式（計(jì)算員工的出生日期）作為范圍分區(qū)依據(jù)。這里最值得注意的是表達(dá)式必須有返回值下載地址   。 

創(chuàng)建list分區(qū) 

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) 

); 

以部門作為分區(qū)依據(jù)，每個(gè)部門做一分區(qū)。 

創(chuàng)建hash分區(qū) 

HASH分區(qū)主要用來確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布。在RANGE和LIST分區(qū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪 個(gè)分區(qū)中；而在HASH分區(qū)中，MySQL 自動(dòng)完成這些工作，你所要做的只是基于將要被哈希的列值指定一個(gè)列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量下載地址 。 

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; 

創(chuàng)建key分區(qū) 

按照KEY進(jìn)行分區(qū)類似于按照HASH分區(qū)，除了HASH分區(qū)使用的用戶定義的表達(dá)式，而KEY分區(qū)的哈希函數(shù)是由MySQL 服務(wù)器提供，服務(wù)器使用其自己內(nèi)部的哈希函數(shù)，這些函數(shù)是基于與PASSWORD()一樣的運(yùn)算法則。“CREATE TABLE ...PARTITION BY KEY”的語法規(guī)則類似于創(chuàng)建一個(gè)通過HASH分區(qū)的表的規(guī)則。它們唯一的區(qū)別在于使用的關(guān)鍵字是KEY而不是HASH，并且KEY分區(qū)只采用一個(gè)或多個(gè) 列名的一個(gè)列表下載地址 。 

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; 


創(chuàng)建復(fù)合分區(qū) 


range - hash(范圍哈希)復(fù)合分區(qū) 

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復(fù)合分區(qū) 

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復(fù)合分區(qū) 

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 復(fù)合分區(qū) 

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) 

) 


修改現(xiàn)有未分區(qū)的表，給表做分區(qū) 

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); 


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

刪除分區(qū)(一次性可刪除多個(gè)分區(qū)) 
alter table pw_trade_record drop partition p20140101,p20160501,...; 

刪除表的所有分區(qū) 
Alter table emp removepartitioning;--不會(huì)丟失數(shù)據(jù) 

合并表分區(qū) 
示例：將表分區(qū)p20140101,p20160501合并到p20160501分區(qū)中 
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 
); 

分解表分區(qū) 
示例：將表分區(qū)pmax分解成p20160518和pmax兩個(gè)分區(qū) 
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 
); 

查看分區(qū)表狀態(tài) 
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分區(qū)表: 

Alter table emp partition by hash(salary)partitions 7;  ----不會(huì)丟失數(shù)據(jù) 

重新定義range分區(qū)表: 

Alter table emp partitionbyrange(salary) 

( 

partition p1 values less than (2000), 

partition p2 values less than (4000) 

); ----不會(huì)丟失數(shù)據(jù) 


重建分區(qū)： 

這和先刪除保存在分區(qū)中的所有記錄，然后重新插入它們，具有同樣的效果。它可用于整理分區(qū)碎片。 

ALTER TABLE emp rebuild partitionp1,p2; 


優(yōu)化分區(qū)： 

如果從分區(qū)中刪除了大量的行，或者對(duì)一個(gè)帶有可變長度的行（也就是說，有VARCHAR，BLOB，或TEXT類型的列）作了許多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”來收回沒有使用的空間，并整理分區(qū)數(shù)據(jù)文件的碎片。 

ALTER TABLE emp optimize partition p1,p2; 

分析分區(qū)： 

讀取并保存分區(qū)的鍵分布。 

ALTER TABLE emp analyze partition p1,p2; 

修補(bǔ)分區(qū)： 

修補(bǔ)被破壞的分區(qū)。 

ALTER TABLE emp repairpartition p1,p2; 

檢查分區(qū)： 

可以使用幾乎與對(duì)非分區(qū)表使用CHECK TABLE 相同的方式檢查分區(qū)。 

ALTER TABLE emp CHECK partition p1,p2; 

這個(gè)命令可以告訴你表emp的分區(qū)p1,p2中的數(shù)據(jù)或索引是否已經(jīng)被破壞。如果發(fā)生了這種情況，使用“ALTER TABLE ... REPAIR PARTITION”來修補(bǔ)該分區(qū)。 

感謝各位的閱讀，以上就是“Mysql表分區(qū)的操作方法”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)Mysql表分區(qū)的操作方法這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！