MySQL Partition table Management

First, maintain the partition

For the maintenance of the table, we generally have the following methods: CHECK table, OPTIMIZE table, ANALYZE table and Repair table.

In these ways, the same applies to partitions. Below, we explain the various roles.

1. Rebuilding partitions

Rebuilds the partition, which is equivalent to deleting the data from the partition and then inserting it again. This is primarily for defragmentation of partitions.

Such as:

ALTER TABLE T1 REBUILD PARTITION p0, p1;

2. Optimizing Partitions

This command is primarily used to reclaim free space and defragmentation of partitions. Executing this command on a partition is equivalent to performing a CHECK PARTITION to the partition sequentially, ANALYZE partition,repair PARTITION command.

Such as:

ALTER TABLE T1 OPTIMIZE PARTITION p0, p1;

Attention:

Some storage engines, such as InnoDB, do not support partition-based optimizing. When you execute the command, the entire table is rebuild, and above the MySQL 5.6.9 version, executing this command causes the entire table to be rebuild and analyze. In this case, it is recommended to use the ALTER TABLE directly ... REBUILD PARTITION or ALTER TABLE ... ANALYZE PARTITION.

3. Analyzing partitions

Read and store the distribution of values in a partition

ALTER TABLE t1 ANALYZE PARTITION P3;

4. Repairing partitions

Repairing a broken partition

ALTER TABLE t1 REPAIR PARTITION p0,p1;

5. Checking partitions

Check partition for errors

ALTER TABLE CHECK PARTITION P1;

Attention:

1> Mysqlcheck and Myisamchk do not support partitioned tables

2> the above partition name can also be replaced by all, which refers to the operation of all partitions.

3> ANALYZE, CHECK, OPTIMIZE, REBUILD, repair, and TRUNCATE commands do not apply to sub-partitions.

Second, how to get information about the partition

1. View the partition clause of a partitioned table by using the Show CREATE table statement

Such as:

Mysql>ShowCreate Tablee\g*************************** 1. Row***************************       Table: ECreate Table:CREATE TABLE' E ' (' ID ' )int( One) not NULL, ' fname 'varchar( -)DEFAULT NULL, ' lname 'varchar( -)DEFAULT NULL) ENGINE=InnoDBDEFAULTCHARSET=latin1/*!50100 PARTITION by RANGE (ID) (PARTITION p0 values less THAN () ENGINE = InnoDB, PARTITION p1 VALUES less THAN (100) Engine = InnoDB, PARTITION p2 values less THAN ($) engine = InnoDB, PARTITION p3 values less THAN MAXVALUE engine = Inn ODB)*/1Rowinch Set(0.00Sec

2. Use the Show Table STATUS statement to see if the table is partitioned

Corresponds to the Create_options field, for example:

Mysql>ShowTableStatus\g*************************** 1. Row***************************name:e engine:innodb Version:Tenrow_format:compact Rows:6avg_row_length:10922data_length:65536max_data_length:0index_length:0Data_free:0auto_increment:NULLCreate_time: -- A- -  A: -: .Update_time:NULLCheck_time:NULLcollation:latin1_swedish_ci Checksum:NULLcreate_options:partitioned Comment:

3. View Information_schema. Partitions table

4. Use the EXPLAIN partitions SELECT statement to see which partition is accessed for the specific SELECT statement.

Third, partition exchange

The syntax for partition switching is as follows:

ALTER TABLE  with TABLE NT

Where PT is the partition table, p is the partition of PT (Note: It can also be a sub-partition), and NT is the target table.

In fact, there are quite a few restrictions on partition switching:

1> NT cannot be a partitioned table

2> NT cannot be a temporary table

3> NT and PT must be in the same structure

4> NT does not have any foreign key constraints, that is, neither the primary key nor the foreign key.

The data in 5> NT cannot be outside the range of the P partition.

Refer to MySQL's official documentation for details

Http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html

MySQL Partition table Management