MySQL DDL 深度解析：从原理到生产实战

生产环境中，一条 ALTER TABLE 可能引发数小时的业务中断。本文从零开始，带你理解 DDL 的本质、锁表的原因，以及如何安全地执行表结构变更。

1. 什么是 DDL？

DDL（Data Definition Language，数据定义语言）是用来定义和修改数据库结构的 SQL 语句。

-- 创建表
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));

-- 修改表结构
ALTER TABLE users ADD COLUMN email VARCHAR(200);

-- 删除表
DROP TABLE users;

与之对应的是 DML（Data Manipulation Language），用来操作表中的数据：

-- DML 操作数据
SELECT * FROM users;
INSERT INTO users VALUES (1, '张三');
UPDATE users SET name = '李四' WHERE id = 1;
DELETE FROM users WHERE id = 1;

关键区别：

维度	DDL（定义结构）	DML（操作数据）
作用对象	表、索引、视图等结构	表中的数据行
核心命令	CREATE, ALTER, DROP	SELECT, INSERT, UPDATE
事务控制	隐式提交，无法回滚	需手动 COMMIT
类比	修改房屋的承重墙	在房间里摆放家具

💡 新手常见误区：执行 ALTER TABLE 后发现改错了，想用 ROLLBACK 撤销——这是不行的，DDL 执行后立即生效且无法回滚。

2. DDL 为什么会锁表？

执行 DDL 时，MySQL 需要确保表结构在变更期间不会被其他操作干扰。想象一下：

• 你正在给表添加一列
• 同时另一个查询正在读取这个表
• 那这个查询应该看到新列还是旧结构？

为了避免这种混乱，MySQL 引入了 MDL（Metadata Lock，元数据锁）。

2.1 MDL 是什么？

每一条访问表的 SQL 都会自动获取 MDL 锁，无需手动声明：

SQL 类型	获取的 MDL 锁	说明
SELECT	MDL 读锁	允许其他读操作
INSERT/UPDATE/DELETE	MDL 读锁	允许其他读写操作
ALTER TABLE	MDL 排他锁	阻塞所有其他操作

关键规则：

• 读锁之间兼容：多个查询可以同时执行
• 读锁与排他锁互斥：DDL 需要等待所有读写操作完成
• 排他锁与排他锁互斥：同一时间只能有一个 DDL

2.2 MDL 锁的生命周期

这是理解锁表问题的关键：MDL 锁在事务结束时才释放。

-- 会话1：开启事务，执行查询
BEGIN;
SELECT * FROM orders WHERE id = 1;  -- 获取 MDL 读锁
-- 注意：此时读锁仍被持有，因为事务未提交！

-- 会话2：尝试执行 DDL
ALTER TABLE orders ADD COLUMN discount DECIMAL(10,2);
-- 状态：Waiting for table metadata lock（被阻塞）

这就是长事务导致 DDL 锁表的根本原因。

2.3 雪崩效应

更糟糕的是，MDL 采用公平锁机制。一旦 DDL 开始等待，后续所有请求都必须排队：

%%{init: {'theme': 'base', 'themeVariables': {'actorBkg': '#3B82F6', 'actorTextColor': '#1E3A5F', 'actorBorder': '#2563EB', 'signalColor': '#60A5FA', 'activationBkgColor': '#DBEAFE', 'activationBorderColor': '#3B82F6'}}}%%
sequenceDiagram
    autonumber
    participant T1 as "事务1: 长查询"
    participant DDL as "DDL: ALTER"
    participant T2 as "事务2: SELECT"
    participant T3 as "事务3: INSERT"

    T1->>T1: "获取 MDL 读锁"
    T1->>T1: "执行长时间查询..."

    Note over DDL: DDL 开始执行
    DDL->>DDL: "请求 MDL 排他锁"
    DDL--xT1: "被事务1阻塞"

    Note over T2,T3: 后续请求到达
    T2->>T2: "请求 MDL 读锁"
    T2--xDDL: "排在 DDL 之后"
    T3->>T3: "请求 MDL 读锁"
    T3--xDDL: "排在 DDL 之后"

    Note over T1,T3: 所有请求堆积

结果：即使 DDL 只需要 1 秒执行完成，但因为一个长事务阻塞了它，后续成百上千的请求都会排队等待，最终导致连接池耗尽、服务雪崩。

3. Online DDL：如何避免锁表

MySQL 5.6 开始引入 Online DDL，让 DDL 执行期间尽量不阻塞业务。目前有三种算法：

%%{init: {'theme': 'base', 'themeVariables': { 'primaryColor': '#3B82F6', 'primaryTextColor': '#1E3A5F', 'primaryBorderColor': '#2563EB', 'lineColor': '#60A5FA', 'secondaryColor': '#10B981', 'tertiaryColor': '#F59E0B'}}}%%
flowchart LR
    A["COPY"] -->|"MySQL 5.6"| B["INPLACE"]
    B -->|"MySQL 8.0"| C["INSTANT"]

    A1["全程锁表"] --- A
    B1["大部分时间不锁表"] --- B
    C1["毫秒级完成"] --- C

    classDef danger fill:#EF4444,stroke:#DC2626,color:#fff
    classDef warning fill:#F59E0B,stroke:#D97706,color:#fff
    classDef success fill:#10B981,stroke:#059669,color:#fff

    class A danger
    class B warning
    class C success

3.1 COPY 算法（全程锁表）

最原始的方式，步骤如下：

1. 创建一个新的临时表
2. 把原表数据逐行复制到临时表
3. 删除原表，把临时表重命名

问题：

• ❌ 需要双倍存储空间
• ❌ 全程持有排他锁，所有读写都被阻塞
• ❌ 表越大，执行时间越长

3.2 INPLACE 算法（大部分时间不锁表）

改进版本，只在开始和结束时短暂加锁：

阶段	锁行为	是否阻塞业务
准备阶段	短暂排他锁	阻塞（毫秒级）
执行阶段	降级为读锁	不阻塞
提交阶段	重新获取排他锁	阻塞（毫秒级）

执行期间，业务的增删改操作会被记录到 row_log 中，DDL 完成后再回放这些变更。

%%{init: {'theme': 'base', 'themeVariables': { 'primaryColor': '#3B82F6', 'primaryTextColor': '#1E3A5F', 'primaryBorderColor': '#2563EB', 'lineColor': '#60A5FA', 'secondaryColor': '#10B981', 'tertiaryColor': '#F59E0B'}}}%%
flowchart TB
    A["开始: 获取排他锁"] --> B["降级为读锁"]
    B --> C["执行结构变更"]

    C --> D["业务 DML 继续执行"]
    D --> E["DML 记录到 row_log"]

    C --> F["变更完成"]
    E --> F
    F --> G["重新获取排他锁"]
    G --> H["回放 row_log"]
    H --> I["完成: 释放锁"]

    classDef lock fill:#EF4444,stroke:#DC2626,color:#fff
    classDef safe fill:#10B981,stroke:#059669,color:#fff

    class A,G lock
    class B,C,D,E,F,H,I safe

注意：

• ⚠️ 仍需额外磁盘空间存储 row_log
• ⚠️ 执行时间仍与数据量相关
• ⚠️ 开始和结束的短暂加锁可能被长事务阻塞

3.3 INSTANT 算法（毫秒级完成）

MySQL 8.0 引入，只修改数据字典，不触碰任何数据页：

-- INSTANT 方式添加列，毫秒级完成
ALTER TABLE users ADD COLUMN avatar VARCHAR(500), ALGORITHM=INSTANT;

支持的操作（MySQL 8.0+）：

• ✅ 在表末尾添加列
• ✅ 添加/删除虚拟生成列
• ✅ 修改列默认值
• ✅ 修改 ENUM/SET 定义

限制：

• ❌ 不支持在非末尾位置添加列（使用 AFTER 子句会失败）
• ❌ 不支持修改列类型
• ❌ 不支持删除列（MySQL 8.0.29+ 支持）

3.4 三种算法对比

特性	COPY	INPLACE	INSTANT
是否阻塞读写	全程阻塞	短暂阻塞	极短暂
执行速度	最慢	中等	最快
额外空间需求	双倍表空间	row_log	无
适用场景	兜底方案	大多数 DDL	特定操作

选择原则：INSTANT > INPLACE > COPY，能用左边的就不用右边的。

3.5 MySQL 版本演进

版本	重要特性
5.5	引入 MDL
5.6	引入 Online DDL，支持 INPLACE
5.7	增加 RENAME INDEX 等 INPLACE 操作
8.0	引入 INSTANT 算法
8.0.29	INSTANT 支持删除列、修改列顺序

4. 故障案例

4.1 案例一：长事务引发雪崩

场景：业务高峰期执行 ALTER TABLE orders ADD COLUMN discount DECIMAL(10,2);

现象：

• 30 秒后，所有涉及 orders 表的请求超时
• 连接数飙升，最终服务不可用

根因：存在一个未提交的长事务持有 MDL 读锁，DDL 等待读锁释放，后续所有请求排队等待 DDL。

%%{init: {'theme': 'base', 'themeVariables': { 'primaryColor': '#3B82F6', 'primaryTextColor': '#1E3A5F', 'primaryBorderColor': '#2563EB', 'lineColor': '#60A5FA', 'secondaryColor': '#10B981', 'tertiaryColor': '#F59E0B'}}}%%
flowchart LR
    A["长事务持有读锁"] --> B["DDL 等待排他锁"]
    B --> C["后续请求排队"]
    C --> D["连接池耗尽"]
    D --> E["服务雪崩"]

    classDef danger fill:#EF4444,stroke:#DC2626,color:#fff
    class A,E danger

解决方案：

-- 1. 执行前检查长事务
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10;

-- 2. 设置锁等待超时（MySQL 8.0+）
SET lock_wait_timeout = 5;  -- 5秒超时自动放弃
ALTER TABLE orders ADD COLUMN discount DECIMAL(10,2);

4.2 案例二：INPLACE DDL 导致复制延迟

场景：主库执行 ALTER TABLE logs MODIFY content TEXT;，耗时 2 小时

现象：从库复制延迟从 0 飙升到 7200 秒

根因：DDL 以单条语句写入 binlog，从库重放需要同样时间

%%{init: {'theme': 'base', 'themeVariables': { 'primaryColor': '#3B82F6', 'primaryTextColor': '#1E3A5F', 'primaryBorderColor': '#2563EB', 'lineColor': '#60A5FA', 'secondaryColor': '#10B981', 'tertiaryColor': '#F59E0B'}}}%%
flowchart LR
    subgraph Master [主库]
        A["DDL 2小时"] --> B["写入 binlog"]
    end

    subgraph Slave [从库]
        B --> C["重放 DDL"]
        C --> D["又需 2小时"]
    end

    classDef primary fill:#3B82F6,stroke:#2563EB,color:#fff
    classDef danger fill:#EF4444,stroke:#DC2626,color:#fff

    class A primary
    class D danger

解决方案：

• 使用 pt-online-schema-change 或 gh-ost
• 在从库先执行，再切换主从

4.3 案例三：INSTANT 失败

场景：

ALTER TABLE users ADD COLUMN avatar VARCHAR(500) AFTER name, ALGORITHM=INSTANT;
-- Error: ALGORITHM=INSTANT is not supported for this operation

根因：使用了 AFTER 子句指定列位置，INSTANT 只支持在末尾添加列

解决方案：去掉 AFTER 子句，或接受使用 INPLACE 算法

5. 核心要点

1. 所有 SQL 都会触发 MDL：SELECT 获取读锁，DDL 获取排他锁
2. MDL 在事务结束时才释放：长事务是锁表问题的常见元凶
3. 公平锁机制导致雪崩：DDL 一旦等待，后续请求全部排队
4. 优先使用 INSTANT：毫秒级完成，无锁表风险
5. INPLACE 并非完全无锁：开始和结束阶段仍需短暂加锁
6. 执行前必须检查长事务：一个未提交的事务就能冻结整个 DDL

参考资料

• MySQL 8.0 Reference Manual: InnoDB Online DDL Operations
• MySQL 8.0 Reference Manual: Metadata Locking
• Percona Toolkit: pt-online-schema-change
• GitHub: gh-ost