为什么简单地删除表数据达不到空间回收的效果 ?

一个InnoDB表包含两部分:表结构定义数据

在MySQL8.0前,表结构是存在**.frm**为后缀的文件中;而8.0版本中,允许把表结构定义在系统数据表中,因为它占用空间很小。就定义一下结构嘛,这不是问题,重点是表数据!

参数innodb_file_per_table

它是定义表数据存放在哪的。

  • OFF:表数据放在系统共享表空间,也就是和数据字典放一起
  • ON:每个InnoDB表数据存储在一个以**.idb**为后缀的文件中

因此,默认都选择ON,这更方便管理嘛。而且,不需要这个表时,可以通过drop table命令,系统直接删除这个文件,也就回收空间了。若是在系统共享表空间中,即使表删除了,空间也不会回收。

但是我们更多的是删除某些行的数据,这?

数据删除流程

可以从它的底层结构分析一下。

我们知道,InnoDB是以B+树的形式存储数据的。如果我们删除掉其中某条记录,InnoDB引擎只会把这个位置标记为删除。如果之后我们再插入一条记录,有可能会复用这个位置,但是磁盘文件的大小不会缩小。(有、逻辑删除的感觉)

但是InnoDB又是按页存储的,如果整个页都从B+树中去掉,那这个页就会被标记为可复用,如果之后插入一条记录需要使用新页时,那就可以复用这个页。

进一步地说,使用delete命令,就是把记录或者页标记为“可复用”,并不是真正地回收表空间!

这种可以被复用,但是没有被使用的,看起来就像是“空洞”。

不止删除数据会造成空洞,插入数据也会!

按照之前的理论来说,数据如果不是按照索引递增顺序紧凑地插入,就有可能造成数据页分裂,那新的页的后面就留下的空洞;更新时也是,更新可以理解为删除+插入,也会造成空洞。

重建表——收缩空间

怎样把表中存在的空洞去掉呢?

假如有一个旧表A需要做空间收缩,可以这样想:

新建一个与A相同结构的表B,然后把A的数据按照id递增的顺序一条一条地导入B。这样做B的主键索引更紧凑,数据页的利用率也高。如果把表B作为临时表,当表A的数据全部导入到表B后,用表B代替表A。从效果上看,就起到了收缩表A空间的作用。

可以使用alter table A engine=InnoDB来重建表。

在MySQL5.5前,这个命令执行流程和上面的话差不多!区别就是中间的步骤不需要我们做,MySQL会自动完成转存数据、交换表名、删除旧表的操作。

在这个过程中,耗时最多的是往临时表插入数据的过程,如果有新数据要写到表A,就会造成数据丢失,因此,在整个DDL过程中,表A不能有数据更新。

在MySQL5.6版本引入Online DDL,对这个流程进行优化。

  1. 建立一个临时文件,扫描A主键所有数据页
  2. 用A中记录生成B+树,放到临时文件中
  3. 生成临时文件的过程中,将所有对A的操作记录到一个日志文件(row log)中
  4. 临时文件生成后,将日志文件的操作应用到临时文件中,得到一个逻辑上与表A相同的数据文件。
  5. 用临时文件替代表A的数据文件

可以看到,它多了一个记录日志和重放操作,使得在重建表的过程,允许对表A做CRUD操作,而不被阻塞。

在alter语句启动的时候,需要获取MDL写锁,但是这个写锁在真正拷贝数据之前就已经退化成读锁了。

为什么要退化?为了实现Online,读锁不会阻塞CRUD。

为什么不直接解锁?为了保护自己,防止别的线程对表做DDL。

其实这个Online DDL过程中,最耗时的过程就是拷贝数据,这个步骤的执行期间应该是可以接受CRUD操作的。所以,对于整个DDL过程来说,不需要全部过程都上锁。锁的过程很短,可以认为是Online的。

想要操作得比较安全,可以使用github开源的gh-ost

Online和inplace

。。。?