深入分析mysql數據庫表分區

表分區我在很久以前有聽說過，意思就是講表分區是將一大表，根據條件分割成若干個小表。mysql5.1開始支持數據表分區了了，下面我來給各位深入分析mysql表分區的一些相關知識點，希望文章對各位有幫助。

什麼是表分區

通俗地講表分區是將一大表，根據條件分割成若干個小表。mysql5.1開始支持數據表分區了。
如：某用戶表的記錄超過了600萬條，那麼就可以根據入庫日期將表分區，也可以根據所在地將表分區。當然也可根據其他的條件分區。

為什麼要對表進行分區

為了改善大型表以及具有各種訪問模式的表的可伸縮性，可管理性和提高數據庫效率。
分區的一些優點包括：

1）、與單個磁盤或文件系統分區相比，可以存儲更多的數據。
2）、對於那些已經失去保存意義的數據，通常可以通過刪除與那些數據有關的分區，很容易地刪除那些數據。相反地，在某些情況下，添加新數據的過程又可以通過為那些新數據專門增加一個新的分區，來很方便地實現。通常和分區有關的其他優點包括下面列出的這些。MySQL分區中的這些功能目前還沒有實現，但是在我們的優先級列表中，具有高的優先級；我們希望在5.1的生產版本中，能包括這些功能。
3）、一些查詢可以得到極大的優化，這主要是借助於滿足一個給定WHERE語句的數據可以只保存在一個或多個分區內，這樣在查找時就不用查找其他剩余的分區。因為分區可以在創建了分區表後進行修改，所以在第一次配置分區方案時還不曾這麼做時，可以重新組織數據，來提高那些常用查詢的效率。
4）、涉及到例如SUM()和COUNT()這樣聚合函數的查詢，可以很容易地進行並行處理。這種查詢的一個簡單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“並行”，這意味著該查詢可以在每個分區上同時進行，最終結果只需通過總計所有分區得到的結果。
5）、通過跨多個磁盤來分散數據查詢，來獲得更大的查詢吞吐量。

mysql分區類型

根據所使用的不同分區規則可以分成幾大分區類型。

RANGE 分區：

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

LIST 分區：

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

HASH分區：

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

KEY
分區：類似於按HASH分區，區別在於KEY分區只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含整數值。

復合分區：

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

例子RANGE 分區：

創建表分區range方式，也可以使用hash，list，key

 代碼如下 復制代碼

create table foo (

id int not null auto_increment,

created DATETIME,

primary key (id, created)

) engine = innodb partition by range (TO_DAYS(created))(

PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2009-01-01')),

PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2010-01-01'))

)

增加表分區

ALTER TABLE foo ADD PARTITION(

PARTITION foo_3 VALUES LESS THAN (TO_DAYS('2011-01-01'))

)

插入一條數據

insert into `foo` (`id`, `created`) values (1, '2008-01-02'),(2, '2009-01-02');

分析確認分區是否生效

explain partitions select  * from foo where created = '2008-01-02';

創建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;

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)

)

;

6)       分區表的管理操作

刪除分區:

 代碼如下 復制代碼

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”來修補該分區。