MySQL online DDL介绍

原创已于 2025-12-12 10:53:10 修改 · 1.7k 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2024-09-19 15:40:19 首次发布

MySQL 专栏收录该内容

11 篇文章

订阅专栏

部署运行你感兴趣的模型镜像

一、DDL简介

一般来说MySQL分为DDL（定义）和DML（操作）。

DDL：Data Definition Language，即数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引、约束等数据库对象。包括：新建、修改、删除等；相关的命令有：CREATE，ALTER，DROP等语句。
DML：Data Manipulation Language，即数据操作语言，即处理数据库中数据的操作就是DML，包括：插入，更新，删除等；相关的命令有：INSERT,UPDATE,DELETE。

DDL、DML区别：

DML操作是可以手动控制事务的开启、提交和回滚的。
DDL操作是隐性提交的，不能rollback，一定要慎之又慎！

执行DDL语句一般，DDL操作要在业务低峰期进行。

提供了两种方式可执行DDL，包括MySQL原生在线DDL（Online DDL）以及第三方工具pt-osc或gh-ost。

二、Online DDL

2.1 简介

在MySQL 5.6版本之前，DDL语句特别是ALTER语句，会阻塞整个表的读写操作。

例如，对表 A 进行 DDL 的具体过程如下：

按照表A的定义新建一个表B
对表A加写锁
在表B上执行DDL指定的操作
将A中的数据拷贝到B
释放A的写锁
删除表A
将表B重命名为A

如果表 A 数据量比较大，拷贝到表B的过程会消耗大量时间，并占用额外的存储空间。此外，由于DDL操作占用了表A的写锁，所以表A上的DDL和DML都将阻塞无法提供服务。

如果遇到巨大的表，可能需要几个小时才能执行完成，势必会影响应用程序，因此需要对这些操作进行良好的规划，以避免在高峰时段执行这些更改。

Online DDL支持更多的ALTER TABLE类型操作来避免数据拷贝，同时支持了在线上DDL的过程中不阻塞DML操作，即在执行DDL期间允许在不中断数据库服务的情况下执行DML。然而并不是所有的DDL操作都支持Online操作。

Online DDL的流程：

建立一个临时文件，扫描表A主键的所有数据页；
用数据页中表A的记录生成B+树，存储到临时文件中；
生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
用临时文件替换表A的数据文件；
删除原表A的文件。

2.2 语法

ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE, LOCK=NONE;

ALTER语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。退化的目的是为了实现Online MDL读锁不会阻塞增删改操作。不直接解锁是为了禁止其他线程对这个表同时做DDL。

Online DDL最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是Online的。

上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗IO和CPU资源的。

表级的锁是MDL（metadata lock)。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。

2.3 算法

MySQL本身自带三种方法，分别是：copy、inplace、instant。

copy 算法为最古老的算法，在 MySQL 5.5 及以下为默认算法。
从 MySQL 5.6 开始，引入了inplace算法并且默认使用。inplace算法还包含两种类型：rebuild-table和not-rebuild-table。MySQL使用inplace算法时，会自动判断，能使用not-rebuild-table的情况下会尽量使用，不能的时候才会使用rebuild-table。当DDL涉及到主键和全文索引相关的操作时，无法使用not-rebuild-table，必须使用rebuild-table。其他情况下都会使用not-rebuild-table。
从 MySQL 8.0.12 开始，引入了instant算法并且默认使用。目前instant算法只支持增加列等少量 DDL 类型的操作，其他类型仍然会默认使用 inplace。

MySQL 5.7 ALGORITHM支持的算法

ALGORITHM	说明
DEFAULT	默认算法，自动使用可用的最高效的算法
COPY	最原始的方式，所有的存储引擎都支持，不使用Online DDL，操作时会创建临时表，执行全表拷贝和重建，过程中会写入Redo Log和大量的Undo Log，需要添加读锁，非常低效
INPLACE	尽可能避免表拷贝和重建，由存储引擎决定如何实现，有些操作是可以立即生效的（比如重命名列，改变列的默认值等），但有些操作依然需要全表或者部分表的拷贝和重建（比如添加删除列、添加主键、改变列为 NULL 等）

Copy算法：

描述：按照原表定义创建一个新的临时表

流程：

对原表加写锁（禁止DML，允许select）；
在步骤1 建立的临时表执行DDL；
将原表中的数据 copy 到临时表；
释放原表的写锁；
将原表删除，并将临时表重命名为原表。

从上可见，采用copy方式期间需要锁表，禁止DML，因此是非Online的。比如：删除主键、修改列类型、修改字符集，这些操作会导致行记录格式发生变化（无法通过全量 + 增量实现Online）。

Inplace算法：

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

rebuild：需要重建表（重新组织聚簇索引）。比如 optimize table、添加索引、添加/删除列、修改列 NULL/NOT NULL 属性等；
no-rebuild：不需要重建表，只需要修改表的元数据，比如删除索引、修改列名、修改列默认值、修改列自增值等。

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

建立一个临时文件，扫描表 A 主键的所有数据页；
用数据页中表A的记录生成B+树，存储到临时文件中；
生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
用临时文件替换表 A 的数据文件。

根据表 A 重建出来的数据是放在临时文件里，这个临时文件是InnoDB在内部创建出来的，整个DDL过程都在InnoDB内部完成。对于server层来说，没有把数据挪动到临时表，是一个原地操作，这就是“inplace”名称的来源。

2.5 锁策略

默认情况下，MySQL/MariaDB 在执行DDL期间会使用尽可能少的锁，如果必要，可以通过LOCK子句控制在执行DDL 时允许对表加锁的级别。如果指定的操作所要求的限制级别不满足（EXCLUSIVE>SHARED>NONE），则语句执行失败并报错。

策略	说明
DEFAULT	使用当前操作支持的粒度最小的锁策略
NONE	不获取任何表锁，允许所有的 DML 操作
SHARED	对表添加共享锁（读锁），只允许只读的 DML 操作
EXCLUSIVE	对表添加排它锁（写锁），不允许任何 DML 操作

2.4 执行过程

Online DDL主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段。下面将主要介绍ddl执行过程中三个阶段的流程。

1、Prepare阶段：初始化阶段会根据存储引擎、用户指定的操作、用户指定的ALGORITHM 和LOCK计算DDL过程中允许的并发量，这个过程中会获取一个shared metadata lock，用来保护表的结构定义。

创建新的临时frm文件(与InnoDB无关)。
持有EXCLUSIVE-MDL锁，禁止读写。
根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)。假如是Add Index，则选择online-norebuild即INPLACE方式。
更新数据字典的内存对象。
分配row_log对象来记录增量(仅rebuild类型需要)。
生成新的临时ibd文件(仅rebuild类型需要) 。
数据字典上提交事务、释放锁。

注：Row log是一种独占结构，它不是redo log。它以Block的方式管理DML记录的存放，一个Block的大小为由参数innodb_sort_buffer_size控制，默认大小为1M，初始化阶段会申请两个Block。

2、DDL执行阶段：执行期间的 shared metadata lock 保证了不会同时执行其他的 DDL，但 DML 能可以正常执行。