一、概述
MySQL DBA 应该都知道，数据库操作里面，DDL 操作（比如 ALTER 等）代价是非常高的，特别是在单表上千万的情况下，加个索引或改个列类型，就有可能堵塞整个表的读写。一个是有些 DDL 操作本身有锁，一个是 MDL 锁。但 DDL 又是一个不可避免的需求，无论是业务迭代，还是整理表碎片都需要。
我们知道如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重建表，才能达到表文件变小的目的。试想一下，如果你现在有一个表 A，需要做空间收缩，为了把表中存在的空洞去掉，你可以怎么做呢？
你可以新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。
由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。显然地，表 B 的主键索引更紧凑，数据页的利用率也更高。如果我们把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。
这里，你可以使用 alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表 B 不需要你自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。
基本上整个过程如下“图1”：
显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。
从 MySQL 5.6 开始，大家期待的 Online DDL 出现了，可以实现修改表结构的同时，依然允许 DML 操作。在这个特性出现以前，用的比较多的 DDL 操作工具是 pt-online-schema-change & gh-ost，当然现在用的也比较多。
二、Online DDL原理
MySQL 5.6 版本开始引入的 Online DDL，对“图1”描述的操作流程做了优化。
我给你简单描述一下引入了 Online DDL 之后，重建表的流程如下“图2”：
简单描述一下，如下：
1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是下图中 state2 的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是下图中 state3 的状态；
5. 用临时文件替换表 A 的数据文件。
可以看到，与“图1”过程的不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。
上面只是重建表过程的优化，那么一个 ALTER 语句真正执行时，其实 Online DDL 的过程是下面这样的：
1. DDL 启动时拿 MDL 写锁
2. 然后降级成 MDL 读锁
3. 真正做 DDL
4. 升级成 MDL 写锁
5. 释放 MDL 锁
其中 MDL 元数据锁在整个过程的 1、2、4、5 步骤如果没有锁冲突，执行时间非常短，如果说有大事务或者大的查询已经持有 MDL 锁，那么在 DDL 的第一步就会进行阻塞，需要获取锁，通过 show processlist 查看进行状态为 waiting meta data lock。而在第3步占用了 DDL 绝大部分时间，因为要重建表，这期间这个表可以正常读写数据，是因此称为“online”。而非“online”模式，也就是 Copy 表的方式与整个“Online DDL”操作步骤是一样的，也需要拿 MDL 锁，然后释放；区别主要在第 3 步。
可以看到 DDL 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。首先获取 MDL 写锁是为了确保没有其他线程在用这个表。为什么要退化呢？就是为了实现 Online DDL，MDL 读锁不会阻塞增删改操作。
那为什么不干脆直接解锁呢？其实为了保护自己，禁止其他线程对这个表同时做 DDL。
而对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。
需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，还是推荐使用 GitHub 开源的 gh-ost 来做。
你可能注意到了，在“图1”中，我们把表 A 中的数据导出来的存放位置叫作 tmp_table。这是一个临时表，是在 server 层创建的。而在“图2”中，根据表 A 重建出来的数据是放在“tmp_file”里的，这个临时文件是 InnoDB 在内部创建出来的。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。
所以，如果你有一个 1TB 的表，现在磁盘间是 1.2TB，能不能做一个 inplace 的 DDL 呢？
答案是不能。因为，tmp_file 也是要占用临时空间的。我们重建表的这个语句 alter table t engine=InnoDB，其实隐含的意思是：
|
1 |
alter table t engine=innodb,ALGORITHM=inplace; |
跟 inplace 对应的就是拷贝表的方式了，用法是：
|
1 |
alter table t engine=innodb,ALGORITHM=copy; |
当你使用 ALGORITHM=copy 的时候，表示的是强制拷贝表，对应的流程就是“图1”的操作过程。
这样说你可能会觉得，inplace 跟 Online 是不是就是一个意思？ 其实不是的，只是在重建表这个逻辑中刚好是这样而已。
比如，如果我要给 InnoDB 表的一个字段加全文索引，写法是：
|
1 |
alter table t add FULLTEXT(field_name); |
这个过程是 inplace 的，但会阻塞增删改操作，是非 Online 的。 如果说这两个逻辑之间的关系是什么的话，可以概括为：
1. DDL 过程如果是 Online 的，就一定是 inplace 的；
2. 反过来未必，也就是说 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。
对于不支持 Inplace 的 DDL 操作采用 Copy 方式，比如修改列类型，删除主键，修改字符集等，这些操作都会导致记录格式发生变化，目前无法使用 Inplace 方式。对于 Inplace 方式，MySQL 内部以”是否修改记录格式”为基准也分为两类，一类需要重建表（重新组织记录），比如 optimize table、添加索引、添加/删除列、修改列 NULL/NOT NULL 属性等；另外一类是只需要修改表的元数据，比如删除索引、修改列名不改变数据类型、修改列默认值、修改列自增值等。MySQL 将这两类方式分别称为 rebuild 和 no-rebuild 方式。
此外 ALTER 语句还有 LOCK 选项控制是否锁表，根据不同的 DDL 操作类型有不同的表现。默认 MySQL 尽可能不去锁表，但是像修改主键这样的昂贵操作不得不选择锁表。
LOCK=NONE，即 DDL 期间允许并发读写涉及的表，比如为了保证 ALTER TABLE 时不影响用户注册或支付，可以明确指定，好处是如果不幸该 ALTER 语句不支持对该表的继续写入操作，则会提示失败，而不会直接发到库上执行。ALGORITHM=COPY 的默认 LOCK 级别。
LOCK=SHARED，即 DDL 期间表上的写操作会被阻塞，但不影响读取。
LOCK=DEFAULT，即 DDL 开始时，让 MySQL 自己去判断 LOCK 的模式，原则是 MySQL 尽可能不去锁表。
LOCK=EXCLUSIVE，即 DDL 期间该表不可用，堵塞任何读写请求。如果你想 ALTER 操作在最短的时间内完成，或者表短时间内不可用能接受，可以手动指定。
最后，你可能会碰到这种情况，对一个表重建后，发现表空间不仅没变小，还稍微大了一点儿，比如 1TB 变成了 1.01TB。
这主要是因为原表是紧凑的，而在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新使用。也就是说，其实重建表之后不是“最”紧凑的。假如是这么一个过程：
1. 将表 t 重建一次；
2. 插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间；
3. 这种情况下，再重建一次表 t，就可能会出现问题中的现象。
我们怎么判断 DDL 执行是 Inplace 还是 Copy 的方式呢？一个最直观的判断方法是看命令执行后影响的行数，没有新建临时表的话影响的行数是0。也就是说如果是 Inplace 方式则影响行数是0，如果是 Copy 方式就会显示影响行数为整表记录数。
还有如果将 ALTER 操作显式的放到事务里，MySQL 默认会提交 ALTER 语句前面的事务，然后自己独立执行 ALTER 语句。即无法满足在“可重复读”隔离级别下，在一个事务更改表结构后不提交，然后在另一个事务查询的时候不会看见新的表结构。以后当元数据信息都下沉到了 InnoDB 层后，可能会满足这个表结构修改也能实现可重复问题问题。
有同学问到使用 optimize table、analyze table 和 alter table 这三种方式重建表的区别。这里，我顺便再简单和你解释一下。 从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认的就是上面图 4 的流程了； analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁； optimize table t 等于 recreate+analyze。
三、Online DDL状态
从上面的介绍可以看出，不是 MySQL 5.6 支持 Online DDL 就可以随心所欲的 ALTER TABLE，锁不锁表要看情况。
提示：下表根据官方 Summary of Online Status for DDL Operations 整理挑选的常用操作。
- In-Place 为 Yes 是优选项，说明该操作支持 In-Place。
- Rebuilds Table？为 No 是优选项，因为为 Yes 需要重建表，就是 Copy 数据，大部分情况与 In-Place 是相反的。
- Allows Concurrent DML？为 Yes 是优选项，说明 DDL 期间表依然可读写，可以指定 LOCK=NONE（如果操作允许的话 MySQL 自动就是 NONE）。
- Only Modifies Metadata？默认所有 DDL 操作期间都允许查询请求，放在这只是便于参考。
- Notes 会对前面几列 Yes/No 带 * 号的限制说明。
| Operation | In-Place? | Rebuilds Table? | Allows Concurrent DML? | Only Modifies Metadata? | Notes |
|---|---|---|---|---|---|
| 创建全文索引 | Yes | No* | No | No | 第一个全文索引需要通过Copy Table的方式创建；其后的全文索引可以通过Inplace方式创建 |
| 添加普通索引 | Yes* | No* | Yes | No | 对全文索引有限制 |
| 删除索引 | Yes | No | Yes | Yes | 仅修改表的元数据 |
| OPTIMIZE TABLE | Yes* | Yes | Yes | No | 从5.6.17开始使用ALGORITHM=INPLACE，当然如果指定了old_alter_table=1或mysqld启动带–skip-new则将还是COPY模式，如果表上有全文索引只支持COPY |
| 设置列默认值 | Yes | No | Yes | Yes | 仅修改表的元数据 |
| 修改auto-increment值 | Yes | No | Yes | No* | 修改存储在内存中的值，而不是数据文件。 |
| 添加外键 | Yes* | No | Yes | Yes | 当禁用foreign_key_checks时，可以使用in-place算法，否则必须使用copy算法 |
| 删除外键 | Yes | No | Yes | Yes | foreign_key_checks参数没有任何影响 |
| 改变列名 | Yes* | No* | Yes* | Yes | 为了允许DML并发，仅改变列名，不改变数据类型 |
| 添加列 | Yes* | Yes* | Yes* | No | 尽管允许ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作。当添加列是auto-increment，不允许DML并发 |
| 删除列 | Yes | Yes* | Yes | No | 尽管允许ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作 |
| 修改列数据类型 | No* | Yes* | No | No | 修改类型或添加长度，都会拷贝表，而且不允许更新操作；对于varchar类型可支持在线修改操作 |
| 修改表引擎 | Yes* | Yes | Yes | No | 尽管允许ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作 |
| 修改ROW_FORMAT 和KEY_BLOCK_SIZE |
Yes | Yes | Yes | No | 尽管允许ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作 |
| 设置列属性NULL 或NOT NULL |
Yes | Yes | Yes | No | 尽管允许ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作 |
| 添加主键 | Yes* | Yes | Yes | No | 尽管允许ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作。如果列定义必须转化NOT NULL，则不允许INPLACE |
| 删除并添加主键 | Yes | Yes | Yes | No | 在同一个 ALTER TABLE 语句删除就主键、添加新主键时，才允许inplace；数据大幅重组,所以它仍然是一项昂贵的操作。 |
| 删除主键 | No | Yes | No | No | 不允许并发DML，要拷贝表，而且如果没有在同一 ATLER TABLE 语句里同时添加主键则会收到限制 |
| 转换表字符集 | No | Yes* | No | No | 如果新的字符集编码不同，则重构表 |
| 优化表 | Yes* | Yes | Yes | No | 如果表上创建有全文索引，则不支持algorithm=inplace选项 |
| 设置表的 persistent statistics | Yes | No | Yes | Yes | 仅修改表的元数据metadata |
从表看出，In-Place 为 No，DML 一定是 No，说明 ALGORITHM=COPY 一定会发生拷贝表，只读。但 ALGORITHM=INPLACEE 也要可能发生拷贝表，但可以并发 DML 操作。比如：添加、删除列，改变列顺序、添加或删除主键、改变行格式 ROW_FORMAT 和压缩块大小 KEY_BLOCK_SIZE、改变列 NULL 或 NOT NULL、改变列 NULL 或 NOT NULL 等。不允许并发 DML 的情况有：修改列数据类型、删除主键、变更表字符集，即这些类型操作ddl是不能online的。
另外，更改主键索引与普通索引处理方式是不一样的，主键即聚集索引，体现了表数据在物理磁盘上的排列，包含了数据行本身，需要拷贝表；而普通索引通过包含主键列来定位数据，所以普通索引的创建只需要一次扫描主键即可，而且是在已有数据的表上建立二级索引，更紧凑，将来查询效率更高。
修改主键也就意味着要重建所有的普通索引。删除二级索引更简单，修改 InnoDB 系统表信息和数据字典，标记该所以不存在，标记所占用的表空间可以被新索引或数据行重新利用。
下“图3”是一个包含 1078880 行的表，进行的 Online DDL 和 PT ONLINE 模式一些修改操作之间的比较结果（ONLINE DDL VS PT-ONLINE-SCHEMA-CHANGE）：
四、Online DDL测试
MySQL 5.6 支持的 ONLINE DDL 其各种算法测试。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
# Add Index; mysql> alter table sbtest add index c_index(c),ALGORITHM=INPLACE,LOCK=NONE; Query OK, 0 rows affected (20.74 sec) Records: 0 Duplicates: 0 Warnings: 0 # Drop Index; mysql> alter table sbtest drop index c_index; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 # Add Colume; mysql> alter table sbtest add column td varchar(10),ALGORITHM=INPLACE,LOCK=NONE; Query OK, 0 rows affected (11.31 sec) Records: 0 Duplicates: 0 Warnings: 0 # Rename Colume + don't change its data type; mysql> alter table sbtest change column td dt varchar(10),ALGORITHM=INPLACE,LOCK=NONE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 # Rename Colume + chage its data type（not support ALGORITHM=INPLACE）; mysql> alter table sbtest change column dt td varchar(10),ALGORITHM=INPLACE,LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. # Rename Colume + chage its data type（support ALGORITHM=COPY,LOCK=SHARED ）; mysql> alter table sbtest change column dt td varchar(10),ALGORITHM=COPY,LOCK=SHARED; Query OK, 2000000 rows affected (14.15 sec) Records: 2000000 Duplicates: 0 Warnings: 0 # Drop Colume; mysql> alter table sbtest drop column td,ALGORITHM=INPLACE,LOCK=NONE; Query OK, 0 rows affected (12.37 sec) Records: 0 Duplicates: 0 Warnings: 0 # Convert character（not support ALGORITHM=INPLACE）; mysql> alter table sbtest CONVERT TO character set utf8,ALGORITHM=COPY,LOCK=SHARED; Query OK, 2000000 rows affected (16.81 sec) Records: 2000000 Duplicates: 0 Warnings: 0 |
可以看出来更改数据类型不支持 INPLACE 算法，只支持 COPY。并且使用 COPY 算法时，不支持 LOCK=NONE。
|
1 2 3 |
# Rename Colume + chage its data type（support copy）; mysql> alter table sbtest change column dt td varchar(10),ALGORITHM=COPY,LOCK=NONE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. |
但是可以支持 LOCK=SHARED，就是允许读操作。
|
1 2 3 |
mysql> alter table info change column td dd varchar(100),ALGORITHM=COPY,LOCK=SHARED; Query OK, 2000000 rows affected (14.15 sec) Records: 2000000 Duplicates: 0 Warnings: 0 |
对于 tinyint、int、smallint、bigint 等数值类型的数据类型，自身位大小的增大或减小是支持 ONLINE 的（注意：不支持从 tinyint 变更为 int），如下：
|
1 2 3 4 5 6 7 |
mysql> alter table t1 change id id bigint(10),ALGORITHM=INPLACE,LOCK=NONE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 change id id bigint(30),ALGORITHM=INPLACE,LOCK=NONE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 |
另外，如果是更改字段属性（类型不变）根据类型不同支持的 Online DDL 也是有限的，如把允许为 NULL 变更为不允许为NULL就不支持。
|
1 2 |
mysql> alter table sbtest change column dd dd varchar(100) not null,ALGORITHM=INPLACE,LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY. |
但是在线支持变更 comment 属性。
|
1 |
mysql> alter table info change dd dd varchar(100) null comment "test",ALGORITHM=INPLACE,LOCK=NONE; |
需要注意的是，在 MySQL 5.6 之前使用 ALTER TABLE … ALGORITHM=INPLACE 的表不支持包含时间列（DATE，DATETIME，TIMESTAMP），不然会报错。
MySQL 5.7 版本支持重命名索引和修改 VARCHAR 的大小（增大不能减小），且无需 table-copy（秒级增加 varchar 大小）。这两项操作在之前的版本中，都需要重建索引或表，适用于各引擎。
|
1 2 |
# change its data type; mysql> alter table sbtest ALGORITHM=INPLACE,change column dt td varchar(100); |
但存在限制，即只支持 0~255 字节之间增大或者 256~65535 字节之间增大，也就是说若从 254 增到 256 时不能使用 INPLACE 算法（增加到 255 可以），必须使用 COPY 算法，否侧报错。注意，这里说的是字节，不是字符，具体 varchar 字符长度对应的字节大小还要根据字符集来，比如表为 utf8 字符集时 varchar(100) 的字节长度为 100*3 个字节，表为 utf8mb4 字符集时 varchar(100) 的字节长度为 100*4 个字节。
MySQL 为什么要以 255 字节作为分界判断呢？实际上，varchar 字段的实际内容前有 1 个或 2 个字节存储实际内容的长度（行的 header 部分存储），而到底是 1 个字节还是 2 个字节，就取决于创建表或修改的时候，这个字段声明的长度。也就是说，当表为 utf8 字符集时，字段 varchar(4) 的实际内容长度为 4*3=12 字节，则需要用 1 个字节存储，1 个字节能存储最大 0-255 字节；而字段 varchar(300) 的实际长度为 300*3=900 字节，则需要 2 个字节存储，2 个字节最大能存储 0-65535 字节。因此，如果 varchar 类型增加长度的范围不符合上面的条件，则必须重做数据。
当然这事儿也不是不能解决的，InnoDB 是为了节省空间，如果我们放弃这个节省策略，对于所有的 varchar，都用 2 个字节来保存实际长度，是不是就没这个问题了呢。
另外使用 INPLACE 算法缩小 VARCHAR 的 ALTER TABLE 也是不支持的，必须用 COPY 算法。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> alter table sbtest add column td varchar(10); Query OK, 0 rows affected (10.81 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table sbtest ALGORITHM=INPLACE,change column td td varchar(254); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table sbtest ALGORITHM=INPLACE,change column td td varchar(256); ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. |
最后说一下 Online DDL 操作根据数据量的不同的耗时时间，我在生产环境做过两千五百万数据的 ALTER 操作，耗时 12 分钟 31 秒，我的硬件配置是 32 核 32G。按照这个时间可以根据硬件不同大概估算出数据量不同时的耗时时间。比如 100 万数据估计耗时：1000000 / (25000000 / (12*60+30)) = 30秒。
五、Online DDL限制
- 在 alter table 时，如果涉及到 table copy 操作，要确保 datadir 目录有足够的磁盘空间，能够放的下整张表，因为拷贝表的的操作是直接在数据目录下进行的。
- 添加索引无需 table copy，但要确保 tmpdir 目录足够存下索引一列的数据（如果是组合索引，当前临时排序文件一合并到原表上就会删除）。
- 在主从环境下，主库执行 alter 命令在完成之前是不会进入 binlog 记录事件，如果允许 dml 操作则不影响记录时间，所以期间不会导致延迟。然而，由于从库是单个 SQL Thread 按顺序应用 relay log，轮到 ALTER 语句时直到执行完才能下一条，所以从库会在主库 DDL 完成后开始产生延迟。（pt-osc可以控制延迟时间，所以这种场景下它更合适）。
- 在每个 DDL 语句执行开始之前，如果期间有其他语句申请了排它锁，则 DDL 语句需要等待，会产生 waiting lock。
- 在执行一个允许并发 DML 在线 ALTER TABLE 时，结束之前这个线程会应用 online log 记录的增量修改，而这些修改是其它 thread 里产生的，所以有可能会遇到重复键值错误 (ERROR 1062 (23000): Duplicate entry)。
- 根据在线 DDL 操作的内部操作和 LOCK 语句的子句，在 ALTER TABLE DDL 操作的初始和最后阶段，在线 DDL 操作可能需要在短时间内独占访问表。因此，如果有长时间运行的事务执行插入，更新，删除或 SELECT … FOR UPDATE 在表上，在线 DDL 操作可能会在完成之前等待; 并且如果在进行中启动了类似的长时间运行的事务，则在线 ALTER TABLE 操作可能会在完成之前等待。
- 涉及到 table copy 时，目前还没有机制限制暂停 DDL，或者限制 IO 阀值，在 MySQL 5.7.6 开始能够通过 performance_schema 观察 alter table 的进度。
- 一般来说，建议把多个 alter 语句合并在一起进行，避免多次 table rebuild 带来的消耗。但是也要注意分组，比如需要 copy table 和只需 inplace 就能完成的，应该分两个 alter 语句。
- 如果 DDL 执行时间很长，期间又产生了大量的 dml 操作，以至于超过了 innodb_online_alter_log_max_size 变量所指定的大小，会引起 DB_ONLINE_LOG_TOO_BIG 错误。默认为 128M，特别对于需要拷贝大表的 alter 操作，考虑临时加大该值，以此获得更大的日志缓存空间。
- 需要注意的是，在 MySQL 5.6 之前使用 ALTER TABLE … ALGORITHM=INPLACE 的表不支持包含时间列（DATE，DATETIME，TIMESTAMP），不然会报错。
- 从 MySQL 5.7.11 开始，你可以使用 innodb_tmpdir 配置选项为在线 DDL 操作定义单独的临时目录。该 innodb_tmpdir 选项被引入以帮助避免由于在 ALTER TABLE 重建表的在线操作期间创建的大型临时排序文件而可能发生的临时目录溢出问题。
- 执行完 ALTER TABLE 之后，最好 ANALYZE TABLE tb1 去更新索引统计信息。
- 如果操作失败，在线 DDL 操作的回滚可能是昂贵的。
六、Online DDL实施细节
Online DDL 主要包括3个阶段：prepare 阶段，ddl 执行阶段，commit 阶段。rebuild 方式比 no-rebuild 方式实质多了一个 ddl 执行阶段，prepare 阶段和 commit 阶段类似。下面将主要介绍 DDL 执行过程中三个阶段的流程。
- Prepare 阶段
1. 创建新的临时frm文件(与InnoDB无关)。
2. 持有EXCLUSIVE-MDL锁，禁止读写。
3. 根据alter类型，确定执行方式（copy，online-rebuild，online-norebuild）。假如是Add Index，则选择online no-rebuild即Inplace方式。
4. 更新数据字典的内存对象。
5. 分配row_log对象记录增量（仅rebuild类型需要）。
6. 生成新的临时ibd文件（仅rebuild类型需要）。
- DDL 执行阶段
1. 降级EXCLUSIVE-MDL锁，允许读写。
2. 扫描old_table的聚集索引每一条记录rec。
3. 遍历新表的聚集索引和二级索引，逐一处理。
4. 根据rec构造对应的索引项。
5. 将构造索引项插入sort_buffer块排序。
6. 将sort_buffer块更新到新的索引上。
7. 记录DDL执行过程中产生的增量(仅rebuild类型需要)。
8. 重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)。
9. 重放row_log间产生DML操作append到row_log最后一个Block。
- Commit阶段
1. 当前Block为row_log最后一个时，禁止读写，升级到EXCLUSIVE-MDL锁。
2. 重做row_log中最后一部分增量。
3. 更新innodb的数据字典表。
4. 提交事务（刷事务的redo日志）。
5. 修改统计信息。
6. rename临时idb文件，frm文件。
7. 变更完成。
这有一个导图挺直观的：http://blog.itpub.net/22664653/viewspace-2056953 。添加列时由于需要 copy table，row_log 会重放到新表上（临时 ibd 文件），直到最后一个 block，锁住原表禁止更新。row_log 记录了 DDL 变更过程中新产生的 DML 操作，并在 DDL 执行的最后将其应用到新的表中，保证数据完整性。
七、添加删除索引实现
MySQL 各版本，对于 add Index 的处理方式是不同的，主要有三种：
- Copy方式
这是InnoDB最早支持的创建索引的方式。顾名思义，创建索引是通过创建带索引的临时表拷贝方式实现的。
1. 新建一个带有新索引的临时表。
2. 然后锁原表，进制DML操作，允许读操作。
3. 将原表数据全部拷贝到临时表（无排序，一行行拷贝）。
4. 然后Rename，升级字典锁，禁止读写。
5. 完成创建索引的操作。
- Inplace方式
这是原生 MySQL 5.5 以及 innodb_plugin 中提供的创建索引的方式。所谓 Inplace，也就是索引创建在原表上直接进行，不会拷贝临时表。相对于 Copy Table 方式，这是一个进步。Inplace 方式创建索引，创建过程中，原表同样可读的，但是不可写。
1. 新建一个带有新索引的临时表。
2. 然后锁原表，禁止 DML 操作，允许读操作。
3. 读取聚集索引，构造新的索引项，排序并插入新索引。
4. 然后 Rename，升级字典锁，禁止读写。
5. 完成创建索引的操作。
- Online方式
这是 MySQL 5.6.7 中提供的创建索引的方式。无论是 Copy 方式，还是 Inplace 方式，创建索引的过程中，原表只能允许读取，不可写。对应用有较大的限制，因此 MySQL 最新版本中，InnoDB 支持了所谓的 Online 方式创建索引。
InnoDB 的 Online Add Index，首先是 Inplace 方式创建索引，无需使用临时表。在遍历聚簇索引，收集记录并插入到新索引的过程中，原表记录可修改。而修改的记录保存在 Row Log 中。当聚簇索引遍历完毕，并全部插入到新索引之后，重放 Row Log 中的记录修改，使得新索引与聚簇索引记录达到一致状态。
与 Copy 方式相比，Online Add Index 采用的是 Inplace 方式，无需 Copy Table，减少了空间开销；与此同时，Online Add Index 只有在重放 Row Log 最后一个 Block 时锁表，减少了锁表的时间。
与 Inplace 方式相比，Online Add Index 吸收了 Inplace 方式的优势，却减少了锁表的时间。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
sql_table.cc::mysql_alter_table(); // 1. 判断当前DDL操作是否可以Inplace进行 check_if_supported_inplace_alter(); … // 2. 开始进行Online创建的前期准备工作 prepare_inplace_alter_table(); … // 修改表的数据字典信息 prepare_inplace_alter_table_dict(); … // 等待InnoDB所有的后台线程，停止操作此表 dict_stats_wait_bg_to_stop_using_tables(); … // Online Add Index区别与Inplace Add Index的关键 // 在Online操作时，原表同时可以读写，因此需要 // 将此过程中的修改操作记录到row log之中 row0log.cc::row_log_allocate(); row_log_t* log = (row_log_t*)&buf[2 * srv_sort_buf_size]; // 标识当前索引状态为Online创建，那么此索引上的 // DML操作会被写入Row Log，而不在索引上进行更新 dict_index_set_online_status(index, ONLINE_INDEX_CREATION); … // 3. 开始进行真正的Online Add Index的操作(最重要的流程) inplace_alter_table(); // 此函数的操作，前部分与Inplace Add Index基本一致 // 读取聚簇索引、排序、并插入到新建索引中 // 最大的不同在于，当插入完成之后，Online Add Index // 还需要将row log中的记录变化，更新到新建索引中 row0merge.cc::row_merge_build_index(); … // 在聚簇索引读取、排序、插入新建索引的操作结束之后 // 进入Online与Inplace真正的不同之处，也是Online操作 // 的精髓部分——将这个过程中产生的Row Log重用 row0log.cc::row_log_apply(); // 暂时将新建索引整个索引树完全锁住 // 注意：只是暂时性锁住，并不是在整个重用Row Log的 // 过程中一直加锁(防止加锁时间过长的优化，如何优化？) rw_lock_x_lock(dict_index_get_lock(new_index)); … // InnoDB Online操作最重要的处理流程 // 将Online Copy Table中，记录的Row Log重放到新建索引上 // 重放Row Log的算法如下： // 1. Row Log中记录的是Online创建索引期间，原表上的DML操作 // 这些操作包括：ROW_OP_INSERT；ROW_OP_DELETE_MARK; … // 2. Row Log以Block的方式存储，若DML较多，那么Row Logs可能 // 会占用多个Blocks。row_log_t结构中包含两个指针：head与tail // head指针用于读取Row Log，tail指针用于追加写新的Row Log； // 3.在重用Row Log时，算法遵循一个原则：尽量减少索引树加锁 // 的时间(索引树加X锁，也意味着表上禁止了新的DML操作) // 索引树需要加锁的场景： // (一) 在重用Row Log跨越新的Block时，需要短暂加锁； // (二) 若应用的Row Log Block是最后一个Block，那么一直加锁 // 应用最后一个Block，由于禁止了新的DML操作，因此此 // Block应用完毕，新索引记录与聚簇索引达到一致状态， // 重用阶段结束； // (三) 在应用中间Row Log Block上的row log时，无需加锁，新的 // DML操作仍旧可以进行，产生的row log记录到最后一个 // Row Log Block之上； // 4. 如果是创建Unique索引，那么在应用Row Log时，可能会出现 // 违反唯一性约束的情况，这些情况会被记录到 // row_merge_dup_t结构之中 row_log_apply_ops(trx, index, &dup); row_log_apply_op(); row_log_apply_op_low(); … // 将New Index的Online row log设置为NULL， // 标识New Index的数据已经与聚簇索引完全一致 // 在此之后，新的DML操作，无需记录Row Log dict_index_set_online_status(); index->online_status = ONLINE_INDEX_COMPLETE; index->online_log = NULL; rw_lock_x_unlock(dict_index_get_block(new_index)); row_log_free(); … // 4. Online Add Index的最后步骤，做一些后续收尾工作 commit_inplace_alter_table(); … |
八、Online DDL空间要求
上面在说 Online DDL 使用限制时也说了 innodb_online_alter_log_max_size 参数，这个参数是 MySQL 5.6.6 引入的，因为在 Online DDL 过程中需要保持 delete、update、insert 这些数据，所以需要一个日志去保持，这个参数就是限制这个日志的最大大小，当 DDL 过程中需要的这个日志的大小比这个限制还大的时候就会报错。
具体错误大概如下：
|
1 |
ERROR 1799 (HY000) at line 1: Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again. |
该参数为动态参数且全局的，可通过如下命令加大（不一定是我下面设置的大小，这个根据你的情况）
|
1 |
mysql> set global innodb_online_alter_log_max_size=402653184; |
官方文档关于这个参数的解释：
| innodb_online_alter_log_max_size | Type | integer | |
| Default | 134217728 | ||
| Min Value | 65536 | ||
| Max Value | 2**64-1 | ||
该参数如果太小有可能导致 DDL 失败，这期间所有的未提交的 DML 操作都会回滚；但是如果太大会可能会导致 DDL 操作后最后锁定表的时间更长（锁定表，应用日志到表上）。
八、若干问题
Online Add Index 是否支持 Unique 索引？
确切的答案是：支持，InnoDB 支持 Online 创建 Unique 索引。
既然支持，就会面临 Check Duplicate Key 的问题。Row Log 中如果存在与索引中相同的键值怎么处理？怎么检测是否存在相同键值？
InnoDB 解决此问题的方案也比较简介易懂。其维护了一个 row_merge_dup_t 的数据结构，存储了在 Row log 重放过程中遇到的违反唯一性冲突的 Row Log。应用完 Row Log 之后，外部判断是否存在 Unique 冲突（有多少 Unique 冲突，均会记录），Online 创建 Unique 索引失败。
Online 与数据一致性如何兼得？
实际上，Online DDL 并非整个过程都是 Online，在 prepare 阶段和 commit 阶段都会持有 MDL-Exclusive 锁，禁止读写；而在整个 DDL 执行阶段，允许读写。由于 prepare 和 commit 阶段相对于 DDL 执行阶段时间特别短，因此基本可以认为是全程 Online 的。prepare 阶段和 commit 阶段的禁止读写，主要是为了保证数据一致性。prepare 阶段需要生成 row_log 对象和修改内存的字典；commit 阶段，禁止读写后，重做最后一部分增量，然后提交，保证数据一致。
如何实现 Server 层和 InnoDB 层一致性？
在 prepare 阶段，Server 层会生成一个临时的 frm 文件，里面包含了新表的格式；InnoDB 层生成了临时的 ibd 文件（rebuild方式）；在 DDL 执行阶段，将数据从原表拷贝到临时 ibd 文件，并且将 row_log 增量应用到临时 ibd 文件；在 commit 阶段，InnoDB 层修改表的数据字典，然后提交；最后 InnoDB 层和 mysql 层面分别重命名 frm 和 idb 文件。
Row Log 是什么样的结构，如何组织的？
在 Online Add Index 过程中，并发 DML 产生的修改，被记录在 Row Log 中。首先，Row Log 不是 InnoDB 的 Redo Log，而是每个正在被 Online 创建的索引的独占结构。
Online 创建索引，遵循的是先创建索引数据字典，后填充数据的方式。因此，当索引数据字典创建成功之后，新的 DML 操作就可以读取此索引，尝试进行更新。但是，由于索引结构上的 status 状态为 ONLINE_INDEX_CREATION，因此这些更新不能直接应用到新索引上，而是放入 Row Log 之中，等待被重放到索引之上。
Row Log 中，以 Block 的方式管理 DML 操作内容的存放。一个 Block 的大小为由参数 innodb_sort_buffer_size 控制，默认大小为 1M (1048576)。初始化阶段，Row Log 申请两个这样的 Block。
在 Row Log 重放的过程中，到底需要多久的锁表时间？
前面的流程分析中，也提到了锁表的问题(内部为锁新建索引树的操作实现)。
在重放 Row log 时，有两个情况下，需要锁表：
情况一：在使用完一个Block，跳转到下一个Block时，需要短暂锁表，判断下一个Block是否为Row Log的最后一个Block。若不是最后一个，跳转完毕后，释放锁；使用Block内的row log不加锁，用户DML操作仍旧可以进行。
情况二：在使用最后一个Block时，会一直持有锁。此时不允许新的DML操作。保证最后一个Block重放完成之后，新索引与聚簇索引记录达到一致状态。
综上分析两个锁表情况，情况二会持续锁表，但是由于也只是最后一个Block，因此锁表时间也较短，只会短暂的影响用户操作，在低峰期，这个影响是可以接受的。
Online Add Index 是否也存在与 Inplace 方式一样的限制？
由于Online Add Index同时也是Inplace方式的，因此Online方式也存在着Inplace方式所存在的问题：新索引上缺乏版本信息，因此无法为老事务提供快照读。
不仅如此，相对于Inplace方式，Online方式的约束更甚一筹，不仅所有小于创建此Index的事务不可使用新索引，同时，所有在新索引创建过程中开始的事务，也不能使用新索引。
这个增强的限制，在rowmerge.cc::row_merge_read_clustered_index()函数中调整，在聚簇索引遍历完成之后，将新索引的trx_id，赋值为Online Row Log中最大的事务ID。待索引创建完成之后，所有小于此事务ID的事务，均不可使用新索引。
在遍历聚簇索引读取数据时，读取的是记录的最新版本，那么此记录是否在Row Log也会存在？InnoDB如何处理这种情况？
首先，答案是肯定的。遍历聚簇索引读取记录最新版本时，这些记录有可能是新事务修改/插入的。这些记录在遍历阶段，已经被应用到新索引上，于此同时，这些记录的操作，也被记录到Row Log之中，出现了一条记录在新索引上存在，在Row Log中也存在的情况。
当然，InnoDB已经考虑到了这个问题。在重放Row Log的过程中，对于Row Log中的每条记录，首先会判断其在新索引中是否已经存在(row0log.c::row_log_apply_op_low())，若存在，则当前Row Log可以跳过(或者是将操作类型转换)。
例如：Row Log中记录的是一个INSERT操作，若此INSERT记录在新索引中已经存在，那么Row Log中的记录，可以直接丢弃(若存在项与INSERT项完全一致)；或者是将INSERT转换为UPDATE操作(Row Log记录与新索引中的记录，部分索引列有不同)；
对 InnoDB 表做 DDL 过程中异常了，为啥再次做 DDL 报 #sql-xxx already exists
这个错误是什么鬼？这个表#sql-xxx实质是做ddl产生的临时表，ddl异常退出后（比如进程被kill，或者机器异常掉电等），临时文件没有清理。再次执行时，会创建同名的#sql-xxx临时文件，从而导致报错。这里的xxx与table-id强相关，如果是这样，我们把这个讨厌的#sql-xxx临时文件删掉如何呢？再次重做ddl发现还是报同样的错误。这主要原因是，这个临时表信息在innodb的数据字典有残留，通过查询数据字典视图information_schema.innodb_sys_tables，可以发现存在一条#sql-xxx的表记录。
深层次原因：ddl整个过程不是原子的，prepare过程中会新建frm文件，ibd文件，并更新数据字典；然后再进行拷贝全量+重放增量操作；最后再rename frm文件，idb文件，并修改数据字典。由于整个过程涉及到server层和innodb层，并不是一个大事务（每次改数据字典都是单独一个事务），所以执行过程中如果异常终止，就会导致临时表数据字典残留在系统表内。
影响：虽然临时表信息残留在数据字典内，但不影响用户后续操作。
解决方法：由于临时表与table-id强相关，如何改变table-id是我们需要做的，但表又不能被修改，table-id改变不了。这就成了一个悖论，要做ddl，需要改变table-id；要改变table-id，又需要通过ddl操作。查看源码后发现，对于online ddl，临时表名依赖于变更表的table-id（比如#sql-ib79，79就是变更表的table-id），而对于copy类型（非online）的ddl，临时表名则不依赖于table-id（由mysqld进程号+连接会话号产生，比如sql-604d_2，604d是mysqld进程号，2是会话号）。因此，我们通过copy类型的ddl，就可以产生表名不一样的临时表了，也就可以完成ddl任务了。比如：alter table test_log add column c88 int, ALGORITHM=copy;
其它：ddl异常结束，会导致重做ddl失败。如果做ddl过程中，kill query，这个时候ddl也会退出，但退出前会做好善后工作，清理数据字典，因此再次做ddl不会存在问题。
十、ONLINE DDL BUG
执行 Online DDL 时有可能会遇到重复键值错误（ERROR 1062 (23000): Duplicate entry）。可以查看 bug＃76895，通过该 Bug 链接可以查看到该问题官方认为是 Online DDL 的一个限制问题，并不是 Bug。所以这个问题目前还没有得到解决。
当你 Alter Add Column 使用 ALGORITHM=INPLACE,LOCK=NONE 时，虽然属于 in-place，但在 InnoDB 层还是会有复制表的操作，但可以并发执行 DML，这些 DML 操作会被缓存在 Online Log 中。在数据复制完成后会应用 Online Log 记录的这段时间的增量修改，只要对主键或唯一键出现破坏唯一性约束的操作，都会导致 Online DDL 执行失败。
复现一下场景：
|
1 2 3 4 5 6 7 |
CREATE TABLE `s1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_c` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
查看一下数据：
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select * from s1 limit 5; +----+------+------+ | id | name | c | +----+------+------+ | 1 | dkey | 1 | | 2 | dkey | 2 | | 3 | dkey | 3 | | 4 | dkey | 4 | | 5 | dkey | 5 | +----+------+------+ 5 rows in set (0.00 sec) |
执行 Online DDL 的同时执行 insert into …
| Session A | Session B |
| alter table s1 add t int default 0; | |
| insert into s1(name,c) values(‘dkey’,5); ERROR 1062 (23000): Duplicate entry ‘5’ for key ‘uniq_c’ |
|
| DDL执行报错 – ERROR 1062 (23000): Duplicate entry ‘831071’ for key ‘PRIMARY’ |
可以看出来复现这个问题很容易，只要对主键或唯一键出现破坏唯一性约束的操作，都会导致 Online DDL 执行失败。包括下列操作：
|
1 |
insert into s1(id,name,c) values(1000000,'dkey',5) on duplicate key update c=1; |
另外，再看一下 replace 操作导致 Online DDL 失败的过程：
| Session A | Session B |
| alter table s1 add t int default 0; | |
| replace into s1(id,name,c) values(1,’dkey’,5); Query OK, 3 rows affected (0.04 sec) |
|
| DDL执行报错 – ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’ |
这个 replace 操作很有意思的，影响行数为 3，我们看一下 replace 更新后的结果。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select * from s1 limit 5; +----+------+------+ | id | name | c | +----+------+------+ | 1 | dkey | 5 | | 2 | dkey | 2 | | 3 | dkey | 3 | | 4 | dkey | 4 | | 6 | dkey | 6 | +----+------+------+ 5 rows in set (0.00 sec) |
两条数据被合并成了一条，先根据主键更新了一次，然后根据唯一键更新了一次，replace 内部处理了。但是在 DDL 时出现重复了。
可以看出，业务出现以上几种操作时都会出现 Online DDL 执行不成功。从现象来看，客户端都已经抛出重复键错误了，但好像 Online DDL 仍然将报错放入到 Online Log 中了，导致应用这些 Log 的时候报错了，具体可能需要看代码了。
简单的解决办法也是有的，多尝试几次，可能会成功；另外使用 PT-OSC/GH-OST 工具或者使用 ALGORITHM=COPY,LOCK=SHARED 算法加读锁。
<参考>
