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

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

为了节约老本，定期进行数据备份，并通过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...