MySQL11- 为什么表数据删掉一半，表文件大小不变？

为何删除数据文件大小不变解答

一个InnoDB表包含两部分， 即： 表结构定义和数据。 在MySQL 8.0版本以前， 表结构是存在以.frm为后缀的文件里。 而MySQL 8.0版本， 则已经允许把表结构定义放在系统数据表中了。

接下来， 先说明为什么简单地删除表数据达不到表空间回收的效果， 然后再和你介绍正确回收空间的方法。

表数据既可以存在共享表空间里， 也可以是单独的文件。 这个行为是由参数innodb_file_per_table控制的：

1. 这个参数设置为OFF表示的是， 表的数据放在系统共享表空间， 也就是跟数据字典放在一起；
2. 这个参数设置为ON表示的是， 每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。

从MySQL 5.6.6版本开始， 它的默认值就是ON了，也就是每个InnoDB表数据存储在一个单独的.ibd文件中，执行drop table命令会直接删除掉表同时回收空间。

以下内容基于innodb_file_per_table on展开。

假设要删除R4，innodb只会标记R4删除。如果之后插入一个ID在300和600之间的记录时，可能会复用（所谓复用就是该空间的数据只会标记为B4删除，当后面如果有数据想要插入，就能够插入到这个位置）该位置。如果删掉整页，整个数据页可以被复用。所以磁盘文件大小不会缩小。

delete命令其实只是把记录的位置， 或者数据页标记为了“可复用”， 但磁盘文件的大小是不会变的。 也就是说， 通过delete命令是不能回收表空间的。 这些可以复用， 而没有被使用的空间， 看起来就像是空洞

插入数据也会产生空洞，如果按索引递增插入，那么索引是紧凑的（插入的数据根据递归顺序插入到空洞处）。如果数据插入随机（标记为可复用的记录位置不在使用，而是直接插入到二叉树的后面，导致空洞位置无法利用），当某页满时，再插入数据，就会申请一个新页，将旧页的部分数据保存到新页中。所以旧页中可能有空洞。

也就是说， 经过大量增删改的表， 都是可能是存在空洞的。 所以， 如果能够把这些空洞去掉， 就能达到收缩表空间的目的。而重建表， 就可以达到这样的目的。

解决删除记录产生的空洞问题—重建表

重建表解决空洞问题的原理
假设表A有空洞，那么我们需要新建一个与表A结构相同的临时表B，然后按照主键ID递归的顺序把A中的数据读到表B中，然后将表B替换成表A从而达到压缩表A空间的作用。

有什么命令可以重建表？
以使用alter table A engine=InnoDB命令来重建表。临时表B不需要你自己创建， MySQL会自动完成转存数据、 交换表名、 删除旧表的操作。

重建表的缺点
重建表的过程中显然花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到哦表A的话，就会造成数据丢失，因此在整个DDL过程中，表A不能更新。也就是说DDL不能Online

MySQL5.6引入Online DDL

我给你简单描述一下引入了Online DDL之后， 重建表的流程：

1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中state3 的状态
5. 用临时文件替换表 A 的数据文件。

重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。
如果是线上服务，要控制操作时间。如果想要比较安全的操作，推荐使用github开源的gh-ost。

optimize table、analyze table和 alter table 这三种方式重建表的区别。

• 从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是重建表）默认是上图的流程；
• analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
• optimize table t 等于 recreate+analyze。