默认情况下，MySQL 在执行 DDL 操作时会尽量减少锁定。如果需要，可以在就地操作和某些复制操作中指定 LOCK 子句以强制更严格的锁定。如果 LOCK 子句指定的锁定级别低于特定 DDL 操作允许的级别，则语句会报错。LOCK 子句按从最不严格到最严格的顺序描述如下：By default, MySQL uses as little locking as possible during a DDL operation. The LOCK clause can be specified for in-place operations and some copy operations to enforce more restrictive locking, if required. If the LOCK clause specifies a less restrictive level of locking than is permitted for a particular DDL operation, the statement fails with an error. LOCK clauses are described below, in order of least to most restrictive:

在线 DDL 操作可以被视为有三个阶段：Online DDL operations can be viewed as having three phases:

由于上述专用元数据锁要求，在线 DDL 操作可能需要等待持有表元数据锁的并发事务提交或回滚。在 DDL 操作开始之前或期间启动的事务可能会持有正在被更改的表的元数据锁。在长时间运行或不活动的事务情况下，在线 DDL 操作可能会因为等待专用元数据锁而超时。此外，在线 DDL 操作请求的待处理专用元数据锁会阻塞该表上的后续事务。Due to the exclusive metadata lock requirements outlined above, an online DDL operation may have to wait for concurrent transactions that hold metadata locks on the table to commit or rollback. Transactions started before or during the DDL operation can hold metadata locks on the table being altered. In the case of a long running or inactive transaction, an online DDL operation can time out waiting for an exclusive metadata lock. Additionally, a pending exclusive metadata lock requested by an online DDL operation blocks subsequent transactions on the table.

下面的示例演示了在线 DDL 操作等待专用元数据锁，以及待处理元数据锁如何阻塞该表上的后续事务。The following example demonstrates an online DDL operation waiting for an exclusive metadata lock, and how a pending metadata lock blocks subsequent transactions on the table.

会话 1：Session 1:

mysql> CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
mysql> START TRANSACTION;
mysql> SELECT * FROM t1;

会话 1 的 SELECT 语句对表 t1 获取了共享元数据锁。The session 1 SELECT statement takes a shared metadata lock on table t1.

会话 2：Session 2:

mysql> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;

会话 2 中的在线 DDL 操作，需要对表 t1 获取排他元数据锁以提交表定义更改，必须等待会话 1 的事务提交或回滚。The online DDL operation in session 2, which requires an exclusive metadata lock on table t1 to commit table definition changes, must wait for the session 1 transaction to commit or roll back.

会话 3：Session 3:

mysql> SELECT * FROM t1;

会话 3 中发出的 SELECT 语句被阻塞，等待会话 2 中 ALTER TABLE 操作请求的排他元数据锁被授予。The SELECT statement issued in session 3 is blocked waiting for the exclusive metadata lock requested by the ALTER TABLE operation in session 2 to be granted.

您可以使用 SHOW FULL PROCESSLIST 来确定事务是否在等待元数据锁。You can use SHOW FULL PROCESSLIST to determine if transactions are waiting for a metadata lock.

mysql> SHOW FULL PROCESSLIST\G
...
*************************** 2. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 44
  State: Waiting for table metadata lock
   Info: ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
...
*************************** 4. row ***************************
     Id: 7
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 5
  State: Waiting for table metadata lock
   Info: SELECT * FROM t1
4 rows in set (0.00 sec)

元数据锁信息也可以通过性能模式 metadata_locks 表暴露出来，该表提供有关会话之间元数据锁依赖关系、会话正在等待的元数据锁以及当前持有元数据锁的会话的信息。更多信息，请参见 第 29.12.13.3 节，“The metadata_locks 表”。Metadata lock information is also exposed through the Performance Schema metadata_locks table, which provides information about metadata lock dependencies between sessions, the metadata lock a session is waiting for, and the session that currently holds the metadata lock. For more information, see Section 29.12.13.3, “The metadata_locks Table”.

DDL 操作的性能在很大程度上取决于操作是否即时执行、是否就地执行以及是否重建表。The performance of a DDL operation is largely determined by whether the operation is performed instantly, in place, and whether it rebuilds the table.

要评估 DDL 操作的相对性能，您可以比较使用 ALGORITHM=INSTANT、ALGORITHM=INPLACE 和 ALGORITHM=COPY 的结果。也可以在启用 old_alter_table 的情况下运行语句，以强制使用 ALGORITHM=COPY。To assess the relative performance of a DDL operation, you can compare results using ALGORITHM=INSTANT, ALGORITHM=INPLACE, and ALGORITHM=COPY. A statement can also be run with old_alter_table enabled to force the use of ALGORITHM=COPY.

对于修改表数据的 DDL 操作，您可以通过查看命令完成后显示的 “受影响的行” 值来确定 DDL 操作是就地进行更改还是执行表复制。例如：For DDL operations that modify table data, you can determine whether a DDL operation performs changes in place or performs a table copy by looking at the “rows affected” value displayed after the command finishes. For example:

在对大表运行 DDL 操作之前，请按照以下步骤检查操作是快还是慢：Before running a DDL operation on a large table, check whether the operation is fast or slow as follows:

由于涉及记录并发 DML 操作所做更改的某些处理工作，然后在最后应用这些更改，因此在线 DDL 操作的整体耗时可能比阻止其他会话访问表的表复制机制更长。原始性能的下降与使用该表的应用程序的更好响应性相平衡。在评估更改表结构的技术时，应考虑最终用户对性能的感知，这基于诸如网页加载时间等因素。Because there is some processing work involved with recording the changes made by concurrent DML operations, then applying those changes at the end, an online DDL operation could take longer overall than the table-copy mechanism that blocks table access from other sessions. The reduction in raw performance is balanced against better responsiveness for applications that use the table. When evaluating the techniques for changing table structure, consider end-user perception of performance, based on factors such as load times for web pages.