MySQL深度解析--数据的删除与重建表

Ⅰ.数据存放和数据删除

1.MySQL中数据的删除流程（delete）

• 删除一条记录时，InnoDB会将该记录标记为删除（可复用）状态。并不会从磁盘上释放该记录所在的空间，只是如果后面有数据要插入该位置时可以直接复用该存储空间。
• 删掉了一个数据页上的所有记录时，整个数据页就可以被复用了。如果表（任意表）需要新的数据页时，都可以复用这个数据页。

数据页的复用和记录的复用是不同的

• 记录的复用，只限于符合范围条件的数据。
• 数据页的复用，可以复用到任何位置。

2.数据空洞

删除数据或者插入数据会造成数据空洞

• 删除数据操作会留下可复用的空间，这些空间没有被利用，造成了数据空洞。
• 插入数据时，如果数据是随机插入的，就可能造成索引的数据页分裂。页分裂完成后，之前的数据页的末尾就留下了空洞（注意：实际上，可能不止1个记录的位置是空洞）。

也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

3.重建表

# 5.6版本前，不是Online的，重建表与对该表的增删改查不能同时进行。
alter table A engine=InnoDB

Online DDL
MySQL在5.6以后引入的 Online DDL，对alter table操作流程做了优化

Online DDL重建表的流程：

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

alter语句执行时会获取MDL写锁，但是在真正的拷贝之前，为了不影响增删改查写锁会退化成MDL读锁。
PS：重建表推荐使用GitHub开源的gh-ost来做。

---

Online 和 inplace

inplace 和 copy 的区别：

• inplace 整个DDL过程都在InnoDB内部完成。
• copy 在server层创建了tmp_table 用来中转。

#alter table t engine=InnoDB 相当于下面的语句
alter table t engine=innodb,ALGORITHM=inplace;

# 与inplace对应的就是copy
alter table t engine=innodb,ALGORITHM=copy;

Online 和 inplace 之间的关系：

1. DDL过程如果是Online 的，就一定是inplace 的；
2. 反过来未必，也就是说inplace的DDL，有可能不是Online的。截止到MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引(SPATIAL index)就属于这种情况。

重建表的三种方式

alter table t engine = InnoDB 也就是 recreate ，详见上文。
analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；
optimize table t 等于recreate + analyze。