一、前言

Github：https://github.com/HealerJean

博客：http://blog.healerjean.com

DDL（data definition language）数据库定义语言：

其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上

二、DDL 执行方式

MySQL DDL 表结构变更的方式多种多样，每种方式都有其独特的优势和局限性。在选择合适的 DDL 方式时，需要根据具体的业务场景、数据库版本和性能要求来综合考虑。对于大表或高并发场景，建议优先考虑 Online DDL 或 pt- osc 方式以减少对业务的影响。同时，随着 MySQL 版本的更新迭代，新的 DDL 执行方式（如 INSTANT DDL）也将为数据库管理带来更多便利和高效性。

2、Online DDL

Online DDL（在线数据定义语言）是一种在不中断数据库服务的情况下修改数据库结构的技术。这种技术能够支持 DDL（数据定义语言）操作与 DML（数据操作语言）操作同时进行，从而减少对业务产生的影响。以下是对 Online DDL 原理的详细说明：

1）原理

**a、数据控制 **

1、Online DDL 操作被封装在一个事务中，确保数据的一致性和完整性。如果操作成功，则提交事务；如果失败，则回滚事务。

2、在执行 Online DDL期间，MySQL 会记录 DDL 操作对表数据产生的增量变更（如新增、修改、删除的行）。这些变更被记录在内部日志中，以便在 DDL 操作完成后重放这些变更，以保持数据的一致性。

**b、锁表机制 **

虽然 Online DDL 减少了锁表时间，但在某些阶段（如准备阶段、提交阶段）仍然需要锁表（这些锁的持续时间通常在毫秒级，远小于传统 DDL 的“全程锁表” ）。MySQL 会尽可能缩短这些锁表时间，并使用不同类型的锁（如共享锁、排他锁）来控制对表的访问。 MySQL 会根据需要选择合适的锁类型和级别。

• 原理：准备阶段”和“提交阶段”的锁，通常是：

  • 元数据锁（Metadata Lock, MDL）：防止其他线程同时修改表结构；

  • 或非常短暂的表级共享/排他锁（Table Lock）；

• 举例：执行 ALTER TABLE t ADD INDEX idx_x(x); 时：
  • 开始时：加一个短暂的 排他 MDL 锁，记录“我要开始建索引了”；
  • 然后释放，进入后台构建阶段；
  • 结束时：再加一个短暂的 排他 MDL 锁，将新索引状态从“不可见”改为“可见”；
  • 整个过程对 DML 影响极小。

• 查看 DDL 的锁级别

  • ALGORITHM = INPLACE

  • LOCK=NONE（表示允许并发 DML）

    • -- 明确指定锁级别
      ALTER TABLE t ADD INDEX idx_x(x) LOCK=NONE;
            
      -- 查看执行计划（是否 inplace）
      EXPLAIN ALTER TABLE t ADD INDEX idx_x(x);
      

• 结论：因此，MySQL 的 Online DDL 并非完全无锁，而是通过精细的锁控制 + 增量日志 + 异步执行，将锁的影响降到最低，实现近乎“无感”的在线变更。

d、表重建的优化

1、在进行结构变更时，MySQL 会尽量减少对原表的影响。对于支持 Online DDL的操作，MySQL 会尝试使用一种称为 “ In-place ”的方式，即直接在原表上修改，而不需要复制整个表到临时表中。

2、对于某些不支持 In-place 的操作，MySQL 会采用一种称为“ Copy ”的方式，即创建一个新表，将原表数据复制到新表中，并在新表上执行 DDL 操作，然后替换原表。但在这个过程中，MySQL 会优化锁的使用，减少锁表时间。

2）优点：

1、减少系统停机时间：

1、传统的 DDL 操作通常需要锁定受影响的表，导致数据库在执行 DDL 操作期间无法对外提供服务，造成系统停机时间。而 Online DDL能够在不中断正常服务的情况下执行表结构的变更，从而显著减少系统停机时间，提高系统可用性。

2、减低业务风险： Online DDL可以避免由于长时间锁定表而导致的业务中断和故障，这对于对数据库连续性和可用性要求较高的业务尤为重要。通过减少停机时间和提高并发性能，Online DDL 能够降低业务运行的风险。

2、提高并发性能：

1、Online DDL 允许 DDL 操作与 DML（数据操作语言）操作并发执行，减少对表的锁定时间。这提高了数据库的并发性能，使得用户可以在不受影响的情况下继续进行数据操作，提升了系统的整体性能和响应速度。

3、降低磁盘和 IO 消耗：

1、相较于传统的 table - copy 方式，Online DDL 中的 In- place 算法直接在原表上进行结构修改，无需创建新的临时表来存储数据。这种方式减少了磁盘空间的消耗和 IO 开销，提高了资源利用效率。

3）缺点：

**1、性能影响 **

1、尽管 Online DDL 减少了锁表时间，但在某些阶段仍然需要锁表。如果此时有其他事务正在访问该表，则可能需要等待锁释放，从而影响性能。

2、在执行 Online DDL 期间，由于需要记录增量日志、处理锁等操作，可能会对数据库性能产生一定影响。特别是在处理大表或复杂 DDL 操作时，性能影响可能更加明显。

b、增量日志大小有限制

1、在执行 Online DDL 时，MySQL 会记录增量日志。但这些日志的大小是有限制的，如果表数据变更量非常大，可能会超出日志的限制，从而影响操作的进行。

3、有可能造成主从延迟

1、从库要等待主库执行完并将相关的 binlog 同步过来才能执行，在复制环境中，Online DDL 操作可能会在主库上产生大量的增量日志，这些日志需要同步到从库。如果网络延迟或从库处理能力不足，可能会导致主从延迟。

4、无法暂停，只能中断

一旦 Online DDL 操作开始，通常无法暂停。如果需要取消操作，只能中断并回滚事务。这可能会导致已经执行的部分变更被撤销，影响数据的一致性。

2、pt-osc

Percona Toolkit 的 pt-online-schema-change

全表拷贝的设计，让主从同步仅需传递 “新表替换” 的元数据变更（旧表数据已完整拷贝到新表），几乎无延迟。但全表 IO 消耗、时间成本高，适配 “从库延迟零容忍” 场景，逻辑合理。

1）原理

1、创建新表：根据原表结构创建一个新表，并在新表上执行所需的 DDL 操作（如添加列、修改列类型等）。

2、创建触发器：在原表上创建三个触发器，分别对应 INSERT、UPDATE、DELETE 操作，以捕获这些操作期间的数据变更，并将这些变更应用到新表中。

3、拷贝数据：将原表中的数据分块（chunk）拷贝到新表中。在拷贝过程中，原表上的触发器会确保任何新的 DML 操作都被同步到新表中。

4、切换表名：完成数据拷贝后，将原表重命名为一个临时名称，然后将新表重命名为原表的名称，最后删除原表的临时名称。

2）优点

1、减少业务影响：pt-osc 在执行 DDL 操作时，通过创建新表和触发器的方式，避免了直接对原表加锁，从而减少了对业务读写操作的影响。

2、数据一致性：通过触发器和分块拷贝数据的方式，pt-osc 确保了原表和新表在数据变更过程中的一致性。

3、灵活性：pt-osc 支持多种 DDL 操作，如添加列、修改列类型、添加索引等，提供了较高的灵活性。

4、易于使用：pt-osc 以命令行方式执行，操作简单，易于集成到自动化脚本中。

5、无主从延迟：

• 减少 DDL 对从库影响：pt - osc创建影子表和拷贝数据的过程，是在主库上独立进行的，这些操作并不直接影响主从复制链路。从库仍然可以正常地接收和执行主库发送过来的 DML（数据操作语言，如 INSERT、UPDATE、DELETE）操作。
• 变更操作的轻量级同步：最后一步的原子表重命名操作，在主从复制中只是一个元数据的变更，这个变更在从库上执行速度非常快。因为此时影子表已经准备好，数据也和主库一致，从库只需要执行简单的表名替换操作，而不需要像传统方式那样去执行耗时的索引创建或表结构变更操作，所以几乎不会产生延迟 。

3）缺点

1、对存储不友好：新临时表存放数据也需要空间（最大空间需求可能和原表一样），拷贝数据时还会产生大量 binlog，所以对于本来空间就紧张的实例而言，这方式真的是雪上加霜

2、执行时间长&磁盘 IO可能较高 ：要拷贝全量数据，所以执行时间也会很长，需要将老表的所有数据都拷贝到新表上，这就意味着拷贝期间，磁盘 IO 可能较高

3、对表结构有要求：：pt - osc 要求表必须有主键或唯一索引，以确保触发器能够正确地同步数据变更。如果表不满足这些要求，pt - osc将无法执行。

4、如果运行过程中报错，无法从上一个位置继续 ：pt - osc 在执行过程中，如果遇到错误导致中断，通常需要从头开始重新执行，因为它没有内置的断点续传机制。

5、在高负载或主从延迟过大时可能暂停操作：为了减少对业务的影响，pt - osc 会检测服务器的负载和主从延迟情况。如果检测到负载过高或延迟过大，pt-osc 可能会暂停操作，等待条件改善后再继续。

3、COPY 方式（传统 DDL ）

1）原理：

1、在执行 DDL 操作前，MySQL 会创建一个临时表来存储变更后的表结构。

2、锁定原表，禁止 DML 操作（但允许查询）。

3、将原表数据复制到临时表。

4、释放锁，并将临时表重命名为原表名，原表被删除。

2）优点：

1、实现简单，适用于所有 MySQL 版本。

3）缺点：

1、DDL 操作期间，表被锁定，无法进行 DML 操作，对业务影响大。

2、复制全量数据可能导致执行时间较长，且磁盘 IO 较高。

2、INSTANT 方式（ MySQL 8.0及以上版本）

1）原理：

• INSTANT DDL 是 MySQL 8.0.12 引入的高效 DDL 执行模式，仅修改数据字典中的元数据，不触发数据复制、页重组或表重建。

• 对于已存在的数据行，新列的值在查询时动态生成（如使用默认值）；只有新插入或更新的行才会真正写入该列。

• 操作可在毫秒级完成，且完全支持并发 DML（LOCK=NONE）。

• 注意：添加的列必须是表的最后一列，且不能是主键或唯一键的一部分

2）优点：

• 极速执行：无论表大小，添加字段几乎瞬时完成；
• 零业务影响：全程不阻塞读写操作；
• 节省资源：无需临时空间，减少 I/O 和 CPU 开销。

3）缺点：

• 支持的操作有限：目前仅支持：在末尾添加列（非主键/唯一键）、添加/删除虚拟生成列；、修改列默认值、重命名表等。

• 不支持的操作：修改列类型、删除普通列、添加索引、修改字符集等仍需 INPLACE 或 COPY。

• 后续 UPDATE 可能引发页分裂：首次更新包含“INSTANT 列”的旧行时，可能增加存储碎片。

三、DDL 操作

1、索引操作

1）二级索引

3）唯一索引及主键操作

2、列操作

1）添加列、删除列、重排列顺序、变更列类型、修改列为空或非空

• Online DDL：从图可以看出，这 6 种 DDL 操作，在选择 Online 方式时，都会重建表，效果上与 pt - osc 并无太大差别，还得担忧从库复制延迟的问题，那既然如此，直接选择 pt- osc 的方式更省事。
  • Online DDL 以下这几种情况会锁表，堵塞其他增删改操作，需要注意：
    • 增加一个自增列。
    • 单纯修改列类型。
    • 修改列名，同时修改了列类型（该情形应该算修改列类型的一个特殊例子）（只支持 Online DDL）。
• pt- osc：首选。需要注意的是，因为 pt - osc 不支持修改列名，所以上述的第三点，只能选择 Online DDL的方式执行，但是选择Online DDL，又会出现锁表导致堵塞其他增删改的操作，所以慎重。

2）修改列名（只改列名）、设置 / 删除默认值

• Online DDL：从图三可以看出，这类操作会只修改表元数据信息，速度极快，直接选 Online DDL方式即可

• pt - osc ：直接无视

3）修改自增列的自增值

⬤ Online DDL：在 MySQL 8.0 版本前，自增值不存在持久化的概念，修改这个值，只会在内存中修改，更不涉及数据的拷贝及变动，所以直接使用 Online DDL 方式即可。 

⬤ pt - osc ：直接无视

2）VARCHAR 列增加列大小

MySQL 底层在存储变长列 VARCHAR 列的内容时，还会额外记录内容占用字节数的大小，记录这个大小，也是需要空间的。另外，还有个东西咱需要了解下，VARCHAR 列存储一个字符，使用 utf8 字符集时，最大需要 3 字节（比如存储一个中文字符），而 utf8mb4最大需要 4 字节（比如存储一个表情符）。知道了使用什么字符集，咱就可以计算出存储一个 VARCHAR变 长列最大需要多少字节了。

记录列占用字节数大小，所需的空间，会根据 列存储最大需要字节数 细分出两种情况：

1）列存储最大需要字节数为 0 - 255 时，记录列占用字节数大小需要 1 字节。

2）列存储最大需要字节数为 256 - 65535 时，记录列占用字节数大小需要 2 字节。

3）因表数据行，非大对象的列，总的存储内容长度限制就是 65535，所以，单列 VARCHAR 存储需求自然也不能超过这个限制，也就是不存在超过 65535的情况。



回归正题，VARCHAR 列增加大小：

1）如果列长度增加后，记录列占用字节数大小所需字节数不变，也就是列存储最大需要字节数依然在同一个范围内：0-255 或 256-65535，那这类操作，ALGORITHM 支持使用 ` In Place 算法，只会修改表的元数据信息，瞬间完成，此情形，直接选 Online DDL`即可。

2）如果列长度增加后，记录列占用字节数大小所需字节数变了，从 1 字节变成 2 字节，ALGORITHM 则只支持 COPY 算法，这就意味着会出现数据拷贝的情况，同时会堵塞其他增删改的操作，这情形选 pt - osc。

# 建表
create table t1(name varchar(10) null) charset=utf8mb4;

# 列存储最大需要字节数计算：长度 10，utf8mb4字符集存储单字符最大需要字节数 4
# 列存储最大需要字节数 = 10 * 4 = 40
# 记录列占用字节数大小所需空间为1字节


# 表结构变更一
alter table t1 modify name varchar(63) null;

# 列存储最大需要字节数计算：长度 63，utf8mb4字符集存储单字符最大需要字节数 4
# 列存储最大需要字节数 = 63 * 4 = 252
# 记录列占用字节数大小所需空间依然为1字节，ALGORITHM默认选用In Place，Online DDL执行瞬间完成


# 表结构变更二
alter table t1 modify name varchar(64) null;

# 列存储最大需要字节数计算：长度 64，utf8mb4字符集存储单字符最大需要字节数 4
# 列存储最大需要字节数 = 64 * 4 = 256
# 记录列占用字节数大小所需空间变为2字节，ALGORITHM只能使用COPY，引发数据拷贝，堵塞其他增删改操作，选择pt-osc

2、表操作

1）修改表名

⬤ Online DDL：从图看出，Online DDL 修改表名，只会涉及到修改表的元数据信息，瞬间完成。

⬤ pt - osc ：不支持

2） 表碎片整理、更改行格式、修改字符集、收集统计信息

⬤ Online DDL：咱对表的操作，常用到的，可能就是表碎片整理、更改行格式（比如改成压缩模式），修改字符集（含内容转换）及收集统计信息，这些操作，从图看到了，基本都是需要重建表，建议首选 pt - osc 。

⬤ pt - osc ：首选

3、表分区

1）普通表转分区表

⬤ Online DDL：他的本质是新建一个临时表，每个分区对应一个数据文件，然后进行拷贝，拷贝完毕后，表名互换，删除老表。眼熟不？从某种程度上讲，这个过程与 pt- osc 是相似的，但是，Online DDL 方式会锁表，堵塞其他增删改操作，所以，直接选择pt-osc方式即可。

⬤ pt - osc ：必选

2）新增分区、删除分区、TRUNCATE 分区

⬤ Online DDL：

新增分区，只分析常用的 RANGE 及 LIST 分区。RANGE 分区新增分区，有个严格的限制，新分区 less than 的值必须是递增的，换句话讲就是不存在数据拷贝的问题。LIST 分区，这个更直接，相关内容如果在 LIST 分区中不存在，直接不允许插入，新增分区也不存在数据拷贝的问题。所以，这两种选用 Online DDL 时，操作几乎都是瞬时完成的，直接使用 Online DDL 即可。

删除分区时，会对当前分区上锁，堵塞该分区的其他增删改操作，但既然你都打算删除分区了，想必自然也不会再对该分区有其他操作。 其对应系统底层的操作，类似于直接将分区对应的物理文件进行删除，操作时，如果文件足够大，系统 IO 会瞬间暴涨，继而影响业务，所以建议在业务低峰期间进行。

TRUNCATE 分区操作，对应系统底层的操作，类似于直接将分区对应的物理文件进行清空，操作时，如果文件足够大，系 统 IO会 瞬间暴涨，继而影响业务，所以建议在业务低峰期间进行。



pt - osc ：直接无视



四、问题

1、元数据锁和表锁

+-------------------------------------------------------------+
|                        MySQL 表操作                         |
+-------------------------------------------------------------+
| 1. 打开表？ → 先申请 MDL 锁                                  |
|    - DML → 申请 MDL-S（共享）                                |
|    - DDL → 申请 MDL-X（排他）                                |
|                                                             |
| 2. DDL 执行时，通过 LOCK 选项决定并发策略：                   |
|    - LOCK=EXCLUSIVE → 一直拿 MDL-X → 阻塞所有 DML            |
|    - LOCK=SHARED    → 允许部分 DML（如 SELECT）              |
|    - LOCK=NONE      → 允许所有 DML → 用 row log 记录变更     |
|                                                             |
| 3. 最终阶段：短暂拿 MDL-X 锁，完成原子切换                   |
+-------------------------------------------------------------+

1）什么是元数据锁（Metadata Lock, MDL）

场景：你正在图书馆看书（DML：SELECT * FROM book）管理员突然要修改这本书的分类标签（DDL：ALTER TABLE book ADD COLUMN tag）。图书馆规定：只要有人在看这本书，管理员就不能改它的标签。

• 你看书时，先申请一个“读权限”（MDL-S 锁）；
• 管理员要改标签，必须申请“写权限”（MDL-X 锁）；
• 但你没看完（事务没提交），管理员就得一直等着你释放“读权限”。

关键点：**MDL 锁是在事务开始时就加上的，事务结束才释放！ **

• 这就是为什么一个长查询会导致 DDL 卡住的原因。

-- 事务A（长时间运行）
START TRANSACTION;
SELECT * FROM users;  -- 加了 MDL-S 锁，一直不释放！

-- 事务B（想改表结构）
ALTER TABLE users ADD COLUMN age INT;  -- 申请 MDL-X 锁 → 会一直等事务A结束！

2）表锁：LOCK=NONE/SHARED/EXCLUSIVE 是什么？

这是你在执行 ALTER TABLE 时，可以主动指定的一个选项，用来控制 DDL 执行期间是否允许 DML。

ALTER TABLE users ADD COLUMN age INT LOCK=NONE;  -- 允许并发读写

问题：它是怎么实现“允许并发 DML”的？

答案：你以为是“大家同时改一个表”？错！MySQL 是这么玩的：实现原理：Row Log 缓冲区 + 重放机制

1. DDL 开始时，先申请 MDL-X 锁（短暂）；
2. 然后降级为 MDL-S 或其他模式，允许新 DML 进来；
3. 所有在 DDL 执行期间发生的 DML 操作（INSERT/UPDATE/DELETE），都被记录到一个叫 row log 的缓冲区；
4. 当 DDL 主体操作完成（比如表重建完），MySQL 把 row log 里的变更重放到新表上；
5. 最后短暂拿一下 MDL-X 锁，原子性地切换表结构，提交。

2、如何避免被 MDL 卡住？

• 避免长事务

  • SET SESSION innodb_lock_wait_timeout = 10;
    SET SESSION lock_wait_timeout = 10;
    

• 在低峰期执行 DDL；

• 使用 pt-online-schema-change 自动处理 MDL 等待；

• 监控 MDL 等待

  SELECT * FROM performance_schema.metadata_locks;
  SELECT * FROM sys.schema_table_lock_waits;  -- 更友好
  

总结：一句话记住 MDL 是 “门卫”，决定谁可以进这个表；LOCK = NONE 是“施工公告”，说‘装修期间营业’，但最后还得关门5秒换招牌。

• 要想 DDL 不卡，先让“门卫”放行（没有长事务占用 MDL-S）；
• 再用 LOCK=NONE 实现“边营业边装修”。

3、In Place = Yes 和 Rebuilds Table = Yes 冲突吗

不冲突：

1. 是否用了 COPY 算法？ → In Place
2. 是否动了数据页？ → Rebuilds Table
3. 是否只改了元数据？ → Only Modifies Metadata（最高级优化）

4、怎么知道使用的是哪种呢？

判断原生 DDL 使用了哪种算法：INSTANT / INPLACE / COPY

1）方法 1：使用 EXPLAIN ALTER（MySQL 8.0.19+）

EXPLAIN FORMAT=JSON ALTER TABLE users ADD COLUMN status INT DEFAULT 1;

返回结果中会包含：

{
  "alter_information": {
    "access_mode": "none",
    "algorithm": "instant",
    "lock": "none"
  }
}

2）通过 performance_schema 观察 DDL 执行过程

-- 开启相关消费者（通常默认开启）
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_statements%';

-- 执行 ALTER 后查看
SELECT 
    DIGEST_TEXT,
    OTHER_TAG   -- 这里会显示 algorithm 类型
FROM performance_schema.events_statements_history 
WHERE DIGEST_TEXT LIKE 'ALTER TABLE%';

3）方法 3：通过执行时间和影响判断（经验法）

4）强制指定 ALGORITHM 来测试

你可以主动指定算法，看是否报错：

-- 测试是否支持 INSTANT
ALTER TABLE users ADD COLUMN age INT ALGORITHM=INSTANT; -- 成功 → 支持 INSTANT

-- 测试是否必须 COPY
ALTER TABLE users MODIFY COLUMN name VARCHAR(500) ALGORITHM=COPY; -- 成功 → 原本可能走 INPLACE

如果某个 ALGORITHM 不支持，MySQL 会报错：

ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported...