InnoDB 表包含：表结构和数据。8.0 前，表结构存在.frm 文件。8.0 版本，表结构（占用空间小）放系统数据表。

一、为什么删除表数据，表空间不能回收？

1.参数innodb_file_per_table

控制表数据可以存在共享表空间里，也可以是单独的文件。

1. OFF ，数据共享表空间，跟数据字典一起；表删掉，空间不会回收。

2. ON (默认)，存在 .ibd 文件中；不需要时， drop table直接删除

删除行，数据被删除了，空间没有回收。

2.数据删除流程

索引的示意图。B+ 树

图 1 B+ 树索引示意图

删掉 R4，把 R4 标记为删除。插入ID 300 和 600 复用。磁盘文件大小不变。

InnoDB 数据按页存储，删掉页上所有，整个页复用

3.数据页跟记录复用是不同

记录复用：插入 ID = 800 ，不能复用

整页：从 B+ 树里摘掉，可复用任何位置。

两页利用率都小，合到一个，另一页标记为可复用。

delete 只标记“可复用”，没被用空间，像“空洞”。

4、插入也会造成空洞

按索引递增插（紧凑），随机插（页分裂）

图 2 插入数据导致页分裂

page A 已满，再插，申请新页 page B 保存。页分后，A 末尾留空洞（实际上不止 1 个记录位是空洞）。

更新：删旧值，插新。也会造空洞。

二、 回收空间：重建表

新建表 B（表 A 相同结构），A 中读出插入 B （按主键 ID 递增）。

B 是新建的， A 上空洞， B 不存在（主键索引紧凑）。 B 替换 A收缩表作用。

alter table A engine=InnoDB 命令重建表。MySQL 5.5 前，B 不需创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

临时表插入数据（时间最多），期间新写入，到A 数据丢失。DDL中A 中不能更新（非Online）。

图 3 改锁表 DDL

2、Online DDL（ 可做增删改）重建表流程：

1.  建立临时文件，扫描表 A 主键所有数据页；

2.  A 记录生成 B+ 树，存储临时文件中；

3.  生成临时文件的过程中， A 操作记录日志文件（row log）中，state2 状态；

4. 临时文件生成后，日志文件操作应用临时文件，逻辑数据上与A 相同，state3 状态；

5.  临时文件替换表 A。

图 4 Online DDL

第 6 篇《全局锁和表锁 ：给表加个字段怎么索这么多阻碍？》留言说，DDL 之前拿 MDL 写锁，还能叫 Online DDL 吗？

alter 启动获 MDL 写锁，拷贝数据前退化成读锁（图 4 ）。

    为什么退化？实现 Online，MDL 读锁不阻塞增删改操作。

    不解锁，保护自己，禁止其他线程做 DDL。

拷贝数据最耗时，可以接受增删改。 DDL 过程，锁时间非常短，可认为 Online 。

上述重建方法都扫描原表、构建临时文件。对大表来说，很消耗 IO 和 CPU 资源。线上服务，小心控制操作时间。用 GitHub 开源gh-ost 来做安全。

3.Online 和inplace

inplace 和DDL 有关、容易和Online混淆 

图 3 表 A 中数据导出来放 tmp_table（临时表）， server 层创建。

inplace：重建数据放“tmp_file”里（图 4 ），InnoDB 内部创建。DDL 在 InnoDB 内部完成。server 层“原地”操作（没把数据挪动到临时表）。

例1：1TB 的表，磁盘间 1.2TB，不能做 inplace  DDL ，tmp_file 占临时空间。

alter table t engine=InnoDB，隐含意思：

alter table t  engine=innodb, ALGORITHM=inplace;

对应强制拷贝表（图 3）用法是：

alter table t  engine=innodb,ALGORITHM=copy;

例2：给 InnoDB 表字段加全文索引（过程 inplace ，非 Online ）

alter table t add  FULLTEXT(field_name);

两个逻辑关系是：

1.  DDL 过程如 Online ，一定 inplace ；

2.  反过来未必， inplace 的 DDL，可能不是 Online 。如：添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 。

4.analyze table 和 alter table 、optimize table重建表区别

alter table t engine = InnoDB（recreate）图  4 ；

analyze table t 表索引重新统计，没修改数据，过程加了 MDL 读锁；

optimize table t 等于 recreate+analyze。recreate几乎全新，analyze必要小极端情况用

小结

数据库中收缩表空间方法。

收缩一个表，delete 数据表文件大小不变，alter table 重建表文件变小

重建表两种实现：Online DDL业务低峰期使用

思考题

“收缩表空间，适得其反”什么原因 ？

表 t 1TB；执行 alter table tengine=InnoDB；变成 1.01TB。

答：表本身没空洞（如刚重建表）。DDL 时，刚好外部 DML 执行，引入新空洞。

重建表后不是“最”紧凑：重建表时，InnoDB 不会把整张表占满，留1/16 给后续更新。

如：1.  表 t 重建； 插入数据，掉预留空间；2.  再重建表 t，上面现象。

评论1

1.Truncate 会释放表空间吗？理解为drop+create

2.重建表没更新，有可能产生页分裂和空洞？Online 没有

3.相对 Server层没有新建临时表，就是 inplace，怎么判断是不是相对 Server 层没有新建临时表？

命令执行后影响行数，没有新建临时表，新建行数0

4.分布式ID（雪花算法生成的ID）生成的索引和自增ID。性能一样

雪花算法生成ID：越来越大，但不逐渐递增，长度bitint。

评论2

将 alter 显式放到事务里 ，事务不提交 ， 另一事务查询到alter 操作后表结构 是否打破了 mvcc ？

alter table 默认提交前面事务，自己独立执行