Mysql学习笔记--删除数据

首先 innodb_file_per_table 要设置为ON
innodb_file_per_table：
1 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
2 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
如果设置为OFF，则drop table也是不会回收空间的。
删除流程
MYSQL的删除并不是真正的把数据删除，只是标志为删除。
比如删除ID为300的行，则是把这个记录标记为删除。后续插入的时候是会复用这个记录。
其次， 数据页的复用跟记录的复用是不同的。
记录的复用，只是限于范围条件的复用，
整个页删除，是可以复用到任何的位置。
如果相邻的两个数据页数据利用率很小，就会合并，然后把后面的页面标记为删除。
当使用delete删除整个表的时候，也只是把整个表标记为删除，以后会复用整个表的空间，所以磁盘空间是不会减少的。
复用而没有被使用的空间，看起来就像是空洞。
插入数据也会产生空洞。
如果是按索引顺序递增插入的情况，索引是紧凑的，但如果是随机插入就可能产生空洞。
比如 PAGE A的末尾的ID是500，然后PAGE A要插满了，此时插入550就不得不重新申请一个页面，此时PAGE A的末尾就产生了可能不止一个记录的空洞。
更新索引上的值就是删除一个索引，插入一个索引，也是会产生空洞的。
只要把表的空洞去掉，就可以缩减空间。
重建表
MYSQL 5.5 之前的流程：
1 新建一个与表 A 结构相同的临时表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中
2 用B替换A
由于B的索引更紧凑，数据页的利用率更高。A的空洞也不会存在。

alter table A engine=InnoDB 

MYSQL5.6 引入了online DDL:
1 建立一个临时文件，扫描表 A 主键的所有数据页；
2 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
4 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
5 用临时文件替换表 A 的数据文件。

图 4 的流程中，alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。不直接解锁的原因是防止其他线程对这张表做DDL。
对于很大的表是很耗费IO和CPU资源的，要把握好操作时间。推荐开源的gh-ost。
Online 和 inplace
1 DDL 过程如果是 Online 的，就一定是 inplace 的；
2 反过来未必，也就是说 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间
索引 (SPATIAL index) 就属于这种情况。
inplace是说整个操作都是在innodb内完成的，server层没有做数据挪动到临时表的操作，是一个原地操作。在重建表的过程中，inplace 恰好等于 online，但两者并不相等。