Mysql Innodb在线DDL原理

Mysql Innodb在线DDL原理

昨晚在生产环境执行DDL时，数据库发现出现大量锁等待，数据库链接一直释放不了，CPU升高，数据库差一点宕机，业务应用基本不可用，吓尿。

Waiting for table metadata lock

下面，结合mysql官方文档，仔细研究一下mysql的在线ddl功能。

MySQL :: MySQL 5.7 Reference Manual :: 14.13 InnoDB and Online DDL

mysql支持在线ddl其实是innodb存储引擎支持的功能。

Innodb 在线DDL原理

示例：

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

普通的DDL语句后面可以通过添加ALGORITHM和LOCK来控制DDL语句的执行算法和加锁方式。

LOCK语句

看一下官网的说明：

默认情况下，MySQL 在 DDL 操作期间使用尽可能少的锁定。LOCK如果需要，可以指定该子句以强制执行更严格的锁定。如果LOCK子句指定的锁定级别低于特定 DDL 操作所允许的限制级别，则语句将失败并出现错误。 LOCK条款描述如下，按照从最少到最多限制的顺序：

LOCK=NONE：允许并发查询和 DML。比较爽，执行DDL的时候，可以对表进行查询和DML操作。例如，将此子句用于涉及客户注册或购买的表，以避免在冗长的 DDL 操作期间使表不可用。

LOCK=SHARED：允许并发查询但阻止 DML。例如，在数据仓库表上使用此子句，您可以将数据加载操作延迟到 DDL 操作完成，但不能长时间延迟查询。

LOCK=DEFAULT：允许尽可能多的并发（并发查询、DML 或两者兼有）。省略LOCK子句与指定LOCK=DEFAULT.当您知道 DDL 语句的默认锁定级别不会导致表的可用性问题时，请使用此子句。

LOCK=EXCLUSIVE：阻止并发查询和 DML。如果主要关注的是在尽可能短的时间内完成 DDL 操作，并且不需要并发查询和 DML 访问，则使用此子句。如果服务器应该空闲，您也可以使用此子句，以避免意外的表访问。

ALGORITHM语句

取值范围有 DEFAULT | INPLACE | COPY

不同的DDL操作类型对于ALGORITHM有不同的含义，大致应该是INPLACE是在原表操作，COPY是拷贝一份新表，然后对于新表操作DDL 将旧表数据迁移到新表。

元数据锁

元数据锁的概念

https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html

MySQL 使用元数据锁定来管理对数据库对象的并发访问并确保数据一致性。元数据锁定不仅适用于表，还适用于模式、存储程序（过程、函数、触发器、调度事件）、表空间、使用 GET_LOCK()函数获取的用户锁。

元数据锁获取

如果给定锁有多个等待者，则首先满足最高优先级的锁请求，但与 max_write_lock_count系统变量相关的异常除外。写锁请求的优先级高于读锁请求。但是，如果 max_write_lock_count设置为某个较低的值（例如，10），如果读锁定请求已经被传递给 10 个写锁定请求，则读锁定请求可能优于挂起的写锁定请求。通常不会发生此行为，因为 max_write_lock_count默认情况下具有非常大的值。

语句一个一个地获取元数据锁，而不是同时获取，并在过程中进行死锁检测。

DML 语句通常按照语句中提及表的顺序获取锁。

DDL 语句LOCK TABLES和其他类似语句尝试通过按名称顺序获取显式命名表上的锁来减少并发 DDL 语句之间可能出现的死锁数量。对于隐式使用的表（例如也必须锁定的外键关系中的表），可能会以不同的顺序获取锁。

元数据锁释放

为了确保事务可串行化，服务器不得允许一个会话对另一个会话中未完成的显式或隐式启动事务中使用的表执行数据定义语言 (DDL) 语句。服务器通过获取事务中使用的表上的元数据锁并将这些锁的释放推迟到事务结束时来实现这一点。表上的元数据锁可防止更改表的结构。这种锁定方法意味着一个会话中的事务正在使用的表在事务结束之前不能由其他会话在 DDL 语句中使用。

这个原则不仅适用于事务表，也适用于非事务表。假设一个会话开始一个使用事务表 t和非 事务表的事务nt，如下所示：

START TRANSACTION;

SELECT * FROM t;

SELECT * FROM nt;

服务器在这两个上都持有元数据锁t ，nt直到事务结束。如果另一个会话尝试对任一表执行 DDL 或写入锁定操作，它会阻塞直到在事务结束时释放元数据锁定。例如，如果第二个会话尝试以下任何操作，则它会阻塞：

DROP TABLE t;

ALTER TABLE t ...;

DROP TABLE nt;

ALTER TABLE nt ...;

LOCK TABLE t ... WRITE;

相同的行为适用于 The LOCK TABLES ... READ。也就是说，显式或隐式启动的事务会更新任何表（事务性或非事务性）阻塞并被LOCK TABLES ... READ该表阻塞。

如果服务器获取语法上有效但在执行期间失败的语句的元数据锁，它不会提前释放锁。锁释放仍然延迟到事务结束，因为失败的语句被写入二进制日志并且锁保护了日志的一致性。

在自动提交模式下，每个语句实际上都是一个完整的事务，因此为语句获取的元数据锁只保留到语句的末尾。

PREPARE一旦准备好语句，即使准备发生在多语句事务中，在语句 期间获取的元数据锁 也会被释放。

在线DDL执行过程

看一下官方的说明：

在线 DDL 操作可以被视为具有三个阶段：

第一阶段：初始化

在初始化阶段，服务器根据存储引擎能力、语句中指定的操作以及用户指定的选项来确定操作期间允许的并发 ALGORITHM和LOCK 。在此阶段，将采用共享的可升级元数据锁来保护当前表定义。

第二阶段：执行

在此阶段，准备并执行语句。元数据锁是否升级为独占取决于初始化阶段评估的因素。如果需要独占元数据锁，则仅在语句准备期间短暂使用。

第三阶段：提交表定义

在提交表定义阶段，元数据锁升级为独占，以逐出旧表定义并提交新表定义。一旦授予，独占元数据锁定的持续时间很短。由于上面概述的独占元数据锁要求，在线 DDL 操作可能必须等待在表上持有元数据锁的并发事务提交或回滚。在 DDL 操作之前或期间启动的事务可以持有正在更改的表上的元数据锁。在长时间运行或非活动事务的情况下，在线 DDL 操作可能会超时等待独占元数据锁定。此外，在线 DDL 操作请求的未决独占元数据锁会阻塞表上的后续事务。

上述说明非常重要，执行在线DDL需要获取元数据锁，元数据锁肯能会被其他事物而不能释放，那么当前DDL会一直堵塞该表请求元数据锁，并且会堵塞后续对于该表的所有请求，导致数据库连接一直不能释放，连接池打满，CPU升高，致命问题。因此在执行DDL一定要确认当前表是否存在未提交的事务。

可参考博客：

MySQL出现Waiting for table metadata lock的原因以及解决方法 - digdeep - 博客园

场景一：长事物运行，阻塞DDL，继而阻塞所有同表的后续操作。通过show processlist可以看到TableA上有正在进行的操作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。

场景二：未提交事物，阻塞DDL，继而阻塞所有同表的后续操作。通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以在 information_schema.innodb_trx中查看到。在事务没有完成之前，TableA上的锁不会释放，alter table同样获取不到metadata的独占锁。

场景三：通过show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。

以下示例演示了等待独占元数据锁的在线 DDL 操作，以及挂起的元数据锁如何阻止表上的后续事务。

第 1 节：

mysql> CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;

mysql> START TRANSACTION;

mysql> SELECT * FROM t1;

会话 1SELECT语句在表 t1 上获取共享元数据锁。

第 2 节：

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

会话 2 中的在线 DDL 操作需要表 t1 上的独占元数据锁来提交表定义更改，必须等待会话 1 事务提交或回滚。

第三节：

mysql> SELECT * FROM t1;

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

可以使用 SHOW FULL PROCESSLIST来确定事务是否正在等待元数据锁定。

元数据锁信息也通过 Performance Schemametadata_locks 表公开，该表提供有关会话之间的元数据锁依赖关系、会话正在等待的元数据锁以及当前持有元数据锁的会话的信息

在线 DDL 性能

对于修改表数据的 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)

MySQL :: MySQL 5.7 Reference Manual :: 14.17.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema

在线 DDL 空间要求

在线 DDL 操作有以下空间要求：

临时日志文件：

当在线 DDL 操作创建索引或更改表时，临时日志文件会记录并发 DML。临时日志文件根据需要扩展为由 innodb_sort_buffer_size指定的最大值 innodb_online_alter_log_max_size。如果操作耗时较长且并发 DML 对表的修改过多以至于临时日志文件的大小超过 的值 innodb_online_alter_log_max_size，则在线 DDL 操作会失败并 DB_ONLINE_LOG_TOO_BIG出错，并且未提交的并发 DML 操作会回滚。一个大的 innodb_online_alter_log_max_size 设置允许在线 DDL 操作期间更多的 DML，但它也会延长 DDL 操作结束时表被锁定以应用记录的 DML 的时间段。

该innodb_sort_buffer_size 变量还定义了临时日志文件读取缓冲区和写入缓冲区的大小。

临时排序文件：

重建表的在线 DDL 操作将临时排序文件写入 MySQL 临时目录（$TMPDIR在 Unix%TEMP% 上、Windows 上或由 --tmpdir) 在索引创建期间。临时排序文件不会在包含原始表的目录中创建。每个临时排序文件都足够大，可以容纳一列数据，并且每个排序文件在其数据合并到最终表或索引中时都会被删除。涉及临时排序文件的操作可能需要等于表中的数据量加上索引的临时空间。如果在线 DDL 操作使用了数据目录所在文件系统上的所有可用磁盘空间，则会报告错误。

如果 MySQL 临时目录不足以容纳排序文件，请设置tmpdir为不同的目录。或者，使用 .为在线 DDL 操作定义一个单独的临时目录 innodb_tmpdir。此选项在 MySQL 5.7.11 中引入，以帮助避免由于大型临时排序文件而可能发生的临时目录溢出。

中间表文件：

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

该innodb_tmpdir选项不适用于中间表文件。

使用在线 DDL 简化 DDL 语句

在引入在线 DDL之前，将许多 DDL 操作组合到一个ALTER TABLE 语句中是常见的做法。因为每条ALTER TABLE 语句都涉及复制和重建表，所以一次对同一个表进行多次更改会更有效，因为这些更改都可以通过对表的一次重建操作来完成。缺点是涉及 DDL 操作的 SQL 代码更难维护和在不同脚本中重用。如果每次具体更改都不同，您可能必须ALTER TABLE为每个略有不同的场景构建一个新的复合体。

对于可以就地完成的 DDL 操作，您可以将它们分成单独ALTER TABLE 的语句，以便更轻松地编写和维护，而不会牺牲效率。例如，您可能会采用复杂的语句，例如：

ALTER TABLE t1 ADD INDEX i1(c1), ADD UNIQUE INDEX i2(c2),

  CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;

并将其分解为可以独立测试和执行的更简单的部分，例如：

ALTER TABLE t1 ADD INDEX i1(c1);

ALTER TABLE t1 ADD UNIQUE INDEX i2(c2);

ALTER TABLE t1 CHANGE c4_old_name c4_new_name INTEGER UNSIGNED NOT NULL;

您可能仍将多部分ALTER TABLE语句用于：

必须按特定顺序执行的操作，例如创建索引，然后是使用该索引的外键约束。

操作都使用相同的特定LOCK 子句，您希望作为一个组成功或失败。

无法就地执行的操作，即仍然使用表复制方法的操作。

您指定的操作 ALGORITHM=COPY或 old_alter_table=1，如果需要在特定场景中实现精确的向后兼容性，则强制执行表复制行为。

如果是DDL需要采用复制表的方式来执行，那么DDL合并执行可以来优化性能，避免多次重建表。

在线DDL

是不是所有的DDL都支持在线实施，可以参考一下官方文档

MySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations

inplace 表示不需要重建表执行ddl

rebuilds tableb表示重建表执行ddl

Permits Concurrent DML 表示执行DDL时是否允许DML

Only Modifies Metadata 表示执行DDL时是否允许修改元数据锁

需要关注一下常见的DDL操作，索引增加，列增加，列修改

索引操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Creating or adding a secondary index	Yes	No	Yes	No
Dropping an index	Yes	No	Yes	Yes
Renaming an index	Yes	No	Yes	Yes
Adding a FULLTEXT index	Yes*	No*	No	No
Adding a SPATIAL index	Yes	No	No	No
Changing the index type	Yes	No	Yes	Yes

可知添加 删除 重命名索引是不会锁表的 是可以执行DML的

在创建索引时，该表仍可用于读取和写入操作。该 CREATE INDEX语句仅在访问表的所有事务完成后才结束，以便索引的初始状态反映表的最新内容。

在线 DDL 支持添加二级索引意味着您通常可以通过创建没有二级索引的表，然后在加载数据后添加二级索引来加快创建和加载表和关联索引的整个过程。

CREATE INDEX新创建的二级索引仅包含or ALTER TABLE语句执行完毕 时表中已提交的数据 。它不包含任何未提交的值、旧版本的值或标记为删除但尚未从旧索引中删除的值。

如果服务器在创建二级索引时退出，则在恢复时，MySQL 会删除任何部分创建的索引。您必须重新运行ALTER TABLE orCREATE INDEX语句。

主键操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a primary key	Yes*	Yes*	Yes	No
Dropping a primary key	No	Yes	No	No
Dropping a primary key and adding another	Yes	Yes	Yes	No

重组 聚集索引 总是需要复制表数据。因此，最好在创建表时 定义主键ALTER TABLE ... ADD PRIMARY KEY，而不是稍后发布。当你创建一个UNIQUE或 PRIMARY KEY索引时，MySQL 必须做一些额外的工作。对于UNIQUE索引，MySQL 检查表中是否包含键的重复值。对于PRIMARY KEY索引，MySQL 还检查没有任何PRIMARY KEY 列包含NULL。

一句话主键在建表时就搞好。

列操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a column	Yes	Yes	Yes*	No
Dropping a column	Yes	Yes	Yes	No
Renaming a column	Yes	No	Yes*	Yes
Reordering columns	Yes	Yes	Yes	No
Setting a column default value	Yes	No	Yes	Yes
Changing the column data type	No	Yes	No	No
Extending VARCHAR column size	Yes	No	Yes	Yes
Dropping the column default value	Yes	No	Yes	Yes
Changing the auto-increment value	Yes	No	Yes	No*
Making a column NULL	Yes	Yes*	Yes	No
Making a column NOT NULL	Yes*	Yes*	Yes	No
Modifying the definition of an ENUM or SET column	Yes	No	Yes	Yes

修改字段类型是需要重建表 并且堵塞DML，因此性能不好。

添加字段时是支持inplace或者重建表 ，并且不需要堵塞DML(添加自增列时会堵塞DML)

扩展VARCHAR列大小

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

列所需的长度字节数 VARCHAR必须保持不变。对于VARCHAR大小为 0 到 255 字节的列，需要一个长度字节来对值进行编码。对于VARCHAR 大小为 256 字节或更大的列，需要两个长度字节。因此，就地ALTER TABLE仅支持将 VARCHAR列大小从 0 字节增加到 255 字节，或从 256 字节增加到更大的大小。就地 ALTER TABLE不支持增加 VARCHAR列从小于 256 字节到大小等于或大于 256 字节。在这种情况下，所需的长度字节数从 1 变为 2，仅表副本 ( ALGORITHM=COPY) 支持。不支持VARCHAR使用就地 减小大小。ALTER TABLE减小VARCHAR 大小需要一个表格副本 ( ALGORITHM=COPY)。

Varchar扩展时 如果字节数不变则采用inplace算法，如果字节数发生变化，则必须采用rebuild算法，性能不好，因此字符串长度改变要注意。

生成的列操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a STORED column	No	Yes	No	No
Modifying STORED column order	No	Yes	No	No
Dropping a STORED column	Yes	Yes	Yes	No
Adding a VIRTUAL column	Yes	No	Yes	Yes
Modifying VIRTUAL column order	No	Yes	No	No
Dropping a VIRTUAL column	Yes	No	Yes	Yes

外键操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a foreign key constraint	Yes*	No	Yes	Yes
Dropping a foreign key constraint	Yes	No	Yes	Yes

表操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Changing the ROW_FORMAT	Yes	Yes	Yes	No
Changing the KEY_BLOCK_SIZE	Yes	Yes	Yes	No
Setting persistent table statistics	Yes	No	Yes	Yes
Specifying a character set	Yes	Yes*	No	No
Converting a character set	No	Yes*	No	No
Optimizing a table	Yes*	Yes	Yes	No
Rebuilding with the FORCE option	Yes*	Yes	Yes	No
Performing a null rebuild	Yes*	Yes	Yes	No
Renaming a table	Yes	No	Yes	Yes

表空间操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Enabling or disabling file-per-table tablespace encryption	No	Yes	No	No

分区操作

Partitioning Clause	In Place	Permits DML	Notes
PARTITION BY	No	No	Permits ALGORITHM=COPY, LOCK={DEFAULT|SHARED|EXCLUSIVE}
ADD PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Does not copy existing data for tables partitioned by RANGE or LIST. Concurrent queries are permitted for tables partitioned by HASH or LIST. MySQL copies the data while holding a shared lock.
DROP PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Does not copy existing data for tables partitioned by RANGE or LIST.
DISCARD PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
IMPORT PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
TRUNCATE PARTITION	Yes	Yes	Does not copy existing data. It merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions.
COALESCE PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by HASH or LIST, as MySQL copies the data while holding a shared lock.
REORGANIZE PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by LINEAR HASH or LIST. MySQL copies data from affected partitions while holding a shared metadata lock.
EXCHANGE PARTITION	Yes	Yes
ANALYZE PARTITION	Yes	Yes
CHECK PARTITION	Yes	Yes
OPTIMIZE PARTITION	No	No	ALGORITHM and LOCK clauses are ignored. Rebuilds the entire table. See Section 22.3.4, “Maintenance of Partitions”.
REBUILD PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by LINEAR HASH or LIST. MySQL copies data from affected partitions while holding a shared metadata lock.
REPAIR PARTITION	Yes	Yes
REMOVE PARTITIONING	No	No	Permits ALGORITHM=COPY, LOCK={DEFAULT|SHARED|EXCLUSIVE}

分区表的方式一般都是新建空表，建好分区，然后将旧表数据迁移到新表。

总结：

   在线执行DDL时一定要注意，能不能及时获取到元数据锁，如果不能及时获取，会阻塞后续所有请求，危害很大。可以通过查询当前活动链接以及当前事务来查看是否可以及时获取到元数据锁。