|
引用MySQL表RANGE分区，新增，删除，重组，效率对比这篇文章的表作为示例。
LIST分区和RANGE分区很相似。
LIST分区管理
创建表时进行分区
CREATE TABLE `student_1` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(30) NOT NULL,
`SEX` char(2) NOT NULL,
`AGE` int(11) NOT NULL,
`CLASS` varchar(10) NOT NULL,
`GRADE` varchar(20) NOT NULL,
`HOBBY` varchar(100) DEFAULT NULL,
`CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`,`AGE`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY LIST ( AGE )
(
PARTITION p1 VALUES IN ( 18,19 ),
PARTITION p2 VALUES IN ( 20,21 ),
PARTITION p3 VALUES IN ( 22,23 ),
PARTITION p4 VALUES IN ( 24 )
);
为现有数据的表进行分区
ALTER TABLE student_1
PARTITION BY LIST ( AGE )
(
PARTITION p1 VALUES IN ( 18,19 ),
PARTITION p2 VALUES IN ( 20,21 ),
PARTITION p3 VALUES IN ( 22,23 ),
PARTITION p4 VALUES IN ( 24 )
);
新增分区
ALTER TABLE student_1 ADD PARTITION (PARTITION p5 VALUES IN ( 25 ));
重组分区
合并分区
ALTER TABLE student_1
REORGANIZE PARTITION p4,p5 INTO
(
PARTITION p4_p5 VALUES IN ( 24,25 )
);
拆分分区
ALTER TABLE student_1
REORGANIZE PARTITION p4_p5 INTO
(
PARTITION p4 VALUES IN ( 24 ),
PARTITION p5 VALUES IN ( 25 )
);
查看分区的数据量
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_NAME = 'student_1';
删除分区
当删除一个分区，该分区中所有的数据同时也被删除了。
ALTER TABLE student_1 DROP PARTITION p1;
取消分区
alter table student_1 remove partitioning;
|