Documentation Home

14.13.1 在线DDL操作

本节的以下主题下提供了 DDL 操作的在线支持详细信息、语法示例和使用说明。

索引操作

下表概述了对索引操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参阅 语法和使用说明

表 14.8 索引操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
创建或添加二级索引 是的 是的
删除索引 是的 是的 是的
添加FULLTEXT索引 是的* 不*
更改索引类型 是的 是的 是的

语法和使用说明
  • 创建或添加二级索引

    CREATE INDEX name ON table (col_list);
    ALTER TABLE tbl_name ADD INDEX name (col_list);

    在创建索引时,该表仍可用于读写操作。该 CREATE INDEX语句仅在访问表的所有事务都完成后才结束,因此索引的初始状态反映了表的最新内容。

    在线 DDL 支持添加二级索引意味着您通常可以通过创建不带二级索引的表,然后在加载数据后添加二级索引来加快创建和加载表及关联索引的整个过程。

    CREATE INDEX新创建的二级索引仅包含or ALTER TABLE语句执行完毕 时表中已提交的数据 。它不包含任何未提交的值、旧版本的值或标记为删除但尚未从旧索引中删除的值。

    如果服务器在创建二级索引时退出,则在恢复时,MySQL 会删除任何部分创建的索引。您必须重新运行ALTER TABLE orCREATE INDEX语句。

    一些因素会影响此操作的性能、空间使用和语义。有关详细信息,请参阅 第 14.13.6 节,“在线 DDL 限制”

  • 删除索引

    DROP INDEX name ON table;
    ALTER TABLE tbl_name DROP INDEX name;

    在删除索引时,该表仍可用于读取和写入操作。该 DROP INDEX语句仅在访问表的所有事务都完成后才结束,因此索引的初始状态反映了表的最新内容。

  • 添加FULLTEXT索引

    CREATE FULLTEXT INDEX name ON table(column);

    FULLTEXT如果没有用户定义的 FTS_DOC_ID列, 添加第一个索引会重建表。FULLTEXT可以在不重建表的情况下添加 额外 的索引。

  • 更改索引类型 ( USING {BTREE | HASH})

    ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INPLACE;

主键操作

下表概述了对主键操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。请参阅 语法和使用说明

表 14.9 对主键操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
添加主键 是的* 是的* 是的
删除主键 是的
删除主键并添加另一个 是的 是的 是的

语法和使用说明
  • 添加主键

    ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

    就地重建表。数据被大量重组,使其成为一项昂贵的操作。 ALGORITHM=INPLACE如果必须将列转换为 ,则在某些情况下不允许使用 NOT NULL.

    重组 聚集索引 总是需要复制表数据。因此,最好在创建表时 定义主键ALTER TABLE ... ADD PRIMARY KEY,而不是稍后发布。

    当你创建一个UNIQUEPRIMARY KEY索引时,MySQL 必须做一些额外的工作。对于UNIQUE索引,MySQL 检查表中是否包含键的重复值。对于PRIMARY KEY索引,MySQL 还会检查没有任何PRIMARY KEY 列包含NULL.

    当您使用该 ALGORITHM=COPY子句添加主键时,MySQL 会将 NULL关联列中的值转换为默认值:数字为 0,基于字符的列和 BLOB 为空字符串,0000-00-00 00:00:00 为DATETIME. 这是 Oracle 建议您不要依赖的非标准行为。仅当设置包含或 标志ALGORITHM=INPLACE时才允许 使用添加主键;当 设置为严格时, 允许,但如果请求的主键列包含,语句仍然会失败SQL_MODEstrict_trans_tablesstrict_all_tablesSQL_MODEALGORITHM=INPLACENULL值。该 ALGORITHM=INPLACE行为更符合标准。

    如果您创建的表没有主键, InnoDB请为您选择一个,它可以是列UNIQUE上定义的第一个键 ,也可以是NOT NULL系统生成的键。为避免不确定性和额外隐藏列的潜在空间需求,请将 PRIMARY KEY子句指定为语句的一部分 CREATE TABLE

    MySQL 通过将现有数据从原始表复制到具有所需索引结构的临时表来创建新的聚集索引。一旦数据被完全复制到临时表中,原始表将被重命名为不同的临时表名称。包含新聚簇索引的临时表被重命名为原始表的名称,并且原始表从数据库中删除。

    适用于二级索引操作的在线性能增强不适用于主键索引。InnoDB 表的行存储在 基于 主键组织的聚簇索引中,形成一些数据库系统所谓的索引组织表。由于表结构与主键紧密相关,重新定义主键仍然需要复制数据。

    当对主键的操作使用 ALGORITHM=INPLACE时,即使数据仍然被复制,它也比使用更高效, ALGORITHM=COPY因为:

    • 不需要撤消日志记录或关联的重做日志记录ALGORITHM=INPLACE。这些操作增加了使用 ALGORITHM=COPY.

    • 二级索引条目是预先排序的,因此可以按顺序加载。

    • 未使用更改缓冲区,因为二级索引中没有随机访问插入。

    如果服务器在创建新聚集索引时退出,则不会丢失任何数据,但您必须使用在此过程中存在的临时表来完成恢复过程。由于很少会在大型表上重新创建聚簇索引或重新定义主键,或者在此操作期间遇到系统崩溃,因此本手册不提供有关从这种情况下恢复的信息。

  • 删除主键

    ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;

    ALGORITHM=COPY支持在同一 ALTER TABLE语句中删除主键而不添加新主键。

  • 删除主键并添加另一个

    ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

    数据被大量重组,使其成为一项昂贵的操作。

列操作

下表概述了对列操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参阅 语法和使用说明

表 14.10 列操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
添加列 是的 是的 是的*
删除列 是的 是的 是的
重命名列 是的 是的* 是的
重新排序列 是的 是的 是的
设置列默认值 是的 是的 是的
更改列数据类型 是的
删除列默认值 是的 是的 是的
更改自动增量值 是的 是的 不*
制作专栏NULL 是的 是的* 是的
制作专栏NOT NULL 是的* 是的* 是的
修改ENUMor SET列的定义 是的 是的 是的

语法和使用说明
  • 添加列

    ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

    添加自 增列时不允许并发 DML 。数据被大量重组,使其成为一项昂贵的操作。至少, ALGORITHM=INPLACE, LOCK=SHARED是必需的。

  • 删除列

    ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

    数据被大量重组,使其成为一项昂贵的操作。

  • 重命名列

    ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;

    要允许并发 DML,请保持相同的数据类型并仅更改列名。

    当你保持相同的数据类型和[NOT] NULL属性时,只改变列名,操作总是可以在线进行。

    您还可以重命名属于外键约束的列。外键定义会自动更新以使用新的列名。重命名参与外键的列仅适用于 ALGORITHM=INPLACE. 如果您使用该 ALGORITHM=COPY子句,或者某些其他条件导致操作使用 ALGORITHM=COPY,则该ALTER TABLE语句将失败。

  • 重新排序列

    要对列重新排序,请使用FIRSTor AFTERin CHANGEor MODIFY操作。

    ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

    数据被大量重组,使其成为一项昂贵的操作。

  • 更改列数据类型

    ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

    仅支持更改列数据类型 ALGORITHM=COPY

  • 设置列默认值

    ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INPLACE, LOCK=NONE;

    仅修改表元数据。默认列值存储在表的.frm 文件 中,而不是InnoDB 数据字典中。

  • 删除列默认值

    ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INPLACE, LOCK=NONE;
  • 更改自动增量值

    ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

    修改存储在内存中的值,而不是数据文件。

    在使用复制或分片的分布式系统中,您有时会将表的自动增量计数器重置为特定值。插入表中的下一行使用指定的值作为其自动增量列。您也可以在数据仓库环境中使用此技术,在该环境中您定期清空所有表并重新加载它们,然后从 1 重新启动自动递增序列。

  • 制作专栏NULL

    ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

    就地重建表。数据被大量重组,使其成为一项昂贵的操作。

  • 制作专栏NOT NULL

    ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

    就地重建表。 STRICT_ALL_TABLES或者 STRICT_TRANS_TABLES SQL_MODE是操作成功所必需的。如果该列包含 NULL 值,则操作失败。服务器禁止更改可能导致引用完整性丢失的外键列。请参阅第 13.1.7 节,“ALTER TABLE 语句”。数据被大量重组,使其成为一项昂贵的操作。

  • 修改ENUMor SET列 的定义

    CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
    ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INPLACE, LOCK=NONE;

    通过将新的枚举或集合成员添加到有效成员值列表的末尾ENUM来修改or 列 的定义 可以就地执行,只要数据类型的存储大小不变即可。例如,向 具有 8 个成员的列添加一个成员会将每个值所需的存储空间从 1 个字节更改为 2 个字节;这需要一个表副本。在列表中间添加成员会导致现有成员重新编号,这需要表副本。 SETSET

外键操作

下表概述了对外键操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参阅 语法和使用说明

表 14.11 外键操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
添加外键约束 是的* 是的 是的
删除外键约束 是的 是的 是的

语法和使用说明
  • 添加外键约束

    禁用时支持 该INPLACE算法 。foreign_key_checks否则,仅COPY 支持算法。

    ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1)
      REFERENCES tbl2(col2) referential_actions;
  • 删除外键约束

    ALTER TABLE tbl DROP FOREIGN KEY fk_name;

    可以在 foreign_key_checks启用或禁用选项的情况下在线执行删除外键。

    如果您不知道特定表的外键约束名称,请执行以下语句并在 CONSTRAINT子句中为每个外键查找约束名称:

    SHOW CREATE TABLE table\G

    或者,查询 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 表并使用CONSTRAINT_NAMECONSTRAINT_TYPE列来标识外键名称。

    您还可以在单​​个语句中删除外键及其关联索引:

    ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;
笔记

如果正在更改的表中已经存在外键(即它是 包含子句的子表FOREIGN KEY ... REFERENCE),则附加限制适用于在线 DDL 操作,即使是那些不直接涉及外键列的操作:

  • 如果对父表的更改通过使用or 参数 的or 子句 ALTER TABLE导致子表中的相关更改,则子表上的 an 可以等待另一个事务提交。ON UPDATEON DELETECASCADESET NULL

  • 同样,如果一个表是外键关系中的 父表,即使它不包含任何子句,如果, , or 语句导致 子表中 的or操作FOREIGN KEY,它也可以等待ALTER TABLE完成。INSERTUPDATEDELETEON UPDATEON DELETE

表操作

下表概述了表操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参阅 语法和使用说明

表 14.12 表操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
改变ROW_FORMAT 是的 是的 是的
改变KEY_BLOCK_SIZE 是的 是的 是的
设置持久表统计信息 是的 是的 是的
指定字符集 是的 是的*
转换字符集 是的
优化表 是的* 是的 是的
FORCE使用选项重建 是的* 是的 是的
执行空重建 是的* 是的 是的
重命名表 是的 是的 是的

语法和使用说明
  • 改变ROW_FORMAT

    ALTER TABLE tbl_name ROW_FORMAT = row_format, ALGORITHM=INPLACE, LOCK=NONE;

    数据被大量重组,使其成为一项昂贵的操作。

    有关该 ROW_FORMAT选项的其他信息,请参阅 表格选项

  • 改变KEY_BLOCK_SIZE

    ALTER TABLE tbl_name KEY_BLOCK_SIZE = value, ALGORITHM=INPLACE, LOCK=NONE;

    数据被大量重组,使其成为一项昂贵的操作。

    有关该 KEY_BLOCK_SIZE选项的其他信息,请参阅 表格选项

  • 设置持久表统计选项

    ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;

    仅修改表元数据。

    持久性统计信息包括 STATS_PERSISTENTSTATS_AUTO_RECALCSTATS_SAMPLE_PAGES。有关详细信息,请参阅第 14.8.11.1 节,“配置持久优化器统计参数”

  • 指定字符集

    ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;

    如果新字符编码不同,则重建表。

  • 转换字符集

    ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;

    如果新字符编码不同,则重建表。

  • 优化表

    OPTIMIZE TABLE tbl_name;

    从 MySQL 5.6.17 开始就地执行。FULLTEXT 具有索引的表不支持就地操作。该操作使用该INPLACE 算法,但ALGORITHM不允许 LOCK语法。

  • FORCE使用选项 重建表

    ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

    ALGORITHM=INPLACE从 MySQL 5.6.17 开始 使用ALGORITHM=INPLACE不支持带FULLTEXT 索引的表。

  • 执行“空”重建

    ALTER TABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

    从 MySQL 5.6.17 开始使用ALGORITHM=INPLACEALGORITHM=INPLACE不支持带FULLTEXT 索引的表。

  • 重命名表

    ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INPLACE, LOCK=NONE;

    MySQL 重命名与表对应的文件 tbl_name而不制作副本。(您也可以使用该RENAME TABLE语句重命名表。请参阅 第 13.1.32 节,“RENAME TABLE 语句”。)专门为重命名的表授予的权限不会迁移到新名称。它们必须手动更改。

分区操作

ALTER TABLE 分区子句外,分区 InnoDB表的在线 DDL 操作遵循适用于常规InnoDB表的相同规则。

ALTER TABLE分区子句不通过与常规非分区InnoDB表相同的内部在线 DDL API,并且大多数不支持ALGORITHMLOCK子句。

如果在 ALTER TABLE语句中使用分区子句,分区表将使用该 算法重新分区。换句话说,一个新的分区表是用新的分区方案创建的。新创建的表包括 语句应用的任何更改,表数据被复制到新的表结构中。 ALTER TABLE COPYALTER TABLE

如果您不使用 ALTER TABLE使用该算法的分区子句修改表分区或在您的语句COPY中执行任何其他分区管理,请 对每个表分区执行支持的操作。但是请注意,当 对每个分区执行操作时,由于对多个分区执行操作,对系统资源的需求会增加。 ALTER TABLEALTER TABLEINPLACEINPLACE ALTER TABLE

即使语句的分区子句 ALTER TABLE没有像常规非分区InnoDB表一样通过相同的内部在线 DDL API,MySQL 仍然会尽可能减少数据复制和锁定:

  • ADD PARTITIONDROP PARTITION对于按现有数据分区 RANGELIST不复制现有数据 的表。

  • TRUNCATE PARTITION不复制现有数据。

  • 在由or分区的表期间ADD PARTITIONCOALESCE PARTITION对于 or 分区的表 允许并发查询。MySQL 在持有共享锁的同时复制数据。 HASHLIST

  • 对于REORGANIZE PARTITIONREBUILD PARTITIONADD PARTITION或对于由或 COALESCE PARTITION 分区的表,允许并发查询。复制来自受影响分区的数据,同时在表上持有共享元数据(读取)锁。 LINEAR HASHLIST

  • PARTITION BYREMOVE PARTITIONING允许并发查询。

ALTER TABLE更改表分区的语句不能 ALGORITHMDEFAULTCOPY或一起使用INPLACE。(MySQL 5.6.11 及更高版本支持使用 ALGORITHM=1ALGORITHM=2 执行升级或降级时的此类语句。)此类ALTER TABLE语句也不支持 LOCK子句。

有关ALTER TABLE分区子句的其他信息,请参阅 分区选项第 13.1.7.1 节,“ALTER TABLE 分区操作”。有关分区的一般信息,请参阅 第 19 章,分区