MySQL InnoDB 表空间回收：原理、空洞与高效优化实践

第一时间捕获有价值的信号

本文深入解析 MySQL InnoDB 表空间无法回收的原理，揭示文件空洞的形成机制。详细介绍通过重建表、Online DDL 等方法高效回收空间，优化数据库性能与存储效率。告别无效删除，掌握正确的 MySQL 空间优化技巧。

核心内容

这篇旨在说明为什么简单地删除表数据达不到表空间回收的效果，然后再介绍正确回收空间的方法。

InnoDB的物理存储

InnoDB表的组成：表结构（frm）+数据（ibd）
- MySQL 8.0开始，允许将表结构定义（占用空间很小）放在系统数据表中
控制参数innodb_file_per_table
- ON：每个InnoDB表数据存储在一个以.ibd为后缀的文件中，更容易管理，DROP TABLE会直接删除这个文件
- OFF：InnoDB表数据存储在共享表空间， DROP TABLE空间也是不会回收的

mysql> SHOW VARIABLES LIKE '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

文件空洞

空洞：可以被复用但没有被使用的空间，经过大量增删改的表，都会存在空洞

数据删除流程

mysql-reclaim-space-1

如果删掉R4，InnoDB只会将R4标记为删除，如果再插入300~600的记录时，可能会复用这个位置，但磁盘文件不会缩小。记录的复用，仅限于符合范围条件的数据
如果删除了一个数据页上的所有记录，那么整个数据页都可以被复用的
- 当整个页从B+树里摘除后，可以被复用到任何位置
- 如果将page A上的所有记录删除后，page A会被标记为可复用
- 当插入ID=50的记录时，需要申请新页时page A可以被复用
如果相邻的两个数据页利用率都很小，系统会把这两个数据页上的数据合并到其中一个页上，另一个数据页就会被标记为可复用
如果通过DELETE命令删除整个表，那么所有的数据页都会被标记为可复用，但磁盘上的文件同样不会变小
TRUNCATE = DROP + CREATE

插入

mysql-reclaim-space-2

如果数据是随机插入的，就有可能造成索引的数据页分裂
page A已满，如果再插入ID=550的数据，就必须申请一个新的页面page B来保存数据，导致页分裂，留下了空洞

更新

更新索引上的值，等同于先逻辑删除旧值后再插入新值，同样也会造成空洞

重建表

逻辑过程：

新建一个与表A结构相同的表B
按照主键递增的顺序，把表A中的数据一行一行读出，然后再插入表B，表B的主键索引更紧凑，数据页的利用率也更高
表B作为临时表，数据从表A导入到表B，然后用表B替换A

重建命令

# ALTER TABLE 默认会提交前面的事务
ALTER TABLE A ENGINE=InnoDB;

mysql-reclaim-space-3

Online DDL，重建表的流程：

建立一个临时文件（InnoDB内部），扫描表A主键的所有数据页
用数据页中表A的记录生成B+树，存储到临时文件
state 2（日志）：生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中
state 3（重放）：临时文件生成后，将日志文件的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件
用最新的临时文件替换表A的数据文件

MDL锁

ALTER语句在启动时需要获取MDL写锁，但会在真正拷贝数据之前退化为MDL读锁，MDL读锁不会阻塞其他线程对这个表的DML，同时又能阻塞其他线程对这个表的DDL
对一个大表来说，Online DDL最耗时的过程是拷贝数据到临时表的过程，期间是可以接受DML，相对于整个DDL过程来说，锁的时间非常短，对业务来说，可以认为是Online

性能消耗

重建表会扫描原表数据和构建临时文件（或临时表）
对于大表来说，重建表会非常消耗IO和CPU资源
推荐工具：gh-ost

Online 与 Inplace

tmp_table是一个临时表，在Server层创建的
tmp_file是临时文件，在InnoDB内部创建的，整个DDL过程都在InnoDB内部完成，对于Server层来说，并没有把数据挪动到临时表，是个原地操作（Inplace）
DDL过程如果是Online的，那一定是Inplace的，反之不成立，ALTER TABLE t ADD FULLTEXT(field_name);是Inplace的，但会阻塞DML（非Online）

ALTER TABLE A ENGINE=InnoDB;
等同于
mysql> ALTER TABLE t ENGINE=InnoDB, ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

与Inplace对应的是Copy，强制拷贝表到Server层

mysql> ALTER TABLE t ENGINE=InnoDB, ALGORITHM=COPY;
Query OK, 100000 rows affected (1.46 sec)
Records: 100000  Duplicates: 0  Warnings: 0

ALTER + ANALYZE + OPTIMIZE

ALTER TABLE t ENGINE=InnoDB：重建表
ANALYZE TABLE t：触发表索引信息的重新采样统计
OPTIMIZE TABLE t：ALTER + ANALYZE

问题思考

假设现在有人碰到了一个“想要收缩表空间，结果适得其反”的情况，看上去是这样的：一个表t文件大小为1TB；对这个表执行 alter table t engine=InnoDB；发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了1.01TB。可能是什么原因呢？

在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。将表t重建一次，插入一部分数据，但是插入的这些数据本来用掉了一部分的预留空间；这种情况下，再重建一次表t，就可能会出现问题中的现象。