MySQL Partition Table Management ~ 2

MySQL Partition Table Management ~ 2
I. Maintenance partitions: CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE. These methods also apply to partitions. Next, we will explain the functions of these products. 1. Rebuilding partitions: recreating a partition is equivalent to deleting data in the partition and then re-inserting it. This is mainly used for partitioning. For example: alter table t1 rebuild partition p0, p1; 2. Optimizing partitions this command is mainly used to recycle free space and PARTITION fragments. Executing this command on a PARTITION is equivalent to executing the check partition, analyze partition, and repair partition commands on the PARTITION in sequence. For example: alter table t1 optimize partition p0, p1; Note: Some storage engines, such as InnoDB, do not support PARTITION-based Optimizing. When you execute this command, the entire table will be rebuilt. in MySQL 5.6.9 or later versions, executing this command will cause the entire table to be rebuilt and analyze. In this case, we recommend that you directly use alter table... rebuild partition or alter table... analyze partition. 3. analyzing partitions alter table t1 analyze partition p3; 4. repairing partitions REPAIR damaged partitions alter table t1 repair partition p0, p1; 5. checking partitions CHECK whether the PARTITION has an error alter table trb3 check partition p1; Note: 1> mysqlcheck and myisamchk do not support partition table 2> the preceding PARTITION names can also be replaced by all, operations are performed on all partitions. 3> the ANALYZE, CHECK, OPTIMIZE, REBUILD, REPAIR, and TRUNCATE commands are not applicable to subpartitions. Ii. How to obtain partition information 1. you can use the show create table statement to view partition clauses of a partition TABLE, for example: mysql> show create table e \ G ***************************** 1. row *************************** Table: eCreate Table: create table 'E' ('id' int (11) not null, 'fname' varchar (30) default null, 'lname' varchar (30) default null) ENGINE = InnoDB default charset = latin1 /*! 50100 partition by range (id) (PARTITION p0 values less than (50) ENGINE = InnoDB, PARTITION p1 values less than (100) ENGINE = InnoDB, PARTITION p2 values less than (150) ENGINE = InnoDB, PARTITION p3 values less than maxvalue engine = InnoDB) */1 row in set (0.00 sec) 2. use the show table status statement to check whether the TABLE partition corresponds to the Create_options field. For example: mysql> show table status \ G ***************************** 1. row ************ * ************** Name: e Engine: InnoDB Version: 10 Row_format: Compact Rows: 6 Avg_row_length: 10922 Data_length: 65536Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2015-12-07 22:26:06 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: partitioned Comment: 3. view the INFORMATION_SCHEMA.PARTITIONS table 4. EXPLAIN PARTITIONS S Use the ELECT statement to check which partition the SELECT statement accesses. Iii. Syntax of partition exchange: alter table pt exchange partition p with table nt where pt is a partition table and p is a PARTITION of pt (Note: it can also be a subpartition ), nt is the target table. In fact, there are many restrictions on partition switching: 1> nt cannot be a partition table 2> nt cannot be a temporary table 3> the structure of nt and pt must be consistent 4> nt does not have any foreign key constraints, that is, it cannot be a primary key, it cannot be a foreign key. 5> data in nt cannot be outside the range of p partition.