MySQL表空间收缩

shysh95

发布于 2022-02-16 21:31:57

3.6K0

发布于 2022-02-16 21:31:57

文章被收录于专栏：shysh95shysh95

InnoDB表的两部分主要由啥构成？

表结构定义
数据

表结构定义存放在哪里？

MySQL 8.0以前，存放在以.frm为后缀的文件里
MySQL 8.0以后，表结构定义存放在系统数据表中


--查看test库表t的信息
desc test.t;
show columns from test.t;

-- 以下命令在information_schema库执行
-- 查看test库下面表t的列信息
select * from COLUMNS where TABLE_SCHEMA = 'test' and TABLE_NAME = 't';

表数据存放在哪里？

表数据的存放位置由参数innodb_file_per_table控制：

设置为OFF，表的数据存放在系统共享表空间（ibdata为前缀的文件）
设置为ON，表数据存储在一个以.ibd为后缀，以表名为前缀的文件中

-- 查看innodb_file_per_table参数
show global variables like  'innodb_file_per_table';

表数据单独存放在一个文件的好处是？

表数据单独存放成一个文件更容易管理，在我们执行drop table命令的时候，系统会直接删除这个文件，但如果是放在共享表空间中，即使表删掉空间也不会回收。

InnoDB的标记删除？

首先再明确一点，InnoDB里面的数据都是用B+数的结构进行组织的。

假设我们删除了R5这条记录，InnoDB引擎只会将R5这条记录标记为删除，并不是将页中该位置的数据置空，磁盘文件大小并不会缩小，这就是标记删除。

后续如果插入一个ID在3和6之间的数据时就会复用该位置，如果整个页上的所有记录被标记为删除，那么整个数据页就可以被复用了。

InnoDB如果发现相邻的两个页利用率比较小，就会将其中两个页的数据合并到其中一个页上，另一个页就可以复用了。

通过上述可以说明，即使我们使用delete命令将整个表的数据删除，所有的数据页虽然可以被复用，但是磁盘文件不会缩小。这些可以复用而没有被使用的空间称为空洞。

除了删除数据会造成空洞，如果数据是按照索引递增顺序插入索引是紧凑的，但是如果数据是随机插入就可能会造成页分裂，形成数据空洞。

如何减少空洞，收缩表空间？

重建表。

如何重建表？

alter table t engine=InnoDB;

MySQL5.6以前上面的DDL不是Online的，在5.6以后做了优化成了Online，5.6以后上述语句重建表的流程如下：

建立一个临时文件，扫描表t主键的所有数据页
用数据页中表t的记录生成B+树，存储到临时文件中
生成临时文件的过程中，将所有对表t的操作记录在一个日志(row log)文件中
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表t相同的数据文件
用临时文件替换表t的数据文件

上述alter语句虽然在启动的时候会获取MDL写锁(阻塞增删改查)，但MDL写锁在拷贝数据之前会退化成读锁(不会阻塞增删改查)，整个DDL过程拷贝数据是最为耗时的，其他过程可以忽略不计，因此可以认为该DDL是Online的。

在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。也就是说，其实重建表之后不是紧凑无空洞的。

该重建方法的弊端是如果遇到比较大的表，扫描原表数据和构建临时文件是极其消耗IO和CPU资源的。

更加安全的缩小表空间的做法推荐使用gh-ost这款开源工具。

什么是inplace？

上面在重建表的过程中，根据表t创建出来的数据存放的临时文件是在InnoDB引擎内部进行创建，整个DDL过程也都是在InnoDB内部完成，对于Server层来说，并没有把数据挪到一个临时表，是一个原地操作，称之为inplace。

-- alter table t engine=InnoDB;等同于下面这个语句
alter table t engine=innodb,ALGORITHM=inplace;

-- 下面的这个语句是与inplace相反的copy的方式
-- 该方式会强制创建一个临时表
alter table t engine=innodb,ALGORITHM=copy;

Online和Inplace的关系