Detailed explanation of mysql related methods to rebuild table partitions and retain data

This article introduces the method of mysql to rebuild table partitions and retain data. Mysql table partition (partition) can separate the records of a table into multiple areas for storage. When querying, the records can be stored in the corresponding areas according to the query conditions. Partition search does not require the entire table query, improving query efficiency.

There is not much difference in the use of partitioned tables and non-partitioned tables, but if you want to repartition the table, deleting the partition and rebuilding will delete the data, so it cannot Direct operation requires some special processing implementation.

Mysql method to rebuild table partitions and retain data:

1. Create a new table and new partition with the same structure as the original table.
2. Copy the data in the original table to the new table.
3. Delete the original table.
4. Change the new table name to the original table name.

Example:

The original structure of the log table is as follows, partitioned by id.

CREATE DATABASE `test`;use `test`;CREATE TABLE `log` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `content` text NOT NULL COMMENT '内容', `status` tinyint(3) unsigned NOT NULL COMMENT '记录状态', `addtime` int(11) unsigned NOT NULL COMMENT '添加时间', `lastmodify` int(11) unsigned NOT NULL COMMENT '最后修改时间', PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p10w VALUES LESS THAN (100000) ENGINE = InnoDB,
PARTITION p20w VALUES LESS THAN (200000) ENGINE = InnoDB,
PARTITION p50w VALUES LESS THAN (500000) ENGINE = InnoDB,
PARTITION p100w VALUES LESS THAN (1000000) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;insert into `log`(content,status,addtime,lastmodify) 
values('content1',1, unix_timestamp('2018-01-11 00:00:00'), unix_timestamp('2018-01-11 00:00:00')),
('content2',1, unix_timestamp('2018-02-22 00:00:00'), unix_timestamp('2018-02-22 00:00:00')),
('content3',1, unix_timestamp('2018-03-31 00:00:00'), unix_timestamp('2018-03-31 00:00:00'));

View data partition distribution

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME = 'log';+----------------+------------+| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+| p10w           |          3 |
| p20w           |          0 |
| p50w           |          0 |
| p100w          |          0 || pmax           |          0 |
+----------------+------------+

Log data needs to be searched by time, so it is necessary to rebuild partitions by log time.

1. Create log2 and partition it by time (1 partition per month)

CREATE TABLE `log2` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `content` text NOT NULL COMMENT '内容', `status` tinyint(3) unsigned NOT NULL COMMENT '记录状态', `addtime` int(11) unsigned NOT NULL COMMENT '添加时间', `lastmodify` int(11) unsigned NOT NULL COMMENT '最后修改时间', PRIMARY KEY (`id`,`addtime`), KEY `id`(`id`), KEY `addtime`(`addtime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (addtime)
(PARTITION p201801 VALUES LESS THAN (unix_timestamp('2018-02-01 00:00:00')) ENGINE = InnoDB,
PARTITION p201802 VALUES LESS THAN (unix_timestamp('2018-03-01 00:00:00')) ENGINE = InnoDB,
PARTITION p201803 VALUES LESS THAN (unix_timestamp('2018-04-01 00:00:00')) ENGINE = InnoDB,
PARTITION p201804 VALUES LESS THAN (unix_timestamp('2018-05-01 00:00:00')) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

2. Copy the log data to log2

insert into `log2` select * from `log`;

3. Delete the log table

drop table `log`;

4. Rename the log2 table to log

rename table `log2` to `log`;

Check the data partition distribution after execution

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME = 'log';+----------------+------------+| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+| p201801        |          1 |
| p201802        |          1 |
| p201803        |          1 |
| p201804        |          0 || pmax           |          0 |
+----------------+------------+

You can see that the data in the log table has been stored in new partitions.

This article explains the relevant methods of MySQL to rebuild table partitions and retain data. For more related knowledge, please pay attention to the PHP Chinese website.

Related recommendations:

Explanation about php json_encode not supporting object private attributes

Explanation of PHP generating unique RequestID class related content

MySQL View database table capacity

The above is the detailed content of Detailed explanation of mysql related methods to rebuild table partitions and retain data. For more information, please follow other related articles on the PHP Chinese website!