MySQL RANGE分割槽

pursuer.chen發表於2016-07-12
MySql

介紹

RANGE分割槽基於一個給定的連續區間範圍，早期版本RANGE主要是基於整數的分割槽。在5.7版本中DATE、DATETIME列也可以使用RANGE分割槽，同時在5.5以上的版本提供了基於非整形的RANGE COLUMN分割槽。RANGE分割槽必須的連續的且不能重疊。使用

“VALUES LESS THAN ()” 來定義分割槽區間,非整形的範圍值需要使用單引號，並且可以使用MAXVALUE作為分割槽的最高值。

 

 

一、RANGE分割槽

1.建立分割槽

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

alter table employees add index ix_store_id(store_id) ;
alter table employees add index ix_job_code(job_code) ;

p0:指分割槽p0,這個分割槽名的取值可以隨便取值只要同一個表裡面的每個分割槽名不重複即可，也不需要非得從0開始，你也可以使用a、b、c、d。

THEN():分割槽的範圍值，這個值只能的連續不重疊的從小到大的值。

2.效能分析

插入測試資料

insert into employees(id,job_code,store_id) values(1,1001,1),(2,1002,2),(3,1003,3),(4,1004,4);

 從執行計劃可以看到兩個查詢都用到了分割槽的效果；如果細心估計會發現第二個查詢沒有走索引，並不是使用小於就不會走索引而且執行計劃分析評估任務不走索引的效果會更好，事實卻是如果當前查詢整個分割槽的資料時使用索引的話還需要去查詢其它的欄位還不如直接掃描整個分割槽來的快。

3.增加分割槽 

 由於當前分割槽值的範圍是小於21，當向分割槽表中插入一個超過分割槽範圍的值時會報錯。這個時候可以增加一個分割槽，當你不確定需要給一個多大的上限值時可以使用MAXVALUE

alter table employees add PARTITION  (PARTITION p4 VALUES LESS THAN MAXVALUE);

注意：增加分割槽只能在最大端增加

4.刪除分割槽

alter table employees drop  PARTITION p4;

注意：通過這種刪除分割槽的方式會將分割槽中的資料也刪除，慎用！！！！。但是通過刪除分割槽的方式刪除資料會比delete快很多，因為它相當於刪除一個資料庫一樣因為每個分割槽都是一個獨立的資料檔案。用來刪除歷史分割槽資料是非常好的辦法。

5.拆分合並分割槽

拆分合並分割槽統稱為重新定義分割槽，拆分分為不會造成資料的丟失，只將會將資料從一個分割槽移動到另一個分割槽。

例1：將P0拆分成s1,s2兩個分割槽

ALTER TABLE employees REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (3),
    PARTITION s1 VALUES LESS THAN (6)
);

注意：原來分割槽p0的範圍是[負無窮-6)，所以新拆分的分割槽也必須是這範圍，所以新的分割槽範圍值最大不能超過6。

 

分割槽由原來的p0[-6)變成了so[-3),s1[3-6)，整個分割槽的範圍還是不變。

例2：將s1,p1,p2合併為a,b兩個分割槽

ALTER TABLE employees REORGANIZE PARTITION s1,p1,p2 INTO (
    PARTITION a VALUES LESS THAN (5),
    PARTITION b VALUES LESS THAN (16)
);

原本的s1,p1,p2分割槽範圍是：[3-16)所以新的分割槽也必須和原本的分割槽相同，所以新的分割槽的值不能低於3不能高於16即可。

 

分割槽由原來的s1[3-6),p1[6-11),p2[11-16)變成了現在的a[3-5),b[5-16),總的範圍沒有發生變化

注意：無論是拆分還是合併分割槽都不能改變分割槽原本的覆蓋範圍，並且合併分割槽只能合併連續的分割槽不能跳過分割槽合併；並且不能改變分割槽的型別，例如不能把range分割槽改成key分割槽等。

二、日期欄位分割槽方法

注意：RANG分割槽針對日期欄位進行分割槽可以使用時間型別的函式進行轉換成整形，但是如果你的查詢語句需要利用分割槽那麼查詢語句也需要使用相同的時間函式進行查詢。

1.使用YEAR()函式進行分割槽

CREATE TABLE employees1 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
); 

插入測試資料

insert into employees1(id,separated,job_code,store_id) values(1,'1990-03-04',1001,1),(2,'1995-03-04',1002,2),(3,'1998-03-04',1003,3),(4,'2016-03-04',1004,4);

 

對於日期欄位分割槽，查詢條件使用> 、< 、betnwen、=都會利用分割槽查詢,如果條件使用函式轉換則不會走分割槽，比如使用YEAR()。

2.TIMESTAMP型別的列的分割槽方法

針對TIMESTAMP的日期型別的欄位需要使用專門的UNIX_TIMESTAMP（）函式進行轉換

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

三、null值處理 

當往分割槽列中插入null值RANG 分割槽會將其當作最小值來處理即插入最小的分割槽中

CREATE TABLE test (
    id INT NOT NULL,
    store_id INT 
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);
insert into test(id,store_id) values(1,null);
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='test';

備註：文章中的示例摘自mysql官方參考手冊

 

四、移除表的分割槽

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意：使用remove移除分割槽是僅僅移除分割槽的定義，並不會刪除資料和drop PARTITION不一樣，後者會連同資料一起刪除

 

 

分割槽系列文章： 

RANGE分割槽：http://www.cnblogs.com/chenmh/p/5627912.html

LIST分割槽：http://www.cnblogs.com/chenmh/p/5643174.html

COLUMN分割槽：http://www.cnblogs.com/chenmh/p/5630834.html

HASH分割槽：http://www.cnblogs.com/chenmh/p/5644496.html

KEY分割槽：http://www.cnblogs.com/chenmh/p/5647210.html

子分割槽：http://www.cnblogs.com/chenmh/p/5649447.html

指定各分割槽路徑：http://www.cnblogs.com/chenmh/p/5644713.html

分割槽建索引：http://www.cnblogs.com/chenmh/p/5761995.html

分割槽介紹總結：http://www.cnblogs.com/chenmh/p/5623474.html

總結

 有兩點非常重要需要注意，第一刪除分割槽時要慎重因為會連同分割槽裡的資料一併刪除，拆分合並分割槽新的分割槽一定要和原來的分割槽的範圍一致。RANGE COLUMN分割槽單獨用章節進行講解，。

 

 

 

備註：

    作者：pursuer.chen

    部落格：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結。

《歡迎交流討論》

相關文章