14.13.2 在线 DDL 性能和并发性

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

访问 table 的应用程序响应速度更快，因为在进行 DDL 操作时可以 continue 执行 table 上的查询和 DML 操作。减少锁定和 awaitMySQL 服务器资源可带来更大的可伸缩性，即使对于 DDL 操作中不涉及的操作也是如此。
就地操作避免了与 table 复制方法相关的磁盘 I/O 和 CPU 周期，从而最大程度地减少了数据库的总体负载。最小化负载有助于在 DDL 操作期间保持良好的性能和高吞吐量。
就地操作比 table 复制操作将更少的数据读入缓冲池，这减少了从内存中清除经常访问的数据。在 DDL 操作之后，清除频繁访问的数据可能会导致性能暂时下降。

LOCK 子句

默认情况下，在 DDL 操作期间，MySQL 使用的锁尽可能少。如果需要，可以指定LOCK子句以实施更严格的锁定。如果LOCK子句指定的锁定限制级别小于特定 DDL 操作所允许的锁定级别，则该语句将失败并显示错误。 LOCK子句的描述 Sequences 如下(从最小到最严格)：

LOCK=NONE :

允许并发查询和 DML。

例如，对于涉及 Client 注册或购买的 table，请使用此子句，以避免在冗长的 DDL 操作期间使这些 table 不可用。

LOCK=SHARED :

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

例如，在数据仓库 table 上使用此子句，您可以在其中延迟数据加载操作直到 DDL 操作完成，但是查询不能长时间延迟。

LOCK=DEFAULT :

允许尽可能多的并发(并发查询，DML 或两者)。省略LOCK子句与指定LOCK=DEFAULT相同。

当您知道 DDL 语句的默认锁定级别不会导致 table 的可用性问题时，请使用此子句。

LOCK=EXCLUSIVE :

阻止并发查询和 DML。

如果主要的关注点是在尽可能短的时间内完成 DDL 操作，并且不需要并发查询和 DML 访问，请使用此子句。如果服务器应处于空闲状态，则也可以使用此子句，以避免意外的 table 访问。

在线 DDL 和元数据锁

联机 DDL 操作可以视为具有三个阶段：

阶段 1：初始化

在初始化阶段，服务器将考虑存储引擎功能，语句中指定的操作以及用户指定的ALGORITHM和LOCK选项，确定在操作期间允许多少并发。在此阶段，将使用共享的可升级元数据锁来保护当前 table 定义。

阶段 2：执行

在此阶段，准备并执行该语句。元数据锁是否升级到独占取决于初始化阶段评估的因素。如果需要独占元数据锁定，则仅在语句准备期间进行短暂锁定。

阶段 3：提交 table 格定义

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

由于上面概述的排他性元数据锁定要求，在线 DDL 操作可能必须 await 持有 table 上元数据锁定的并发事务才能提交或回滚。在 DDL 操作之前或期间启动的事务可以将元数据锁保存在要更改的 table 上。如果事务长时间运行或处于非活动状态，则在线 DDL 操作可能会超时，await 独占元数据锁定。此外，在线 DDL 操作请求的待处理独占元数据锁定会阻止 table 上的后续事务。

下面的示例演示了 await 独占元数据锁定的联机 DDL 操作，以及未决的元数据锁定如何阻止 table 上的后续事务。

Session 1:

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

会话 1 SELECT语句对 tablet1 进行了共享元数据锁定。

Session 2:

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

会话 2 中的联机 DDL 操作需要在 tablet1 上具有排他的元数据锁定才能提交 table 定义更改，该操作必须 await 会话 1 事务提交或回滚。

Session 3:

mysql> SELECT * FROM t1;

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

您可以使用显示完整的程序 Lists确定事务是否正在 await 元数据锁定。

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_lockstable 公开元数据锁信息，该 table 提供有关会话之间的元数据锁依赖性，会话正在 await 的元数据锁以及当前持有元数据锁的会话的信息。有关更多信息，请参见第 25.12.12.1 节，“ The metadata_lockstable”。

在线 DDL 性能

DDL 操作的性能在很大程度上取决于是否在适当位置执行该操作以及是否重建该 table。

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

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

更改列的默认值(快速，不影响 table 数据)：

Query OK, 0 rows affected (0.07 sec)

添加索引(需要花费时间，但0 rows affectedtable 示未复制 table)：

Query OK, 0 rows affected (21.42 sec)

更改列的数据类型(花费大量时间，并且需要重建 table 的所有行)：

Query OK, 1671168 rows affected (1 min 35.54 sec)

在大 table 上运行 DDL 操作之前，请按照以下步骤检查操作是快速还是慢速：

克隆 table 结构。
用少量数据填充克隆 table。
在克隆 table 上运行 DDL 操作。
检查“受影响的行”值是否为零。非零值 table 示该操作将复制 table 数据，这可能需要进行特殊规划。例如，您可以在计划的停机时间内执行一次 DDL 操作，或者一次在每个副本服务器上执行一次 DDL 操作。

Note

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

性能架构阶段事件可用于监视ALTER TABLE进度。参见第 14.17.1 节“使用性能模式监视 InnoDBtable 的 ALTER TABLE 进度”。

因为记录并发 DML 操作所做的更改涉及一些处理工作，然后在结束时应用这些更改，所以在线 DDL 操作可能比阻止其他会话访问 table 的 table 复制机制花费更长的时间。对于使用该 table 的应用程序，原始性能的下降与更好的响应能力之间取得了平衡。在评估用于更改 table 结构的技术时，请根据诸如网页加载时间之类的因素，考虑最终用户对性能的了解。

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35