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

数据库占用空间太大，把一个最大的表删掉了一半的数据，怎么表文件的大小还是没变？

一个InnoDB表包含两部分，即：表结构定义和数据。在MySQL 8.0版本以前，表结构是存在.frm为后缀的文件里。而MySQL 8.0版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。

参数innodb_file_per_table

该参数控制表数据是存在共享表空间还是单独的文件。

1.OFF 表数据放在系统共享空间，也就是跟数据字典放在一起；

2.ON 每个InnoDB表数据存储在一个以.ibd为后缀的文件中。

从MySQL5.6.6版本开始，这个参数的默认值就是ON了。

建议不论使用MySQL的哪个版本，都将这个值设置成ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使删除了表，空间也是不会回收的。

删除整个表的时候，可以通过drop table命令回收表空间。但是遇到更多的是删除某些行的场景。就是开头提到的问题。

 

数据删除流程

如果删除掉某一行，InnoDB会将这一行的位置标记为删除，后期插入可以复用，但是磁盘文件的大小并不会缩小。

如果删除掉一个数据页上的所有记录，结果是整个数据页就可以被复用了。

但是需要注意的是，数据页的复用跟记录的复用是不同的

记录的复用，只限于符合范围条件的数据。而整个页从B+树里面摘掉以后，可以复用到任何位置。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合并到其中一个页上，另外一个数据页就被标记为可复用。

进一步的，如果用delete命令把真个表的数据都删除，结果就是，所有的数据页都会被标记为可复用。但是磁盘上的文件不会变小。

也就是说delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

 

实际上，不止是删除数据会造成空洞，插入数据页会。

如果数据是按照顺序插入的，那么索引是紧凑的，但是如果数据是随机插入的，就可能造成索引的数据页分裂。而数据页分裂的过程中就可能造成空洞。

另外，更新索引上的值，可以理解为删除一个旧的值，在插入一个新值，不难理解，这也是会造成空洞的。

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

而重建表，就可以达到这样的目的。

 

重建表

按照主键递增的顺序，把表A的数据一行一行地从表A里读出来插入到表B中。显然，表B的主键索引更加紧凑，数据页的利用率也更高。

可以使用alter table A engine=Innodb命令来重建表。在MySQL 5.5版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表B不需要你自己创建，MySQL会自动完成转存数据、交换表明、删除旧表的操作。

如果这个过程中，有新的的数据要写入到表A的话，就会造成数据丢失。因此，在整个DDL过程中，表A不能有更新。也就是说，这个DDL不是Online的。

MySQL5.6版本开始引入的Online DDL，对这个操作流程做了优化。

重建表的流程如下：

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

问题：DDL之前是要拿MDL写锁的，这样还能叫Online DDL吗？

答：在上图的流程中，alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前留退化成读锁了。为什么要退化呢？为了实现Online，MDL读锁不会阻塞增删改操作。为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做DDL。

如果线上数据量大的话，推荐使用GitHub开源的gh-ost来做。

 

Online和inplace

上面第一幅图，把表A中的数据导出来的存放位置叫做tmp_table。这是一个临时表，是在Server层创建的。

第二幅图中，根据表A创建出来的数据是放在“tmp_file”里的，这个临时文件是在InnoDB内部创建出来的。整个DDL过程在InnoDB内部完成。对于server层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的由来。

重建表语句alter table t engine=innodb,其实隐含的意思是：

alter table t engine=innodb,algorithm=inplace;

跟inplace对应的就是拷贝表的方式了，用法是：

alter table t engine=innodb,algorithm=copy;

当你使用algorithm=copy的时候，表示的是强制拷贝表，对应的流程就是第一幅图的操作过程。

这样看起来，inplace跟Online是不是一个意思？其实不是，只是在重建表这个逻辑中刚好是这样而已。

比如，如果要给InnoDB表的一个字段加全文索引，写法是：

alter table t add fulltext(field_name);

这个过程是inplace的，但是会阻塞增删改操作，是非Online的。

如果说这两个逻辑之间的关系是什么的话，可以概括为：

1. DDL过程如果是Online的，就一定是inplace的；
2. 反过来未必，也就是说inplace的DDL，有可能不是Online的。截止到MySQL8.0，添加全文索引（FILLTEXT index）和空间索引（SPATIAL index）就属于这种情况。

延伸：

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

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

 

上一篇：12 | 为什么我的MySQL会“抖”一下？

下一篇：14 | count(*)这么慢，我该怎么办？