MySQL COLUMNS分割槽

pursuer.chen發表於2016-11-04
MySql

介紹

COLUMN分割槽是5.5開始引入的分割槽功能，只有RANGE COLUMN和LIST COLUMN這兩種分割槽；支援整形、日期、字串；RANGE和LIST的分割槽方式非常的相似。

 

 

COLUMNS和RANGE和LIST分割槽的區別

1.針對日期欄位的分割槽就不需要再使用函式進行轉換了，例如針對date欄位進行分割槽不需要再使用YEAR()表示式進行轉換。

2.COLUMN分割槽支援多個欄位作為分割槽鍵但是不支援表示式作為分割槽鍵。

COLUMNS支援的型別

整形支援：tinyint,smallint,mediumint,int,bigint;不支援decimal和float

時間型別支援：date,datetime

字元型別支援：char,varchar,binary,varbinary;不支援text,blob

一、RANGE COLUMNS分割槽

1.日期欄位分割槽

CREATE TABLE members (
    id INT,
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION a VALUES LESS THAN ('1960-01-01'),
    PARTITION b VALUES LESS THAN ('1970-01-01'),
    PARTITION c VALUES LESS THAN ('1980-01-01'),
    PARTITION d VALUES LESS THAN ('1990-01-01'),
    PARTITION e VALUES LESS THAN MAXVALUE
);

1.插入測試資料

insert into members(id,joined) values(1,'1950-01-01'),(1,'1960-01-01'),(1,'1980-01-01'),(1,'1990-01-01');

2.查詢分割槽資料分佈

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

 當前有5個分割槽只插入了4條記錄，其中C分割槽是沒有記錄的，結果和實際一樣。

3.分析執行計劃

explain select id,joined from tb_partition.members where joined=YEAR(now());
explain select id,joined from tb_partition.members where joined='1963-01-01';

第一條查詢使用了函式導致查詢沒有走具體的分割槽而是掃描的所有的分割槽，而第二條查詢執行語句查詢具體的分割槽。

2.多個欄位組合分割槽

CREATE TABLE rcx (
    a INT,
    b INT
    )
PARTITION BY RANGE COLUMNS(a,b) (
     PARTITION p0 VALUES LESS THAN (5,10),
     PARTITION p1 VALUES LESS THAN (10,20),
     PARTITION p2 VALUES LESS THAN (15,30),
     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

注意：多欄位的分割槽鍵比較是基於陣列的比較。它先用插入的資料的第一個欄位值和分割槽的第一個值進行比較，如果插入的第一個值小於分割槽的第一個值那麼就不需要比較第二個值就屬於該分割槽；如果第一個值等於分割槽的第一個值，開始比較第二個值同樣如果第二個值小於分割槽的第二個值那麼就屬於該分割槽。

例如：

insert into rcx(a,b)values(1,20),(10,15),(10,30);

第一組值：(1,20);1<5所以不需要再比較20了，該記錄屬於p0分割槽。

第二組值:(10,15)，10>5,10=10且15<20，所以該記錄屬於P1分割槽

第三組值:(10,30),10=10但是30>20，所以它不屬於p1,它滿足10<15所以它屬於p2

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

 注意：RANGE COLUMN的多列分割槽第一列的分割槽值一定是順序增長的，不能出現交叉值，第二列的值隨便，例如以下分割槽就會報錯

PARTITION BY RANGE COLUMNS(a,b) (
     PARTITION p0 VALUES LESS THAN (5,10),
     PARTITION p1 VALUES LESS THAN (10,20),
     PARTITION p2 VALUES LESS THAN (8,30),
     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

由於分割槽P2的第一列比P1的第一列要小，所以報錯，後面的分割槽第一列的值一定要比前面分割槽值要大，第二列沒規定。

二、LIST COLUMNS分割槽

1.非整形欄位分割槽

CREATE TABLE listvar (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST COLUMNS(hired) 
(
    PARTITION a VALUES IN ('1990-01-01 10:00:00','1991-01-01 10:00:00'),
    PARTITION b VALUES IN ('1992-01-01 10:00:00'),
    PARTITION c VALUES IN ('1993-01-01 10:00:00'),
    PARTITION d VALUES IN ('1994-01-01 10:00:00')
);

ALTER TABLE listvar ADD INDEX ix_hired(hired);

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

LIST COLUMNS分割槽對分整形欄位進行分割槽就無需使用函式對欄位處理成整形，所以對非整形欄位進行分割槽建議選擇COLUMNS分割槽。

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

 2.多欄位分割槽

CREATE TABLE listvardou (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST COLUMNS(id,hired) 
(
    PARTITION a VALUES IN ( (1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00') ),
    PARTITION b VALUES IN ( (2,'1992-01-01 10:00:00') ),
    PARTITION c VALUES IN ( (3,'1993-01-01 10:00:00') ),
    PARTITION d VALUES IN ( (4,'1994-01-01 10:00:00') )
);

ALTER TABLE listvardou ADD INDEX ix_hired(hired);

INSERT INTO listvardou() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(2,'1992-01-01 10:00:00'),(3,'1993-01-01 10:00:00');

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

EXPLAIN SELECT * FROM listvardou WHERE id=1 and hired='1990-01-01 10:00:00';

由於分割槽是組合欄位，filtered只有50%，對於組合分割槽索引也最好是建組合索引，其實如果能通過id欄位刷選出資料，單獨建id欄位的索引也是有效果的，但是組合索引的效果是最好的，其實和非分割槽鍵索引的概念差不多。

ALTER TABLE listvardou ADD INDEX ix_hired1(id,hired);

備註：文章中的示例摘自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

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 COLUMNS和LIST COLUMNS分割槽其實是RANG和LIST分割槽的升級，所以可以直接使用COLUMN分割槽。注意COLUMNS分割槽不支援timestamp欄位型別。

 

 

備註：

    作者：pursuer.chen

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

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

《歡迎交流討論》

相關文章