MySQL分割區之LIST分割區詳解

2022-04-14 10:04:42

介紹

LIST分割區和RANGE分割區非常的相似，主要區別在於LIST是列舉值列表的集合，RANGE是連續的區間值的集合。二者在語法方面非常的相似。同樣建議LIST分割區列是非null列，否則插入null值如果列舉列表裡面不存在null值會插入失敗，這點和其它的分割區不一樣，RANGE分割區會將其作為最小分割區值儲存，HASHKEY分為會將其轉換成0儲存，主要LIST分割區只支援整形，非整形欄位需要通過函數轉換成整形；5.5版本之後可以不需要函數轉換使用LIST COLUMN分割區支援非整形欄位，在COLUMN分割區中有詳細的講解。

一、建立分割區

List各個分割區列舉的值只需要不相同即可，沒有固定的順序。

CREATE TABLE tblist (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (2,7,8),
    PARTITION c VALUES IN (3,9,10),
    PARTITION d VALUES IN (4,11,12)
);

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='tblist';

1.插入資料

insert into tblist(id,store_id) values(1,1),(7,7);

往a、b兩個分割區中各插入一條記錄

2.插入不在列表中的值

當往分割區中插入不在列舉列表中的值是會插入失敗，插入null值如果null值不在列舉列表中也同樣失敗

二、分割區管理

1.增加分割區

ALTER TABLE tblist ADD PARTITION (PARTITION e VALUES IN (20));

注意：不能增加包含現有任意值的分割區。

2.合併分割區

ALTER TABLE tblist REORGANIZE PARTITION  a,b INTO (PARTITION m VALUES IN (1,5,6,2,7,8));

將分割區a,b合併為分割區m

注意：同RANGE分割區一樣，只能合併相鄰的幾個分割區，不能跨分割區合併。例如不能合併a,c兩個分割區，只能通過合併a,b,c

3.拆分分割區

ALTER TABLE tblist REORGANIZE PARTITION  a,b,c INTO 
(PARTITION n VALUES IN (1,5,6,3,9,10),
PARTITION m VALUES IN (2,7,8));

ALTER TABLE tblist REORGANIZE PARTITION  n INTO 
    ( PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (3,9,10));

經過兩輪的拆分，列舉列表（3,9,10）排到了（2,7,8）的前面去了；其實是這樣的，一開始合併abc成nm兩個分割區由於n中的列舉值小於m所以n在m的前面，後面再拆分n分割區由於n分割區在m分割區的前面所以拆分出來的分割區也是排在m分割區的前面，由於a分割區的值小於b分割區的值所以a排在b的前面。

注意：1.在5.7.12版本中測試發現，合併和拆分分割區重新定義的列舉值可以不是原來的值，如果原來的列舉值包含了資料而新合併或拆分的分割區列舉值又不不包含原來的列舉值會造成資料丟失。雖然不知道為什麼mysql不會禁止該行為，但是人為的要求無論是合併還是拆分分割區列舉值保持不變，或者只能增加不能減少，這樣能保證資料不丟失。

2.合併和拆分後的分割區由於是相鄰的分割區進行合併和拆分會根據原本的分割區的值新的分割區也會在原本的分割區的順序位置。

4.刪除分割區

ALTER TABLE tblist DROP PARTITION e;

注意：刪除分割區同時會將分割區中的資料刪除，同時列舉的list值也被刪除，後面無法往表中插入該值的資料。

三、其它分割區

1.對時間欄位進行分割區

CREATE TABLE listdate (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST( YEAR(hired) ) 
(
    PARTITION a VALUES IN (1990),
    PARTITION b VALUES IN (1991),
    PARTITION c VALUES IN (1992),
    PARTITION d VALUES IN (1993)
);

ALTER TABLE listdate ADD INDEX ix_hired(hired);

INSERT INTO listdate() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00');

EXPLAIN SELECT * FROM listdate WHERE hired='1990-01-01 10:00:00';

LIST分割區也支援對非整形的時間型別欄位的轉換分割區。

四、移除表的分割區

ALTER TABLE tablename
REMOVE PARTITIONING ;

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

參考：

RANGE分割區：https://www.jb51.net/article/244269.htm

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

總結

重新定義LIST分割區時只能重新定義相鄰的分割區，不能跳過分割區定義，重新定義的分割區列表列舉必須包含原分割區的列表列舉，如果丟失某個包含記錄的列舉值那麼資料也將被刪除；重新定義分割區不能改變分割區的型別。

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