MySQL Online DDL

在 MySQL5.6 中，支持更多的 alter table 类型操作来避免 copy data，同时支持了在线上 DDL 的过程中不阻塞 DML 操作，真正意义上的实现了 Online DDL。
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

Online DDL功能为就地表更改和并发DML提供支持。此功能的优点包括：

• 在繁忙的生产环境中提高响应速度和可用性，因为生产环境无法使表在数分钟或数小时内不可用是不切实际的。
• 使用该LOCK子句在DDL操作期间调整性能和并发之间的平衡的能力 。
• 与表复制方法相比，磁盘空间使用量和I / O开销更少。

online DDL改进了MySQL操作的几个方面：

• 访问表的应用程序响应速度更快，因为在进行DDL操作时可以继续进行表上的查询和DML操作。减少锁定和等待MySQL服务器资源可以带来更大的可伸缩性，即使对于DDL操作中不涉及的操作也是如此。
• 就地操作避免了与表复制方法相关的磁盘I / O和CPU周期，从而最大程度地减少了数据库的总体负载。最小化负载有助于在DDL操作期间保持良好的性能和高吞吐量。
• 就地操作比表复制操作将更少的数据读入缓冲池，这减少了从内存中清除经常访问的数据。在DDL操作之后，清除频繁访问的数据可能会导致性能暂时下降。

通常，无需执行任何特殊操作即可启用在线DDL。默认情况下，MySQL会在允许的情况下在适当的位置执行操作，并尽可能减少锁定。
可以使用语句的ALGORITHM和LOCK子句控制DDL操作：
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
LOCK语句用于调优并发访问程度，ALGORITHM语句用于性能比较，并在遇到任何问题时作为对较早的表复制行为的后备。例如：

• 为避免意外使该表不可用于读取或写入，请在 ALTER TABLE语句上指定一个子句， 例如 LOCK=NONE（允许读取和写入）或 LOCK=SHARED（允许读取）。如果请求的并发级别不可用，该操作将立即停止。
• 要比较性能，请使用ALGORITHM=INPLACE和ALGORITHM=COPY进行声明。在old_alter_table禁用和启用配置选项的情况下运行语句 。
• 为避免使用ALTER TABLE复制表的操作绑定服务器，请包括 ALGORITHM=INPLACE。如果该语句不能使用就地机制，则会立即暂停。

online DDL操作

空间要求

• 临时日志文件的空间：
  创建索引或更改表时，临时日志文件记录并发DML。临时日志文件将根据需要扩展，innodb_sort_buffer_size（默认是1M）最大值由指定 innodb_online_alter_log_max_size（默认值128M）。根据测试经验，通常 512M 是比较推荐的值。好在该参数是动态的，可以基于会话级别进行调整。在进行在线索引添加操作时，数据库性能会有 20～30%的下降。
  如果临时日志文件超过大小限制，则联机DDL操作将失败，并且未提交的并发DML操作将回滚。较大的 innodb_online_alter_log_max_size 设置允许在联机DDL操作期间使用更多DML，但是当表被锁定以应用记录的DML时，它也会延长DDL操作结束时的时间。
  如果该操作花费很长时间，并且并行DML修改了表，以至于临时日志文件的大小超过的值 innodb_online_alter_log_max_size ，则online DDL操作将失败并显示 DB_ONLINE_LOG_TOO_BIG错误。

• 临时排序文件的空间
  重建表的在线DDL操作将临时排序文件写入MySQL临时目录（$TMPDIR在Unix，%TEMP% Windows或Windows Server 2003指定的目录中）—tmpdir ）。临时排序文件不在包含原始表的目录中创建。每个临时排序文件都足够大，可以容纳一列数据，并且每个排序文件的数据合并到最终表或索引中时都将被删除。::涉及临时排序文件的操作可能需要的临时空间等于表中的数据量加上索引。::如果联机DDL操作使用了数据目录所在的文件系统上的所有可用磁盘空间，则会报告错误。
  如果MySQL临时目录的大小不足以容纳排序文件，请设置 tmpdir为其他目录。或者，使用定义一个单独的临时目录以进行联机DDL操作 innodb_tmpdir 。MySQL 5.6.29中引入了此选项，以帮助避免因大型临时排序文件而导致的临时目录溢出。

• 中间表文件的空间
  一些重建表的在线DDL操作会在与原始表相同的目录中创建一个临时中间表文件。::中间表文件可能需要的空间等于原始表的大小。中间表文件名以#sql-ib前缀开头，并且仅在联机DDL操作期间短暂出现。:: innodb_tmpdir选项不适用于中间表文件。

online DDL的限制

以下限制适用于联机DDL操作：

• 在上创建索引时，将复制该表 作为一个临时表。

• 如果存在ON…CASCADE或ON…SET NULL约束的表，ALTER TABLE语句中的 LOCK=NONE不被允许。

• 在联机DDL操作完成之前，必须等待持有表上元数据锁的事务提交或回滚。在线DDL操作可能在执行阶段短暂地要求对表进行排他元数据锁定，而在更新表定义时始终要求在操作的最后阶段进行锁定。因此，在表上持有元数据锁的事务可能导致在线DDL操作阻塞。在表上保留元数据锁的事务可能已经在联机DDL操作之前或期间启动。在表上持有元数据锁定的长时间运行或非活动事务可能导致联机DDL操作超时。

• 在外键关系中的表上执行onlie DDL操作不会等待在外键关系中的另一个表上执行的事务提交或回滚。事务在正在更新的表上拥有排他元数据锁，并在与外键相关的表上拥有共享元数据锁（用于外键检查）。::当需要排他的元数据锁来更新表定义时，共享的元数据锁允许继续进行在线DDL操作，但在最后阶段阻止该操作。当其他事务等待联机DDL操作完成时，此方案可能导致死锁。::

• 运行联机DDL操作时，ALTER TABLE语句的线程将 应用 DML操作的online log，DML操作是在并发的其他线程上对同一表上运行的。应用DML操作时，即使重复输入只是临时的，还是会被online log中的后续输入还原，也可能会遇到重复的键输入错误（错误1062（23000）：重复的输入）。这类似于外键约束检查的想法，InnoDB在该检查中，约束必须在一个事务期间保持。

• OPTIMIZE TABLE用于将InnoDB表映射到ALTER TABLE用于重建表并更新索引统计信息并释放聚簇索引中未使用的空间的操作。在5.6.17之前，此操作没有在线DDL支持。创建辅助索引的效率不高，因为键是按照它们在主键中出现的顺序插入的。从5.6.17开始，OPTIMIZE TABLE支持在线DDL，以重建常规InnoDB表和分区表。

• MySQL的5.6之前创建包括时间列的表（ DATE ，DATETIME或TIMESTAMP ），并没有使用ALGORITHM=COPY重建，不支持ALGORITHM=INPLACE。在这种情况下，ALTER TABLE … ALGORITHM=INPLACE操作将返回以下错误：
  ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
  Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

• 以下限制通常适用于涉及重建表的大型表上的联机DDL操作：

  • 没有任何机制可以暂停联机DDL操作或限制联机DDL操作的I/O或CPU使用率。
  • 在线DDL操作的进度监视功能仅限于MySQL 5.7.6，该版本引入了用于监视 ALTER TABLE进度的Performance Schema阶段事件 。
    请参阅 使用性能模式监视InnoDB表的ALTER TABLE进度 。
  • 如果操作失败，则回滚在线DDL操作可能会非常昂贵。
  • 长时间运行的联机DDL操作可能导致复制滞后。联机DDL操作必须先在源上运行，然后才能在副本上运行。另外，在源上并发处理的DML仅在副本上的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和元数据锁

online DDL操作可以视为具有三个阶段：

• 阶段1：初始化
  在初始化阶段，服务器将考虑存储引擎功能，语句中指定的操作以及用户指定的ALGORITHM和LOCK 选项，以确定在操作期间允许多少并发 。在此阶段，将使用共享的可升级元数据锁来保护当前表定义。
• 阶段2：执行
  在此阶段，准备并执行该语句。元数据锁是否升级到独占取决于初始化阶段评估的因素。如果需要独占元数据锁，则仅在语句准备期间进行短暂锁定。
• 阶段3：提交表定义
  在提交表定义阶段，将元数据锁升级为独占，以退出旧表定义并提交新表定义。一旦被授予，独占元数据锁定的持续时间就很短。

由于上面概述的排他性元数据锁定要求，在线DDL操作可能必须等待持有表上元数据锁定的并发事务才能提交或回滚。在DDL操作之前或期间启动的事务可以将元数据锁保存在要更改的表上。如果事务长期运行或处于非活动状态，则在线DDL操作可能会超时，等待独占元数据锁定。 _{此外，在线DDL操作请求的待处理独占元数据锁定会阻止表上的后续事务。}

以下示例演示了等待独占元数据锁定的联机DDL操作，以及未决元数据锁定如何阻止表上的后续事务。
会话1：

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

会话1 SELECT 语句 对表t1 进行 共享元数据锁定。

会话2:

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

会话2中的联机DDL操作需要在表t1上具有 排他的元数据锁定 才能提交表定义更改，该操作必须等待会话1事务提交或回滚。

会话3:

mysql> SELECT * FROM t1;

SELECT 会话3中发出 的语句被阻塞，等待 ALTER TABLE 会话2中的操作请求的 互斥元数据锁 被授予。

此时，show 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)

online DDL的性能

DDL操作的性能在很大程度上取决于是否在适当位置执行该操作以及是否重建该表。
要评估DDL操作的相对性能，您可以将使用ALGORITHM=INPLACE的结果与使用ALGORITHM=COPY的结果进行比较。或者，可以比较 old_alter_table禁用和启用的结果 。
对于修改表数据的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操作。

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

tips

使用 ALTER TABLE … LOCK=NONE 来强制使用在线 DDL 操作，若操作不支持，则会抛出异常，如：ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED.

附上一个详细对比MySQL5.6和5.7版本的Online DDL操作的文章：MySQL Online DDL - 云+社区 - 腾讯云