关于mysql:MySQL-DDL执行方式Online-DDL介绍

1 引言

大家好，明天与大家一起分享一下 mysql DDL 执行形式。

一般来说 MySQL 分为 DDL（定义）和 DML（操作）。

• DDL：Data Definition Language，即数据定义语言，那相干的定义操作就是 DDL，包含：新建、批改、删除等；相干的命令有：CREATE，ALTER，DROP，TRUNCATE 截断表内容（开发期，还是挺罕用的），COMMENT 为数据字典增加备注。
• DML：Data Manipulation Language，即数据操作语言，即解决数据库中数据的操作就是 DML，包含：选取，插入，更新，删除等；相干的命令有：SELECT,INSERT,UPDATE,DELETE，还有 LOCK TABLE，以及不罕用的 CALL – 调用一个 PL/SQL 或 Java 子程序，EXPLAIN PLAN – 解析剖析数据拜访门路。

咱们能够认为：

• CREATE，ALTER，DROP，TRUNCATE，定义相干的命令就是 DDL;
• SELECT，INSERT，UPDATE，DELETE，操作解决数据的命令就是 DML；

DDL、DML 区别：

• DML 操作是能够手动管制事务的开启、提交和回滚的。
• DDL 操作是隐性提交的，不能 rollback，肯定要审慎哦！

日常开发咱们对一条 DML 语句较为相熟，很多开发人员都理解 sql 的执行过程，比拟相熟，然而 DDL 是如何执行的呢，大部分开发人员可能不太关怀，也认为没必要理解，都交给 DBA 吧。其实不然，理解一些能尽量避开一些 ddl 的坑，那么上面带大家一起理解一下 DDL 执行的形式，也算抛砖引玉吧。如有谬误，还请各位大佬们斧正。

2 概述

在 MySQL 应用过程中，依据业务的需要对表构造进行变更是个广泛的运维操作，这些称为 DDL 操作。常见的 DDL 操作有在表上减少新列或给某个列增加索引。

咱们罕用的易维平台提供了两种形式可执行 DDL，包含 MySQL 原生在线 DDL（online DDL）以及一种第三方工具 pt-osc。

下图是执行形式的性能比照及阐明：

本文将对 DDL 的执行工具之 Online DDL 进行简要介绍及剖析，pt-osc 会专门再进行介绍。

3 介绍

MySQL Online DDL 性能从 5.6 版本开始正式引入，倒退到当初的 8.0 版本，经验了屡次的调整和欠缺。其实早在 MySQL 5.5 版本中就退出了 INPLACE DDL 形式，然而因为实现的问题，仍然会阻塞 INSERT、UPDATE、DELETE 操作，这也是 MySQL 晚期版本长期被吐槽的起因之一。

在 MySQL 5.6 版本以前，最低廉的数据库操作之一就是执行 DDL 语句，特地是 ALTER 语句，因为在批改表时，MySQL 会阻塞整个表的读写操作。例如，对表 A 进行 DDL 的具体过程如下：

1. 依照表 A 的定义新建一个表 B
2. 对表 A 加写锁
3. 在表 B 上执行 DDL 指定的操作
4. 将 A 中的数据拷贝到 B
5. 开释 A 的写锁
6. 删除表 A
7. 将表 B 重命名为 A

在以上 2-4 的过程中，如果表 A 数据量比拟大，拷贝到表 B 的过程会耗费大量工夫，并占用额定的存储空间。此外，因为 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无奈提供服务。

如果遇到微小的表，可能须要几个小时能力执行实现，势必会影响应用程序，因而须要对这些操作进行良好的布局，以防止在顶峰时段执行这些更改。对于那些要提供全天候服务（24*7）或保护工夫无限的人来说，在大表上执行 DDL 无疑是一场真正的噩梦。

因而，MySQL 官网一直对 DDL 语句进行加强，自 MySQL 5.6 起，开始反对更多的 ALTER TABLE 类型操作来防止数据拷贝，同时反对了在线上 DDL 的过程中不阻塞 DML 操作，真正意义上的实现了 Online DDL，即在执行 DDL 期间容许在不中断数据库服务的状况下执行 DML(insert、update、delete)。然而并不是所有的 DDL 操作都反对在线操作。到了 MySQL 5.7，在 5.6 的根底上又减少了一些新的个性，比方：减少了重命名索引反对，反对了数值类型长度的增大和减小，反对了 VARCHAR 类型的在线增大等。然而根本的实现逻辑和限度条件相比 5.6 并没有大的变动。

4 用法

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

ALTER 语句中能够指定参数 ALGORITHM 和 LOCK 别离指定 DDL 执行的算法模式和 DDL 期间 DML 的锁管制模式。

• ALGORITHM=INPLACE 示意执行 DDL 的过程中不产生表拷贝，过程中容许并发执行 DML（INPLACE 不须要像 COPY 一样占用大量的磁盘 I / O 和 CPU，缩小了数据库负载。同时缩小了 buffer pool 的应用，防止 buffer pool 中原有的查问缓存被大量删除而导致的性能问题）。
• 如果设置 ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的形式，采纳表拷贝的形式进行，过程中会阻塞所有的 DML。另外也能够设置 ALGORITHEM=DAFAULT，让 MySQL 以尽量保障 DML 并发操作的准则抉择执行形式。
• LOCK=NONE 示意对 DML 操作不加锁，DDL 过程中容许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的申请，实用于须要尽快实现 DDL 或者服务库闲暇的场景）、SHARED（容许 SELECT，然而阻塞 INSERT UPDATE DELETE，实用于数据仓库等能够容许数据写入提早的场景）和 DEFAULT（依据 DDL 的类型，在保障最大并发的准则下来抉择 LOCK 的取值）。

5 两种算法

第一种 Copy：

1. 依照原表定义创立一个新的长期表；
2. 对原表加写锁（禁止 DML，容许 select）；
3. 在步骤 1 建设的长期表执行 DDL；
4. 将原表中的数据 copy 到长期表；
5. 开释原表的写锁；
6. 将原表删除，并将长期表重命名为原表。
7. 从上可见，采纳 copy 形式期间须要锁表，禁止 DML，因而是非 Online 的。比方：删除主键、批改列类型、批改字符集，这些操作会导致行记录格局发生变化（无奈通过全量 + 增量实现 Online）。

第二种 Inplace：

在原表上进行更改，不须要生成长期表，不须要进行数据 copy 的过程。依据是否行记录格局，又可分为两类：

• rebuild：须要重建表（从新组织聚簇索引）。比方 optimize table、增加索引、增加 / 删除列、批改列 NULL/NOT NULL 属性等；
• no-rebuild：不须要重建表，只须要批改表的元数据，比方删除索引、批改列名、批改列默认值、批改列自增值等。
• 对于 rebuild 形式实现 Online 是通过缓存 DDL 期间的 DML，待 DDL 实现之后，将 DML 利用到表上来实现的。例如，执行一个 alter table A engine=InnoDB; 重建表的 DDL 其大抵流程如下：
• 建设一个临时文件，扫描表 A 主键的所有数据页；
• 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
• 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
• 临时文件生成后，将日志文件中的操作利用到临时文件，失去一个逻辑数据上与表 A 雷同的数据文件；
• 用临时文件替换表 A 的数据文件。

阐明：

1. 在 copy 数据到新表期间，在原表上是加的 MDL 读锁（容许 DML，禁止 DDL）；
2. 在利用增量期间对原表加 MDL 写锁（禁止 DML 和 DDL）；
3. 依据表 A 重建进去的数据是放在 tmp_file 里的，这个临时文件是 InnoDB 在外部创立进去的，整个 DDL 过程都在 InnoDB 外部实现。对于 server 层来说，没有把数据移动到长期表，是一个原地操作，这就是”inplace”名称的起源。

应用 Inplace 形式执行的 DDL，产生谬误或被 kill 时，须要肯定工夫的回滚期，执行工夫越长，回滚工夫越长。

应用 Copy 形式执行的 DDL，须要记录过程中的 undo 和 redo 日志，同时会耗费 buffer pool 的资源，效率较低，长处是能够疾速进行。

不过并不是所有的 DDL 操作都能用 INPLACE 的形式执行，具体的反对状况能够在（在线 DDL 操作 ) 中查看。

以下是常见 DDL 操作：

官网反对列表：

6 执行过程

Online DDL 次要包含 3 个阶段，prepare 阶段，ddl 执行阶段，commit 阶段。上面将次要介绍 ddl 执行过程中三个阶段的流程。

1）Prepare 阶段：初始化阶段会依据存储引擎、用户指定的操作、用户指定的 ALGORITHM 和 LOCK 计算 DDL 过程中容许的并发量，这个过程中会获取一个 shared metadata lock，用来爱护表的构造定义。

• 创立新的长期 frm 文件 (与 InnoDB 无关)。
• 持有 EXCLUSIVE-MDL 锁，禁止读写。
• 依据 alter 类型，确定执行形式 (copy,online-rebuild,online-norebuild)。如果是 Add Index，则抉择 online-norebuild 即 INPLACE 形式。
• 更新数据字典的内存对象。
• 调配 row_log 对象来记录增量 (仅 rebuild 类型须要)。
• 生成新的长期 ibd 文件 (仅 rebuild 类型须要)。
• 数据字典上提交事务、开释锁。

注：Row log 是一种独占构造，它不是 redo log。它以 Block 的形式治理 DML 记录的寄存，一个 Block 的大小为由参数 innodb_sort_buffer_size 管制，默认大小为 1M，初始化阶段会申请两个 Block。

2）DDL 执行阶段：执行期间的 shared metadata lock 保障了不会同时执行其余的 DDL，但 DML 能能够失常执行。

• 降级 EXCLUSIVE-MDL 锁，容许读写（copy 不可写）。
• 扫描 old_table 的汇集索引每一条记录 rec。
• 遍历新表的汇集索引和二级索引，逐个解决。
• 依据 rec 结构对应的索引项
• 将结构索引项插入 sort_buffer 块排序。
• 将 sort_buffer 块更新到新的索引上。
• 记录 ddl 执行过程中产生的增量 (仅 rebuild 类型须要)
• 重放 row_log 中的操作到新索引上 (no-rebuild 数据是在原表上更新的)。
• 重放 row_log 间产生 dml 操作 append 到 row_log 最初一个 Block。

3）Commit 阶段：将 shared metadata lock 降级为 exclusive metadata lock，禁止 DML，而后删除旧的表定义，提交新的表定义。

• 以后 Block 为 row_log 最初一个时，禁止读写，降级到 EXCLUSIVE-MDL 锁。
• 重做 row_log 中最初一部分增量。
• 更新 innodb 的数据字典表。
• 提交事务 (刷事务的 redo 日志)。
• 批改统计信息。
• rename 长期 idb 文件，frm 文件。
• 变更实现。

Online DDL 过程中占用 exclusive MDL 的步骤执行很快，所以简直不会阻塞 DML 语句。

不过，在 DDL 执行前或执行时，其余事务能够获取 MDL。因为须要用到 exclusive MDL，所以必须要等到其余占有 metadata lock 的事务提交或回滚后能力执行下面两个波及到 MDL 的中央。

7 踩坑

后面提到 Online DDL 执行过程中须要获取 MDL，MDL (metadata lock) 是 MySQL 5.5 引入的表级锁，在拜访一个表的时候会被主动加上，以保障读写的正确性。当对一个表做 DML 操作的时候，加 MDL 读锁；当做 DDL 操作时候，加 MDL 写锁。

为了在大表执行 DDL 的过程中同时保障 DML 能并发执行，后面应用了 ALGORITHM=INPLACE 的 Online DDL，但这里依然存在死锁的危险，问题就出在 Online DDL 过程中须要 exclusive MDL 的中央。

例如，Session 1 在事务中执行 SELECT 操作，此时会获取 shared MDL。因为是在事务中执行，所以这个 shared MDL 只有在事务完结后才会被开释。

# Session 1> START TRANSACTION;> SELECT * FROM tbl_name;# 失常执行 

这时 Session 2 想要执行 DML 操作也只须要获取 shared MDL，依然能够失常执行。

# Session 2> SELECT * FROM tbl_name;# 失常执行 

但如果 Session 3 想执行 DDL 操作就会阻塞，因为此时 Session 1 曾经占用了 shared MDL，而 DDL 的执行须要先获取 exclusive MDL，因而无奈失常执行。

# Session 3> ALTER TABLE tbl_name ADD COLUMN n INT;# 阻塞 

通过 show processlist 能够看到 ALTER 操作正在期待 MDL。

因为 exclusive MDL 的获取优先于 shared MDL，后续尝试获取 shared MDL 的操作也将会全副阻塞

+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| Id | User            | Host             | db   | Command | Time | State                           | Info            |│----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| 11 | root            | 172.17.0.1:53048 | demo | Query   |    3 | Waiting for table metadata lock | alter table ... |+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+

到这一步，后续无论是 DML 和 DDL 都将阻塞，直到 Session 1 提交或者回滚，Session 1 占用的 shared MDL 被开释，前面的操作能力继续执行。

下面这个问题次要有两个起因：

1. Session 1 中的事务没有及时提交，因而阻塞了 Session 3 的 DDL
2. Session 3 Online DDL 阻塞了后续的 DML 和 DDL

对于问题 1，有些 ORM 框架默认将用户语句封装成事务执行，如果客户端程序中断退出，还没来得及提交或者回滚事务，就会呈现 Session 1 中的状况。那么此时能够在 infomation_schema.innodb_trx 中找出未实现的事务对应的线程，并强制退出。

> SELECT * FROM information_schema.innodb_trx\G*************************** 1. row ***************************trx_id: 421564480355704trx_state: RUNNINGtrx_started: 2022-05-01 014:49:41trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 9trx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 0trx_lock_structs: 0trx_lock_memory_bytes: 1136trx_rows_locked: 0trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0trx_schedule_weight: NULL1 row in set (0.0025 sec)

能够看到 Session 1 正在执行的事务对应的 trx_mysql_thread_id 为 9，而后执行 KILL 9 即可中断 Session 1 中的事务。
对于问题 2，在查问很多的状况下，会导致阻塞的 session 迅速增多，对于这种状况，能够先中断 DDL 操作，避免对服务造成过大的影响。也能够尝试在从库上批改表构造后进行主从切换或者应用 pt-osc 等第三方工具。

8 限度

• 仅实用于 InnoDB（语法上它能够与其余存储引擎一起应用，如 MyISAM，但 MyISAM 只容许 algorithm = copy，与传统办法雷同）；
• 无论应用何种锁（NONE，共享或排它），在开始和完结时都须要一个短暂的工夫来锁表（排它锁）；
• 在增加 / 删除外键时，应该禁用 foreign_key_checks 以防止表复制；
• 依然有一些 alter 操作须要 copy 或 lock 表（老办法），无关哪些表更改须要表复制或表锁定，请查看官网；
• 如果在表上有 ON … CASCADE 或 ON … SET NULL 束缚，则在 alter table 语句中不容许 LOCK = NONE；
• Online DDL 会被复制到从库（同主库一样，如果 LOCK = NONE，从库也不会加锁），但复制自身将被阻止，因为 alter 在从库以单线程执行，这将导致主从提早问题。

官网参考资料：MySQL :: MySQL 5.7 Reference Manual :: 14.13.6 Online DDL Limitations

9 总结

本次和大家一起理解 SQL 的 DDL、DML 及区别，也介绍了 Online DDL 的执行形式。

目前可用的 DDL 操作工具包含 pt-osc，github 的 gh-ost，以及 MySQL 提供的在线批改表构造命令 Online DDL。pt-osc 和 gh-ost 均采纳拷表形式实现，即创立个空的新表，通过 select+insert 将旧表中的记录逐次读取并插入到新表中，不同之处在于解决 DDL 期间业务对表的 DML 操作。

到了 MySQL 8.0 官网也对 DDL 的实现从新进行了设计，其中一个最大的改良是 DDL 操作反对了原子个性。另外，Online DDL 的 ALGORITHM 参数减少了一个新的选项：INSTANT，只需批改数据字典中的元数据，无需拷贝数据也无需重建表，同样也无需加排他 MDL 锁，原表数据也不受影响。整个 DDL 过程简直是霎时实现的，也不会阻塞 DML，不过目前 8.0 的 INSTANT 应用范畴较小，后续再对 8.0 的 INSTANT 做具体介绍吧。

另外，易维平台也提供了 pt-osc 的执行形式，下次再与大家一起分享 pt-osc 的执行形式吧，敬请期待！

作者：刘邓忠