MySQL中的分区(七)子分区(复合分区)

子分区(Subpartitioning)是对已分区的分区表再次分区,又称为复合分区(Composite Partitioning)。

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 p3 VALUES LESS THAN MAXVALUE

    );

 

(子分区)复合分区用于保存非常大量的数据:

  1. 每个分区必须有相同数量的子分区。
  2. 如果要显示指定子分区,则每个分区都要显示指定。
  3. 子分区的名称在整个表中是唯一的。

CREATE TABLE ts1 (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 p3 VALUES LESS THAN MAXVALUE(SUBPARTITION s4,SUBPARTITION s5)

    );

posted @ 2020-07-29 17:45  别看窗外的世界  阅读(872)  评论(0编辑  收藏  举报