在线DDL性能和并发性
在线DDL改进了MySQL操作的几个方面:
- 访问表的应用程序响应速度更快,因为在进行DDL操作时可以继续进行表上的查询和DML操作。减少锁定和等待MySQL服务器资源可带来更大的可伸缩性,即使对于DDL操作中不涉及的操作也是如此。
- 即时操作仅修改数据字典中的元数据。在表上没有采取任何元数据锁定,并且表数据不受影响,从而使操作立即进行。并发DML不受影响。
- 联机操作避免了与表复制方法相关的磁盘I / O和CPU周期,从而最大程度地减少了数据库的总体负载。最小化负载有助于在DDL操作期间保持良好的性能和高吞吐量。
- 与表复制操作相比,联机操作将较少的数据读取到缓冲池中,从而减少了从内存中清除经常访问的数据。在DDL操作之后,清除频繁访问的数据可能会导致性能暂时下降。
LOCK子句
默认情况下,在DDL操作期间,MySQL使用的锁尽可能少。如果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操作可以视为具有三个阶段:
阶段1:初始化
在初始化阶段,服务器将考虑存储引擎功能,语句中指定的操作以及用户指定的
ALGORITHM
和LOCK
选项,以确定在操作期间允许多少并发。在此阶段,将使用共享的可升级元数据锁来保护当前表定义。阶段2:执行
在此阶段,准备并执行该语句。元数据锁是否升级到独占取决于初始化阶段评估的因素。如果需要独占元数据锁定,则仅在语句准备期间进行短暂锁定。
阶段3:提交表定义
在提交表定义阶段,将元数据锁升级为独占,以退出旧表定义并提交新表定义。一旦被授予,独占元数据锁定的持续时间就很短。
由于上面概述的排他性元数据锁定要求,在线DDL操作可能必须等待持有表上元数据锁定的并发事务才能提交或回滚。在DDL操作之前或期间启动的事务可以将元数据锁保存在要更改的表上。如果事务长期运行或处于非活动状态,则在线DDL操作可能会超时,等待独占元数据锁定。此外,在线DDL操作请求的待处理独占元数据锁定会阻止表上的后续事务。
下面的示例演示了等待独占元数据锁定的联机DDL操作,以及未决元数据锁定如何阻止表上的后续事务。
第一场:
mysql>CREATE TABLE t1 (c1 INT)ENGINE =InnoDB; mysql>START TRANSACTION ; mysql>SELECT *FROM t1;
会话1 SELECT
语句对表t1进行共享元数据锁定。
第二场:
mysql>ALTER TABLE t1ADD COLUMN x INT,ALGORITHM =INPLACE,LOCK =NONE ;
会话2中的联机DDL操作需要对表t1进行排他性元数据锁定才能提交表定义更改,该操作必须等待会话1事务提交或回滚。
第三节:
mysql>SELECT *FROM t1;
SELECT
会话3中发出的语句被阻塞,等待ALTER TABLE
会话2中的操作请求的互斥元数据锁被授予。
您可以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)
元数据锁定信息也通过“性能模式”metadata_locks
表公开,该表提供有关会话之间的元数据锁定依赖性,会话正在等待的元数据锁定以及当前持有元数据锁定的会话的信息。有关更多信息,请参见“ metadata_locks表”。
在线DDL性能
DDL操作的性能在很大程度上取决于该操作是否立即执行,是否在原地执行以及是否重建表。
为了评估一个DDL操作的相对表现,可以比较使用的结果ALGORITHM=INSTANT
,ALGORITHM=INPLACE
和ALGORITHM=COPY
。也可以old_alter_table
启用启用强制运行的语句ALGORITHM=COPY
。
对于修改表数据的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处理,请检查Performance Schema和
INFORMATION_SCHEMA
与InnoDB
DDL操作之前和之后相关的表,以参见物理读取,写入,内存分配等的数量。性能架构阶段事件可用于监视
ALTER TABLE
进度。请参见“使用性能模式监视InnoDB表的ALTER TABLE进度”。
因为记录并发DML操作所做的更改涉及一些处理工作,然后在结束时应用这些更改,所以在线DDL操作可能比阻止其他会话访问表的表复制机制花费更长的时间。对于使用该表的应用程序,原始性能的下降与更好的响应能力之间取得了平衡。在评估更改表结构的技术时,请根据诸如网页加载时间之类的因素,考虑最终用户对性能的看法。