MySQL Online DDL：千万行表加字段为什么不锁表了
2026/4/30大约 9 分钟
MySQL Online DDL：千万行表加字段为什么不锁表了
适读人群：Java后端开发、DBA、对MySQL DDL机制感兴趣的工程师 | 阅读时长：约22分钟
开篇故事
2018年，我第一次在生产环境对一个千万行的订单表执行ALTER TABLE ADD COLUMN，整整锁表47分钟。
在这47分钟内，所有写操作全部阻塞，用户无法下单。那次事故让我被骂了整整一周。
后来我才知道，MySQL 5.6就已经有了Online DDL，MySQL 5.7/8.0进一步完善。对大表的DDL操作，只要用对姿势，可以做到基本不影响业务。
但「基本不影响」不等于「完全不影响」，今天把Online DDL的原理和正确使用方式讲清楚，特别是那些仍然会锁表的情况。
一、DDL操作的三种执行方式
-- 显式指定DDL算法和锁级别
ALTER TABLE orders
ADD COLUMN remark VARCHAR(200),
ALGORITHM=INPLACE, -- 算法：COPY | INPLACE | INSTANT
LOCK=NONE; -- 锁级别：NONE | SHARED | EXCLUSIVE | DEFAULTCOPY算法（老方式，MySQL 5.5以前的唯一方式）：
1. 创建一个临时表（新结构）
2. 将数据从原表逐行复制到临时表
3. 等待所有事务结束（加表级排他锁）
4. 用临时表替换原表
锁定时间：整个复制过程期间表被锁（读写均阻塞）
对千万行表：可能锁表几十分钟INPLACE算法（MySQL 5.6+ Online DDL）：
1. 准备阶段：获取MDL写锁（毫秒级），创建新的索引/添加列
2. 执行阶段：释放MDL写锁，获取MDL读锁
在此期间允许读写操作
同时将期间的DML变更记录在row_log中
3. 提交阶段：获取MDL写锁（毫秒级），将row_log中的变更应用到新结构
完成后替换表定义
锁定时间：仅在准备和提交阶段持有MDL写锁（毫秒级）
执行过程中允许读写：真正的Online DDLINSTANT算法（MySQL 8.0.12+）：
仅修改表的元数据，不重建表数据
仅支持：ADD COLUMN（列加在末尾）
DROP COLUMN（8.0.29+）
RENAME COLUMN等
锁定时间：极短（仅元数据修改，毫秒级）
限制：不是所有DDL操作都支持INSTANT二、底层原理：INPLACE Online DDL的row_log机制
Online DDL期间的并发处理：
主线程（DDL执行）:
① 扫描原表所有行，构建新的表结构/索引
② 将每行数据写入临时文件（磁盘）
业务线程（正常DML操作）:
INSERT/UPDATE/DELETE 正常执行
但每次DML操作同时记录到 row_log（内存中的变更日志）
提交阶段：
③ 将row_log中的所有变更，重新应用到新的表结构上
④ 交换表定义（瞬间完成）
row_log的大小限制：
innodb_online_alter_log_max_size（默认128MB）
如果DDL执行期间DML产生的变更超过128MB → DDL失败！
→ 重新执行DDL，或者临时增大该参数MDL锁（元数据锁）的持有时机：
开始DDL:
瞬间持有MDL写锁 → 检查没有冲突的事务
执行DDL（耗时的重建过程）:
只持有MDL读锁 → 允许DML操作
提交DDL:
瞬间持有MDL写锁 → 应用row_log，完成替换
关键：MDL读锁和DML兼容（都能运行），只有MDL写锁会阻塞所有操作三、完整解决方案与代码
3.1 各种DDL操作的Online支持情况
-- 支持INPLACE/INSTANT的操作（不锁表）：
-- 1. 添加列（INSTANT，最快）
ALTER TABLE orders
ADD COLUMN remark VARCHAR(200) DEFAULT NULL,
ALGORITHM=INSTANT;
-- 2. 修改列的默认值（INSTANT）
ALTER TABLE orders
MODIFY COLUMN status TINYINT NOT NULL DEFAULT 0,
ALGORITHM=INSTANT;
-- 3. 添加索引（INPLACE，允许读写）
ALTER TABLE orders
ADD INDEX idx_user_id (user_id),
ALGORITHM=INPLACE, LOCK=NONE;
-- 4. 删除索引（INPLACE）
ALTER TABLE orders
DROP INDEX idx_old_index,
ALGORITHM=INPLACE, LOCK=NONE;
-- 5. 修改列名（INPLACE，8.0+）
ALTER TABLE orders
RENAME COLUMN old_name TO new_name,
ALGORITHM=INPLACE, LOCK=NONE;
-- 6. 批量DDL（尽量合并到一条语句）
ALTER TABLE orders
ADD COLUMN remark VARCHAR(200),
ADD COLUMN extra_info JSON,
ADD INDEX idx_created (created_at),
ALGORITHM=INPLACE, LOCK=NONE;
-- 一条ALTER合并多个操作，只需一次表重建，比分开执行更高效
-- 需要LOCK=SHARED或EXCLUSIVE的操作（会阻塞写）：
-- 修改列的数据类型（需要重建数据）
ALTER TABLE orders
MODIFY COLUMN amount DECIMAL(15,4), -- 精度变化
ALGORITHM=COPY; -- 无法INPLACE，必须COPY
-- 锁表！对大表要用pt-online-schema-change
-- 修改字符集
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4;
-- 需要重建所有列，锁表时间长3.2 用pt-online-schema-change处理不支持Online DDL的操作
对于必须重建表的DDL（修改列类型、修改字符集等），使用pt-online-schema-change（pt-osc）：
# 安装
yum install percona-toolkit
# pt-osc工作原理：
# 1. 创建影子表（新结构）
# 2. 创建触发器（INSERT/UPDATE/DELETE → 同步到影子表）
# 3. 批量从原表复制数据到影子表
# 4. 数据同步完成后，原子性地RENAME（瞬间完成）
# 5. 删除触发器和原表
# 执行修改列类型的DDL（不锁表）
pt-online-schema-change \
--host=localhost \
--port=3306 \
--user=root \
--password=password \
--database=mydb \
--table=orders \
--alter="MODIFY COLUMN amount DECIMAL(15,4)" \
--execute \
--max-load="Threads_running=100" \ # 服务器负载超过100个running线程时暂停
--critical-load="Threads_running=200" \ # 超过200个running线程时终止
--chunk-size=1000 \ # 每批复制1000行
--chunk-time=0.5 \ # 每批操作控制在0.5秒内（控制复制速度，减少对业务影响）
--print # 打印执行过程3.3 gh-ost：更安全的大表DDL工具
# gh-ost（GitHub出品）不使用触发器，而是通过解析binlog同步变更
# 更安全，对业务性能影响更小
# 安装
wget https://github.com/github/gh-ost/releases/download/v1.1.5/gh-ost-binary-linux-amd64-20221101160634.tar.gz
# 执行大表DDL
gh-ost \
--host=localhost \
--port=3306 \
--user=root \
--password=password \
--database=mydb \
--table=orders \
--alter="ADD COLUMN remark VARCHAR(200)" \
--allow-on-master \ # 允许直接在主库执行（通过binlog同步）
--max-load=Threads_running=50 \
--chunk-size=1000 \
--throttle-control-replicas=slave1:3306,slave2:3306 \ # 监控从库延迟
--max-lag-millis=1500 \ # 从库延迟超过1500ms时暂停
--execute3.4 DDL操作前的检查清单（Java代码）
/**
* 大表DDL执行前的安全检查
*/
@Service
public class DDLSafetyChecker {
@Autowired
private DataSource dataSource;
/**
* 在执行大表DDL前进行安全检查
*/
public DDLCheckResult checkBeforeDDL(String database, String tableName) throws SQLException {
DDLCheckResult result = new DDLCheckResult();
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement()) {
// 1. 检查表大小
ResultSet sizeRs = stmt.executeQuery(String.format(
"SELECT TABLE_ROWS, DATA_LENGTH/1024/1024 AS data_mb, " +
"INDEX_LENGTH/1024/1024 AS index_mb " +
"FROM information_schema.TABLES " +
"WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'",
database, tableName
));
if (sizeRs.next()) {
long rows = sizeRs.getLong("TABLE_ROWS");
double dataMb = sizeRs.getDouble("data_mb");
result.setTableRows(rows);
result.setTableSizeMb(dataMb);
if (rows > 5_000_000) {
result.addWarning(String.format(
"表有%d行（%.1f MB），建议使用pt-osc或gh-ost执行DDL", rows, dataMb));
}
}
// 2. 检查当前活跃事务
ResultSet txRs = stmt.executeQuery(
"SELECT COUNT(*) AS cnt FROM information_schema.INNODB_TRX " +
"WHERE trx_state = 'RUNNING'"
);
if (txRs.next()) {
int activeTxCount = txRs.getInt("cnt");
if (activeTxCount > 10) {
result.addWarning(String.format(
"当前有%d个活跃事务，DDL可能需要等待这些事务完成", activeTxCount));
}
}
// 3. 检查当前负载
ResultSet loadRs = stmt.executeQuery(
"SHOW GLOBAL STATUS LIKE 'Threads_running'"
);
if (loadRs.next()) {
int threadsRunning = Integer.parseInt(loadRs.getString("Value"));
if (threadsRunning > 30) {
result.addWarning(String.format(
"当前有%d个运行中的线程，数据库负载较高，建议等待低峰期", threadsRunning));
}
}
// 4. 检查主从延迟
ResultSet slaveRs = stmt.executeQuery("SHOW SLAVE STATUS");
if (slaveRs.next()) {
int lag = slaveRs.getInt("Seconds_Behind_Master");
if (lag > 5) {
result.addWarning(String.format(
"从库延迟%d秒，DDL会进一步增大延迟，建议待延迟消除后再执行", lag));
}
}
}
return result;
}
}四、踩坑实录
坑1：MDL锁等待导致后续所有查询被阻塞
场景：
事务T1: BEGIN; SELECT * FROM orders; (长事务，持有MDL读锁)
执行DDL: ALTER TABLE orders ADD COLUMN remark VARCHAR(200);
→ DDL等待获取MDL写锁
→ 因为T1持有MDL读锁，DDL被阻塞
此时：所有后续的SELECT也在等MDL读锁！
因为DDL在等写锁，MySQL的MDL锁协议让后续的读锁请求也排队等待
→ 所有查询被阻塞，系统雪崩！报错/现象：
SHOW PROCESSLIST:
Id | State | Info
101 | Waiting for table metadata lock | SELECT * FROM orders WHERE...
102 | Waiting for table metadata lock | INSERT INTO orders...
103 | Waiting for table metadata lock | ALTER TABLE orders ADD COLUMN...
...
(几十个线程都在等待MDL锁)解决方案：
-- 执行DDL前：先终止或等待长事务
-- 找出持有MDL锁的事务
SELECT p.id, p.user, p.host, p.db, p.command, p.time, p.state, p.info
FROM information_schema.processlist p
WHERE p.command != 'Sleep'
ORDER BY p.time DESC;
-- 或者设置DDL获取MDL锁的超时时间
SET lock_wait_timeout = 10; -- 10秒内获取不到MDL写锁，DDL失败（不影响业务）
ALTER TABLE orders ADD COLUMN remark VARCHAR(200);
-- 如果超时，重试即可，不影响正在运行的业务坑2：pt-osc的触发器与应用层触发器冲突
场景：orders表上已有应用层创建的触发器（记录变更日志）
pt-osc在执行时也要在orders表上创建触发器
→ 触发器冲突，pt-osc报错报错：
Cannot use pt-online-schema-change for table `orders`:
Table `orders` has triggers. Use --no-drop-triggers to continue.解决方案：
# 方案1：临时删除应用层触发器，执行完pt-osc后重建
# 方案2：使用gh-ost（不使用触发器，通过binlog同步）
# 方案3：添加 --no-check-alter 但需要手动处理触发器冲突坑3：在从库上执行的DDL，主从同步会触发锁表
场景：主库执行了Online DDL（不锁表），binlog记录了DDL语句
从库回放binlog时，以STATEMENT格式执行DDL
→ 从库上DDL是老方式执行的，可能锁表！解决方案：
# 从库配置：确保DDL也能Online执行
slave_skip_errors = OFF # 不跳过错误
# 从库的DDL执行方式与主库相同（MySQL 5.6+ binlog格式为ROW时，DDL按语句模式重放）
# 主要关注：从库的DDL执行期间，从库上的查询是否受影响
# 如果从库用于读写分离，DDL期间读请求可能也会被影响五、总结与延伸
Online DDL的实用总结：
MySQL 8.0.12+ 首选INSTANT：ADD COLUMN（加到末尾）、修改默认值等操作，先尝试ALGORITHM=INSTANT，几乎零影响。
INPLACE算法注意row_log：DDL执行期间有大量DML操作时，row_log可能超过innodb_online_alter_log_max_size（默认128MB），导致DDL失败。高峰期要谨慎。
MDL锁是最大的隐患：执行DDL前检查是否有长事务，设置lock_wait_timeout防止DDL无限等待并阻塞后续查询。
大表必须用工具：行数超过500万的表，强烈建议用pt-osc或gh-ost，而不是直接执行ALTER TABLE。gh-ost相比pt-osc对主库影响更小，推荐优先使用。
从库延迟：DDL操作（特别是重建表的DDL）会产生大量binlog，加剧主从延迟，执行前需确认从库延迟在可接受范围内。