下面演示MySQL Range類型分區的操作，其他類型的分區還有HASH、Key、List等等。

分區優點：

1. 分區可以分在多個磁盤，存儲更大一點。

2. 根據查找條件，也就是where後面的條件，查找隻查找相應的分區不用全部查找了。

3. 進行大數據搜索時可以進行并行處理。

4. 跨多個磁盤來分散數據查詢，來獲得更大的查詢吞吐量.

CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)

partition BY RANGE( YEAR(purchased) ) (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (1995),

PARTITION p2 VALUES LESS THAN (2000),

PARTITION p3 VALUES LESS THAN (2005),

PARTITION p4 VALUES LESS THAN (2010),

PARTITION p5 VALUES LESS THAN (2015)

);

2. 插入演示數據

INSERT INTO tr VALUES

(1, 'desk organiser', '2003-10-15'),

(2, 'alarm clock', '1997-11-05'),

(3, 'chair', '2009-03-10'),

(4, 'bookcase', '1989-01-10'),

4. 删除分區

下面指定删除分區p2，執行如下命令。

ALTER TABLE tr DROP PARTITION p2;

需要注意的是：當删除一個分區時，分區中的數據也會被删除。

再次執行前面的SELECT 腳本，沒有任何數據返回。

SELECT * FROM tr

WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';

返回結果：0 row(s) returned

SELECT * FROM tr PARTITION (p2);

出現異常：Error Code: 1735. Unknown partition 'p2' in table 'tr'

5. 查看表tr的分區定義

SHOW CREATE TABLE tr;

partition p2 已經不存在了。

現在插入 purchased 列數據在1995-01-01 到 2004-12-31 之間的數據，新的行數據将存儲在 partition p3中。

INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');

SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '2004-12-31';

select * from tr partition(p3);

6. RANGE 重建分區

将原來的 p0,p1 分區合并起來，放到新的 p0 分區中。

在分區合并之前，先檢查一下 p0和p1 分區中的數據。

select * from tr partition(p0,p1);

輸出結果：3條記錄

下面進行分區合并操作。

ALTER TABLE tr REORGANIZE PARTITION p0, p1 INTO (PARTITION p0 VALUES LESS THAN (1995));

合并操作完成之後，分區 p1 已經不存在了，新的 p0 分區數據記錄如下，3條記錄。

select * from tr partition(p0);

查看更新後的分區定義，分區p0的範圍進行了重新定義。

SHOW CREATE TABLE tr;

打開MySQL的數據目錄，查看分區的表空間文件如下。

7. 子分區

子分區是分區表中每個分區的再次分割，子分區既可以使用HASH分區，也可以使用KEY分區。這也被稱為複合分區（composite partitioning）。

子分區的幾點注意事項：

• 如果一個分區中創建了子分區，其他分區也要有子分區。
• 如果創建了子分區，每個分區中的子分區數必有相同。
• 同一分區内的子分區，名字不相同，不同分區内的子分區名字可以相同。
• 由于分區是RANGE和LIST分區，所以删除分區也是同RANGE和LIST分區一樣，這裡隻能對每個分區進行删除，不能針對每個子分區進行删除操作，删除分區後子分區連同數據一并被删除。

子分區由兩種創建方法：

一種是不定義每個子分區的名字和路徑由分區決定；

二是定義每個子分區的分區名和各自的路徑；

（1）不定義每個子分區

表名稱：tb_sub

CREATE TABLE tb_sub (id INT, purchased DATE)

PARTITION BY RANGE( YEAR(purchased) )

SUBPARTITION BY HASH( TO_DAYS(purchased) )

SUBPARTITIONS 2 (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (2000),

PARTITION p2 VALUES LESS THAN MAXVALUE

);

分區表空間文件如下。

查看系統中表tb_sub 信息：

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION

FROM information_schema.PARTITIONS

WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tb_sub';

（2）定義每個子分區

定義子分區可以為每個子分區定義具體的分區名和分區路徑。

CREATE TABLE tb_sub_ev (id INT, purchased DATE)

PARTITION BY RANGE( YEAR(purchased) )

SUBPARTITION BY HASH( TO_DAYS(purchased) ) (

PARTITION p0 VALUES LESS THAN (1990) (

SUBPARTITION s0,

SUBPARTITION s1

),

PARTITION p1 VALUES LESS THAN (2000) (

SUBPARTITION s2,

SUBPARTITION s3

),

PARTITION p2 VALUES LESS THAN MAXVALUE (

SUBPARTITION s4,

SUBPARTITION s5

)

);

插入測試記錄：

INSERT INTO tb_sub_ev() VALUES(1,'1989-01-01'),(2,'1989-03-19'),(3,'1989-04-19');

從查詢結果中，可以看到3條記錄分表存儲在2個不同的子分區中。

查看如下查詢語言的執行計劃：

explain select * from tb_sub where purchased='1989-01-01';

explain select * from tb_sub where purchased='1989-03-19';

explain select * from tb_sub where purchased='1989-04-19';

8. 移除表的分區

注意：使用remove移除分區是僅僅移除分區的定義，并不會删除數據和drop PARTITION不一樣，後者會連同數據一起删除。

ALTER TABLE tb_sub REMOVE PARTITIONING;

移除分區之後，再次查詢表中的數據，确認表的數據依然存在。

查看表結構，确認表分區已經成功移除了。

show create table tb_sub;

對MySQL 有興趣的新手，可以進一步觀看如下培訓課程。

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部