Mysql 表空间和数据页空洞

我就是我 2022-04-04 02:57 137阅读 0赞

### 独立表空间 ###

表空间： 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_test` 表 `1-180`行中的 `155-180 行` ，`ibd文件` 原始大小为 `144KB`，当我们删除 150-160 这时候，数据页就有了 10条ID 的空洞 ，此时表中ID是 ： `0-149 151 -180` 共计 170条， 那么这时候，插入20条 还是 10条加数据页呢 ？

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