Describes how MySQL rebuilds table partitions and preserves data

This article describes MySQL rebuilding table partitioning and preserving data methods, MySQL table partitioning (partition)The records of a table can be separated into multiple areas to store, the query can be based on the conditions of the query in the corresponding partition search, without the need for an entire table query, improve query efficiency.

There is not much difference between partitioned tables and tables that do not have partitions, but if you want to repartition the tables, deleting partition rebuilds deletes the data , so it is not straightforward to operate and requires some special processing implementations.

How MySQL rebuilds table partitions and preserves data:

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

Instance:

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

CREATE DATABASE ' test '; use ' test '; CREATE TABLE ' log ' (' id ' int (one) unsigned not null auto_increment, ' content ' text NOT null COMMENT ' contents ', ' status ' tinyint (3) unsigned not null COMMENT ' record state ', ' addtime ' int (one) unsigned NOT null COMMENT ' Add time ', ' lastmodify ' int (one) unsigned NO T NULL COMMENT ' Last modified time ', PRIMARY KEY (' id ')) engine=innodb auto_increment=1 DEFAULT charset=utf8/*!50100 PARTITION by RANG E (ID) (PARTITION p10w values less THAN (100000) engine = Innodb,partition p20w values less THAN (200000) engine = Innodb,p Artition p50w values less THAN (500000) engine = innodb,partition p100w values less THAN (1000000) engine = Innodb,partiti On pmax values less THAN MAXVALUE ENGINE = InnoDB) */;insert into ' log ' (content,status,addtime,lastmodify) VALUES (' content 1 ', 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 the partition needs to be rebuilt by log time .

1. Create a log2, partitioned by time (1 partitions per month)

CREATE TABLE ' log2 ' (' id ' int (one) unsigned not null auto_increment, ' content ' text NOT null COMMENT ' contents ', ' status ' Tinyin T (3) unsigned not null COMMENT ' record state ', ' addtime ' int (one) unsigned NOT null COMMENT ' Add time ', ' lastmodify ' int (one) unsigned N OT NULL COMMENT ' Last modified time ', PRIMARY key (' id ', ' addtime '), key ' id ' (' id '), key ' Addtime ' (' Addtime ')) Engine=innodb Auto_incre Ment=1 DEFAULT charset=utf8/*!50100 PARTITION by RANGE (addtime) (PARTITION p201801 VALUES less THAN (' 2018 -02-01 00:00:00 ') engine = innodb,partition p201802 VALUES less THAN (Unix_timestamp (' 2018-03-01 00:00:00 ') engine = Inn Odb,partition p201803 values less THAN (Unix_timestamp (' 2018-04-01 00:00:00 ') ENGINE = innodb,partition p201804 values LE SS 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 the 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 ';

View 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 log table's data has been stored in the new partition.

This article describes how MySQL rebuilds table partitions and preserves data, and more about the PHP Chinese web.

Related recommendations:

PHP Json_encode does not support object private property resolution

PHP generates a unique RequestID class of related content

JS basic data type and conversion operator