MySQL Online DDL大表变更风险控制：三种算法与MDL锁处理

Online DDL解决了什么问题

MySQL性能调优场景中，大表结构变更（加字段、加索引、修改列类型）一直是DBA的噩梦。传统ALTER TABLE在5.6之前会锁全表，拷贝整张表数据，一张千万行的表变更耗时可能以小时计，期间所有DML被阻塞。Online DDL从5.6开始引入，允许在变更期间并发执行DML操作，大幅减少了业务影响窗口。但”Online”不等于”零影响”——不同的ALTER操作、不同的算法，对线上业务的影响差异巨大。理解Online DDL的三种算法和各自的限制，是安全执行大表变更的前提。

Online DDL的三种算法对比

查看ALTER操作将使用哪种算法：

-- 先用EXPLAIN确认算法（不会真正执行）
ALTER TABLE orders ALGORITHM=INSTANT, ADD COLUMN is_archived TINYINT DEFAULT 0;

-- 如果MySQL选择INSTANT，瞬间完成
-- 如果不支持INSTANT，会报错：ALGORITHM=INSTANT is not supported

INSTANT算法：最快但限制最多

MySQL 8.0.12引入INSTANT算法，加列操作只修改表的metadata，不拷贝任何数据行：

-- INSTANT加列：毫秒级完成
ALTER TABLE orders ADD COLUMN region VARCHAR(32) DEFAULT 'unknown', 
  ALGORITHM=INSTANT;

-- 支持INSTANT的常见操作（8.0.12+）：
-- ADD COLUMN（在表末尾加列）
-- ADD/DROP VIRTUAL COLUMN
-- 修改列默认值
-- 修改列名（RENAME COLUMN）

-- 不支持INSTANT的场景：
-- 在指定位置加列（AFTER/FIRST）
-- 删除列
-- 修改列类型
-- 添加自增列
-- 表已有65,535字节的行长度限制问题

注意：8.0.29之前，一张表只能执行一次INSTANT ADD COLUMN。8.0.29之后取消了这个限制，但列数仍有上限。

INPLACE算法：并发DML的关键

INPLACE是大部分Online DDL操作使用的算法。它在原表的InnoDB数据文件上直接修改，避免全表拷贝。加索引是最常见的INPLACE操作：

-- INPLACE加索引：允许并发DML
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

-- LOCK=NONE：允许并发读写（最宽松）
-- LOCK=SHARED：允许并发读，阻塞写
-- LOCK=EXCLUSIVE：阻塞读写（等同于OFFLINE）

-- 查看当前正在执行的DDL进度
SELECT * FROM performance_schema.events_stages_current 
WHERE EVENT_NAME LIKE 'stage/innodb/alter%'\G

INPLACE的关键限制——即使标注为INPLACE，某些操作仍会短暂锁表：

-- 以下操作标注为INPLACE，但在开始和结束阶段需要MDL锁
-- 开始阶段：获取EXCLUSIVE MDL锁（极短，通常<1秒）
-- 结束阶段：再次获取EXCLUSIVE MDL锁（极短）
-- 中间阶段：允许并发DML

-- 如果有长事务持有MDL锁，DDL会被阻塞
-- 此时DDL阻塞期间，后续所有DML也会被MDL锁排队阻塞！
-- 这是Online DDL最常见的"意外锁表"场景

MDL锁阻塞的诊断与处理

Online DDL执行过程中，如果存在未提交的长事务，DDL在获取MDL锁时会被阻塞，同时阻塞后续所有对该表的DML操作，造成”雪崩”。

-- 查看MDL锁等待
SHOW PROCESSLIST;
-- 典型场景：DDL处于Waiting for table metadata lock状态
-- 后续DML也处于Waiting for table metadata lock状态

-- 找到阻塞DDL的长事务
SELECT t.trx_id, t.trx_state, t.trx_started, 
       t.trx_query, t.trx_tables_locked,
       TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS running_seconds
FROM information_schema.innodb_trx t
ORDER BY t.trx_started ASC;

-- 确认长事务的会话信息
SELECT p.ID, p.USER, p.HOST, p.DB, p.COMMAND, p.TIME, p.STATE, p.INFO
FROM information_schema.PROCESSLIST p
WHERE p.ID IN (SELECT trx_mysql_thread_id FROM information_schema.innodb_trx)
ORDER BY p.TIME DESC;

处理策略：

-- 策略1：KILL长事务（有风险，确认业务可接受）
KILL CONNECTION <long_trx_thread_id>;

-- 策略2：KILL DDL语句本身（避免雪崩）
-- DDL被KILL后，排队的DML可以继续执行
KILL CONNECTION <ddl_thread_id>;

-- 策略3：设置锁等待超时（执行DDL前配置）
SET SESSION lock_wait_timeout = 5;  -- 5秒内获取不到MDL锁就放弃
ALTER TABLE orders ADD INDEX idx_status (status), 
  ALGORITHM=INPLACE, LOCK=NONE;
-- 如果5秒内获取不到MDL锁，报错退出，不影响业务

大表加索引的风险控制实操

千万行级别的表加索引，INPLACE模式下仍然需要扫描全表构建索引，耗时可能数分钟。期间会占用额外IO和CPU。实操建议：

-- 步骤1：业务低峰期执行
-- 步骤2：设置session级参数，降低IO影响
SET SESSION innodb_sort_buffer_size = 67108864;  -- 64MB排序缓冲
SET SESSION innodb_online_alter_log_max_size = 268435456;  -- 256MB DML日志

-- 步骤3：设置锁超时保护
SET SESSION lock_wait_timeout = 10;

-- 步骤4：执行DDL
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 步骤5：监控进度
-- 在另一个session中查询进度
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,
       ROUND(WORK_COMPLETED/WORK_ESTIMATED*100, 2) AS pct
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/innodb/alter%';

如果INPLACE不支持（如修改列类型），退回COPY算法时需要额外注意：

-- COPY算法等同于全表重建，期间只允许并发读
-- 检查是否有COPY的替代方案

-- 修改列类型（必须COPY）：用gh-ost或pt-osc工具替代
-- gh-ost示例
gh-ost \
  --host=127.0.0.1 \
  --database=production \
  --table=orders \
  --alter="MODIFY COLUMN amount DECIMAL(12,2)" \
  --allow-on-master \
  --initially-drop-ghost-table \
  --ok-to-drop-table \
  --execute

变更前检查清单

• 用ALGORITHM=INSTANT测试操作是否支持INSTANT——支持则秒级完成，无需担心
• 不支持INSTANT时，用ALGORITHM=INPLACE, LOCK=NONE测试——支持则可在线执行
• 不支持INPLACE时，必须用第三方工具（gh-ost/pt-osc）——不要用COPY算法对线上大表操作
• 执行前检查长事务：SELECT COUNT(*) FROM information_schema.innodb_trx WHERE trx_started < NOW() - INTERVAL 60 SECOND;
• 设置lock_wait_timeout保护，避免DDL无限等待MDL锁
• 大表加索引在低峰期执行，并监控performance_schema.events_stages_current进度
• 确认磁盘空间充足——INPLACE加索引临时空间约为索引大小的1-2倍
• 多列批量变更拆分为单条ALTER——MySQL对同一条ALTER中的多列变更可能退回COPY算法