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

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

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

26.3.1 RANGE 和 LIST 分区的管理

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

可以使用 ALTER TABLE 语句，并带有 DROP PARTITION 选项，从通过 RANGE 或 LIST 进行分区的表中删除分区。假设您已创建了一个通过范围进行分区的表，然后使用以下 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'（包括）的 purchased 列值将新行插入到更改后的表中时，这些行将存储在分区 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 分区使用的 ALTER TABLE ... DROP PARTITION 语法与用于删除 RANGE 分区的语法完全相同。但是，这在您之后使用该表时会产生一个重要区别：您不能再将任何具有已删除分区的值列表中包含的任何值的任何行插入到该表中。（有关示例，请参见第 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 表的问题，由于新分区的值已存在于现有分区的值列表中，因此失败。我们可以通过添加一个仅包含不冲突值的 partition 来处理此问题，然后重新组织新分区和现有分区，以便存储在现有分区中的值现在被移到新分区中。

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

PREV HOME UP NEXT