MySQL探秘：为何删除半数表数据后文件大小依旧

文章标题：

MySQL探索：删除半数表数据后文件大小未变的缘由

文章内容：

一个InnoDB表包含两部分：表的结构定义以及数据。在MySQL 8.0版本之前，表结构定义存于后缀为.frm的文件中。后续版本允许将表结构定义放置在系统数据表内。由于表结构定义所占空间极小，所以重点探讨表数据部分。

首先阐述为何简单删除表数据无法达成表空间回收效果，接着介绍正确回收空间的办法。

参数innodb_file_per_table

表数据既可存储在共享表空间中，也能是独立文件，这由参数innodb_file_per_table控制：
- 设为OFF时，表数据存于系统共享表空间，即与数据字典存放一处；
- 设为ON时，每个InnoDB表的数据存储在一个后缀为.ibd的文件里。

从MySQL 5.6.6版本起，该参数默认值为ON。建议使用ON，因为一个表单独存储成文件更便于管理，且不需要该表时通过drop table命令可直接删除文件；若存于共享表空间，即便表被删除，空间也不会回收。后续讨论基于innodb_file_per_table=ON的设置。

删除整张表时可使用drop table命令回收表空间，但日常更多场景是删除某些行。

数据删除流程

为明晰删除部分行的场景，需从数据删除流程说起。

看InnoDB中一个索引的示意图：

假设要删除R4记录，InnoDB仅会标记R4为删除。若后续插入ID在300 - 600间的记录，可能复用该位置，但磁盘文件大小不变。

若一个数据页的所有记录均被删除，会怎样？答案是整个数据页可被复用。

不过数据页复用与记录复用不同。记录复用限于符合范围条件的数据，而数据页可复用时，所有范围数据均可使用。例如上述索引中，若page A可复用，ID = 50的记录也能使用该页。

若相邻两个数据页利用率低，系统会将两页数据合并至其中一页，另一页标记为可复用。

进一步讲，用delete命令删除整个表数据时，所有数据页均标记为可复用，但磁盘文件不会变小。即delete命令无法回收表空间，这些可复用却未使用的空间形似“空洞”。

实际不仅删除数据会造成空洞，插入数据也会。若数据插入随机，可能致索引数据页分裂。如上述索引中，假设page A已满，插入ID = 550的数据时：

page A已满时插入数据，需申请新页面page B存数据。因页分裂致部分数据移动，page A出现空洞。

除插入外，更新可视为删除 + 插入，也可能造成空洞。即增删改均可能产生空洞。故消除这些空洞可收缩表空间。

重建表

假设有表A，欲去除空洞，有何办法？

可新建与表A结构相同的表B，按主键ID递增顺序，逐行从表A读取数据插入表B。因表B为新建，无表A的空洞。将表B作临时表，数据从表A导入表B后，用表B替换表A，表A即无空洞。

可用alter table A engine=InnoDB命令重建表。MySQL 5.5之前版本，执行流程与上述相近，区别是无需自建临时表，MySQL自动完成转存数据、交换表名、删除旧表操作。

往临时表插入数据时，若表A有新数据写入会致数据损失，故整个DDL过程表A不能更新，即DDL非Online。

MySQL 5.6起引入Online DDL优化流程：
- 建立临时文件；
- 扫描表A主键所有数据页，用记录生成B+树存于临时文件；
- 生成临时文件时，将对A的操作记录于日志文件（row log），对应图中state 2状态；
- 临时文件生成后，将日志文件操作应用于临时文件，得与表A逻辑数据相同的临时文件；
- 用临时文件替换表A。

该流程因日志文件与重放操作，重建表时允许表A增删改。

因对表改动有MDL锁，alter语句启动时获MDL写锁，拷贝数据前退化为读锁，禁其他线程同时做DDL，不阻塞增删改。

大表时，Online DDL最耗时为拷贝数据至临时表，写锁锁住时间短，可视为Online。

需知，上述重建方法扫描原表数据并构建临时文件，大表操作耗IO和CPU资源。线上服务控时推荐用开源gh-ost。

Online和inplace

说到Online，提易混淆概念inplace。

早版本重建表时，表A数据导出存放于tmp_table，由Server层创建。

后续版本，表A重建数据存于tmp_file（见前图），由InnoDB内部创建。因DDL在InnoDB内部完成，Server层无数据挪至临时表，为“原地”操作，称inplace。

若表大小1TB，磁盘空间1.2TB，能否做inplace DDL？不行，因tmp_file占临时空间。

重建表完整语句：

alter table t engine=innodb,ALGORITHM=inplace;
alter table t engine=innodb,ALGORITHM=copy;

其中，copy表强制拷贝用临时表，inplace表用临时文件。

inplace是否即Online？非也，仅重建表逻辑如此。

两者关系：
- DDL为Online则必为inplace；
- 反之不然，inplace DDL不一定是Online。MySQL 8.0前，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 属此情况。如给InnoDB表字段加全文索引，过程inplace但阻塞增删改。