深化淺析MySQL COLUMNS分區

深化淺析MySQL COLUMNS分區。本站提示廣大學習愛好者：（深化淺析MySQL COLUMNS分區）文章只能為提供參考，不一定能成為您想要的結果。以下是深化淺析MySQL COLUMNS分區正文

引見

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 COLUMNS和LIST COLUMNS分區其實是RANG和LIST分區的晉級，所以可以直接運用COLUMN分區。留意COLUMNS分區不支持timestamp字段類型。

以上所述是給大家引見的MySQL COLUMNS分區，希望對大家有所協助，假如大家有任何疑問請給我留言，會及時回復大家的。在此也十分感激大家對網站的支持！