大表怎么加字段、加索引？Online DDL 是怎么回事？

详细解析#

DDL 为什么危险：MDL 排队#

所有访问表的语句都要先拿这张表的 MDL：读写拿共享 MDL，互相兼容；DDL 要排他 MDL。事务里拿到的 MDL 要到事务结束才释放，不是语句结束。

会话 1：BEGIN; SELECT * FROM t LIMIT 1;    -- 拿到共享 MDL，事务一直没提交
会话 2：ALTER TABLE t ADD COLUMN c INT;     -- 要排他 MDL，等待会话 1
会话 3：SELECT * FROM t WHERE id = 1;       -- 排在会话 2 后面，也被阻塞
会话 4、5、6……                               -- 全部堆积，连接池很快被占满

排他锁请求的优先级高于共享锁，所以会话 3 即使和会话 1 并不冲突，也要排在等待中的 DDL 后面。SHOW PROCESSLIST 里会看到大量 Waiting for table metadata lock。lock_wait_timeout 控制等 MDL 的超时时间，默认是 31536000 秒（一年），等于无限等待。它和行锁的超时 innodb_lock_wait_timeout（默认 50 秒）是两回事。表级、行级锁的其他内容见 InnoDB 有哪些锁。

Online DDL 的 ALGORITHM 和 LOCK#

LOCK 可以是 NONE（允许读写）、SHARED（只允许读）、EXCLUSIVE（读写都阻塞），不写就是 DEFAULT，尽量给出最大的并发。显式写上 ALGORITHM 和 LOCK 的好处是：操作不支持指定的方式时直接报错，不会悄悄退化成锁表的方式。

INPLACE 不等于全程无锁：初始化时拿可升级的共享 MDL；准备执行时可能短暂升级成排他 MDL；执行期间允许读写；最后提交新表定义时再升级成排他 MDL。所以上面 MDL 排队的问题同样存在。执行期间别人的修改记在临时的 online log 里，最后回放；修改太多超过 innodb_online_alter_log_max_size（默认 128MB）时 DDL 失败。

MySQL 8.0 常见操作的支持情况#

INSTANT 加列、删列有限制：每张表最多累积 64 个行版本，用完后只能用 INPLACE 或 COPY，重建表（如 OPTIMIZE TABLE）后清零；不能和不支持 INSTANT 的操作写在同一条 ALTER 里；压缩表、带全文索引的表不支持。VARCHAR 的长度按字节算：utf8mb4 下 VARCHAR(63) 最多 252 字节，改成 VARCHAR(64) 就跨过了 255 字节，只能 COPY。

gh-ost 和 pt-online-schema-change#

两个工具的思路一样：不在原表上直接改，而是做一张新结构的影子表，追平数据后再换过来。

1. 按新结构建影子表
2. 按主键（或唯一键）分批把原表的存量数据拷进影子表
3. 同步拷贝期间原表的增量修改
4. 追平后短暂加锁，用原子的 RENAME 交换两张表，旧表留作备份或删除

两者都能按负载和从库延迟限流，默认值不同：pt-osc 默认在 Threads_running 超过 25 或从库延迟超过 1 秒时暂停拷贝（触发器照常执行）；gh-ost 默认只按从库延迟（1.5 秒）限流，Threads_running 这类阈值要用 --max-load 自己配置。gh-ost 还能把切换推迟到人工确认，可以先在从库上完整演练一遍。

上线操作规范#

1. 先确认操作的执行方式：查官方文档或在测试环境执行，显式写上 ALGORITHM 和 LOCK
2. 选业务低峰执行，确认磁盘空间足够（重建表需要再放下一份表）
3. 执行前查有没有长事务、长查询占着这张表
4. 在执行 DDL 的会话里把 lock_wait_timeout 设成几秒，拿不到 MDL 就快速失败、稍后重试，而不是一直排队堵住业务
5. 关注从库：DDL 要在主库执行完才会在从库开始，大表的 INPLACE 操作会让从库延迟和 DDL 的耗时相当
6. 灰度：分库分表的场景先改一个分片，观察没问题再批量执行

代码示例#

-- 1. 查运行超过 60 秒的事务
SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 60 SECOND;

-- 2. 拿不到元数据锁就在 5 秒后报错，不要无限期排队
SET SESSION lock_wait_timeout = 5;

-- 3. 显式指定方式，不支持时直接报错
ALTER TABLE orders ADD COLUMN remark VARCHAR(255) NULL, ALGORITHM = INSTANT;
ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time), ALGORITHM = INPLACE, LOCK = NONE;

-- 4. DDL 卡住时，看谁在等、谁挡住了它
SELECT waiting_pid, waiting_query, blocking_pid, blocking_lock_type
FROM sys.schema_table_lock_waits;

面试官可能追问#

易错点#

• INSTANT 和 INPLACE 都不是完全无锁，执行中都要短暂拿排他 MDL，长事务同样会让它们排队、堵住业务
• lock_wait_timeout 管元数据锁，innodb_lock_wait_timeout 管行锁，前者默认一年
• VARCHAR 扩展能否 INPLACE 按字节算，utf8mb4 下每个字符按 4 字节计
• gh-ost、pt-osc 最后切换表时也要拿 MDL，同样要避开长事务