MySQL学习Day20——InnoDB数据存储结构

一、数据库的存储结构:页

1.磁盘与内存交互基本单位:页

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

2.页结构概述:

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

3.页的大小:

不同的数据库管理系统的页大小不同，在MySQL的InnoDB存储引擎中，默认页大小是16KB，可以通过命令查看页大小:

SHOW VARIABLES LIKE '%innodb_page_size%';

4.页的上层结构:

在数据库中，还存在着区、段和表空间的概念。

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

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

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

二、页的内部结构:

页如果按类型划分的有数据页(保存 B+ 树节点)、 系统页、 Undo 页和事务数据页等。数据页的16KB大小的存储空间被划分为七个部分，分别是文件头(File Header)、页头(Page Header)、最大最小记录(Infimum+supremum)、用户记录(User Records)、空闲空间(Free Space)、页目录(PageDirectory)和文件尾(File Tailer)。

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

第1部分:File Header文件头和File Tailer文件尾

(1)文件头部信息:描述各种页的通用信息，大小为38字节

名称	大小	描述
FIL_PAGE_SPACE_OR_CHKSUM	4字节	页的校验和
FIL_PAGE_OFFSET	4字节	页号
FIL_PAGE_PREV	4字节	上一个页的页号
FIL_PAGE_NEXT	4字节	下一个页的页号
FIL_PAGE_LSN	8字节	页面被最后修改时对应的日志序列位置
FIL_PAGE_TYPE	2字节	该页的类型
FIL_PAGE_FILE_FLUSH_LSN	8字节	表示该文件至少被刷新到了对应的LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4字节	该页属于哪个表空间

a.FIL_PAGE_OFFSET:每一个页都有一个单独的页号， 用来唯一标识一个页。

b.FIL_PAGE_TYPE:表示当前页的类型

类型名称	十六进制	描述
FIL_PAGE_TYPE_ALLOCATED	0x0000	最新分配，还未使用
FIL_PAGE_UNDO_LOG	0x0002	Undo 日志页
FIL_PAGE_INODE	0x0003	段信息节点
FIL_PAGE_IBUF_FREE_LIST	0x0004	Insert Buffer空闲列表
FIL_PAGE_IBUF_BITMAP	0x0005	Insert Buffer位图
FIL_PAGE_TYPE_SYS	0x0006	系统页
FIL_PAGE_TYPE_TRX_SYS	0x0007	事务系统数据
FIL_PAGE_TYPE_FSP_HDR	0x0008	表空间头部信息
FIL_PAGE_TYPE_XDES	0x0009	扩展描述页
FIL_PAGE_TYPE_BLOB	0x000A	溢出页
FIL_PAGE_INDEX	0x45BF	索引页/数据页

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

d.FIL_PAGE_SPACE_OR_CHECKSUM:InnoDB存储引擎以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候断电了，造成了该页传输的不完整。为了检测一个页是否完整(也就是在同步的时候有没有发生只同步一半的尴尬情况)，这时可以通过文件尾的校验和(checksum值)与文件头的校验和做比对，如果两个值不相等则证明页的传输有问题，需要重新进行传输，否则认为页的传输已经完成。

e.FIL_PAGE_LSN:页面被最后修改时对应的日志序列位置

(2)文件尾部:

a.前四个字节代表页的校验和，和File Header中的对应

b.后四个字节代表页面被最后修改时对应的日志序列位置

第2部分:记录部分，主要作用是存储记录

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

(2)User Records用户记录:一条一条记录按照指定的行格式相互之间形成单链表

记录头信息(5字节):

名称	大小/bit	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	每层非叶子节点中的最小记录
n_owned	4	当前记录拥有的记录数
heap_no	13	当前记录在记录堆的位置信息
record_type	3	当前记录的类型
next_record	16	下一条记录的相对位置

a.delete_mask:标记当前记录是否被删除，占用1个二进制位，值为0表示记录没有被删除，值为1表示记录被删除。

b.min_rec_mask:B+树的每层非叶子节点中的最小记录都有该标记，且值为1.

c.record_type:表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录,2表示最小记录,3表示最大记录

d.heap_no:表示当前记录在本页中的位置

e.n_owned:页目录中每个组中最后一条记录的头信息中会存储一共有多少条记录

f.next_record:表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量

第3部分:页目录与页头

(1)页目录:

1.将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为已删除的记录：

a.第1组，也就是最小记录所在的分组，只有1个记录;

b.最后一组，就是最大记录所在的分组，会有1-8条记录;

c.其余的组记录数量在 4-8 条之间;

这样做的好处是，除了第1组(最小记录所在组)以外，其余组的记录数会尽量平分。

2.在每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为nowned字段。

3.页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存怕起来，每组的地址偏移量也被称之为槽(slot)，每个槽相当于指针指向了不同组的最后一条记录。

(2)页面头部:

名称	占用大小	描述
PAGE_N_DIR_SLOTS	2字节	在页目录中的槽的数量
PAGE_HEAP_TOP	2字节	还未使用的空间的最小地址
PAGE_N_HEAP	2字节	本页中的记录数量(包含最大/小记录和已删除的记录)
PAGE_FREE	2字节	第一个被标记为删除的记录地址
PAGE_GARBAGE	2字节	已删除记录占用的字节数目
PAGE_LAST_INSERT	2字节	最后插入记录的位置
PAGE_DIRECTION	2字节	记录插入的方向
PAGE_N_DIRECTION	2字节	一个方向连续插入的记录数量
PAGE_N_RECS	2字节	该页中的记录数量(不包含最大/小记录和已删除的记录)
PAGE_MAX_TRX_ID	8字节	修改当前页的最大事务ID
PAGE_LEVEL	2字节	当前页在B+树中所处的层级
PAGE_INDEX_ID	8字节	索引ID,表示当前页属于哪个索引
PAGE_BTR_SEG_LEAF	10字节	B+树叶子节段的头部信息
PAGE_BTR_SEG_TOP	10字节	B+树非叶子节段的头部信息

B+树是如何进行记录检索的:

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

普通索引和唯一索引在查询效率上的区别:

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

三、InnoDB的行格式:

1.查看默认行格式:

SELECT @@innodb_default_row_format;

2.修改行格式:

ALTER TABLE 表名 ROW_FORMAT = ;

3.COMPACT行格式:

(1)变长字段长度列表:MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

(2)NULL值列表:Compact行格式会把可以为NULL的列统一管理起来，存在一个标记为NULL值列表中。如果表中没有允许存储NULL的列，则NULL值列表也不存在了。之所以要存储NULL是因为数据都是需要对齐的，如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空置的话，虽然能达到效果，但是这样很浪费空间，所以直接就在行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据，哪些是空数据。二进制位的值为1时，代表该列的值为NULL，二进制位的值为0时，代表该列的值不为NULL。

(3)记录头信息

(4)记录的真实数据:记录的真实数据除了自定义的列的数据以外，还有三个隐藏列

列名	是否必须	占用空间	描述
row_id	否	6字节	行ID，唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

4.Dynamic和Compressed行格式:

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

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

四、区、段、碎片区:

1.引入区的原因:

B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远 。尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/O。一个区就是在物理位置上连续的64个页。因为 InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB=1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照 区为单位分配 ，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费(数据不足以填充满整个区)，但是从性能角度看，可以消除很多的随机I/O。

2.引入段的原因:

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

3.引入碎片区的原因:

考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况,InnoDB提出碎片(fragment)区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。 碎片区直属于表空间，并不属于任何一个段。在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的,当某个段已经占用了 32个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。

4.区的分类:

空闲区(FREE):现在还没有用到这个区中的任何页面

有剩余空间的碎片区(FREE_FRAG):表示碎片区中还有可用的页面

没有剩余空间的碎片区(FULL_FRAG):表示碎片区中的所有页面都被使用，没有空闲页面

附属于某个段的区(FSEG):每一个索引都可以分为叶子节点段和非叶子节点段

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

5.表空间:

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

独立表空间:每张表有一个独立的表空间，也就是数据和索引信息都会保存在独立表空间中，独立表空间可以在不同的数据库之间进行迁移。空间可以回收。

系统表空间:额外记录一些有关整个系统信息的页面，用户不能直接访问InnoDB中的这些系统表，但可以通过接口系统数据库information_schema中提供的innodb_sys查看。

表名	描述
SYS_TABLES	整个InnoDB存储引擎中所有的表的信息
SYS_COLUMNS	整个InnoDB存储引擎中所有的列的信息
SYS_INDEXES	整个InnoDB存储引擎中所有的索引的信息
SYS_FIELDS	整个InnoDB存储引擎中所有的索引对应的列的信息
SYS_FOREIGN	整个InnoDB存储引擎中所有的外键的信息
SYS_FOREIGN_COLS	整个InnoDB存储引擎中外键对应的列的信息
SYS_TABLESPACES	整个InnoDB存储引擎中所有的表空间的信息
SYS_DATAFILES	整个InnoDB存储引擎中所有的表空间对应文件系统的文件路径的信息
SYS_VIRTUAL	整个InnoDB存储引擎中所有的虚拟生成列的信息