18.3.1. RANGE和LIST分区的管理

第18章：分区 / 18.3. 分区管理 / 18.3.1. RANGE和LIST分区的管理

关于如何添加和删除分区的处理，RANGE和LIST分区非常相似。基于这个原因，我们在本节讨论这两种分区的管理。关于HASH和KEY分区管理的信息，请参见18.3.2节，“HASH和KEY分区的管理”。删除一个RANGE或LIST分区比增加一个分区要更加简单易懂，所以我们先讨论前者。

从一个按照RANGE或LIST分区的表中删除一个分区，可以使用带一个DROP PARTITION子句的ALTER TABLE命令来实现。这里有一个非常基本的例子，假设已经使用下面的CREATE TABLE和INSERT语句创建了一个按照RANGE分区的表，并且已经插入了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)

    ->     );

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tr VALUES

    ->     (1, 'desk organiser', '2003-10-15'),

    ->     (2, 'CD player', '1993-11-05'),

    ->     (3, 'TV set', '1996-03-10'),

    ->     (4, 'bookcase', '1982-01-10'),

    ->     (5, 'exercise bike', '2004-05-09'),

    ->     (6, 'sofa', '1987-06-05'),

    ->     (7, 'popcorn maker', '2001-11-22'),

    ->     (8, 'aquarium', '1992-08-04'),

    ->     (9, 'study desk', '1984-09-16'),

    ->     (10, 'lava lamp', '1998-12-25');

Query OK, 10 rows affected (0.01 sec)

可以通过使用下面的命令查看那些记录已经插入到了分区p2中：

mysql> SELECT * FROM tr

    -> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';

+------+-----------+------------+

| id   | name      | purchased  |

+------+-----------+------------+

|    3 | TV set    | 1996-03-10 |

|   10 | lava lamp | 1998-12-25 |

+------+-----------+------------+

2 rows in set (0.00 sec)

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

mysql> ALTER TABLE tr DROP PARTITION p2;

Query OK, 0 rows affected (0.03 sec)

记住下面一点非常重要：当删除了一个分区，也同时删除了该分区中所有的数据。可以通过重新运行前面的SELECT查询来验证这一点：

mysql> SELECT * FROM tr WHERE purchased

    -> BETWEEN '1995-01-01' AND '1999-12-31';

Empty set (0.00 sec)

如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用TRUNCATE TABLE命令。（请参见13.2.9节，“TRUNCATE语法”）。

如果希望改变表的分区而又不丢失数据，使用“ALTER TABLE ... REORGANIZE PARTITION”语句。参见下面的内容，或者在13.1.2节，“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=MyISAM DEFAULT CHARSET=latin1

PARTITION BY RANGE (YEAR(purchased)) (

  PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,

  PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,

  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM

1 row in set (0.01 sec)

如果插入购买日期列的值在'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  |

+------+----------------+------------+

|   11 | pencil holder  | 1995-07-12 |

|    1 | desk organiser | 2003-10-15 |

|    5 | exercise bike  | 2004-05-09 |

|    7 | popcorn maker  | 2001-11-22 |

+------+----------------+------------+

4 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”语法。但是，在对其后使用这个表的影响方面，还是有重大的区别：在这个表中，再也不能插入这么一些行，这些行的列值包含在定义已经删除了的分区的值列表中 (有关示例，请参见18.2.2节，“LIST分区” ）。

要增加一个新的RANGE或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 (1970),

    PARTITION p1 VALUES LESS THAN (1980),

    PARTITION p2 VALUES LESS THAN (1990)

);

进一步假设成员的最小年纪是16岁。随着日历接近2005年年底，你会认识到不久将要接纳1990年（以及以后年份）出生的成员。可以按照下面的方式，修改成员表来容纳出生在1990－1999年之间的成员：

ALTER TABLE ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

要点：对于通过RANGE分区的表，只可以使用ADD PARTITION添加新的分区到分区列表的高端。设法通过这种方式在现有分区的前面或之间增加一个新的分区，将会导致下面的一个错误：

mysql> ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (1960));

错误1463 (HY000): 对每个分区，VALUES LESS THAN 值必须严格增长

采用一个类似的方式，可以增加新的分区到已经通过LIST分区的表。例如，假定有如下定义的一个表：

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)

)；

可以通过下面的方法添加一个新的分区，用来保存拥有数据列值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));

错误1465 (HY000): 在LIST分区中，同一个常数的多次定义

因为带有数据列值12的任何行都已经分配给了分区p1，所以不能在表tt上再创建一个其值列表包括12的新分区。为了实现这一点，可以先删除分区p1，添加分区np，然后使用修正后的定义添加一个新的分区p1。但是，正如我们前面讨论过的，这将导致保存在分区p1中的所有数据丢失——而这往往并不是你所真正想要做的。另外一种解决方法可能是，建立一个带有新分区的表的副本，然后使用“CREATE TABLE ... SELECT ...”把数据拷贝到该新表中，然后删除旧表，重新命名新表，但是，当需要处理大量的数据时，这可能是非常耗时的。在需要高可用性的场合，这也可能是不可行的。

幸运地是，MySQL 的分区实现提供了在不丢失数据的条件下重新定义分区的方式。让我们首先看两个涉及到RANGE分区的简单例子。回想一下现在定义如下的成员表：

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=MyISAM DEFAULT CHARSET=latin1

PARTITION BY RANGE (YEAR(dob)) (

  PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,

  PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,

  PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.

  PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM

假定想要把表示出生在1960年前成员的所有行移入到一个分开的分区中。正如我们前面看到的，不能通过使用“ALTER TABLE ... ADD PARTITION”来实现这一点。但是，要实现这一点，可以使用ALTER TABLE上的另外一个与分区有关的扩展，具体实现如下：

ALTER TABLE members REORGANIZE PARTITION p0 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语句也可以用来合并相邻的分区。可以使用如下的语句恢复成员表到它以前的分区：

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 相同的规则 (请参见13.1.5节，“CREATE TABLE语法”)。应当注意到，在把多少个分区合并到一个分区或把一个分区拆分成多少个分区方面，没有限制。例如，可以重新组织成员表的四个分区成两个分区，具体实现如下：

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (

    PARTITION m0 VALUES LESS THAN (1980),

    PARTITION m1 VALUES LESS THAN (2000)

)；

同样，对于按LIST分区的表，也可以使用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中确定分区模式的PARTITION子句相同的规则。

最重要的是，应该记住：新分区模式不能有任何重叠的区间（适用于按照RANGE分区的表）或值集合（适用于重新组织按照LIST分区的表）。

· partition_definitions 列表中分区的合集应该与在partition_list 中命名分区的合集占有相同的区间或值集合。

例如，在本节中用作例子的成员表中，分区p1和p2总共覆盖了1980到1999的这些年。因此，对这两个分区的重新组织都应该覆盖相同范围的年份。

· 对于按照RANGE分区的表，只能重新组织相邻的分区；不能跳过RANGE分区。

例如，不能使用以“ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...”开头的语句，来重新组织本节中用作例子的成员表。因为，p0覆盖了1970年以前的年份，而p2覆盖了从1990到1999（包括1990和1999）之间的年份，因而这两个分区不是相邻的分区。

· 不能使用REORGANIZE PARTITION来改变表的分区类型；也就是说，例如，不能把RANGE分区变为HASH分区，反之亦然。也不能使用该命令来改变分区表达式或列。如果想在不删除和重建表的条件下实现这两个任务，可以使用“ALTER TABLE ... PARTITION BY ....”，例如：

·                ALTER TABLE members

·                    PARTITION BY HASH(YEAR(dob))

·                    PARTITIONS 8；

注释：在MySQL 5.1发布前的版本中，“ALTER TABLE ... PARTITION BY ...”还没有实现。作为替代，要么使用先删除表，然后使用想要的分区重建表，或者——如果需要保留已经存储在表中的数据——可以使用“CREATE TABLE ... SELECT ...”来创建新的表，然后从旧表中把数据拷贝到新表中，再删除旧表，如有必要，最后重新命名新表。