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

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

InnoDB将数据划分为若干个页，16KB

以页作为磁盘和内存间交互的基本单位，即一次最少从磁盘读取16KB的内容到内存，一次最少把内存中16KB的内容刷新到磁盘中。

数据库管理存储空间的基本单位是页，数据库IO操作的最小单位是页

记录按行存储，但数据库的读取不以行为单位，否则一次IO处理一行，效率太低！

1.2 页结构概述

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

1.3 页的大小

不同DBMS的页大小不同。MySQL的InnoDB中页的默认大小为16KB

show variables like '%innodb_page_size%';

1.4 页的上层结构

区：在InnoDB中一个区会分配64个连续的页，一个区的大小为 64*16KB = 1MB
段：一个或多个区组成，段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。必然创建表时创建一个表段，创建索引时创建索引段
表空间：逻辑容器，一个表空间中可以有一个或多个段，但一个段只属于一个表空间。数据库由一个或多个表空间组成，从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间

2. 页的内部结构

页如果按类型划分，常见的有数据页(保留B+树节点)、系统页、Undo页、事务数据页

数据页的16KB大小的存储空间被划分为七个结构：

我们可以把七个结构分成3个部分。

2.1 第一部分：文件头、文件尾

2.1.1 文件头部 38字节

作用：描述各种页的通用信息。

仅展现重要部分

FIL_PAGE_OFFSET(4字节) ：页号
FIL_PAGE_TYPE(2字节)：页的类型

类型名称	十六进制	描述
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	索引页，也就是我们所说的`数据页`

FIL_PAGE_PREV（4字节）和FIL_PAGE_NEXT（4字节）：上/下一个页的页号
FIL_PAGE_SPACE_OR_CHKSUM(4字节)：校验和(checksum)
校验和作用： InnoDB存储引擎以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候断电了，造成了该页传输的不完整。
为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），这时可以通过对比文件头尾校验和，不相等则证明页的传输有问题，需要重新进行传输。

FIL_PAGE_LSN（8字节）：页面被最后修改时对应的日志序列位置（Log Sequence Number）

2.1.2 File Trailer（文件尾部）（8字节）

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

2.2 第2部分：

2.2.1 Free Space (空闲空间)

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

2.2.2 User Records (用户记录)

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

2.2.3 Infimum + Supremum（最小最大记录）

记录可以比较大小吗？可以，比较记录的大小就是比较主键的大小。

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

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

heap_no后续会介绍

2.3 第3部分：页目录和页面头部

2.3.1 Page Directory（页目录）

为什么需要页目录？

在页中，记录是以单向链表的形式进行存储的。检索效率不高。因此在页结构中专门给记录做一个目录，通过二分查找法的方式进行检索，提升效率。

页目录，二分法查找

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

举例：

现在的page_demo表中正常的记录共有6条，InnoDB会把它们分成两组，第一组中只有一个最小记录，第二组中是剩余的5条记录。如下图：

从这个图中我们需要注意这么几点：

页目录中有两个槽，记录被分成了两个组，槽1中的值是112，代表最大记录的地址偏移量（就是从页面的0字节开始数，数112个字节）；槽0中的值是99，代表最小记录的地址偏移量。
注意最小和最大记录的头信息中的n_owned属性
- 最小记录的n_owned值为1，这就代表着以最小记录结尾的这个分组中只有1条记录
- 最大记录的n_owned值为5

用箭头指向的方式替代数字，这样更易于我们理解，修改后如下

为什么最小记录的n_owned值为1，而最大记录的n_owned值为5呢？

InnoDB规定：对于最小记录所在的分组只能有1条记录，最大记录所在的分组拥有的记录条数只能在1~8条之间，剩下的分组中记录的条数范围只能在是 4 ~ 8 条之间。

分组是按照下边的步骤进行的：

初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

快速查找：寻找主键值为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条，所以遍历一个组中的记录的代价是很小的。

2.3.2 Page Header（页面头部）

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

名称	占用空间大小	描述
PAGE_N_DIR_SLOTS	2字节	在页目录中的槽数量
PAGE_HEAP_TOP	2字节	还未使用的空间最小地址，也就是说从该地址之后就是`Free Space`
PAGE_N_HEAP	2字节	本页中的记录的数量（包括最小和最大记录以及标记为删除的记录）
PAGE_FREE	2字节	第一个已经标记为删除的记录的记录地址（各个已删除的记录通过`next_record`也会组成一个单链表，这个单链表中的记录可以被重新利用）
PAGE_GARBAGE	2字节	已删除记录占用的字节数
PAGE_LAST_INSERT	2字节	最后插入记录的位置
PAGE_DIRECTION	2字节	假如新插入的一条记录的主键值比上一条记录的大，插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是PAGE_DIRECTION。
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+树叶子段的头部信息，仅在B+树的Root页定义
PAGE_BTR_SEG_TOP	10字节	B+树非叶子段的头部信息，仅在B+树的Root页定义

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

一颗B+树可以分成两个部分：

叶子节点，B+树最底层的节点，节点的高度为0，存储行记录
非叶子节点，节点高度>0，存储索引键和页面指针

1.B+树是如何进行记录检索的？

首先从B+树的根开始，逐层检索，直至找到叶子节点，即对应的数据页为止，将数据页加载到内存中，页目录中的槽采用二分查找的方法先找到记录分组，再在分组中通过链表遍历的方式查找数据

2.普通索引和唯一索引在查询效率上有什么不同？

唯一索引即关键字唯一，找到了关键字就停止检索。而普通索引的可能存在多个相同关键字，我们读取一条记录时，不是单独将这条记录从磁盘读出，而是将这个记录所在页加载到内存中进行读取。一个页可能存储上千条记录，因此在普通索引的字段上进行查找也就是在内存中多几次判断下一条记录的操作。效率差别不大

3. InnoDB行格式（或记录格式）

在磁盘上的存放方式

3.1 指定行格式的语法

查看默认行格式：

SELECT @@innodb_default_row_format;

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称

ALTER TABLE 表名 ROW_FORMAT=行格式名称

3.2 COMPACT行格式

在MySQL 5.1版本中，默认设置为Compact行格式。一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分。

3.2.1 变长字段长度列表

MySQL支持一些变长的数据类型，比如VARCHAR(M)、TEXT类型等，它们称为变长字段，变长字段中存储多少字节的数据不是固定的，所以要存它们占用的字节数。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

注意：这里面存储的变长长度和字段顺序是反过来的。比如两个varchar字段在表结构的顺序是a(10)，b(15)。那么在变长字段长度列表中存储的长度顺序就是15，10，是反过来的。

3.2.2 NULL值列表

可以把为NULL的列统一管理起来，存在一个标记为NULL值列表中。

为什么定义NULL值列表？ 之所以要存储NULL是因为数据都是需要对齐的，如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空置的话，虽然能达到效果，但是这样很浪费空间，所以直接就在行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据，哪些是空数据，格式如下：

二进制位的值为1时，代表该列的值为NULL。
二进制位的值为0时，代表该列的值不为NULL。

注意：同样顺序也是反过来存放的

col2为 NOT NULL

3.2.3 记录头信息（5字节）

预留位1，2：没有使用
delete_mask(1bit)：标记着当前记录是否被删除
- 值为0：代表记录并没有被删除
- 值为1：代表记录被删除掉了

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

min_rec_mask：B+树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mask值为1。我们自己插入的四条记录的min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。
record_type：这个属性表示当前记录的类型，一共有4种类型的记录：
- 0：表示普通记录
- 1：表示B+树非叶节点记录
- 2：表示最小记录
- 3：表示最大记录
heap_no：当前记录在本页中的位置。

怎么不见heap_no值为0和1的记录呢？ MySQL会自动给每个页里加了两个记录，由于这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。最小记录和最大记录的heap_no值分别是0和1，也就是说它们的位置最靠前

n_owned：页目录中每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段
next_record：当前记录的真实数据到下一条记录的真实数据的地址偏移量。非常重要

删除第二条记录：

3.2.4 记录的真实数据

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

一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为 row_id 的隐藏列作为主键。

3.3 Dynamic和Compressed行格式

一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录，这种现象称为行溢出

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

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

Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。在数据页中只存放20个字节的指针（溢出页的地址），实际的数据都存放在Off Page（溢出页）中。
Compact和Redundant两种格式会在记录的真实数据处存储一部分数据（存放768个前缀字节）。

3.4 Redundant行格式

Redundant是MySQL5.0之前InnoDB的行记录存储方式。5.0支持Redundant是为了兼容之前版本的页格式

3.4.1 字段长度偏移量

不同于Compact格式，Redudant的首部是一个字段长度偏移列表，同样是逆序放置的

两处不同：

少了变长：Redundant将该记录所有列的长度信息按照逆序存储到字段长度偏移列表
多了偏移：存放的是该列的偏移量

举例：一条记录的字段长度偏移列表：
2B 25 1F 1B 13 0C 06

第一列(row_id)的长度是6个字节，所以最后一个为06
第二列(transaction_id)长度为6个字节。偏移量为 0C

3.4.2 记录头信息

占用6个字节

n_fields：代表一行中列的个数，很好的解释了MySQL一个行最多支持1023列。
1byte_off_flags：定义偏移列表占用1个字节还是两个字节，1为1字节，0为2字节

4. 区、段和碎片区

4.1 为什么要有区？

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

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

4.2 为什么要有段？

范围查询其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以InnoDB对B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。一个索引会生成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状态的区是附属于某个段的。

5. 表空间

可以看作InnoDB逻辑结构的最高层，所有数据都存放在表空间中。

表空间是一个逻辑容器，存储段。

5.1 独立表空间

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

空间可以回收(DROP TABLE操作可自动回收)

表空间对应的文件大小

新建一张表，发现对应的.ibd文件只占用96K，即6个页面(MySQL5.7中，8.0更大，因为.frm的并入)，.ibd文件有自扩展性

查看InnoDB表空间类型：

show variables like 'innodb_file_per_table';

5.2 系统表空间

整个MySQL进程只有一个系统表空间，结构与独立表空间类似，但会额外记录一些有关整个系统信息的页面。

InnoDB数据字典

每当我们向表中插入记录时，MySQL校验过程如下：

先校验对应的表是否存在，列是否符合，寻找该表的聚簇索引和所有二级索引对应的根页面是哪个表空间的哪个页面，然后把记录插入对应的B+树中。

MySQL除了保存插入的用户数，还需保存许多额外的信息：

某个表属于哪个表空间，有多少行
表对应每一个列的类型
有几个索引
...

上述这些数据称为元数据。InnoDB特意定义了一些内部系统表来记录这些元数据

注意：用户不能直接访问这些内部系统表，但系统数据库information_schema中提供了一些以innodb——sys开头的表：

USE information_schema;
SHOW TABLES LIKE 'innodb_sys%';

它们并不是真正的内部系统表，而是存储引擎启动时读取这些以SYS开头的系统表并填充进去的，字段并不完全一样，不过作为参考足以。

6. 数据页加载的三种方式

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

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

如果缓存池没有该页数据，那么有三种方式读取数据：

内存读取：执行时间1ms

随机读取：执行时间10ms

顺序读取：一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储。如果一个磁盘吞吐量是40MB/S，那么对于一个16KB大小的页，一次可以读取2560(40MB/16KB)，相当于一个页的读取速度为0.4ms

进阶篇(7) InnoDB数据存储结构