到底还是开源软件,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。