mysql DDL

MySQL DDL 的原理简析

1、copy 算法

较简单的实现方法，MySQL 会建立一个新的临时表，把源表的所有数据写入到临时表，在此期间无法对源表进行数据写入。MySQL在完成临时表的写入之后，用临时表替换掉源表。这个算法主要被早期(<=5.5)版本所使用。

2、inplace 算法

从 5.6 开始，常用的 DDL 都默认使用这个算法。inplace 算法包含两类：inplace-no-rebuild 和 inplace-rebuild，两者的主要差异在于是否需要重建源表。

inplace 算法的操作阶段主要分为三个：Prepare阶段： - 创建新的临时 frm 文件(与 InnoDB 无关)。 - 持有 EXCLUSIVE-MDL 锁，禁止读写。 - 根据 alter 类型，确定执行方式(copy，online-rebuild，online-not-rebuild)。 更新数据字典的内存对象。 - 分配 row_log 对象记录数据变更的增量(仅 rebuild 类型需要)。 - 生成新的临时ibd文件 new_table(仅rebuild类型需要)。

Execute 阶段：降级EXCLUSIVE-MDL锁，允许读写。扫描old_table聚集索引(主键)中的每一条记录 rec。遍历new_table的聚集索引和二级索引，逐一处理。根据 rec 构造对应的索引项。将构造索引项插入 sort_buffer 块排序。将 sort_buffer 块更新到 new_table 的索引上。记录 online-ddl 执行过程中产生的增量(仅 rebuild 类型需要)。重放 row_log 中的操作到 new_table 的索引上(not-rebuild 数据是在原表上更新)。重放 row_log 中的DML操作到 new_table 的数据行上。

Commit阶段：当前 Block 为 row_log 最后一个时，禁止读写，升级到 EXCLUSIVE-MDL 锁。重做 row_log 中最后一部分增量。更新 innodb 的数据字典表。提交事务(刷事务的 redo 日志)。修改统计信息。rename 临时 ibd 文件，frm文件。变更完成，释放 EXCLUSIVE-MDL 锁。

3、instant 算法

MySQL 8.0.12 才提出的新算法，目前只支持添加列等少量操作，利用 8.0 新的表结构设计，可以直接修改表的 metadata 数据，省掉了 rebuild 的过程，极大的缩短了 DDL 语句的执行时间。

4、pt-online-schema-change

借鉴了 copy 算法的思路，由外部工具来完成临时表的建立，数据同步，用临时表替换源表这三个步骤。其中数据同步是利用 MySQL 的触发器来实现的，会少量影响到线上业务的 QPS 及 SQL 响应时间。

三、mysql 8.0特性instant add column

1、instant add column原理

mysql数据库针对亿级别的大表加字段是痛苦的，需要对表进行重建，MySQL 5.7 支持 Online DDL，大部分 DDL 不影响对表的读取和写入，但是依然会消耗非常多的时间，且占用额外的磁盘空间，并会造成主从延迟。所以大表 DDL 仍是一件令 DBA 头痛的事。而mysql8.0使用instant ADD COLUMN特性，只需很短的时间，字段就加好了，享受MongoDB那样的非结构化存储的灵活方便,无形中减少了开发的工作量。

快速加列采用的是 instant 算法，使得添加列时不再需要 rebuild 整个表，只需要在表的 metadata 中记录新增列的基本信息即可。在 alter 语句后增加 ALGORITHM=INSTANT 即代表使用 instant 算法， 如果未明确指定，则支持 instant 算法的操作会默认使用。如果 ALGORITHM=INSTANT 指定但不支持，则操作立即失败并显示错误。

下表概述了对列操作的在线 DDL 支持。星号表示附加信息、异常或依赖项。

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a column	Yes*	Yes	No*	Yes*	Yes
Dropping a column	Yes*	Yes	Yes	Yes	Yes
Renaming a column	Yes*	Yes	No	Yes*	Yes
Reordering columns	No	Yes	Yes	Yes	No
Setting a column default value	Yes	Yes	No	Yes	Yes
Changing the column data type	No	No	Yes	No	No
Extending VARCHAR column size	No	Yes	No	Yes	Yes
Dropping the column default value	Yes	Yes	No	Yes	Yes
Changing the auto-increment value	No	Yes	No	Yes	No*
Making a column NULL	No	Yes	Yes*	Yes	No
Making a column NOT NULL	No	Yes*	Yes*	Yes	No
Modifying the definition of an ENUM or SET column	Yes	Yes	No	Yes	Yes

一般情况下的建议：如果使用的是 MySQL 5.5 或者 MySQL 5.6，推荐使用 gh-ost
如果使用的是 MySQL 5.7，索引等不涉及修改数据的操作，建议使用默认的 inplace 算法。如果涉及到修改数据(例如增加列)，不关心主
从同步延时的情况下使用默认的 inplace 算法，关心主从同步延时的情况下使用 gh-ost
如果使用的是 MySQL 8.0，推荐使用 MySQL 默认的算法设置，在语句不支持 instant 算法并且在意主从同步延时的情况下使用gh-ost1.2.3.4.

补充

更多信息可以查看mysql官方文档

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html