数据库占用空间太大,将表删除掉一半的数据后,为什么表的大小还是没有变
数据库的回收,一个InnoDB表中包含了两个部分,分别是表结构定义和数据。在Mysql8.0以前,表结构是存在以.frm为后缀的文件里,在mysql8.0,则允许将表结构定义放在系统数据表中,因为表结构定义占用空间很少,所以表的大小还是和表数据有关的。
进行简单的表删除操作,对表进行空间回收,表数据的存储可以在共享表空间中,也可以存放在单独的文件当中,可以通过innodb_file_per_table这个命令来控制,当它等于off的时候,将表的数据存放在系统共享空间,当它为on的时候,存放在一个.ibd文件当中。在mysql5.6.6以后,这条命令的默认值为on。
将文件存放在文件当中更适合管理,当不需要表的时候直接drop table回收表空间即可,但是我们在日常生活中是删除某些行,而不是直接删除整个表。如果放着共享空间当中,即使表删除掉了也不会进行回收。
对放置在文件当中的数据我们进行下面的讨论
要去删除数据,我们是在b+树上去找,然后将它标记然后标记为删除,要是以后还要用这个位置的时候,就会复用这个位置,所以磁盘的文件并不会缩小,要是直接删除上一个数据页上所有的记录,那么这个数据页就可以被复用
数据页的复用和记录的复用是不同的
记录的复用只限于符合范围条件的数据,而数据页的复用,是可以将记录放到任何一个位置的。,我们使用delete语句将整个表文件数据删除的时候,只是将表的数据页标记为可复用,并不太能回收表空间,所以磁盘大小也不会变化,看起来就像是空洞一样。
当我们不按照顺序随机插入数据的时候,也会造成表空洞现象,比如当一个数据页满了,我需要再插入一个数据,他就会去申请一个新页,然后进行页分裂,这样第一个数据页的末尾就会出现空洞。
当我们需要去去除这些空洞的时候,直接重建表就可以达到。
alter table A engine =InnoDB这个命令就可以达成
表A需要空间收缩,将表A的结构复制后,按照主键递增的顺序将数据一行一行的拿出来复制到A当中,当表复制完成后,再使用B来替换A,就起到了空间收缩的作用,因为表b的索引更加紧凑,起到了空间收缩的作用。
花时间最多的步骤是往临时表插入数据的过程,如果在这个过程中,有新的数据要写入到表 A 的话,就会造成数据丢失。因此,在整个 DDL 过程中,表 A 中不能有更新。也就是说,这个 DDL 不是 Online 的。
在mysql5.6后引入了Online DDL,对操作流程做了优化,这个我会在下篇文章中提到。