第07章_InnoDB数据存储结构

1. 数据库的存储结构：页

索引结构给我们提供了高效的索引方式,不过索引信息以及数据记录都是保存在文件上的，确切说是存储在页结构中。另一方面，索引是在存储引擎中实现的, MySQL服务器上的存储引擎负责对表中数据的读取和写入工作。
不同存储引擎中存放的格式一般是不同的, 甚至有的存储引擎比如Memory都不用磁盘来存储数据。由于InnoDB是MySQL的默认存储引擎，所以本章剖析InnoDB存储引擎的数据存储结构。

1.1 磁盘与内存交互基本单位：页

InnoDB将数据划分为若干个页，InnoDB中页的大小默认为16KB。

==以页作为磁盘和内存之间交互的基本单位==，也就是一次最少从磁盘中读取16KB的内容到内存中， 一次最少把内存中的16KB内容刷新到磁盘中。也就是说，==在数据库中，不论读一行,还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页(Page) ，数据库I/0操作的最小单位是页。-一个页中可
以存储多个行记录。==

记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取(也就是一次I/O操作)只能处
理一行数据，效率会非常低。

image-20230414094053928

1.2 页结构概述

==页a、页b、页c ..页n这些页可以不在物理结构上相连==，其是通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表,每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

1.3 页的大小

不同的数据库管理系统（简称DBMS）的页大小不同。比如在 MySQL 的 InnoDB 存储引擎中，默认页的大小是 16KB，我们可以通过下面的命令来进行查看：

show variables like '%innodb_page_size%'

image-20230414093555421

SQL Server 中页的大小为 8KB，而在 Oracle 中我们用术语 “块“ （Block）来表示 “页”，Oracle 支持的快大小为2KB, 4KB, 8KB, 16KB, 32KB 和 64KB。

1.4 页的上层结构

另外在数据库中，还存在着区（Extent）、段（Segment）和表空间（Tablespace）的概念。行、页、区、段、表空间的关系如下图所示：

区(Extent) 是比页大一级的存储结构， 在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是6KB,所以一个区的大小是64* 16KB= 1MB。

段(Segment) 由一个或多个区组成，区在文件系统是一个连续分配的空间 (在InnoDB中是连续的64个页) ,
不过在段中不要求区与区之间是相邻的。段 是数据库中的分配单值，不同类 型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段, 创建一个索引时会创建一个索引段。

表空间(Tablespace) 是一个逻辑容器,表空间存储的对象是段,在一个表空间中可以有一个或多个段,但是一
个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间 等。

2. 页的内部结构

2.1页的内部结构概述

页如果按类型划分的话，常见的有 数据页（保存B+树节点）、目录页、系统页、Undo 页 和 事务数据页 等。数据页是我们最常使用的页。

数据页的 16KB 大小的存储空间被划分为七个部分，分别是文件头（File Header）、页头（Page Header）、最大最小记录（Infimum + supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Tailer）。

页结构的示意图如下所示：

image-20230414095413685

如下表所示：

image-20230414095424589

我们可以把这7个结构分为3个部分。

2.2File Header&File Trailer

  1. File Header(文件头)

​ 文件头的作用：描述各种页的通用数据（比如页的编号，其上一页，下一页）

​ 大小：38字节

​ 构成：

FIL_PAGE_OFFSET：每一个页都有一个单独的页号，就跟你的身份证号码一样，InnoDB通过页号可以唯一定位一个页。

FIL_PAGE_TYPE：代表当前页的类型

FIL_PAGE_PREV和FIL_PAGE_NEXT：InnoDB都是以页为单位存放数据的，如果数据分散到多个不连续的页中存储的话需要把这些页关联起来，FIL_ PAGE_ PREV和FIL_ PAGE_NEXT就分别代表本页的上一个和下
一个页的页号。 这样通过建立一个双向链表把许许多多的页就都串联起来了 ，保证这些页之间不需要是物理上的连续，而是逻辑上的连续。

FIL_PAGE_SPACE_OR_CHKSUM：代表当前页面的校验和( checksum)。

什么是校验和?

就是对于一个很长的字节串来说，我们会通过某种算法来计算-个比较短的值来代表这个很长的字节串，这个比较短的值就称为校验和。在比较两个很长的字节串之前，先比较这两个长字节串的校验和，如果校验和都不一-样，则两个长字节串肯定是不同的，所以省去了直接比较两个比较长的字节串的时间损耗。

FIL_PAGE_LSN：页面被最后修改时对应的日志序列位置(英文名是: Log Sequence Number)

  1. File Trailer(文件尾)
  • 前4个字节代表页的校验和。这个部分是和FileHeader中的校验和相对应的。
  • 后4个字节代表页面被最后修改时对应的日志序列位置(LSN):
    这个部分也是为了校验页的完整性的，如果首部和尾部的LSN值校验不成功的话，就说明
    同步过程出现了问题。

2.3User Records 、Infimum + supremum、Free Space

页的主要作用是存储记录，所以“最大和最小记录”和“用户记录”部分占了页结构的主要空间。

  1. Free Space(空闲空间)

我们自己存储的记录会按照指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，==每当我们插入一.条记录，都会从FreeSpace部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到UserRecords部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意
味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。==

  1. User Records(用户记录)

User Records中的这些记录按照指定的行格式一条一条摆在User Records部分，相互之间形成单链表。

用户记录里的一条条数据如何记录?这里需要讲讲记录行格式的记录头信息。

  1. Infimum + supremum(最大最小记录)

记录可以比较大小吗？

是的，记录可以比大小，对于一条完整的记录来说，比较记录的大小就是比较主键的大小。比方说我们插入的4行记录的主键值分别是：1、2、3、4，这也就意味着这4条记录是从小到大依次递增。

InnoDB规定的最小记录与最大记录这两条记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定的部分组成的，如图所示：

image-20230414112322156

这两条记录不是我们自己定义的记录，所以它们并不存放在页的User Records部分，他们被单独放在一个称为Infimum + Supremum的部分，如图所示：

image-20230414112504023

2.4Page Directory&Page Header

  1. Page Directory(目录页)

在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。==因此在页结构中专门设计了页目录这个模块，专门给记录做一个目录，通过二分查找法的方式进行检索，提升效率。==

  1. Page Header(页头部)

==为了能得到一个数据页中存储的记录的状态信息，比如本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等，特意在页中定义了一个叫Page Header的部分，这个部分占用固定的56个字节，专门存储各种状态信息。==

2.3 从数据库页的角度看B+树如何查询

一颗B+树按照字节类型可以分为两部分：

  1. 叶子节点，B+ 树最底层的节点，节点的高度为0，存储行记录。
  2. 非叶子节点，节点的高度大于0，存储索引键和页面指针，并不存储行记录本身。

image-20230414122937560

当我们从页结构来理解 B+ 树的结构的时候，可以帮我们理解一些通过索引进行检索的原理：

  1. B+树是如何进行记录检索的?
    通过B+树的索引查询行记录，首先是从B+树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，目录页中的槽(slot) 采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找记录。

  2. 普通索引和唯一索引在查询效率上有什么不同?
    我们创建索引的时候可以是普通索引，也可以是唯一索引，那么这两个索引在查询效率上有什么不同呢?

    唯一索引就是在普通索引上增加了约束性，也就是关键字唯一， 找到了关键字就停止检索。

    普通索引，可能会存在用户记录中的关键字相同的情况，根据页结构的原理，当我们读取一条记录的时候,不是单独将这条记录从磁盘中读出去，而是将这个记录所在的页加载到内存中进行读取。InnoDB 存储引擎的页大小为16KB,在一个页中可能存储着上千个记录，因此在普通索引的字段上进行查找也就是在内存中多几次“判断下一条记录”的操作。对于CPU来说，这些操作所消耗的时间是可以忽略不计的。所以对一个索引字段进行检索,采用普通索引还是唯一索引在检索效率上基本上没有差别。

3. InnoDB行格式 (或记录格式)

InnoDB的行格式一共分为四种，分别为指定行格式的语法，COMPACT行格式，Dynamic和Compressed行格式以及Redundant行格式.

查看MySQL系统默认的行格式：

SELECT @@innodb_default_row_format;

image-20230414123920655

其中COMPACT行格式就可以是来实际存储记录的格式，其主要分为以下结构：

image-20230414110859236

记录头信息中，如创建一个表：

mysql> CREATE TABLE page_demo(  
->   c1 INT,  
->   c2 INT,  
->   c3 VARCHAR(10000),  
->   PRIMARY KEY (c1)  
-> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.03 sec)

这个表中记录的行格式示意图：


这些记录头信息中各个属性如下：

delete_mask：这个属性标记着当前记录是否被删除，占用1个二进制位。值为0：代表记录并没有被删除值为1：代表记录被删除掉了 。被删除的记录为什么还在页中存储呢？你以为它删除了，可它还在真实的磁盘上。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后其他的记录在磁盘上需要重新排列，导致性能消耗。所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

min_rec_mask：B+树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mask值为1。我们自己插入的四条记录的min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。

record_type： 这个属性表示当前记录的类型，一共有4种类型的记录： 0：表示普通记录 1：表示B+树非叶节点记录 2：表示最小记录 3：表示最大记录 。从图中我们也可以看出来，我们自己插入的记录就是普通记录，它们的record_type值都是0，而最小记录和最大记录的record_type值分别为2和3。至于record_type为1的情况，我们在索引的数据结构章节讲过。

heap_no：这个属性表示当前记录在本页中的位置。 从图中可以看出来，我们插入的4条记录在本页中的位置分别是：2、3、4、5。 怎么不见heap_no值为0和1的记录呢？MySQL会自动给每个页里加了两个记录，由于这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。最小记录和最大记录的heap_no值分别是0和1，也就是说它们的位置最靠前。

n_owned：页目录中每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段。详情见page directory。

next_record：记录头信息里该属性非常重要，它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比如：第一条记录的next_record值为32，意味着从第一条记录的真实数据的地址处向后找32个字节便是下一条记录的真实数据。注意，下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定Infimum记录（也就是最小记录）的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）。下图用箭头代替偏移量表示next_record。

image-20230414112706156

next_record的删除操作：

删除操作：从表中删除掉一条记录，这个链表也是会跟着变化：

mysql> DELETE FROM page_demo WHERE c1 = 2;
Query OK, 1 row affected (0.02 sec)

删掉第2条记录后的示意图就是：

image-20230414113040979

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

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

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

next_record的添加操作：

添加操作：主键值为2的记录被我们删掉了，但是存储空间却没有回收，如果我们再次把这条记录插入到表中，会发生什么事呢？

mysql> INSERT INTO page_demo VALUES(2, 200, 'tong');
Query OK, 1 row affected (0.00 sec)

我们看一下记录的存储情况：

image-20230414113338222

直接复用了原来被删除记录的存储空间。

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

简化后的行格式示意图：

image-20230414111430408

插入数据：

INSERT INTO page_demo VALUES
(1, 100, 'song'), 
(2, 200, 'tong'), 
(3, 300, 'zhan'), 
(4, 400, 'lisi');

图示如下：

image-20230414111513726

4. 区、段与碎片区

4.1 为什么要有区？

B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍B+树索引的适用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，
就是所谓的随机I/0。再次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/0是非常慢的，==所以我
们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/0。==

引入区的概念,一个区就是在物理位置上连续的64个页。因为InnoDB中的页大小默认是16KB,所以一一个区的大小是64*16KB=1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是
按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间
的浪费(数据不足以填充满整个区) ， 但是从性能角度看,可以消除很多的随机I/O，功大干过 !

4.2 为什么要有段？

对于范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以InnoDB对B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。==存放叶子节点的区的集合就算是一个段( segment)，存放非叶子节点的区的集合也算是一个段。 也就是说一个索引会生成2个
段,一个叶子节点段，一个非叶子节点段。==

除了索引的叶子节点段和非叶子节点段之外, InnoDB中还有为存储一些特殊的数据而定义的段,比如回滚段。所以，常见的段有数据段、索引段、回滚段。 数据段即为B+树的叶子节点，索弓|段即为B+树的非叶子节点。

在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。这从一定程度上简化了DBA对于段的管理。

段其实不对应表空间中某一个连续的物理区域， 而是一个逻辑上的概念,由若干个零散的页面以及一些完整的区组成。

4.3 为什么要有碎片区？

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段,而段是以区为单位申请
存储空间的，一个区默认占用1M (64 * 16Kb = 1024Kb)存储空间,所以默认情况下一个只存了几条记录的小表也
需要2M的存储空间么?以后每次添加一个索引都要多申请2M的存储空间么?这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常纯粹的，也就是一个区被整个分配给某一 个段，或者说区中的所有页面都是为了存储同-一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能他用。

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

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

  • ==在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。==
  • ==当某个段已经占用了32个碎片区页面之后,就会申请以完整的区为单位来分配存储空间。==

所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。

4.4 区的分类

区大体上可以分为4种类型：

  • 空闲的区 (FREE) : 现在还没有用到这个区中的任何页面。
  • 有剩余空间的碎片区 (FREE_FRAG)：表示碎片区中还有可用的页面。
  • 没有剩余空间的碎片区 (FULL_FRAG)：表示碎片区中的所有页面都被使用，没有空闲页面。
  • 附属于某个段的区 (FSEG)：每一个索引都可以分为叶子节点段和非叶子节点段。

处于FREE、FREE_FRAG 以及 FULL_FRAG 这三种状态的区都是独立的，直属于表空间。而处于 FSEG 状态的区是附属于某个段的。

如果把表空间比作是一个集团军，段就相当于师，区就相当于团。一般的团都是隶属于某个师的，就像是处于 FSEG 的区全部隶属于某个段，而处于 FREE、FREE_FRAG 以及 FULL_FRAG 这三种状态的区却直接隶属于表空间，就像独立团直接听命于军部一样。

5. 表空间

==表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。==

表空间是一个逻辑容器，表空间存储的对象是段,在一个表空间中可以有一个或多个段,但是一个段只能属于一个表空间。表空间数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间(Systemtablespace)、独 立表空间(File-per-table tablespace)、撤销表 空间(Undo Tablespace)和临时表空间(Temporary Tablespace)等。

5.1 独立表空间

独立表空间，即每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间 (即：单表) 可以在不同的数据库之间进行 迁移。

空间可以回收 DROP TABLE 操作可自动回收表空间；其他情况，表空间不能自己回收) 。如果对于统计分析或是日志表，删除大量数据后可以通过：alter table TableName engine=innodb; 回收不用的空间。对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

独立表空间结构

独立表空间由段、区、页组成。

真实表空间对应的文件大小

我们到数据目录里看，会发现一个新建的表对应的 .ibd 文件只占用了 96K，才6个页面大小 (MySQL5.7中)，这是因为一开始表空间占用的空间很小，因为表里边都没有数据。不过别忘了这些 .ibd 文件是自扩展的，随着表中数据的增多，表空间对应的文件也逐渐增大。

查看 InnoDB 的表空间类型：

show variables like 'innodb_file_per_table'

你能看到 innodb_file_per_table=ON, 这就意味着每张表都会单词保存一个 .ibd 文件。

5.2 系统表空间

系统表空间的结构和独立表空间基本类似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，这部分是独立表空间中没有的。

InnoDB数据字典

image-20230414132757978

删除这些数据并不是我们使用 INSERT 语句插入的用户数据，实际上是为了更好的管理我们这些用户数据而不得以引入的一些额外数据，这些数据页称为 元数据。InnoDB 存储引擎特意定义了一些列的 内部系统表 (internal system table) 来记录这些元数据：

image-20230414132810483

这些系统表也称为 数据字典，它们都是以 B+ 树的形式保存在系统表空间的某个页面中。其中 SYS_TABLES、SYS_COLUMNS、SYS_INDEXES、SYS_FIELDS 这四个表尤其重要，称之为基本系统表 (basic system tables) ，我们先看看这4个表的结构：

注意：用户不能直接访问 InnoDB 的这些内部系统表，除非你直接去解析系统表空间对应文件系统上的文件。不过考虑到查看这些表的内容可能有助于大家分析问题，所以在系统数据库 information_schema 中提供了一些以 innodb_sys 开头的表:

USE information_schema;
SHOW TABLES LIKE 'innodb_sys%';

在 information_scheme 数据库中的这些以 INNODB_SYS 开头的表并不是真正的内部系统表 (内部系统表就是我们上边以 SYS 开头的那些表)，而是在存储引擎启动时读取这些以 SYS 开头的系统表，然后填充到这些以 INNODB_SYS 开头的表中。以 INNODB_SYS 开头的表和以 SYS 开头的表中的字段并不完全一样，但仅供大家参考已经足矣。

附录：数据页加载的三种方式

InnoDB从磁盘中读取数据 最小单位 是数据页。而你想得到的 id = xxx 的数据，就是这个数据页众多行中的一行。

对于MySQL存放的数据，逻辑概念上我们称之为表，在磁盘等物理层面而言是按 数据页 形式进行存放的，当其加载到 MySQL 中我们称之为 缓存页。

如果缓冲池没有该页数据，那么缓冲池有以下三种读取数据的方式，每种方式的读取速率是不同的：

1. 内存读取

如果该数据存在于内存中，基本上执行时间在 1ms 左右，效率还是很高的。

image-20230414125715503

2. 随机读取

如果数据没有在内存中，就需要在磁盘上对该页进行查找，整体时间预估在10ms 左右，这10ms中有6ms是磁
盘的实际繁忙时间(包括了寻道和半圈旋转时间)， 有3ms是对可能发生的排队时间的估计值，另外还有1ms的
传输时间，将页从磁盘服务器缓冲区传输到数据库缓冲区中。这10ms看起来很快,但实际上对于数据库来说消
耗的时间已经非常长了，因为这还只是一个页的读取时间。

image-20230414125806813

3. 顺序读取

顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘/0操作了。如果一个磁盘
的吞吐量是40MB/S,那么对于一个16KB大小的页来说，一次可以顺序读取2560 (40MB/16KB) 个页，相当于一
个页的读取时间为0.4ms。采用批量读取的方式，即使是从磁盘上进行读取，效率也比从内存中只单独读取一个
页的效率要高。

MySQL
#MySQL #数据库
第07章_InnoDB数据存储结构
https://xhablog.online/2021/04/20/MySQL高级-第07章_InnoDB数据存储结构/
作者
Xu huaiang
发布于
2021年4月20日
许可协议