mysql 在线DDL ＜old_alter_table参数＞

最新推荐文章于 2025-11-11 16:24:41 发布

原创最新推荐文章于 2025-11-11 16:24:41 发布 · 1k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #mysql

本文详细介绍了MySQL中ALTER TABLE命令的两种算法：COPY和INPLACE。COPY算法涉及表数据的复制和重命名，可能导致并发DML操作暂停；而INPLACE算法则尝试就地修改表，支持部分在线DDL操作。通过old_alter_table参数可以控制这些行为，理解这些机制有助于优化数据库维护。

old_alter_table

命令行格式	`--old-alter-table[={OFF\|ON}]`
系统变量	`old_alter_table`
范围	全局，会话
动态的	是的
类型	布尔值
默认值	`OFF`

启用此变量后，服务器不会使用优化的方法来处理ALTER TABLE操作。它恢复使用临时表，复制数据，然后将临时表重命名为原始表，如 MySQL 5.0 及更早版本所使用的那样。

5.7默认为OFF

mysql中的ALTER TABLE 命令使用算法：

（1）COPY：对原表的一个副本进行操作，将表数据从原表逐行复制到新表中。不允许并发 DML。

此时设置的set old_alter_table=1；

（2）INPLACE：操作避免复制表数据，但可能会就地重建表。在操作的准备和执行阶段可能会短暂地对表进行独占元数据锁定。通常，支持并发 DML。此时设置的set old_alter_table=0；

COPY

ALTER TABLE使用该 COPY算法的操作等待正在修改表的其他操作完成。对表副本应用更改后，将数据复制过来，删除原始表，并将表副本重命名为原始表的名称。当ALTER TABLE 操作执行时，原始表可以被其他会话读取（除了很快指出的例外）。ALTER TABLE操作开始后开始的表的更新和写入将暂停，直到新表准备好，然后自动重定向到新表。该表的临时副本是在原表的数据库目录中创建的，除非它是一个RENAME TO 将表移动到驻留在不同目录中的数据库的操作。

前面提到的例外是 ALTER TABLE在准备安装表.frm文件的新版本、丢弃旧文件并从表和表定义缓存中清除过时的表结构时阻止读取（不仅仅是写入）。此时，它必须获取排他锁。为此，它等待当前读取器完成，并阻止新的读取和写入。

ALTER TABLE使用该COPY算法的操作会阻止并发 DML 操作。仍然允许并发查询。也就是说，表复制操作始终至少包括LOCK=SHARED（允许查询但不允许 DML）的并发限制。您可以LOCK通过指定来进一步限制支持子句的操作的并发性LOCK=EXCLUSIVE，这会阻止 DML 和查询。有关更多信息，请参阅并发控制。

要强制将COPY算法用于 ALTER TABLE否则不会使用它的操作，请启用 old_alter_table系统变量或指定ALGORITHM=COPY。如果old_alter_table设置与ALGORITHM值不是 DEFAULT的ALGORITHM 子句之间存在冲突，则该子句优先。

对于InnoDB表，对位于共享表空间中的表ALTER TABLE使用该COPY算法的操作可以增加表空间使用的空间量。此类操作需要与表中的数据加上索引一样多的额外空间。对于驻留在共享表空间中的表，操作期间使用的额外空间不会释放回操作系统，就像驻留在每个表文件表空间中的表一样。

INPLACE

ALTER TABLE使用该INPLACE算法的操作包括：

ALTER TABLEInnoDB 在线 DDL功能支持的操作。
重命名表。MySQL 重命名与表对应的文件，tbl_name而不进行复制。（您也可以使用该RENAME TABLE语句重命名表。）专门为重命名的表授予的权限不会迁移到新名称。它们必须手动更改。
仅修改表元数据的操作。这些操作是即时的，因为服务器只更改表.frm文件，而不是更改表内容。仅元数据操作包括：
- 重命名列。
- 更改列的默认值（NDB表除外）。
- 通过在有效成员值列表的末尾添加新的枚举或集合成员来修改ENUM或 SET列的定义，只要数据类型的存储大小不改变。例如，向具有 8 个成员的列添加成员将每个值所需的存储空间从 1 个字节更改为 2 个字节；这需要一个表副本。在列表中间添加成员会导致现有成员重新编号，这需要表副本。
重命名索引。
添加或删除二级索引，用于 InnoDB和 NDB表。
对于NDB表，在可变宽度列上添加和删除索引的操作。这些操作在线进行，无需复制表，也不会在大部分持续时间内阻塞并发 DML 操作。

ALTER TABLE升级的MySQL 5.5的时间列到5.6格式ADD COLUMN， CHANGE COLUMN，MODIFY COLUMN，ADD INDEX，和 FORCE操作。INPLACE由于必须重建表，因此无法使用该算法完成此转换，因此ALGORITHM=INPLACE在这些情况下指定会导致错误。ALGORITHM=COPY必要时指定。

如果ALTER TABLE对用于分区表的多列索引的操作KEY更改了列的顺序，则只能使用 ALGORITHM=COPY.

的WITHOUT VALIDATION和WITH VALIDATION条款影响是否 ALTER TABLE执行用于就地操作虚拟生成的列修改。

NDB Cluster 以前支持ALTER TABLE使用ONLINE和 OFFLINE关键字进行在线操作。不再支持这些关键字；它们的使用会导致语法错误。MySQL NDB Cluster 7.5（及更高版本）支持ALGORITHM=INPLACE使用与标准 MySQL 服务器相同的语法进行在线操作。NDB不支持在线更改表空间。

ALTER TABLE使用DISCARD ... PARTITION ... TABLESPACE或IMPORT ... PARTITION ... TABLESPACE不创建任何临时表或临时分区文件。

ALTER TABLEwith ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REBUILD PARTITION, orREORGANIZE PARTITION 不创建临时表（与NDB表一起使用时除外）；但是，这些操作可以并且确实会创建临时分区文件。

ADD或DROP用于操作 RANGE或LIST分区直接操作或接近。ADD或 COALESCE操作HASH 或KEY分区在所有分区之间复制数据，除非使用LINEAR HASH或 LINEAR KEY；这实际上与创建新表相同，尽管ADD orCOALESCE操作是逐个分区执行的。REORGANIZE操作仅复制更改的分区，不接触未更改的分区。