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

version 8.4

8.0 current
5.7

8.0 Japanese

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

17.12.2 在线 DDL 性能和并发性

在线 DDL 可以改进 MySQL 操作的多个方面：

访问表的应用程序变得更加响应，因为可以在 DDL 操作进行时继续执行查询和 DML 操作。减少对 MySQL 服务器资源的锁定和等待，提高了可扩展性，即使操作不参与 DDL 操作。
实时操作只修改数据字典中的元数据。在执行阶段可能会短暂地获取表的元数据锁定。表数据未受影响，使得操作瞬间完成。允许并发 DML 操作。
在线操作避免了与表复制方法相关的磁盘 I/O 和 CPU 周期，减少了对数据库的总体负载。减少负载有助于保持良好的性能和高通量 durante DDL 操作。
在线操作读取的数据少于表复制操作，这减少了缓冲池中的频繁访问数据的 purging。purging 频繁访问数据可能会在 DDL 操作后导致暂时性能下降。

默认情况下，MySQL 在 DDL 操作中使用尽量少的锁定。可以使用LOCK子句来指定在-place 操作和一些复制操作时强制更严格的锁定，如果需要。如果LOCK子句指定的锁定级别比 DDL 操作允许的锁定级别更少，语句将失败并返回错误。LOCK子句在下面按顺序列出，从最不严格到最严格：

LOCK=NONE:

允许并发查询和 DML 操作。

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

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

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

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

在不期望DDL语句默认锁定级别对表的可用性造成问题时使用该子句。
LOCK=EXCLUSIVE：

阻止并发查询和DML操作。

在DDL操作的完成时间最短为主要考虑时使用该子句，如果并发查询和DML访问不必要，也可以使用该子句。如果服务器应该处于空闲状态，以避免意外表访问，可以使用该子句。

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

Phase 1: 初始化

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

在这个阶段，语句被准备和执行。是否将元数据锁升级为独占的取决于初始化阶段评估的因素。如果需要独占的元数据锁，它只在语句准备时短暂地持有。
提交表定义阶段

在提交表定义阶段，元数据锁被升级为独占以驱逐旧的表定义并提交新的一个。获取独占元数据锁后，它的持有时间很短。

由于上述独占元数据锁要求，一次在线DDL操作可能需要等待当前事务持有该表的元数据锁的事务提交或回滚。开始于或在DDL操作期间的事务可以持有被修改的表的元数据锁。在长时间运行或 inactive 事务的情况下，一次在线DDL操作可能会超时等待独占元数据锁。此外，一个pending 的独占元数据锁请求阻止了该表上的后续事务。

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

Session 1：

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

会话 1 的SELECT 语句在表 t1 上持有共享元数据锁。

Session 2：

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

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

Session 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)

元数据锁的信息也可以通过性能chema中的metadata_locks表获取，该表提供了会话之间元数据锁的依赖关系、会话等待的元数据锁和当前持有元数据锁的会话信息。更多信息，请见第29.12.13.3节，“The metadata_locks Table”。

DDL操作的性能主要取决于操作是否立即执行、是否在原地执行和是否重建表。

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

对修改表数据的DDL操作，您可以通过查看命令完成后显示的“rows affected”值来确定是否在原地修改或复制表。例如：

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

在对大型表运行DDL操作前，请按照以下步骤检查操作是否快速或慢：

克隆表结构。
将克隆表中添加少量数据。
在克隆表上运行DDL操作。
查看“rows affected”值是否为零。如果不是零，表示操作复制了表数据，这可能需要特殊的计划。例如，您可以在预定的停机时间内执行DDL操作，或者在每个副本服务器上一个接一个地执行。

Note

为了更好地理解MySQL与DDL操作相关的处理，可以查看Performance Schema和INFORMATION_ SCHEMA表，以了解InnoDB相关的物理读取、写入、内存分配等信息，前后对比DDL操作的结果。

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

由于在记录并发DML操作所做的更改时需要一些处理工作，然后在结尾应用这些更改，因此在线DDL操作可能比阻塞其他会话访问表的表复制机制总体花费更多时间。对表结构变化的技术评估时，考虑基于因素如网页加载时间的终端用户性能感知。

PREV HOME UP NEXT