14.13.2 在线 DDL 性能和并发_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第 15 章 InnoDB 存储引擎 / 15.12 InnoDB和在线DDL / 14.13.2 在线 DDL 性能和并发

14.13.2 在线 DDL 性能和并发

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

访问该表的应用程序响应更快，因为在 DDL 操作正在进行时可以继续对表进行查询和 DML 操作。减少锁定和等待 MySQL 服务器资源导致更大的可伸缩性，即使对于 DDL 操作中不涉及的操作也是如此。
就地操作避免了与表复制方法相关的磁盘 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 操作可以看作具有三个阶段：

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

由于上面列出的独占元数据锁要求，联机 DDL 操作可能必须等待在表上持有元数据锁的并发事务提交或回滚。在 DDL 操作之前或期间启动的事务可以在被更改的表上持有元数据锁。在长时间运行或非活动事务的情况下，联机 DDL 操作可能会在等待独占元数据锁时超时。此外，联机 DDL 操作请求的未决独占元数据锁会阻止表上的后续事务。

以下示例演示了等待排他元数据锁的在线 DDL 操作，以及挂起的元数据锁如何阻止表上的后续事务。

第 1 节：

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

会话 1SELECT语句在表 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 Schemametadata_locks 表公开，该表提供有关会话之间的元数据锁依赖关系、会话正在等待的元数据锁以及当前持有元数据锁的会话的信息。有关详细信息，请参阅第 25.12.12.1 节，“metadata_locks 表”。

在线 DDL 性能

DDL操作的性能很大程度上取决于操作是否就地执行以及是否重建表。

要评估 DDL 操作的相对性能，您可以将使用的结果与使用ALGORITHM=INPLACE的结果进行比较ALGORITHM=COPY。或者，您可以将结果与 old_alter_table禁用和启用进行比较。

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

更改列的默认值（快速，不影响表数据）：
```
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 操作。
检查“受影响的行”值是否为零。非零值表示操作复制表数据，这可能需要特殊规划。例如，您可能会在计划停机期间执行 DDL 操作，或者一次在每个副本服务器上执行一个操作。

笔记

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

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

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