MySQL :: MySQL 8.4 参考手册 :: 17.12 InnoDB 和在线 DDL

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 17 InnoDB 存储引擎 / 17.12 InnoDB 和在线 DDL

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

17.12 InnoDB 与在线 DDL17.12 InnoDB and Online DDL

在线 DDL 功能提供对即时和就地表修改以及并发 DML 的支持。这一功能的优点包括：The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Benefits of this feature include:

在繁忙的生产环境中提高响应能力和可用性，因为让表不可用几分钟或几小时是不切实际的。Improved responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.
对于就地操作，可以使用 LOCK 子句在 DDL 操作期间调整性能和并发之间的平衡。请参阅 LOCK 子句。For in-place operations, the ability to adjust the balance between performance and concurrency during DDL operations using the LOCK clause. See The LOCK clause.
比表复制方法占用更少的磁盘空间和 I/O 开销。Less disk space usage and I/O overhead than the table-copy method.

通常，您无需执行任何特殊操作即可启用在线 DDL。默认情况下，MySQL 会尽可能立即或就地执行操作，并尽量减少锁定。Typically, you do not need to do anything special to enable online DDL. By default, MySQL performs the operation instantly or in place, as permitted, with as little locking as possible.

您可以使用 ALGORITHM 和 LOCK 子句来控制 DDL 操作的某些方面，这些子句用于 ALTER TABLE 语句。它们放在语句的末尾，并用逗号与表和列的定义分开。例如：You can control aspects of a DDL operation using the ALGORITHM and LOCK clauses of the ALTER TABLE statement. These clauses are placed at the end of the statement, separated from the table and column specifications by commas. For example:

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

LOCK 子句可用于就地执行的操作，并且有助于在操作过程中微调对表的并发访问程度。对于即时执行的操作，仅支持 LOCK=DEFAULT。ALGORITHM 子句主要用于性能比较，并在遇到问题时作为旧表复制行为的备用方案。例如：The LOCK clause may be used for operations that are performed in place and is useful for fine-tuning the degree of concurrent access to the table during operations. Only LOCK=DEFAULT is supported for operations that are performed instantly. The ALGORITHM clause is primarily intended for performance comparisons and as a fallback to the older table-copying behavior in case you encounter any issues. For example:

为了避免在原地 ALTER TABLE 操作期间意外使表无法进行读取、写入或两者，应该在 ALTER TABLE 语句中指定一个子句，例如 LOCK=NONE（允许读取和写入）或 LOCK=SHARED（允许读取）。如果所请求的并发级别不可用，操作将立即停止。To avoid accidentally making the table unavailable for reads, writes, or both, during an in-place ALTER TABLE operation, specify a clause on the ALTER TABLE statement such as LOCK=NONE (permit reads and writes) or LOCK=SHARED (permit reads). The operation halts immediately if the requested level of concurrency is not available.
要比较算法之间的性能，请运行带有 ALGORITHM=INSTANT、ALGORITHM=INPLACE 和 ALGORITHM=COPY 的语句。您还可以运行启用 old_alter_table 配置选项的语句，以强制使用 ALGORITHM=COPY。To compare performance between algorithms, run a statement with ALGORITHM=INSTANT, ALGORITHM=INPLACE and ALGORITHM=COPY. You can also run a statement with the old_alter_table configuration option enabled to force the use of ALGORITHM=COPY.
为了避免使用复制表的 ALTER TABLE 操作占用服务器，请包含 ALGORITHM=INSTANT 或 ALGORITHM=INPLACE。如果无法使用指定的算法，语句将立即停止。To avoid tying up the server with an ALTER TABLE operation that copies the table, include ALGORITHM=INSTANT or ALGORITHM=INPLACE. The statement halts immediately if it cannot use the specified algorithm.

首页上一级下一节