MySQL线上DDL操作全解析：从Copy、Online到Instant算法与版本演进

要回答如何给线上运行的表执行DDL语句，首先需要理解执行DDL时MySQL内部发生了什么。

在 MySQL 5.6 之前，执行DDL操作会给表加上写锁，这会阻塞该表所有的读写操作。对生产环境而言，写锁持续的时间越长，带来的业务中断风险和性能影响就越大。因此，回答的核心就在于如何避免或最小化这种影响。

MySQL中主要存在两种DDL算法：

• COPY算法
• INPLACE算法（即Online DDL）

一、COPY算法

在MySQL 5.6之前，这是唯一的算法。执行DDL或表重建时，其流程如下：首先创建一个临时表(tmp)，然后将原表A的数据逐行拷贝到tmp中；数据迁移完成后，将原表A重命名为其他名称，再将tmp重命名为A，从而完成操作。

不难发现，COPY算法的数据拷贝过程会产生大量IO，非常耗时。为了确保数据一致性，在整个拷贝完成（即上图的state 4）之前，原表A不允许进行任何更新操作，这意味着整个过程中表都被加了写锁。因此，COPY算法并非“Online”（在线）的，它会阻塞DML操作。

二、INPLACE算法 (Online DDL)

为了解决写锁时间过长的问题，MySQL 5.6引入了INPLACE算法，即通常所说的Online DDL。它通过引入日志文件(row log) 来大幅缩短需要加锁的时间。深入了解 MySQL 的数据库与中间件 内部机制，有助于更好地应用这些算法。

其核心流程如下：

1. 建立一个临时文件，扫描表A主键的所有数据页。
2. 利用数据页中的记录为表A生成一个新的B+树，存储到这个临时文件中。
3. 在生成临时文件的同时，将所有对表A的DML操作记录到一个行日志文件(row log)中（对应下图state 2）。
4. 临时文件生成后，将row log中的操作应用到临时文件，得到逻辑上与表A一致的数据文件（对应state 3）。
5. 最后，用这个临时的数据文件替换表A的原始数据文件。

一个典型的用法是：

alter table A engine = innodb, ALGORITHM = inplace;

由于row log的记录和重放机制，INPLACE算法允许在表重建或DDL执行期间对表进行增删改操作，这也是其被称为“Online”的缘由。

那么，既然有row log记录变更，是否完全不需要写锁了呢？并非如此。INPLACE算法在启动时仍需获取MDL（元数据锁）写锁，但在开始拷贝数据前，这个写锁会退化为读锁。MDL读锁不会阻塞DML操作，从而实现了“Online”。而COPY算法则是在整个拷贝期间都持有MDL写锁。

为什么需要先获取写锁再退化？
这是为了保护DDL操作自身的安全。获取MDL写锁可以确保在修改表物理结构和元数据时，没有其他线程同时进行DDL，从而避免元数据损坏或死锁等问题。

值得注意的是，并非所有ALTER操作都会长时间阻塞。例如，在表末尾增加列（ALTER TABLE ... ADD COLUMN ...）这类只修改元数据的操作，加锁时间极短。而如果是在表中间增删列，则可能涉及数据行的重排，需要更谨慎。

1. COPY与INPLACE创建的临时对象有何不同？

观察两幅流程图可以发现：

• COPY算法：在Server层创建名为tmp_table的临时表。
• INPLACE算法：在InnoDB存储引擎内部创建名为tmp_file的临时文件。
  由于tmp_file的整个操作都在InnoDB内部完成，从Server层的视角看，数据并未“移动”，因此被称为“原地”(inplace)操作。

2. Online与Inplace的关系

• 如果一次DDL过程是Online的，那么它一定是inplace的。
• 但反过来，inplace的DDL不一定就是Online的。例如，截止MySQL 8.0，添加全文索引(FULLTEXT index)和空间索引(SPATIAL index)属于inplace操作，但会阻塞DML，因此不是Online的。
  此外，并非所有DDL都支持INPLACE，比如修改列数据类型。具体支持情况需参考官方文档。

三、Instant Add Column算法

INPLACE算法虽然不阻塞DML，但如果表很大，整个重建过程可能持续数分钟甚至更久，形成一个“长事务”。为了进一步实现“秒级”甚至“毫秒级”的加字段操作，MySQL 8.0推出了Instant Add Column算法。

ALTER TABLE A ADD COLUMN new_column INT, ALGORITHM = INSTANT;

其核心思想是：只修改元数据，不触碰现有数据行。

1. 不创建临时文件，不拷贝任何现有数据。
2. 仅在表的“数据字典”中记录“新增了一列，默认值为XXX，位置在最后”。
3. 读取旧数据页时，如果遇到缺少新列物理存储的行，自动用默认值填充（这依赖于MySQL 8.0新的数据页格式能力）。
4. 只有新插入或更新的数据行，才会将新列的值实际写入磁盘页。

因此，Instant Add Column速度极快，无论表多大，操作通常都在毫秒到秒级内完成。理解和应用这些高级特性是 数据库运维 工作中的重要部分。

主要限制：

• 只能加在最后一列：这是最关键的限制。因为加在末尾不会改变已有数据行中各列的物理偏移量。若在中间插入，则所有后续列的偏移都需要调整，必须重写所有旧数据行。
• 字段必须允许NULL或有默认值（NOT NULL且无默认值不行）。
• 新字段不能有唯一约束（UNIQUE, PRIMARY KEY）或自增属性。
• 表必须是InnoDB引擎，并使用MySQL 8.0.12及以上版本的新数据页格式。

四、主从切换方案

当上述算法均不适用（例如在低版本MySQL中执行不支持Online的DDL）时，可以采用高可用架构下的主从切换方案。
在双主或主从集群中，可以：

1. 先在备库（Master B/Slave）上执行DDL操作。
2. 完成DDL后，将应用流量切换到已更新的备库（Master B）。
3. 再对原主库（Master A）执行同样的DDL。
4. DDL完成后，可根据需要将流量切回或保持新架构。

五、总结与最佳实践

回到最初的问题：如何给线上运行的表执行DDL？

1. 评估场景与环境：首先明确业务容忍的中断时间、MySQL版本和具体的DDL操作类型。
2. 优先使用Instant算法（MySQL 8.0+）：对于添加允许NULL或有默认值的末尾列，使用ALGORITHM=INSTANT，几乎无感。
3. 其次使用Online DDL（MySQL 5.6/5.7）：对于支持的操作，使用ALGORITHM=INPLACE，以最小化锁表时间。
4. 降级方案：主从切换：对于不支持Online的DDL或更低版本，利用数据库高可用架构，通过切换来规避主库长时间锁表的风险。