【MySQL】改表是否会锁表？

haifun

2024-04-07

数据库

MySQL

改表是否会锁表，取决于使用的改表方案，不同的改表方案通常会有不同的锁表时长。下面将分别介绍 MySQL 原生 Online-DDL 以及开源第三方工具 gh-ost 两种改表方案。

Online-DDL

Online-DDL算法：

在执行 DDL 操作时，不管何种算法，都会经历准备（prepare）、执行（DDL）、提交（commit）三个阶段。不同之处是在三个阶段中分别作了不同的处理。

准备阶段：
1. 对表加元数据共享锁，读取 .frm 元数据；（此时 DDL 不能并行，DML 可以并行）
2. 共享锁升级为排他锁；（此时 DDL、DML 都不能并行）
3. 在 server 层通过 create like 语句，创建临时表，engine 层也生成对应 .ibd、.frm 文件；（8.0 之后没有 .frm 文件）
执行阶段：
1. 修改临时表元数据；（加列）
2. 拷贝原表数据到临时表；（最耗时，按行 copy）
3. 删除原表及文件；
4. 重命名临时表及文件；
提交阶段：
1. 提交事务，释放锁；

COPY算法整个流程期间都持有锁，既不能写入数据也不能查询数据，容易造成数据库堵塞，因此并不实用。

准备阶段：
1. 对表加元数据共享锁，并升级为排他锁；（此时 DML 不能并行）
2. 判断是否使用 inplace 算法，判断语句是 rebuild table 还是 no-rebuild，rebuild 在原表所在的路径下创建 .frm 和 .ibd 临时中转文件；（在 engine 层克隆，而不是像 copy 那样，在 server 层创建（create like））
3. 申请 row log 空间，用于存放 DDL 执行阶段产生的 DML 操作；（no-rebuild不需要）
执行阶段：
1. 释放排他锁，保留元数据共享锁；（此时 DML 可以并行）
2. 扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中；（在引擎层扫描，最耗时）
3. 将所有对原表的 DML 操作记录在日志文件 row log 中并回放；
提交阶段：
1. 升级元数据共享锁，产生排他锁锁表；（此时 DML 不能并行）
2. 重做 row log 中的内容；（no-rebuild 不需要）
3. 重命名原表文件，将临时文件改名为原表文件名，删除原表文件；
4. 提交事务，变更完成；

INPLACE 算法无需拷贝全表数据到新表，但还是需要 IN-PLACE 方式（原地，无需生成新的临时表）重建整表。这种情况下，在 DDL 的初始准备和最后结束两个阶段时需要加排他 DML锁（metadata lock，元数据锁），除外，DDL 期间不会阻塞 DML。

虽然准备、提交两个阶段都无法进行 CRUD，但实际上，整个 DDL 过程执行阶段时间占比最长，对于业务层来说，绝大部分时间可以保证正常访问。

只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他 DML 锁，原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的，也不会阻塞 DML。

这个新特性是8.0.12引入的（腾讯DBA团队贡献），目前只支持添加列等少量操作。

gh-ost 是 GitHub 开源的一款 MySQL 在线 DDL 改表工具。它的主要优点包括：

gh-ost 工具基于中间表增量迁移数据 + rename 表替换完成改表动作。通过创建一个与原表结构相同的_ghost_表，然后将原表的数据逐行复制到_ghost_表中，同时捕获并复制原表上的数据变更。当数据复制完成后，将_ghost_表重命名为原表，完成改表操作。

其中耗时主要发生在数据迁移阶段，该阶段不锁表，rename 表替换阶段会有短暂锁表，但通常为秒级。

改表操作是动态执行的，耗时与集群负载/主从延迟/业务写入量/网络等多种外部因素息息相关，目前没有精确的改表速度评估方式。经验评估方式有2种：

（仅供参考，实际执行速度可能会偏差较大。建议预留充足时间，避免影响业务进度。）

通常情况下，我们期望 RDS 改表锁表时间为秒级，同时通过避开高峰期执行，从而减少对业务产生的影响。

改表方案选择：