2. DDL

1. DDL类型和流程

1.1 紧凑

MySQL 中定义紧凑：页是满的，主键索引和二级索引树，叶子节点是满的就表示是紧凑的，不要全全满，因为 MySQL 可能会预留1/16，这种情况可以将之视为紧凑

1.2 DDL算法类型

DDL 算法类型分为：copy、inplace、instant

1.2.1 copy

copy 算法流程主要分为以上五步： lock、copy、drop、rename、unlock

copy 步骤，会新建一个临时表，将旧表数据一行行拷贝到新表，逻辑先从引擎层读取数据到 server 层，然后 server 层再将数据插入到引擎层，为什么不直接在引擎层做数据拷贝呢？上一节有提到过，引擎层能做的事就是将数据一行行返回给 server 层，如果我们的 DDL 语句不涉及列的变更，是可以简单的拷贝的，但是一旦有列变更，引擎层就无法处理，因此 MySQL 设计为统一查询到 server 层后，在 server 层处理后，插入到临时表，因此 space id 会发生变化，因为是一张新建的表，同时 rows_examined 为全表行数

疑问：再第三步 drop 表时，由于禁写不禁读，因此读请求是允许，如果再 drop 的瞬间有 select，那是否会出现 table not exists 问题？

不会，MySQL 能保证

1.2.2 inplace（online）

1.2.2.1 静态 inplace

以加索引为例，流程主要为：

• 锁表，只读
• 扫描主键，读 c 到临时文件
• 临时文件排序
• 新建索引 c
• 将排序后数据插入索引 c
• 解锁

为什么叫做 inplace？因为整个流程没有新建表，都是在引擎层做的，因此 space id 没有变化，并且 rows_Examined 为 0，跟 copy 相比，inplace 算法得到的索引更为紧凑

如果一个语句同时加多个索引，那么会扫描主键索引多少遍？答案是一遍，扫描一遍，同时取多个列的数据创建多个临时文件再走后面流程

1.2.2.2 动态 inplace

动态相较于上面静态而言，不同点在于加锁时机不同，动态是在索引构建完成后，对表加读锁，然后应用 log，完成后解锁，整体流程为：

• 扫描主键，读 c 到临时文件
• 临时文件排序
• 新建索引 c
• 将排序后数据插入索引 c
• 锁表，只读
• 应用 alter_log
• 解锁

可以看到动态 inplace 锁表时间很短，或者相较于 copy 以及静态 inplace 而言，很短，因此 inplace 算法被视为 Online DDL

有个问题，alter_log 有多大？超过大小后怎么办？

alter_log 大小为 innodb_online_alter_log_max_size，如果超过该参数大小，回滚 DDL 语句，将 alter_log 和已经建好的索引树删掉即可，因此建议低峰期做，减小 alter_log 满的几率

再一个点，由于有 alter_log，在应用 alter_log 前，索引是紧凑的，但是应用 alter_log 后，就不紧凑了，因为 alter_log 中的数据不是有序的，并且很大可能会插入到索引中间，造成页分裂，MySQL 设计者设计页时会预留 1/16 空间，这样能极大缓解应用 alter_log 时造成页分裂的情况

1.2.3 instant（online）

直接修改元数据，可以使用语句 select * from information_schema.innodb_tables where name = 'xxxdb/xxxtable'\G来查看某个库某个表的元数据信息，如下所示：

主要过程就是直接修改表的元数据，修改后表 total_row_versions+1，MySQL 会记录每行的 version，后续增删改语句涉及到的行，version 会及修改为 total_row_versions，因此表中行可能存在某些行真实有某些列，但某些行却没有，这样查询是怎么知道该真实取列的值还是从元数据中取值呢？

前面说过，MySQL 表每行都会维护一个 version，这样在后续查询中，如果该行 version 小于 total_row_versions，那么就取默认值或者 null，否则才真正取该列的值

以下面表为例：

可以看到，每一行都有一个 version，当前 total_row_versions 为 2，这样在查询前三行时，由于 version 为 1，小于 2，因此直接从元数据中取值，第四行则真实取列 X 的值，引擎层会主动实现该逻辑，这样在 server 层看到的就是正确的结果

total_row_versions 最大值为 64，一旦超过，MySQL 就会强制在最新的 DDL 中使用 copy 算法，需要特别注意

有没有一种情况，给一个小表执行 instant DDL 语句，该语句会执行很长时间，比如超过一分钟？

以上图为例，session 执行 DDL 语句，需要申请 MDL 写锁，会被 session1 的 MDL 读锁堵住，要等到 session1 执行完成 commit 后才会解锁，然后才能执行

此时由于 session2 在等待 MDL 写锁，我们知道，MDL 解锁过程可以理解为队列排队模式，因此后面的 session3 请求会被堵住，这就会造成给小表执行 DDL 影响业务的情况，因此在执行 DDL 之前，要检查是否有长事务存在，如果有，需要等待长事务执行完成或者主动 kill 掉长事务才能执行 DDL

instant DDL MDL加锁逻辑：

1. MDL X 锁
2. 降级为 S 锁
3. 执行 DDL 过程
4. 升级为 MDL X 锁
5. 解锁 MDL X 锁

1.2.4 每种算法适用场景

算法	锁表时间	资源消耗	支持的操作示例	适用场景
COPY	全程锁表（阻塞读写）	高（IO/CPU）	修改列类型、删除主键、修改字符集	低版本或复杂操作
INPLACE	短暂元数据锁（可能部分阻塞写）	中（可能重建数据）	添加索引、修改主键、添加列（需重建表）	平衡性能与操作复杂度
INSTANT	极短暂元数据锁	低（仅元数据）	添加末尾列、删除列（8.0.23+）、重命名列/表	高版本MySQL，最小化业务中断

MySQL优化器会根据操作类型和版本自动选择最优算法，优先级：
INSTANT → INPLACE → COPY。
可通过EXPLAIN ALTER TABLE查看算法选择。

1.2.4.1 copy 适用场景

• 不支持Inplace或Instant的操作：

  • 修改列的数据类型（如VARCHAR(50) → INT）。
  • 删除主键（需重建表）。
  • 修改表的字符集或排序规则。

• 低版本MySQL（如5.5及以下）：
  默认使用COPY算法，因Inplace和Instant尚未支持。

• 特定存储引擎（如MyISAM）：
  某些操作仅支持COPY。

1.2.4.2 inplace 适用场景

• 支持“快速”Inplace的操作（仅修改元数据）：

  • 添加/删除二级索引（InnoDB）。
  • 重命名列（MySQL 8.0+）。
  • 设置DEFAULT值（MySQL 8.0+）。

• 需要重建数据的操作（部分Inplace）：

  • 添加VARCHAR列（长度增加）。
  • 修改主键（需重组数据）。
  • OPTIMIZE TABLE（隐式重建表）。

1.2.4.3 instant 适用场景

• 仅修改元数据的轻量操作：

  • 添加列（必须位于列末尾且无默认值或默认值为常量）。
  • 删除列（MySQL 8.0.23+）。
  • 重命名表/列。
  • 修改COMMENT、ENUM/SET选项（不改变存储空间）。

• 快速DDL需求：
  需要最小化业务中断的场景（如在线服务）。

• 不支持的操作：

  • 修改列数据类型。
  • 修改主键。
  • 添加自增列（需重建表）。

• 版本要求：
  MySQL 8.0.12+（部分操作需更高版本，如删除列需8.0.23+）。

1.2.4.4 注意事项

• 版本兼容性：
  INSTANT算法需MySQL 8.0+，且不同小版本支持的操作不同（如删除列在8.0.23+才支持）。
• 存储引擎限制：
  InnoDB对INSTANT支持最完善，其他引擎可能退化为COPY。
• 空间管理：
  INSTANT操作可能导致表元数据碎片，定期重建表可优化（OPTIMIZE TABLE）。

2. optimize table 过程分析

不能用 instant 和 inplace，得到的普通索引也是紧凑的，原因是先重建主键索引，使用 copy 算法将数据拷贝到新表，完成后再添加其他索引，使用 inplace 算法是紧凑的

1. 创建临时表
2. 拷贝数据
3. 新建索引

3. 生产上怎么做DDL

除了使用 PT 和 gh-ost 工具，还可以考虑使用主备来做，下面操作使用主备来做都挺好：

• 加索引
• optimize table
• varchar（20）-> varchar（100）（这个操作需要小心，主从切换后要等备库也执行完 DDL 后，主库才能对外继续服务）

4. 自制 online DDL——触发器

create table t1 like t;  
alter table t1 add index(c);

1.创建触发器  
insert -->insert into log_table("replace...")

delete --> insert into log_table("delete...")

update --> insert("delete"); insert into("replace") 

2. start ...

select * from t

insert into 

3. start ...

select aid, alter_log from log_table ; apply aler_log  
delete from log_tables where id =$aid

4.  repeat step 3
5.  lock table t; repeat step 3;

上述就是 PT 工具的执行逻辑，可以看到，PT 完全依赖触发器，MySQL 5.6 及之前版本，一个操作只能建一个触发器，比如 insert 只能建一个触发器，如果表上已有触发器，就不能使用 PT，可以考虑 binlog 版本的 gh-ost

5. 自制 online DDL——binlog

create table t1 like t;  
alter table t1 add index(c);

1. start ...  
select * from t insert into

2. apply binlog（此处 binlog 是需要重写的，比如表名这些需要修改）

3. repeat step 2

4. lock table t; repeat step 2;

上面是 gh-ost 的执行逻辑，可以看到，没有使用触发器，取而代之的是用 binlog 来实现增量数据的应用

binlog 如何收集呢？将自己伪装成从库，直接收目标表的 binlog，然后改写 binlog 即可，此处需要使用 row 格式，statement 可能有坑，比如表上有触发器，statement 只会记录原操作，触发器的操作就丢失了，row 格式则能保证数据完全一致

6. 自制 online DDL——物理复制

PT 和 gh-ost 都是 copy 算法，第一个拷贝阶段可能耗时过长，可以考虑使用物理拷贝的方式实现第一步，减少事务时间，这个办法是未经实证的，后续可以考虑去实现的，步骤如下：

// 1.创建空表结构副本
create table t1 like t;
// 2. 丢弃新表的表空间，删除 t1 的 .ibd 文件（InnoDB 数据文件），使其成为“无数据”状态
alter table t1 discard tablespace;
// 3. 锁定原表并准备导出，对表 t 加共享锁（只允许读，阻塞写）。
flush table t for export;
// 4. 复制数据文件，将原表 t 的 .ibd 文件复制到 t1 的数据文件位置。此处可能是远程复制
// cp t.ibd t1.ibd
// 5. 解锁
unlock tables;
// 6. 导入表空间，将 t1.ibd 文件与表结构关联，加载数据。
alter table t1 import tablespace;

6.1 注意事项

1. 结构一致性：
   t1 必须与 t 结构完全相同（列顺序、索引、约束等），否则导入失败。

2. 版本兼容性：

   • MySQL 5.6+ 支持 FLUSH TABLE ... FOR EXPORT。
   • 跨版本迁移需谨慎（如从 5.7 到 8.0 可能存在兼容问题）。

3. 锁释放：
   执行 FLUSH TABLE ... FOR EXPORT 后需在相同会话中执行 UNLOCK TABLES 释放锁（或等待会话结束）。

4. 文件完整性：
   确保复制的 .ibd 和 .cfg 文件未被修改。

6.2 总结

步骤	关键作用	风险点
CREATE TABLE ... LIKE	克隆结构	结构不一致导致后续失败
DISCARD TABLESPACE	清空目标表数据	操作后表不可读
FLUSH TABLE FOR EXPORT	锁定原表并生成元数据文件	长时间锁表影响业务
文件复制	快速迁移数据	文件权限或损坏导致失败
IMPORT TABLESPACE	关联数据文件与表结构	元数据校验不通过

此方法在 数据量大且结构稳定 时效率极高，但需严格遵循步骤并验证结果。

此外，可以发现，在 flush tables 阶段，会锁表只读，如果是一张非常大的表，由于物理网线等限制，还是需要一定时间才能 cp 完成的，这段时间内表是只读的，不能接受，那该怎么解决呢？

可以考虑使用备库，在备库上执行上述操作，这样只会锁备库表，造成的影响是主从延迟可能增加，但能接受

7. 答疑

1. 同一个表，加索引和删索引最好分成两个语句？

   是的，因为删索引很快，如果跟加索引放在一起，可能会导致索引删除耗时过长（加索引耗时过长），因此建议分开，还有如果需要修改索引，建议先加后删，比如索引 b 需要修改为 b,c 联合索引，应该先加 b,c 联合索引，然后再删除索引 b

2. 同一个表加两个索引，最好分成两个语句

   不对，一起执行可以减少扫描主键的消耗（只扫描一次），不要分成两个语句

3. add unique index，使用 copy 算法比使用 inplace 算法快

   不对，因为 copy 算法需要重新构建整张表，inplace 只需要新建一个索引即可，而且 copy 算法效果还不好，构建出来的只有主键索引是紧凑的，其他索引不是紧凑的

4. add unique index，使用 copy 算法比使用 inplace 算法更容易成功

   对的，因为 inplace 多了应用 alter_log 阶段，这个阶段相较于 copy 算法可能存在唯一键冲突

5. optimize table

6. 什么是数据字典 表结构，标有多少列，有哪些索引，列的定义、默认值等跟表结构相关的都是数据字典

   系统也有数据字典，比如系统有多少库，有多少张表等

   具象化可以理解为 show databases; show tables; show create table;这些语句返回的结果都是数据字典的一部分

7. replace into

   该语句强行插入数据，如果遇到有唯一键冲突的列，有多少条就删除多少条，删除完成后再插入

8. insert …… select 加哪些锁？

   insert into t1 select * from t limit 2，t1 加意向写锁，t 加意向读锁以及两条行锁

9. 意向锁有什么作用？

   在行级锁的基础上，先在表级别声明事务的意图（“我可能要对某些行加锁”），从而让其他事务快速判断是否能加表锁，无需逐行检查。因为 innodb 之前，MySQL 只支持表锁，判断能否添加表锁很简单，只需要看是否有 session 持有表锁即可，但是 innodb 支持行锁，此时判断能否加表锁，如果没有意向锁，只能一行行检查是否有表锁，需要扫描全表才能定，效率很低，因此 innodb 引入意向锁，就能简单解决该问题，增删改加 IX，查询加 IS，跟 MDL 一样完全由 MySQL 自动处理。

   作用	说明
   声明意图	表级意向锁表明事务可能对某些行加锁，避免其他事务盲目加表锁。
   提高并发效率	通过锁兼容矩阵减少锁冲突，避免逐行检查锁状态。
   协调多粒度锁	解决行锁和表锁的共存问题，是 InnoDB 多粒度锁机制的核心组件。

   意向锁的兼容性

   意向锁通过锁兼容矩阵减少冲突：

   锁类型	IS	IX	S	X
   IS	✅	✅	✅	❌
   IX	✅	✅	❌	❌
   S	✅	❌	✅	❌
   X	❌	❌	❌	❌

   • ✅ 兼容：允许同时持有。
   • ❌ 冲突：需等待对方释放锁。

   关键规则：

   • 表级锁（S/X） 会与意向锁（IS/IX）冲突：

     • 表级 S 锁与 IX 锁冲突（因为 IX 表示有事务可能修改行）。
     • 表级 X 锁与 IS/IX 锁均冲突。

   • 意向锁之间兼容：
     IS 和 IX 可以共存（多个事务可同时对不同行加行锁）。