面试官问我为什么Mysql表数据删除了一

前言

随着表记录的增加,表体积也会越发变大,为了性能考虑,我们可能会尝试通过delete命令,删除部分无用的数据,以期达到减小表体积的效果。不过令人失望的是,即使我们把表中的数据全部删除,表的体积也不会有丝毫的减少。

今天我们来聊聊,缩小表体积的正确姿势。

Mysql是如何删除数据的

我们知道,Mysql中的数据是以B+树的形式存放的,当我们删除一条数据时,Mysql的innodb引擎找到这条记录后,只会将这条记录所在的位置标记为删除,而不会真的删除磁盘上的文件。当有新的记录插入时,可能会复用这个位置。

另外,innodb的数据是按数据页的形式存放的,如果整个页上的数据都被删除了,那这个页就会被标记为可复用了。

所以,即使我们使用delete命令删除整个表的记录,也仅仅是所有的数据页被标记为可复用,但是磁盘上的文件,并不会变小。

换句话说,通过delete命令是无法回收表空间的。

进一步的,当我们多次使用delete命令删除数据后,大量的数据页上会出现很多可以复用而没有被使用的空洞。

当然,除了删除数据会造成空洞,插入、更新数据也会。

总得来说,经过大量增删改的表,基本都会存在很多的数据空洞。如果我们想办法减少这些空洞,自然可以节省大量的表空间。

重建表

重建表的逻辑也很简单:新建一个和原表结构一样的表,将原表中的数据,按照主键递增的方式,把数据一行一行地从原表读出,然后写入新表。

在新表中,主键索引更加紧凑,空间利用率更高,体积自然也更小。

重建表的命令也很简单:

altertabletengine=InnoDB;

需要注意的是,Mysql5.5版本之前,这个DDL命令不是online的,在执行此命令时,务必要选择业务低峰期。

从5.6版本开始,此命令已被优化为onlineDDL。

onlineDDL的流程是这样的:

1,扫描原表中的所有数据,生成一颗新的B+树,将其存入一个临时文件。

2,存入临时文件的过程中,如果有对原表的增删改操作,将这些操作存入一个日志文件。

3,临时文件生成完以后,将日志中的操作重放到临时文件中,这样就得到了一个与原表一样的数据文件。

4,用临时文件替换原表的数据。

由于日志文件的存在,在重建表的过程中,原表仍然是可以操作的,所以可以称之为onlineDDL。

总结

如果我们要对一个表缩容,仅仅通过delete命令删除数据是不行的,需要使用altertable命令进行表重建。altertable在5.6之后是onlineDDL,但是会消耗大量的IO及CPU,所以建议在业务低峰期使用此命令。5.5之前的版本,建议业务停止后再进行操作。

我是

程序员拾山,欢迎


转载请注明:http://www.aierlanlan.com/rzgz/7391.html

  • 上一篇文章:
  •   
  • 下一篇文章: