数据切分Mysql分区表的管理与维护_MySQL

RANGE与LIST分区管理：

案例：有一个聊天记录表，用户几千左右，已经对表按照用户进行一定粒度的水平分割，现仍然有部分表存储的记录比较多，于是按照下列方式有对表进行了分区，分区的好处是，可以动态改变分区，删除分区后，数据也一同被删除，如聊天记录只保存两年，那么你就可以按照时间进行分区，定期删除两年前的分区，动态创建新的的分区就能做到很好的数据维护。

分区表创建的语句如下：

DROP TABLE IF EXISTS `msgss`;
CREATE TABLE `msgss` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT &＃39;表主键&＃39;,
  `sender` int(10) unsigned NOT NULL COMMENT &＃39;发送者ID&＃39;,
  `reciver` int(10) unsigned NOT NULL COMMENT &＃39;接收者ID&＃39;,
  `msg_type` tinyint(3) unsigned NOT NULL COMMENT &＃39;消息类型&＃39;,
  `msg` varchar(225) NOT NULL COMMENT &＃39;消息内容&＃39;,
  `atime` int(10) unsigned NOT NULL COMMENT &＃39;发送时间&＃39;,
  `sub_id` tinyint(3) unsigned NOT NULL COMMENT &＃39;部门ID&＃39;,
  PRIMARY KEY (`id`,`atime`,`sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*********分区信息**************/
PARTITION BY RANGE (atime) SUBPARTITION BY HASH (sub_id) 
(
		PARTITION t0 VALUES LESS THAN(1451577600)
		(
			SUBPARTITION s0,
			SUBPARTITION s1,
			SUBPARTITION s2,
			SUBPARTITION s3,
			SUBPARTITION s4,
			SUBPARTITION s5
		),
		PARTITION t1 VALUES LESS THAN(1483200000)
		(
			SUBPARTITION s6,
			SUBPARTITION s7,
			SUBPARTITION s8,
			SUBPARTITION s9,
			SUBPARTITION s10,
			SUBPARTITION s11
		),
		PARTITION t2 VALUES LESS THAN MAXVALUE
		(
			SUBPARTITION s12,
			SUBPARTITION s13,
			SUBPARTITION s14,
			SUBPARTITION s15,
			SUBPARTITION s16,
			SUBPARTITION s17
		)
);

上述语句创建了三个按照RANGE划分的主分区，每个主分区下面有六个按照HASH划分的子分区。

插入测试数据：

INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH&＃39;,UNIX_TIMESTAMP(NOW()),1);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 2&＃39;,UNIX_TIMESTAMP(NOW()),2);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 3&＃39;,UNIX_TIMESTAMP(NOW()),3);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 10&＃39;,UNIX_TIMESTAMP(NOW()),10);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 7&＃39;,UNIX_TIMESTAMP(NOW()),7);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 5&＃39;,UNIX_TIMESTAMP(NOW()),5);

INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH&＃39;,1451577607,1);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 2&＃39;,1451577609,2);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 3&＃39;,1451577623,3);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 10&＃39;,1451577654,10);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 7&＃39;,1451577687,7);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 5&＃39;,1451577699,5);

INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH&＃39;,1514736056,1);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 2&＃39;,1514736066,2);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 3&＃39;,1514736076,3);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 10&＃39;,1514736086,10);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 7&＃39;,1514736089,7);
INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,&＃39;Hello HASH 5&＃39;,1514736098,5);

EXPLAIN PARTITIONS SELECT * FROM msgss;

可以检测到分区信息如下：

检测分区数据分布：

EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`<1451577600;

EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1451577600 AND `atime`<1483200000;

EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1483200000 AND `atime`<1514736000;

EXPLAIN PARTITIONS SELECT * FROM msgss WHERE `atime`>1514736000;

需求：目前已经是2017年，需要将2015年所有的聊天记录删除，但是保留2016年的聊天记录，并且2017年的数据也能正常按照分区进行存储。

实现以上需求，需要两步，第一步删除t0分区，第二步按照新规则重建分区。

删除分区语句：

ALTER TABLE `msgss` DROP PARTITION t0;

重建分区语句：

ALTER TABLE `msgss` PARTITION BY RANGE (atime) SUBPARTITION BY HASH (sub_id) 
(
		PARTITION t0 VALUES LESS THAN(1483200000)
		(
			SUBPARTITION s0,
			SUBPARTITION s1,
			SUBPARTITION s2,
			SUBPARTITION s3,
			SUBPARTITION s4,
			SUBPARTITION s5
		),
		PARTITION t1 VALUES LESS THAN(1514736000)
		(
			SUBPARTITION s6,
			SUBPARTITION s7,
			SUBPARTITION s8,
			SUBPARTITION s9,
			SUBPARTITION s10,
			SUBPARTITION s11
		),
		PARTITION t2 VALUES LESS THAN MAXVALUE
		(
			SUBPARTITION s12,
			SUBPARTITION s13,
			SUBPARTITION s14,
			SUBPARTITION s15,
			SUBPARTITION s16,
			SUBPARTITION s17
		)
);