关于mysql:MySQL-MariaDB-Online-DDL-参考指南

概述

在晚期的 MySQL 版本中，DDL 操作（如创立索引等）通常都须要对数据表加锁，操作过程中 DML 操作都会被阻塞，影响失常业务。MySQL 5.6 和 MariaDB 10.0 开始反对 Online DDL，能够在执行 DDL 操作的同时，不影响 DML 的失常执行，线上间接执行 DDL 操作对用户根本无感知（局部操作对性能有影响）。

不同版本的数据库对各种 DDL 语句的反对存在肯定的差别，本文将会针对 MySQL 和 MariaDB 对 Online DDL 的反对状况做一个汇总，在须要执行 DDL 操作时，能够参考本文的 Online DDL 反对状况 局部。

本文将会继续修改和更新，最新内容请参考我的 GITHUB 上的程序猿成长打算我的项目，欢送 Star，更多精彩内容请 follow me。

在 ALTER TABLE 语句中，反对通过 ALGORITHM 和 LOCK 语句来实现 Online DDL：

ALGORITHM - 管制 DDL 操作如何执行，应用哪个算法
LOCK - 管制在执行 DDL 时容许对表加锁的级别

ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM 反对的算法

ALGORITHM	阐明
DEFAULT	默认算法，主动应用可用的最高效的算法
COPY	最原始的形式，所有的存储引擎都反对，不应用 Online DDL，操作时会创立长期表，执行全表拷贝和重建，过程中会写入 Redo Log 和大量的 Undo Log，须要增加读锁，十分低效
INPLACE	尽可能防止表拷贝和重建，更确切的名字应该是 `ENGINE` 算法，由存储引擎决定如何实现，有些操作是能够立刻失效的（比方重命名列，扭转列的默认值等），但有些操作仍然须要全表或者局部表的拷贝和重建（比方增加删除列、增加主键、扭转列为 NULL 等）
NOCOPY	该算法是 `INPLACE` 算法的子集，用于防止聚簇索引（主键索引）的重建造成全表重建，也就说用该算法会禁止任何引起聚簇索引重建的操作
INSTANT	用于防止 `INPLACE` 算法在须要批改数据文件时异样低效的问题，所有波及到表拷贝和重建的操作都会被禁止

NOCOPY 算法反对：MariaDB 10.3.2+，MySQL 不反对该算法。
INSTANT 算法反对：MariaDB 10.3.2+，MySQL 8.0.12+。

算法应用规定：

如果用户指定的算法为 COPY，则 InnoDB 应用 COPY 算法。
如果用户指定的是 COPY 之外的其它算法，则 InnoDB 会依照算法效率，抉择最高效的算法，最差的状况下采纳用户指定的算法。比方用户指定了 ALOGRITHM = NOCOPY，则 InnoDB 会从 (NOCOPY, INSTANT) 中抉择反对的最高效的算法。

MySQL 服务次要为 Server 层 和 存储引擎层 两局部组成，Server 层蕴含了 MySQL 大部分外围性能，所有的内置函数，跨存储引擎的性能如存储过程、触发器、视图等。存储引擎层负责数据的存储和读取，采纳了插件式的架构模式。

COPY 算法 作用在 Server 层，其执行过程都是在 Server 层，因而所有存储引擎都反对应用该算法，执行过程如下图

INPLACE 算法 作用于存储引擎层，是 InnoDB 存储引擎特有的 DDL 算法，执行过程如下图所示

LOCK 策略

默认状况下，MySQL/MariaDB 在执行 DDL 期间会应用尽可能少的锁，如果必要，能够通过 LOCK 子句管制在执行 DDL 时容许对表加锁的级别。如果指定的操作所要求的限度级别不满足（EXCLUSIVE > SHARED > NONE），则语句执行失败并报错。

策略	阐明
DEFAULT	应用以后操作反对的粒度最小的锁策略
NONE	不获取任何表锁，容许所有的 DML 操作
SHARED	对表增加共享锁（读锁），只容许只读的 DML 操作
EXCLUSIVE	对表增加排它锁（写锁），不容许任何 DML 操作

为了防止执行 DDL 时，因为锁表导致生产服务不可用，在执行表构造变更语句时，能够增加 LOCK=NONE 子句，如果语句须要获取共享锁或者排它锁，则会间接报错，这样就能够防止意外锁表，造成线上服务不可用了。

Online DDL 执行过程

Online DDL 操作次要分为三个阶段：

阶段 1：初始化
在初始化阶段，服务器会依据存储引擎的能力，操作的语句和用户指定的 ALGORITHM 和 LOCK 选项来决定容许多大程度的并发。在这个阶段会创立一个 可降级的元数据共享锁（SU）来爱护表定义。
阶段 2：执行
这个阶段会筹备并执行 DDL 语句，依据 阶段 1 评估的后果来决定是否将元数据锁降级为 排它锁 （X），如果须要降级为排它锁，则只在 DDL 的 筹备阶段 短暂的增加排它锁。
阶段 3：提交表定义
在表定义的提交阶段，元数据锁会降级为排它锁来更新表的定义。独占排它锁的持续时间十分短。

元数据锁（MDL，Metadata Lock）次要用于 DDL 和 DML 操作之间的并发访问控制，爱护表构造（表定义）的统一，保障读写的正确性。MDL 不须要显式的应用，在拜访表时会主动加上。

因为下面三个阶段中对元数据锁的独占， Online DDL 过程必须期待曾经持有元数据锁的并发事务提交或者回滚能力继续执行。

留神：当 Online DDL 操作正在期待元数据锁时，该元数据锁会处于挂起状态，后续的所有事务都会被阻塞。在 MariaDB 10.3 之后，能够通过增加 NO WAIT 或者 WAIT n 来管制期待所得超时工夫，超时立刻失败。
ALTER TABLE tbl_name [WAIT n|NOWAIT] ...CREATE ... INDEX ON tbl_name (index_col_name, ...) [WAIT n|NOWAIT] ...DROP INDEX ... [WAIT n|NOWAIT]DROP TABLE tbl_name [WAIT n|NOWAIT] ...LOCK TABLE ... [WAIT n|NOWAIT]OPTIMIZE TABLE tbl_name [WAIT n|NOWAIT]RENAME TABLE tbl_name [WAIT n|NOWAIT] ...SELECT ... FOR UPDATE [WAIT n|NOWAIT]SELECT ... LOCK IN SHARE MODE [WAIT n|NOWAIT]TRUNCATE TABLE tbl_name [WAIT n|NOWAIT]

评估 Online DDL 操作的性能

Online DDL 操作的性能取决于是否产生了表的重建。在对大表执行 DDL 操作之前，为了防止影响失常业务操作，最好是先评估一下 DDL 语句的性能再抉择如何操作。

复制表构造，创立一个新的表
在新创建的表中插入大量数据
在新表下面执行 DDL 操作
查看执行操作后返回的 rows affected 是否是 0。如果该值非 0，则意味着须要拷贝表数据，此时对 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)
```

因为在执行 Online DDL 过程中须要记录并发执行的 DML 操作产生的变更，而后在执行完 DDL 操作之后再利用这些变更，因而应用 Online DDL 操作破费的工夫比不应用 Online 模式执行要更长一些。

Online DDL 反对状况

INSTANT 算法反对：MariaDB 10.3.2+，MySQL 8.0.12+。NOCOPY 只反对 MariaDB 10.3.2 以上版本，不反对 MySQL，这里就暂且疏忽了。

重点关注是否 重建表 和 反对并发 DML：不须要重建表，反对并发 DML 最佳。

二级索引

操作	INSTANT	INPLACE	重建表	并发 DML	只批改元数据
创立或者增加二级索引	❌	✅	❌	✅	❌
删除索引	❌	✅	❌	✅	✅
重命名索引（⚠️MySQL 5.7+，MariaDB 10.5.2+）	❌	✅	❌	✅	✅
增加 `FULLTEXT` 索引	❌	✅ ①	❌ ①	❌	❌
增加 `SPATIAL` 索引（⚠️MySQL 5.7+，MariaDB 10.2.2+）	❌	✅	❌	❌	❌
批改索引类型	✅	✅	❌	✅	✅

阐明：

① 第一次增加全文索引字段时须要重建表，之后就不须要了

主键

操作	INSTANT	INPLACE	重建表	并发 DML	只批改元数据
增加主键	❌	✅ ②	✅ ②	✅	❌
删除主键	❌	❌	✅	❌	❌
删除一个主键同时增加一个新的	❌	✅	✅	✅	❌

阐明：

重建聚簇索引总是须要拷贝表数据（InnoDB 是“索引组织表”），所以最好是在创立表的时候就定义好主键
如果创立表是没有指定主键，InnoDB 会抉择第一个 NOT NULL 的 UNIQUE 索引作为主键，或者应用系统生成的 KEY
② 对聚簇索引来说，应用 INPLACE 模式比 COPY 模式要高效一些：不会产生 undo log 和 redo log，二级索引是有序的，所以能够按程序加载，不须要应用变更缓冲区

一般列

操作	INSTANT	INPLACE	重建表	并发 DML	只批改元数据
列增加	✅ ③	✅	❌ ③	✅ ③	❌
列删除	❌ ④	✅	✅	✅	❌
列重命名	❌	✅	❌	✅ ⑤	✅
扭转列的程序	❌ ⑫	✅	✅	✅	❌
设置默认值	✅	✅	❌	✅	✅
批改数据类型	❌	❌	✅	❌	❌
扩大 `VARCHAR` 长度（⚠️MySQL 5.7+, MariaDB 10.2.2+）	❌ ⑬	✅	❌ ⑥	✅	✅
删除列的默认值	✅	✅	❌	✅	✅
扭转自增值	❌	✅	❌	✅	❌ ⑦
设置列为 NULL	❌	✅	✅ ⑧	✅	❌
设置列为 NOT NULL	❌	✅ ⑨	✅ ⑨	✅	❌
批改 `ENUM` 和 `SET` 列的定义	✅	✅	❌ ⑩	✅	✅

阐明：

③ 并发 DML：当插入一个自增列时，不反对并发的 DML 操作，增加自增列时，大量的数据会被从新组织，代价昂扬
③ 重建表：增加列时，MySQL 5.7及之前版本须要重建表，MySQL 8.0 当 ALGORITHM=INPLACE 时，须要重建表，ALGORITHM=INSTANT 时不须要重建
③ INSTANT算法：增加列时，应用 INSTANT 算法有上面这些限度
- 增加列操作不能和其它不反对 INSTANT 算法的操作合并为一条 ALTER TABLE 语句
- 新增的列只能增加到表的最初，不能放到其它列的后面，在 MariaDB 10.4 之后，反对在任意地位增加
- 不能将列增加到 ROW_FORMAT=COMPRESSED 的表中
- 不能将列增加到蕴含 FULLTEXT 的表中
- 不能将列增加到长期表中，长期表只反对 ALGORITHM=COPY
- 不能将列增加到驻留在数据字典表空间中的表中
- 在增加列的时候不会计算行的大小限度，该限度在执行 DML 操作插入或者更新表时才会被查看
④ 删除列时，大量的数据须要被从新组织，代价昂扬，在 MariaDB 10.4 之后，删除列反对 INSTANT 算法
⑤ 重命名列时，确保只扭转列名，不扭转数据类型，这样能力反对并发的 DML 操作
⑥ 扩大 VARCHAR 长度时，INPLACE 是有条件的，必须保障用于标识字符串长度的长度字节不变（这里说的都是字节，不是 VARCHAR 的字符长度，字节占用与采纳的字符集无关，utf8 字符集下，一个字符占 3 个字节， utf8mb4 则 4 个字节）
- 当 VARCHAR 列长度在 0-255 个字节时，长度标识占用一个字节
- 当 VARCHAR 列长度大于 255 个字节时，长度标识占用两个字节

因而，INPLACE 只反对 0-255 个字节之间或者 256 个字节到更大的长度之间的变更。VARCHAR 列长度减小是不反对 INPLACE 的。

⑦ 自增列值变更是批改的内存中的值，不是数据文件
⑧ ⑨ 设置列为 [NOT] NULL 时，大量的数据被从新组织，代价昂扬
⑩ 批改 ENUM 和 SET 类型的列定义时，是否须要表拷贝取决于已有元素的个数和插入成员的地位
⑫ 在 MariaDB 10.4 之后，列排序反对 INSTANT 算法
⑬ 在 MariaDB 10.4.3 之后，InnoDB 反对应用 INSTANT 算法减少列的长度，然而也有一些限度，具体参考 Changing the Data Type of a Column

生成列

操作	INSTANT	INPLACE	重建表	并发 DML	只批改元数据
增加 `STORED` 列	❌	❌	✅	❌	❌
批改 `STORED` 列的排序	❌	❌	✅	❌	❌
删除 `STORED` 列	❌	✅	✅	✅	❌
增加 `VIRTUAL` 列	✅	✅	❌	✅	✅
批改 `VIRTUAL` 列的排序	✅	❌	✅	❌	❌
删除 `VIRTUAL` 列	✅	✅	❌	✅	✅

外键

操作	INSTANT	INPLACE	重建表	并发 DML	只批改元数据
增加外键束缚	❌	✅ ⑭	❌	✅	✅
删除外键束缚	❌	✅	❌	✅	✅

阐明：

⑭ 增加外键时，只有当 foreign_key_checks 选项被禁用的时候才反对 INPLACE 算法

表

操作	INSTANT	INPLACE	重建表	并发 DML	只批改元数据
批改 `ROW_FORMAT`	❌	✅	✅	✅	❌
批改 `KEY_BLOCK_SIZE`	❌	✅	✅	✅	❌
设置长久表统计信息	❌	✅	❌	✅	✅
指定字符集	❌	✅	✅ ⑮	❌	❌
转换字符集	❌	❌	✅ ⑯	❌	❌
优化表	❌	✅ ⑰	✅	✅	❌
应用 `FORCE` 选项重建表	❌	✅ ⑱	✅	✅	❌
执行空的重建	❌	✅ ⑲	✅	✅	❌
重命名表	✅	✅	❌	✅	✅

阐明：

⑮⑯ 当字符集不同时，须要重建表
⑰⑱⑲ 如果表中蕴含 FULLTEXT 的字段，则不反对 INPLACE

表空间

操作	INSTANT	INPLACE	重建表	并发 DML	只批改元数据
重命名惯例表空间	❌	✅	❌	✅	✅
启用或者禁用惯例表空间加密	❌	✅	❌	✅	❌
启用或者禁用 `file-per-table` 表空间加密	❌	❌	✅	❌	❌

限度

在长期表 TEMPORARY TABLE 上创立索引时会产生表拷贝
如果表上有 ON...CASCADE 或者 ON...SET NULL 束缚，则 ALERT TABLE 不反对字句 LOCK=NONE
在 Onlne DDL 操作实现之前，它必须期待相干表曾经持有元数据锁的事务提交或者回滚，在这个过程中，相干表的新事务会被阻塞，无奈执行
当在大表上执行波及到表重建的 DDL 时，会存在以下限度
- 没有任何机制能够暂停 Online DDL操作或限度 Online DDL 操作的 I/O 或CPU使用率
- 如果操作失败，则回滚 Online DDL操作的代价十分昂扬
- 长时间运行的 Online DDL 可能会导致复制提早。 Online DDL 操作必须在 Master 上执行实现后能力在 Slave 上执行，在这个过程中，并发解决的 DML 在 Slave 下面必须期待 DDL 操作实现后才会执行。

写在最初

本文将会继续修改和更新，最新内容请参考我的 GITHUB 上的程序猿成长打算我的项目，欢送 Star，更多精彩内容请 follow me。

参考

MariaDB Knowledge Base: InnoDB Online DDL
MariaDB Knowledge Base: WAIT and NOWAIT
MySQL 5.6: InnoDB and Online DDL
MySQL 5.7: InnoDB and Online DDL
MySQL 8.0: InnoDB and Online DDL
极客工夫：MySQL实战 45 讲