《MySQL是怎样运行的:从根儿上理解MySQL》- mysql 数据存储

《MySQL是怎样运行的:从根儿上理解MySQL》- mysql 数据存储

《MySQL是怎样运行的:从根儿上理解MySQL》读书笔记

InnoDb数据页结构

页是 InnoDB 管理存储空间的基本单位，一个页的大小一般是 16KB 。InnoDB 为了不同的目的而设计了许多种不同类型的 页 ，比如存放表空间头部信息的页，存放 Insert Buffer 信息的页，存放 INODE 信息的页，存放 undo 日志信息的页等等等等。我们聚焦的是那些存放我们表中记录的那种类型的页，官方称这种存放记录的页为索引（ INDEX ）页，这些表中的记录就是我们日常口中所称的 数据 ，所以目前还是叫这种存放记录的页为 数据页

名称	中文名	占用空间大小	简单描述
File Header	文件头部	38 字节	页的一些通用信息
Page Header	页面头部	56 字节	数据页专有的一些信息
Infimum + Supremum	最小记录和最大记录	26 字节	两个虚拟的行记录
User Records	用户记录	不确定	实际存储的行记录内容
Free Space	空闲空间	不确定	页中尚未使用的空间
Page Directory	页面目录	不确定	页中的某些记录的相对位置
File Trailer	文件尾部	8 字节	校验页是否完整

innodb行记录模式

暂时未写

记录头信息的密码

一些比较重要的头信息

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

• delete_mask

  这个属性标记着当前记录是否被删除，占用1个二进制位，值为 0 的时候代表记录并没有被删除，为 1 的时候代表记录被删除掉了。

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

• min_rec_mask

  B+树的每层非叶子节点中的最小记录都会添加该标记，什么是个 B+ 树？什么是个非叶子节点？好吧，等会 再聊这个问题。反正我们自己插入的四条记录的 min_rec_mask 值都是 0 ，意味着它们都不是 B+ 树的非叶 子节点中的最小记录

• n_owned

• heap_no

  ​ 这个属性表示当前记录在本 页 中的位置，我们插入的4条记录在本 页 中,那位置分别是： 2 、 3 、 4 、 5但是为什么没有0和1呢？

  他们自动给每个页里边儿加了两个记录，由于这两个记录并不是我们自己插入的，所以有时候也称为 伪记录 或者 虚拟记录 。这两个伪记录一个代表最小记录 ，一个代表最大记录 【比较的是索引大小】

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

• record_type

  ​ 这个属性表示当前记录的类型，一共有4种类型的记录， 0 表示普通记录， 1 表示B+树非叶节点记录， 2 表示最小记录，3 表示最大记录我们自己插入的记录就是普通记录，它们的record_type值都是0，而最小记录和最大记录的 record_type 值分别为 2 和 3 。

• next_record

​ 表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比方说第一条记录的 next_record 值为 32 ，意味着从第一条记录的真实数据的地址处向后找 32 个字节便是下一条记录的真实数据。如果你熟悉数据结构的话，就立即明白了，这其实是个 链表 ，可以通过一条记录找到它的下一条记录。但是需要注意注意再注意的一点是， 下一条记录 指得并不是按照我们插入顺序的下一条记录，而 是按照主键值由小到大的顺序的下一条记录。而且规定 Infimum记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）

next_record指向的是下一条记录的头信息与真实数据信息连接的位置，而不是整条记录的起始位置。这样指针前边是头信息右边是数据信息，更方便读取。并且头信息之前还存在null值列表和变长字段长度列表，其都是按照字段逆序排列的，当我们从右往左读的时候就更方便直观。

当执行删除第2条记录后,

• 就会将其删除标志位设为1，
• 改变第一条记录的next_record
• 将被删除的记录的next_record设为0（null）
• 最大记录 的 n_owned 值从 5 变成了 4

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

Page Directory（页目录）

当页内的记录较多，通过遍历的方式查找数据咋显得很慢，因此引入了目录。创建过程如下：

• 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。

• 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的 n_owned 属性表示该记录拥有多少条记录，也就是该组内共有几条记录。

• 将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近 页 的尾部的地方，这个地方就是所 谓的 Page Directory ，也就是 页目录 （此时应该返回头看看页面各个部分的图）。页面目录中的这些地址偏移量被称为 槽 （英文名： Slot ），所以这个页面目录就是由 槽 组成的。

如下图：我们将此页内的记录分为两组，第一个组只有最小记录；第二个组具有插入的数据及最大记录。

页目录中就存储了连个槽指针来表示两个组内最大数据的偏移量

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

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

• 初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。

• 之后每插入一条记录，都会从 页目录 中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对 应的记录的 n_owned 值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。

• 在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一 个5条记录。这个过程会在 页目录 中新增一个 槽 来记录这个新增分组中最大的那条记录的偏移量。

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

1. 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。

2. 通过记录的 next_record 属性遍历该槽所在的组中的各个记录。

Page Header（页面头部）

设计 InnoDB 的大叔们为了能得到一个数据页中存储的记录的状态信息，比如本页中已经存储了多少条记录，第

一条记录的地址是什么，页目录中存储了多少个槽等等，特意在页中定义了一个叫 Page Header 的部分，它是

页 结构的第二部分，这个部分占用固定的 56 个字节，专门存储各种状态信息

File Header（文件头部）

File Header 针对各种类型的页都通用，也就是说不同类型的页都会以 File Header 作

为第一个组成部分，它描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页是谁

每个数据页的 File Header 部分都有上一个和下一个页的编号，所以所有的数据页会组成一个双链表

File Trailer

​ InnoDB 存储引擎会把数据存储到磁盘上，但是磁盘速度太慢，需要以 页 为单位把数据加载到内存中理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一 半的时候中断电了咋办，这不是莫名尴尬么？为了检测一个页是否完整（也就是在同步的时候有没有发生只同步 一半的尴尬情况）， InnoDB 在每个页的尾部都加了一个 File Trailer 部分，这个部分由 8 个字 节组成，可以分成2个小部分

• 前4个字节代表页的校验和

  这个部分是和 File Header 中的校验和相对应的。每当一个页面在内存中修改了，在同步之前就要把它的校 验和算出来，因为 File Header 在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也 会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电 了，那么在 File Header 中的校验和就代表着已经修改过的页，而在 File Trialer 中的校验和代表着原先 的页，二者不同则意味着同步中间出了错。

• 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）

  这个部分也是为了校验页的完整性的

File Trailer 与 File Header 类似，都是所有类型的页通用的。

快速查询的秘籍-B+树索引

数据记录的多少直接影响着页的数量的大小。随着页数量的增多，我们不可能去遍历页，因此得再页的基础上再去创建索引，使其能够快速的找到在哪一页

数据页具有页号、数据范围、偏移量属性，我们可以根据这三个值建立我们的索引。这里Innodb的设计者们发现这些属性和我们的数据记录很相似啊，只是页号变成了主键，数据范围、偏移量变成了表属性，因此设计者们 复用了上面的页结构，只是每条数据变成了索引数据（页号、数据范围、偏移量属性），其同样复用用诸如Page Directory PageHeader等结构，唯一的一个区别就是索引记录行格式中record_type变为了1，表示此是目录项记录，不是用户记录。

那当我们的数据量增大，我们的索引页就会随之增多，那索引页的查找就变得慢了起来，所以我们还是采用了创建索引的思想在索引的基础上再创建一个更高级的索引页，来管理之前的索引页。

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到 B+ 树这个数据结构中了，所以我们也称这些数据页为 节点 。从图中可以看出来，我们的实际用户记录其实都存放在B+树的最底层的节点 上，这些节点也被称为 叶子节点 或 叶节点 ，其余用来存放 目录项 的节点称为 非叶子节点 或者 内节点 ，其 中 B+ 树最上边的那个节点也称为 根节点.

从图中可以看出来，一个 B+ 树的节点其实可以分成好多层，设计 InnoDB 的大叔们为了讨论方便，规定最下边的 那层，也就是存放我们用户记录的那层为第 0 层，之后依次往上加。之前的讨论我们做了一个非常极端的假设： 存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录 数量是非常大的，假设，假设，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有 存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：

• 如果 B+ 树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。

• 如果 B+ 树有2层，最多能存放 1000×100=100000 条记录。

• 如果 B+ 树有3层，最多能存放 1000×1000×100=100000000 条记录。

• 如果 B+ 树有4层，最多能存放 1000×1000×1000×100=100000000000 条记录。

你的表里能存放 100000000000 条记录么？所以一般情况下，我们用到的 B+ 树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内 有所谓的 Page Directory （页目录），所以在页面内也可以通过二分法实现快速定位记录

聚簇索引

我们上边介绍的 B+ 树本身就是一个目录，或者说本身就是一个索引。它有两个特点：

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

• 页内的记录是按照主键的大小顺序排成一个单向链表。

• 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

• 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成

一个双向链表。

2. B+ 树的叶子节点存储的是完整的用户记录。

   所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

​ 我们把具有这两种特性的 B+ 树称为聚簇索引 ，所有完整的用户记录都存放在这个 聚簇索引 的叶子节点处。这种 聚簇索引 并不需要我们在 MySQL 语句中显式的使用 INDEX 语句去创建（后边会介绍索引相关的语句）， InnoDB 存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在 InnoDB 存储引擎中， 聚簇索引 就是数 据的存储方式（所有的用户记录都存储在了 叶子节点 ），也就是所谓的索引即数据，数据即索引。

二级索引

​ 聚簇索引 只能在搜索条件是主键值时才能发挥作用，因为 B+ 树中的数据都是按照主键进行排序的。当我们根据其他字段的排序规则建立普通索引的时候我们不可能为了建立索引再去复制一份数据形成一个索引树，因此我们可以将索引属性与主键相关联，其存储的数据不是完整的用户记录，而是只有索引列+主键

区别：

• 二级索引根据对应的索引项的排序规律建立索引
• 叶子节点存储的并不是完整的用户记录，而只是索引列+主键两个值
• 目录项记录中不再是主键+页号 的搭配，而变成了 索引属性+页号 的搭配。

查询的时候先通过索引列查到主键，再通过主键去搜索聚簇索引树查找真正的数据

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让 B+ 树按照 c2 和 c3 列的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照 c2 列进行排序。

• 在记录的 c2 列相同的情况下，采用 c3 列进行排序

以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它的意思

与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

• 建立 联合索引 只会建立1棵 B+ 树。

• 为c2和c3列分别建立索引会分别以 c2 和 c3 列的大小为排序规则建立2棵 B+ 树

索引创建过程

• 每当为某个表创建一个B+ 树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一

个根节点 页面。最开始表中没有数据的时候，每个 B+ 树索引对应的 根节点 中既没有用户记录，也没有目录项记录。

• 随后向表中插入用户记录时，先把用户记录存储到这个根节点 中。
• 当根节点 中的可用空间用完时继续插入记录，此时会将 根节点 中的所有记录复制到一个新分配的页，比 如 页a 中，然后对这个新页进行 页分裂 的操作，得到另一个新页，比如 页b 。这时新插入的记录根据键值 （也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到 页a 或者 页b 中，而根节点 便升级为存储目录项记录的页。

这个过程需要大家特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的 根节点 的页号便会被记录到某个地方，然后凡是 InnoDB 存储引擎需要用到这个索引的时候，都会从那个固定的地方取出 根节点 的页号，从而来访问这个索引。

优化

• 回表查询具有一定的代价，需要回表的记录越多，使用二级索引的性能就越低，只有查询的记录比较少，才更倾向于使用二级索引+回表的形式查询数据

• 最好在查询列表里只包含索引列，即覆盖索引；同时不鼓励用 * 号作为查询列

  表，最好把我们需要查询的列依次标明；左前缀匹配选择

  挑选索引

• 只为where子句中的列，连接子句中的列或者出现在Orderby、groupby中的列构建索引

• 索引列值尽可能分散

• 在表示的整数范围允许的情况 下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要使用 BIGINT

  • 存储小，io效率高
  • 类型小，比较速度快

• 只索引字符串值的前缀,例如索引name的前KEY 10个字符e_birthday_phone_number (name(10), birthday, phone_number)

  • 索引前缀列对排序产生影响，对前缀相同的字符串通过索引是无法排序的，只能通过实际记录进行查询

• 让索引列在表达式中单独出现

  如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的

• 让主键具有 AUTO_INCREMENT ，避免产生页分裂

表空间-段-区的概念

​ 表空间 是一个抽象的概念，对于系统表空间来说，对应着文件系统中一个或多个实际文件；对于每个独立表空间来说，对应着文件系统中一个名为 表名.ibd 的实际文件。大家可以把表空间想 象成被切分为许许多多个 页 的池子，当我们想为某个表插入一条记录的时候，就从池子中捞出一个对应的页来把数据写进去。

页

页面的类型

​ InnoDB是以页为单位管理存储空间的，我们的聚簇索引（也就是完整的表数据）和其他的二级索引都是以 B+ 树的形式保存到表空间的，而 B+ 树的节点就是数据页。除了这种存放索引数据的页面类型之外，InnoDB也为了不同的目的设计了若干种不同类型的页面。比如：Undo日志页，段信息页、系统页等

页的通用部分

• File Header ：记录页面的一些通用信息

  • file header中存在了页号以及上下页号、页类型、属于哪个表空间等

• File Trailer ：校验页是否完整，保证从内存到磁盘刷新时内容的一致性。

注意点

• 表空间中的每一个页都对应着一个页号，也就是 FIL_PAGE_OFFSET ，这个页号由4个字节组成，也就是32个 比特位，所以一个表空间最多可以拥有2³²个页，如果按照页的默认大小16KB来算，一个表空间最多支持 64TB的数据。
• 某些类型的页可以组成链表，链表中的页可以不按照物理顺序存储，而是根据 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 来存储上一个页和下一个页的页号。需要注意的是，这两个字段主要是为了 INDEX 类型的 页，也就是我们之前一直说的数据页建立 B+ 树后，为每层节点建立双向链表用的，一般类型的页是不使用这两个字段的。

区

概念

表空间中的页实在是太多了，为了更好的管理这些页面，设计 InnoDB 的大叔们提出了 区 （英文名： extent 的概念。对于16KB的页来说，连续的64个页就是一个 区 ，也就是说一个区默认占用1MB空间大小。不论是系统表空间还是独立表空间，都可以看成是由若干个区组成的，每256个区被划分成一组

我们从图中看一看出，256个区被分为了一组，每个组中的前几个页的类型是不同的第一个组最开始的3个页面的类型是固定的，也就是说 extent 0 这个区最开始的3个页面的类型是固定的，分别是：

• FSP_HDR 类型：这个类型的页面是用来登记整个表空间的一些整体属性以及本组所有的区 。需要注意的一点是，整个表空间只有一个 FSP_HDR 类型的页面。

• IBUF_BITMAP 类型：这个类型的页面是存储本组所有的区的所有页面关于 INSERT BUFFER 的信息。

• INODE 类型：这个类型的页面存储了许多称为 INODE 的数据结构

其余各组最开始的2个页面的类型是固定的，也就是说 extent 256 、 extent 512 这些区最开始的2个页面 的类型是固定的，分别是：

• XDES 类型：全称是 extent descriptor ，用来登记本组256个区的属性，也就是说对于在 extent 256 区中的该类型页面存储的就是 extent 256 ~ extent 511 这些区的属性，对于在 extent 512 区中的该 类型页面存储的就是 extent 512 ~ extent 767 这些区的属性。上边介绍的 FSP_HDR 类型的页面其实和 XDES 类型的页面的作用类似，只不过 FSP_HDR 类型的页面还会额外存储一些表空间的属性。

• IBUF_BITMAP 类型

引入的原因

​ 当数据量很小的时候，我们只需要有页然后通过页来建立我们的索引就可以了，但是对着数据量的增大，我们每向表中插入一条记录，本质上就是向该表的聚簇索引以及所有二级索引代表的 B+ 树的节点中插入数据。而 B+ 树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍 B+ 树索引的适用场景的时候特别提到范围查询只需 要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，就是所谓的 随机I/O 。所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可 以使用所谓的 顺序I/O 。

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

​ 事实上从理论上讲，引入不引入区对于我们的Innodb存储引擎的查询过程没有任何影响。

段

仅仅存在区的缺点

​ 引入了区的概念以后，我们再分配空间的时候就会按照区来分配。但是问题又来了，在b+树中，我们根据是否是叶子节点将页分成的不同的类型（如数据页、目录页），如果我们在分配空间的时候不去区分叶子节点与非叶子节点，那么当我们去范围查询数据的时候，构成链表的数据页在内存的分配上可能穿插目录页，导致数据页在内存分配上又不是连续的，则其遍历的效果又打了折扣。

段的引入

​ 所以设计者们把叶子节点和非叶子节点在区的分配上做了区分，非叶子节点和叶子节点都有自己各自的区。存放叶子节点的区的集合是一个段，存放非叶子节点的区的集合是另一个段.。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。

碎片区的引入

​ 默认情况下一个使用 InnoDB 存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存 储空间的，一个区默认占用1M存储空间，所以**默认情况下一个只存了几条记录的小表也需要2M（两个段，每个段都只有一个区，分别存储数据页和目录页）的存储空间么？**以后每次添加一个索引都要多申请2M的存储空间么？这对于存储记录比较少的表简直是天大的浪费。这种问题的出现主要在于区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。

​ 所以引入了碎片区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。**碎片区直属于表空 间，并不属于任何一个段。**所以此后为某个段分配存储空间的策略是这样的

• 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。

• 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间。

？这对于存储记录比较少的表简直是天大的浪费。这种问题的出现主要在于区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。

​ 所以引入了碎片区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。**碎片区直属于表空 间，并不属于任何一个段。**所以此后为某个段分配存储空间的策略是这样的

• 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。

• 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间。