乐趣区

关于数据库:面试官为什么-delete-表数据磁盘空间却还是被占用

jiezi

最近面试，还真有一个面试官，问到我之前遇到过的一个线上故障问题解决方案（当面试官说进口时，我暗自庆幸还好我遇到过 ……），如下。

最近有个上位机获取下位机上报数据的我的项目，因为上报频率比拟频繁且数据量大，导致数据增长过快，磁盘占用多。

为了节约老本，定期进行数据备份，并通过 delete 删除表记录。

明明曾经执行了 delete，可表文件的大小却没减小，令人费解

我的项目中应用 Mysql 作为数据库，对于表来说，个别为表构造和表数据。表构造占用空间都是比拟小的，个别都是表数据占用的空间。

当咱们应用 delete 删除数据时，的确删除了表中的数据记录，但查看表文件大小却没什么变动。

Mysql 数据结构

但凡应用过 mysql，对 B + 树必定是有所耳闻的，MySQL InnoDB 中采纳了 B+ 树作为存储数据的构造，也就是常说的索引组织表，并且数据时依照页来存储的。因而在删除数据时，会有两种状况：

  • 删除数据页中的某些记录
  • 删除整个数据页的内容

表文件大小未更改和 mysql 设计无关

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

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

通用删除整页数据也将记录标记删除，数据就复用用该地位，与删除默写记录不同的是，删除整页记录，当起初插入的数据不在原来的范畴时，都能够复用地位，而如果只是删除默写记录，是须要插入数据合乎删除记录地位的时候能力复用。

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

那怎么能力让表大小变小

DELETE 只是将数据标识位删除，并没有整顿数据文件，当插入新数据后，会再次应用这些被置为删除标识的记录空间，能够应用 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

最初，再说一下 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，但仍旧倡议在业务访问量低的时候应用

总结

delete 删除数据时，其实对应的数据行并不是真正的删除，仅仅是将其标记成可复用的状态，所以表空间不会变小。

能够重建表的形式，疾速将 delete 数据后的表变小（OPTIMIZE TABLE 或 ALTER TABLE），在 5.6 版本后，创立表曾经反对 Online 的操作，但最好是在业务低峰时应用。

起源：https://www.toutiao.com/i6935…

退出移动版