MySQLCOLUMNS分区
介绍
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
);mysql 字符串转数组
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 bers where joined=YEAR(now());
explain select id,joined from bers 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=1and 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分区：
LIST分区：
HASH分区：
KEY分区：
⼦分区：
指定各分区路径：
分区建索引：
分区介绍总结：
总结
RANGE COLUMNS和LIST COLUMNS分区其实是RANG和LIST分区的升级，所以可以直接使⽤COLUMN分区。注意COLUMNS分区不⽀持timestamp字段类型。
备注：
作者：
博客：
本站点所有随笔都是原创，欢迎⼤家转载；但转载时必须注明⽂章来源，且在⽂章开头明显处给明链接。
《欢迎交流讨论》

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。