RANGE和LIST分区的管理
范围分区和列表分区的添加和删除以类似的方式进行,因此在本节中我们将讨论两种分区的管理。有关使用按哈希或键分区的表的信息,请参见“HASH和KEY分区的管理”。
使用带有选项的语句,可以从按RANGE
或进行分区的表中删除分区。假设您创建了一个按范围划分的表,然后使用以下和语句填充了10条记录:LIST
ALTER TABLE
DROP PARTITION
CREATE TABLE
INSERT
mysql>CREATE TABLE tr (id INT,name VARCHAR(50), purchased DATE) ->PARTITION BY RANGE ( YEAR(purchased) ) ( ->PARTITION p0VALUES LESS THAN (1990), ->PARTITION p1VALUES LESS THAN (1995), ->PARTITION p2VALUES LESS THAN (2000), ->PARTITION p3VALUES LESS THAN (2005), ->PARTITION p4VALUES LESS THAN (2010), ->PARTITION p5VALUES LESS THAN (2015) -> ); Query OK, 0 rows affected (0.28 sec) mysql>INSERT INTO trVALUES -> (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 trPARTITION (p2); +------ +------------- +------------ + | id | name | purchased | +------ +------------- +------------ + | 2 | alarm clock | 1997-11-05 | | 10 | lava lamp | 1998-12-25 | +------ +------------- +------------ + 2 rows in set (0.00 sec)
有关更多信息,请参见“分区选择”。
要删除名为的分区p2
,请执行以下命令:
mysql>ALTER TABLE trDROP PARTITION p2; Query OK, 0 rows affected (0.03 sec)
注意该
NDBCLUSTER
存储引擎不支持ALTER TABLE ... DROP PARTITION
。但是,它确实支持ALTER TABLE
本章中描述的其他与分区相关的扩展。
重要的是要记住,删除分区时,还删除了该分区中存储的所有数据。通过重新运行上一个SELECT
查询,您可以看到是这种情况:
mysql>SELECT *FROM trWHERE purchased -> BETWEEN '1995-01-01' AND '1999-12-31'; Empty set (0.00 sec)
注意
DROP PARTITION
受本地分区就地API支持,并且可以与结合使用ALGORITHM={COPY|INPLACE}
。DROP PARTITION
与一起ALGORITHM=INPLACE
删除分区中存储的数据并删除分区。但是,DROP PARTITION
使用ALGORITHM=COPY
或old_alter_table=ON
重建分区表,并尝试将数据从删除的分区移动到具有兼容PARTITION ... VALUES
定义的另一个分区。无法删除的数据将被删除。
因此,在对该表DROP
执行之前,您必须具有该表的特权ALTER TABLE ... DROP PARTITION
。
如果希望在保留表定义及其分区方案的同时删除所有分区中的所有数据,请使用以下TRUNCATE TABLE
语句。(请参见“ TRUNCATE TABLE语句”。)
如果打算在不丢失数据的情况下更改表的分区,请ALTER TABLE ... REORGANIZE PARTITION
改用。参见下面或在第13.1.9,“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=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'
包含在内,这些行将存储在partition中p3
。您可以如下验证:
mysql>INSERT INTO trVALUES (11, 'pencil holder', '1995-07-12'); Query OK, 1 row affected (0.00 sec) mysql>SELECT *FROM trWHERE 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 trDROP PARTITION p3; Query OK, 0 rows affected (0.03 sec) mysql>SELECT *FROM trWHERE 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
。但是,这对以后使用表的影响有一个重要的区别:您不能再将具有定义删除分区的值列表中包含的任何值的行插入表中。(例如,请参见“列表分区”。)
要将新的范围或列表分区添加到先前分区的表中,请使用以下ALTER TABLE ... ADD PARTITION
语句。对于由进行分区的表RANGE
,可以将其添加到现有分区列表的末尾新范围。假设您有一个分区表,其中包含组织的成员资格数据,其定义如下:
CREATE TABLE members ( id INT, fname VARCHAR(25), lname VARCHAR(25), dob DATE )PARTITION BY RANGE ( YEAR(dob) ) (PARTITION p0VALUES LESS THAN (1980),PARTITION p1VALUES LESS THAN (1990),PARTITION p2VALUES LESS THAN (2000) );
进一步假设成员的最小年龄为16岁。随着日历的临近,到2015年底,您意识到您很快就会接受2000年(及以后)出生的成员。您可以修改members
表格以容纳2000年至2010年出生的新成员,如下所示:
ALTER TABLE membersADD PARTITION (PARTITION p3VALUES LESS THAN (2010));
对于按范围进行分区的表,您只能用于ADD PARTITION
将新分区添加到分区列表的高端。尝试以这种方式在现有分区之间或之前添加新分区会导致错误,如下所示:
mysql>ALTER TABLE members >ADD PARTITION ( >PARTITION nVALUES LESS THAN (1970)); ERROR 1463 (HY000): VALUES LESS THAN value must be strictly » increasing for each partition
您可以通过将第一个分区重组为两个新分区(将它们之间的范围划分)来解决此问题,如下所示:
ALTER TABLE membersREORGANIZE PARTITION p0INTO (PARTITION n0VALUES LESS THAN (1970),PARTITION n1VALUES 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=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)
另请参见“ ALTER TABLE分区操作”。
您还可以使用ALTER TABLE ... ADD PARTITION
将新分区添加到由分区的表中LIST
。假设tt
使用以下CREATE TABLE
语句定义了一个表:
CREATE TABLE tt ( id INT,data INT )PARTITION BY LIST (data ) (PARTITION p0VALUES IN (5, 10, 15),PARTITION p1VALUES IN (6, 12, 18) );
可以添加在其中存储具有行一个新的分区data
的列值7
,14
和21
如下所示:
ALTER TABLE ttADD PARTITION (PARTITION p2VALUES IN (7, 14, 21));
请记住,您不能添加LIST
包含现有分区的值列表中已包含的任何值的新分区。如果尝试这样做,将导致错误:
mysql>ALTER TABLE ttADD PARTITION > (PARTITION npVALUES IN (4, 8, 12)); ERROR 1465 (HY000): Multiple definition of same constant » in list partitioning
因为任何具有data
列值的行12
都已分配给partition 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 p1VALUES LESS THAN (1991),PARTITION p2VALUES LESS THAN (1996),PARTITION p3VALUES LESS THAN (2001),PARTITION p4VALUES LESS THAN (2005) );ALTER TABLE employeesADD PARTITION (PARTITION p5VALUES LESS THAN (2010),PARTITION p6VALUES 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=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)
假设您希望将代年之前出生的成员的所有行移动到单独的分区中。正如我们已经看到的,这不能使用来完成ALTER TABLE ... ADD PARTITION
。但是,您可以使用另一个与分区相关的扩展ALTER TABLE
来完成此操作:
ALTER TABLE membersREORGANIZE PARTITION n0INTO (PARTITION s0VALUES LESS THAN (1960),PARTITION s1VALUES LESS THAN (1970) );
实际上,此命令将分区p0
分为两个新分区s0
和s1
。它还会p0
根据两个PARTITION ... VALUES ...
子句中s0
包含的规则将存储在其中的数据移到新分区中,以便仅包含YEAR(dob)
小于1960的记录,并s1
包含YEAR(dob)
大于或等于1960但小于1960的行。比1970年。
甲REORGANIZE PARTITION
条款还可以使用用于合并相邻的分区。您可以在members
表上反转上一条语句的效果,如下所示:
ALTER TABLE membersREORGANIZE PARTITION s0,s1INTO (PARTITION p0VALUES LESS THAN (1970) );
使用分割或合并分区时,不会丢失任何数据REORGANIZE PARTITION
。在执行上面的语句中,MySQL将所有的已存储在分区中的记录s0
,并s1
为分区p0
。
的一般语法REORGANIZE PARTITION
如下所示:
ALTER TABLE tbl_nameREORGANIZE PARTITION partition_listINTO (partition_definitions);
在这里,tbl_name
是分区表的名称,并且partition_list
是一个或多个要更改的现有分区的名称的逗号分隔列表。partition_definitions
是新分区定义的逗号分隔列表,该列表遵循与partition_definitions
中使用的列表相同的规则CREATE TABLE
。使用时,您不仅限于将多个分区合并为一个分区,或将一个分区拆分为多个分区REORGANIZE PARTITION
。例如,您可以将members
表的所有四个分区重组为两个,如下所示:
ALTER TABLE membersREORGANIZE PARTITION p0,p1,p2,p3INTO (PARTITION m0VALUES LESS THAN (1980),PARTITION m1VALUES LESS THAN (2000) );
您还可以将REORGANIZE PARTITION
其用于按分区的表LIST
。让我们回到将新分区添加到列表分区tt
表中而失败的问题,因为新分区的值已经存在于现有分区之一的值列表中。我们可以通过添加一个仅包含无冲突值的分区,然后重新组织新分区和现有分区,以便将存储在现有分区中的值移至新分区来处理此问题:
ALTER TABLE ttADD PARTITION (PARTITION npVALUES IN (4, 8));ALTER TABLE ttREORGANIZE PARTITION p1,npINTO (PARTITION p1VALUES IN (6, 18),PARTITION npVALUES in (4, 8, 12) );
以下是ALTER TABLE ... REORGANIZE PARTITION
用于重新分区由RANGE
或分区的表时要记住的一些关键点LIST
:
PARTITION
用于确定新分区方案的选项应遵循与CREATE TABLE
语句所使用的规则相同的规则。新的
RANGE
分区方案不能有任何重叠范围。一个新的LIST
分区方案不能有任何重叠的值集。partition_definitions
列表中的分区组合应与清单中命名的组合分区具有相同的范围或整体值集partition_list
。例如,在本节中用作示例的表中,分区
p1
和p2
一起涵盖了1980年至1999年members
。对这两个分区的任何重组应涵盖总体相同的年份范围。对于由分区的表
RANGE
,您只能重组相邻的分区。您不能跳过范围分区。例如,您不能
members
使用以开头的语句来重组示例表,ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...
因为p0
涵盖了1970年之前p2
的年份以及1990年至1999年(含两端)的年份,因此它们不是相邻的分区。(p1
在这种情况下,您不能跳过分区。)您不能用于
REORGANIZE PARTITION
更改表使用的分区类型(例如,不能将RANGE
分区更改为HASH
分区或反向分区)。您也不能使用此语句更改分区表达式或列。要完成这两项任务中的任何一项而无需删除并重新创建表,可以使用ALTER TABLE ... PARTITION BY ...
,如下所示:ALTER TABLE membersPARTITION BY HASH ( YEAR(dob) )PARTITIONS 8;