SQL Lab

Appearance

大表加列默认值 INSTANT 秒级完成 ​

⭐⭐DDL5.7 & 8.0INSTANTADD COLUMNOnline DDL元数据变更锁表

500 万行加列：INSTANT 秒级 vs 传统锁表几小时 ​

产品要求给订单表加一个"订单来源"字段，标记订单来自 web、App 还是小程序。表有 500 万行数据，DBA 在 MySQL 5.7 上执行：

sql
ALTER TABLE t_order ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT 'web' COMMENT '订单来源';

这条 DDL 跑了 8 分钟，期间 MDL 锁导致业务查询排队堆积，监控告警一片飘红。加一列而已，为什么要重建整张表？

这就是 "大表加列锁表" 的经典痛点——MySQL 5.7 的 ADD COLUMN 虽然支持 INPLACE，但仍需逐行重建表数据，执行时间与表行数成正比。而 MySQL 8.0 引入的 ALGORITHM=INSTANT 将加列变成了纯元数据操作，无论表多大都是毫秒级完成。

真实场景

大表加列是生产环境最常见的 DDL 需求之一。业务迭代总要加字段：订单来源、扩展属性、标记位。在 5.7 时代，每次加列都是一次"小型手术"——低峰期执行、提前通知、提心吊胆。8.0 的 INSTANT 算法彻底解决了这个痛点。

问题分析 ​

bad.sql ​

sql
-- MySQL 5.7 传统方式加列（重建整张表）
-- 5.7 中 ADD COLUMN 默认走 INPLACE 但需要重建表数据（rebuild）
-- 过程: 创建新表结构 -> 逐行拷贝数据 -> 重命名替换 -> 释放旧表
-- 50 万行需完整拷贝，期间持有 MDL 排他锁，阻塞所有 DML
-- 生产 500 万行表锁表可达 10 分钟以上
ALTER TABLE t_order ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT 'web' COMMENT '订单来源';

DDL 执行过程 ​

MySQL 5.7 的 ADD COLUMN 虽然支持 ALGORITHM=INPLACE，但仍属于 rebuild 操作：

1. 获取 MDL 排他锁
2. 创建带新列的临时表结构
3. 逐行从原表拷贝数据到临时表（50 万行全量拷贝）
4. 拷贝期间降级 MDL 锁，允许并发 DML（INPLACE 模式）
5. 拷贝完成后再次获取 MDL 排他锁
6. 重命名临时表替换原表，释放旧表
7. 释放锁

为什么慢 ​

维度5.7 传统加列影响
数据拷贝全表逐行拷贝50 万行需完整重建，I/O 和 CPU 开销大
磁盘空间需要 2 倍表空间原表 + 临时表同时存在
执行时间与表行数成正比50 万行约 30-60 秒，500 万行约 5-10 分钟
MDL 锁开始和结束阶段排他高并发下可能导致查询排队堆积
主从延迟从库同样耗时主库 10 分钟，从库也 10 分钟，数据严重滞后

核心问题：

  1. 全表数据重建：InnoDB 需要为每一行重新组织记录格式，加入新列的默认值
  2. 逐行拷贝：50 万行数据逐行读取、转换、写入新表，I/O 开销巨大
  3. 时间与行数成正比：表越大，拷贝时间越长。500 万行生产表可达 10 分钟以上
  4. 磁盘空间翻倍：拷贝期间原表和临时表同时存在，需要 2 倍表空间
  5. 主从级联延迟：主库执行 10 分钟，从库回放同样需要 10 分钟，期间从库数据严重滞后

核心认知

5.7 加列慢的本质不是"加列"这个动作慢，而是"为每一行物理写入默认值"慢。新列的默认值被逐行写进每条记录，这本质上是一次全表 UPDATE。

优化方案 ​

good.sql ​

sql
-- MySQL 8.0 INSTANT 算法加列（秒级完成）
-- ALGORITHM=INSTANT 只修改数据字典中的元数据，不触碰表数据
-- 新列的默认值记录在元数据中，查询时动态返回，无需逐行填充
-- 无论表有多少行，执行时间都是毫秒级
-- 注意: INSTANT 是 8.0.12+ 的特性，5.7 不支持
ALTER TABLE t_order ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT 'web' COMMENT '订单来源', ALGORITHM=INSTANT;

原理 ​

ALGORITHM=INSTANT 的执行流程完全不同：

1. 短暂获取 MDL 排他锁（毫秒级）
2. 修改数据字典中的表结构元数据（记录新列定义和默认值）
3. 释放锁
完成！不触碰任何表数据

核心原理：

  1. 只改元数据：新列的定义（名称、类型、默认值）写入数据字典，表数据文件完全不动
  2. 默认值动态返回：查询时 InnoDB 发现行记录中没有新列，直接从元数据读取默认值返回
  3. 与表大小无关：无论 50 万行还是 5 亿行，执行时间都是毫秒级
  4. MDL 锁极短：仅在修改元数据的瞬间持有排他锁，业务完全无感知
传统方式 (5.7):
  行记录: [id][order_no][user_id][amount][status][created_at]
  加列后: 每行都要物理插入 [source] 字段 -> 全表重建

INSTANT (8.0):
  行记录: [id][order_no][user_id][amount][status][created_at]  <- 不变
  元数据: source VARCHAR(20) DEFAULT 'web'                      <- 只改这里
  查询时: 发现行中没有 source -> 从元数据取默认值 'web' 返回

对比 ​

bad.sql (5.7 传统)good.sql (8.0 INSTANT)
数据拷贝全表逐行重建不拷贝任何数据
磁盘空间2 倍表空间0 额外空间
50 万行耗时~30-60 秒~10 毫秒
500 万行耗时~5-10 分钟~10 毫秒
MDL 锁持有秒级~分钟级毫秒级
主从延迟从库同样耗时从库同样毫秒级
指标优化前 (bad)优化后 (good)
访问类型--
使用索引--
扫描行数--
附加信息--

🚀 50 万行加列从 30-60 秒降到 10 毫秒，提升 3000+ 倍，且与表大小无关

INSTANT 支持的操作（8.0） ​

操作是否支持 INSTANT
ADD COLUMN（末尾加列）✅ 支持
RENAME COLUMN✅ 支持
DROP COLUMN✅ 8.0.29+ 支持
修改列默认值✅ 支持
ADD INDEX❌ 不支持（需 INPLACE）
MODIFY COLUMN（改类型）❌ 不支持（需 INPLACE/COPY）

避坑指南 ​

注意事项

  1. 升级到 8.0：如果还在 5.7，大表加列是最大的 DDL 痛点之一，升级 8.0 是最彻底的解决方案。

  2. 末尾加列才支持 INSTANT：8.0.12 只支持在表末尾加列，中间插入列（AFTER/BEFORE）需 8.0.29+。

  3. 不要显式指定 COPY：ALGORITHM=COPY 会强制走最差路径，让 MySQL 自行选择最优算法。

  4. 验证算法：执行前可用 ALTER TABLE ... ALGORITHM=INSTANT 显式指定，不支持会直接报错而非静默退化。

  5. 8.0 默认即 INSTANT：8.0 中 ADD COLUMN 不指定 ALGORITHM 时默认尝试 INSTANT，无需显式声明。但显式声明更安全——不支持时会直接报错。

5.7 vs 8.0 差异 ​

特性5.78.0
ADD COLUMN 算法INPLACE（需 rebuild）INSTANT（纯元数据）
加列耗时（50 万行）30-60 秒~10 毫秒
加列耗时（500 万行）5-10 分钟~10 毫秒
磁盘空间需求2x 表空间0 额外
默认值处理逐行物理写入元数据记录，查询时动态返回

8.0 的 INSTANT 是 DDL 领域的革命

INSTANT 算法将加列从"全表重建"变为"纯元数据修改"，是 MySQL 8.0 最有价值的特性之一。如果你还在 5.7，大表加列的痛苦就是升级 8.0 最充分的理由。8.0.12+ 支持末尾加列 INSTANT，8.0.29+ 进一步支持任意位置加列和删列 INSTANT。

本地复现 ​

bash
# 默认在 MySQL 8.0 上运行
./scripts/run-case.sh 48-instant-add-column

# 在 MySQL 5.7 上运行（对比）
./scripts/run-case.sh 48-instant-add-column --ver 5.7

# 跳过造数据重跑
./scripts/run-case.sh 48-instant-add-column --no-seed

MIT Licensed

Copyright © 2026 SQL Lab