MySQL复合分区_MySQL

到底还是开源软件，MySQL对复合分区的支持远远没有Oracle丰富。

在MySQL 5.6版本中，只支持RANGE和LIST的子分区，且子分区的类型只能为HASH和KEY。

譬如：

本文来源gao@daima#com搞(%代@#码网@

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    );

上述创建语句中，最外层是RANGE分区，分为3个区，里面是HASH子分区，分为2个区，这样，该表一共分了3*2=6个分区。

当然，也可以用SUBPARTITION语句来显示定义子分区。

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        )    );

注意：

1> 如果你在分区中使用了SUBPARTITION语句，则每个分区中都必须定义，且每个分区中子分区的数量必须保持一致。譬如以下两种用法就会报错：

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语句中，可指定该分区的物理位置。譬如：

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        )    );

以上这个创建语句，将不同的分区分布到不同的物理路径下，无疑会极大的分散IO，这一点还是蛮吸引人的。

可惜，在本机测试过程中，报“ERROR 1030 (HY000): Got error -1 from storage engine”错误，具体原因还不太清楚，怀疑是MySQL的bug。

喜欢 (0)赏

[搞代码]

分享 (0)