分区相关子句 forALTER
TABLE
可以与分区表一起使用,用于重新分区、添加、删除、丢弃、合并和拆分分区,以及执行分区维护。
只需在分区表上 使用
partition_options
with 子句即可根据. 该子句始终以 , 开头,并遵循适用于子句 for 的相同语法和其他规则 (有关更多详细信息,请参阅第 13.1.17 节,“CREATE TABLE 语句”),并且还可以用于分区现有表尚未分区。例如,考虑如下所示定义的(未分区)表:ALTER TABLE
partition_options
PARTITION BY
partition_options
CREATE 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 函数,默认情况下用于KEY
MySQL 5.5 及更高版本中的新分区表。(使用 MySQL 5.5 及更高版本中采用的键散列函数创建的分区表不能由 MySQL 5.1 服务器使用。)不指定该选项与使用 . 具有相同的效果ALGORITHM=2
。此选项主要用于升级或降级时使用[LINEAR] KEY
MySQL 5.1 和更高版本的 MySQL 之间的分区表,或者用于创建由 MySQL 5.5 或更高版本服务器分区KEY
或LINEAR 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 服务器兼容,因为KEY
MySQL 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_definition
for 子句支持与语句 的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
的分区,如下所示:p0
p1
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
语句完成这些操作将需要非常复杂的条件。例如,此语句从分区中删除所有行p1
并p3
: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
仅支持使用MyISAM
、InnoDB
或MEMORY
存储引擎的分区表。它也适用于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
)HASH
NDB
ONLINE
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=
rows
ALTER ONLINE TABLE ... REORGANIZE PARTITION
rows
MAX_ROWS
CREATE TABLE
尝试在显式分区表上
REORGANIZE PARTITION
不使用该 选项会导致错误REORGANIZE PARTITION without parameters can only be used on auto-partitioned tables using HASH partitioning。partition_names
INTO (partition_definitions
)
笔记对于没有显式命名的分区,MySQL 自动提供默认名称
p0
、p1
、p2
等。子分区也是如此。ALTER TABLE ... REORGANIZE PARTITION
有关语句 的更多详细信息和示例 ,请参阅第 19.3.1 节,“RANGE 和 LIST 分区的管理”。要与表交换表分区或子分区,请使用
ALTER TABLE ... EXCHANGE PARTITION
语句——即将分区或子分区中的任何现有行移动到非分区表,并将非分区表中的任何现有行移动到表分区或子分区。有关使用信息和示例,请参阅 第 19.3.3 节,“使用表交换分区和子分区”。
有几个选项提供分区维护和修复功能,类似于通过 and 等语句为非分区表实现的功能
CHECK TABLE
(REPAIR TABLE
分区表也支持这些功能;有关更多信息,请参阅 第 13.7.2 节,“表维护语句”)。这些包括ANALYZE PARTITION
、CHECK PARTITION
、OPTIMIZE PARTITION
、REBUILD PARTITION
和REPAIR PARTITION
。这些选项中的每一个都采用一个 子句,该partition_names
子句由一个或多个分区名称组成,以逗号分隔。分区必须已经存在于要更改的表中。您也可以使用ALL
关键字代替partition_names
,在这种情况下,该语句作用于所有表分区。有关更多信息和示例,请参阅 第 19.3.4 节,“分区的维护”。某些 MySQL 存储引擎(例如
InnoDB
)不支持按分区优化。对于使用这种存储引擎的分区表,ALTER TABLE ... OPTIMIZE PARTITION
导致整个表重建和分析,并发出适当的警告。(缺陷 #11751825,缺陷 #42822)要解决此问题,请改用语句
ALTER TABLE ... REBUILD PARTITION
和ALTER TABLE ... ANALYZE PARTITION
。对于未分区的表,不允许使用
ANALYZE PARTITION
、CHECK PARTITION
、OPTIMIZE PARTITION
和选项。REPAIR PARTITION
REMOVE PARTITIONING
使您能够删除表的分区而不影响表或其数据。此选项可以与其他ALTER TABLE
选项结合使用,例如用于添加、删除或重命名列或索引的选项。使用
ENGINE
选项 withALTER TABLE
更改表使用的存储引擎而不影响分区。
当ALTER TABLE ... EXCHANGE PARTITION
或
ALTER TABLE ... TRUNCATE PARTITION
针对使用
MyISAM
(或使用表级锁定的另一个存储引擎)的分区表运行时,只有那些实际读取的分区被锁定。(这不适用于使用采用行级锁定的存储引擎的分区表,例如InnoDB
。)请参阅
第 19.6.4 节,“分区和锁定”。
一个ALTER TABLE
语句可以包含一个PARTITION BY
or
REMOVE PARTITIONING
子句作为对其他更改规范的补充,但PARTITION
BY
orREMOVE PARTITIONING
子句必须在任何其他规范之后最后指定。
、ADD PARTITION
、DROP
PARTITION
、COALESCE PARTITION
、
REORGANIZE PARTITION
、和
选项不能与单个 中的其他更改规范组合ANALYZE
PARTITION
,因为刚刚列出的选项作用于各个分区。有关详细信息,请参阅
第 13.1.7.1 节,“ALTER TABLE 分区操作”。
CHECK PARTITION
REPAIR PARTITION
ALTER
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;
在第一种情况下,您可以
使用带有单个选项的单个语句同时分析分区p1
和p2
表的表
,该选项列出了要分析的两个分区,如下所示:
t1
ANALYZE PARTITION
ALTER TABLE t1 ANALYZE PARTITION p1, p2;
在第二种情况下,不可能同时对同一张表的不同分区执行
ANALYZE
和CHECK
操作。相反,您必须发出两个单独的语句,如下所示:
ALTER TABLE t1 ANALYZE PARTITION p1;
ALTER TABLE t1 CHECK PARTITION p2;
ANALYZE
、CHECK
、
OPTIMIZE
、REBUILD
、
REPAIR
和TRUNCATE
操作不支持子分区。