子分区也称为复合分区,就是在已有分区的基础上再进行分区。MySQL允许在range和list的分区的基础上再进行hash或key分区。
create table t7( a int,b date) engine=innodb partition by range (year(b)) subpartition by hash(to_days(b)) subpartitions 2 ( partition p0 values less than (1990), partition p1 values less than (2000), partition p2 values less than maxvalue); |
指定子分区的名称:
create table t8( a int,b date) engine=innodb partition by range (year(b)) subpartition by hash(to_days(b)) ( partition p0 values less than (1990) ( subpartition s0, subpartition s1 ), partition p1 values less than (2000) ( subpartition s3, subpartition s4 ), partition p2 values less than maxvalue ( subpartition s5, subpartition s6 ) ); |
指定子分区表的存储空间:
create table t9( a int,b date) engine=myisam partition by range (year(b)) subpartition by hash(to_days(b)) ( 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 s2 data directory = '/disk2/data' index directory = '/disk2/idx', subpartition s3 data directory = '/disk3/data' index directory = '/disk3/idx' ), partition p2 values less than maxvalue ( subpartition s4 data directory = '/disk4/data' index directory = '/disk4/idx', subpartition s5 data directory = '/disk5/data' index directory = '/disk5/idx' ) ); |