MySQL Online DDL 进化史：从 5.6 到 8.0，DBA 终于不用半夜改表了

「在 MySQL 的世界里，ALTER TABLE 曾经是 DBA 最怕的两个单词」

如果你是从 MySQL 5.5 时代过来的老 DBA，一定对凌晨 3 点改表结构的经历记忆犹新。一张几千万行的表，加个字段可能要锁表几个小时，业务只能停服维护。运维群里一片哀嚎，开发同学问“还要多久”，你只能苦笑着说“看运气”。

从 5.6 到 8.0，MySQL 团队花了近十年时间，把 Online DDL 从"基本能用"打磨到"几乎无感"。这段历史里有技术突破，有重大翻车，有国人的重要贡献，也有 Oracle 的迷之操作。

我从 5.5 时代一路用过来，亲眼见证了这场变革。今天就来扒一扒这段进化史，顺便聊聊那些官方文档不会告诉你的坑。

先搞清楚：DDL 操作的三种执行方式

在讲版本差异之前，必须先理解三个核心概念，这是理解整篇文章的基础：

从 COPY 到 INPLACE 到 INSTANT，就是 Online DDL 的进化方向——让 DDL 对业务的影响越来越小。

📖 为什么叫"Online"：这里的 Online 不是指"联网"，而是指 DDL 执行期间业务可以"在线"继续读写，不需要停服维护。

⚠️ 常见误区：很多人以为 INPLACE 就等于 Online DDL，其实不然。Online 的本质是 LOCK=NONE
，即 DDL 执行期间不阻塞读写。而 COPY 算法最多只能做到 LOCK=SHARED
（允许读、阻塞写），所以 COPY 一定不是 Online。只有 INPLACE 或 INSTANT 配合 LOCK=NONE
，才是真正的 Online DDL。

记住这个判断标准：看到某个操作只支持 COPY 或 LOCK=SHARED EXCLUSIVE，心里就要有数——这不是 Online，业务会受影响。

还有一个关键维度：是否 Rebuild Table

除了算法和锁级别，官方文档还有一个容易被忽略的维度：Rebuilds Table（是否重建表）。

即使同样是 INPLACE 算法，有些操作需要重建整张表（如添加主键），有些只改元数据（如重命名索引）。重建表意味着：

• 需要额外的磁盘空间
• 执行时间与数据量成正比
• 会产生大量 IO 和 CPU 开销

⚠️ 磁盘空间的隐藏消耗

重建表（Rebuilds Table = Yes） 的空间消耗不止"约等于原表"这么简单：

• 数据目录会产生 ≈ 原表大小的中间表文件（如 #sql-ib…
  ）
• tmpdir innodb_tmpdir 可能生成排序文件（峰值可接近"表数据 + 索引"总量）
• 如果 tmpdir 与 datadir 在同一磁盘，整体峰值可能逼近 2~3× 原表大小
• Online DDL 的临时日志文件（记录并发 DML）(ALGORITHM=INPLACE, LOCK=NONE 下才需要用到这个日志)

不重建表（Rebuilds Table = No） 也不代表完全不需要额外空间：

• 仍可能产生 tmpdir 临时排序文件（如添加索引）
• Online DDL 的临时日志文件（记录并发 DML）(ALGORITHM=INPLACE, LOCK=NONE 下才需要用到这个日志)

经验法则：重建表类操作预留 2~3× 空间，不重建表类至少预留 20% 余量。DDL 结束后这些临时空间会释放。最大的坑就是完全没考虑临时空间消耗，跑到一半磁盘满了。

⚠️ innodb_online_alter_log_max_size 的坑

Online DDL 执行期间，并发的 DML 操作会被记录到一个临时日志文件（row log），最后再回放到新表。这个日志的大小上限由 innodb_online_alter_log_max_size
 控制，默认只有 128MB。

如果 DDL 持续时间较长，同时表的写入量又很大，128MB 可能不够用。一旦超出，DDL 会直接失败并回滚，前功尽弃。

-- 查看当前值
SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size';

-- 临时调大（如 1GB），在 DDL 前设置
SET GLOBAL innodb_online_alter_log_max_size = 1073741824;

对于大表或写入密集的场景，建议提前调大这个参数，或者直接用 gh-ost
 / pt-osc
 避开这个限制。

所以判断一个 DDL 的代价，要同时看三个维度：Algorithm + Lock + Rebuilds Table。

💡 善用官方文档：MySQL 官方文档有一个专门的页面，列出了所有 DDL 操作的执行方式对照表，包括支持的算法、锁级别、是否重建表等。这是 DBA 的必备参考：

• MySQL 8.0: Online DDL Operations
• MySQL 8.4: Online DDL Operations

建议收藏这个页面，每次做 DDL 前先查一下。

MySQL 5.5 及之前：黎明前的黑暗

在 5.5 时代，原生的 DDL 操作让 DBA 非常头疼，但并非全无希望。那时候我刚入行，每次要加索引、加字段都得提前跟业务方打招呼："今晚停服维护，预计 2-4 小时"。业务方的表情，你懂的。

Fast Index Creation (FIC) 的引入
MySQL 5.5 InnoDB 引擎引入了 Fast Index Creation。对于添加二级索引（Secondary Index），不再需要重建整个表，而是直接在原表文件上追加索引数据。

• 锁情况
  ：表会被加共享锁（Shared Mode）。这意味着读取可以继续，但写入会被阻塞。
• 痛点
  ：虽然不用拷全表了，但对于写密集型业务，长时间无法写入等同于停服。

其他操作的 COPY 噩梦
除了加索引，其他大部分操作（如添加列、修改列类型）依然是传统的 COPY 方式：

1. 创建一个带新结构的临时表
2. 加锁
   （通常会阻塞写）
3. 逐行复制数据到临时表（最耗时）
4. 删除原表，重命名临时表
5. 释放锁

一张 1 亿行的表，加个字段可能要几个小时，期间业务无法写入，简直是灾难。那时候运维群里最常见的对话就是："还要多久？" "看运气吧..."

救世主：第三方工具的崛起

正是因为官方方案不够完美，社区开始自己造轮子，涌现了基于“影子表”（Ghost Table）思路的解决方案。

📖 历史背景：早期的主流思路是"影子表 + 触发器"。

1. oak-online-alter-table（2009 年）
   ：出自 openark kit，由 Shlomi Noach 开发，是这一领域的先驱之一。
2. pt-online-schema-change（2011 年）
   ：Percona Toolkit 里的明星工具，完善了影子表方案，成为很多年的行业标准。
3. gh-ost（2016 年）
   ：后来 GitHub 开源了 gh-ost，抛弃了触发器，改用模拟从库读取 Binlog 来同步数据，对主库性能影响更小。

这些工具的核心逻辑都是：在后台悄悄建一个新表同步数据，最后瞬间切换。虽然步骤繁琐，但保证了线上业务的读写不受影响。

MySQL 5.6：Online DDL 元年（2013 年）

5.6 是一个里程碑版本，正式在内核层面引入了 Online DDL 框架。Oracle 终于意识到，不能让 DBA 们永远依赖第三方工具了——毕竟被社区用户天天吐槽也不是什么光彩的事。

核心改进一：INPLACE 算法登场

5.6 开始，很多操作不再需要复制全表数据，也不再阻塞写入：

-- 5.6 开始，大部分二级索引可以在线添加（不阻塞读写，FULLTEXT/SPATIAL 等特殊索引除外）
ALTER TABLE orders
  ADD INDEX idx_user_id (user_id),
  ALGORITHM=INPLACE,
  LOCK=NONE;

这里多了两个重要的子句：

• ALGORITHM=INPLACE
  
  ：告诉 MySQL 使用原地修改算法
• LOCK=NONE
  
  ：明确要求不锁表，允许并发 DML

如果 MySQL 发现这个操作不支持 INPLACE 或 LOCK=NONE，会直接报错而不是静默降级——这是个很重要的设计，避免你以为是 Online 结果实际锁表了。

核心改进二：锁级别细分

5.6 引入了 LOCK 选项，让你可以精确控制锁的程度：

⚠️ 老司机建议：如果你什么都不写，MySQL 默认走 DEFAULT——听起来挺智能，但问题是"自动选择"意味着你不知道实际会用什么锁。如果操作不支持 NONE，MySQL 会静默升级到 SHARED 甚至 EXCLUSIVE，而不是报错。所以我的习惯是：对于必须保证"零写入阻塞"的场景，显式指定 LOCK=NONE
。这样如果操作不支持，直接报错，总比执行到一半发现业务被锁了强。

5.6 的重要限制

虽然是重大进步，但很多核心操作还是只能 COPY：

-- 以下操作在 5.6 仍然需要复制全表（COPY 方式）
ALTER TABLE orders ADD COLUMN remark VARCHAR(500);      -- 加列
ALTER TABLE orders MODIFY COLUMN name VARCHAR(200);     -- 改列类型/长度
ALTER TABLE orders DROP COLUMN temp_flag;               -- 删列

加一个字段还是要复制全表——这在大表上依然是噩梦。即使是 INPLACE 加索引，也需要大量的 IO 资源来构建索引树，可能会拖慢线上数据库的响应。所以那时候我们还是老老实实用 pt-osc，心里踏实。

验证 DDL 执行方式的方法

如何知道你的 DDL 会用什么方式执行？

-- 方法 1：试探性执行
ALTER TABLE orders ADD INDEX idx_test (user_id),
  ALGORITHM=INSTANT, LOCK=NONE;
-- 如果报错 ERROR 1845，说明不支持 INSTANT（5.6/5.7 肯定不支持 INSTANT 关键字）

-- 方法 2：测试环境测试，并查询 performance_schema（需要开启，并开启相关 instruments/consumers） 
-- 观察 stage/innodb/alter% 相关的事件  # 5.6 引入，5.7.6 才基本可用

MySQL 5.7：稳步增强（2015 年）

5.7 在 5.6 的基础上继续扩展 Online 操作的范围，修复了一些边界情况。

改进一：真正的 RENAME INDEX

在 5.6 中，如果你想重命名索引，只能 DROP
 旧索引再 ADD
 新索引，这会触发索引重建，开销很大。
5.7 引入了真正的 RENAME INDEX
 语法，属于 INPLACE 操作且只修改元数据，瞬间完成。

-- 5.7+ 支持
ALTER TABLE orders RENAME INDEX idx_old TO idx_new;

改进二：VARCHAR 扩展（有条件）

这是 5.7 一个重要但容易误解的改进：

-- 5.7 支持：在同一“长度区间”内扩展 VARCHAR 是 INPLACE 的
ALTER TABLE users MODIFY COLUMN name VARCHAR(100); -- 原来是 VARCHAR(50)

原理：VARCHAR 长度字节数分为“1 字节”（长度 < 256）和“2 字节”（长度 ≥ 256）两种。

• 0-255 字节内扩展
  ：INPLACE（不锁表，不拷数据）
• 256-65535 字节内扩展
  ：INPLACE
• 跨区间扩展（如 250 -> 300）
  ：COPY（需要复制全表！）

⚠️ 注意：这里说的是字节。UTF8MB4 编码下，一个字符最多 4 字节。所以 VARCHAR(60)
 (240字节) 扩容到 VARCHAR(70)
 (280字节) 就会触发 COPY。

改进三：虚拟生成列与“函数索引”

5.7 引入了虚拟生成列（Virtual Generated Column）。虽然 5.7 没有原生的“函数索引”（Functional Index 是 8.0.13 才引入的），但我们可以通过“虚拟列 + 索引”的方式变相实现：

-- 1. 添加虚拟列（INPLACE，瞬间完成，不占空间）
ALTER TABLE users ADD COLUMN age INT
  GENERATED ALWAYS AS (JSON_EXTRACT(profile, '$.age')) VIRTUAL;

-- 2. 对虚拟列建索引（INPLACE，需要构建索引时间）
ALTER TABLE users ADD INDEX idx_age (age);

5.6 vs 5.7 对比

核心痛点：加列（Add Column）依然需要 COPY。

MySQL 8.0：INSTANT DDL 时代（2018 年起）

8.0 是 Online DDL 的又一次飞跃。这次 Oracle 终于啃下了"快速加列"这块硬骨头——虽然过程有点曲折，中间还翻了一次大车。

INSTANT 算法：只改元数据，不动数据文件

INSTANT 的核心思想是：既然新加的列都用默认值，那直接在元数据里记一笔“这里有个新列，默认值是 X”就行了，何必去改每一行数据呢？

-- 8.0.12+：加列可以瞬间完成
ALTER TABLE orders ADD COLUMN remark VARCHAR(500) DEFAULT '',
  ALGORITHM=INSTANT;

执行这条语句时：

1. 在数据字典中记录新列定义。
2. 现有的数据行保持不变。
3. 读取老数据时，MySQL 自动填充默认值。

这就是为什么 INSTANT 能做到毫秒级完成，无论表有多大。

💡 小知识：从 8.0.12 开始，INSTANT 成为 DDL 的默认最高优先算法。MySQL 会按 INSTANT → INPLACE → COPY 的顺序尝试，优先使用对业务影响最小的方式。如果你不显式指定 ALGORITHM
，MySQL 会自动帮你选最优的。

💡 冷知识：INSTANT ADD COLUMN 这个功能，最早是腾讯游戏 DBA 团队贡献给 MariaDB 的（2016 年，MariaDB 10.3）。MySQL 8.0.12 后来移植了这一特性。这是国人对开源数据库生态的重要贡献。

8.0 各版本的 INSTANT 能力演进

8.0.12（2018 年）：首次支持 INSTANT ADD COLUMN
限制很多：只能加在表的最后。

8.0.29（2022 年）：野心与翻车
8.0.29 试图支持任意位置加列和删除列的 INSTANT 操作。为此，Oracle 修改了 InnoDB 的行格式，引入了"行版本号"机制。

然而，翻车了。

因为新机制在特定场景下（如崩溃恢复、并发 DML）存在严重 Bug，会导致数据损坏。Oracle 被迫在官网下架了 8.0.29 版本——没错，直接下架，不是打补丁。这在 MySQL 历史上也是相当罕见的操作，可见问题有多严重。

参见我之前写的文章：MySQL 8.0.29 出现重大 bug，现已下架

8.0.32+：走向成熟
经过几个版本的修复，从 8.0.32 开始，任意位置加列的 INSTANT DDL 终于稳定下来。

8.0 对"快速加列"的支持演进

8.0 最大的能力突破就是对加列操作的 INSTANT 支持。下表对比了 8.0 前期和后期版本的差异：

📖 MySQL 5.7 的“重命名列”在满足条件时是 INPLACE 算法，但也仅元数据变更（和 INSTANT类似）；MySQL 8.0.28 起把这类操作在更多场景下提升为可用 ALGORITHM=INSTANT，但并非所有场景都等价（例如涉及引用外键时可能仍需 INPLACE）

INSTANT 的隐藏成本与限制

1. 行版本次数限制
   ：一张表最多支持 64 次 INSTANT DDL 操作（8.0 系列）。超过后需要重建表（OPTIMIZE TABLE
   ）。好消息是 MySQL 9.1.0 起，这个上限提升到了 255 次，对于频繁变更的表更加友好了。

-- 查看表当前的 INSTANT 操作次数（8.0.29+）
SELECT NAME, TOTAL_ROW_VERSIONS
FROM information_schema.innodb_tables
WHERE NAME = 'your_db/your_table';

-- TOTAL_ROW_VERSIONS 达到 64（或 255）时，下一次 INSTANT DDL 会失败
-- 此时需要执行 OPTIMIZE TABLE 重建表，计数器归零

1. 空间碎片
   ：INSTANT DROP COLUMN 只是标记删除，物理空间不会释放。
2. 性能开销
   ：读取带有多个版本的行数据时，会有微小的 CPU 解析开销。
3. 自增列不支持 Online
   ：新增的列如果带 AUTO_INCREMENT
    属性，会锁表（ALGORITHM=INPLACE, LOCK=SHARED
   ）。这是个容易踩的坑——你以为加列秒完成，结果因为是自增列，直接锁表了。

⚠️ 再次踩坑警告：自增列只能有一个，且必须是主键或唯一索引的一部分。新增自增列本质上是在修改表的核心结构，所以无法 INSTANT。如果业务需要新增自增列，务必走第三方工具或在停机窗口执行。

🔥 大厂实践：对于频繁变更的表，定期（比如每月）执行一次 OPTIMIZE TABLE
，既清理碎片也重置 INSTANT 计数器。甚至可以把这个指标加到监控系统里，提前预警。

绕不开的坑：元数据锁（MDL）

讲了这么多 Online DDL 的进化，但有一个坑是所有版本都绑不开的——元数据锁（Metadata Lock, MDL）。

什么是元数据锁？

MySQL 5.5.3 引入了 MDL 机制，用于保护表结构在 DDL 和 DML 之间的一致性。简单说：

• DML（SELECT/INSERT/UPDATE/DELETE）
   会持有 MDL 读锁
• DDL（ALTER TABLE）
   需要获取 MDL 写锁
• 读锁之间不互斥，但写锁与任何锁互斥

这意味着：DDL 要执行，必须等所有正在进行的 DML 事务结束。

经典翻车场景

场景一：一个长查询拖垮整个表，甚至业务雪崩。

时间线：
T1: 事务 A 开始，执行 SELECT ... FROM t（持有 t 表的 MDL 读锁，事务未结束）
T2: DBA 执行 ALTER TABLE t（申请 t 表的 MDL 写锁，因 T1 未结束而等待）
T3: 新的 SELECT/INSERT 访问 t（可能排在该 MDL 写锁请求之后，出现“读写也被卡住”的队列效应）
T4: 如果事务 A  业务语句还触达了多张表（JOIN/子查询/触发器等），则这些表的 DDL 也可能被一并阻塞；同时多表 JOIN 的业务请求会因其中一张表被卡而整体排队。
T5: 更多请求堆积…连接数暴涨…业务雪崩

一个未提交的事务（哪怕是个简单的 SELECT，哪怕 SQL 已经跑完），就能让后续所有请求排队等待。这就是为什么有时候一个"秒级完成"的 Online DDL，实际上卡了几分钟甚至更久，最严重情况就是业务雪崩了。

场景二：从库 DDL 阻塞备份

这个坑更隐蔽。假设你在主库执行了一个 DDL：

1. DDL 通过 Binlog 复制到从库
2. 从库的 SQL 线程尝试执行 DDL，需要获取 MDL 写锁
3. 恰好此时从库正在执行备份（如 mysqldump --single-transaction
   ），持有 MDL 读锁
4. DDL 被阻塞，从库复制延迟暴涨

⚠️ 踩坑警告：如果你用从库做备份，DDL 执行前一定要确认备份窗口。或者使用 pt-online-schema-change
 / gh-ost
 这类工具，它们在从库只会复制 DML，不会有 DDL 锁争抢问题。

如何诊断 MDL 等待？

由于篇幅关系，只介绍了一种最通用、最简单的方法 show processlist
，他能确认发生了 MDL 等待的事件，但要排查谁阻塞了他，请关注陈臣老师的公众号。例如：MySQL 5.7 中如何定位 DDL 被阻塞的问题

mysql> show processlist;
+----+-------+-----------+---------+---------+------+---------------------------------+------------------------------------+
| Id |User|Host| db      | Command |Time|State| Info                               |
+----+-------+-----------+---------+---------+------+---------------------------------+------------------------------------+
|55|admin| localhost | mdl_lab | Sleep   |94||NULL|
|56|admin| localhost | mdl_lab | Query   |67| Waiting fortable metadata lock|ALTERTABLE t_mdl ADDCOLUMNc INT |
|57|admin| localhost |NULL| Query   |0| starting                        |show processlist                   |
+----+-------+-----------+---------+---------+------+---------------------------------+------------------------------------+
3rowsinset (0.01 sec)

DBA 执行原生 DDL 的最佳实践

既然原生 Online DDL 有这么多坑，那在生产环境执行时，应该怎么做才稳妥？这是我多年踩坑总结的检查清单。

执行前：充分评估

1. 确认 DDL 类型

先查官方文档，确认这个操作：

• 支持什么算法？（INSTANT INPLACE COPY）
• 需要什么锁？（NONE SHARED EXCLUSIVE）
• 是否重建表？（Rebuilds Table: Yes No）

2. 检查磁盘空间

如果是 COPY 算法或需要 Rebuild Table，需要额外空间：

-- 估算表大小
SELECT
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_db' AND table_name = 'your_table';

经验法则：预留 2-3 倍表大小 的空间（原表 + 临时表 + 索引重建）。

3. 评估执行时间

在测试环境用相同数据量跑一遍，记录耗时。生产环境通常会更慢（因为有并发 DML）。

4. 选择执行窗口

• 优先选择业务低峰期
• 避开备份窗口（尤其是从库备份）
• 如果用读写分离，关注从库复制延迟

执行时：显式指定参数

核心原则：不要让 MySQL 自己选，显式写清楚你的期望。

-- **会话级别**设置元数据锁等待超时（关键！）
SET lock_wait_timeout =2;  -- 等待 MDL 锁最多 2 秒，超时直接失败。

-- 显式指定算法和锁级别
ALTERTABLE orders
ADDCOLUMN remark VARCHAR(500) DEFAULT'',
    ALGORITHM = INSTANT,   -- 明确要求 INSTANT
LOCK=NONE;           -- 明确要求不锁表

⚠️ 为什么设 lock_wait_timeout
：默认值是 31536000 秒（一年！）。如果前面有慢查询或未提交事务，DDL 会一直等待，同时阻塞后续所有请求。设置短超时可以快速失败，避免雪崩。失败后排查原因，处理完再重试。

参数说明：

• ALGORITHM=INSTANT
  
  ：如果不支持，会报错 ERROR 1845
  ，而不是静默降级到 COPY
• LOCK=NONE
  
  ：如果不支持，会报错，而不是静默升级到 SHARED

完整的执行脚本模板

-- Step 1: 检查当前是否有长事务或锁等待
SELECT*FROM information_schema.innodb_trx ORDERBY trx_started LIMIT10;
SELECT*FROM sys.schema_table_lock_waits;

-- Step 2: 检查磁盘空间（如果是 COPY/Rebuild）
-- 确保 datadir 所在分区有足够空间

-- Step 3: 设置会话参数
SET lock_wait_timeout =2;
SET innodb_lock_wait_timeout =2;

-- Step 4: 执行 DDL（显式指定算法和锁）
ALTERTABLE your_table
ADDCOLUMN new_col VARCHAR(100) DEFAULT'',
    ALGORITHM = INSTANT,
LOCK=NONE;

-- Step 5: 验证结果
SHOWCREATETABLE your_table;
DESC your_table;

执行后：验证与监控

1. 确认表结构正确
   ：SHOW CREATE TABLE
   
2. 检查复制延迟
   ：SHOW SLAVE STATUS\G
    看 Seconds_Behind_Master
   
3. 观察业务指标
   ：QPS、响应时间、错误率
4. 如果是 Rebuild Table
   ：后续观察磁盘空间和表碎片情况，一般能回收 DATA_FREE
   、让索引更紧凑，碎片更少。

什么时候该放弃原生 Online DDL？

以下场景，建议上 gh-ost
 或 pt-online-schema-change
：

第三方工具的优势：

• 可暂停、可限速
  ：不会把主库 IO 打满
• 可回滚
  ：切换前随时可以取消
• 对从库友好
  ：只复制 DML，不会产生 DDL 锁等待

总结

回顾这十年的进化，从凌晨 3 点蹲公司改表，到现在 INSTANT 秒级完成，MySQL 的 DDL 确实进步了太多。

核心要点回顾：

1. 三种算法
   ：COPY（不 Online）→ INPLACE（可能 Online）→ INSTANT（无感 Online）
2. 三个维度
   ：评估 DDL 要看 Algorithm + Lock + Rebuilds Table
3. MDL 是隐形杀手
   ：再快的 DDL 也可能被一个未提交事务卡住
4. 显式优于隐式
   ：永远指定 ALGORITHM
    和 LOCK
   ，设置 lock_wait_timeout
   
5. 大表用工具
   ：超大表或核心业务，gh-ost
    / pt-osc
    更稳妥

思考题：如果你需要把一个 INT
 列改成 BIGINT
（不支持 INSTANT），在生产环境有什么办法尽量减少对业务的影响？欢迎在评论区分享你的骚操作。

我是芬达，一个在数据库领域摸爬滚打了十多年的老兵。如果这篇文章对你有帮助，欢迎点赞、在看、转发三连。我们下期见。