子分区
子分区(也称为复合分区)是分区表中每个分区的进一步划分。考虑以下CREATE TABLE
语句:
CREATE TABLE ts (id INT, purchased DATE)PARTITION BY RANGE ( YEAR(purchased) )SUBPARTITION BY HASH ( TO_DAYS(purchased) )SUBPARTITIONS 2 (PARTITION p0VALUES LESS THAN (1990),PARTITION p1VALUES LESS THAN (2000),PARTITION p2VALUES LESS THAN MAXVALUE );
表ts
有3个RANGE
分区。这些分区中的每个分区p0
,p1
和p2
进一步分为2个子分区。实际上,整个表分为多个3 * 2 = 6
分区。但是,由于该PARTITION BY RANGE
子句的作用,其中的前2个仅将值小于1990的记录存储在该purchased
列中。
可以对通过RANGE
或进行分区的表进行子分区LIST
。子分区可以使用HASH
或KEY
分区。这也称为复合分区。
注意
SUBPARTITION BY HASH
和SUBPARTITION BY KEY
一般遵循相同的语法规则PARTITION BY HASH
和PARTITION BY KEY
分别。唯一的例外是SUBPARTITION BY KEY
(不同于PARTITION BY KEY
)当前不支持默认列,因此即使表具有显式主键,也必须指定用于此目的的列。这是我们正在努力解决的已知问题;有关更多信息和示例,请参阅子分区问题。
也可以使用SUBPARTITION
子句明确定义子分区,以指定各个子分区的选项。例如,ts
如上例所示,创建相同表的更详细的方式是:
CREATE TABLE ts (id INT, purchased DATE)PARTITION BY RANGE ( YEAR(purchased) )SUBPARTITION BY HASH ( TO_DAYS(purchased) ) (PARTITION p0VALUES LESS THAN (1990) (SUBPARTITION s0,SUBPARTITION s1 ),PARTITION p1VALUES LESS THAN (2000) (SUBPARTITION s2,SUBPARTITION s3 ),PARTITION p2VALUES LESS THAN MAXVALUE (SUBPARTITION s4,SUBPARTITION s5 ) );
这里列出了一些语法上的注意事项:
- 每个分区必须具有相同数量的子分区。
如果
SUBPARTITION
在分区表的任何分区上使用显式定义任何子分区,则必须全部定义它们。换句话说,以下语句将失败:CREATE TABLE ts (id INT, purchased DATE)PARTITION BY RANGE ( YEAR(purchased) )SUBPARTITION BY HASH ( TO_DAYS(purchased) ) (PARTITION p0VALUES LESS THAN (1990) (SUBPARTITION s0,SUBPARTITION s1 ),PARTITION p1VALUES LESS THAN (2000),PARTITION p2VALUES LESS THAN MAXVALUE (SUBPARTITION s2,SUBPARTITION s3 ) );即使使用此语句,它仍然会失败
SUBPARTITIONS 2
。- 每个
SUBPARTITION
子句必须(至少)包括子分区的名称。否则,您可以为子分区设置任何所需的选项,或允许其采用该选项的默认设置。 子分区名称在整个表中必须唯一。例如,以下
CREATE TABLE
语句有效:CREATE TABLE ts (id INT, purchased DATE)PARTITION BY RANGE ( YEAR(purchased) )SUBPARTITION BY HASH ( TO_DAYS(purchased) ) (PARTITION p0VALUES LESS THAN (1990) (SUBPARTITION s0,SUBPARTITION s1 ),PARTITION p1VALUES LESS THAN (2000) (SUBPARTITION s2,SUBPARTITION s3 ),PARTITION p2VALUES LESS THAN MAXVALUE (SUBPARTITION s4,SUBPARTITION s5 ) );