MySQL :: MySQL 9.0 参考手册 :: 17.12.2 在线 DDL 性能和并发性

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 在线 DDL 性能和并发性

17.12.2 在线 DDL 性能和并发性

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

访问表的应用程序响应更快，因为在 DDL 操作进行时，可以继续对表执行查询和 DML 操作。减少锁定和等待 MySQL 服务器资源会导致更高的可伸缩性，即使对于不参与 DDL 操作的操作也是如此。
即时操作仅修改数据字典中的元数据。在操作的执行阶段，可能会短暂获取表上的独占元数据锁。表数据不受影响，使操作瞬间完成。允许并发 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 相同。
如果您预计 DDL 语句的默认锁定级别不会导致表的任何可用性问题，请使用此子句。
LOCK=EXCLUSIVE:
阻塞并发查询和 DML。
如果主要关注的是在尽可能短的时间内完成 DDL 操作，并且不需要并发查询和 DML 访问，请使用此子句。如果服务器应该处于空闲状态，您也可以使用此子句来避免意外的表访问。

在线 DDL 和元数据锁

在线 DDL 操作可以看作有三个阶段

阶段 1：初始化
在初始化阶段，服务器会考虑存储引擎功能、语句中指定的操作以及用户指定的 ALGORITHM 和 LOCK 选项，来确定操作期间允许的并发程度。在此阶段，将获取共享的可升级元数据锁以保护当前表定义。
阶段 2：执行
在此阶段，将准备和执行语句。元数据锁是否升级为独占锁取决于初始化阶段评估的因素。如果需要独占元数据锁，则仅在语句准备期间短暂获取。
阶段 3：提交表定义
在提交表定义阶段，元数据锁将升级为独占锁，以逐出旧表定义并提交新表定义。一旦授予，独占元数据锁的持续时间很短。

由于上述独占元数据锁的要求，在线 DDL 操作可能必须等待持有表元数据锁的并发事务提交或回滚。在 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)

元数据锁信息也会通过 Performance Schema metadata_locks 表公开，该表提供有关会话之间元数据锁依赖关系、会话正在等待的元数据锁以及当前持有元数据锁的会话的信息。有关更多信息，请参见第 29.12.13.3 节，“metadata_locks 表”。

在线 DDL 性能

DDL 操作的性能很大程度上取决于操作是立即执行、就地执行还是重建表。

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

对于修改表数据的 DDL 操作，您可以通过查看命令完成后显示的 “受影响的行数” 值来确定 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 操作。

注意

为了更好地理解与 DDL 操作相关的 MySQL 处理，请在 DDL 操作前后检查与 InnoDB 相关的 Performance Schema 和 INFORMATION_SCHEMA 表，以查看物理读取、写入、内存分配等的次数。

Performance Schema 阶段事件可用于监控 ALTER TABLE 进度。请参见第 17.16.1 节，“使用 Performance Schema 监控 ALTER TABLE 进度（针对 InnoDB 表）”。

由于记录并发 DML 操作所做的更改并在最后应用这些更改会涉及一些处理工作，因此在线 DDL 操作的总体时间可能比阻止其他会话访问表的表复制机制要长。原始性能的降低与使用该表的应用程序的响应能力提高相平衡。在评估更改表结构的技术时，请根据网页加载时间等因素考虑最终用户对性能的感知。