Java面试题 -为什么delete表数据磁盘空间还被占用？

1. 前言

声明：本文总结笔记参考 https://www.toutiao.com/i6935264754059477542?wid=1627461136093

最近有个全量同步的功能开发，操作过程是先删除在插入，并通过delete删除表记录。这样会有一个问题，如下：

已经执行了delete，表文件的大小为何没减小？

2. 分析问题

delete后发生了什么？比如想要删除 R4 这条记录： 过程描述：

• InnoDB 直接将 R4 这条记录标记为删除，称为可复用的位置。
• 如果之后要插入 ID 在 300 到 700 间的记录时，就会复用该位置。
• 由此可见，磁盘文件的大小并不会减少。

原因：MySQL InnoDB 中采用了 B+ 树作为存储数据的结构，也就是常说的索引组织表，并且数据时按照页来存储的。具体可以参考之前写过的博客《数据库系列课程（17）-索引实现原理（小结）》

3. 如何解决？ 3.1 optimize

可以使用OPTIMIZE TABLE来回收未使用的空间，并整理数据文件的碎片，语法如下：

OPTIMIZE TABLE 表名;

注意：OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

另外，也可以执行通过ALTER TABLE重建表

ALTER TABLE 表名 ENGINE=INNODB

3.1 optimize 与alter的区别

• alter table t engine = InnoDB等于recreate）
• optimize table t 等于recreate + analyze

4. Online DDL

最后，再说一下Online DDL，dba的日常工作肯定有一项是ddl变更，ddl变更会锁表，这个可以说是dba心中永远的痛，特别是执行ddl变更，导致库上大量线程处于“Waiting for meta data lock”状态的时候。因此在 5.6 版本后引入了 Online DDL。

Online DDL推出以前，执行ddl主要有两种方式copy方式和inplace方式。inplace方式又称为(fast index creation)。相对于copy方式，inplace方式不拷贝数据，因此较快。但是这种方式仅支持添加、删除索引两种方式，而且与copy方式一样需要全程锁表，实用性不是很强。Online方式与前两种方式相比，不仅可以读，还可以支持写操作。

执行online DDL语句的时候，使用ALGORITHM和LOCK关键字，这两个关键字在我们的DDL语句的最后面，用逗号隔开即可。示例如下：

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM选项：

• INPLACE：替换：直接在原表上面执行DDL的操作。
• COPY：复制：使用一种临时表的方式，克隆出一个临时表，在临时表上执 DDL，然后再把数据导入到临时表中，在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间，表不允许DML的操作。
• DEFAULT：默认方式，有MySQL自己选择，优先使用INPLACE的方式。

LOCK选项:

• SHARE：共享锁，执行DDL的表可以读，但是不可以写。
• NONE：没有任何限制，执行DDL的表可读可写。
• EXCLUSIVE：排它锁，执行DDL的表不可以读，也不可以写。
• DEFAULT：默认值，也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为DEFAULT，那就是交给MySQL子句去觉得锁还是不锁表。不建议使用，如果你确定你的DDL语句不会锁表，你可以不指定lock或者指定它的值为default，否则建议指定它的锁类型。

执行DDL操作时，ALGORITHM选项可以不指定，这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT，也是同样的效果。如果指定了ALGORITHM选项，但不支持的话，会直接报错。

OPTIMIZE TABLE 和ALTER TABLE 表名 ENGINE=INNODB都支持Oline DDL，但依旧建议在业务访问量低的时候使用。