本节的以下主题下提供了 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
新创建的二级索引仅包含orALTER 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
,而不是稍后发布。当你创建一个
UNIQUE
或PRIMARY 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_MODE
strict_trans_tables
strict_all_tables
SQL_MODE
ALGORITHM=INPLACE
NULL
值。该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 |
是的* | 是的* | 是的 | 不 |
修改ENUM or
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
语句将失败。重新排序列
要对列重新排序,请使用
FIRST
orAFTER
inCHANGE
orMODIFY
操作。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;
删除列默认值
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 语句”。数据被大量重组,使其成为一项昂贵的操作。修改
ENUM
orSET
列 的定义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 个字节;这需要一个表副本。在列表中间添加成员会导致现有成员重新编号,这需要表副本。SET
SET
下表概述了对外键操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参阅 语法和使用说明。
语法和使用说明
添加外键约束
禁用时支持 该
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_NAME
和CONSTRAINT_TYPE
列来标识外键名称。您还可以在单个语句中删除外键及其关联索引:
ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;
如果正在更改的表中已经存在外键(即它是
包含子句的子表FOREIGN KEY ... REFERENCE
),则附加限制适用于在线 DDL 操作,即使是那些不直接涉及外键列的操作:
如果对父表的更改通过使用or 参数 的or 子句
ALTER TABLE
导致子表中的相关更改,则子表上的 an 可以等待另一个事务提交。ON UPDATE
ON DELETE
CASCADE
SET NULL
同样,如果一个表是外键关系中的 父表,即使它不包含任何子句,如果, , or 语句导致 子表中 的or操作
FOREIGN KEY
,它也可以等待ALTER TABLE
完成。INSERT
UPDATE
DELETE
ON UPDATE
ON 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_PERSISTENT
、STATS_AUTO_RECALC
和STATS_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=INPLACE
。ALGORITHM=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,并且大多数不支持ALGORITHM
和
LOCK
子句。
如果在
ALTER TABLE
语句中使用分区子句,分区表将使用该
算法重新分区。换句话说,一个新的分区表是用新的分区方案创建的。新创建的表包括
语句应用的任何更改,表数据被复制到新的表结构中。
ALTER TABLE
COPY
ALTER TABLE
如果您不使用
ALTER TABLE
使用该算法的分区子句修改表分区或在您的语句COPY
中执行任何其他分区管理,请
对每个表分区执行支持的操作。但是请注意,当
对每个分区执行操作时,由于对多个分区执行操作,对系统资源的需求会增加。
ALTER
TABLE
ALTER
TABLE
INPLACE
INPLACE
ALTER
TABLE
即使语句的分区子句
ALTER TABLE
没有像常规非分区InnoDB
表一样通过相同的内部在线 DDL API,MySQL 仍然会尽可能减少数据复制和锁定:
ADD PARTITION
DROP PARTITION
对于按现有数据分区RANGE
或LIST
不复制现有数据 的表。TRUNCATE PARTITION
不复制现有数据。在由or分区的表期间
ADD PARTITION
和COALESCE PARTITION
对于 or 分区的表 允许并发查询。MySQL 在持有共享锁的同时复制数据。HASH
LIST
对于
REORGANIZE PARTITION
、REBUILD PARTITION
或ADD PARTITION
或对于由或COALESCE PARTITION
分区的表,允许并发查询。复制来自受影响分区的数据,同时在表上持有共享元数据(读取)锁。LINEAR HASH
LIST
PARTITION BY
并REMOVE PARTITIONING
允许并发查询。
ALTER TABLE
更改表分区的语句不能
ALGORITHM
与DEFAULT
、
COPY
或一起使用INPLACE
。(MySQL 5.6.11 及更高版本支持使用
ALGORITHM=1
或ALGORITHM=2
执行升级或降级时的此类语句。)此类ALTER
TABLE
语句也不支持
LOCK
子句。
有关ALTER
TABLE
分区子句的其他信息,请参阅
分区选项和
第 13.1.7.1 节,“ALTER TABLE 分区操作”。有关分区的一般信息,请参阅
第 19 章,分区。