MySQL sub-partitions and mysql partitions

MySQL sub-partitions and mysql partitions
Introduction

Subpartitions are actually used to separate each partition in each partition table. Currently, only tables with RANGE and LIST partitions can perform subpartitions. subpartitions can only be HASH or KEY partitions. Subpartitions can partition the original data again.

 

 

1. Create a subpartition

There are two ways to create a subpartition. One is that the name and path of each subpartition are not determined by the partition. The other is to define the partition name and path of each subpartition.

1. Do not define each subpartition

CREATE TABLE tb_sub (id INT, purchased DATE)    PARTITION BY RANGE( YEAR(purchased) )    SUBPARTITION BY HASH( TO_DAYS(purchased) )    SUBPARTITIONS 2 (        PARTITION p0 VALUES LESS THAN (1990),        PARTITION p1 VALUES LESS THAN (2000),        PARTITION p2 VALUES LESS THAN MAXVALUE    );

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tb_sub';

2. Define each sub-partition

Define a subpartition. You can define the specific partition name and path for each subpartition.

CREATE TABLE tb_sub_ev (id INT, purchased DATE)    PARTITION BY RANGE( YEAR(purchased) )    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (        PARTITION p0 VALUES LESS THAN (1990) (            SUBPARTITION s0,            SUBPARTITION s1        ),        PARTITION p1 VALUES LESS THAN (2000) (            SUBPARTITION s2,            SUBPARTITION s3        ),        PARTITION p2 VALUES LESS THAN MAXVALUE (            SUBPARTITION s4,            SUBPARTITION s5        )    );

3. Test Data

INSERT INTO tb_sub_ev() VALUES(1,'1989-01-01'),(2,'1989-03-19'),(3,'1989-04-19');

When three records are inserted into the table, '2017-01-01 'and '2017-04-19' are stored in the p0_s0 partition, and '2017-03-19 'is stored in p0_s1.

Ii. Partition Management

Partition management is the same as RANGE and LIST partition management.

1. merge partitions

Merge p0 and p1 partitions

ALTER TABLE tb_sub_ev REORGANIZE PARTITION p0,p1 INTO (    PARTITION m1 VALUES LESS THAN (2000)    ( SUBPARTITION n0,      SUBPARTITION n1     )     );

Note: The Sub-partitions of the merged partition must be two. This must be understood because they must be consistent with only two sub-partitions when creating the partition, And the merged partition will not cause data loss.

2. Shard

ALTER TABLE tb_sub_ev REORGANIZE PARTITION m1 INTO (     PARTITION p0 VALUES LESS THAN (1990) (            SUBPARTITION s0,            SUBPARTITION s1        ),        PARTITION p1 VALUES LESS THAN (2000) (            SUBPARTITION s2,            SUBPARTITION s3        )    );

Similarly, to split a partition, you must ensure that each partition is composed of two subpartitions.

3. delete partitions

ALTER TABLE  tb_sub_ev DROP PARTITION P0;

Note: Because partitions are RANGE and LIST partitions, deleting partitions is the same as RANGE and LIST partitions. You can only delete each partition, but not each subpartition, after the partition is deleted, the subpartition is deleted along with the data.

3. Incorrect subpartition Creation

1. Do not define each sub-partition or define each sub-partition

 CREATE TABLE tb_sub_ev_nex (id INT, purchased DATE)    PARTITION BY RANGE( YEAR(purchased) )    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (        PARTITION p0 VALUES LESS THAN (1990) (            SUBPARTITION s0,            SUBPARTITION s1        ),        PARTITION p1 VALUES LESS THAN (2000),        PARTITION p2 VALUES LESS THAN MAXVALUE (            SUBPARTITION s4,            SUBPARTITION s5        )    );  

Partition p1 does not define a subpartition, so partition creation fails.

4. Remove table partitions

ALTER TABLE tablenameREMOVE PARTITIONING ;

Note: using remove to remove a PARTITION is to only remove the definition of the PARTITION, and the data will not be deleted differently from the drop PARTITION. The latter will be deleted together with the data.

 

 

Refer:

RANGE partitioning: http://www.cnblogs.com/chenmh/p/5627912.html

LIST partition: http://www.cnblogs.com/chenmh/p/5643174.html

COLUMN partition: http://www.cnblogs.com/chenmh/p/5630834.html

HASH partition: http://www.cnblogs.com/chenmh/p/5644496.html

KEY partition: http://www.cnblogs.com/chenmh/p/5647210.html

Specify partition paths: http://www.cnblogs.com/chenmh/p/5644713.html

Partitioning index: http://www.cnblogs.com/chenmh/p/5761995.html

Partition Summary: http://www.cnblogs.com/chenmh/p/5623474.html

Summary

The benefit of sub-partitions is that the data in the partition can be further divided, so that the data is more scattered and each sub-partition can be defined with its own storage path, this section is explained separately in the next article specifying the partition paths.

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion