Documentation Home
MySQL 8.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


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: 初始化

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

  • 执行阶段

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

  • 提交表定义阶段

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

由于上述独占元数据锁要求,一次在线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=INSTANTALGORITHM=INPLACEALGORITHM=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操作前,请按照以下步骤检查操作是否快速或慢:

  1. 克隆表结构。

  2. 将克隆表中添加少量数据。

  3. 在克隆表上运行DDL操作。

  4. 查看rows affected值是否为零。如果不是零,表示操作复制了表数据,这可能需要特殊的计划。例如,您可以在预定的停机时间内执行DDL操作,或者在每个副本服务器上一个接一个地执行。

Note

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

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

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