Documentation Home

13.1.7.1 ALTER TABLE 分区操作

分区相关子句 forALTER TABLE可以与分区表一起使用,用于重新分区、添加、删除、丢弃、合并和拆分分区,以及执行分区维护。

  • 只需在分区表上 使用partition_options with 子句即可根据. 该子句始终以 , 开头,并遵循适用于子句 for 的相同语法和其他规则 (有关更多详细信息,请参阅第 13.1.17 节,“CREATE TABLE 语句”),并且还可以用于分区现有表尚未分区。例如,考虑如下所示定义的(未分区)表: ALTER TABLEpartition_optionsPARTITION BYpartition_optionsCREATE TABLE

    CREATE TABLE t1 (
        id INT,
        year_col INT
    );

    这个表可以被分区HASH,使用id列作为分区键,通过这个语句分成8个分区:

    ALTER TABLE t1
        PARTITION BY HASH(id)
        PARTITIONS 8;

    MySQL 5.6.11 及更高版本支持 ALGORITHM带有 [SUB]PARTITION BY [LINEAR] KEY. ALGORITHM=1使服务器在计算行在分区中的位置时使用与 MySQL 5.1 相同的密钥散列函数; ALGORITHM=2意味着服务器采用 key-hashing 函数,默认情况下用于KEYMySQL 5.5 及更高版本中的新分区表。(使用 MySQL 5.5 及更高版本中采用的键散列函数创建的分区表不能由 MySQL 5.1 服务器使用。)不指定该选项与使用 . 具有相同的效果ALGORITHM=2。此选项主要用于升级或降级时使用[LINEAR] KEYMySQL 5.1 和更高版本的 MySQL 之间的分区表,或者用于创建由 MySQL 5.5 或更高版本服务器分区KEYLINEAR KEY在 MySQL 5.5 或更高版本服务器上分区的表,可以在 MySQL 5.1 服务器上使用。

    要升级KEY在 MySQL 5.1 中创建的分区表,首先执行 SHOW CREATE TABLE并记下显示的确切列和分区数。现在ALTER TABLE使用与语句中完全相同的列列表和分区数执行CREATE TABLE语句,同时 ALGORITHM=2紧跟 PARTITION BY关键字添加。LINEAR(如果它用于原始表定义,您还应该包括关键字。)此处显示了mysql客户端会话的示例:

    mysql> SHOW CREATE TABLE p\G
    *************************** 1. row ***************************
           Table: p
    Create Table: CREATE TABLE `p` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `cd` datetime NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50100 PARTITION BY LINEAR KEY (id)
    PARTITIONS 32 */
    1 row in set (0.00 sec)
    
    mysql> ALTER TABLE p PARTITION BY LINEAR KEY ALGORITHM=2 (id) PARTITIONS 32;
    Query OK, 0 rows affected (5.34 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> SHOW CREATE TABLE p\G
    *************************** 1. row ***************************
           Table: p
    Create Table: CREATE TABLE `p` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `cd` datetime NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50100 PARTITION BY LINEAR KEY (id)
    PARTITIONS 32 */
    1 row in set (0.00 sec)

    降级使用 MySQL 5.5 及更高版本中使用的默认密钥哈希创建的表以允许 MySQL 5.1 服务器使用它是类似的,除了在这种情况下您应该使用 ALGORITHM=1强制使用 MySQL 5.1 密钥哈希重建表的分区功能。建议您不要这样做,除非需要与 MySQL 5.1 服务器兼容,因为KEYMySQL 5.5 和更高版本中默认使用的改进哈希函数修复了旧实现中发现的许多问题。

    笔记

    通过 升级的表ALTER TABLE ... PARTITION BY ALGORITHM=2 [LINEAR] KEY ...不能再被 MySQL 5.1 服务器使用。(这样的表需要降级ALTER TABLE ... PARTITION BY ALGORITHM=1 [LINEAR] KEY ...才能被 MySQL 5.1 服务器再次使用。)

    使用ALTER TABLE ... PARTITION BY语句生成的表必须遵循与使用创建的表相同的规则CREATE TABLE ... PARTITION BY。这包括管理表可能具有的任何唯一键(包括任何主键)与分区表达式中使用的一个或多个列之间关系的规则,如 第 19.6.1 节“分区键、主键和唯一键”。指定分区数的CREATE TABLE ... PARTITION BY规则也适用于 ALTER TABLE ... PARTITION BY.

    partition_definitionfor 子句支持与语句 的ALTER TABLE ADD PARTITION同名子句相同的选项 CREATE TABLE。(有关语法和描述,请参阅 第 13.1.17 节,“CREATE TABLE 语句”。)假设您创建了如下所示的分区表:

    CREATE TABLE t1 (
        id INT,
        year_col INT
    )
    PARTITION BY RANGE (year_col) (
        PARTITION p0 VALUES LESS THAN (1991),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (1999)
    );

    您可以向该表添加一个新分区p3,用于存储小于以下值的值2002

    ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));

    DROP PARTITION可用于删除一个或多个RANGE分区LIST 。该语句不能与 HASH分区KEY 一起使用;相反,使用COALESCE PARTITION(见下文)。存储在 partition_names列表中命名的已删除分区中的所有数据都将被丢弃。例如,给定之前定义的表 ,您可以删除名为和 t1的分区,如下所示: p0p1

    ALTER TABLE t1 DROP PARTITION p0, p1;
    笔记

    DROP PARTITION不适用于使用NDB 存储引擎的表。请参阅 第 19.3.1 节,“RANGE 和 LIST 分区的管理”第 18.2.7 节,“NDB Cluster 的已知限制”

    ADD PARTITION目前DROP PARTITION不支持IF [NOT] EXISTS.

    支持分区表的重命名。您可以使用间接重命名单个分区ALTER TABLE ... REORGANIZE PARTITION;但是,此操作会复制分区的数据。

    要从选定分区中删除行,请使用该 TRUNCATE PARTITION选项。此选项采用一个或多个以逗号分隔的分区名称的列表。例如,考虑t1此处定义的表:

    CREATE TABLE t1 (
        id INT,
        year_col INT
    )
    PARTITION BY RANGE (year_col) (
        PARTITION p0 VALUES LESS THAN (1991),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (1999),
        PARTITION p3 VALUES LESS THAN (2003),
        PARTITION p4 VALUES LESS THAN (2007)
    );

    要从分区中删除所有行p0,请使用以下语句:

    ALTER TABLE t1 TRUNCATE PARTITION p0;

    刚刚显示的语句与以下语句具有相同的效果DELETE

    DELETE FROM t1 WHERE year_col < 1991;

    截断多个分区时,分区不必是连续的:这可以大大简化分区表上的删除操作,否则WHERE如果使用DELETE语句完成这些操作将需要非常复杂的条件。例如,此语句从分区中删除所有行 p1p3

    ALTER TABLE t1 TRUNCATE PARTITION p1, p3;

    此处显示等效DELETE 语句:

    DELETE FROM t1 WHERE
        (year_col >= 1991 AND year_col < 1995)
        OR
        (year_col >= 2003 AND year_col < 2007);

    如果您使用ALL关键字代替分区名称列表,则该语句将作用于所有表分区。

    TRUNCATE PARTITION仅删除行;它不会改变表本身或其任何分区的定义。

    笔记

    TRUNCATE PARTITION不适用于子分区。

    要验证行是否已删除,请 INFORMATION_SCHEMA.PARTITIONS使用如下查询检查表:

    SELECT PARTITION_NAME, TABLE_ROWS
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_NAME = 't1';

    TRUNCATE PARTITION仅支持使用 MyISAMInnoDBMEMORY存储引擎的分区表。它也适用于BLACKHOLE表格(但没有效果)。ARCHIVE表格 不支持它 。

    COALESCE PARTITION可以与按HASH或 分区的表一起使用,KEY以减少分区数 number。假设您已经创建t2了如下表:

    CREATE TABLE t2 (
        name VARCHAR (30),
        started DATE
    )
    PARTITION BY HASH( YEAR(started) )
    PARTITIONS 6;

    要将 所使用的分区数 t2从 6 减少到 4,请使用以下语句:

    ALTER TABLE t2 COALESCE PARTITION 2;

    最后一个 number分区中包含的数据被合并到其余分区中。在这种情况下,分区 4 和 5 将合并到前 4 个分区(分区编号为 0、1、2 和 3)。

    要更改分区表使用的部分而非全部分区,您可以使用REORGANIZE PARTITION. 可以通过多种方式使用此语句:

    • 将一组分区合并为一个分区。这是通过在 partition_names列表中命名多个分区并为 提供单个定义来完成的 partition_definition

    • 将现有分区拆分为多个分区。通过为单个分区命名 partition_names并提供多个 partition_definitions.

    • 要更改使用定义的分区子集的范围VALUES LESS THAN或使用定义的分区子集的值列表 VALUES IN

    • 此语句也可以在没有 选项的情况下用于使用分区自动分区以强制重新分配数据的表。(目前,只有 表以这种方式自动分区。)这在 NDB Cluster 中很有用,在将新的 NDB Cluster 数据节点在线添加到现有 NDB Cluster 之后,您希望将现有 NDB Cluster 表数据重新分配到新数据节点。在这种情况下,您应该调用带有选项的语句;换句话说,如下所示: partition_names INTO (partition_definitions)HASHNDBONLINE

      ALTER ONLINE TABLE table REORGANIZE PARTITION;

      您不能在联机表重组的同时执行其他 DDL,也就是说,在执行语句时不能发出其他 DDL 语句ALTER ONLINE TABLE ... REORGANIZE PARTITION。有关在线添加 NDB Cluster 数据节点的更多信息,请参阅 第 18.6.7 节,“在线添加 NDB Cluster 数据节点”

      ALTER ONLINE TABLE ... REORGANIZE PARTITION不适用于使用该MAX_ROWS选项创建的表,因为它使用MAX_ROWS 原始 CREATE TABLE语句中指定的常量值来确定所需的分区数,因此不会创建新分区。相反,您可以使用 增加此类表的最大行数;在这种情况下,不需要(如果执行会导致错误)。的值 必须大于原始语句中指定的值才能起作用。 ALTER ONLINE TABLE ... MAX_ROWS=rowsALTER ONLINE TABLE ... REORGANIZE PARTITIONrowsMAX_ROWSCREATE TABLE

      尝试在显式分区表上REORGANIZE PARTITION不使用该 选项会导致错误REORGANIZE PARTITION without parameters can only be used on auto-partitioned tables using HASH partitioningpartition_names INTO (partition_definitions)

    笔记

    对于没有显式命名的分区,MySQL 自动提供默认名称 p0p1p2等。子分区也是如此。

    ALTER TABLE ... REORGANIZE PARTITION 有关语句 的更多详细信息和示例 ,请参阅第 19.3.1 节,“RANGE 和 LIST 分区的管理”

  • 要与表交换表分区或子分区,请使用ALTER TABLE ... EXCHANGE PARTITION语句——即将分区或子分区中的任何现有行移动到非分区表,并将非分区表中的任何现有行移动到表分区或子分区。

    有关使用信息和示例,请参阅 第 19.3.3 节,“使用表交换分区和子分区”

  • 有几个选项提供分区维护和修复功能,类似于通过 and 等语句为非分区表实现的功能 CHECK TABLEREPAIR TABLE分区表也支持这些功能;有关更多信息,请参阅 第 13.7.2 节,“表维护语句”)。这些包括ANALYZE PARTITIONCHECK PARTITIONOPTIMIZE PARTITIONREBUILD PARTITIONREPAIR PARTITION。这些选项中的每一个都采用一个 子句,该partition_names子句由一个或多个分区名称组成,以逗号分隔。分区必须已经存在于要更改的表中。您也可以使用ALL关键字代替partition_names,在这种情况下,该语句作用于所有表分区。有关更多信息和示例,请参阅 第 19.3.4 节,“分区的维护”

    某些 MySQL 存储引擎(例如 InnoDB)不支持按分区优化。对于使用这种存储引擎的分区表,ALTER TABLE ... OPTIMIZE PARTITION导致整个表重建和分析,并发出适当的警告。(缺陷 #11751825,缺陷 #42822)

    要解决此问题,请改用语句 ALTER TABLE ... REBUILD PARTITIONALTER TABLE ... ANALYZE PARTITION

    对于未分区的表,不允许使用 ANALYZE PARTITIONCHECK PARTITIONOPTIMIZE PARTITION和选项。REPAIR PARTITION

  • REMOVE PARTITIONING使您能够删除表的分区而不影响表或其数据。此选项可以与其他 ALTER TABLE选项结合使用,例如用于添加、删除或重命名列或索引的选项。

  • 使用ENGINE选项 with ALTER TABLE更改表使用的存储引擎而不影响分区。

ALTER TABLE ... EXCHANGE PARTITIONALTER TABLE ... TRUNCATE PARTITION针对使用 MyISAM(或使用表级锁定的另一个存储引擎)的分区表运行时,只有那些实际读取的分区被锁定。(这不适用于使用采用行级锁定的存储引擎的分区表,例如InnoDB。)请参阅 第 19.6.4 节,“分区和锁定”

一个ALTER TABLE 语句可以包含一个PARTITION BYor REMOVE PARTITIONING子句作为对其他更改规范的补充,但PARTITION BYorREMOVE PARTITIONING子句必须在任何其他规范之后最后指定。

ADD PARTITIONDROP PARTITIONCOALESCE PARTITIONREORGANIZE PARTITION、和 选项不能与单个 中的其他更改规范组合ANALYZE PARTITION,因为刚刚列出的选项作用于各个分区。有关详细信息,请参阅 第 13.1.7.1 节,“ALTER TABLE 分区操作”CHECK PARTITIONREPAIR PARTITIONALTER TABLE

ALTER TABLE 给定语句 中只能使用以下任一选项的单个实例: PARTITION BY, ADD PARTITION, DROP PARTITION, TRUNCATE PARTITION, EXCHANGE PARTITION, REORGANIZE PARTITION, 或 COALESCE PARTITION, ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION, REMOVE PARTITIONING

例如,以下两个语句是无效的:

ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;

ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;

在第一种情况下,您可以 使用带有单个选项的单个语句同时分析分区p1p2表的表 ,该选项列出了要分析的两个分区,如下所示: t1ANALYZE PARTITION

ALTER TABLE t1 ANALYZE PARTITION p1, p2;

在第二种情况下,不可能同时对同一张表的不同分区执行 ANALYZECHECK 操作。相反,您必须发出两个单独的语句,如下所示:

ALTER TABLE t1 ANALYZE PARTITION p1;
ALTER TABLE t1 CHECK PARTITION p2;

ANALYZECHECKOPTIMIZEREBUILDREPAIRTRUNCATE 操作不支持子分区。