MySQL :: MySQL 8.4 参考手册 :: 26.3.1 RANGE 和 LIST 分区管理

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / RANGE 和 LIST 分区管理

26.3.1 RANGE 和 LIST 分区管理

RANGE 和 LIST 分区的添加和删除方式类似，因此在本节中我们将讨论这两种分区管理方式。有关操作通过散列或键进行分区的表的更多信息，请参见第 26.3.2 节，“HASH 和 KEY 分区管理”。

从通过 RANGE 或 LIST 进行分区的表中删除分区可以使用 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)

注意

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)

注意

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

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

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

如果您打算不丢失数据地更改表的分区，请改用 ALTER TABLE ... REORGANIZE PARTITION。有关 REORGANIZE PARTITION 的信息，请参见下文或第 15.1.9 节，“ALTER TABLE 语句”。

如果您现在执行 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 进行分区的表，这可用于在现有分区列表的末尾添加新的范围。假设您有一个包含您组织的成员资格数据的已分区表，该表定义如下

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 表以容纳在 2000 年到 2010 年期间出生的新成员

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 语句定义

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 分区的简单示例。回想一下 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 中使用的 partition_definitions 列表相同的规则。在使用 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 与按 LIST 分区的表一起使用。让我们回到向列表分区 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 列表中的分区组合应该总体上与 partition_list 中命名的组合分区覆盖相同的范围或值集。
例如，分区 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;
```