- 当前位置：首页 >域名 >聊聊 MySQL 的 Online DDL
聊聊 MySQL 的 Online DDL
发布时间：2025-11-04 06:49:27 来源：技术快报 作者：IT科技
今天与大家一起分享一下 mysql DDL执行方式。聊聊
一般来说MySQL分为DDL（定义）和DML（操作）。聊聊
DDL：Data Definition Language，聊聊即数据定义语言，聊聊那相关的聊聊定义操作就是DDL，包括：新建、聊聊修改、聊聊删除等；相关的聊聊命令有：CREATE，ALTER，聊聊DROP，聊聊TRUNCATE截断表内容（开发期，聊聊还是聊聊挺常用的），COMMENT 为数据字典添加备注。聊聊DML：Data Manipulation Language，聊聊即数据操作语言，聊聊即处理数据库中数据的操作就是DML，包括：选取，插入，更新，删除等；相关的命令有：SELECT,INSERT,UPDATE,DELETE，还有 LOCK TABLE，以及不常用的CALL – 调用一个PL/SQL或Java子程序，EXPLAIN PLAN – 解析分析数据访问路径。我们可以认为：
CREATE，ALTER ，云服务器DROP，TRUNCATE，定义相关的命令就是DDL;SELECT，INSERT，UPDATE，DELETE，操作处理数据的命令就是DML；DDL、DML区别：
DML操作是可以手动控制事务的开启、提交和回滚的。DDL操作是隐性提交的，不能rollback，一定要谨慎哦！日常开发我们对一条DML语句较为熟悉，很多开发人员都了解sql的执行过程，比较熟悉，但是DDL是如何执行的呢，大部分开发人员可能不太关心，也认为没必要了解，都交给DBA吧。其实不然，WordPress模板了解一些能尽量避开一些ddl的坑，那么下面带大家一起了解一下DDL执行的方式，也算抛砖引玉吧。如有错误，还请各位大佬们指正。
概述
在MySQL使用过程中，根据业务的需求对表结构进行变更是个普遍的运维操作，这些称为DDL操作。常见的DDL操作有在表上增加新列或给某个列添加索引。
我们常用的易维平台提供了两种方式可执行DDL，包括MySQL原生在线DDL（online DDL）以及一种第三方工具pt-osc。
下图是执行方式的性能对比及说明：
本文将对DDL的执行工具之Online DDL进行简要介绍及分析，pt-osc会专门再进行介绍。
介绍
MySQL Online DDL 功能从 5.6 版本开始正式引入，发展到现在的 8.0 版本，经历了多次的调整和完善。其实早在 MySQL 5.5 版本中就加入了 INPLACE DDL 方式，但是因为实现的问题，依然会阻塞 INSERT、源码下载UPDATE、DELETE 操作，这也是 MySQL 早期版本长期被吐槽的原因之一。
在MySQL 5.6版本以前，最昂贵的数据库操作之一就是执行DDL语句，特别是ALTER语句，因为在修改表时，MySQL会阻塞整个表的读写操作。例如，对表 A 进行 DDL 的具体过程如下：
按照表 A 的定义新建一个表 B对表 A 加写锁在表 B 上执行 DDL 指定的操作将 A 中的数据拷贝到 B释放 A 的写锁删除表 A将表 B 重命名为 A在以上 2-4 的过程中，如果表 A 数据量比较大，拷贝到表 B 的过程会消耗大量时间，并占用额外的存储空间。此外，由于 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。
如果遇到巨大的表，可能需要几个小时才能执行完成，势必会影响应用程序，因此需要对这些操作进行良好的规划，以避免在高峰时段执行这些更改。对于那些要提供全天候服务（24*7）或维护时间有限的人来说，在大表上执行DDL无疑是一场真正的噩梦。
因此，MySQL官方不断对DDL语句进行增强，自MySQL 5.6 起，开始支持更多的 ALTER TABLE 类型操作来避免数据拷贝，同时支持了在线上 DDL 的过程中不阻塞 DML 操作，真正意义上的实现了 Online DDL，即在执行 DDL 期间允许在不中断数据库服务的情况下执行DML(insert、update、delete)。然而并不是所有的DDL操作都支持在线操作。到了 MySQL 5.7，在 5.6 的基础上又增加了一些新的特性，关注公众号：码猿技术专栏，回复关键词：1111 获取阿里内部Java性能调优手册！比如：增加了重命名索引支持，支持了数值类型长度的增大和减小，支持了 VARCHAR 类型的在线增大等。但是基本的实现逻辑和限制条件相比 5.6 并没有大的变化。
用法
复制ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;1.ALTER 语句中可以指定参数 ALGORITHM 和 LOCK 分别指定 DDL 执行的算法模式和 DDL 期间 DML 的锁控制模式。
ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题）。如果设置 ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的方式，采用表拷贝的方式进行，过程中会阻塞所有的DML。另外也可以设置 ALGORITHEM=DAFAULT，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景）、SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景）和 DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）。两种算法
第一种 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 的数据文件。说明：
在 copy 数据到新表期间，在原表上是加的 MDL 读锁（允许 DML，禁止 DDL）；在应用增量期间对原表加 MDL 写锁（禁止 DML 和 DDL）；根据表 A 重建出来的数据是放在 tmp_file 里的，这个临时文件是 InnoDB 在内部创建出来的，整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个原地操作，这就是”inplace”名称的来源。使用Inplace方式执行的DDL，发生错误或被kill时，需要一定时间的回滚期，执行时间越长，回滚时间越长。
使用Copy方式执行的DDL，需要记录过程中的undo和redo日志，同时会消耗buffer pool的资源，效率较低，优点是可以快速停止。
不过并不是所有的 DDL 操作都能用 INPLACE 的方式执行，具体的支持情况可以在（在线 DDL 操作) 中查看。
以下是常见DDL操作：
官网支持列表：
执行过程
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 能可以正常执行。
降级EXCLUSIVE-MDL锁，允许读写（copy不可写）。扫描old_table的聚集索引每一条记录rec。遍历新表的聚集索引和二级索引，逐一处理。根据rec构造对应的索引项将构造索引项插入sort_buffer块排序。将sort_buffer块更新到新的索引上。记录ddl执行过程中产生的增量(仅rebuild类型需要)重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)。重放row_log间产生dml操作append到row_log最后一个Block。3）Commit阶段：将 shared metadata lock 升级为 exclusive metadata lock，禁止DML，然后删除旧的表定义，提交新的表定义。
当前Block为row_log最后一个时，禁止读写，升级到EXCLUSIVE-MDL锁。重做row_log中最后一部分增量。更新innodb的数据字典表。提交事务(刷事务的redo日志)。修改统计信息。rename临时idb文件，frm文件。变更完成。
Online DDL 过程中占用 exclusive MDL 的步骤执行很快，所以几乎不会阻塞 DML 语句。
不过，在 DDL 执行前或执行时，其他事务可以获取 MDL。由于需要用到 exclusive MDL，所以必须要等到其他占有 metadata lock 的事务提交或回滚后才能执行上面两个涉及到 MDL 的地方。
踩坑
前面提到 Online DDL 执行过程中需要获取 MDL，MDL (metadata lock) 是 MySQL 5.5 引入的表级锁，在访问一个表的时候会被自动加上，以保证读写的正确性。当对一个表做 DML 操作的时候，加 MDL 读锁；当做 DDL 操作时候，加 MDL 写锁。
为了在大表执行 DDL 的过程中同时保证 DML 能并发执行，前面使用了 ALGORITHM=INPLACE 的 Online DDL，但这里仍然存在死锁的风险，问题就出在 Online DDL 过程中需要 exclusive MDL 的地方。
例如，Session 1 在事务中执行 SELECT 操作，此时会获取 shared MDL。由于是在事务中执行，所以这个 shared MDL 只有在事务结束后才会被释放。
复制# Session 1> START TRANSACTION;> SELECT * FROM tbl_name;# 正常执行1.这时 Session 2 想要执行 DML 操作也只需要获取 shared MDL，仍然可以正常执行。
复制# Session 2> SELECT * FROM tbl_name;# 正常执行1.但如果 Session 3 想执行 DDL 操作就会阻塞，因为此时 Session 1 已经占用了 shared MDL，而 DDL 的执行需要先获取 exclusive MDL，因此无法正常执行。
复制# Session 3> ALTER TABLE tbl_name ADD COLUMN n INT;# 阻塞1.通过 show processlist 可以看到 ALTER 操作正在等待 MDL。
由于 exclusive MDL 的获取优先于 shared MDL，后续尝试获取 shared MDL 的操作也将会全部阻塞
复制+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+ | Id | User | Host | db | Command | Time | State | Info |│----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+ | 11 | root | 172.17.0.1:53048 | demo | Query | 3 | Waiting for table metadata lock | alter table ... |+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+1.2.3.到这一步，后续无论是 DML 和 DDL 都将阻塞，直到 Session 1 提交或者回滚，Session 1 占用的 shared MDL 被释放，后面的操作才能继续执行。
上面这个问题主要有两个原因：
Session 1 中的事务没有及时提交，因此阻塞了 Session 3 的 DDLSession 3 Online DDL 阻塞了后续的 DML 和 DDL对于问题 1，有些ORM框架默认将用户语句封装成事务执行，如果客户端程序中断退出，还没来得及提交或者回滚事务，就会出现 Session 1 中的情况。那么此时可以在 infomation_schema.innodb_trx 中找出未完成的事务对应的线程，并强制退出。
复制> SELECT * FROM information_schema.innodb_trx\G- Copyright © 2025 Powered by 聊聊 MySQL 的 Online DDL,技术快报 滇ICP备2023006006号-46sitemap