MYSQL一页可以放多少个索引 mysql数据页和索引页

文章目录

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

• 1.1 磁盘与内存交互基本单位：页
• 1.2 页结构概述
• 1.3 页的上层结构

• 2. 页的内部结构

• 2.1 第1部分：文件头部和文件尾部

• 2.1.1 File Header（文件头部）（38字节）
• 2.1.2 File Trailer（文件尾部）（8字节）

• 2.2 第2部分：空闲空间、用户记录和最小最大记录

• 2.2.1 Free Space (空闲空间)
• 2.2.2 User Records (用户记录)
• 2.2.3 Infimum + Supremum（最小最大记录）

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

• 2.3.1 Page Directory（页目录）
• 2.3.2 Page Header（页面头部）

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

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

由于InnoDB是MySQL的默认存储引擎，所以本章剖析InooDB存储引擎的数据存储结构。

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

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

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

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

1.2 页结构概述

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

1.3 页的上层结构

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

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

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

2. 页的内部结构

2.1 第1部分：文件头部和文件尾部

2.1.1 File Header（文件头部）（38字节）

作用：
描述各种页的通用信息。（比如页的编号、其上一页、下一页是谁等）

大小：38字节

名称	占用空间大小	描述
FIL_PAGE_SPACE_OR_CHKSUM	4字节	页的校验和（checksum值）
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字节	页属于哪个表空间

• FIL_PAGE_OFFSET（4字节）：每一个页都有一个单独的页号，就跟你的身份证号码一样，InnoDB通过页号可以唯一定位一个页。
• 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字节）：InnoDB都是以页为单位存放数据的，如果数据分散到多个不连续的页中存储的话需要把这些页关联起来，FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了，保证这些页之间不需要是物理上的连续，而是逻辑上的连续。
• FIL_PAGE_SPACE_OR_CHKSUM（4字节）：代表当前页面的校验和（checksum）。文件头部和文件尾部都有属性：FIL_PAGE_SPACE_OR_CHKSUM

作用：

InnoDB存储引擎以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候断电了，造成了该页传输的不完整。

为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），这时可以通过文件尾的校验和（checksum 值）与文件头的校验和做比对，如果两个值不相等则证明页的传输有问题，需要重新进行传输，否则认为页的传输已经完成。

• 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部分，相互之间形成单链表。

上一节【记录头信息】行格式-记录头信息

CREATE TABLE page_demo(
	c1 INT PRIMARY KEY,
	c2 INT,
	c3 VARCHAR(10000),
)CHARSET=ASCII ROW_FORMAT=COMPACT;

这是设置c1为主键，那么InnoDB就没有必要创建row_id隐藏列了。

并且指定了ASCII字符集以及COMPACT的行格式。

这一节主要讲述记录头信息的作用，因此可以简化一下行格式

往表中插入记录，User Records记录了，但是需要注意的是，这里把记录中的头信息和实际数据都用是十进制表示出来了，但是实际上是二进制位。

• delete_flag：这个属性是标记当前记录是否被删除，占用1比特。
  值为0表示没有被删除，值为1表示记录被删除

• 为什么被删除的记录还在页中呢？这是因为如果真实移除这些记录，需要在磁盘上重新排列其他记录，这样会带来型号消耗。
  所有被删除的记录会组成一个垃圾链表，记录这个链表占用的空间称为可重用空间。之后如果有新记录插入到表中，它们就可以覆盖被删除的这些记录占用的存储空间。

• min_rec_flag：B+树每层非叶子节点中的最小的目录项都会添加该标记。
• n_owned：记录了该组有几条记录
• heap_no：一条记录在堆中的相对位置
  记录在User Records中是亲密无间地排列的，这种结构被称为堆。

• 在页面前面的记录heap_no比较小，在页面后面的记录比较大。
  例如，前面插入的4条数据中，heap_no的值分别为2、3、4、5
  而0和1则是最小记录（Infimun记录）和最大记录（Supremum记录）的heap_no值，它们被称为虚拟记录或伪记录。Infimun记录和Supremum记录这两条记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定单词组成的。由于其heap_no最小，所以这两个记录放在堆的最前面。

无论我们向页中插入了多少条记录，设计InnoDB 的大叔都规定，任何用户记录都Infimum记录大 ，任何用户记录都比 Supremum记录小, Infimum记录和 Supremum记录没有主键值。

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

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

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

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

这里说的记录的大小是指主键的大小。并且，heap_no是固定不变的，也就是值分配之后就不会发生改动了，即使被删除，值也不会发生变化。

• record_type：这个属性表示当前记录类型。
  一共有4个类型，0表示普通记录，1表示B+树非叶子节点的目录项记录，2表示Infimum记录，3表示Supermum记录。
• next_record：这个属性非常重要，它表示当前记录的真实数据到下一条记录的真实数据的距离。

• 正数表示下一条记录在当前记录后面，负数表示下一条记录在当前记录前面。
• 下一条记录并不是指插入顺序中的下一条记录，而是按照主键值由小到大的顺序排列的下一条记录。
• 并且规定了Infimum记录的下一条记录就是本页中主键值最小的用户记录，本页主键值最大的记录的下一条记录就是Supremum记录。
• 如果将第二条数据删掉，那么第二条记录的delete_flag就会被设置为1，并第二条记录的next_record变为0，第一条记录的next_record指向第三条记录
• next_record的位置之所以在记录头和真实数据之间，是因为这种向左读取就是记录头信息，向右读取就是真实数据，前面说到变长字段长度列表和NULL值列表都是逆序存放的，这样可以使得记录中位置靠前的字段和它对应的字段长度信息在内存中距离更近，可以提高缓存命中率。
• 如果将刚才删除的数据再次插入表中，会直接复用原来删除记录的空间。

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

2.3.1 Page Directory（页目录）

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

页目录，二分法查找

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

举例：

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

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

• 现在页目录部分中有两个槽，也就意味着我们的记录被分成了两个组，槽1中的值是112，代表最大记录的地址偏移量（就是从页面的0字节开始数，数112个字节）；槽0中的值是99，代表最小记录的地址偏移量。
• 注意最小和最大记录的头信息中的n_owned属性

• 最小记录的n_owned值为1，这就代表着以最小记录结尾的这个分组中只有1条记录，也就是最小记录本身。
• 最大记录的n_owned值为5，这就代表着以最大记录结尾的这个分组中只有5条记录，包括最大记录本身还有我们自己插入的4条记录。

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

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

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

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

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

怎么查找记录呢？

初始情况下最低的槽就是low = 0， 最高的槽就是hight=4，比如要找主键为6的记录。

计算中间槽的位置：（0+4）/2等于2，查找槽2的对应的记录的主键值为8，由于8>6，所以设置hight=2，
重新计算中间槽位置：（0 + 2）/2等于1，查找槽1的队友的记录的主键值为4，由于4<6，所以设置low=1，hight不变。
因为hight - low的值为1，所以确定主键值为6的记录在槽2。
因此可以从槽2的最小记录开始遍历查找。
槽2对应的记录是改组的主键值最大的记录，那么怎么定位槽2的最小记录呢？很简单，由于槽之间是挨着的，所以很容易找到槽1的最大记录，而槽1的最大记录的下一条记录就是槽2的最小记录。

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_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页定义