【MySQL】InnoDB记录存储结构

短命女 2024-03-30 12:44 71阅读 0赞

#### 文章目录 ####

*  1. 前言
 *  2. InnoDB概述
 *  3. InnoDB行格式
 *   *  3.1 COMPACT 行格式
     *   *  3.1.1 记录的额外信息
         *  3.1.2 记录真实的数据
         *  3.1.3 CHAR(M)列的存储格式
     *  3.2 REDUNDANT 行格式
     *   *  3.2.1 字段长度偏移列表
         *  3.2.2 记录头信息
         *  3.2.3 NULL值处理
         *  3.2.4 CHAR(M)列的存储格式
     *  3.3 溢出列
     *  3.4 DYNAMIC行格式和COMPRESSED行格式

## 1. 前言 ##

对于MySQL来说，我们对它的认知并不多，只知道它的主要功能是存储数据。

但是 **这些数据存放在哪里？以什么格式存放？MySQL怎么访问这些数据？** 我们都不知道。

对于MySQL服务器来说，负责对表数据进行读取和写入工作的是存储引擎。在MySQL中有很多存储引擎，比如InnoDB、MyISAM等。**不同的存储引擎存放数据的格式一般是不一样的。**

对于MEMORY这个存储引擎来说，它不用磁盘存储数据，也就意味着服务器关闭后表的数据就消失了。

而**InnoDB是MySQL的默认存储引擎**，**它是一个将表中数据存储在磁盘上的存储引擎**。

--------------------

## 2. InnoDB概述 ##

InnoDB会将数据划分为若干个页，**以页作为磁盘和内存之间的交互的基本单位**。并且页的大小一般为16KB，也就是说，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中16KB内容刷新到磁盘中。这样的设计，使得磁盘和内存之间数据交互不那么慢。

在MySQL中，系统变量`innodb_page_size`表示了InnoDB的存储引擎中的页的大小，默认值为16384字节，也就是16KB。**但是需要注意的是，在MySQL服务器运行过程中不可以更改页面大小！！！！**

![img][]

--------------------

## 3. InnoDB行格式 ##

在InnoDB中，设计者设计了4种行格式

*  COMPACT
 *  REDUNDANT
 *  DYNAMIC
 *  COMPRESSED

> **行格式，也称记录格式**，是指记录在磁盘上的存放形式。

对于行格式，可以使用以下语法来指定行格式

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

--------------------

### 3.1 COMPACT 行格式 ###

COMPACT行格式的示意图如下

![在这里插入图片描述][71a7e9a0d61aca10f35041de68ed2148.png]

由示意图可以看出，一条记录分为两大部分，分别是**记录的额外信息和记录的真实数据**

--------------------

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

记录的额外信息包括三部分，分别是**变长字段长度列表、NULL值列表和记录头信息**。

> **什么是变长字段长度列表？**

在MySQL中，有一些变长的数据类型，比如VARCHAR等。**这些类型的列就可以被称为变长字段**。**变长字段存储的数据长度是不固定的，因此需要将这些数据占用的字节数存储起来。**

在 COMPACT 行格式中，**所有变长字段的真实数据占用的字节数都存放在记录的开头位置**，从而形成一个变长字段长度列表，各变长字段的真实数据占用的字节数按照列的顺序**逆序存放**。

**并且，并不是所有记录都有变长字段长度列表这部分，如果表中所有列都不是变长的数据类型或者所有列的值都是NULL的画，就不需要变长字段长度列表了。**

对于这样的一个表

![image-20221228152409690][]

表的数据如下

+------+-----+------+------+
    | c1   | c2  | c3   | c4   |
    +------+-----+------+------+
    | aaaa | bbb | cc   | d    |
    | eeee | fff | NULL | NULL |
    +------+-----+------+------+

**对于第一条数据来说，变长字段的内容长度如下**

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th>存储内容</th> 
   <th>内容长度（十进制表示）</th> 
   <th>内存长度（十六进制表示）</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>c1</td> 
   <td>‘aaaa’</td> 
   <td>4</td> 
   <td>0x04</td> 
  </tr> 
  <tr> 
   <td>c2</td> 
   <td>‘bbb’</td> 
   <td>3</td> 
   <td>0x03</td> 
  </tr> 
  <tr> 
   <td>c3</td> 
   <td>‘d’</td> 
   <td>1</td> 
   <td>0x01</td> 
  </tr> 
 </tbody> 
</table>

因此，变长字段长度列表字节串用十六进制表示效果如下

01 03 04
    #注意：这里实际上是没有空格的，只是为了方便展示

![image-20221228153038808][]

第一条记录的变长字段长度存储如上图所示。

\*\*像上面的变长字段的内容占用的字节数用1字节就可以表示(\*\*也就是内容长度4，3，1可以分别使用十六进制0x04、0x03、0x01表示)，但是如果变长字段的内容占用字节比较多，就需要使用2字节来表示了。

> **什么时候使用1字节表示？什么时候使用2字节表示呢？**

InnoDB有属于它的规则。

*  **假如某个字符集最多需要W字节来表示一个字符**。比如utf8mb4字符集中的W就是4，GBK字符集中的W就是2。
 *  对于变长类型VARCHAR(M)来说，**这种类型最多存储M个字符**，所以这种类型表示的字符串最多占用字节为`M × W`
 *  **假设该变长字段实际存储的字符串占用字节数为L**
 *  那么如果`M × W <= 255`，那么使用1字节来表示真实数据占用的字节数
 *  如果`M × W > 255`
    
     *  如果`L <= 127`，则使用1字节来表示真实数据占用的字节数
     *  如果`L > 127`，则使用2字节来表示真实数据占用的字节数

--------------------

> **什么是NULL值列表**

**NULL值列表是统一管理一条记录中值为NULL的列。**

它的处理过程如下：

1.  首先统计表中允许存储NULL的列有哪些。也就是排除被NOT NULL修饰的列。
2.  如果表中没有允许存储NULL的列，则NULL值列表就不存在了。
3.  否则，将每个允许存储NULL的列对应一个二进制，**二进制位按照列的存储逆序排序**
    
    1.  **二进制位的值为1时，表示该列的值为NULL**
    2.  **二进制位的值为0时，表示该列的值不为NULL**
4.  MySQL规定NULL值列表必须用整个字节的位表示，二进制位个数不是整个字节的需要补上0

![img][img 1]

因此，对于上面提到的这个表

+------+-----+------+------+
    | c1   | c2  | c3   | c4   |
    +------+-----+------+------+
    | aaaa | bbb | cc   | d    |
    | eeee | fff | NULL | NULL |
    +------+-----+------+------+

**两条记录的NULL值列表如下**

![image-20221228155408508][]

![image-20221228155420055][]

> **第二条记录的06的由来是这样的：**
> 
> 在表中，c2列被NOT NULL修饰，所以不可能为NULL，只有c1和c3和c4可能为NULL。
> 
> 但在第二条记录中，只有c1不为NULL，因此倒序排列后的NULL列表为110
> 
> 110转化为十进制就是06

--------------------

> **什么是记录头信息？**

**记录头信息是由固定的5字节组成，用于描述记录的一些属性，5字节也就是40个二进制位。**

![img][img 2]

记录头信息中的各二进制代表的详细信息如下图所示

![image-20221228160147258][]

![image-20221228160255688][]

--------------------

#### 3.1.2 记录真实的数据 ####

这里说的记录真实的数据除了自己定义的列的数据外，MySQL还会默认给每条记录加一些列。

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th>是否必需</th> 
   <th>占用空间</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>row_id</code></td> 
   <td>否</td> 
   <td>6字节</td> 
   <td>行ID，唯一标识一条记录</td> 
  </tr> 
  <tr> 
   <td><code>trx_id</code></td> 
   <td>是</td> 
   <td>6字节</td> 
   <td>事务ID</td> 
  </tr> 
  <tr> 
   <td><code>roll_pointer</code></td> 
   <td>是</td> 
   <td>7字节</td> 
   <td>回滚指针</td> 
  </tr> 
 </tbody> 
</table>

**InnoDB表的主键生成策略：**

1.  优先使用用户自定义主键作为主键
2.  如果没有自定义主键，那么就会选取一个不允许存储NULL值的UNIQUE键作为主键。
3.  如果表中连不允许存储 NULL值的 UNIQUE 键都没有定义，，则InnoDB 会为表默认添加一个名为 row\_id 隐藏列作为主键.

![img][img 3]

在上面提到的表中，数据如下

+------+-----+------+------+
    | c1   | c2  | c3   | c4   |
    +------+-----+------+------+
    | aaaa | bbb | cc   | d    |
    | eeee | fff | NULL | NULL |
    +------+-----+------+------+

*  这个表使用的是ascii字符集，因此0x61616161表示的就是字符串’aaaa’，以此类推
 *  但是需要注意的是，c3列用的是CHAR(10)类型的，它存储的是’cc’，得到的结果是0x6363。**CHAR是不可变的，虽然只占用了2字节，但是c3依然会占用10字节，其余8个字节用空格字符填充，也就是0x20**
 *  对于第二条记录的NULL值，它们被存储在前面的NULL值列表处，就不在记录真实数据存储了。

--------------------

#### 3.1.3 CHAR(M)列的存储格式 ####

**在表结构使用ascii字符集的时候，由于该字符集采用固定的一个字节来编码一个字符，是一个定长编码字符集，所以说CHAR不属于变长字段。**

**但是如果采用变长编码的字符集（如utf8表示1~3字节），即使使用的类型为CHAR，该类型对应的类也会存储到变长字段长度列表中！！！**

![img][img 4]

**在COMPACT 行格式中规定，采用变长编码字符集的CHAR(M)类型的列要求至少占用M个字节，意思就是说我们向列中存储一个空字符串也会占用10字节。**

这样设计的意图是想以后更新该列的时候，如果新值大于旧值得字节长度但不大于10字节的时候，可以直接更新，而不用重新分配一个新的记录空间。

--------------------

### 3.2 REDUNDANT 行格式 ###

REDUNDANT 行格式的示意图如下

![image-20221228162840658][]

--------------------

#### 3.2.1 字段长度偏移列表 ####

在REDUNDANT行格式中，没有了“变长”，意味着 REDUNDANT行格 会把该条记录中所有列（包括隐藏列）的长度信息按照逆序存储到字段长度偏移列表中

**它的偏移量是通过采用两个相邻偏移量的差值来计算各个列值的长度的**

继续以这个表的数据来说

+------+-----+------+------+
    | c1   | c2  | c3   | c4   |
    +------+-----+------+------+
    | aaaa | bbb | cc   | d    |
    | eeee | fff | NULL | NULL |
    +------+-----+------+------+

![img][img 5]

第一条记录的字段长度偏移列表为

25 24 1A 17 13 0C 06

顺序排序后

06 0C 13 17 1A 24 25

这个是怎么计算出来的？

*  第一列（row\_id）的长度就是0x06个字节，也就是6字节
 *  第二列（trx\_id）的长度就是（0x0C - 0x06），也就是6个字节
 *  第三列（roll\_pointer）的长度就是（0x13 - 0x0C）个字节，也就是7个字节

其他列以此类推即可！！！

--------------------

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

REDUNDANT 行格式的记录头信息占用6个字节，总计48个二进制位

![image-20221228163712228][]

第一条记录中的头信息是

00 00 10 0F 00 BC

根据此可以得出各属性信息

![image-20221228163919504][]

对比COMPACT 行格式可以发现，REDUNDANT 行格式对了`n_field`和`1byte_offs_flag`这两个属性

但是缺少了`record_type`这个属性

`1byte_offs_flag`的值决定了每个列的偏移量是使用1字节还是2字节

*  当它的值为1的时候，表示使用1字节来表示存储偏移量
 *  当它的值为0的时候，表示使用2字节来表示存储偏移量。

> 那么`1byte_offs_flag`的值怎么选择的呢？

这是根据REDUNDANT行格式记录的真实数据占用的大小来判断的

*  **当记录的真实数据占用的字节数不大于 127** (十六进制 0x7F，二进制 01111111)时，每个列对应的偏移量占用 1 字节。
 *  **当记录的真实数据占用的字节数大于127**，但不大于32767(十六进制0x7FFF，二进制0111111111111111)时，每个列对应的偏移量占用2字节。
 *  有没有记录的真实数据大于32767的情况呢?有，不过此时记录的一部分已经存放到了所谓的溢出页中(后面我们会详细讨论)，在本页中只保留前 768字节和20字节的溢出页面地址(当然这20字节中还记录了一些别的信息)。在这种情况下只使用2字节来存储每个列对应的偏移量就够了。

--------------------

#### 3.2.3 NULL值处理 ####

**REDUNDANT行格式中将列对应的偏移量值得第一个比特位作为是否为NULL的依据，这个比特位也成为NULL比特位**。

这就是为什么只要记录的真实数据大于 127 （十六进制0x7F，二进制01111111）时候，就采用2字节来表示一个列对应的偏移量了。

+------+-----+------+------+
    | c1   | c2  | c3   | c4   |
    +------+-----+------+------+
    | aaaa | bbb | cc   | d    |
    | eeee | fff | NULL | NULL |
    +------+-----+------+------+

对于这里的第二条记录，长度偏移列表为

A4 A4 1A 17 13 0C 06

顺序排序就是

06 C 13 17 1A A4 A4

![img][img 5]

**由于c3列为定长类型，所欲NULL值也将占用记录的真实数据部分，所以使用0x00字节填充**

**而如果是变长数据类型，则不再记录的真实数据占用任何存储空间。**

--------------------

#### 3.2.4 CHAR(M)列的存储格式 ####

对于REDUNDANT行格式来说，不管使用的字符集是什么，只要使用CHAR(M)类型，**该列的真实数据占用的内存空间大小就是该字符集表示一个字符最多需要的字节数和M的乘积。**

比如utf8字符集的CHAR(10)类型的列，其真实数据占用的存储空间大小始终为30字节\*\*。这样做，在更新数据的时候，就不需要申请新的存储空间了。\*\*

--------------------

### 3.3 溢出列 ###

**在COMPACT 行格式和REDUNDANT 行格式中，对于占用内存空间非常多的列，在记录真实数据处只会存储该列的一部分数据，将其他数据分散存储在几个其他页中。**

并在记录真实数据处用20字节存储指向这些页的地址。

![img][img 6]

--------------------

### 3.4 DYNAMIC行格式和COMPRESSED行格式 ###

DYNAMIC行格式和COMPRESSED行格式在处理溢出列的时候，**它们不会在记录的真实数据处存储该溢出列真实数据 的前 768 字节**，而是把该列的所有真实数据都存储到溢出页中，**只在记录的真实数据处存储 20 节大小的指向溢出页的地址**

而COMPRESSED不同于DYNAMIC的一点在于会使用压缩算法进行压缩，以节省空间。

![image-20221228170425520][]

除了处理溢出的时候会于COMPACT 行格式不一样外，其他都于COMPACT 行格式很像。

--------------------

> 参考：
> 
>  *  《MySQL是怎样运行的：从根儿上理解 MySQL》
>  *  [《MySQL是怎么运行的：从根儿上理解MySQL》(1-5)学习总结\_月亮的-影子的博客-CSDN博客\_从根上理解mysql][MySQL_MySQL_1-5_-_-CSDN_mysql]
>  *  [ mysql是怎样运行的-从根儿上理解mysql学习笔记(一)\_spencersong的博客-CSDN博客\_mysql 是怎样运行的][mysql_-_mysql_spencersong_-CSDN_mysql]

--------------------

[img]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/5c019454c2b14057a16cb63c318882e6.png
[71a7e9a0d61aca10f35041de68ed2148.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/97687209a50f40f1b5009052e91e5651.png
[image-20221228152409690]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/cd16a32dd718469896795fb16b010686.png
[image-20221228153038808]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/62ff3d23bf444f94b6c2437dff45aad5.png
[img 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/0dd3f731c23649e0b5f1ae547e1a6305.png
[image-20221228155408508]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/de242863a2c84e25842f46e1d45467b6.png
[image-20221228155420055]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/ba71c572a84748f98a69e79cf9955614.png
[img 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/08b1f3d26edc472ca61a8af562a42e12.png
[image-20221228160147258]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d51d7e6f286d4772953a13af135d2929.png
[image-20221228160255688]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/6c7d6ed5f1244067b7fb1c0709a99f6d.png
[img 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d811cde183214483838a732e8e994532.png
[img 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c02250d4065444849cd0ca8f0e101119.png
[image-20221228162840658]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/77e816ae24214d56a83372b93ddd6792.png
[img 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/bd6e66cba54c48798b0f2290a98a7a7b.png
[image-20221228163712228]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/230c6a8a05e94173ae2dd6b5eeb1db7f.png
[image-20221228163919504]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/abd0a25e18de4a1c891b460ef64ccd3c.png
[img 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b65e374783fb43ae90db8daf318fe678.png
[image-20221228170425520]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c4cfd202f7f949f89bddf7422434d981.png
[MySQL_MySQL_1-5_-_-CSDN_mysql]: https://blog.csdn.net/m0_46388866/article/details/121056570
[mysql_-_mysql_spencersong_-CSDN_mysql]: https://blog.csdn.net/none123java321/article/details/113174336