Mysql 表空间和 数据页空洞

独立表空间

表空间: innodb 引擎存储的最高层; 存放所有的数据
独立表空间:Mysql 版本 5.6 后默认开启的单表单空间
Innodb 默认存储引擎页的大小为 16K ;默认表空间 大小为96k

独立表空间 开启方式 innodb_file_per_table ON 从Mysql 5.6.6 开始,默认值 ON

如果我们修改了 info表 的表结构 ,比如 给表添加注释,发现

9440 Dec 14 09:59 info.frm

磁盘上的 frm 表结构文件 立刻被更新了。

那我们如果删除一行数据呢? 从文件更改时间上来看也是立刻修改了。

180224 Dec 14 10:03 temp.ibd

但是 idb数据文件 大小 180224却没有改变

那么数据库的删除流程到底是怎么样的? 为什么我们删除一行,存储的ibd文件大小却没有改变,当我们点击 删除表的时候,SQl 引擎发生了什么?

Innodb 的删除

为什么 ibd 文件不会变小?

InnoDb 的数据存储类型是 B+树 ,B+树的叶子结点上 ,存储的是 数据页 ; 一页数据页存储的是 数据记录,假设一条数据记录占用 200字节 ,一页数据页大小为 16Kb ,那么一页可以存储80条数据记录 ,当某条数据记录,比如 ID = 300 的数据记录被删除了,实际上,引擎只会标记这条数据记录为删除,而不会将数据记录从磁盘上抹去,所以磁盘文件上的 ibd 文件不会缩小

一个数据页 Page A 上,总是存储着 ID从 X 到 X+N 的 数据记录, 如果 X 到 X+N 之间的数据记录被删除了,会留下一个数据空洞,那么插入的X 到 X+N 区间之间,且不在PageA上,就会复用这个数据页, 所以文件大小不会减少

如果删除了1整个数据页的所有记录,那么这整个数据页都会被复用,

所以删除和插入,其实是在数据页上,产生和填补空洞 的过程 ,经过删除更新插入的表,都可能会产生大量的空洞。

整理数据页的空洞

那么,当一张表被更新插入,产生了数据空洞之后,如何整理数据页,使得数据变得更紧凑呢?可以采取重建表的操作 alter table,实际上这里是 引擎自动完成了 转存数据、交换表名、删除旧表的操作

mysql> alter table temp engine=innodb;  

163840 Dec 14 10:52 temp.ibd

测试数据页的增长效果

新建一张 new_test 表 
CREATE TABLE `new_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=191 DEFAULT CHARSET=utf8mb4;

我们知道 初始 ibd文件 的大小默认是 96K ,inndb 默认单个数据页大小是16K,对 new_test 表进行数据增加,观察 ibd文件大小。

insert into new_test (name) value("leon”)

发现 当rows 增长时,ibd文件大小增长规律如下:

idb文件大小 VS rows
96k – 0~81
128k – 82~136
144k – 136~190
160k – 190~255

16K 增长对应 54条rows ,1条row算出来约等于 296.29b , 正好等于 char 255b + int(11) 44 = 299b 。

可以看出来的确是由连续的数据页组成数据的。

数据空洞的复用

为了数据空洞的复用的问题,我们来删除 new_test1-180行中的 155-180 行ibd文件 原始大小为 144KB,当我们删除 150-160 这时候,数据页就有了 10条ID 的空洞 ,此时表中ID是 : 0-149 151 -180 共计 170条, 那么这时候,插入20条 还是 10条加数据页呢 ?

测试可得。插入20条,ID到 201。表空间变为 160k,所以原来的数据页存在空洞 可以被复用

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页