MySQL Sub-partition

Introduction

The sub-partition is actually separate each partition of each partition table, currently only the range and list partition table can be sub-partition, sub-partition can only be hash or key partition. Sub-partitions can divide the original data into partitions again.

First, create sub-partitions

Sub-partitions are created by two methods, one is to not define each sub-partition sub-partition name and path is determined by the partition, the second is to define each sub-partition's partition name and the respective path

1. Do not define each sub-partition

CREATE TABLE tb_sub (id INT, purchased DATE)    PARTITION by RANGE (year (purchased))    Subpartition by HASH (to_days (purchased)    )2  (        PARTITION p0 Values less THAN (1990),        PARTITION p1 values less THAN ( c12/>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 sub-partitions to define specific partition names and partition paths for each sub-partition

CREATE TABLE Tb_sub_ev (id INT, purchased DATE)    PARTITION by RANGE (purchased)    subpartition by HASH (To_days (purchased)) (        PARTITION p0 VALUES less THAN (  1990) (            subpartition s0,            subpartition s1        ),        PARTITION p1 VALUES less THAN (  - ) (            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 inserting three records inside, where ' 1989-01-01 ' and ' 1989-04-19 ' are stored in the P0_S0 partition, ' 1989-03-19 ' is stored in the P0_S1

Second, partition management

Partition management is the same as the partition management of range and list

1. Merging partitions

Merging P0,P1 two partitions

ALTER TABLE Tb_sub_ev REORGANIZE PARTITION p0,p1 into (    PARTITION M1 VALUES less THAN) 
    
      (subpartition n0,      subpartition N1     )     );
    

Note: The sub-partition of the merge partition must also be two, which needs to be understood because only two sub-partitions of each partition must be consistent with the partition being created, and merging partitions will not cause data loss.

2. Splitting partitions

ALTER TABLE Tb_sub_ev REORGANIZE PARTITION M1 into (PARTITION     p0 VALUES less THAN (1990) (            subpartition s0,            subpartition s1        ),        PARTITION p1 VALUES less THAN (            subpartition S2,            Subpartition S3        )    );

Similarly, splitting a partition must also ensure that each partition is two sub-partitions.

3. Deleting a partition

ALTER TABLE  Tb_sub_ev DROP PARTITION P0;

Note: Because the partition is a range and list partition, the delete partition is also the same as the range and list partitions, where each partition can only be deleted, not deleted for each sub-partition, and the sub-partition is deleted along with the data after the partition is deleted.

Third, wrong sub-partition creation

1. Either do not define each sub-partition or you need to define each

CREATE TABLE Tb_sub_ev_nex (id INT, purchased DATE)    PARTITION by RANGE (purchased)    subpartition by HASH (To_days (purchased)) (        PARTITION p0 VALUES less THAN (  1990) (            subpartition s0,            subpartition s1        ),        PARTITION p1 VALUES less THAN (  - ),        PARTITION p2 VALUES less THAN MAXVALUE (            subpartition S4,            subpartition S5        )    );  

This is because partition P1 does not define sub-partitions, so creating partitions fails

Iv. removing partitions from a table

ALTER TABLE Tablenameremove Partitioning;

Note: Removing a partition using remove removes only the definition of the partition and does not delete the data and the drop partition, which is deleted along with the data

Reference:

Range Partition: 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 each partition path: http://www.cnblogs.com/chenmh/p/5644713.html

Partition Build Index: http://www.cnblogs.com/chenmh/p/5761995.html

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

Summary

The benefit of sub-partitioning is that the partition's data can be re-divided so that the data is more dispersed, and each sub-partition can be defined with its own storage path, which is explained separately in the next article that specifies each partition path.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

MySQL Sub-partition