mysql如何存储一行记录

mysql的数据存放在哪个文件？

众所周知，mysql的数据是保存在磁盘的，那么具体是保存在什么文件呢？

MySQL存储的行为是由存储引擎实现的，而InnoDB是我们常用的存储引擎，也是目前mysql默认的存储引擎。所以我们主要以InnoDB存储引擎展开讨论。

我们可以先来看看MySQL数据库的文件存放在哪个目录：

我们每创建一个database就会在/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 中每一张表的数据都存放在一个独立的 .idb 文件。

所以，一张数据库表的数据是保存在idb后缀的文件里的，这个文件也被称作独占表空间文件

表空间文件结构

表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构如图：

1、行（row）

表中的记录都是按行进行存放的，每行记录根据不同的行格式有不同的存储结构

2、页（page）

虽然记录是按行存储的，但是数据库的读取不是以行(row)为单位，否则一次读取（一次I/O）只能处理一行数据，效率就非常的低。因此，InnoDB的数据是按页为单位来读写的，当需要读一条记录的时候，是以页为单位，将整体读入内存。

默认每个页的大小为16KB，也就是最多能保证16KB的连续存储空间。

页是InnoDB存储引擎磁盘管理的最小单元，所以数据库每次读写都是以16KB为单位，一次最少从磁盘中读取16K的内容到内存中，一次最少把内存中16K的内容刷新到磁盘。

页有很多类型，常见的有数据页、undo日志页、溢出页等。数据表中的行记录是用数据页来管理的。

3、区（extent）

我们知道InnoDB存储引擎是用B+树来组织数据的。

B+树中的每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离的非常远，那么磁盘查询时就会有大量的随机I/O，而随机I/O是非常慢的。

解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。

那具体怎么解决呢？

在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

4、段（segment）

表空间是由各个段组成的，段是由多个区组成的。段一般分为数据段、索引段和回滚段等。

索引段：存放 B + 树的非叶子节点的区的集合；
数据段：存放 B + 树的叶子节点的区的集合；
回滚段：存放的是回滚数据的区的集合，事务隔离的时候， MVCC 利用了回滚段实现了多版本查询数据。

InnoDB的行格式有哪些？

行格式就是一条记录的存储结构

InnoDB提供了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 行格式，因为 Dynamic 和 Compressed 这两个行格式跟 Compact 非常像，在弄清楚Compact行格式之后去了解其他两个也会事半功倍。

Compact行格式

我们可以看到，一条完整的记录分为记录的额外信息和记录的真实数据两部分。

记录的额外信息

记录的额外信息分为变长字段长度列表、null值列表和记录头信息

1、变长字段长度列表

varchar(n) 和 char(n) 的区别是什么，相信大家都非常清楚，char 是定长的，varchar 是变长的，变长字段实际存储的数据的长度（大小）是不固定的。

所以，在存储数据的时候也要把数据占用的大小存在变长字段长度列表里，读取数据的时候才能根据这个去读取对应长度的数据。其余TEXT、BLOB等变长字段都是这么实现的。

举个例子，我们创建如下的表：

CREATE TABLE `t_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;

字符集为ascii（所以每个字符占用一个字节），name和phone都是变长字段

现在t_user表中有这三条记录：

我们来看看这三条记录的行格式中的变长字段长度列表是怎样存储的

我们先看第一条记录：

name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；
phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；
age 列和 id 列不是变长字段，所以这里不用管。

这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放（等下会说为什么要这么设计），所以「变长字段长度列表」里的内容是「 03 01」，而不是「01 03」。

同样，第二条记录的行格式如下：

第三条记录中，phone的值是null，null不会放在行格式中记录的真实数据里，所以变长字段长度列表里不需要保存值为 NULL 的变长字段的长度。

为什么「变长字段长度列表」的信息要按照逆序存放？

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

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

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

每个数据库表的行格式都有「变长字段字节数列表」吗？

其实变长字段字节数列表不是必须的。

当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了，因为没必要，不如去掉以节省空间。

所以「变长字段长度列表」只出现在数据表有变长字段的时候。

2、NULL值列表

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

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

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

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

还是用上面的数据为例

第一条记录不存在null值，所以用二进制表示是这样的

由于InnoDB用整数字节的二进制位来表示NULL值列表，现在不足8位，所以要高位补0

所以对于第一条记录，NULL值列表用十六进制表示就是0x00。

接下来看第二条记录，age是null值，所以对于这条数据NULL值列表用十六进制表示是0x04

最后第三条，同理：

十六进制为0x06

所以我们把三条记录的null值列表填充完毕后，行格式是这样的：

同理，NULL 值列表也不是必须的。

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

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

「NULL 值列表」是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是 NULL，这时候怎么表示？

「NULL 值列表」的空间不是固定 1 字节的。

当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

3、记录头信息

记录头信息中包含的内容很多，这里就不一一列举了，这里说几个比较重要的：

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 个字节。

如果你熟悉 MVCC 机制，你应该就清楚 trx_id 和 roll_pointer 的作用了

varchar（n）中n的最大取值为多少？

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

也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。

varchar(n)字段类型的n代表的是最多存储的字符数量，并不是字节大小。

要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1个字符要占用多少字节，比如 ascii 字符集， 1 个字符占用 1 字节，那么 varchar(100) 意味着最大能允许存储 100 字节的数据。

单字段

前面我们知道，一行记录最大只能存储65535字节的数据

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

我们可以来验证一下

1
2
3

CREATE TABLE test ( 
`name` VARCHAR(65535)  NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

可以看到创建失败。

从报错信息可以看出一行数据的最大字节数是 65535（不包含 TEXT、BLOBs 这种大对象类型），其中包含了 storage overhead。

问题来了，这个 storage overhead 是什么呢？其实就是「变长字段长度列表」和「NULL 值列表」，也就是说一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和「NULL 值列表」所占用的字节数的。所以，我们在算 varchar(n) 中 n 最大值时，需要减去 storage overhead 占用的字节数。

这是因为我们存储字段类型为 varchar(n) 的数据时，其实分成了三个部分来存储：

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

前面创建表的时候，字段是允许为 NULL 的，所以会用 1 字节来表示「NULL 值列表」

「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。

所以，我们要先知道每个变长字段的「变长字段长度」需要用多少字节表示？具体情况分为：

条件一：如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」；
条件二：如果变长字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变长字段长度」；

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

所以在本案例中，在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。

可以看到创建成功

如果采用的是 UTF-8，varchar(n) 最多能存储的数据计算方式就不一样了：

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

多字段

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

行溢出后mysql如何处理？

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

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

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