• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 在线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:初始化

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

    • 阶段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 t1 ADD 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=INSTANTALGORITHM=INPLACEALGORITHM=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操作之前,请按照以下步骤检查操作是快速还是慢速:

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

    为了更好地了解与DDL操作相关的MySQL处理,请检查Performance Schema和INFORMATION_SCHEMAInnoDBDDL操作之前和之后相关的表,以参见物理读取,写入,内存分配等的数量。

    性能架构阶段事件可用于监视ALTER TABLE进度。请参见“使用性能模式监视InnoDB表的ALTER TABLE进度”。

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