小火箭

MySQL DDL

13 December 2020

MySQL 表结构信息存储

InnoDB 表和索引可以创建在系统表空间或者独立表空间（innodb_file_per_table=on）。对于 InnoDB 表，MySQL 会将表的数据字典信息存储在 .frm 文件中，同时也会存储入口信息（数据库名/表名）到系统表空间，在 INFORMATION_SCHEMA.INNODB_SYS_* 系统表中能够查询到表结构信息；如果 innodb_file_per_table=on，MySQL 会将表的数据存储在 .ibd 文件中，否则存储在系统表空间中。

MySQL Metadata Locking

MySQL 采用 MDL 来管理对数据库对象的并发访问和确保数据的一致性。MDL 有多种类型，主要分为两大类：共享锁和排他锁。对于 DML 需要获取共享锁类型的 MDL，因为 DML 不修改表元数据信息。而 DDL 需要先申请排他锁类型的 MDL（确保没有多个 DDL 同时在修改表元数据信息），然后降级为共享锁，开始拷贝数据（DDL COPY 算法此时允许其他会话读，不允许写；DDL INPLACE 算法大部分情况下允许其他会话读写），当拷贝完数据后，需要升级为排他锁，交换表（此时不允许其他会话读写）。

关于 MDL 的详细内容参考文章：

Online DDL

Online DDL 指在执行 DDL 期间允许对表执行 DML 或者仅修改元数据信息。DDL 时使用的 COPY 算法是非 Online 的，INPLACE 算法在大部分情况下是 Online 的。我们可以通过查看 MySQL 的官方文档了解不同的 DDL 对于 Online 的支持情况：Online DDL Operations。

COPY 算法

INPLACE 算法

Online DDL 存在的问题：

  • 执行 DDL 会增加数据库压力，无法控制 MySQL DDL 过程的资源占用，无法暂停
  • 增加主从延迟

对于这两个问题可以通过第三方表结构变更工具来缓解。

第三方表结构变更工具

pt-online-schema-change
  1. 对表做检查：
    1. 是否有触发器
    2. 是否有外键约束，支持外键：How pt-online-schema-change Handles Foreign Keys
    3. 是否有主键或唯一索引
    4. 主从复制是否设置了 replication filters，避免变更的时候导致主从复制失败
  2. 创建一张与旧表相同结构的新表，执行表结构变更
  3. 在旧表上创建删除、更新、插入触发器，变更会应用到新表上
    1. INSERT 语句会被替换为 REPLACE 语句
  4. 按块拷贝数据到新表 insert ignore select * from table lock in share mode，拷贝过程中加共享锁
  5. RENAME TABLE t TO old_table_del, new_table TO t;

资源控制方式：通过监控数据库负载、主从复制延迟，调控拷贝的数据块大小实现，但触发器会一直在执行。

gh-ost
  1. 对表做检查：
    1. 是否有触发器
    2. 是否有外键，不支持外键
    3. 是否有主键或唯一索引
  2. 创建一张与旧表相同结构的新表，执行表结构变更
  3. 连接到某台数据库上，扮演从库接收 binlog
  4. 创建 changelog 表，并注入”good to go”的记录
  5. 开始监听原表 DML 的 binlog 事件
  6. 按块拷贝数据到新表 insert ignore select * from table lock in share mode，拷贝过程中加共享锁
  7. 当数据迁移与 binlog 重放完成后，将会在 changelog 表上注入”copy all done”的记录
  8. 通过 cut-over 对表进行切换

资源控制方式：通过 changelog 表中插入流控信息实时进行调控，可随时停止拷贝数据块和应用变更。

无触发器设计：Triggerless design

cur-over 实现：Describing safe, blocking, atomic, pure-mysql cut-over phase

外键说明：Thoughts on Foreign Keys?

Fb online-schema-change
  1. 检查
    1. 是否有触发器
    2. 是否有主键或唯一索引
    3. 是否有外键约束，不支持外键
  2. 创建一张与旧表相同结构的新表，执行表结构变更
  3. 创建 changelog 表
  4. 在旧表上创建删除、更新、插入触发器，变更会记录到 changelog 表中
    1. INSERT 语句会被替换为 REPLACE 语句
  5. select into outfile; load data infie; 拷贝旧表数据到新表，避免间隙锁
  6. 应用变更记录
  7. 校验新表数据是否正确
  8. 通过 cut-over 对表进行切换
    1. 没有像 gh-ost 那样做到原子切换

快速加列

MySQL 8.0 Online DDL 新增了 instant 算法，使得添加列时不再需要 rebuild 整个表，只需要在表的 metadata 中记录新增列的基本信息即可。

不同表结构变更方式对比

Battle of the Online Schema Change Methods