MySQL online dll最佳实践的思考

2025/06/13

注：以下所有讨论基于InnoDB存储引擎下。

MySQL的表结构和数据是怎么存储的？

想要知道DDL为什么这么痛苦，就得先知道MySQL的表结构和数据是怎么存储的，在DDL过程里面，修改了什么东西。

MySQL 5.7 及之前

存储了什么？

1. 表结构的存储:
   存储在一个与表同名的 .frm 文件中。例如，创建一张名为 users 的表，在数据库目录下就会有一个 users.frm 文件。
   这个文件由MySQL Server层管理，记录了表的列定义、数据类型、字符集等静态元数据。

2. 数据和索引 (实体建筑) 的存储:
   当 innodb_file_per_table 开启时，数据和索引被存储在一个与表同名的 .ibd 文件中（例如 users.ibd）。
   这个文件内部包含了表的所有数据行和所有二级索引。

修改了什么？

当执行一个需要重建表（例如修改列类型）的 ALTER TABLE 操作时,实际是在做这两个操作。

1. 修改frm：MySQL需要根据你的指令，生成一份新的 .frm 文件。
2. 重构数据：MySQL会创建一个全新的临时 .ibd 文件，然后把旧 .ibd 文件里的数据逐行读出，按照新结构写入新文件。

MySQL 8.0 及之后

为什么执行ddl这么痛苦？

在MySQL的远古版本当中，ddl语句是通过COPY algorithm来执行的，这个过程主要分为5个步骤。

1. 获取排他元数据锁。
2. 创建一个新的临时表。
3. 把旧表中的数据拷贝到临时表中。
4. 切表（cut-over）操作。
5. 释放排他元数据锁，删除旧表。

为什么痛苦：

1. frm的修改和idb的修改是两个不同的过程，如果这个过程里面服务器崩了，ddl的原子性得不到保障。
2. 假如数据量很大重建过程很久。