mysql数据删除策略

首先我们表数据的存放，他可以存在共享空间里面，也可以是单独的文件，这个行为是由参数innodb_file_per_table控制的。它有以下两个值：

OFF：表的数据存放在系统共享表空间。

ON：每个InnoDB表数据存储在一个以.ibd为后缀的文件中。默认是ON

数据删除流程

接下来删除流程的讲解时基于innodb_file_per_table是ON来描述的。下面我们看看一个索引结构图

现在如果我们要删除R4这个记录，那么就把R4这个记录成删除，之后如果要插入一个ID在300和600之间的记录时，可能会复用这个位置。如此，磁盘文件的大小不会减少。

如果删除了整个数据页上所有的数据，那么整个数据页就可以复用了，数据页的复用和记录的复用是不同的。

 

1. 记录的复用，只限于符合范围的数据，如上面R4的位置被删除后，如果插入一个ID是400的行，那么可以直接复用，如果是800的，那么不能。

2. 数据页的复用，如果选择需要插入一条ID为50的记录，需要另外申请一个数据页，那么可能这个数据页就会被复用。

以上两种情况，磁盘上的文件都不会变小。

还有一种情况，插入数据也可能会造成数据的留空，如下插入一个550的数据，而一页上的数据已经满了，那么就会申请新的一页。

这个时候pageA页就会留下空白。如果要把这些空白去掉，就可以采用重建表的操作。

重建表

使用alter table A engine=InnoDB命令来重建表，MySQL会自动完成转存数据、交换表名、删除旧表的操作。

上面是一个改锁表的过程，这个会创建一个临时表。

花时间最多的是往临时表插入数据的过程，有新的数据要写入到表A，造成数据丢失，所以在整个执行语句的过程中，A表不能有更新，所以整个执行时不能在线执行的。在后面的版本mysql做了优化，这个叫online执行：

1. 建立一个临时文件，扫描表A主键的所有数据页；

2. 用数据页中表A的记录生成B+树，存储到临时文件中；

3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中state2的状态；

4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；

5. 用临时文件替换表A的数据文件。

 

在以上的流程中，alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。这样子，就不会阻塞增删改的操作了。但是也不能直接解锁，因为怕有其他的线程也在执行相同的指令。

Online和inplace

在上面的第一个重建表方案中，会创建出来一个临时表，这个是在server层创建的。表A重建出来的数据时放在tmp_file中的，这个临时文件是InnoDB在内部创建出来的。整个语句执行过程都是在InnoDB中完成的，即使在引擎层完成的，对于server层来说，他并没有把数据移动到临时表，是一个inplace操作。

所以说online操作一定是inplace的。但是inplace的不一定是online的，如加一个全文索引，它会阻塞增删改操作，是非online的。

最后说一句，在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。