源于最近有几次对于一些大表进行DDL操作，但可能因为对InnoDB引擎的Online DDL的原理所知甚少，以至于有一些同学一开口就是，"执行DDL时会锁表，如果数据量太大锁表时间太长，会阻塞到线上业务"，为确保以后进行DDL操作时能够做到心中有数，对Online DDL做一些总结。

前身

MySQL于5.6版本推出Online DDL，那么在没有Online DDL前，它是如何做DDL？主要分为两种形式。

copy table

• create temp table，创建临时表结构，保持与原表结构一致
• lock original table，不允许对原表进行DML操作，仅允许Queries
• ddl on temp table，在临时表上执行DDL (lock)
• copy original table data into temp table，将原表数据复制到临时表 (lock)
• rename temp tablename to oringal tablename，对原表加锁并进行rename操作，不允许DML & Queries，直至整个过程完成。

从整个执行过程来分析，为了保证执行期间表数据的一致性，必须通过加锁来阻止DML操作，只允许Queries。当表数据量过大时，在复制阶段所需要的时间过长导致整个语句在执行期间加锁阻塞其他事务的DML语句，故而导致应用连接数撑爆，大量事务超时等问题。

inplace

inplace又称为fast index creation，仅支持索引的创建。

• create frm, 创建数据字典
• lock original table，不允许对原表进行DML操作，仅允许Queries
• copy table，按照聚簇索引顺序读取数据，构造新的索引项，顺序插入新的索引页
• lock original table，锁表，不允许DML & Queries
• rename，替换frm文件，完成DDL过程

inplace方式比起需要copy table自然是更优的，因为只需要从聚簇索引读取新索引的列项出来构造新索引页，其他索引不受影响。但是，无论是copy table 还是 inplace在执行操作的过程中都需要长时间锁表，阻塞DML语句，毫无疑问对于服务运行而言简直是毁灭式的行为，因此online ddl迫在眉睫。

在讨论online ddl前，有必要先了解几个点，其中包括 Innodb索引组织形式，LOCK CAUSE，METADATA LOCK。

索引组织形式

关于索引的官方文档

开发同学在构建数据库表时，都会根据业务特点选择合适的索引，因为索引的目的是加速搜索。从官网文档中可以看到Innodb存储引擎将索引分为Clustered Index以及Secondary Index，称为聚集索引与二级索引（也称聚簇索引，非聚簇索引）。

二者如何区分？Innodb只会有一个聚簇索引，聚簇索引的叶子节点上存储的是Row Data，一般情况下是表定义中的PRIMARY KEY，如果没有则选择表定义中第一个非空UNIQUE KEY，如果表定义中即没有PK，也没有UK，则会通过Innodb隐藏的一个ROW ID作为索引。而处理聚簇索引之外的其他索引，皆称为二级索引。二级索引的叶子节点上存储的是聚簇索引的值。

正因为这个特征，很多企业定义的SQL规范中都会有类似：

• PK尽量选择长整型，且趋势递增，推荐使用AUTO INCREMENT
• PK不适合较长的字符串
• ...

LOCK CAUSE

关于LOCK CAUSE的官方文档

默认情况下，MySQL会选择尽可能的轻量的锁来完成DDL操作，在某些特定情况下可以更加严格的锁来完成操作。比如前文提到，DDL过程中完成不允许DML，仅允许Queries，也可以不允许DML & Queries等。

• LOCK=NONE
  Permits concurrent queries and DML.
• LOCK=SHARED
  Permits concurrent queries but blocks DML.
• LOCK=DEFAULT
  Permits as much concurrency as possible (concurrent queries, DML, or both). Omitting the LOCK clause is the same as specifying LOCK=DEFAULT.
• LOCK=EXCLUSIVE
  Blocks concurrent queries and DML

METADATA LOCK

关于METADATA LOCK的官方文档
关于METADATA LOCK的补充文档

元数据锁，是MySQL的表锁之一，属于隐式锁（另一种是显式锁，通过lock table ... with read/write指定）。在补充文档中可以了解到元数据锁的由来，也对其进行了解释。补充文档中提及元数据锁有11种类型，常用的有MDL_SHARED_READ,MDL_SHARED_WRITE及MDL_EXCLUSIVE。元数据锁是一个跟随事务结束而释放的锁，由MySQL控制，保护处于事务中的表元数据的一致性。

• MDL_SHARED_READ，Queries时加锁，属于共享锁
• MDL_SHARED_WRITE，DML时加锁，属于共享锁
• MDL_EXCLUSIVE，DDL时加锁，属于独占锁

Online DDL

简述

MySQL在5.6.7版本推了Online DDL能力，主要是在基于原有的fast index creation上增强实现。其次，假定前提，衡量DDL是否足够Online在于是否长时间允许DML。

目前Online DDL支持两种形式

• COPY
• INPLACE

MySQL如何选择该两种形式呢？

• 支持指定，即在执行语句上指定ALGORITHM=INPLACE / COPY。
• 默认对于不支持Online DDL的sql语句则采用COPY，相反则采用INPLACE。

INPLACE会根据是否涉及到修改行记录格式分为三种情形

• Rebuilds Table，修改了行记录格式，比如修改列类类型、增减列等
• Not Rebuilds Table & Not Only Modifies Metadata，不需要重建表但是也不仅仅只是修改元数据，比如增加索引。
• Only Modifies Metadata，仅修改元数据，比如删除索引、设置列默认值，重命名列名等

Online DDL支持选项

• ALGORITHM={COPY|INPLACE}
• LOCK={NONE|SHARED|DEFAULT|EXCLUSIVE}，参照前文LOCK CAUSE

可以从官方文档查看Online DDL支持情况。

有几个点需要特意说明

• INPLACE并不表示绝对支持并行DML，但是COPY绝对不支持并行DML。
• 简单区分 INPLACE与COPY，在于是否需要创建临时表
• 简单区分 INPLACE与COPY，COPY主要由Server支持，INPLACE主要由Innodb支持
• INPLACE不是不需要额外的数据空间，取决是否为Only Modifies Metadata
• Only Modifies Metadata不需要Rebuilds Table，不需要Rebuilds Table的不一定是Only Modifies Metadata

image.png

实现原理

Online DDL主要分为3个阶段，PREPARE，EXECUTE，COMMIT

• PREPARE
  • 创建新的临时frm文件
  • 持有MDL_EXCLUSIVE锁，禁止读写
  • 根据alter类型，确定执行方式（copy，rebuild，not-rebuild）
  • 更新数据字典的内存对象
  • 若是需要rebuild，分配row_log对象用于记录增量
  • 若是需要rebuild，生成新的临时ibd文件
• EXECUTE
  • 如果是仅修改元数据：
    • 这部分无操作
  • 其他，则是：
    • 降低MDL_EXCLUSIVE锁，允许DML & Queries（copy 不允许写）
    • 记录DDL执行过程中产生的增量row-log（非only modify metadata类型需要）
    • 扫描old_table的聚集索引每一条记录record
    • 遍历新表的聚集索引和二级索引，逐一处理
    • 根据record构造对应的索引项
    • 将构造索引项插入sort_buffer块
    • 将sort_buffer块插入新的索引
    • 把row_log中的操作应用到新临时表中，应用到最后一个Block
• COMMIT
  • 升级到MDL_EXECLUSIVE锁，禁止读写
  • 重做最后一部分的row_log增量
  • 更新innodb的数据字典表
  • 提交事务，写redo日志
  • 修改统计信息
  • rename临时的ibd文件、frm文件
  • DDL完成

在整个Online DDL的过程中，并非是完全的Permits Concurrent DML，但是由于整个过程中持有MDL_EXCLUSIVE锁的时间较短，所以近似的认为整个过程是Permits Concurrent DML

Online DDL带来的优势

• Online DDL期间，Queries和DML操作在多数情况下可以正常执行，锁表时间大大减少。
• 允许INPLACE操作的DDL，避免COPY方式的磁盘IO及CPU资源，减少对数据库的整体负荷，使得在DDL期间，能够维持数据库的高性能及高吞吐量；
• 允许INPLACE操作的 DDL，比需要COPY到临时表的操作要更少占用buffer pool，避免以往DDL过程中性能的临时下降，因为需要拷贝数据到临时表，这个过程会占用到buffer pool，导致内存中的部分频繁访问的数据会被清理出去。

资料

https://www.cnblogs.com/cchust/p/4639397.html
https://www.cnblogs.com/xinysu/p/6732646.html
https://www.cnblogs.com/dbabd/p/10381942.html
http://mysql.taobao.org/monthly/2021/03/06/