MySQL Online DDL 原理

1. MySQL Online DDL 原理

目录

• 1. MySQL Online DDL 原理
  • 1.1. 前言
  • 1.2. 用法
    • 1.2.1. 参数选择
    • 1.2.2. 适用场景（来自8.0）
    • 1.2.3. 不适用的DDL
  • 1.3. 执行过程
  • 1.4. 总结：

1.1. 前言

MySQL 的 DDL(Data Definition Language) 包括增减字段、增减索引等操作。在 MySQL 5.6 之前，MySQL 的 DDL 操作会按照原来的表复制一份，并做相应的修改，例如，对表 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 都将阻塞无法提供服务。

因此，MySQL 5.6 增加了 Online DDL，允许在不中断数据库服务的情况下进行 DDL 操作。

1.2. 用法

ALTER TABLE tbl\_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

1.2.1. 参数选择

ALGORITHM的取值：INPLACE / COPY / DAFAULT

• copy创建临时表（锁表）
• inplace（不影响DML）
• default默认（根据具体操作类型自动选择）
• INSTANT只修改元数据（8.0新增，在修改名字等极少数情况可用）

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 / SHARED / DAFAULT / exclusive

• LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景）
• SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景） - - - DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）

1.2.2. 适用场景（来自8.0）

- Index Operations
- Primary Key Operations
- Column Operations
- Generated Column Operations
- Foreign Key Operations
- Table Operations
- Tablespace Operations
- Partitioning Operations

详情参考：
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

• Index Operations
  下表概述了对索引操作的在线 DDL 支持。 星号表示附加信息、异常或依赖项。 有关详细信息，请参阅语法和使用说明。
  

1.2.3. 不适用的DDL

不过并不是所有的 DDL 操作都能用 INPLACE 的方式执行，具体的支持情况可以在 MySQL Reference Manual — Online DDL Operations 中查看。
例如 Table 14.10 中显示修改列的数据类型不支持 INPLACE

这时尝试将原类型为 FLOAT 的 column_name 改为 INT

ALTER TABLE tbl\_name MODIFY COLUMN column\_name INT, ALGORITHM=INPLACE, LOCK=NONE;

会报错
ERROR: 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

1.3. 执行过程

1. 初始化：根据存储引擎、用户指定的操作、用户指定的 ALGORITHM 和 LOCK 计算 DDL 过程中允许的并发量，这个过程中会获取一个 shared metadata lock，用来保护表的结构定义
2. 执行 DDL：根据第一步的情况决定是否将 shared metadata lock 升级为 exclusive metadata lock（仅在语句准备阶段），然后生成语句并执行。执行期间的 shared metadata lock 保证了不会同时执行其他的 DDL，但 DML 能可以正常执行
3. 提交：将 shared metadata lock 升级为 exclusive metadata lock，然后删除旧的表定义，提交新的表定义

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

不过，在 DDL 执行前或执行时，其他事务可以获取 MDL。由于需要用到 exclusive MDL，所以必须要等到其他占有 metadata lock 的事务提交或回滚后才能执行上面两个涉及到 MDL 的地方。

实例：
前面提到 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 只有在事务结束后才会被释放。

1.4. 总结：

在线DDL还是可能会导致锁，因为要获取（Waiting for table metadata lock ）DML锁，所以，如果表上已经有锁，然后没有释放的话，DDL就无法获取到对应的锁，然后导致死锁。

添加索引和字段建议使用PT工具