为什么 DELETE 大量数据后，表文件大小不会变？(redo / undo / 数据页空洞)

考察要点

这道题面试官想看你是否深刻理解 InnoDB 存储引擎的空间管理机制和 MVCC (多版本并发控制)原理。核心在于区分逻辑删除与物理删除，并了解 DELETE 操作在事务、数据页、日志（Undo/Redo）层面的完整链路。高水平的回答应能进一步引申出如何真正回收磁盘空间，并给出不同场景下的最佳实践。

核心概念

DELETE 大量数据后，表文件（.ibd 文件，在 innodb_file_per_table=ON 的情况下）大小通常不会改变，其根本原因是 InnoDB 执行的是逻辑删除 (Logical Delete)，而非立即释放空间的物理删除 (Physical Delete)。

1. DELETE 操作的内部流程

当执行一个 DELETE 语句时，InnoDB 内部会发生以下事情：

1. 事务与 MVCC：DELETE 操作被包裹在一个事务中。为了支持事务回滚和一致性读（MVCC），原始的行记录不会被立即物理删除。相反，它会被记录到 Undo Log 中。其他事务如果需要读取这个旧版本的数据，可以从 Undo Log 中获取。

2. 数据页中的逻辑删除：在数据页（Data Page）层面，被删除的记录只是被“标记”为已删除。具体来说：

   • 记录头信息（Record Header）中的 delete_mask 标志位会被设置为 1。
   • 这条被标记为删除的记录会被加入到数据页的“垃圾链表”（Garbage List）中。
   • 此时，记录本身仍然占据着物理空间。

   图示：InnoDB 数据页中记录的变化

   mermaid

   ⧉⛶

    1graph TD
    2    subgraph 数据页 (Before DELETE)
    3        A[Record 1] --> B(Record 2)
    4        B --> C(Record 3)
    5    end
    6
    7    subgraph 数据页 (After DELETE Record 2)
    8        A_after[Record 1] --> C_after(Record 3)
    9        Deleted_B(Record 2 <br> delete_mask=1)
   10        style Deleted_B fill:#f9f,stroke:#333,stroke-width:2px,stroke-dasharray: 5 5
   11        FreeList --> Deleted_B
   12    end
   13
   14    linkStyle 1 stroke-width:0px;
   15    linkStyle 3 stroke-width:0px;

   如图所示，Record 2 被删除后，它在主键索引的链表结构中被移除，但其占用的空间被链接到了页内的空闲空间链表，等待后续重用。

3. 空间重用机制：这些被标记为删除、但仍占据空间的位置，被称为“数据空洞”。InnoDB 会优先重用这些空洞。当有新的 INSERT 操作时，如果新记录的大小可以放入这些空洞，InnoDB 就会直接使用这些空间，而无需申请新的数据页。只有当整个数据页的空闲空间多到一定程度（默认是页空间的 50%，由 MERGE_THRESHOLD 控制），并且有机会和相邻页合并时，这个数据页才可能被完全释放，但这个过程是被动且不保证立即发生的。

4. Redo Log 的角色：DELETE 操作作为一个数据修改操作，其产生的变更（如修改数据页、Undo 页等）会以物理日志的形式记录到 Redo Log 中，用于保证事务的持久性。万一数据库在变更刷盘前宕机，重启后可以通过 Redo Log 来恢复数据。

总结：DELETE 操作本质上是将数据标记为“可复用”，而不是“已释放”。整个表文件就像一个大的预分配空间，其大小由历史达到的“高水位线”（High-Water Mark）决定，DELETE 只是在内部制造了一些“气泡”，并不会降低水位。

常见低分回答

1. 回答模糊，只说“为了性能”：这是一个非常表面的回答。面试官想听到的是“为了什么性能？具体机制是什么？”。不解释 MVCC、Undo Log 和数据页重用机制，就无法体现深度。
2. 混淆 DELETE 和 TRUNCATE：错误地认为 DELETE 应该和 TRUNCATE 一样释放空间。TRUNCATE 是 DDL，它通过直接删除并重建表定义的方式快速清空数据，重置高水位线，因此能立刻释放空间，但它不支持事务回滚。
3. “因为有 Undo Log，所以空间不释放”：这个回答只对了一半。Undo Log 解释了为什么旧数据不能立即消失（为了 MVCC 和回滚），但没有解释为什么在事务提交后，空间仍然不被操作系统回收。核心在于 InnoDB 的空间重用策略。
4. 忽略 innodb_file_per_table 的影响：默认情况下（innodb_file_per_table=ON），每张表一个 .ibd 文件。如果该参数为 OFF，所有表数据都存放在共享表空间 ibdata1 中。在这种情况下，DELETE 造成的空洞会留在 ibdata1 中，而 ibdata1 文件几乎不可能收缩，优化起来更为复杂和危险。

工程实践与优化

既然 DELETE 不会主动释放空间，那么在生产环境中，当我们确实需要回收因大量删除而产生的磁盘空间时，应该怎么做？

1. OPTIMIZE TABLE

   • 原理：对于 InnoDB，OPTIMIZE TABLE tbl_name 实际上会映射到 ALTER TABLE tbl_name ENGINE=InnoDB，这是一个“重建表”的操作。MySQL 会创建一个临时表，将原表中的有效数据（未被逻辑删除的）拷贝到新表，然后删除原表，最后将临时表重命名为原表名。这个过程会消除数据空洞，重建所有索引，从而使表文件收缩到最小。
   • SQL 示例：
     sql

     ⧉⛶

      1OPTIMIZE TABLE your_large_table;

   • 缺点：在 MySQL 5.7 及之前版本，这个操作会锁住全表，对于线上大表是灾难性的。MySQL 8.0 对此有所改进（ALGORITHM=INPLACE），但仍然可能涉及大量 I/O 和 CPU 消耗，并可能在元数据锁阶段造成短暂的阻塞。

2. pt-online-schema-change (Percona Toolkit)

   • 原理：这是业界标准的在线 DDL 工具。它通过创建一个“影子表”，在原表上创建触发器（INSERT/UPDATE/DELETE），将 DDL 期间的增量数据变更同步到影子表。同时，它会分块（chunk-by-chunk）地将原表数据拷贝到影子表。拷贝完成后，通过一个原子性的 RENAME TABLE 操作完成切换。
   • 优点：整个过程几乎不锁表，对线上业务影响极小。
   • 用法示例（模拟重建）：
     bash

     ⧉⛶

      1pt-online-schema-change --alter "ENGINE=InnoDB" D=your_db,t=your_large_table --execute

3. 分区表（Partitioning）

   • 场景：对于按时间增长的数据（如日志、订单），这是最佳策略。例如，可以按月或按天对表进行分区。
   • 原理：当需要删除旧数据时，不再使用 DELETE，而是直接删除对应的分区。ALTER TABLE tbl_name DROP PARTITION p202301;
   • 优点：DROP PARTITION 是一个近乎瞬时的元数据操作，它直接删除对应的分区文件，快速且高效，不会产生大量 Undo Log，对系统负载极小。

4. TRUNCATE TABLE

   • 场景：如果需要删除表中的所有数据。
   • 优点：速度极快，立即释放空间，重置 AUTO_INCREMENT 计数器。
   • 缺点：DDL 操作，隐式提交，无法回滚，不会触发 DELETE 触发器。

常见追问与延伸题目

1. 追问：DELETE、TRUNCATE 和 DROP 有什么区别？
   • 回答要点：可以从以下几个维度进行对比：

2. 追问：如果我的 MySQL 实例设置的是 innodb_file_per_table=OFF，OPTIMIZE TABLE 还能收缩空间吗？

   • 回答要点：不能。当 innodb_file_per_table=OFF 时，所有表和索引都存储在共享表空间（如 ibdata1）中。OPTIMIZE TABLE 可以在共享表空间内部整理数据，消除空洞，但这些被整理出来的空闲空间只能被 InnoDB 内部的其他表使用，而不能将空间返还给操作系统。ibdata1 文件的大小一旦增长，就几乎无法收缩。唯一收缩 ibdata1 的方法是，使用 mysqldump 逻辑备份所有数据库，关闭 MySQL，删除 ibdata1 文件，重启 MySQL（它会重建一个小的 ibdata1），然后恢复数据。这是一个非常重量级且需要停机的操作，因此生产环境强烈推荐使用 innodb_file_per_table=ON。

3. 追问：DELETE 一条记录会产生多少 Undo Log 和 Redo Log？

   • 回答要点：这是一个深入理解日志系统的问题。
     • Undo Log：DELETE 操作会产生一条 TRX_UNDO_DEL_MARK_REC 类型的 Undo Log。这条日志包含了被删除记录的主键和所有列的旧值，以便在需要时进行回滚或提供给其他事务进行一致性读。
     • Redo Log：DELETE 产生的 Redo Log 至少包含以下内容：
       1. 对 Undo Page 进行修改的 Redo Log（写入 Undo Log 本身也是一个需要持久化的操作）。
       2. 对数据页中记录头 delete_mask 标志位进行修改的 Redo Log。
       3. 可能还包括对页内链表指针、页目录等数据结构修改的 Redo Log。
     • 总结：DELETE 操作既会写 Undo Log（为了逻辑回滚），也会写 Redo Log（为了物理持久化）。Undo Log 的内容是逻辑的（行的前镜像），而 Redo Log 的内容是物理的（数据页某个偏移量的字节变化）。