mysql-innodb数据存储结构

莫等闲！

已于 2023-07-15 10:51:07 修改

阅读量181

点赞数

分类专栏：数据库文章标签： mysql 数据库 java

于 2023-03-07 17:15:59 首次发布

本文链接：https://blog.csdn.net/weixin_48155735/article/details/129380870

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1.innodb 中页的结构

名称	占用大小	说明
File Header	38B	文件头，描述页的信息
Page Header	56B	页头,页的状态信息
lnfimum-Supremum	26B	最大和最小记录，这是两个虚拟的行记录
User Records	不确定	用户记录，存储行记录内容
Free Space	不确定	空闲记录，页中还没有被使用的空间
Page Directory	不确定	页目录，存储用户记录的相对位置
File Trailer	8B	文件尾,校验页是否完整

1.1 文件头文件尾

首先是文件通用部分，也就是文件头和文件尾。
① 文件头部信息

校验和

作用：
InnoDB存储引擎以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候断电了，造成了该页传输的不完整。
为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），这时可以通过文件尾的校验和（checksum 值）与文件头的校验和做比对，如果两个值不相等则证明页的传输有问题，需要重新进行传输，否则认为页的传输已经完成。
具体的：
每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了，那么在File Header中的校验和就代表着已经修改过的页，而在File Trailer中的校验和代表着原先的页，二者不同则意味着同步中间出了错。这里，校验方式就是采用 Hash 算法进行校验。

上下页号

FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。

1.2 最大最小空闲空间用户记录

1.2.1用户记录

User Records中的这些记录按照指定的行格式一条一条摆在User Records部分，相互之间形成单链表。
用户记录的一条条记录如何记录？
这里需要讲讲记录行格式的记录头信息

行格式记录头

简化后

行格式记录头字段

delete_mask

标记删除 0->1 1删除

min_rec_mask

record_type

0普通记录，1 目录项记录 2 最小 3最大记录

heap_no

当前记录在本页中的位置。
0 当前页的最小记录，1 当前页的最大记录

n_ownd

每组元素记录

next_record

删除操作：
从表中删除掉一条记录，这个链表也是会跟着变化：
mysql> DELETE FROM page_demo WHERE c1 = 2;

从图中可以看出来，删除第2条记录前后主要发生了这些变化：

第2条记录并没有从存储空间中移除，而是把该条记录的delete_mask值设置为1。
第2条记录的next_record值变为了0，意味着该记录没有下一条记录了。
第1条记录的next_record指向了第3条记录。
最大记录的n_owned值从 5 变成了 4 。

所以，不论我们怎么对页中的记录做增删改操作，InnoDB始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的。

添加操作：
主键值为2的记录被我们删掉了，但是存储空间却没有回收，如果我们再次把这条记录插入到表中，会发生什么事呢？
mysql> INSERT INTO page_demo VALUES(2, 200, ‘tong’);

当数据页中存在多条被删除掉的记录时，这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表，以备之后重用这部分存储空间。

1.2.2 最大最小记录

页中的最大最小记录
这两条记录不是我们自己定义的记录，所以它们并不存放在页的User Records部分

1.3 页目录页面头部

为什么需要页目录
页中的，记录以单链表的形式存储。引入页目录，可以通过二分查找的方式进行检索，提高效率。
将所有记录进行分组。

将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录。
第 1 组，也就是最小记录所在的分组只有 1 个记录；
最后一组，就是最大记录所在的分组，会有 1-8 条记录；
其余的组记录数量在 4-8 条之间。
这样做的好处是，除了第 1 组（最小记录所在组）以外，其余组的记录数会尽量平分。
在每个组中最后一条记录的头信息(行格式记录头信息的n_owned字段)中会存储该组一共有多少条记录，作为 n_owned 字段。
页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

页目录结构下，如何快速查找数据

现在看怎么从这个页目录中查找记录。因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用二分法来进行快速查找。5个槽的编号分别是：0、1、2、3、4，所以初始情况下最低的槽就是low=0，最高的槽就是high=4。比方说我们想找主键值为6的记录，过程是这样的：

计算中间槽的位置：(0+4)/2=2，所以查看槽2对应记录的主键值为8，又因为8 > 6，所以设置high=2，low保持不变。
重新计算中间槽的位置：(0+2)/2=1，所以查看槽1对应的主键值为4，又因为4 < 6，所以设置low=1，high保持不变。
因为high - low的值为1，所以确定主键值为6的记录在槽2对应的组中。此刻我们需要找到槽2中主键值最小的那条记录，然后沿着单向链表遍历槽2中的记录。
但是我们前边又说过，每个槽对应的记录都是该组中主键值最大的记录，这里槽2对应的记录是主键值为8的记录，怎么定位一个组中最小的记录呢？别忘了各个槽都是挨着的，我们可以很轻易的拿到槽1对应的记录（主键值为4），该条记录的下一条记录就是槽2中主键值最小的记录，该记录的主键值为5。所以我们可以从这条主键值为5的记录出发，遍历槽2中的各条记录，直到找到主键值为6的那条记录即可。
由于一个组中包含的记录条数只能是1~8条，所以遍历一个组中的记录的代价是很小的。

小结：
在一个数据页中查找指定主键值的记录的过程分为两步：

通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
通过记录的next_record属性遍历该槽所在的组中的各个记录。

2.innodb 行格式

InnoDB存储引擎设计了4种不同类型的行格式，分别是Compact（紧密）、Redundant（冗余）、Dynamic（动态）和Compressed（压缩）行格式。

2.1 compact 行格式

一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分。

NULL值列表

数据需要对齐， 1代表该列的NULL,0代表该列不为NULL

例如：字段 a、b、c，其中a是主键，在某一行中存储的数依次是 a=1、b=null、c=2。那么Compact行格式中的NULL值列表中存储：01。第一个0表示c不为null，第二个1表示b是null。这里之所以没有a是因为数据库会自动跳过主键，因为主键肯定是非NULL且唯一的，在NULL值列表的数据中就会自动跳过主键。

记录的真实数据

3个隐藏的列

实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。

一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。
事务ID和回滚指针在后面的《第14章_MySQL事务日志》章节中讲解。

2.2 dynamic 和 Compressed

问题 varchar(100)和varchar(10)

1、varchar(100)和varchar(10)的区别在哪里？
结果是否定的。虽然他们用来存储90个字符的数据，其存储空间相同。但是对于内存的消耗是不同的。对于VARCHAR数据类型来说，硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的，但是对于内存来说，则不是。其时使用固定大小的内存块来保存值。简单的说，就是使用字符类型中定义的长度，即200个字符空间。显然，这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响。解释可以参见这里。如果不想看解释，我这里大概说下：假设VARCHAR(100)与VARCHAR(200)类型，实际存90个字符，它不会对存储端产生影响（就是实际占用硬盘是一样的）。但是，它确实会对查询产生影响，因为当MySql创建临时表（SORT，ORDER等）时，VARCHAR会转换为CHAR，转换后的CHAR的长度就是varchar的长度，在内存中的空间就变大了，在排序、统计时候需要扫描的就越多，时间就越久。

总结：
二者在磁盘上存储占的空间是一样的。区别有二。第一、一个变长一个固定长度。第二、在内存中的操作方式，varchar也是按照最长的方式在内存中进行操作的。比如说要进行排序的时候，varcahr(100)是按照100这个长度来进行的。

行溢出问题

#数据库 一行最大字节65535 BYTE 个 
#  用了varchar 2 Byte 记录 长字段长度 
varchar(65532)
	  65535=65532
 #(可用)+2(2个字节的变长字段的长度)+1(1个字节null标识)
#	 数据库 一行最大字节65535 BYTE 个，
# 所以极限情况下，只一个字段 varchar
varchar(65533)  not null
	 65535=65533+2 (2个字节的变长字段的长度)
	# utf8 一个字符3个字节
	# utf8mb4 一个字符 4个字节
create table varchardemo{
	c carchar(65532)
	 # 65535 -2字节记录变长字段的长度-1NULL标识。
}charset=ascii row_format=compact;

create table varchardemo{
	c carchar(65533) not null
	 # 65535 -2字节记录变长字段的长度
}charset=ascii row_format=compact;

一页是16kB 也就是16384B < 65533 B varchar() 最大。
这样就可能出现一个页存放不了一条记录，这种现象称为行溢出。

在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中进行**分页存储**，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。

dynamic 和compressed 描述

在MySQL 8.0中，默认行格式就是Dynamic，Dynamic、Compressed行格式和Compact行格式挺像，只不过在处理行溢出数据时有分歧：

Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。如图，在数据页中只存放20个字节的指针（溢出页的地址），实际的数据都存放在Off Page（溢出页）中。
Compact和Redundant两种格式会在记录的真实数据处存储一部分数据（存放768个前缀字节）。
Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储。

3.区、段、碎片区、表空间

3.1 区

降低从磁盘读取数据的时间。

页双向指针，在页文件头部有两个指针
再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/0是非常慢的(寻道，半圈旋转速度慢)，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/0 （读取速度特别快）。
引入区的概念，一个区就是在物理位置上连续的64个页。
因为InnoDB 中的页大小默认是16KB，所以一个区的大小是64*16KB=1MB。

3.2 段

引入叶子节点有自己独有的区，非叶子节点也有独立的区。
方便进行范围查询。
存放叶子节点的区的集合是一个段，非叶子节点的区的集合也是一个段。那么一个索引会生成2个段，一个是叶子节点段，一个是非叶子节点段。

3.3碎片区直属表空间

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M (64*16Kb=1024Kb）存储空间，所以默认情况下一个只存了几条记录的小表也需要2M的存储空间么?

为了考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况，InnoDB提出了一个碎片(fragment)区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。

所以此后为某个段分配存储空间的策略是这样的: