MySQL database sub-partition tutorial

MySQL database sub-partition tutorial

A child partition is a second split for each partition in a partitioned table. For example, consider the following create TABLE statement:

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

);

The table TS has 3 range partitions. Each of these 3 partitions--p0, p1, and p2--are further divided into 2 sub partitions. In fact, the entire table is divided into 3 * 2 = 6 partitions. However, because of the role of the partition by range clause, the first 2 of these partitions only hold those records in the "purchased" column whose values are less than 1990.

In MySQL 5.1, it is possible to repartition a table that has been partitioned by range or list. A child partition can use either hash or key partitions. This is also known as a composite partition (composite partitioning).

In order to specify options for individual sub partitions, it is also possible to use the Subpartition clause to explicitly define a child partition. For example, a more detailed way to create the same table given in the previous example is as follows:

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

)

);

Some of the syntax items to note:

· Each partition must have the same number of sub partitions.

· If you use Subpartition to explicitly define any child partitions on any partition on a partitioned table, you must define all of the child partitions. In other words, the following statement executes the failure:

· 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

· )

· );

Even if the statement contains a subpartitions 2 clause, it will still fail.

· Each subpartition clause must include (at least) a name of the child partition. Otherwise, you may want to set any options you want to the child partition, or allow the child partition to use its default settings for those options.

· Within each partition, the name of the sub partition must be unique, but there is no need to remain unique throughout the table. For example, the following create TABLE statement is valid:

· 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 S0,

· Subpartition S1

· ),

· PARTITION p2 VALUES less THAN MAXVALUE

· (

· Subpartition S0,

· Subpartition S1

· )

· );

A child partition can be used in a particularly large table to allocate data and indexes across multiple disks. Suppose there are 6 disks, namely/disk0,/disk1,/DISK2, etc. Now consider the following 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 S0

DATA DIRECTORY = '/disk0/data '

INDEX DIRECTORY = '/disk0/idx ',

Subpartition S1

DATA DIRECTORY = '/disk1/data '

INDEX DIRECTORY = '/disk1/idx '

),

PARTITION p1 VALUES less THAN (2000)

(

Subpartition S0

DATA DIRECTORY = '/disk2/data '

INDEX DIRECTORY = '/disk2/idx ',

Subpartition S1

DATA DIRECTORY = '/disk3/data '

INDEX DIRECTORY = '/disk3/idx '

),

PARTITION p2 VALUES less THAN MAXVALUE

(

Subpartition S0

DATA DIRECTORY = '/disk4/data '

INDEX DIRECTORY = '/disk4/idx ',

Subpartition S1

DATA DIRECTORY = '/disk5/data '

INDEX DIRECTORY = '/disk5/idx '

)

);

In this example, the data and indexes for each range partition use a separate disk. There may be many other changes, and here is another possible 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

)

);

In this example, the storage is allocated as follows:

· The purchase date of 1990 years ago accounted for a large amount of storage space, so it was divided into four parts for storage, the data and indexes of the two sub partitions (S0A and s0b) comprising the P0 partition were stored separately with a separate disk. Other words:

o Sub-partition s0a data is saved in disk/disk0.

o Sub-partition s0a index is saved in disk/disk1.

o Sub-partition s0b data is saved in disk/disk2.

o Sub-partition s0b index is saved in disk/DISK3.

· The record of saving the purchase date from 1990 to 1999 (partition P1) does not need to save as much storage space as the record of the purchase date before 1990. These records are saved on 2 disks (/DISK4 and/DISK5) instead of 4 disks:

o The data and index of the first child partition (S1A) belonging to the partition P1 are saved on disk/DISK4-where the data is saved under the path/disk4/data and the index is saved under/DISK4/IDX.

o The data and index of the second sub partition (S1B) belonging to the partition P1 are saved on disk/DISK5-where the data is saved under the path/disk5/data and the index is saved under/DISK5/IDX.

· Save the purchase date from 2000 to the current record (partition P2) does not require as much space as the previous two range partitions. Currently, the default location is sufficient to hold all of these records.

In the future, if the number purchased 10 years after 2000 has reached the default location and cannot provide sufficient storage space, the corresponding record (line) can be used by using "ALTER TABLE ..." REORGANIZE PARTITION "statement moved to another location.

Note : Please pay attention to the triple programming Tutorials section for more wonderful articles .