22.3.1 RANGE 和 LIST 分区的 Management

范围分区和列 table 分区的添加和删除以类似的方式进行,因此在本节中我们将讨论两种分区的 Management。有关使用按哈希或键分区的 table 的信息,请参见第 22.3.2 节“ HASH 和密钥分区的 Management”

可以使用带有DROP PARTITION选项的ALTER TABLE语句从由RANGELIST分区的 table 中删除分区。假设您创建了一个按范围划分的 table,然后使用以下CREATE TABLEINSERT语句填充了 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)

有关更多信息,请参见第 22.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)

因此,您必须具有 table 的DROP特权,然后才能对该 table 执行ALTER TABLE ... DROP PARTITION

如果希望在保留 table 定义及其分区方案的同时删除所有分区中的所有数据,请使用TRUNCATE TABLE语句。 (请参阅第 13.1.34 节“ TRUNCATE TABLE 语句”。)

如果要更改 table 的分区而又不丢失数据,请改用ALTER TABLE ... REORGANIZE PARTITION。有关REORGANIZE PARTITION的信息,请参见下面或第 13.1.8 节“ ALTER TABLE 语句”中的信息。

如果现在执行显示创建 table语句,则可以查看 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=latin1
/*!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'之间(包括'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而从 table 中删除的行数,等效的DELETE查询不会报告该行数。

删除LIST分区所使用的ALTER TABLE ... DROP PARTITION语法与删除RANGE分区所使用的语法完全相同。但是,这对以后使用 table 的影响有一个重要区别:您不能再将具有定义删除分区的值列 table 中包含的任何值的行插入 table 中。 (例如,请参见第 22.2.2 节“列 table 分区”。)

要将新的范围或列 table 分区添加到以前的分区 table 中,请使用ALTER TABLE ... ADD PARTITION语句。对于被RANGE分区的 table,可以将其添加到现有分区列 table 末尾的新范围。假设您有一个分区 table,其中包含组织的成员资格数据,其定义如下:

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 岁。随着 calendar 的临近,到 2015 年底,您意识到您很快就会接受 2000 年(及以后)出生的成员。您可以修改memberstable,以容纳 2000 年至 2010 年出生的新成员,如下所示:

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

对于按范围进行分区的 table,您可以使用ADD PARTITION将新分区仅添加到分区列 table 的高端。尝试以这种方式在现有分区之间或之前添加新分区会导致错误,如下所示:

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

使用显示创建 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=latin1
/*!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)

另请参见第 13.1.8.1 节,“ ALTER TABLE 分区操作”

您还可以使用ALTER TABLE ... ADD PARTITION将新分区添加到由LIST分区的 table 中。假设使用以下CREATE TABLE语句定义了 tablett

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列值71421的行,如下所示:

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

请记住,您不能添加一个新的LIST分区,该分区包含现有分区的值列 table 中已包含的所有值。如果尝试这样做,将导致错误:

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,所以您不能在 tablett上创建一个新分区,该 table 的值列 table 中包含12。为此,您可以先删除p1,然后添加np,然后添加具有修改后的定义的新p1。但是,如前所述,这将导致p1中存储的所有数据丢失—通常情况下,这并不是您 true 想要的。另一种解决方案似乎是使用新分区复制 table,然后使用创建 table...选择...将数据复制到 table 中,然后删除旧 table 并重命名新 table,但这在处理 table 时可能非常耗时。大量数据。在要求高可用性的情况下,这可能也不可行。

您可以在一个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分区的简单示例。调用现在定义的memberstable,如下所示:

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=latin1
/*!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)

假设您希望将代 table1960 年之前出生的成员的所有行移动到单独的分区中。正如我们已经看到的,使用ALTER TABLE ...添加分区无法完成此操作。但是,您可以使用另一个与分区相关的 extensionsALTER TABLE来完成此操作:

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

实际上,此命令将分区p0分为两个新分区s0s1。还会根据两个PARTITION ... VALUES ...子句中包含的规则将p0中存储的数据移到新分区中,以便s0仅包含YEAR(dob)小于 1960 的记录,而s1包含YEAR(dob)大于的行。等于 1960 但小于 1970.

REORGANIZE PARTITION子句也可用于合并相邻分区。您可以反转上一条语句对memberstable 的影响,如下所示:

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

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

REORGANIZE PARTITION的一般语法如下所示:

ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);

在这里,* tbl_name 是分区 table 的名称, partition_list *是一个或多个要更改的现有分区的名称的逗号分隔列 table。 * partition_definitions *是用逗号分隔的新分区定义列 table,遵循的规则与CREATE TABLE中使用的partition_definitions *列 table 相同。使用REORGANIZE PARTITION时,您不仅限于将多个分区合并为一个,也可以将一个分区拆分为多个。例如,您可以将memberstable 的所有四个分区重组为两个,如下所示:

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

您还可以对被LIST分区的 table 使用REORGANIZE PARTITION。让我们回到将新分区添加到列 table 分区的tttable 而失败的问题,因为新分区的值已经存在于现有分区之一的值列 table 中。我们可以通过添加一个仅包含无冲突值的分区,然后重新组织新分区和现有分区,以便将存储在现有分区中的值移至新分区来处理此问题:

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重新分区被RANGELIST分区的 table 时,请牢记以下一些关键点:

  • 用于确定新分区方案的PARTITION选项应遵循与CREATE TABLE语句所使用的规则相同的规则。

新的RANGE分区方案不能有任何重叠范围。新的LIST分区方案不能包含任何重叠的值集。

    • partition_definitions 列 table 中的分区组合应与 partition_list *中命名的组合分区具有相同的范围或整体值集。

例如,分区p1p2一起涵盖了memberstable 中从 1980 年到 1999 年的时间,此 table 作为本节的示例。对这两个分区的任何重组应涵盖总体相同的年份范围。

  • 对于由RANGE分区的 table,您只能重组相邻的分区;您不能跳过范围分区。

例如,您无法使用以ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...开头的语句来重组示例memberstable,因为p0涵盖了 1970 年之前的年份,而p2涵盖了 1990 年至 1999 年(含两端)的年份,因此它们不是相邻的分区。 (在这种情况下,您不能跳过分区p1.)

  • 您不能使用REORGANIZE PARTITION更改 table 使用的分区类型(例如,不能将RANGE分区更改为HASH分区或相反)。您也不能使用此语句更改分区 table 达式或列。要完成这两项任务中的任何一个而无需删除并重新创建 table,可以使用ALTER TABLE ...分区依据...,如下所示:
ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;