MySQL composite Partition

MySQL composite Partition
In the end, it is still open-source software. MySQL's support for composite partitions is far from that of Oracle. In MySQL 5.6, only the RANGE and LIST subpartitions are supported, and the subpartitions can only be HASH and KEY types. For example:

CREATE TABLE ts (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    );

 

In the above statement, the outermost layer is the RANGE partition, which is divided into three partitions, which are HASH subpartitions and divided into two partitions. In this way, this table is divided into 3*2 = 6 partitions. Of course, you can also use the SUBPARTITION statement to display and define subpartitions.

CREATE TABLE ts (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        )    );

 

Note: 1> if you use the SUBPARTITION statement in a partition, each partition must be defined and the number of subpartitions in each partition must be consistent. For example, an error is reported in the following two usage methods:

CREATE TABLE ts (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        ),        PARTITION p2 VALUES LESS THAN MAXVALUE (            SUBPARTITION s3,            SUBPARTITION s4        )    );CREATE TABLE ts (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 s2,            SUBPARTITION s3        )    );

 

2> In the SUBPARTITION statement, you can specify the physical location of the partition. For example:

CREATE TABLE ts (id INT, purchased DATE)    PARTITION BY RANGE(YEAR(purchased))    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (        PARTITION p0 VALUES LESS THAN (1990) (            SUBPARTITION s0a                DATA DIRECTORY = '/disk0'                INDEX DIRECTORY = '/disk1',            SUBPARTITION s0b                DATA DIRECTORY = '/disk2'                INDEX DIRECTORY = '/disk3'        ),        PARTITION p1 VALUES LESS THAN (2000) (            SUBPARTITION s1a                DATA DIRECTORY = '/disk4/data'                INDEX DIRECTORY = '/disk4/idx',            SUBPARTITION s1b                DATA DIRECTORY = '/disk5/data'                INDEX DIRECTORY = '/disk5/idx'        ),        PARTITION p2 VALUES LESS THAN MAXVALUE (            SUBPARTITION s2a,            SUBPARTITION s2b        )    );

 

The above statement will distribute different partitions to different physical paths, which will undoubtedly greatly distribute IO, which is quite attractive. Unfortunately, during the local test, the ERROR "error 1030 (HY000): Got ERROR-1 from storage engine" is reported. The specific cause is unclear and it is suspected that it is a MySQL bug.