Scott's world.

MySQL-删表陷阱相关笔记

Word count: 2.8kReading time: 9 min
2020/01/05 Share

MySQL-删表陷阱相关笔记

有时候我们不会碰到这样一个问题

  • 我的数据库占用空间太大,我把一个最大的表删掉了一半的数据,为什么表文件的大小还是没有变化

这次我们就来讨论如何解决这样一个问题,讲述删除表操作中的相关知识

也就是数据库表的空间回收问题

我们以MySQL中常见的InnoDB引擎展开讨论

InnoDB表包含两部分

  • 结构定义
  • 数据

在MySQL 8.0版本以前,表结构是存在以.frm为后缀的文件里。而MySQL 8.0版本,则已经允许把表结构定义放在系统数据表中了。

因为表结构定义占用的空间很小,所以我们今天主要讨论的是表数据。

回到问题本身,即简单地删除表数据达不到表空间回收的效果

首先我们需要了解参数innodb_file_per_table

参数innodb_file_per_table

表数据既可以存在共享表空间里,也可以是单独的文件。

这个行为是由参数innodb_file_per_table控制的:

  • 这个参数设置为OFF表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;
  • 这个参数设置为ON表示的是,每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。

从MySQL 5.6.6版本开始,它的默认值就是ON了

将innodb_file_per_table设置为ON,是推荐做法

为什么这么说呢,因为一个表单独存储为一个文件更容易管理,可通过drop table命令直接删除文件,而如果存储在共享表空间中,即使删除空间也不会回收

我们在删除整个表的时候,可以使用drop table命令回收表空间。但是,我们遇到的更多的删除数据的场景是删除某些行,这时就遇到了我们文章开头的问题:表中的数据被删除了,但是表空间却没有被回收。

这里,让我们再回到删除这个操作开始说起

数据删除流程

我们知道InnoDB的数据都是B+树的结构组织的

img

假设我们要删除R4记录,注意它并不会真正地删除,InnoDB引擎只会把R4这个记录进行标记为删除

如果之后要再插入一个ID在300和600之间的记录时,可能会复用这个位置。但是,磁盘文件的大小并不会缩小,因为数据并没有真正地删除

我们在前面知道InnoDB的数据是按页存储的

那如果我们删除了一个数据页所以记录也会将整个数据页变成可复用了

数据页复用与记录复用的不同

  • 记录的复用,只限于符合范围条件的数据,即复用范围有限
  • 数据页从B+树摘掉后,可以复用到任何位置

如果相邻的两个数据页利用率都很小,系统就会把这两个页上的数据合到其中一个页上,另外一个数据页就被标记为可复用。

进一步地,如果我们用delete命令把整个表的数据删除呢?

结果就是,所有的数据页都会被标记为可复用。但是磁盘上,文件不会变小。

我们由此知道通过delete命令是不能回收表空间的。这些可以复用,而没有被使用的空间,看起来就像是“空洞”。

实际上,不止是删除数据会造成空洞,插入数据也会。

  • 如果数据是按照索引递增顺序插入的,那么索引是紧凑的。但如果数据是随机插入的,就可能造成索引的数据页分裂。

  • 另外,更新索引上的值,可以理解为删除一个旧的值,再插入一个新值。不难理解,这也是会造成空洞的。

经过大量增删改的表,都是可能是存在空洞的。

而如何能够解决空洞这一问题是否说明就能解决收缩表空间的问题

知道问题的缘由,接下来就让我们来解决问题,这里就要开始谈起重建表操作

重建表

当我们使用以下命令来重建表

1
alter table A engine=InnoDB

假如现在有一表A需要做空间收缩,为了把表中存在的空洞去掉

新建一个与表A结构相同的表B,然后按照主键ID递增的顺序,把数据一行一行地从表A里读出来再插入到表B中。

由于表B是新建的表,所以表A主键索引上的空洞,在表B中就都不存在了。显然地,表B的主键索引更紧凑,数据页的利用率也更高。如果我们把表B作为临时表,数据从表A导入表B的操作完成后,用表B替换A,从效果上看,就起到了收缩表A空间的作用。

img

显然,花时间最多的步骤是往临时表插入数据的过程

在MySQL 5.5版本之前,我们不用自己创建临时表表B,MySQL会自动完成转存数据、交换表名、删除旧表的操作。

但是如果在这个过程中,有新的数据要写入到表A的话,就会造成数据丢失。因此,在整个DDL过程中,表A中不能有更新。也就是说,这个DDL不是Online的。

而在MySQL 5.6版本开始引入的Online DDL,对这个操作流程做了优化。

引入了Online DDL之后,重建表的流程:

  • 建立一个临时文件,扫描表A主键的所有数据页;
  • 用数据页中表A的记录生成B+树,存储到临时文件中;
  • 生成临时文件的过程中,将所有对A的操作记录在一个日志文件(row log)中,对应的是图中state2的状态;
  • 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表A相同的数据文件,对应的就是图中state3的状态;
  • 用临时文件替换表A的数据文件。

img

我们可以很明显地看到实现OnlineDDL,起最大作用的便是row log

这里有一个问题比较有趣

  • DDL之前是要拿MDL写锁的,这样还能叫Online DDL吗?

确实,图4的流程中,alter语句在启动的时候需要获取MDL写锁,但是这个写锁在真正拷贝数据之前就退化成读锁了。

为什么要退化呢?为了实现Online,MDL读锁不会阻塞增删改操作。

那为什么不干脆直接解锁呢?为了保护自己,禁止其他线程对这个表同时做DDL。

而对于一个大表来说,Online DDL最耗时的过程就是拷贝数据到临时表的过程,这个步骤的执行期间可以接受增删改操作。所以,相对于整个DDL过程来说,锁的时间非常短。对业务来说,就可以认为是Online的。

需要补充说明的是,上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗IO和CPU资源的。因此,如果是线上服务,你要很小心地控制操作时间。如果想要比较安全的操作的话,我推荐你使用GitHub开源的gh-ost来做。

Online和inplace

说到Online,我还要再和你澄清一下它和另一个跟DDL有关的、容易混淆的概念inplace的区别。

MySQL各版本,对于add Index的处理方式是不同的,主要有三种:

  • Copy Table方式

这是InnoDB最早支持的创建索引的方式。顾名思义,创建索引是通过临时表拷贝的方式实现的。

新建一个带有新索引的临时表,将原表数据全部拷贝到临时表,然后Rename,完成创建索引的操作。

这个方式创建索引,创建过程中,原表是可读的。但是会消耗一倍的存储空间。

  • Inplace方式

这是原生MySQL 5.5,以及innodb_plugin中提供的创建索引的方式。所谓Inplace,也就是索引创建在原表上直接进行,不会拷贝临时表。相对于Copy Table方式,这是一个进步。

Inplace方式创建索引,创建过程中,原表同样可读的,但是不可写。

  • Online方式

这是MySQL 5.6.7中提供的创建索引的方式。无论是Copy Table方式,还是Inplace方式,创建索引的过程中,原表只能允许读取,不可写。对应用有较大的限制,因此MySQL最新版本中,InnoDB支持了所谓的Online方式创建索引。

InnoDB的Online Add Index,首先是Inplace方式创建索引,无需使用临时表。在遍历聚簇索引,收集记录并插入到新索引的过程中,原表记录可修改。而修改的记录保存在Row Log中。当聚簇索引遍历完毕,并全部插入到新索引之后,重放Row Log中的记录修改,使得新索引与聚簇索引记录达到一致状态。

与Copy Table方式相比,Online Add Index采用的是Inplace方式,无需Copy Table,减少了空间开销;与此同时,Online Add Index只有在重放Row Log最后一个Block时锁表,减少了锁表的时间。

与Inplace方式相比,Online Add Index吸收了Inplace方式的优势,却减少了锁表的时间。

使用optimize table、analyze table和alter table这三种方式重建表的区别。

  • 从MySQL 5.6版本开始,alter table t engine = InnoDB(也就是recreate)默认的就是上面图的流程了;
  • analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了MDL读锁;
  • optimize table t 等于recreate+analyze。

问题

假设现在有人碰到了一个“想要收缩表空间,结果适得其反”的情况,看上去是这样的:

  1. 一个表t文件大小为1TB;
  2. 对这个表执行 alter table t engine=InnoDB;
  3. 发现执行完成后,空间不仅没变小,还稍微大了一点儿,比如变成了1.01TB。

你觉得可能是什么原因呢 ?

参考

这个表,本身就已经没有空洞的了,比如说刚刚做过一次重建表操作。

在DDL期间,如果刚好有外部的DML在执行,这期间可能会引入一些新的空洞。

本来就很紧凑,没能整出多少剩余空间。
重新收缩的过程中,页会按90%满的比例来重新整理页数据(10%留给UPDATE使用),
未整理之前页已经占用90%以上,收缩之后,文件就反而变大了。

CATALOG
  1. 1. MySQL-删表陷阱相关笔记
    1. 1.1. 参数innodb_file_per_table
    2. 1.2. 数据删除流程
      1. 1.2.1. 数据页复用与记录复用的不同
    3. 1.3. 重建表
    4. 1.4. Online和inplace
    5. 1.5. 问题
      1. 1.5.1. 参考