存储过程mysql_MySQL表空间回收的正确姿势

不知道你有没有遇到过这样的情况。线上业务对MySQL表进行增删改查操作。随着时间的推移，表中的数据越来越多，表数据文件也越来越大。数据库占用的空间自然逐渐增大

为了减少表数据文件对磁盘的占用空间，我们使用命令将最大的业务表中的旧数据删除了一半。删除后，磁盘上的表数据文件并没有收缩。即使删除整个表数据，文件大小仍然没有变小，为什么？

本文将详细分析上述问题，并给出回收表空间的正确方法。

初步描述

目前大多数MySQL数据库都使用引擎，所以除非特别说明，本文中的示例都是基于引擎的。

MySQL配置中有一个配置项叫e。设置为1后，

每个表的数据将单独存储在一个后缀为.ibd的文件中。

如果 e 没有打开，

表的数据存储在系统的共享表空间中，因此即使删除表，共享表空间也不会释放这部分空间。

因此，一般情况下，e选项设置为1。同时，为了直观地看到表数据文件的大小变化，文中的例子也是基于开启该选项的。

问题复发

创建一个新表ta。表结构如下

mysql> show create table ta\G
*************************** 1. row ***************************
       Table: ta
Create Table: CREATE TABLE `ta` (
  `id` int(11) NOT NULL,
  `ia` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

使用如下存储过程批量向ta中插入数据

delimiter //
create procedure multinsert(in beg int,in cnt int)
begin
	declare icnt int default 0;
	declare tmp int default 0;
	while icnt < cnt do
		set icnt = icnt + 1;
		set tmp = beg + icnt;
		insert into ta(id,ia) values(tmp,tmp);
	end while;
end//
delimiter ;

在MySQL控制台执行call(0,)命令，向ta表插入10万条数据。

mysql> call multinsert(0,100000);
mysql> select count(*) from ta;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.02 sec)

查看磁盘上表ta的数据文件ta.ibd的大小

[root@ecs-centos-7 test]# cd /var/lib/mysql/test/
[root@ecs-centos-7 test]# ls -l ta.ibd 
-rw-r----- 1 mysql mysql 11534336 1月   3 23:14 ta.ibd

从上面的结果可以知道，向ta表插入10万条数据后，ta.ibd的大小为bytes（约11M）

现在我们使用命令删除一半的表数据（5万条记录）

mysql> delete from ta where id between 1 and 50000;
Query OK, 10000 rows affected (0.03 sec)
mysql> select count(*) from ta;
+----------+
| count(*) |
+----------+
|    50000 |
+----------+
1 row in set (0.02 sec)

删除操作完成后，再次检查磁盘上ta.ibd的大小。

[root@ecs-centos-7 test]# cd /var/lib/mysql/test/
[root@ecs-centos-7 test]# ls -l ta.ibd 
-rw-r----- 1 mysql mysql 11534336 1月   3 23:14 ta.ibd

从上面的结果可以知道，删除ta表的一半，即5万行数据后，ta.ibd的大小为bytes（约11M）

也就是说，删除表ta数据前后，磁盘上的表数据文件并没有缩小。

要理解为什么数据文件没有缩小，需要对删除数据的原理有更深入的了解。

删除数据原理

我们都知道中的数据是使用B+树来组织的。关于B+树的知识，请参考理解B+树

图（一）

上图是索引的示意图。虚线框出的节点属于Page1数据页。叶子节点存储索引对应的数据。它们按照索引从小到大的顺序形成一个有序数组。

假设我们要删除Page1中索引键值为13的数据，也就是上图中红色部分

引擎会将索引键值为 13 的节点标记为已删除。它不会回收节点的真实物理空间。它只会将其标记为已删除的节点。将来可以重复使用。因此，删除表记录和磁盘数据文件不会收缩

你可能会说，上面只是删除了Page1中一个节点的数据。如果将Page1中的节点数据全部删除，那么Page1的空间应该被回收了吧？

答案是，不会被回收

当第1页上的所有数据被删除时，整个数据页将被标记为已删除，并且整个数据页可以被重新使用。因此，在这种情况下，磁盘上的数据文件仍然不会减少。

数据复用

数据复用涉及数据节点的插入、删除、转移、数据页的合并等操作。具体操作过程可以参考理解B+树，这里不再赘述。

在上图（1）中，索引键值为13的节点被删除后，该节点被标记为可重用。

如果后面插入索引键值为7到18之间的记录，则将重用原来索引键值为13的数据节点。

但是，如果后面插入的记录的索引键值不在7到18之间，则可能无法重用原始索引键值为13的数据节点。

也就是说，数据节点的复用需要索引键值满足一定的范围条件。

图（1）中，Page1数据页的所有数据节点都被删除后，整个Page1页就可以复用了。当插入的记录需要使用新页面时，可以重复使用Page1。

当相邻数据页的利用率较低时，可以将它们合并为其中一个数据页。此时，其他数据页将被释放，并且释放的数据页将变得可重用。是的

哪些操作会导致数据漏洞？

当我们用命令删除一条记录后，我们只需将相应的数据节点标记为已删除并可重用即可。这些等待使用的空数据节点，可以被一一视为数据洞。

当数据被删除时，就会产生数据空洞。这一点之前已经解释过，这里不再重复。

如果按照索引大小按顺序插入数据，此时数据页会比较紧凑，不会出现数据空洞。

如果从索引中间插入，可能会导致页分裂，而分裂后的页可能会出现数据空洞。下图是插入导致页面分裂的示例。

如图所示，分裂前叶子页已经满了，数据排列得非常紧凑。

现在插入了索引键值为15的数据。插入后，Page1页面被拆分为上图中的Page1和Page2两个页面。

拆分后，Page1 上有两个洞。这两个数据节点是可复用的，Page2刚好满了。

更新数据可以看成是先删除再插入，也可能会造成数据空洞。

例如：id是表ta的主键，ta set id = 10 where id = 1语句将id = 1更改为id = 10，相当于先删除id = 1的记录，然后插入id = 10。在这种情况下将创建数据漏洞

但如果是像ta set ia = ia + 1 where id = 1这样不改变主键值的语句，就不会造成漏洞。

因此，更新数据可能会导致数据漏洞

综上所述，表的增删改操作可能会造成数据漏洞，而线上服务会对表进行大量的增删改操作，因此出现数据漏洞的可能性比较高。

如何缩小表空间

由于一张表经过大量不规则的增删改操作，会产生大量的数据空洞。

那么如果我们创建一个与原表结构相同但有数据空洞的新表，然后将旧表中的数据按索引升序插入到新表中，待旧表中的数据全部插入后新表，删除旧表，然后将新表重命名为旧表的名称

由于新表中的叶子节点数据是按顺序添加的，因此页面非常紧凑，页面利用率非常高，所需的页面数量比旧表少很多。这样，旧表中索引的漏洞在新表中就不存在了。 ，新的表数据文件占用的磁盘空间自然会减少，从而达到收缩表空间的目的。

以下是缩小表空间的几种方法。虽然方法不同，但基本原理都是通过重建表来达到目的。

这个操作相当于drop+，先删除表，然后再创建一个同名的新表。当然，在执行table命令之前需要保存一份旧表数据的副本。命令完成后，将此数据导入到新表中。

该操作是遍历旧表主键索引的数据页，由数据页中的记录生成B+树结构，并将其存储在磁盘上的临时文件中。遍历完数据页后，用临时文件替换旧表的数据文件。

从版本.6开始，这个操作就是DDL。需要注意的是，这种方法需要扫描表数据文件，对于大表来说非常耗时。如果是在线服务，就需要避开业务高峰。期间，谨慎操作。

注意：

重建表时，整个表不会被填满。每个页面留下大约10%的数据节点以供后续更新。换句话说，重建后的表实际上并不是最紧凑的。

假设有这样一个过程：重建表t一次，

插入一些数据，但是插入的数据占用了部分保留空间。

这种情况下，如果再次重建表t，可能会出现重建表后占用的空间比之前更大的情况。

概括

本文从一个实际问题出发，重现问题、分析问题、解决问题。每个步骤都进行了详细分析。由于篇幅限制，有些细节并不深入，需要读者自行理解。

原文链接：