| |
MySQL supports composite partitions Oracle is far from rich. In MySQL 5.6 release, only supports RANGE and LIST sub-district, sub-district and only type of HASH and KEY.
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
);
Create the above statement, the outermost layer is RANGE partition, divided into three zones, which are HASH sub-partition, divided into two zones, so that the table was divided into 3 * 2 = 6 partitions.
Of course, you can also use SUBPARTITION statement to define the display sub-partition.
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 SUBPARTITION statement in the partition, each partition must be defined, and the number of neutrons each partition partition must be consistent. For example, the following two usage will be given:
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> 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 is created, will be distributed to different partitions under different physical paths, it will undoubtedly greatly dispersed IO, which is still quite attractive.
Unfortunately, the unit testing process, reported "ERROR 1030 (HY000): Got error -1 from storage engine" error, the specific reasons not yet clear, suspected of MySQL bug.
reference:
http://dev.mysql.com/doc/refman/5.6/en/partitioning-subpartitions.html
http://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html |
|
|
|