mysql--Each version of the DDL operation summary

MySQL 5.5 DDL

Prior to MySQL version 5.5, all DDL operations were done using copy table, which was not allowed to be written, read only, and introduced the FIC (Fast index creation) feature in MySQL version 5.5.

FCI 操作流程：（1）对表加共享S锁，允许其他会话读操作，但禁止写操作，（2）根据当前表数据创建索引，（3）新索引创建完成，解除S锁，允许读写。FCI 优点：（1）创建索引不需要拷贝整表数据，创建速度快，（2）创建索引过程中，可以快速中止。FCI限制：（1）FCI特新仅限于复制索引，不试用于聚集索引，（2）索引创建期间，表只允许读不允许写。

Before MySQL 5.6.7, the DDL operation mainly has copy and InPlace two ways, both ways need to lock table prohibit write operation, allow some time period of read operation, InPlace way only support add and delete index two ways.

Copy mode:

(1)新建带索引的临时表(2)锁原表，禁止DML，允许查询(3)将原表数据拷贝到临时表(无排序，一行一行拷贝)(4)进行rename，升级字典锁，禁止读写(5)完成创建索引操作

InPlace Way:

(1)新建索引的数据字典(2)锁表，禁止DML，允许查询(3)读取聚集索引，构造新的索引项，排序并插入新索引(4)等待打开当前表的所有只读事务提交(5)创建索引结束

MySQL 5.6 DDL

After the MySQL 5.6.7 release, Row_log was introduced to record the log generated by the write operation during the DDL period, so that in addition to the two short periods of time when the DDL operation started and ended, the table holding EXCLUSIVE-MDL lock was forbidden to read and write, and the remaining DDL operation phases allowed other calls to read and write to the table. Therefore, it can be counted as online DDL.

For online DDL operations, it also contains copy and InPlace methods, and for InPlace mode, can be subdivided into rebuild mode and no-rebuild way, Rebuild means actions that require a record to be re-organized, such as adding a delete column or exchanging column order, and no-rebuild means actions such as deleting and adding indexes that do not cause the record format to change.

The ONLINE DDL can be divided into three phases:

Prepare阶段：1.创建新的临时frm文件2.持有EXCLUSIVE-MDL锁，禁止读写3.根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)4.更新数据字典的内存对象5.分配row_log对象记录增量6.生成新的临时ibd文件ddl执行阶段：1.降级EXCLUSIVE-MDL锁，允许读写2.扫描old_table的聚集索引每一条记录rec3.遍历新表的聚集索引和二级索引，逐一处理4.根据rec构造对应的索引项6.将构造索引项插入sort_buffer块6.将sort_buffer块插入新的索引7.处理ddl执行过程中产生的增量(仅rebuild类型需要)commit阶段1.升级到EXCLUSIVE-MDL锁，禁止读写2.重做最后row_log中最后一部分增量3.更新innodb的数据字典表4.提交事务(刷事务的redo日志)5.修改统计信息6.rename临时idb文件，frm文件7.变更完成  Online DDL期间产生Row Log会按照Block来存放和处理，回放Row Log时按照Block来处理，一个Block回放完后处理下一个Block，只有到达最后一个Block时才会锁表，保证最后一个Block完成后新数据和老数据保持一致，因此Online DDL期间产生大量Row Log不会导致表被长时间锁定。

You only need to modify the DDL operation of the metadata:

（1）设置列默认值（2）设置自增列的自增值（3）删除索引

DDL operations can be in online No-rebuild mode:

（1）添加索引

DDL operations can be in online rebuild mode:

（1）添加列（2）删除列（3）交换列顺序（4）修改列NULL-NOTNULL属性（5）修改表ROW-FORMAT（6）添加修改主键

Only copy-mode DDL operations can be used:

（1）修改列类型（2）转换字符集（3）Optimize table（4）删除主键 PS: 从MySQL 5.6.17版本后，Optimize table可以采用Inplace方式操作。

Online DDL operation Related parameters:

（1）innodb_sort_buffer_size：用来存放Row log的Block大小由参数innodb_sort_buffer_size控制。（2）innodb_online_alter_log_max_size：控制整个DDL期间产生Row log的文件上限值，当产生的Row Log超过该上限值，则DDL操作失败，并回滚该期间所有未提交的并发DML操作。（3）innodb_sort_buffer_size：在DDL执行期间Row Log会写入到一个日志文件，该日志文件每次按照innodb_sort_buffer_size来扩展。（4）old_alter_table，当该参数被启用后，所有Alter操作将使用COPY方式操作。

A bug with a unique index:

（未找到该BUG出处）MySQL 在处理Row Log的时候存在BUG，会导致创建的唯一索引中可能存在不唯一KEY值的情况。

Duplicate Entry Questions:

在进行Online DDL操作过程中，可能遇到Duplicate entry的报错，但数据和修改命令都正常，该问题解释：When running an online DDL operation, the thread that runs the ALTER TABLE statement applies an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.连接：https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html

MySQL 5.7 DDL

In MySQL version 5.7, add the following new features:

• Support for modifying index name operations

  操作语法：ALTER TABLE t1 RENAME INDEX idx1 to idx2;该操作仅需要修改元数据信息和刷新缓存，因此修改操作能快速完成。

• Support for increasing the length of varchar columns online.

  在Innodb存储引擎中，字节长度小于255的列使用1个字节来标识列长，而对于字节长度超过255的列需要使用2个字节来标识列长。1、如果VARCHAR列长度仅在0-255或255-65535区间发生变化时，仅需要修改元数据信息而不需要对表进行Inplace操作，因此修改操作能快速完成。    语法：alter table tb002 ALGORITHM=INPLACE, CHANGE COLUMN c4 c4 varchar(500);       或：  alter table tb002 ALGORITHM=INPLACE, modify c4 varchar(600);2、如果VARCHAR列长度从0-255区间变化到255-65535区间，则只能使用COPY方式，不允许并发DML。3、如果缩小VARCHAR列的长度，也只能使用COPY方式，不允许并发DML。

• Support for adding primary keys using the InPlace method

  语法：ALTER TABLE tb002 ADD PRIMARY KEY(id),ALGORITHM=INPLACE;

MySQL DDL Summary:

Although MySQL versions 5.6 and 5.7 provide online DDL operations, the online DDL still has the following issues:

（1）主从复制延迟，只有主库上DDL执行成功才会写入到binlog中，而DDL操作在从库上不能并发执行，因此即使主库执行DDL时允许并发DML操作，对于大表操作，仍会引发严重的复制延迟。（2）主库执行Online DDL时，不能根据负载暂停DDL操作。（3）使用Inplace方式执行的DDL，发生错误或被KILL时，需要一定时间的回滚期，执行时间越长，回滚时间越长。（4）使用Copy方式执行的DDL，需要记录过程中的undo和redo日志，同时会消耗buffer pool的资源，效率较低，优点是可以快速停止。（5）Online DDL并不是所有时间段的Online，在特定时间段需要加元数据锁或其他锁。（6）允许并发DML的DDL，可能会导致Duplicate entry问题。

DDL Recommendations:

1、对于并发操作较高的表，无论表数据量多少，不能在业务高峰期操作，2、对于大表和较大表，如果对复制延迟和主库性能敏感，建议改为gh-ost或pt-osc工具，3、对于包含唯一索引创建的DDL，不能使用gh-ost或pt-osc工具，4、能业务低峰期操作的DDL，都尽量安排在业务低峰期进行。

mysql--Each version of the DDL operation summary