Mysql partition table management and maintenance _ MySQL

For more information about Mysql partition tables, see http: blogcsdnnetjhq0113articledetails44592865. for details about how to create Mysql partition tables, refer to: http: blogcsdnne Mysql supports four types of table partitions: RANGE and LIST, HASH and KEY, the RANGE and LIST are similar. partitions are based on one interval. HASH is similar to KEY, and fields are partitioned based on an algorithm.

RANGE and LIST partition management:

Case: there is a chat record table with thousands of users. The table has been horizontally split according to a certain user granularity, and there are still many records stored in some tables, as a result, the table is partitioned in the following ways. the advantage of the partition is that the partition can be dynamically changed. after the partition is deleted, the data is also deleted. for example, the chat record is saved for only two years, then you can partition by time, regularly delete partitions two years ago, and dynamically create new partitions to achieve good data maintenance.

The statement for creating a partition table is as follows:

Drop table if exists 'msgss'; create table 'msgss' ('id' bigint (20) unsigned not null AUTO_INCREMENT COMMENT 'table primary key', 'sender' int (10) unsigned not null comment 'sender ID', 'referer' int (10) unsigned not null comment 'receiver ID', 'MSG _ type' tinyint (3) unsigned not null comment 'message type', 'MSG 'varchar (225) not null comment 'message content', 'atime' int (10) unsigned not null comment' sending time ', 'Sub _ id' tinyint (3) unsigned not null comment 'Department ID', primary key ('id', 'atime ', 'sub _ id ')) ENGINE = InnoDB default charset = utf8/********** PARTITION information ***************/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, PARTITION s10, SUBPARTITION s11), PARTITION t2 values less than maxvalue (SUBPARTITION s12, SUBPARTITION s13, SUBPARTITION s14, SUBPARTITION s15, SUBPARTITION s16, SUBPARTITION s17 ));

The preceding statement creates three primary partitions divided by RANGE. each primary partition has six subpartitions divided by HASH.

Insert test data:

INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',UNIX_TIMESTAMP(NOW()),1);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',UNIX_TIMESTAMP(NOW()),2);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',UNIX_TIMESTAMP(NOW()),3);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',UNIX_TIMESTAMP(NOW()),10);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',UNIX_TIMESTAMP(NOW()),7);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',UNIX_TIMESTAMP(NOW()),5);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',1451577607,1);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',1451577609,2);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',1451577623,3);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',1451577654,10);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',1451577687,7);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',1451577699,5);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH',1514736056,1);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 2',1514736066,2);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 3',1514736076,3);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 10',1514736086,10);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 7',1514736089,7);INSERT INTO `msgss`(`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES(1,2,0,'Hello HASH 5',1514736098,5);

Partition analysis:

Explain partitions select * FROM msgss;

Partition information can be detected as follows:

Check partition data distribution:

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;

Result: the first statement only scans all subpartitions of t0, the second statement only scans all subpartitions of t1, and the third and fourth statements only scan all subpartitions of t2, this proves that the table partition and data distribution are successful.

Requirement: it is already in July 2017. all chat records in July 2015 need to be deleted, but the chat records in July 2016 are retained, and the data in July 2017 can be stored by partition normally.

To meet the preceding requirements, two steps are required: Step 1: delete the t0 partition, and Step 2: Re-create the partition according to the new rule.

Partition deletion statement:

Alter table 'msgss' drop partition t0;

Statement for re-partitioning:

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));

The query found that all data in the past 15 years has been deleted, and the remaining data has been repartitioned and distributed.