ALTER TABLE 添加列时是否会锁表？MySQL 8.0 / 5.7 的在线 DDL 与传统 DDL 区别？

考察要点

这道题面试官想看你是否深入理解 MySQL 的 DDL 执行机制、锁模型以及版本间的核心差异。考察点主要包括：

1. 元数据锁（Metadata Lock, MDL）：是否理解 DDL 操作为了保证表结构和数据的一致性，需要获取 MDL，以及它如何影响并发 DML。
2. 在线 DDL（Online DDL）：是否清楚 MySQL 为了减少 DDL 阻塞而引入的 INPLACE 和 INSTANT 算法，以及它们与传统 COPY 算法的区别。
3. 版本迭代意识：是否了解 MySQL 5.7 到 8.0 在 DDL 功能上的革命性进步，特别是 INSTANT ADD COLUMN 这一重要特性。

核心概念

ALTER TABLE 是否锁表，取决于 MySQL 版本、具体操作类型以及用户指定的算法。核心概念围绕 DDL 算法的演进。

1. 传统 DDL 算法：COPY

在早期版本或某些复杂操作中，MySQL 使用 COPY 算法。

• 过程：
  1. 创建一个带有新结构的新临时表。
  2. 将原表数据逐行拷贝到新表。在此期间，原表通常只能读，不能写（持有读锁）。
  3. 拷贝完成后，删除原表。
  4. 将新表重命名为原表名。此步骤需要获取独占的 MDL 锁，阻塞所有读写。
• 缺点：
  • 长时间锁表：在数据拷贝和最后的重命名阶段会长时间阻塞 DML。
  • 高资源消耗：需要双倍的磁盘空间，并产生大量 I/O。
  • 主从延迟：在主库执行时，会产生大量 binlog，可能导致从库严重延迟。

2. 在线 DDL 算法：INPLACE

从 MySQL 5.6 开始引入，是大部分 DDL 操作的默认算法，旨在减少锁表时间。

• 过程：
  • 避免了创建完整的数据副本，而是在原表上“就地”完成结构变更。
  • 对于某些操作（如添加索引），它允许在构建索引的同时进行 DML 操作。
  • 对于添加列（MySQL 5.7），虽然是 INPLACE，但本质上仍需重建表（rebuild）。它会创建一个新的 .ibd 文件，并将数据逐行整理写入，但这个过程比 COPY 算法更优，且允许并发 DML。
• 锁模型：
  1. 准备阶段：获取一个短暂的独占 MDL 锁，准备 DDL 执行。
  2. 执行阶段：降级为共享 MDL 锁，允许并发读写（DML）。DDL 操作（如重建表）在后台进行。
  3. 提交阶段：再次升级为独占 MDL 锁，提交元数据变更。这个阶段非常快，但如果此时有长事务，DDL 会等待。
• 示例：
  sql

  ⧉⛶

   1-- MySQL 5.7 中，添加列默认使用 INPLACE，但会重建表
   2ALTER TABLE users ADD COLUMN age INT, ALGORITHM=INPLACE, LOCK=NONE;
   3-- LOCK=NONE 允许并发读写，是 INPLACE 的典型用法

3. 即时 DDL 算法：INSTANT (MySQL 8.0+)

MySQL 8.0.12 引入的革命性特性，针对部分 DDL 操作实现了真正的“即时”完成。

• 过程：
  • 只修改元数据：不触碰表的实际数据文件，不重建表，不消耗 I/O。
  • 它只在存储引擎层修改数据字典中关于表结构的定义。
  • 操作几乎瞬间完成，与表大小无关。
• 支持的操作：
  • 在表的末尾添加列。
  • 修改列的默认值。
  • 修改 ENUM / SET 类型的定义。
  • 重命名列（MySQL 8.0.28+）。
• 示例：
  sql

  ⧉⛶

   1-- 在 MySQL 8.0+ 中，这是毫秒级操作，无论表多大
   2ALTER TABLE users ADD COLUMN country VARCHAR(50) DEFAULT 'N/A', ALGORITHM=INSTANT;

版本与操作对比

结论：ALTER TABLE 是否锁表，答案是“视情况而定”。在 MySQL 8.0+ 中，在表末尾添加列是几乎不锁表的即时操作。而在其他场景或老版本中，即使是“在线 DDL”，也存在短暂的锁阶段和潜在的性能影响。

常见低分回答

1. “ALTER TABLE 都会锁全表。”
   • 这是非常过时且错误的认知，完全忽略了 MySQL 5.6 以后 Online DDL 的发展。这会让面试官认为你只用过非常古老的 MySQL 版本，缺乏对现代数据库特性的了解。
2. “MySQL 8.0 添加列不锁表，5.7 会锁表。”
   • 这个回答过于笼统，不够精确。正确的说法是 MySQL 5.7 的 INPLACE 算法也允许并发 DML，只是它需要重建表，资源消耗大、耗时长，并在首尾有短暂锁。而 8.0 的 INSTANT 算法是真正的“不锁表”（指不长期阻塞 DML）且瞬间完成。未能区分 INPLACE 和 INSTANT 是一个失分点。
3. “用 ALGORITHM=INPLACE, LOCK=NONE 就好了。”
   • 虽然这是推荐的语法，但候选人需要解释 LOCK=NONE 的含义是“尽可能不加锁”，而不是“绝对不加锁”。在 DDL 的开始和结束阶段，为了保证元数据一致性，仍然需要短暂的独占锁。不理解这一点，说明对锁模型的认知有偏差。
4. 混淆 INPLACE 和 INSTANT 的工作原理。
   • 误以为 INPLACE 也是只改元数据，或者认为 INSTANT 只是 INPLACE 的一个别名。这是核心概念错误，INPLACE 通常涉及数据文件重写（重建），而 INSTANT 完全不涉及。

工程实践与优化

• 首选工具化 DDL：对于生产环境核心业务的大表（GB/TB 级别），即使 MySQL 8.0 提供了强大的 Online DDL，业界最佳实践仍然是使用 pt-online-schema-change 或 gh-ost。
  • 原因：这些工具通过创建“影子表”并使用触发器同步增量数据的方式进行变更。它们对主库的负载更小、更平滑，支持限流、暂停/恢复，并且在发生问题时回滚和清理更安全。它们将 DDL 对业务的影响降到最低。
• 明确指定 DDL 算法：在手动执行 ALTER 时，养成明确指定 ALGORITHM 和 LOCK 子句的习惯，避免 MySQL 根据操作类型和版本自动选择非预期的算法。
  sql

  ⧉⛶

   1-- 明确意图，如果不支持 INSTANT，操作会失败而不是降级为 INPLACE
   2ALTER TABLE my_large_table ADD COLUMN new_col INT, ALGORITHM=INSTANT;

• 监控 DDL 过程：对于耗时较长的 DDL，需要进行监控。
  • SHOW PROCESSLIST;：查看 DDL 线程的状态，如 altering table, copy to tmp table。
  • performance_schema.events_stages_current：可以提供更详细的 DDL 执行阶段信息。
  • 监控主从延迟：重量级 DDL 可能会导致从库应用 binlog 变慢，需要关注复制状态。
• 规避 MDL 锁等待：DDL 操作在开始时需要获取 MDL 写锁，如果此时表上有未提交的长事务（即使是 SELECT），DDL 将会进入 Waiting for table metadata lock 状态，并阻塞后续所有对该表的请求。
  • 排查：通过 performance_schema.metadata_locks 和 information_schema.innodb_trx 表可以定位持有 MDL 锁的阻塞源。
  • 规避：在低峰期执行 DDL，并在执行前检查是否有长事务。

常见追问与延伸题目

1. 追问：既然 MySQL 8.0 有了 INSTANT ADD COLUMN，为什么大厂还在广泛使用 pt-online-schema-change 或 gh-ost？

   • 回答要点 1 (覆盖场景不全)：INSTANT 算法只支持少数 DDL 操作，如在末尾加列。但更常见的操作，如修改列类型、添加唯一索引、在列中间加字段等，仍然会回退到 INPLACE（重建表）甚至 COPY 算法。这些操作在繁忙的大表上依然风险很高。
   • 回答要点 2 (资源与风险控制)：INPLACE 重建表虽然在线，但会消耗大量 CPU 和 I/O，可能影响线上业务。pt-osc/gh-ost 提供了精细的负载控制（如 --max-lag, --chunk-time），它们通过分批拷贝数据，并主动检查主从延迟来控制节奏，对主库的冲击更小、更可控。
   • 回答要点 3 (高可用与运维便利性)：pt-osc/gh-ost 支持暂停和恢复，运维人员可以根据业务高峰情况灵活操作。而原生的 ALTER TABLE 一旦开始，除了 KILL 掉它（可能导致数据不一致或回滚耗时），没有其他控制手段。

2. 追问：如果我执行 ALTER TABLE ... ADD COLUMN ... FIRST，把新列加在表的第一列，会发生什么？

   • 回答要点 1 (无法 INSTANT)：这个操作无法使用 INSTANT 算法，即使在 MySQL 8.0 中。因为表的物理存储结构中，所有行的起始位置都必须改变。
   • 回答要点 2 (强制重建表)：它会强制使用 INPLACE（重建表）或 COPY 算法。这意味着 MySQL 需要扫描全表，并将每一行数据按照新的列顺序重写到新的数据页中。这是一个成本极高的操作，耗时与表大小成正比。
   • 回答要点 3 (工程建议)：在设计上应避免这种需求。表的物理列顺序不应影响应用逻辑。始终在表的末尾添加新列，以享受 INSTANT DDL 带来的巨大性能优势。如果应用层强依赖列顺序（如 SELECT *），应修改应用代码，明确指定列名，这是更规范的做法。

3. 追问：如果 ALTER TABLE 操作被一个长事务阻塞，状态是 Waiting for table metadata lock，我该如何安全地处理这种情况？

   • 回答要点 1 (定位阻塞源)：首先，通过 SHOW PROCESSLIST 找到处于 Waiting for table metadata lock 状态的 DDL 线程 ID。然后，使用以下查询定位阻塞它的事务和会话：
     sql

     ⧉⛶

      1SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'your_table_name';
      2-- 结合 information_schema.innodb_trx 和 performance_schema.threads 找到具体的SQL和用户

   • 回答要点 2 (分析与决策)：分析找到的阻塞源 SQL。如果它是一个关键业务的核心事务，不能随意终止。如果它是一个临时的查询、一个忘记提交的事务或一个来自数据分析人员的慢查询，可以考虑与对方沟通或直接 KILL 掉该会话。
   • 回答要点 3 (安全终止)：KILL <thread_id> 是最终手段。KILL QUERY 只终止当前语句，而 KILL CONNECTION (默认)会终止整个会话。对于阻塞 MDL 的事务，通常需要 KILL CONNECTION 来强制其回滚并释放锁。在操作前，务必评估 KILL 掉该事务对业务的影响。如果 DDL 语句设置了合理的 lock_wait_timeout，它会在超时后自动失败，避免无限期等待。