MySQL :: MySQL 8.3 Reference Manual :: 17.12.2 Online DDL Performance and Concurrency

Documentation Home

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Online DDL Performance and Concurrency

17.12.2 在线 DDL 性能和并发

在线 DDL 改进了 MySQL 操作的几个方面：

访问表的应用程序变得更加响应，因为查询和 DML 操作可以在 DDL 操作进行时继续执行。减少锁定和等待 MySQL 服务器资源，导致更高的可扩展性，即使对于不涉及 DDL 操作的操作。
即时操作仅修改数据字典中的元数据。在执行阶段，可能会briefly 获取表的独占元数据锁。表数据不受影响，使操作瞬间完成。同时允许并发 DML。
在线操作避免了表复制方法所需的磁盘 I/O 和 CPU 周期，从而最小化了数据库的总体负载。这有助于在 DDL 操作期间维持良好的性能和高吞吐量。
在线操作读取的缓冲池中的数据少于表复制操作，从而减少了频繁访问数据从内存中的清除。频繁访问数据的清除可能会在 DDL 操作后导致暂时的性能下降。

LOCK 子句

默认情况下，MySQL 在 DDL 操作期间使用尽可能少的锁定。可以指定 LOCK 子句来强制执行更严格的锁定，如果需要。如果 LOCK 子句指定的锁定级别低于 DDL 操作所需的锁定级别，语句将失败并返回错误。 LOCK 子句的描述如下，按照从最少到最多的锁定级别排序：

LOCK=NONE：

允许并发查询和 DML。

例如，在涉及客户签约或购买的表上使用该子句，以避免在长时间的 DDL 操作期间使表不可用。
LOCK=SHARED：

允许并发查询，但阻止 DML。

例如，在数据仓库表上使用该子句，可以延迟数据加载操作直到 DDL 操作完成，但查询不能长时间延迟。
LOCK=DEFAULT：

允许尽可能多的并发（并发查询、DML 或两者）。省略 LOCK 子句与指定 LOCK=DEFAULT 等效。

使用该子句时，不期望默认锁定级别会导致表不可用问题。
LOCK=EXCLUSIVE：

阻止并发查询和 DML。

如果主要关心的是尽快完成 DDL 操作，并且不需要并发查询和 DML 访问，可以使用该子句。您也可以在服务器空闲时使用该子句，以避免意外的表访问。

在线 DDL 和元数据锁

在线 DDL 操作可以分为三个阶段：

阶段 1：初始化

在初始化阶段，服务器确定了在操作期间允许的并发级别，考虑了存储引擎的能力、语句中指定的操作和用户指定的 ALGORITHM 和 LOCK 选项。在这个阶段，获取了共享升级的元数据锁以保护当前表定义。
阶段 2：执行

在这个阶段，语句被准备和执行。根据初始化阶段的评估结果，元数据锁可能会升级为独占锁。如果需要独占元数据锁，它只在语句准备期间briefly 获取。
阶段 3：提交表定义

在提交表定义阶段，元数据锁升级为独占锁，以 evict 旧的表定义并提交新的定义。一旦授予，独占元数据锁的持续时间很短。

由于独占元数据锁的要求，如上所述，在线 DDL 操作可能需要等待并发事务提交或回滚，这些事务在表上持有元数据锁。在长时间运行或不活动的事务的情况下，在线 DDL 操作可能会超时等待独占元数据锁。此外，等待独占元数据锁的在线 DDL 操作也会阻止后续的事务在表上执行。

以下示例演示了在线 DDL 操作等待独占元数据锁，并且如何阻止后续事务对表的操作。

会话 1：

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

会话 1 中的 SELECT 语句在表 t1 上获取共享元数据锁。

会话 2：

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

会话 2 中的在线 DDL 操作，需要在表 t1 上获取独占元数据锁以提交表定义更改，必须等待会话 1 事务提交或回滚。

会话 3：

mysql> SELECT * FROM t1;

会话 3 中的 SELECT 语句被阻止，等待会话 2 中的 ALTER TABLE 操作请求的独占元数据锁被授予。

您可以使用 SHOW FULL PROCESSLIST 确定事务是否等待元数据锁。

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 表”。

在线 DDL 性能比较

DDL 操作的性能主要取决于操作是否即时、就地执行，以及是否重建表。

要评估 DDL 操作的相对性能，可以比较使用 ALGORITHM=INSTANT、ALGORITHM=INPLACE 和 ALGORITHM=COPY 的结果。还可以启用 old_alter_table 强制使用 ALGORITHM=COPY。

对于修改表数据的 DDL 操作，可以通过查看命令完成后显示的 “受影响的行数” 值来确定操作是否在就地执行或执行表复制。例如：

更改列的默认值（快速，不影响表数据）：
```
Query OK, 0 rows affected (0.07 sec)
```
添加索引（需要时间，但 0 行受影响 表示表未被复制）：
```
Query OK, 0 rows affected (21.42 sec)
```
更改列的数据类型（需要大量时间，并需要重建表的所有行）：
```
Query OK, 1671168 rows affected (1 min 35.54 sec)
```

在对大型表运行 DDL 操作之前，请检查操作是否快速或慢，如下所示：

克隆表结构。
在克隆表中填充少量数据。
在克隆表上运行 DDL 操作。
检查 “受影响的行数” 值是否为零。如果不是零，表示操作复制表数据，可能需要特殊规划。例如，您可能需要在计划停机期间或在每个副本服务器上逐个执行 DDL 操作。

Note

要更好地理解 MySQL 与 DDL 操作相关的处理，请在 DDL 操作之前和之后检查性能模式和 INFORMATION_SCHEMA 表，以查看物理读取、写入、内存分配等信息。

性能模式阶段事件可以用于监控 ALTER TABLE 进度。请参阅第 17.16.1 节，“使用性能模式监控 InnoDB 表的 ALTER TABLE 进度”。

由于在线 DDL 操作涉及到记录并应用并发 DML 操作的更改，因此可能比表复制机制需要更长的时间。但是，这种机制可以提供更好的响应性。评估技术时，请考虑最终用户对性能的感知，基于因素如网页加载时间。

PREV HOME UP NEXT