为什么表数据删除一半，表文件大小不变？读后总结

心已赠人 2021-10-29 14:08 184阅读 0赞

现象：删除了表数据，但是表文件大小不变  
针对的引擎是innodb  
表数据既可以放在共享表空间里，也可以是单独的文件。由innodb\_file\_per\_table控制。  
off为将表数据放在系统共享表空间，on将表数据存储在一个.ibd为后缀的文件中。  
建议是将innodb\_file\_per\_table设置为on。

数据删除流程  
innodb数据是由b+树组织的，当我们删除某一条记录，引擎只将标记该记录为删除，并不真正删除。将来插入数据可能复用空间，磁盘文件大小并不会缩小

innodb数据是按页存储的，删除了一个数据页上的所有记录，那么这个数据页就可以被复用。

数据页的复用与记录的复用是不同的，范围不同 ，数据页可以复用到任何位置。  
且当两个数据页利用率都很小时，系统会将这个两个数据页合并到其中一个页上，另一个数据页就背标记为可复用。  
但是进一步，我们将整个表数据都删除，所有数据页都标记为可复用，而磁盘大小不变。

所有delete命令，只是将记录或者数据页标记为可复用，不会回收表空间。

这种可以复用但是没有使用的空间像是空洞一样。

不仅是删除记录会产生空洞，插入数据也会产生空洞。  
顺序插入的话，磁盘数据页空间是紧凑的，但是如果是随机插入，会导致数据页内数据的移动。从而导致页分裂。

如果你要收缩空间，最好的操作就是重建表  
命令：alter table A engine=Innodb来重建表  
在mysql5.6之前不支持Online DDL 所以以上操作，表A中不能有更新。

OnlineDDL  
重建表的流程:  
1.建立一个临时文件，扫描表A主键的所有数据页  
2.用表A数据页中的记录生成一颗B+数，插入临时文件中  
3.在生成临时文件的时候，将所有对A的操作记录存在一个日志文件中（row log）  
4.临时文件生成后， 将日志文件应用到临时文件，得到一个与表A逻辑数据一致的数据文件  
5.用临时文件替换表A的数据文件  
alter语句启动时，首先获得MDL写锁，但是这个写锁在数据拷贝的时候会退化成读锁，这样就可以进行增删改操作，不会阻塞上线对表的其他操作。并且可以锁住表保证同时不会被其他连接进行DDL操作。  
重建表需要扫描原表数据和构建临时文件，非常消耗IO与CPU资源。

DDL的过程在InnoDB内部完成  
alter table t engine=innodb,algorithm=inplace;  
alter table t engine=innodb,algorithm=copy;  
在重建表的时候 inplace与copy是相同逻辑