Mysql是怎么删除数据的?

Mysql的删除

问题

问题

有时候会遇到这么一种情况，当数据库占用空间太大时，把一个最大的表删掉了一半的数据，怎么表文件的大小还是没变？

数据库表的空间回收

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

删除表之后为什么空间不减小

参数innodb_file_per_table(表数据的位置)

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

表数据既可以存在共享空间里，也可以是单独的文件，这个行为是由参数innodb_file_per_table决定的

设置为ON：表示每个InnoDB表数据存储在一个以.ibd为后缀的文件里
设置为OFF：表示表的数据放在系统表空间，也就是和数据字典（通常是Information_Schma库）放在一起

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

删除整个表时，可以使用drop table命令回收表空间，但是，我们遇到的更多的删除数据的场景是删除某些行，这时候就遇到了一个问题，表中的数据被删除了，但是表空间没有被回收！！！

数据删除流程

InnoDB表中的数据都是用B+树的结构组织的，假设要删除一条记录，InnoDB引擎只会把要删除行标记为删除，如果之后要插入一个记录时，可能会复用这一行的位置，但是，磁盘文件的大小并不会缩小

所以： InnoDB的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记录，会怎么样？

答案是，整个数据页就可以复用了

注意点：

数据页的复用和一条记录的复用不一样，数据页可以用在任何位置
如果相邻的两个数据页利用率都很小，系统就会把这两个也页上的数据合在另一个页上，另外一个数据页就被标记为可复用

数据页空洞的问题：

这些标记了删除而没有被复用的空间，看起来就像是空洞，不只是删除数据会造成空洞，插入数据也会如果数据是按索引递增顺序插入的，那么索引是紧凑的，但如果数据是随机插入的，就可能造成数据页分裂

另外，更新索引上的值，可以理解为删除一个旧的值，然后插入一个新值，也就是说经过大量增删改查的表都是可能存在空洞的，所以，如果能把这些空洞去掉，就能达到收缩表的目的

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

重建表

新建一个与表A结构相同的表，然后按主键递增的顺序，把数据一行一行的从表A里读出来再插入到表B中，用表B替换表A

mysql> alter table t engine=innoDB;

在mysql5.5之前，这个命令是用copy算法

但是，当把表A中的数据一行一行插到表B时是不能对表A更新的，也就是说在mysql5.5以前，不是online ddl的,意思就是在重建的过程中不能有对表A的更新操作

5.5之后,在复制插入数据的时候，会把所有对表A的操作都记录在一个日志文件row log中，当复制插入完成后把所有的row log用在新表上（解决了在线DDL的问题）

online DDL 和 inplace（没搞懂）

	对于server层来说，没有把数据挪动到临时表，是一个原地操作，这就是inplace名称的来源在复制过程中，会在Server层创建一个临时表，把数据复制在临时表中，而在InnoDB内部创建了tmp_file

mysql> alter table t engine=innoDB,AlGORITHM=inplace;

optimze table/analyze table/alter table这三种方式重建表的区别

从mysql5.6版本开始，alter table t engine=InnoDB(也就是recreate)默认的就是上面的流程
analyze table t其实不是重建表，只是对表的索引信息进行统计，没有修改数据，这个过程加了读锁
optimize table = recreate + analyze

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/202541.html