MySQL分割區之RANGE分割區詳解

2022-04-14 10:04:34

介紹

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分割區：https://www.jb51.net/article/244269.htm

COLUMN分割區：https://www.jb51.net/article/96515.htm

LIST分割區：https://www.jb51.net/article/244256.htm

HASH分割區：https://www.jb51.net/article/244277.htm

KEY分割區：https://www.jb51.net/article/244282.htm

子分割區：https://www.jb51.net/article/244294.htm

指定各分割區路徑：https://www.jb51.net/article/244296.htm

分割區索引以及分割區介紹總結：https://www.jb51.net/article/244300.htm

總結

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

到此這篇關於MySQL分割區之RANGE分割區的文章就介紹到這了,更多相關MySQL RANGE分割區內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！