18.3. 分区管理

优质

小牛编辑

138浏览

2023-12-01

18.3.1. RANGE和LIST分区的管理
18.3.2. HASH和KEY分区的管理
18.3.3. 分区维护
18.3.4. 获取关于分区的信息

MySQL 5.1 提供了许多修改分区表的方式。添加、删除、重新定义、合并或拆分已经存在的分区是可能的。所有这些操作都可以通过使用ALTER TABLE 命令的分区扩展来实现(关于语法的定义，请参见13.1.2节，“ALTER TABLE语法” )。也有获得分区表和分区信息的方式。在本节，我们讨论下面这些主题：

·按RANGE或LIST分区的表的分区管理的有关信息，请参见18.3.1节，“RANGE和LIST分区的管理”。

·关于HASH和KEY分区管理的讨论，请参见18.3.2节，“HASH和KEY分区的管理”。

·MySQL 5.1中提供的、获得关于分区表和分区信息的机制的讨论，请参见18.3.4节，“获取关于分区的信息” 。

·关于执行分区维护操作的讨论，请参见18.3.3节，“分区维护”。

注释：在MySQL 5.1中，一个分区表的所有分区都必须有子分区同样的名字，并且一旦表已经创建，再改变子分区是不可能的。

要点：当前，从5.1系列起建立的MySQL服务器就把“ALTER TABLE ... PARTITION BY ...”作为有效的语法，但是这个语句目前还不起作用。我们期望MySQL 5.1达到生产状态时，能够按照下面的描述实现该语句的功能。

要改变一个表的分区模式，只需要使用带有一个“partition_options”子句的ALTER TABLE 的命令。这个子句和与创建一个分区表的CREATE TABLE命令一同使用的子句有相同的语法，并且总是以关键字PARTITION BY 开头。例如，假设有一个使用下面CREATE TABLE语句建立的按照RANGE分区的表：

CREATE TABLE trb3 (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)

    )；

现在，要把这个表按照使用id列值作为键的基础，通过KEY分区把它重新分成两个分区，可以使用下面的语句：

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2；

这和先删除这个表、然后使用“CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2；”重新创建这个表具有同样的效果。

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 (NULL, '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 ...”来创建新的表，然后从旧表中把数据拷贝到新表中，再删除旧表，如有必要，最后重新命名新表。

18.3.2. HASH和KEY分区的管理

在改变分区设置方面，按照HASH分区或KEY分区的表彼此非常相似，但是它们又与按照RANGE或LIST分区的表在很多方面有差别。所以，本节只讨论按照HASH或KEY分区表的修改。关于添加和删除按照RANGE或LIST进行分区的表的分区的讨论，参见18.3.1节，“RANGE和LIST分区的管理”。

不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式，来从HASH或KEY分区的表中删除分区。但是，可以使用“ALTER TABLE ... COALESCE PARTITION”命令来合并HASH或KEY分区。例如，假定有一个包含顾客信息数据的表，它被分成了12个分区。该顾客表的定义如下：

CREATE TABLE clients (

    id INT,

    fname VARCHAR(30),

    lname VARCHAR(30),

    signed DATE

PARTITION BY HASH( MONTH(signed) )

PARTITIONS 12；

要减少分区的数量从12到6，执行下面的ALTER TABLE命令：

mysql> ALTER TABLE clients COALESCE PARTITION 6；

Query OK, 0 rows affected (0.02 sec)

对于按照HASH，KEY，LINEAR HASH，或LINEAR KEY分区的表， COALESCE能起到同样的作用。下面是一个类似于前面例子的另外一个例子，它们的区别只是在于表是按照LINEAR KEY 进行分区：

mysql> CREATE TABLE clients_lk (

    ->     id INT,

    ->     fname VARCHAR(30),

    ->     lname VARCHAR(30),

    ->     signed DATE

    -> )

    -> PARTITION BY LINEAR KEY(signed)

    -> PARTITIONS 12；

Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 6；

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

COALESCE不能用来增加分区的数量，如果你尝试这么做，结果会出现类似于下面的错误：

mysql> ALTER TABLE clients COALESCE PARTITION 18;

错误1478 (HY000): 不能移动所有分区，使用DROP TABLE代替

要增加顾客表的分区数量从12到18，使用“ALTER TABLE ... ADD PARTITION”,具体如下：

ALTER TABLE clients ADD PARTITION PARTITIONS 18；

注释：“ALTER TABLE ... REORGANIZE PARTITION”不能用于按照HASH或HASH分区的表。

18.3.3. 分区维护

注释：实际上，本节讨论的命令还没有在MySQL 5.1中实现，在这里提出的目的，是为了在5.1版投产前的开发周期期间，引出来自用户测试该软件的反馈意见。(换句话说，就是“请不要反馈这样的缺陷，说这些命令不起作用”）。随着MySQL5.1版开发的继续，这些信息很有可能发生变化。随着分区功能的实现和提高，我们将更新本节的内容。

MySQL 5.1中可以执行许多分区维护的任务。对于分区表，MySQL不支持命令CHECK TABLE，OPTIMIZE TABLE，ANALYZE TABLE，或REPAIR TABLE。作为替代，可以使用ALTER TABLE 的许多扩展来在一个或多个分区上直接地执行这些操作，如下面列出的那样：

·重建分区:这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

示例：

ALTER TABLE t1 REBUILD PARTITION (p0, p1)；

·优化分区：如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

示例：

ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)；

在一个给定的分区表上使用“OPTIMIZE PARTITION”等同于在那个分区上运行CHECK PARTITION，ANALYZE PARTITION，和REPAIR PARTITION。

·分析分区：读取并保存分区的键分布。

示例：

ALTER TABLE t1 ANALYZE PARTITION (p3)；

·修补分区：修补被破坏的分区。

示例：

ALTER TABLE t1 REPAIR PARTITION (p0,p1);

·检查分区： 可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。

示例：

ALTER TABLE trb3 CHECK PARTITION (p1)；

这个命令可以告诉你表t1的分区p1中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。

还可以使用mysqlcheck或myisamchk应用程序，在对表进行分区时所产生的、单独的MYI文件上进行操作，来完成这些任务。请参见8.7节，“mysqlcheck：表维护和维修程序”。（在pre-alpha编码中，这个功能已经可以使用）。

18.3.4. 获取关于分区的信息

本节讨论获取关于现有分区的信息。这个功能仍然处于计划阶段，所以现阶段在这里描述的，实际上是我们想要在MySQL 5.1中实现的一个概观。

如在本章中别处讨论的一样，在SHOW CREATE TABLE的输出中包含了用于创建分区表的PARTITION BY子句。例如：

mysql> SHOW CREATE TABLE trb3\G

*************************** 1. row ***************************

       Table: trb3

Create Table: CREATE TABLE `trb3` (

  `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 p2 VALUES LESS THAN (2000) ENGINE = MyISAM,

  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM

1 row in set (0.00 sec)

注释：当前，对于按HASH或KEY分区的表，PARTITIONS子句并不显示。 (Bug #14327)

SHOW TABLE STATUS用于分区表，它的输出与用于非分区表的输出相同，除了引擎（Engine）列总是包含'PARTITION'值。(关于这个命令的更多信息，参见13.5.4.18节，“SHOW TABLE STATUS语法”）。要获取单个分区的状态信息，我们计划实现一个SHOW PARTITION STATUS命令（请参见下面)。

计划用于分区表的、两个附加的SHOW命令是：

·SHOW PARTITIONS

这个命令预期其功能类似于SHOW TABLES和SHOW DATABASES，除了该命令将列出的是分区而不是表或数据库。这个命令的输出可能包含单个称为Partitions_in_tbl_name的列，其中tbl_name是分区表的名字。对于SHOW TABLES命令而言，如果一旦选择了一个数据库，随后该数据库将作为SHOW TABLES命令的默认数据库。但是由于SHOW PARTITIONS命令不可能用这样的方式来“选择”一个表，它很可能需要使用FROM子句，以便MySQL知道要显示的是哪个表的分区信息。

·SHOW PARTITION STATUS

这个命令将提供关于一个或多个分区的详细状态信息。它的输出很可能包含有与SHOW TABLE STATUS 的输出相同或类似的列，此外，还包括显示用于分区的数据和索引路径的附加列。这个命令可能支持LIKE和FROM子句，这样使得通过名字获得关于一个给定分区的信息，或者获得关于属于指定表或数据库的分区的信息，成为可能。

扩展INFORMATION_SCHEMA数据库的计划也在进行中，以便提供关于分区表和分区的信息。这个计划当前还处一个在非常早的阶段；随着补充的信息变得可用，以及任何新的、与分区有关的INFORMATION_SCHEMA扩展得以实现，我们将更新手册相关部分的内容。

这是MySQL参考手册的翻译版本，关于MySQL参考手册，请访问dev.mysql.com。原始参考手册为英文版，与英文版参考手册相比，本翻译版可能不是最新的。