• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • RANGE和LIST分区的管理

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

    使用带有选项的语句,可以从按RANGE或进行分区的表中删除分区。假设您创建了一个按范围划分的表,然后使用以下和语句填充了10条记录:LISTALTER TABLEDROP PARTITIONCREATE TABLEINSERT

    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)
    

    有关更多信息,请参见“分区选择”。

    要删除名为的分区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}DROP PARTITION与一起ALGORITHM=INPLACE删除分区中存储的数据并删除分区。但是,DROP PARTITION使用ALGORITHM=COPYold_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 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。但是,这对以后使用表的影响有一个重要的区别:您不能再将具有定义删除分区的值列表中包含的任何值的行插入表中。(例如,请参见“列表分区”。)

    要将新的范围或列表分区添加到先前分区的表中,请使用以下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=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 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包含现有分区的值列表中已包含的任何值的新分区。如果尝试这样做,将导致错误:

    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都已分配给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 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=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 members REORGANIZE PARTITION n0 INTO (
        PARTITION s0 VALUES LESS THAN (1960),
        PARTITION s1 VALUES LESS THAN (1970)
    );
    

    实际上,此命令将分区p0分为两个新分区s0s1。它还会p0根据两个PARTITION ... VALUES ...子句中s0包含的规则将存储在其中的数据移到新分区中,以便仅包含YEAR(dob)小于1960的记录,并s1包含YEAR(dob)大于或等于1960但小于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是新分区定义的逗号分隔列表,该列表遵循与partition_definitions中使用的列表相同的规则CREATE TABLE。使用时,您不仅限于将多个分区合并为一个分区,或将一个分区拆分为多个分区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表中而失败的问题,因为新分区的值已经存在于现有分区之一的值列表中。我们可以通过添加一个仅包含无冲突值的分区,然后重新组织新分区和现有分区,以便将存储在现有分区中的值移至新分区来处理此问题:

    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

      例如,在本节中用作示例的表中,分区p1p2一起涵盖了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 members
          PARTITION BY HASH( YEAR(dob) )
      PARTITIONS 8;