MySQL :: MySQL 8.4 参考手册

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 26 分区 / 26.2 分区类型 / 26.2.6 子分区

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

26.2.6 子分区26.2.6 Subpartitioning

子分区——也称为复合分区——是对分区表中每个分区的进一步划分。考虑以下 CREATE TABLE 语句：Subpartitioning—also known as composite partitioning—is the further division of each partition in a partitioned table. Consider the following CREATE TABLE statement:

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
    );

表 ts 有 3 个 RANGE 分区。每个分区——p0、p1 和 p2——都进一步划分为 2 个子分区。实际上，整个表被划分为 3 * 2 = 6 个分区。然而，由于 PARTITION BY RANGE 子句的作用，这其中的前两个分区只存储 purchased 列中值小于 1990 的记录。Table ts has 3 RANGE partitions. Each of these partitions—p0, p1, and p2—is further divided into 2 subpartitions. In effect, the entire table is divided into 3 * 2 = 6 partitions. However, due to the action of the PARTITION BY RANGE clause, the first 2 of these store only those records with a value less than 1990 in the purchased column.

可以对子分区表进行子分区，这些表按 RANGE 或 LIST 分区。子分区可以使用 HASH 或 KEY 分区。这也被称为复合分区。It is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning. This is also known as composite partitioning.

Note

SUBPARTITION BY HASH 和 SUBPARTITION BY KEY 通常遵循与 PARTITION BY HASH 和 PARTITION BY KEY 相同的语法规则。一个例外是，SUBPARTITION BY KEY（不同于 PARTITION BY KEY）当前不支持默认列，因此必须指定用于此目的的列，即使表具有显式主键。这是一个已知问题，我们正在努力解决；有关更多信息和示例，请参见子分区问题。SUBPARTITION BY HASH and SUBPARTITION BY KEY generally follow the same syntax rules as PARTITION BY HASH and PARTITION BY KEY, respectively. An exception to this is that SUBPARTITION BY KEY (unlike PARTITION BY KEY) does not currently support a default column, so the column used for this purpose must be specified, even if the table has an explicit primary key. This is a known issue which we are working to address; see Issues with subpartitions, for more information and an example.

也可以使用 SUBPARTITION 子句显式地定义子分区，以指定各个子分区的选项。例如，与前一个示例中所示的创建相同表 ts 的方式相比，更详细的写法如下：It is also possible to define subpartitions explicitly using SUBPARTITION clauses to specify options for individual subpartitions. For example, a more verbose fashion of creating the same table ts as shown in the previous example would be:

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
        )
    );

这里列出了一些需要注意的语法项：Some syntactical items of note are listed here:

每个分区必须具有相同数量的子分区。Each partition must have the same number of subpartitions.
如果在分区表的任何分区上使用 SUBPARTITION 显式地定义了任何子分区，则必须定义所有子分区。换句话说，以下语句会失败：If you explicitly define any subpartitions using SUBPARTITION on any partition of a partitioned table, you must define them all. In other words, the following statement fails:
```
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
        )
    );
```
即使该语句使用 SUBPARTITIONS 2，它仍然会失败。This statement would still fail even if it used SUBPARTITIONS 2.
每个 SUBPARTITION 子句必须至少包含子分区的名称。否则，您可以为子分区设置任何所需的选项，或者允许它采用该选项的默认设置。Each SUBPARTITION clause must include (at a minimum) a name for the subpartition. Otherwise, you may set any desired option for the subpartition or allow it to assume its default setting for that option.

子分区名称在整个表中必须唯一。例如，以下 CREATE TABLE 语句是有效的：Subpartition names must be unique across the entire table. For example, the following CREATE TABLE statement is valid:

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
        )
    );

首页上一级下一节