MySQL：对于一行数据，在磁盘上是怎么存储的数据的

OceanStar的学习笔记

已于 2022-07-21 16:53:10 修改

阅读量1.4k

点赞数 4

分类专栏：数据库文章标签： mysql 数据库 sql

于 2021-12-20 13:49:16 首次发布

原文链接：https://apppukyptrl1086.pc.xiaoe-tech.com/index

版权

数据库专栏收录该内容

289 篇文章 19 订阅

订阅专栏

行格式

我们在插入数据时，都是以行为单位的。也就是一行一行的插入数据的，这些数据在磁盘上的存放形式也被称为行格式。
InnoDB存储引擎支持四种不同的行格式:
- COMPACT
- REDUNDANT
- DYNAMIC
- COMPRESSED
行格式决定了数据页中的每一行数据，在磁盘上是怎么存储的
我们可以对一个表指定它的行存储的格式是什么样的。

如何指定行格式

（1）创建表时指定

[charset_table_db]> use charset_table_db;

[charset_table_db]> CREATE TABLE record_format_table (
    ->  c1 VARCHAR(10),
    ->  c2 VARCHAR(10) NOT NULL,
    ->  c3 CHAR(10),
    ->  c4 VARCHAR(10)
    -> ) CHARSET=ascii ROW_FORMAT=COMPACT;

（2）修改方法

[charset_table_db]> ALTER TABLE record_format_table ROW_FORMAT=COMPACT

COMPACT行格式

对于COMPACK行存储格式，在这种格式下，每一行数据实际存储的时候，大致格式类似下面这样：
在这里插入图片描述

也就是说，对于每一行数据，它包括两大部分：

记录的额外信息：是一些头部字符，用来描述这一行数据。包括三个部分：
- 变长字段长度列表
- NULL值列表
- 记录的头信息
记录的真实数据：每一个字段(列)具体的值

这就是所谓的行格式。

记录的额外信息

变长字段长度列表

变长字段在磁盘中是怎么存储的

在MySQL中有一些字段的长度是变长的，是不固定的，比如varchar(10)之类的这种类型的字段，实际上它里面存放的字符串的长度是不固定的，有可能是“hello”这么一个字符串，也可能是“a”这么一个字符串。
假设我们当前有一行数据，它的几个字段的类型为VRACHAR(10)，CHAR(1)，CHAR(1)，那么他第一个字段是VARCHAR(10)，这个长度是可能变化的，所以这一行数据可能就是类似于：hello a a，这样子，第一个字段的值是“hello”，后面两个字段的值都是一个字符，就是一个a
然后另外一行数据，同样也是这几个字段，他的第一个字段的值可能是“hi”，后面两个字段也是“a”，所以这一行数据可能是类似于：hi a a。一共三个字段，第一个字段的长度是是不固定的，后面两个字段的长度都是固定的1个字符。
那么现在，需要上上面两条数据写入一个磁盘文件里，那么这个时候在一个磁盘文件里可能有下面的两行数据：hello a a hi a a。也就是说，两行数据在底层磁盘文件里是挨着存储的。
实际上，表里的很多行数据，最终落地在磁盘里的时候，都是上面这种样子的，一大坨数据放在一个磁盘文件里都是挨着存储的

存储在磁盘文件里的变长字段，为什么难以读取？

假设现在我们要读取上面的磁盘文件里的数据，要读取出来hello a a这一行数据，这个时候我们就遇到问题了：从这个磁盘文件里读取的时候，到底哪些内容是一行数据呢？
因为这个表里的第一个字段是VARCHAR(10)类型的，第一个字段的长度是多少我们是不知道的！所以有可能你读取出来的“hello a a hi”是一行数据，也可能读取出来的“hello a”是一行数据。在不知道一行数据的每个字段到底是多少长度的情况下，胡乱的去读取是不现实的，根本不知道磁盘文件里混成一坨的数据里，哪些数据是要读取的一行。

引入变长字段的长度列表，解决一行数据的读取问题

所以才要在存储每一行数据的时候，都保存一下它的变长字段的长度列表，这样才能解决一行数据的读取问题。
也就是说，在存储“hello a a”这行数据的时候，要带上一些额外的附加信息，比如第一块就是它里面的变长字段的长度列表。
也就是，这个hello的VARCHAR(10)类型的变长字段的值。可以看到“hello”的长度是5，也就是0x05，所以此时会在“hello a a”前面补充一些额外信息，首先是变长字段的长度列表，你会看到这行数据在磁盘文件里存储的时候，其实是类似如下格式： 0x05 null值列表数据头 hello a a
还有一行数据可能就是：0x02 null值列表数据头 hi a a，两行数据放在一起存储在磁盘文件里，看起来是如下所示的：

0x05 null值列表 数据头 hello a a 0x02 null值列表 数据头 hi a a

引入变长字段长度列表后，如何解决变长字段的读取问题

假设此时要读取“hello a a”这行数据，你首先会知道这个表里的三个字段的类型是VARCHAR(10) CHAR(1) CHAR(1)，那么此时你先要读取第一个字段的值，那么第一个字段是变长的，到底它的实际长度是多少呢？
此时你会发现第一行数据的开头有一个变长字段的长度列表，里面会读取到一个0x05这个十六进制的数字，发现第一个变长字段的长度是5，于是按照长度为5，读取出来的第一个字段的值，就是“hello”
接着你知道后继两个字段都是CHAR(1)，长度都是固定的1个字符，于是此时就依次按照长度为1读取出来后继两个字段的值，分别是“a”，“a”，于是最终你会读取出来“hello a a”这一行数据
接着假设你要读取第二行数据，你先看一下第二行数据后的变长字段长度列表，发现它第一个变长长度的字段是0x02，于是就读取长度为2的字段值，就是“hi”，再读取两个长度固定为1的字符串，都是“a”，此时读取出来“hi a a”这行数据

如果有多个变长字段，如何存放它们的长度？

比如一行数据有VARCHAR(10) VARCHAR(5) VARCHAR(20) CHAR(1) CHAR(1)，一共5个字段，其中三个是变长字段，此时假设一行数据是这样的：hello hi hao a a。
此时在磁盘中存储的，必须在它开头的变长字段长度列表中存储几个变长字段的长度，一定要注意一点，它是逆序存储的。也就是说先存放VARCHAR(20)这个字段的长度，然后存放VARCHAR(5)这个字段的长度，最后存放VARCHAR(10)这个字段的长度。
现在hello hi hao三个字段的长度分别是0x05 0x02 0x03，但是实际存放在变长字段长度列表的时候，是逆序放的，所以一行数据实际存储可能是下面这样的：

0x03 0x02 0x05 null值列表 头字段 hello hi hao a a

NULL值列表

问题：一行数据中的多个NULL字段值在磁盘上怎么存储？

为什么一行数据里的NULL值不能直接存储？

磁盘上存储的一行数据里除了变长字段长度列表之外，还有另外一块特殊的数据区域，就是NULL值列表。
这个所谓的NULL值列表，就是你一行数据可能有的字段值是NULL，比如你有一个name字段，它是允许为null的，那么实际上存储的时候，如果你没给它赋值，那么这个字段就是null。
那么假设这个字段的NULL值在磁盘上存储的时候，就是按照“NULL”这个字符串来存储，是不是很浪费存储空间？本来它就是个NULL，还存个“NULL”字符串，不是多此一举吗？
所以实际上在磁盘上存储数据的时候，一行数据里的NULL值是肯定不会直接按照字符串的方式存放在磁盘上浪费空间的。

NULL值是以二进制bit位来存储的

对所有的NULL值，不通过字符串在磁盘上存储，而是通过二进制的bit位来存储，一行数据里假设有多个字段的值都是NULL，那么这多个字段的NULL，就会以bit位的形式存放在NULL值列表中。

举个例子：

假设有一个表

CREATE TABLE customer (
name VARCHAR(10) NOT NULL,
address VARCHAR(20),
gender CHAR(1),
job VARCHAR(30),
school VARCHAR(50)
) ROW_FORMAT=COMPACT;

上表示一个客户表，里面有5个字段，分别为name、address、genderjob、school，就代表了客户的姓名、地址、性别、工作以及学校。其中有4个变长字段，还有一个定长字段，然后第一个name字段声明了NOT NULL，其中4个没有，就有可能为NULL。
假设有一行数据jack NULL m NULL xx_school，这5个字段里有两个字段都是NULL。那这行数据在磁盘上是怎么存储的呢？

一行数据的磁盘存储格式

一行数据在磁盘上的存储格式应该是下面这样的：

变长字段长度列表 NULL值列表 头信息 column1=value1 column2=value2 ... columnN=valueN

对于上表，我们需要先看变长字段长度列表应该放什么东西

它一共有4个变长字段，那么按照我们上面说的，是不是应该按照逆序的顺序，先放school字段的长度，再放job、address、name这几个变长字段的长度？
理论上是这样的，但是这里要区分一个问题，那就是如果这个变长字段的值是NULL，就不用在变长字段长度列表里存放它的值长度了，所以在上面这行数据中，只有name和school两个变长字段是有值的，把它们的长度按照逆序放在变长字段长度列表中就可以了，如下所示：

0x09 0x04 NULL值列表 头信息 column1=value1 column2=value2 ... columnN=valueN

接着来看NULL值列表，这个NULL值列表是这样存放的，你所有允许值为NULL的字段，注意是允许，不是说一定值是NULL，只要是允许为NULL的字段，在这里每个字段都有一个二进制bit位的值，如果bit值是1说明是NULL，如果是0说明不是NULL。
比如上面4个字段都允许为NULL，每个人都会有一个bit位，这一行数据的值是“jack NULL m NULL xx_school”，然后其中2个字段是null，2个字段不是null，所以4个bit位应该是：1010
但是实际放在NULL值列表的时候，他是按逆序放的，所以在NULL值列表里，放的是：0101，整体这一行数据看着是下面这样的

0x09 0x04 0101 头信息 column1=value1 column2=value2 ... columnN=valueN

另外就是它实际NULL值列表存放的时候，不会说仅仅是4个bit位，它一般起码是8个bit位的倍数，如果不足8个bit为就高位补0，所以它实际存放看起来是下面这样的：

0x09 0x04 00000101 头信息 column1=value1 column2=value2 ... columnN=valueN

磁盘上的一行数据到底是如何读取出来的呢？

0x09 0x04 00000101 头信息 column1=value1 column2=value2 ... columnN=valueN

首先必须要把变长字段长度列表和NULL值列表读取出来，通过综合分析一下，就知道有几个变长字段，哪几个变长字段是NULL。
此时就可以从变长字段长度列表中解析出来不为NULL的变长字段的值长度，然后也知道哪几个字段是NULL的，此时根据这些信息，就可以从实际的列值存储区域里，把每个字段的值读取出来了。
如果是变长字段的值，就按照它的值长度来读取；如果是NULL，就知道没有值存储，为NULL；如果是定长字段，就按照定长长度读取。这样就可以完美的把一行数据的值都读取出来了

记录头信息

磁盘文件中，40个bit位的数据头以及真实数据是如何存储的

在磁盘上存储数据的时候，每一行数据都会有变长字段长度列表，逆序存放这行数据里的变长字段的长度。

然后会有NULL值列表，对于允许为NULL的字段都会有一个bit位标识这个字段是否为NULL，也是逆序排序的。

每一行数据存储的时候，还得有40个bit位的数据头，这个数据头是用来描述这行数据的。

这40个bit位里，第一个bit位和第二个bit位，都是预留位，是没任何含义的
接下来是delete_mask，它标识这行数据是否被删除了。也就是说在MySQL里删除一行数据的时候，未必是马上把它从磁盘上清理掉，而是在数据头里面用1个bit标记它已经被删除了
下一个是min_rec_mask，在B+树里每一层的非叶子节点里的最小值都有这个标记
然后是4bit的n_owned，记录了一个记录数
接着有13个bit位是heap_no，它表示当前这行数据在记录堆里的位置
然后是34个bit为的record_type，表示这行数据的类型。其中0表示普通类型，1表示B+树的非叶子节点，2表示最小值数据，3表示最大值数据
最后是16个bit的next_record，它执行它下一行数据的指针、

记录的真实数据

一行数据在磁盘文件里存储的时候，实际上首先会包含自己的变长字段的长度列表，然后是NULL指列表，然后是数据头，接着才是真实数据。那么真实数据是如何存储的呢？

存储真实数据的时候，并没有什么特别的，无非就是按照我们哪个字段里的数据值去存储就行了。

举个例子，对于数据“jack NULL m NULL xx_school”，它的真实存储如下：

0x09 0x04 00000101 0000000000000000000010000000000000011001 jack m xx_schoo

先是他的变长字段的长度，用十六进制来存储，然后是NULL值列表，指出了谁是NULL，接着是40个bit位的数据头，然后是真实的数据值，就放在后面。

在读取这个数据的时候：

它会根据变长字段的长度，先读取出来jack这个值，因为它的长度是4，就读取4个长度的数据，jack就出来了。
然后发现第二个字段是NULL，就不用读取了
第三个是定长数据，直接读取一个字段就可以了，就得到了m
第四个字段是NULL，不用读取了
第五个字段变长字段长度是9，可以读取出xx_schoo

但是等等，大家觉得真正在磁盘上存储的时候，我们那些字符串就是直接这么存储在磁盘上吗？

显然不是的！实际上字符串这些东西都是根据我们数据库指定的字符集编码，进行编码之后再存储的，所以一行数据应该为：

0x09 0x04 00000101 0000000000000000000010000000000000011001 616161 636320 6262626262

从上面可以看到，我们的字符串和其他类型的数值最终都会根据字符集编码，变成一些数字和符号存储在磁盘上。

另外，实际存储一行数据的时候，会在它的真实数据部分，加入一些隐藏字段：

首先有一个DB_ROW_ID字段，这就是一个行的唯一标识，是数据库内部搞的一个标识，不是主键ID字段。如果我们没有指定主键和unique_key唯一索引的时候，它就内部自动加一个ROW_ID作为主键
接着是一个DB_TRX_ID字段，这是跟事务相关的，表示哪个事务更新的数据，为事务ID
最后是一个DB_ROLL_PTR字段，这是回滚指针，用来进行事务回滚

加上这几个隐藏字段之后，实际一行数据看起来如下：

0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C（DB_ROW_ID）
00000000032D（DB_TRX_ID） EA000010078E（DB_ROL_PTR） 616161 636320 6262626262

在磁盘上的数据，每一行数据就是类似“0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C（DB_ROW_ID）00000000032D（DB_TRX_ID） EA000010078E（DB_ROL_PTR） 616161 636320 6262626262”这样的东西。这样我们就初步把磁盘上的数据和内存里的数据关联起来了

数据页

无论如何，每一行数据最终都要保存到磁盘中，问题是我们是没插入/更新一行数据，就直接操作磁盘上的数据的吗？

不是。

InnoDB会将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，这个页叫做数据页。
也就是说，InnoDB在存储数据的时候，是通过数据页的方式来组装数据的。
对于数据页的每一行数据，都有对应的行格式。
- 当我们在数据库里面插入一行数据的时候，实际上是在内存里插入一个有复杂存储结构的一行数据，然后随着一些条件的发生，这行数据会被刷到磁盘文件里去。
- 在磁盘文件存储的时候，这样数据也是按照复杂的存储结构去存放的。
- 每一行数据都是放在数据页里面的，我们是以数据页为单位把磁盘上的数据加载到内存的缓存页里来的，也是以页为单位，把缓存页的数据刷入磁盘上的数据页中。

那么每一页有多大呢？

InnoDB中页的大小可以通过系统变量innodb_page_size查看
- 默认是16KB。也就是一般情况下：
  - 一次至少从磁盘中读取16KB的内容的内存中
  - 一次至少把内存中的16KB内容刷新到磁盘中
- 这个变量大小只能在第一次初始化MySQL数据目录时指定（也就是通过mysqld --initialize时指定），一旦指定之后就不能更改（也就是在服务器运行过程中不可以更改这个值的大小）

show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

那么如果以后数据的大小超过了页的大小怎么办呢？

比如有一个表的字段类型为varchar(56632)，意识是最大可以包含65532个字符，这就远大于16KB的大小了，这就是说这一行的这个字段就远超一个数据页的大小了！

这个时候它会在那一页里存储你这行数据，然后在哪个字段中，仅仅包含一部分数据，同时包含一个20字节的指针，指向其他的一些数据页，这些数据页用链表串联起来，存放这个varchar(66632)超大字段里的数据。

在这里插入图片描述
这个过程叫做行溢出，就是说一行数据存储的内容太多了，一个数据页都放不下了，此时只能溢出这个数据页，把数据溢出存放到其他数据页里去，这些数据页就叫做溢出页。

包括其他的一些字段类型都是一样的，比如TEXT、BLOB这种类型的字段，都有可能溢出，如果溢出了一行数据就会存储在多个数据页里。

总结

问题：MySQL为什么要设计这么一套复杂的数据存取机制，要基于内存、日志、磁盘上的数据文件来完成数据的读写呢？为什么对insert、update请求，不直接更新磁盘文件里的数据呢？

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据，虽然技术上是可以做到的，但是必然会导致执行请求的性能极差。

因为磁盘随机读写的性能是最差的，所以直接更新磁盘文件，必然导致我们的数据库无法抗下任何一点点稍微高并发一点的场景。

所以MySQL才设计了如此复杂的一套机制，通过内存里更新数据，然后写redo log以及事务提交，后台线程不定时刷新内存里的数据到磁盘文件里。

通过这种方式保证，每个更新请求，尽量就是更新内存，然后顺序写日志文件。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是比较高的，因为顺序写磁盘文件，它的性能要远高于随机读写磁盘文件。

也正是通过这套机制，才能让MySQL数据库在较高配置的磁盘上，每秒可以抗下几千的读写请求

问题：当我们要执行update之类的SQL语句的时候，必然要涉及到对数据的更新操作，那么此时对数据时在哪里更新的？

此时并不是直接去更新磁盘文件，而是把磁盘上的一些数据加载到内存里来，然后对内存中的数据进行更新，同时写redo log到磁盘上去
在这里插入图片描述

但是这里就有一个问题了，难道我们每次都是把磁盘里的一条数据加载到内存里去进行更新，然后下次要更新别的数据的时候，再从磁盘里加载另外一条数据到内存里去？

肯定不行，这样效率太低了。为此innodb存储引擎引入了一个数据页的概念，也就是把数据组织成一页一页的概念，每一页有16kb，每次加载磁盘的数据到内存里的时候，至少加载一页数据进去，如下图：
在这里插入图片描述

假设我们有一次要更新一条id=1的数据：update xxx set xxx=xxx where id=1

那么此时他会把id=1这条数据所在的一页数据都加载到内存里去，这一页数据里，可能还包含了id=2，id=3等其他数据。

然后我们更新完id=1的数据之后，接着更新id=2的数据，那么此时是不是就不用再次读取磁盘里的数据了

这就是数据页的意义，磁盘和内存之间的数据交换通过数据页来执行，包括内存里更新后的脏数据，刷回磁盘的时候，也是至少一个数据页刷回去。
在这里插入图片描述
当IO线程把内存里的脏数据刷到磁盘上去的时候，也是以数据页为单位来刷回去的。

OceanStar的学习笔记

关注

4
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL：对于一行数据，在磁盘上是怎么存储的数据的

每一行数据都是放在数据页里面的，我们是以数据页为单位把磁盘上的数据加载到内存的缓存页里来的，也是以页为单位，把缓存页的数据刷入磁盘上的数据页中。那SQL语句中的表、行、字段的概念，是怎么变成MySQL里的表空间、数据区、数据页这些概念呢？当我们写入数据库的一行数据，在磁盘上是怎么存储的？为什么不直接更新磁盘上的数据？MySQL为什么要设计这么一套复杂的数据存取机制，要基于内存、日志、磁盘上的数据文件来完成数据的读写呢？为什么对insert、update请求，不直接更新磁盘文件里的数据呢？因
复制链接

扫一扫

专栏目录