MySQL5.7 InnoDB在线DDL操作

MYSQL官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

• 在线DDL原理
• 在线DDL支持情况
  • Index Operations（索引操作）
  • Primary Key Operations（主键操作）
  • Column Operations（列操作）
  • Table Operations（表操作）
• pt-osc方式
• 在线DDL和pt-osc对比
• 参考

在线DDL原理

MySQL5.6开始支持在线DDL操作。在这之前，对表的修改会阻塞整个表的读写操作。

在5.6之后，可以使用ALTER TABLE语句的ALGORITHM和LOCK子句来控制DDL操作的各个方面。这些子句位于语句的末尾，通过逗号与表和列规范分开。例如：

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

LOCK子句有助于微调对表的并发访问程度。ALGORITHM子句主要用于性能比较，以及作为在遇到任何问题时回落到旧的表复制行为的备用方案。

• ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝，另一种是COPY，就是之前的拷贝方式。一般都是默认，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。
• LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求）、SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE）和 DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）。

2种算法原理

• COPY方式
  1. 按照原表定义创建一个新的临时表；
  2. 对原表加写锁（禁止DML，允许select）；
  3. 在步骤1 建立的临时表执行 DDL；
  4. 将原表中的数据 copy 到临时表；
  5. 释放原表的写锁；
  6. 将原表删除，并将临时表重命名为原表。

采用 copy 方式期间需要锁表，禁止DML，因此是非Online的

• INPLACE方式
  在原表上进行更改，不需要生成临时表，不需要进行数据copy的过程。根据是否行记录格式，又可分为两类：

  • rebuild：需要重建表（重新组织聚簇索引）
  • no-rebuild：不需要重建表，只需要修改表的元数据

  对于 rebuild 方式实现 Online 是通过缓存 DDL 期间的 DML，待 DDL 完成之后，将 DML 应用到表上来实现的。例如，执行一个 alter table A engine=InnoDB; 重建表的 DDL 其大致流程如下：

  1. 建立一个临时文件，扫描表 A 主键的所有数据页；
  2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
  3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
  4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
  5. 用临时文件替换表 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的资源，效率较低，优点是可以快速停止

执行流程
Online DDL主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段

• prepare阶段：持有EXCLUSIVE-MDL锁，禁止读写（禁止DML，DDL）
• ddl执行阶段：降级EXCLUSIVE-MDL锁，允许读写（copy不可写）（允许DML，禁止DDL）
• commit阶段：升级EXCLUSIVE-MDL锁，禁止读写（禁止DML，DDL）

Online DDL 过程中占用 exclusive MDL 的步骤执行很快，所以几乎不会阻塞 DML 语句。
不过，在 DDL 执行前或执行时，其他事务可以获取 MDL（metadata lock）。由于需要用到 exclusive MDL，所以必须要等到其他占有 metadata lock 的事务提交或回滚后才能执行上面两个涉及到 MDL 的地方。

（MDL说明：在访问一个表的时候会被自动加上，以保证读写的正确性。当对一个表做 DML 操作的时候，加 MDL 读锁；当做 DDL 操作时候，加 MDL 写锁）


虽然在线DDL不阻塞DML，但是对大表做操作，还是涉及到数据库IO，CPU的消耗，尽量在业务低峰进行。

在线DDL支持情况

Index Operations（索引操作）

创建二级索引，In Place的方式执行，整个过程只会涉及到拷贝二级索引列相关的数据用于创建索引


创建/删除二级索引，表仍然可以进行读写操作。这些操作只有在访问表的所有事务完成后才结束


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


删除索引，只是元数据修改，实际索引占用的存储空间并不会立刻释放。需要执行optimize table才能释放出空间

Primary Key Operations（主键操作）

主键的修改，都会重建表，一般也不会进行此类修改，就不讨论这种情况了。

Column Operations（列操作）

对于列的修改（新增，删除，重新排序，变更列类型，修改列为空或非空）都会重建表（COPY的方式）


会锁表的情况（不支持并发DML）：

• 新增自增列：数据被大量重新组织，使其成为一个昂贵的操作。至少需要ALGORITHM=INPLACE, LOCK=SHARED。
• 修改列类型

VARCHAR类型的修改：

• VARCHAR列所需的长度字节数必须保持不变。对于大小为0到255字节的VARCHAR列，需要一个字节来编码值。对于大小为256字节或更大的VARCHAR列，需要两个字节。因此，In Place ALTER TABLE只支持将VARCHAR列大小从0到255字节增加，或从256字节增加到更大的大小。In Place ALTER TABLE不支持将VARCHAR列大小从小于256字节增加到等于或大于256字节。在这种情况下，所需的长度字节数从1变为2，这仅由表复制（ALGORITHM=COPY）支持。
• 减少VARCHAR大小也需要表复制（ALGORITHM=COPY）支持

仅修改列名，设置/删除默认值，仅修改元数据信息

Table Operations（表操作）

一般常用的就Optimizing a table，对于具有FULLTEXT索引的表，不支持原地操作。操作使用INPLACE算法，但不允许使用ALGORITHM和LOCK语法。


表的重命名，也不需要复制。（您也可以使用RENAME TABLE语句重命名表。详情请参阅RENAME TABLE语句。）但重命名的表授予的权限不会迁移到新名称。必须手动更改

pt-osc方式

pt-osc 用于修改表时不锁表，简单地说，这个工具创建一个与原始表一样的新的空表，并根据需要更改表结构，然后将原始表中的数据以小块形式复制到新表中，然后删除原始表，然后将新表重命名为原始名称。在复制过程中，对原始表的所有新的更改（insert，delete，update）都将应用于新表，因为在原始表上创建了一个触发器，以确保所有新的更改都将应用于新表。

流程如下：

1. 创建一个和要执行 alter 操作的表一样的新的空表结构 (是 alter 之前的结构)；

2. 在新表执行 alter table 语句（速度应该很快）；

3. 在原表中创建触发器 3 个触发器分别对应 insert,update,delete 操作，如果表中已经定义了触发器这个工具就不能工作了；

4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表，保证数据不会丢失（会限制每次拷贝数据的行数以保证拷贝不会过多消耗服务器资源，采用 LOCK IN SHARE MODE 来获取要拷贝数据段的最新数据并对数据加共享锁阻止其他会话修改数据，不过每次加 S 锁的行数不多，很快就会被释放）；

5. 将原表 Rename 为 old 表，再把新表 Rename 为原表（整个过程只在 rename 表的时间会锁一下表，其他时候不锁表）；

6. 如果有参考该表的外键，根据 alter-foreign-keys-method 参数的值，检测外键相关的表，做相应设置的处理（根据修改后的数据，修改外键关联的子表），如果被修改表存在外键定义但没有使用 --alter-foreign-keys-method 指定特定的值，该工具不予执行；

7. 默认最后将旧原表删除、触发器删除。

限制

1. 在使用此工具之前，应为表定义 PRIMARY KEY 或唯一索引，因为它是 DELETE 触发器所必需的；

2. 如果表已经定义了触发器，则不支持 pt-osc ；（注：不是不能有任何触发器，只是不能有针对 insert、update、delete 的触发器存在，因为一个表上不能有两个相同类型的触发器）；

3. 如果表具有外键约束，需要使用选项 --alter-foreign-keys-method，如果被修改表存在外键定义但没有使用 --alter-foreign-keys-method 指定特定的值，该工具不予执行；

4. 还是因为外键，对象名称可能会改变（indexes names 等）；

5. 在 Galera 集群环境中，不支持更改 MyISAM 表，系统变量 wsrep_OSU_method 必须设置为总序隔离（Total Order Isolation，TOI）；

6. 此工具仅适用于 MySQL 5.0.2 及更新版本（因为早期版本不支持触发器）；

7. 需要给执行的账户在 MySQL 上授权，才能正确运行。（应在服务器上授予 PROCESS、SUPER、REPLICATION SLAVE 全局权限以及 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER 和 TRIGGER 表权限。Slave 只需要 REPLICATION SLAVE 和 REPLICATION CLIENT 权限。）

在线DDL和pt-osc对比

对于常见的DDL选择

• 创建二级索引：在线DDL使用InPlace方式执行，整个过程，只涉及到拷贝二级索引列相关的数据用于创建索引，相对于pt-osc，复制数据量会少很多，磁盘IO相对pt-osc少。但是选择在线DDL，会有主从延迟的问题

• 删除索引：在线DDL仅修改元数据，选择在线DDL就好

• 新增列，删除列，变更列类型，修改列为空或非空：在线DDL都会执行Rebuilds Table，而且还有主从延迟的问题。建议选择pt-osc

• 修改列名，设置/删除默认值：在线DDL仅修改元数据，选择在线DDL就好

• 增加/减少varchar字段长度：参见上述Column Operations（列操作）。如果所需的长度字节数从1变为2，建议选择pt-osc

• 执行Optimize table：在线DDL会执行Rebuilds Table，建议选择pt-osc

• 修改表名：在线DDL仅修改元数据，选择在线DDL就好

参考

https://zhuanlan.zhihu.com/p/567083306
https://zhuanlan.zhihu.com/p/633326199
https://blog.csdn.net/jdcdev_/article/details/140521811