15.12.2 Online DDL的性能和并发性

Online DDL改善了MySQL操作的几个方面：

  • 访问表的应用响应速度更快，因为在进行DDL操作期间，查询和DML操作可以继续进行。减少了锁和对MySQL服务器资源的等待，从而提高了可扩展性，即使不涉及DDL操作的操作也会受益。
  • 即时操作只会修改数据字典中的元数据。在操作的执行阶段，会短暂的获取表上的排它性元数据锁。不影响表数据，使得操作可以很快完成。允许并发DML操作。
  • 在线操作避免了表复制方法带来的磁盘I/O和CPU消耗，这减少了数据库的整体负载。负载的降低有助于在DDL操作期间保持高性能和高吞吐。
  • 与表复制方法相比，Online DDL操作读取到buffer pool中的数据更少，这减少了从内存中清除频繁访问的数据。清除频繁访问的数据可能会导致在DDL操作之后出现短暂的性能下降。

LOCK子句 #

默认情况下，MySQL会在DDL操作期间使用尽可能少的锁。如果需要的话，可以在就地操作和一些复制操作中使用LOCK子句来，以实现更严格的锁。如果LOCK子句指定的锁定级别比DDL操作所允许的锁级别更低，语句会报错。下面按从宽松到严格的顺序描述锁子句：

  • LOCK=NONE： 允许并发查询和DML操作。 例如，对客户注册和购买相关的表使用这个子句，可以避免在长时间运行的DDL操作期间使得表不可用。
  • LOCK=SHARED： 允许并发查询，但是会阻塞DML操作。 例如，在数据仓库表中使用这个子句，这种场景下你可以延迟加载数据操作，直到DML操作完成，但是你不能长时间延迟查查询操作。
  • LOCK=DEFAULT 尽可能的允许更多的并发操作（并发查询，DML操作，或两者都有）。不带LOCK子句和指定LOCK=DEFAULT是一样的。 在你不希望DDL语句的锁级别导致表的可用性问题时，可以使用这个级别。
  • LOCK=EXCLUSIVE: 阻塞并发查询和DML操作。 如果首要关注的是尽快完成DDL操作，并发查询和DML访问并不是必须得，你可以使用这个子句。你也可以在服务器空闲时使用这个子句，来避免意料之外的对表的访问。

Online DDL和元数据锁 #

可以认为Online DDL操作有下面三个阶段：

  • 阶段1：初始化 在初始化阶段，MySQL会根据存储引擎能力，语句中的操作，和用户指定的算法及LOCK选项，决定允许多少并发。在这个阶段，会获取一个可升级的共享元数据锁，来保护当前的表定义。

  • 阶段2：执行 在这个阶段，语句被prepared和智行。元数据锁是否会升级为排它锁，取决于初始化阶段提到的因素。如果需要排它性元数据锁，只会在语句的prepare阶段短暂的持有它。

  • 阶段3：提交表的定义 在提交表定义阶段，元数据锁升级为排它锁，用来淘汰旧的表定义，提交新的表定义。获取排它锁后，只会持有很短的时间。

由于上面提到的排它性元数据锁要求，online DDL操作可能需要等待持有元数据锁的事务提交或回滚。在DDL操作启动之前或执行期间启动的事务可以获取被修改的表上的元数据锁。如果遇到长期运行或不活跃的事务，online DDL操作可能会在等待元数据锁时超时。此外，online DDL操作对拍它性元数据锁的请求会阻塞随后对这张表操作的事务。

这里指的应该是：如果有一个online DDL操作在等待某张表上的元数据锁，那么随后对这张表的操作会被阻塞。

下面的例子展示了等待排它性元数据锁的online DDL操作，和一个等待被释放的元数据锁是怎样阻塞随后对这张表操作的事务的：

Session1：

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

session1中的SELECT语句会获取表t1上的共享元数据锁。

Session2：

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

session2中的online DDL操作，需要获取表t1上的排它性元数据锁来提交表定义变更，必须等待session1中的事务提交或回滚。

Session3：

mysql> SELECT * FROM t1;

session3中的SELECT操作会被阻塞，等待session2中的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

元数据锁信息也会通过Performance数据库中的metadata_locks表展示，这张表提供了不同会话对元数据锁的依赖，会话等待的元数据锁，和持有元数据锁的会话。了解更多信息，请查阅章节27.12.13.3，“The metadata_locks Table”。

Online DDL性能 #

一个DDL操作的性能在很大程度上取决于这个操作是否是即时的，就地的，以及是否重建表。

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

对于修改表数据的DDL操作，你可以通过命令执行结束时展示的"rows affected"值来判断一个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操作前，用如下方法检查这个操作是快还是慢：

  1. 克隆表结构。
  2. 向克隆表中填充少量数据。
  3. 在克隆表上执行DDL操作。
  4. 检查"rows affected"值是否是0。 非零则意味着这个操作复制了表数据，这可能需要特殊计划。例如，你可以在计划停机期间执行DDL操作，或每次只在一个副本上执行。

    向更好的理解DDL操作相关的MySQL处理，可以在DDL操作之前或之后检查Performance数据库和INFORMATION_库中与InnoDB相关的表，查看物理读，写数量，内存占用，等等。

    可以使用Performance数据库中的stage events来监控ALTER TABLE进度。查看章节15.6.1,“使用Performance数据库来监控InnoDB表的ALTER TABLE过程”。

因为需要记录并发DML操作的变更，并在之后应用这些变更，一个online DDL操作可能比阻塞其它会话访问表的表复制方法使用的时间更长。对于使用表的应用来说，要平衡原始性能的降低和更好的响应。在评估修改表结构的技术时，需要考虑终端用户的性能体验，通常基于web页面加载时间等因素。