Mysql - delete、重建表过程以及内部的锁状态变化

    Mysql执行delete语句后，表空间会被回收吗？答案是不会。其实不止Mysql、Mongo数据库也是一样的。记得几年前项目上Mongo数据库有下单时的商品快照信息，记录当前下单时的商品状态，主要用于追责（或免责）。当时发现有几年没有对数据进行删除操作，记录了上十亿条数据，当时DBA就说删除只留最近半年的数据，但是表空间是不会被回收的，当时只知道有这么回事，并不知道内部的真正细节。当删除一条数据后，这条数据的位置就可以被复用了，Mysql是用B+树存储的，数据记录在主键聚簇索引上，当数据页上的数据全部被删除后，也不能回收该部分表空间，而是整个数据页都可以被复用了。

    Mysql的表数据本质就是一棵全量字段的B+树，多路平衡查找树。平衡二叉查找树、红黑树当需要满足平衡（控制叶子节点的高度差）、查找（当查询到一个节点后，根据一次判断就可以排除当前一半的数据），所以AVL树（子平衡二叉查找树）为了删除节点后还能满足其特点需要左旋、右旋，红黑树为了满足特点不仅要左旋、右旋，还要变换颜色。B+树本质是多路平衡查找树，为了更好的控制层高，减少可能的磁盘寻址次数（磁盘寻址是毫秒级别的，内存寻址是纳秒级别的，相差万倍到十万倍），底层使用数据页为最小存储单元。当发生数据大量的删除操作后，为了平衡，Mysql默认当数据页的数据小于 2/m就会进行页合并【m的配置的多路的路数】。其实不仅仅删除操作，大量的增删改也一样，都能照成大量的数据页空洞（即表空间浪费），比如当新增了多条数据后当前的数据页 大于 15/16m 数，就会进行页分裂。新增操作时，Mysql会预留 1/16 的空间给后续的写操作，否则后续只要有一点表动就会触发页分裂。 

    我们基本都会创建InnoDB表，而表分为 表结构定义和数据两部分。 在Mysql 8.0以前表结构定义只能存放在 .frm后缀的文件中；Mysql8.0后允许表结构定义存放在系统表数据中【与表数据相比，表结构定义的占用空间非常小】。表数据存储位置可以使用参数 innodb_file_per_table 进行设置，OFF表示存储在系统共享表空间，与数据字典存储在一起；ON表示数据表存储在一个以 .idb结尾的文件中。 从Mysql 5.6.6开始默认配置项为ON，主要是单独进行存储方便管理。如上，所以如果想要对一张大表进行表空间回收操作，那么就需要重建表操作，而该操作依赖将该参数配置为ON。

 

    重建表操作可以通过命令： alter table t engine = InnoDB， 但是执行该命令在Mysql 5.5版本前后的实现逻辑完全不同。

Mysql 5.5之【前】的实现逻辑：新建一张与原表相同的结构的表，将原表中的数据全部拷贝一遍到新表中，此时是依次添加就不存在表空间空洞的问题了【只是此时新建的所有数据页都预留了 1/16的数据页的空间】，再将两张表的表名进行交换，删除旧表。但是整个中间过程对完不可见，即需要加锁（其他读写操作需要等待，比较危险）。此处建立的临时表叫做 tmp_table，此过程是在 Mysql的Server层创建的。

Mysql 5.5之【后】的实现逻辑：引入了 Online DDL的概念，本质就是在进行上述流程的过程中，使用一个临时文件处理客户端发来的请求，当整个过程完成后再合并临时文件。好熟悉，JVM的cms垃圾回收器的味道，有没有，道理都是相同的。此处建立的临时文件 tmp_file是在InnoDB内部完成的，整个过程对于Mysql的Server层来说是一个原地操作，就是“inplace”。 具体的实现流程如下：

1. 在执行alter操作之前需要获取 MDL写锁；
2. 建立一个临时表，扫描原表上面的所有数据页；
3. 在真正数据拷贝之前将MDL写锁退化为MDL读锁，就不会阻塞【增删改查】操作了；退化后就实现了online，退化为读锁是为了保护自己，防止其他线程修改表结构定义。
4. 用数据页中的记录生成B+树存储到临时文件；
5. 生成临时文件的过程中，将所有对该表的操作记录在一个日志文件（row log）中；
6. 临时文件生成后，将日志文件中的操作记录到临建文件，得到一个逻辑数据上与原表相同的数据文件；
7. 用临时文件替换原表文件；

 

理解 online 和 inplace

alter table t engine = InnoDB 引申的含义是：alter table t engine = InnoDB，ALGORITHM=inplace;  // 对应就是Mysql 5.5 之【后】的版本流程

alter table t engine = InnoDB，ALGORITHM=copy; // 对应的流程就是Mysql 5.5之【前】的版本流程

DDL的过程中，如果是Online的话，就是一定是 inplace；如上

反过来未必，也就是说 inplace的DDL有可能不是 Online的，比如添加全文索引（FULLTEXT index）和空间索引（SPATIAL index），此时就是非 online（阻塞）。

alter table t add FULLTEXT（"aaa"）；

 

理解 重建表、analyze、optimize

Mysql 5.6版本开始的重建表的过程：如果叫 recreate table；

analyze table t 是对索引信息重写进行采样统计，没有修改表数据，这个过程加了MDL读锁；具体可以参见：Mysql - 优化器阶段的索引选择过程

optimize table t 的过程 = recreate + analyze； 即如果想要重建表回收表空间，并且重新采样统计索引信息，就可以直接使用该命令即可。