MySQL Delete 表数据后，磁盘空间并未释放，为什么?

有开发小哥咨询了一个问题，记录一下处理过程分享给有需要的朋友。

问题如下：

MySQL数据库中有几张表增删比较频繁、数据变动剧烈且数据量大，导致数据增长过快，磁盘占用多。为了节约成本，定期进行数据备份，并通过delete删除表记录，但是执行delete操作后发现磁盘空间并未释放，这是为什么？

MySQL数据结构

MySQL数据库中的表，一般为表结构和表数据。表结构占用空间都是比较小的，一般都是表数据占用的空间。

InnoDB 中采用了 B+ 树作为存储数据的结构，也就是常说的索引组织表。在InnoDB中，delete操作并不会真的删除数据，MySQL实际上只是给要删除的数据打了标记，标记为删除。磁盘所占空间不会变小，即表空间并没有真正被释放。

这样的设计是因为：如果在磁盘上移除之后，很多其它的记录需要在磁盘上重新排列，这会消耗大量的性能。(例如：一个大表，存在索引，删除了其中一行，那么整个索引结构就会发生变化，随之而来的改变索引结构，必将带来磁盘IO)

所有被删除的记录会组成一个垃圾链表，这个链表记录占用的空间叫可重用空间。新插入的记录可覆盖此空间。

比如想要删除 R4 这条记录：

 

 

 

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

通用删除整页数据也将记录标记删除，数据就复用用该位置，与删除默写记录不同的是，删除整页记录，当后来插入的数据不在原来的范围时，都可以复用位置，而如果只是删除默写记录，是需要插入数据符合删除记录位置的时候才能复用。

因此，无论是数据行的删除还是数据页的删除，都是将其标记为删除的状态，用于复用，所以文件并不会减小。

数据空洞

这些被标记为删除的记录，就是数据空洞。不仅浪费空间，还影响查询效率。

MySQL底层是以数据页为单位来存储和读取数据的，每次向磁盘读一次数据就是读一个数据页，每访问一个数据页就对应一次IO操作，磁盘IO访问速度是很慢的。

如果一个表上存在大量的数据空洞，原本只需要一个数据页就保存的数据，由于被很多空洞占用了空间。不得不增加其它数据页来保存数据，相应的MySQL在查询相同数据的时候，就不得不增加磁盘IO操作，从而影响查询速度。

不仅删除会造成数据空洞，插入和更新同样会造成数据空洞。因此一个表在经过大量频繁的增删改后，难免会产生数据空洞，影响查询效率。在生产环境中直接表现为原本查询很快的表变的越来越慢。

那怎么才能让表大小变小

可以使用OPTIMIZE TABLE来回收未使用的空间，并整理数据文件的碎片。

OPTIMIZE TABLE 表名;

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

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

ALTER TABLE 表名 ENGINE=INNODB

有人会问OPTIMIZE TABLE和ALTER TABLE有什么区别？

alter table t engine = InnoDB（也就是recreate），而optimize table t 等于recreate+analyze

Online DDL

DBA的日常工作肯定有一项是ddl变更，ddl变更会锁表，这个可以说是dba心中永远的痛，因此在 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，否则建议指定它的锁类型。

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