MySQL表LIST分区，新增，删除，重组等

引用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;