乐趣区

关于mysql:delete-清空表之后磁盘空间未发生变化

上篇文章结尾和小伙伴们留了一个小问题,就是对于 optimize table 命令,明天我想花点工夫再来和小伙伴们聊一聊这个话题。

1. 删除空洞

1.1 案例展现

首先咱们先来看这样一个例子。

我当初有一个名为 sakila 的数据库,该库中有一个 film 表,这个表中有 1000 条记录,我么先来看下这 1000 条记录占用了多少存储空间:

小伙伴们能够看到,这个文件大小是 360448 个字节。

咱们当初执行 delete 命令将这个表清空:

delete from film;

而后再来查看这个文件的大小:

小伙伴们看到,这个表中的数据没有缩小,甚至还减少了!这是咋回事?

1.2 剖析

以下所说的删除皆指通过 delete 命令删除,不包含通过 truncate/drop 删除。

MySQL 中的数据删除操作有点像咱们素日里做业务开发时用的逻辑删除,当你想要删除掉一行数据的时候,这行数据其实并没有被真正的删除掉,只是临时给标记为删除了而已。

通过后面文章的介绍,小伙伴们应该曾经分明,MySQL 表中的数据最终是以 B+Tree 的模式保留在磁盘中的,当你要删除一条记录的时候,那么对应的叶子上的数据就会被标记为已删除,相似上面这样:

当 ID 为 6 的记录被删除掉之后,这块空间并不会立马被释放出来,MySQL 只是在这个地位做一个删除标记,未来要是还有一个 ID 为 6 的数据被插入进来,就会插入到这里。

因而咱们看到,一张表在通过 N 屡次删除之后,就会呈现大量这种状况,这种就称之为删除空洞。

2. 插入空洞

后面所说的删除会造成空洞,其实插入也会造成空洞。

松哥在之前的文章中和小伙伴们分享过,InnoDB 引擎的表中不倡议应用随机字符串作为 ID,因为随机字符串插入会造成页决裂。页决裂之后,在决裂之前的叶子中,也有可能会空进去新的空间,造成空洞。

例如上面这个例子:

在上图这个 B+Tree 中,持续插入 5,就会造成页决裂,页决裂之后,2 所在的数据页(InnoDB 操作磁盘的最小单位是数据页)就会有空余,这也是空洞的一种。

当然更新索引上的值也会造成空洞,因为更新相当于插入 + 删除。

3. optimize table

想要解决这个问题,咱们能够应用 optimize table 命令来实现。该命令能够用来从新整理表空间,并优化文件碎片。接下来咱们针对后面 1.1 大节中的案例,来试试 optimize table 命令是否无效:

这下面有一句提醒,说 Table does not support optimize, doing recreate + analyze instead,看这个意思,仿佛是说以后这个 InndoDB 引擎的表不反对 optimize 操作,不过咱们不必管,咱们当初去查看表文件大小:

能够看到,表文件数据其实曾经缩小了。

那么这句提醒是咋回事呢?

咱们以 MySQL 官网文档介绍为准来看下:

从这段话中能够看到,在 InnoDB 中应用 optimize 命令,相干的操作最终会被映射为 alter table ...,这个操作松哥在上篇文章中和小伙伴们介绍过了,这也能够实现索引的重整并且开释掉未应用的空间,所以,网上有人说 optimize table 命令不适用于 InnoDB 引擎的表这个说法是不正确的。

同时,官网文档中这段介绍还提到了 optimize 操作是 online DDL 的。online DDL 意味着在执行 optimize 重整表的时候,并不会阻塞正在进行的 CURD 操作。具体流程如下:

  1. 首先建设一个临时文件,这个临时文件用来扫描表原始表主键的所有数据页。
  2. 依据第一步获取到的表记录生成一个 B+Tree,将这个生成的 B+Tree 存储到临时文件中。
  3. 因为第二步会比拟耗时,在第二步执行过程中,如果有针对原始表的 CRUD 操作,则先将操作记录到一个日志文件中,等到第二步的临时文件生成后,在把日志文件利用到临时文件中,就能够获取到一个最新的数据表了。

好啦,这就是对于 optimize table 的操作细节,小伙伴们 GET 到了吧~

退出移动版