MySQL InnoDB 表数据

1. 版本

MySQL 5.7.25，ubuntu 16.04
以下将基于InnoDB进行说明。

2. 相关参数

innodb_file_per_table

从MySQL 5.6.6开始，该参数默认为1（即为'on'）。
参数值含义如下：

'OFF'表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
'ON'表示每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

3. delete语句原理

delete语句并没有因为删除数据而释放出存储被删除数据的空间，其作用仅将被删除数据行标记为可复用。若后续插入的数据的位置刚好位于可复用的位置，则新数据会存储于可复用的位置。
该说明适用于数据行和数据页。

4. drop table语句原理

若innodb_file_per_table设置为1，drop table将整个表删除，同时能回收数据文件原本占有的空间。

5. 数据空洞

经过增删改的表，会导致数据页中存在没有存放数据的空间。这些空间会同样占用文件的体积。

6. 回收数据空洞

数据空洞的回收机制有如下两种（以下讨论基于InnoDB引擎，事务隔离模式为可重复读，表的遍历基于主键）：
方案(1)：建立与原表结构相同的临时表，逐行读取原表数据，并依次将每一行数据插入临时表。最后用临时表替换原表。
方案(2)：
1)建立一个临时表，并扫描原表主键的所有数据页；
2)随后用数据页中原表的记录生成 B+ 树，存储到临时表中；
3)在生成临时表过程中，对原表的操作会记录到日志中；
4)临时表构建完成后，将日志记录的操作应用于临时表；
5)将步骤4)过程中产生的操作应用至临时表；
6)最后将临时表替换原表。
方案(1)会为原表加写锁，阻塞所有对原表的DDL操作。由于需要逐行读取数据，故为每一行添加行锁，后续的增删写操作将被阻塞。
方案（2）在MySQL5.6及以后的版本中，采用onlin DDL操作。在步骤1)获取DML写锁，阻塞DML以及DDL操作；但在步骤4)MDL写锁降级为MDL读锁，允许对原表进行DML操作，并记录在日志中；在步骤5)重新升级为DML写锁。

7. 回收数据空间命令

使用DDL 的如下命令进行操作：
`
ALTER TABLE tb_name engine = innodb [ALGORITHM = inplace | copy]
`
其中，ALGORITM为可选参数，ALGORITHM = inplace表示上文提到的方案(2)，ALGORITHM = copy表示上文提到的方案(1)。
在MySQL 5.6及以后，ALGORITHM = inplace为默认选项（因为MySQL 5.6开始引入online DDL）。MySQL 5.6之前的版本使用ALGORITHM = copy。