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

删除数据的原理和产生的问题、重建表

author：陈镇坤27

创建时间：2021年11月16日00:45:30

编辑时间：2021年11月16日13:52:05、2021年11月17日09:53:27、2021年11月29日15:01:07、2021年12月7日14:44:41

转载请注明出处

——————————————————————————————

数据库的内模式

问：对Innodb的表结构和表数据的存储有什么了解？

答：

表结构：MySQL8.0之前，Innodb表结构存储以.frm后缀文件存储，MySQL8.0之后，直接定义在系统数据表（information_schema库）中；

表数据：参数innodb_file_per_table为ON（MySQL5.6.6开始默认）时，存储在单独的.ibd后缀文件中，为OFF时，存储在系统表空间（.ibdata文件）中

建议：表数据参数设置为ON，drop table可以真正删除表数据。

问：什么是系统表空间和独立表空间？

答：系统表空间，也称共享表空间，一个库的所有表数据（包含索引）全部存储在一个文件中，该文件可拆分；

独立表空间，指一个表由一个后缀.ibd文件存储（数据和索引）。

数据的删除流程

问：数据是怎么删除的？

答：对数据进行标记，标记后，该位置可能可以被复用（之所以是可能，是由于如果有一致性读视图关联到该被标记删除的数据的话，新insert的数据不能复用该空间）。

需要注意的是，如果是数据页内部记录的删除，一般只有在被删除数据两侧索引范围内的数据才能进行复用，如果是整个数据页被删除，则数据页可以复用到任意位置，无索引范围限制。

这些被标记删除的，可复用空间的数据，类似数据“空洞”。

问：如果数据页的利用率很低，会发生什么？

答：当相邻两个数据页利用率都很小时，会发生页合并，其中一个页的数据会合到另外一个页上，然后本数据页标记可复用。

问：什么时候会发生数据“空洞”？

答：除了delete、新增，更新数据也可能导致数据空洞。

当数据按照索引递增顺序插入，则新插入的数据与索引树之间是追加关系的；

当数据按照非索引递增顺序插入，新插入的数据如果所在数据页满了，需要发生页分裂。

更新数据，实质就是标记旧数据为删除，然后插入一条新数据。

重建表

问：怎么重建表？其作用是什么？

答：

alter table A engine=InnoDB

重建索引树，收缩表空间。

问：怎么理解重建表？

答：

早期重建表是建立一张临时表，将原表的数据重新一条条存入临时表，最后再替换表名，得到的新的表便是收缩表空间后的表。

而重建表语句就是做了这些工作（过程中，页预留1/16空间给更新使用），过程中耗时最多环节是转存数据。

MySQL5.6以前的重建表的过程中，默认临时表是生成在server层，为防止转存数据期间丢失数据更新，表A不能更新数据（加MDL写锁）（MDL锁是5.5版本引入，此前又是怎么保证表A不更新数据的呢？）

MySQL5.6，为实现Online DDL（即动态重建），在引擎内部生成临时文件，并在其中记录旧表数据的索引树（此时需要一致性读视图），临时文件生成过程中，通过row log记录客户端的数据更新语句，临时文件生成后将log同步到临时文件和旧文件，最后用临时文件替换旧表数据文件。

过程中，DDL先拿MDL写锁，在执行生成临时文件前，会退化为MDL读锁（该读锁阻塞其他DDL），由于持有写锁时间很短，所以可以认为是Online。

PS：由于重建过程需要扫描原数据和构建临时数据，非常消耗CPU资源，线上服务建议用git-hub上的gh-ost来做。

问：DDL不是会拿MDL写锁吗，为什么说Online DDL是动态的？

答：因为Online会在DDL拿到MDL写锁后，很快将其退化为MDL读锁；而加MDL读锁，是为了阻塞其他线程对该表的DDL。

Online和Inplace

问：说明Online和inplace的区别。

答：两者不是可以放在同一层比较的概念。与inplace相对的其实是copy。

在重建表的过程中，

MySQL5.6以前的重建表语句默认为

alter table t engine=innodb,ALGORITHM=copy;

5.6开始，默认为

alter table t engine=innodb,ALGORITHM=inplace;

copy方式是拷贝数据到server层的临时表，而inplace是相对server层来说，数据在引擎内部的转存，所以看似“原地”。

（PS：无论是server层的copy临时表方式还是引擎层的inplace方式，本质上都是需要另一块大约对等的空间进行转存的。）

此外，inplace的操作并非意味Online，例如：添加全文索引、空间索引。（截止到MySQL8.0）

但Online的操作一定是inplace的。

问：有哪几种重建表的命令？区别是什么？

答：

-- 收缩表空间
alter table t engine = InnoDB

-- 重新统计索引信息
analyze table t

-- 上述两者的结合
optimize table t  

实战题

问：什么场景下，执行了重建表语句，结果得到的表空间不仅没减小，还多占用了一丁点。

答：一开始表的数据页就很紧凑，重建表时，新的数据页会预留1/16给未来的更新使用。

PS：可以通过INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 查看数据页使用情况。