# MySQL分割槽型別

根據所使用的不同分割槽規則可以分成幾大分割槽型別。

**RANGE 分割槽：**

基於屬於一個給定連續區間的列值，把多行分配給分割槽。

**LIST 分割槽：**

類似於按RANGE分割槽，區別在於LIST分割槽是基於列值匹配一個離散值集合中的某個值來進行選擇。

**HASH分割槽：**

基於使用者定義的表示式的返回值來進行選擇的分割槽，該表示式使用將要插入到表中的這些行的列值進行計算。這個函式可以包含MySQL中有效的、產生非負整數值的任何表示式。

**KEY分割槽**

類似於按HASH分割槽，區別在於KEY分割槽只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函式。必須有一列或多列包含整數值。

**複合分割槽**

基於RANGE/LIST 型別的分割槽表中每個分割槽的再次分割。子分割槽可以是 HASH/KEY 等型別。

# mysql分割槽表常用操作示例

> 以部門員工表為例子

### 1. 建立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
	);

以year(birthdate)表示式（計算員工的出生日期）作為範圍分割槽依據。這裡最值得注意的是表示式必須有返回值

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

### 2. 建立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)
	);

### 3. 建立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;

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

### 5. 建立複合分割槽

#### 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 emp drop partition p1;

不可以刪除hash或者key分割槽。

一次性刪除多個分割槽

alter table emp drop partition p1,p2;

增加分割槽:

alter table emp add partition (partition p3 values less than (4000));
	
	alter table empl add partition (partition p3 values in (40));

**分解分割槽:**

Reorganizepartition關鍵字可以對錶的部分分割槽或全部分割槽進行修改，並且不會丟失資料。分解前後分割槽的整體範圍應該一致。

alter table te
	
	reorganize partition p1 into
	
	(
	
	partition p1 values less than (100),
	
	partition p3 values less than (1000)
	
	); ----不會丟失資料

**合併分割槽:**

Merge分割槽：把2個分割槽合併為一個。

alter table te
	
	reorganize partition p1,p3 into
	
	(partition p1 values less than (1000));
	
	----不會丟失資料

**重新定義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 removepartitioning;--不會丟失資料

**重建分割槽：**

這和先刪除儲存在分割槽中的所有記錄，然後重新插入它們，具有同樣的效果。它可用於整理分割槽碎片。

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分割槽表的侷限性**

1. 在5.1版本中分割槽表對唯一約束有明確的規定，每一個唯一約束必須包含在分割槽表的分割槽鍵（也包括主鍵約束）。

CREATE TABLE emptt (
	
	empno varchar(20) NOT NULL  ,
	
	empname varchar(20)，
	
	deptno int,
	
	birthdate date NOT NULL,
	
	salary int ,
	
	primary key (empno)
	
	)
	
	PARTITION BY range (salary)
	
	(
	
	PARTITION p1 VALUES less than (100),
	
	PARTITION p2 VALUES less than (200)
	
	);

這樣的語句會報錯。`MySQL Database Error: A PRIMARY KEY must include allcolumns in the table's partitioning function`；

CREATE TABLE emptt (
	
	empno varchar(20) NOT NULL  ,
	
	empname varchar(20) ,
	
	deptno int(11),
	
	birthdate date NOT NULL,
	
	salary int(11) ,
	
	primary key (empno，salary)
	
	)
	
	PARTITION BY range (salary)
	
	(
	
	PARTITION p1 VALUES less than (100),
	
	PARTITION p2 VALUES less than (200)
	
	);

在主鍵中加入salary列就正常。

2.  MySQL分割槽處理NULL值的方式
如果分割槽鍵所在列沒有notnull約束。
如果是range分割槽表，那麼null行將被儲存在範圍最小的分割槽。
如果是list分割槽表，那麼null行將被儲存到list為0的分割槽。
在按HASH和KEY分割槽的情況下，任何產生NULL值的表示式mysql都視同它的返回值為0。
為了避免這種情況的產生，建議分割槽鍵設定成NOT NULL。

3. 分割槽鍵必須是INT型別，或者透過表示式返回INT型別，可以為NULL。唯一的例外是當分
區型別為KEY分割槽的時候，可以使用其他型別的列作為分割槽鍵（ BLOB or TEXT 列除外）。

4. 對分割槽表的分割槽鍵建立索引，那麼這個索引也將被分割槽,分割槽鍵沒有全域性索引一說。

5. 只有RANG和LIST分割槽能進行子分割槽，HASH和KEY分割槽不能進行子分割槽。

6. 臨時表不能被分割槽。

# 獲取mysql分割槽表資訊的幾種方法

1. `show create table 表名`
可以檢視建立分割槽表的create語句

2. `show table status `
可以檢視錶是不是分割槽表

3. 檢視`information_schema.partitions`表

select 
		  partition_name part,  
		  partition_expression expr,  
		  partition_description descr,  
		  table_rows  
		from information_schema.partitions  where 
		  table_schema = schema()  
		  and table_name='test';  
	
	可以檢視錶具有哪幾個分割槽、分割槽的方法、分割槽中資料的記錄數等資訊

4. `explain partitions select`語句，透過此語句來顯示掃描哪些分割槽，及他們是如何使用的.

# 分割槽表效能比較

1. 建立兩張表: `part_tab`(分割槽表),`no_part_tab`(普通表)

CREATE TABLEpart_tab
		
		( c1 int defaultNULL, c2 varchar2(30) default NULL, c3 date not null)
		
		PARTITION BYRANGE(year(c3))
		
		(PARTITION p0VALUES LESS THAN (1995),
		
		PARTITION p1 VALUESLESS THAN (1996) ,
		
		PARTITION p2 VALUESLESS THAN (1997) ,
		
		PARTITION p3 VALUESLESS THAN (1998) ,
		
		 PARTITION p4 VALUES LESS THAN (1999) ,
		
		PARTITION p5 VALUESLESS THAN (2000) ,
		
		PARTITION p6 VALUESLESS THAN (2001) ,
		
		PARTITION p7 VALUESLESS THAN (2002) ,
		
		PARTITION p8 VALUESLESS THAN (2003) ,
		
		PARTITION p9 VALUESLESS THAN (2004) ,
		
		PARTITION p10VALUES LESS THAN (2010),
		
		PARTITION p11VALUES LESS THAN (MAXVALUE) );
		
		CREATE TABLE no_part_tab
		
		( c1 int defaultNULL, c2 varchar2(30) default NULL, c3 date not null)；

2. 用儲存過程插入800萬條資料

CREATE PROCEDUREload_part_tab()
		
		    begin
		
		    declare v int default 0;
		
		    while v < 8000000
		
		    do
		
		        insert into part_tab
		
		        values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));
		
		         set v = v + 1;
		
		    end while;
		
		end;
		
		insert into no_part_tab  select * frompart_tab;

3. 測試sql效能

查詢分割槽表：

selectcount(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
		
		+----------+
		| count(*) |
		+----------+
		|   795181 |
		+----------+
		1 row in set (2.62 sec)

查詢普通表：

selectcount(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
		
		+----------+
		| count(*) |
		+----------+
		|   795181 |
		+----------+
		1 row in set (7.33 sec)
	
	分割槽表的執行時間比普通表少70%。

4. 透過explain語句來分析執行情況

MySQL 分割槽表知識整理

相關文章