你的位置:首页 > 数据库

[数据库]MySQL复合分区


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

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

譬如:

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。

参考:

http://dev.mysql.com/doc/refman/5.6/en/partitioning-subpartitions.html

http://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html