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: 初始化
在初始化阶段,服务器确定了操作期间允许的并发性,考虑存储引擎能力、语句中指定的操作和用户指定的
ALGORITHM
和LOCK
选项。在这个阶段,服务器获取一个共享可升级的元数据锁,以保护当前表定义。 -
执行阶段
在这个阶段,语句被准备和执行。是否将元数据锁升级为独占的取决于初始化阶段评估的因素。如果需要独占的元数据锁,它只在语句准备时短暂地持有。
-
提交表定义阶段
在提交表定义阶段,元数据锁被升级为独占以驱逐旧的表定义并提交新的一个。获取独占元数据锁后,它的持有时间很短。
由于上述独占元数据锁要求,一次在线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=INSTANT
、ALGORITHM=INPLACE
和ALGORITHM=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操作前,请按照以下步骤检查操作是否快速或慢:
-
克隆表结构。
-
将克隆表中添加少量数据。
-
在克隆表上运行DDL操作。
-
查看“rows affected”值是否为零。如果不是零,表示操作复制了表数据,这可能需要特殊的计划。例如,您可以在预定的停机时间内执行DDL操作,或者在每个副本服务器上一个接一个地执行。
为了更好地理解MySQL与DDL操作相关的处理,可以查看Performance Schema和INFORMATION_ SCHEMA
表,以了解InnoDB相关的物理读取、写入、内存分配等信息,前后对比DDL操作的结果。
性能_schema阶段事件可以用来监控ALTER TABLE
进度。请参阅第17.16.1节,“使用性能_schema监控 InnoDB 表的 ALTER TABLE 进度”。
由于在记录并发DML操作所做的更改时需要一些处理工作,然后在结尾应用这些更改,因此在线DDL操作可能比阻塞其他会话访问表的表复制机制总体花费更多时间。对表结构变化的技术评估时,考虑基于因素如网页加载时间的终端用户性能感知。