MySQL :: MySQL 8.4 Reference Manual :: 26.3.1 Management of RANGE and LIST Partitions

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Management of RANGE and LIST Partitions

26.3.1 RANGE 和 LIST 分区的管理

添加和删除范围和列表分区的操作类似，因此我们在本节中讨论两种类型的分区管理。有关使用哈希或键分区表的信息，请参阅第 26.3.2 节，“HASH 和 KEY 分区的管理”。

从使用范围或列表分区的表中删除一个分区可以通过ALTER TABLE语句中的DROP PARTITION选项来完成。假设您已经创建了一个使用范围分区的表，并使用以下CREATE TABLE和INSERT语句填充了10条记录：

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990),
    ->         PARTITION p1 VALUES LESS THAN (1995),
    ->         PARTITION p2 VALUES LESS THAN (2000),
    ->         PARTITION p3 VALUES LESS THAN (2005),
    ->         PARTITION p4 VALUES LESS THAN (2010),
    ->         PARTITION p5 VALUES LESS THAN (2015)
    ->     );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO tr VALUES
    ->     (1, 'desk organiser', '2003-10-15'),
    ->     (2, 'alarm clock', '1997-11-05'),
    ->     (3, 'chair', '2009-03-10'),
    ->     (4, 'bookcase', '1989-01-10'),
    ->     (5, 'exercise bike', '2014-05-09'),
    ->     (6, 'sofa', '1987-06-05'),
    ->     (7, 'espresso maker', '2011-11-22'),
    ->     (8, 'aquarium', '1992-08-04'),
    ->     (9, 'study desk', '2006-09-16'),
    ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.05 sec)
Records: 10  Duplicates: 0  Warnings: 0

您可以看到应该被插入到分区 p2 中的项，如下所示：

mysql> SELECT * FROM tr
    ->     WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

您还可以使用分区选择来获取此信息，如下所示：

mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

请参阅第 26.5 节，“分区选择”，了解更多信息。

要删除名为 p2 的分区，请执行以下命令：

mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)

Note

NDBCLUSTER 存储引擎不支持ALTER TABLE ... DROP PARTITION。它确实支持ALTER TABLE的其他分区相关扩展，描述在本章节中。

当您删除一个分区时，您也会删除该分区中存储的所有数据。通过重新运行之前的SELECT查询，您可以看到这一点：

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)

Note

DROP PARTITION 支持本地分区API和可能与 ALGORITHM={COPY|INPLACE} 一起使用。DROP PARTITION 与 ALGORITHM=INPLACE 删除分区中的数据并删除分区。然而，DROP PARTITION 与 ALGORITHM=COPY 或 old_alter_table=ON 重建分区表并尝试将数据从删除的分区移动到另一个具有兼容 PARTITION ... VALUES 定义的分区。无法移动到另一个分区的数据会被删除。

因此，您必须在执行 ALTER TABLE ... DROP PARTITION 在该表上之前拥有DROP 权限。

如果您希望删除所有分区中的所有数据，同时保留表定义及其分区方案，请使用 TRUNCATE TABLE 语句。请参阅第 15.1.37 节，“清理表”。

如果您打算更改表的分区而不丢失数据，请使用 ALTER TABLE ... REORGANIZE PARTITION 而不是 DROP PARTITION。请参阅第 15.1.9 节，“ALTER TABLE 语句”，了解更多关于 REORGANIZE PARTITION 的信息。

现在执行一个SHOW CREATE TABLE 语句，您可以看到表的分区结构如何被更改：

mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)

当您使用 purchased 列值在 '1995-01-01' 和 '2004-12-31' 包含范围内插入新行时，那些行将被存储在分区 p3 中。您可以通过以下方式验证这一点：

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    1 | desk organiser | 2003-10-15 |
|   11 | pencil holder  | 1995-07-12 |
+------+----------------+------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)

从表中删除的行数因 ALTER TABLE ... DROP PARTITION 而不是DELETE 查询而未报告给服务器。

删除 LIST 分区使用与删除 RANGE 分区相同的 ALTER TABLE ... DROP PARTITION 语法。然而，有一个重要的差异在于，您无法再向表中插入任何值列表定义已删除分区中的值。请参阅第26.2.2节，“LIST 分区”，以获取示例。

要向之前分区的表中添加新的范围或列表分区，请使用 ALTER TABLE ... ADD PARTITION 语句。对于由 RANGE 分区定义的表，这可以用于在现有分区列表末尾添加一个新范围。假设您有一张名为 members 的已分区表，该表的定义如下：

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

假设成员的最小年龄是16岁。随着时间的推移，尤其是在2015年底时，您意识到必须很快准备好接受出生在2000年（及之后）的人员。您可以修改 members 表以适应新成员，如下所示：

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

对于使用范围分区的表，您可以使用 ADD PARTITION 来添加新的分区到分区列表的高端。尝试在现有分区之间或之前添加新分区会导致错误，如下所示：

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

您可以通过将第一个分区重新组织为两个新分区来解决这个问题，这些分区将它们之间的范围进行划分，如下所示：

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

使用 SHOW CREATE TABLE 可以看到 ALTER TABLE 语句已经产生了所需的效果：

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)

请参阅第15.1.9.1节，“ALTER TABLE 分区操作”。

您还可以使用 ALTER TABLE ... ADD PARTITION 来向由 LIST 分区定义的表中添加新分区。假设有一个名为 tt 的表，其定义如下：

CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

您可以在存储具有 data 列值 7、14 和 21 的行的新分区中添加数据，如下所示：

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

请记住，您不能向现有分区值列表中包含的任何值添加新的 LIST 分区。如果您尝试这样做，会出现错误：

mysql> ALTER TABLE tt ADD PARTITION 
     >     (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant »
                    in list partitioning

由于具有 data 列值 12 的所有行已经被分配到分区 p1 中，您不能在表 tt 上创建一个新分区，该分区包括 12 在其值列表中。要实现这一点，您可以删除 p1，然后添加 np 和一个新的 p1 分区，但这将导致在 p1 中存储的所有数据丢失，这通常不是您真正想要做的事情。另一种解决方案可能看起来是创建包含新分区的表副本，并使用 CREATE TABLE ... SELECT ... 将数据复制到其中，然后删除旧表并重命名新表，但这对于处理大量数据时可能非常耗时，并且在高可用性要求的情况下可能不可行。

您可以在单个 ALTER TABLE ... ADD PARTITION 语句中添加多个分区，如下所示：

CREATE TABLE employees (
  id INT NOT NULL,
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
  PARTITION p1 VALUES LESS THAN (1991),
  PARTITION p2 VALUES LESS THAN (1996),
  PARTITION p3 VALUES LESS THAN (2001),
  PARTITION p4 VALUES LESS THAN (2005)
);

ALTER TABLE employees ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2010),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

幸运的是，MySQL 的分区实现提供了重新定义分区而不丢失数据的方法。让我们首先看几个简单的例子，它们涉及 RANGE 分区。回想一下现在由以下 CREATE TABLE 语句定义的 members 表：

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)

假设您想要将所有代表出生年份在1960年之前的人员的行移动到一个单独的分区中。正如我们已经看到的，这不能使用 ALTER TABLE ... ADD PARTITION。然而，您可以使用另一个与 ALTER TABLE 相关的扩展来实现这一点：

ALTER TABLE members REORGANIZE PARTITION n0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);

实际上，这条命令将分区 p0 分割成两个新的分区 s0 和 s1。它还会根据在两条 PARTITION ... VALUES ... 子句中体现的规则，将原来的 p0 中存储的数据移动到新分区中，因此 s0 只包含那些满足 YEAR(dob) 小于 1960 的记录，而 s1 包含那些 YEAR(dob) 大于或等于 1960 但小于 1970 的行。

可以使用 REORGANIZE PARTITION 子句来合并相邻的分区。您可以将之前语句对 members 表的影响逆转，如下所示：

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);

在使用 REORGANIZE PARTITION 分割或合并分区时，不会丢失任何数据。在执行上述语句时，MySQL 将所有存储在分区 s0 和 s1 中的记录移动到分区 p0。

REORGANIZE PARTITION 的通用语法如下：

ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);

在这里，tbl_name 是分区表的名称，而 partition_list 是一个以逗号分隔的分区名列表，其中包含要更改的现有分区的名称。partition_definitions 是一个以逗号分隔的新分区定义列表，这些定义遵循与在 CREATE TABLE 中使用的相同规则。您不仅限于合并几个分区到一个，或者将一个分区分割成多个分区时使用 REORGANIZE PARTITION。例如，您可以将 members 表的所有四个分区重组为两个，如下所示：

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

您还可以使用 REORGANIZE PARTITION 与列表分区的表一起使用。让我们回到添加一个新分区到列表分区的 tt 表并因为新分区包含了现有分区值列表中已存在的值而失败的问题。我们可以通过添加一个只包含非冲突值的分区，然后将新分区和现有分区重新组织，以便存储在现有分区中的值现在被移动到新分区中：

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

当使用 ALTER TABLE ... REORGANIZE PARTITION 来重分区以 RANGE 或 LIST 分区的表时，请记住以下几点：

用于确定新分区方案的 PARTITION 选项受与在 CREATE TABLE 语句中使用的相同规则限制。

新建立的 RANGE 分区方案不能有重叠的范围；新建立的 LIST 分区方案不能有重叠的值集。
partition_definitions 列表中分区组合应该覆盖原来的分区名列表所涵盖的相同范围或值集总和。

例如，分区 p1 和 p2 一起覆盖了 members 表中 1980 年至 1999 年的年份。任何重组这些两个分区的操作都应该涵盖相同的年份范围总和。
对于以 RANGE 分区的表，您只能重组相邻的分区；您不能跳过范围分区。

例如，您不能使用一个开始于 ALTER TABLE members REORGANIZE PARTITION p0, p2 INTO ... 的语句来重组示例 members 表，因为 p0 覆盖了年份之前的 1970 年，而 p2 覆盖了从 1990 年至 1999 年的年份，包括在内，因此这些不是相邻分区。（您不能跳过分区 p1 在这种情况下。）
您无法使用 REORGANIZE PARTITION 更改表的分区类型（例如，您不能将 RANGE 分区更改为 HASH 分区或反之）。您也不能使用这个语句来更改分区表达式或列。要在不删除并重新创建表的情况下完成这两项任务，可以使用 ALTER TABLE ... PARTITION BY ...，如以下所示：
```
ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;
```

PREV HOME UP NEXT