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