一条MYSQL记录是如何储存的

痛定思痛。 2023-10-13 11:43 44阅读 0赞

主要还是看MYSQL默认的储存引擎 InnoDB

每个创建一个数据库 就会在/var/lib/mysql/ 目录里面创建一个以 database 为名的目录

目录里面包含以下三个文件

*  db.opt，用来存储当前数据库的默认字符集和字符校验规则。(数据库的数据)
 *  t\_order.frm ，t\_order 的**表结构**会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。(表的数据)
 *  t\_order.ibd，t\_order 的**表数据**会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb\_file\_per\_table 控制的，若设置了参数 innodb\_file\_per\_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。(那就是行的数据喽)

所以我们针对于一行数据的话 就要分析这个.ibd(独立表空间文件)

> ## 表空间结构: ##
> 
> ![33c3dda811854983b3be99e1c41715db.png][]
> 
> 行:
> 
> 数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。
> 
> 页:
> 
> 记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。(储存单位为行,读取单位为页)
> 
> 因此，**InnoDB 的数据是按「页」为单位来读写的**，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。
> 
> **默认每个页的大小为 16KB**，也就是最多能保证 16KB 的连续存储空间。
> 
> **页是 InnoDB 存储引擎磁盘管理的最小单元**，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。
> 
> 页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的
> 
> 区:
> 
> 我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。
> 
> B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。
> 
> 解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。
> 
> 那具体怎么解决呢？
> 
> **在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了**。
> 
> ##### 段（segment） #####
> 
> 表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。
> 
>  *  索引段：存放 B + 树的非叶子节点的区的集合；
>  *  数据段：存放 B + 树的叶子节点的区的集合；
>  *  回滚段：存放的是回滚数据的区的集合，

## InnoDB 行格式 ##

nnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式

*  Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了。
 *  由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
 *  Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

所以我们选择直接学习Compact

![f0f26b9045ca4ff4a7bffeffeecfe565.png][]

首先它长这个鬼样子

我们先来看

### 记录的额外信息 ###

#### 变长字段长度列表 ####

就拿我们常用的VARCHAR来说吧 我们都知道VARCHAR就是变长字段,所以我们储存的时候就会把它的长度给存进去,这样我们读数据的时候才知道要读多少,TEXT、BLOB 等变长字段也是这么实现的。

CREATE TABLE user (`id` int(11) NOT NULL,
           `name` VARCHAR(20) DEFAULT NULL,
           `phone` VARCHAR(20) DEFAULT NULL,
           `age` int(11) DEFAULT NULL,
           PRIMARY KEY (`id`) USING BTREE
         ) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

我们创建了这样一张表 注意我们设定了储存引擎为InnoDB(其实这个不用设置 默认的) 字符集ascii(所以一个字符一字节)行格式COMPACT

INSERT INTO user VALUES (1,'name','phone',18);

插入这样一条数据

name和phone 都是VARCHAR类型 也就说 他们两个都是变长数据类型

name长度 四字节 十六进制 为 0x04(刚才说了 这个列表里面储存的是变长字段的长度 储存的就是这个0x04)

phone长度 五字节 十六进制 为0x05

不过说起来我们用的是ASCII格式的字符集 可以这么计算 如果是utf8字符集呢? UTF8字符集是可变长度字符集 所以即使是CHAR类型也要放进去

这些变长字段的真实数据占用的字节数会按照列的顺序**逆序存放**

所以里面存放的是 05 04 而不是04 05

#### ![d9f9a5417127432ea6c399ff6385496b.png][] ####

不过你说为啥它好好待着不行非得反着放呢

#### 逆序存放的原因 ####

主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

就是这样的

![155558fcd444415ca8705e11c4e0e24c.png][]

「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以**使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率**。

同样的道理， NULL 值列表的信息也需要逆序存放。

CPU Cache就是 CPU高速缓存 它的内存很小 要保证他们被一个CPU Cache命中 就要减小他们物理内存上的距离 如果逆序放的话 我们用name举例 它的长度就会在变长字段信息列表里面更后面的地方 这样就离它的真实数据(列1值)距离近了

#### NULL值列表 ####

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

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

另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 `0`。

我们还是用刚才那行数据举例:

因为这行数据全有值 所以都不是null 也就全是0 因为id列是NOT NULL的所以不会有它的对应NULL值列表

![c41aafd0429f406e8dc26c0d96328c34.png][]

不过按照要求 必须用整数个字节的位表示 目前只有3位 我们还得补5位 形成8位

所以实际数据为

![34a5e4af892d47448f60144bffe49fe9.png][]

NULL 值列表也不是必须的。

**当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了**。

所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。

注意啊 是至少1字节 不是最多1字节 那我定义九个列 都是NOT NULL 那就 9位 就超过一字节了

#### 记录头信息 ####

这里面的数据太多了 例举几个重要的

*  delete\_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete\_mask 标记为 1。
 *  next\_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
 *  record\_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

## 记录真实数据 ##

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row\_id、trx\_id、roll\_pointer，

*  row\_id

如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row\_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row\_id 隐藏字段。row\_id不是必需的，占用 6 个字节。

*  trx\_id

事务id，表示这个数据是由哪个事务生成的。 trx\_id是必需的，占用 6 个字节。

*  roll\_pointer

这条记录上一个版本的指针。roll\_pointer 是必需的，占用 7 个字节。

### varchar(n) 中 n 最大取值为多少？ ###

**MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节**。

也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，

这个varchar(n) 里面的n参数 其实是字符 而非字节

比如 ascii 字符集， 1 个字符占用 1 字节，而UTF-8字符集 最多3个字节表示一个字符

#### 单字段的情况 ####

假设数据库表只有一个 varchar(n) 类型的列且字符集是 ascii，在这种情况下， varchar(n) 中 n 最大取值是 65535 吗？

并不是哦 我们前面提到的行结构除了真实数据以外额外信息 也就是我们在储存的时候

*  真实数据
 *  真实数据占用的字节数
 *  NULL 标识，如果不允许为NULL，这部分不需要

假如说 我们允许为NULL 那么就需要用1字节的NULL列表

再有呢

每个变长字段的「变长字段长度」需要用多少字节表示？具体情况分为：

*  条件一：如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」；
 *  条件二：如果变长字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变长字段长度」；
 *  这里的比较实际是 每个字符最大字节数\*括号里面的参数?=255

我们这里字段类型是 varchar(65535) ，字符集是 ascii，所以代表着变长字段允许存储的最大字节数是 65535，符合条件二，所以会用 2 字节来表示「变长字段长度」。

所以n值最大的是 65535-1-2 = 65532

当然，我上面这个例子是针对字符集为 ascii 情况，如果采用的是 UTF-8，varchar(n) 最多能存储的数据计算方式就不一样了：

*  在 UTF-8 字符集下，一个字符最多需要三个字节，varchar(n) 的 n 最大取值就是 65532/3 = 21844。

上面所说的只是针对于一个字段的计算方式。

我的理解是 utf8一个字符最多需要三个字节 两万多个字符总不能都是3字节吧 少点就把3字节的额外数据空间给余出来了

### 行溢出后，MySQL 是怎么处理的？ ###

MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 `16KB`，也就是 `16384字节`，而一个 varchar(n) 类型的列最多可以存储 `65532字节`，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会**发生行溢出，多的数据就会存到另外的「溢出页」中**。

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。在一般情况下，InnoDB 的数据都是存放在 「数据页」中。但是当发生行溢出时，溢出的数据会存放到「溢出页」中。

当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。大致如下图所示。

![4348408d88864f54a62ee1c386258d2e.png][]

上面这个是 Compact 行格式在发生行溢出后的处理。

Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。

这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中，看起来就像下面这样：

![4ae605deefcc4957a1b58c12388903a8.png][]

我们都知道记录是按行储存的,但是数据库的读取却不以行为单位,而以页为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。InnoDB中的数据页默认大小为16kb

这也就说明 每次数据库每次**读写**都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

![4bc7deb063014738b8b16b4d74ad1b1a.png][]

## 数据页结构 ##

![10a02e98e1be489e9a2d93820e3827f7.png][]

## File Header ##

有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表，如下图所示：

![b8b88da024f4b6fa3d4ea1cffaad3489.png][]

采用链表的结构是让数据页之间不需要是物理上的连续的，而是逻辑上的连续。

## User Records ##

**数据页中的记录按照「主键」顺序组成单向链表**，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。

因此，数据页中有一个**页目录**，起到记录的索引作用，就像我们书那样，针对书中内容的每个章节设立了一个目录，想看某个章节的时候，可以查看目录，快速找到对应的章节的页数，而数据页中的页目录就是为了能快速找到记录。

![a10411658e2e9657da2dd55424244da9.png][]

页目录创建的过程如下：

1.  将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录；
2.  每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n\_owned 字段（上图中粉红色字段）
3.  页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），**每个槽相当于指针指向了不同组的最后一个记录**。

从图可以看到，**页目录就是由多个槽组成的，槽相当于分组记录的索引**。然后，因为记录是按照「主键值」从小到大排序的，所以**我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录**，无需从最小记录开始遍历整个页中的记录链表。

以上面那张图举个例子，5 个槽的编号分别为 0，1，2，3，4，我想查找主键为 11 的用户记录：

*  先二分得出槽中间位是 (0+4)/2=2 ，2号槽里最大的记录为 8。因为 11 > 8，所以需要从 2 号槽后继续搜索记录；
 *  再使用二分搜索出 2 号和 4 槽的中间位是 (2+4)/2= 3，3 号槽里最大的记录为 12。因为 11 < 12，所以主键为 11 的记录在 3 号槽里；
 *  这里有个问题，**「槽对应的值都是这个组的主键最大的记录，如何找到组里最小的记录」**？比如槽 3 对应最大主键是 12 的记录，那如何找到最小记录 9。解决办法是：通过槽 3 找到 槽 2 对应的记录，也就是主键为 8 的记录。主键为 8 的记录的下一条记录就是槽 3 当中主键最小的 9 记录，然后开始向下搜索 2 次，定位到主键为 11 的记录，取出该条记录的信息即为我们想要查找的内容。

记录数目限制

*  第一个分组中的记录只能有 1 条记录；
 *  最后一个分组中的记录条数范围只能在 1-8 条之间；
 *  剩下的分组中记录条数范围只能在 4-8 条之间

我们再看看 B+ 树如何实现快速查找主键为 6 的记录，以上图为例子：

可以看到，在定位记录所在哪一个页时，也是通过二分法快速定位到包含该记录的页。定位到该页后，又会在该页内进行二分法快速定位记录所在的分组（槽号），最后在分组内进行遍历查找。

*  上面我们都是在说一个数据页中的记录检索，因为一个数据页中的记录是有限的，且主键值是有序的，所以通过对所有记录进行分组，然后将组号（槽号）存储到页目录，使其起到索引作用，通过二分查找的方法快速检索到记录在哪个分组，来降低检索的时间复杂度。
 *  但是，当我们需要存储大量的记录时，就需要多个数据页，这时我们就需要考虑如何建立合适的索引，才能方便定位记录所在的页。
    
    为了解决这个问题，**InnoDB 采用了 B+ 树作为索引**。磁盘的 I/O 操作次数对索引的使用效率至关重要，因此在构造索引的时候，我们更倾向于采用“矮胖”的 B+ 树数据结构，这样所需要进行的磁盘 I/O 次数更少，而且 B+ 树 更适合进行关键字的范围查询。
    
    InnoDB 里的 B+ 树中的**每个节点都是一个数据页**，结构示意图如下：
    
    ![7c1854e4e1fe0aaebe1f3ce9c68a12e3.png][]
    
    通过上图，我们看出 B+ 树的特点：
 *  只有叶子节点（最底层的节点）才存放了数据，非叶子节点（其他上层节）仅用来存放目录项作为索引。
 *  非叶子节点分为不同层次，通过分层来降低每一层的搜索量；
 *  所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；
 *  从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在\[1, 7)范围之间，所以到页 30 中查找更详细的目录项；
 *  在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；
 *  接着，在叶子节点（页16）中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

### 聚簇索引和二级索引 ###

另外，索引又可以分成聚簇索引和非聚簇索引（二级索引），它们区别就在于叶子节点存放的是什么数据：

*  聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；
 *  二级索引的叶子节点存放的是主键值，而不是实际数据。

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

*  如果有主键，默认会使用主键作为聚簇索引的索引键；
 *  如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
 *  在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。

二级索引的 B+ 树如下图，数据部分为主键值：

![74e5cde275a2ae6bc6da03d229109617.png][]

因此，**如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据。**

###  ###

[33c3dda811854983b3be99e1c41715db.png]: https://img-blog.csdnimg.cn/33c3dda811854983b3be99e1c41715db.png
[f0f26b9045ca4ff4a7bffeffeecfe565.png]: https://img-blog.csdnimg.cn/f0f26b9045ca4ff4a7bffeffeecfe565.png
[d9f9a5417127432ea6c399ff6385496b.png]: https://img-blog.csdnimg.cn/d9f9a5417127432ea6c399ff6385496b.png
[155558fcd444415ca8705e11c4e0e24c.png]: https://img-blog.csdnimg.cn/155558fcd444415ca8705e11c4e0e24c.png
[c41aafd0429f406e8dc26c0d96328c34.png]: https://img-blog.csdnimg.cn/c41aafd0429f406e8dc26c0d96328c34.png
[34a5e4af892d47448f60144bffe49fe9.png]: https://img-blog.csdnimg.cn/34a5e4af892d47448f60144bffe49fe9.png
[4348408d88864f54a62ee1c386258d2e.png]: https://img-blog.csdnimg.cn/4348408d88864f54a62ee1c386258d2e.png
[4ae605deefcc4957a1b58c12388903a8.png]: https://img-blog.csdnimg.cn/4ae605deefcc4957a1b58c12388903a8.png
[4bc7deb063014738b8b16b4d74ad1b1a.png]: https://img-blog.csdnimg.cn/4bc7deb063014738b8b16b4d74ad1b1a.png
[10a02e98e1be489e9a2d93820e3827f7.png]: https://img-blog.csdnimg.cn/10a02e98e1be489e9a2d93820e3827f7.png
[b8b88da024f4b6fa3d4ea1cffaad3489.png]: https://img-blog.csdnimg.cn/img_convert/b8b88da024f4b6fa3d4ea1cffaad3489.png
[a10411658e2e9657da2dd55424244da9.png]: https://img-blog.csdnimg.cn/img_convert/a10411658e2e9657da2dd55424244da9.png
[7c1854e4e1fe0aaebe1f3ce9c68a12e3.png]: https://img-blog.csdnimg.cn/img_convert/7c1854e4e1fe0aaebe1f3ce9c68a12e3.png
[74e5cde275a2ae6bc6da03d229109617.png]: https://img-blog.csdnimg.cn/img_convert/74e5cde275a2ae6bc6da03d229109617.png