MySQL深入11-数据库表空间回收
# MySQL深入-数据库表空间回收
表数据是可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table 控制的:
- 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
- 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
由于一个表单独存储为一个文件更容易管理，且在不需要这个表的时候，通过drop table命令删除表，会直接删除这个文件。如果存储在共享表空间，即使表删除了，空间也是不会回收的。所以建议把innodb_file_per_table这个参数设置为ON。
# 数据删除流程
删除一条记录
对于上图，假设我们删除R4这条记录，InnoDB引擎只会把R4这个记录标记为了删除，如果有一条新的记录(ID在300和600之间)，就会复用这个位置。相当于删除只是做逻辑删除，并没有进行物理删除。因此磁盘文件的大小并不会缩小。
删除整个数据页
上述情况是针对删除一条记录，如果删除Page A这个数据页的话，整个数据页还是可以被复用的，但是复用的情况和单条记录复用情况有所不同。
- 单条记录的复用，只限于符合范围条件的数据。比如上图删除了R4这条数据，那么该位置的复用只对ID在300-600的数据才能进行复用。
- 数据页的复用，是可以复用到任何位置。比如上图删除Page A这个数据页，那么这个数据页是可以复用到任何位置的，假设插入一条ID=50的数据，这个数据页是可以被该新数据复用的。
如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。
删除整个表
如果使用delete命令把整个表的数据删除了，所有的数据页都会被标记为可复用。但是磁盘的文件大小不会变小。
删除小结
delete命令其实只是把位置或者数据页标记为可复用，但是磁盘的大小是不会变化的。**也就是说通过delete命令是不能回收表的空间。**所以会出现即使删除大表一半的数据，磁盘的空间仍然没有变小。
插入数据造成空洞
如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。
如上图所示，Page A数据页已经满了，如果此时再插入一条ID=550的数据，就会导致数据页分裂。分裂完成后，Page A的末尾就留下了空洞。
另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。
经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。
# 重建表
可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行从表A读出来再插入表B中。
由于表B是新建的表，而且主键索引更紧凑，数据页的利用率更高。如果我们把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。
对于上述步骤，可以使用alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表 B 不需要你自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。
对上图的步骤来说，最费时间的步骤就是往临时表插入数据的过程，如果这个过程中，有新数据要写入表A，就会造成数据丢失。因此，在整个DDL过程中，表A中不能有更新，也就是整个过程是不能在线的。
在MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。
建立一个临时文件，扫描表 A 主键的所有数据页；
用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
用临时文件替换表 A 的数据文件。
由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。