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 表分區