MySQL 的数据存放在哪个文件？
MySQL 一行记录是怎么存储的？
MySQL 的数据存放在哪个文件？
大家都知道 MySQL 的数据都是保存在磁盘的 ，那具体是保存在哪个文件呢？
MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件
自然也不 同。
InnoDB 是我们常用的存储引擎，也是 MySQL 默认的存储引擎。所以，本文主要以 InnoDB 存储
引擎展开讨论 。
先来看看 MySQL 数据库的文件存放在哪个目录？
mysql > SHOW VARIABLES LIKE 'datadir' ;
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var /lib /mysql / |
+---------------+-----------------+
1 row in set (0.00 sec )我们每创建一个 database （数据库） 都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名
的目录，然后保存表结构和表数据的文件都会存放在这个目录里。
比如，我这里有一个名为 my_test 的 database ，该 database 里有一张名为 t_order 数据库表。
然后，我们进入 /var/lib/mysql/my_test 目录，看看 里面有什么 文件？
[root@xiaolin ~ ]#ls /var/lib/mysql/my_testdb.opt
t_order.frm
t_order.ibd可以看到，共有三个 文件，这三个 文件分别 代表着：
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 」的文件里的，这个文件也称
为独占表空间文件。
表空间文件的结构是怎么样的？
表空间由段（segment ）、区（extent ）、⻚（page ）、行（row ）组成，InnoDB 存储引擎的逻辑存
储结构大致如下图：
下面我们从下 往上一个个 看看 。
1、行（row ）
数据库表中的记录都是按行（row ）进行存放的，每行记录根据不同的行格式，有不同的存储结
构。
后面我们详细介绍 InnoDB 存储引擎的行格式，也是本文重点介绍的内容。
2、⻚（page ）
记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O
操作）只能处理一行数据，效率会非常低。
因此，InnoDB 的数据是按「⻚」为单位来读写的，也就是说，当需要读一条记录的时候，并不是
将这个行记录从磁盘读出来，而是以⻚为单位，将其整体读入内存。
默认每个⻚的大小为 16KB ，也就是最多能保证 16KB 的连续存储空间。
⻚是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次 读写都是以 16KB 为单位的，一次
最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。
⻚的类型有很多，常⻅的有数据⻚、undo 日志⻚、溢出⻚等等 。数据表中的行记录是用「数据
⻚」来管理的，数据⻚的结构这里我就不讲细说了，之前文章有说过，感兴趣的可以去看这篇文
章：换一个⻆度看 B+ 树
总之知道表中的记录存储在「数据⻚」里面就行。
3、区（extent ）
我们知道 InnoDB 存储引擎是用 B+ 树来组织 数据的。
B+ 树中每一层都是通过双向链表连接起来的，如果是以⻚为单位来分配存储空间，那么链表中相
邻的两个 ⻚之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机
I/O ，随机 I/O 是非常慢的。
解决这个问题也很简单，就是让链表中相邻的⻚的物理位置也相邻，这样就可以使 用顺序 I/O
了，那么在范围查询（扫描叶子节点）的时候性能就会很高。
那具体怎么解决呢？
在表中数据量大的时候，为某个索引分配空间的时候就不再按照⻚为单位分配了，而是按照区
（extent ）为单位分配。每个区的大小为 1MB ，对于 16KB 的⻚来说，连续的 64 个⻚会被划为一
个区，这样就使得链表中相邻的⻚的物理位置也相邻，就能使用顺序 I/O 了。
4、段（segment ）
表空间是由各个段（segment ）组成的，段是由多个区（extent ）组成的。段一般分为数据段、索
引段和回滚段等。
索引段：存放 B + 树的非叶子节点的区的集合；
数据段：存放 B + 树的叶子节点的区的集合；
回滚段：存放的是回滚数据的区的集合，之前讲事务隔离 的时候就介绍到了 MVCC 利用了回
滚段实现了多版本查 询数据。
好了，终于说完表空间的结构了。接下来，就具体讲一下 InnoDB 的行格式了。
之所以要绕一大圈才讲行记录的格式，主要是想让大家知道行记录是存储在哪个文件，以及行记
录在这个表空间文件中的哪个区域，有一个从上 往下切入的视⻆，这样理解起来不会觉得很 抽
象。
InnoDB 行格式有哪些？
行格式（row_format ），就是一条记录的存储结构。
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 行格式。
Redundant 行格式我这里就不讲了，因为现在基本没人用了，这次重点介绍 Compact 行格式，因
为 Dynamic 和 Compressed 这两个 行格式跟 Compact 非常像。
所以，弄懂了 Compact 行格式，之后你们 在去了解其他行格式，很快 也能看懂。
COMPACT 行格式⻓什么 样？
先跟 Compact 行格式混个脸熟，它⻓这样：
可以看到，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个 部分。
接下里，分别 详细说下。
记录的额外信息
记录的额外信息包含 3 个部分：变⻓字段⻓度列表、NULL 值列表、记录头信息。
- 变⻓字段⻓度列表
varchar(n) 和 char(n) 的区别是什么 ，相信大家都非常清楚，char 是定⻓的，varchar 是变⻓的，变⻓字段实际存储的数据的⻓度（大小）不固定的。
所以，在存储数据的时候，也要把数据占用的大小存起来，存到「变⻓字段⻓度列表」里面，读
取数据的时候才能根据这个「变⻓字段⻓度列表」去读取对应⻓度的数据。其他 TEXT 、BLOB 等
变⻓字段也是这么实现的。
为了 展示「变⻓字段⻓度列表」具体是怎么保存「变⻓字段的真实数据占用的字节数」，我们先创
建这样一张表，字符集是 ascii （所以每一个字符占用的 1 字节），行格式是 Compact ，t_user 表中name 和 phone 字段都是变⻓字段：
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 ;现在 t_user 表里有这三条记录：
接下来，我们看看看看 这三条记录的行格式中的 「变⻓字段⻓度列表」是怎样存储的。
先来看第一条记录：
name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01 ；
phone 列的值为 123 ，真实数据占用的字节数是 3 字节，十六进制 0x03 ；
age 列和 id 列不是变⻓字段，所以这里不用管。
这些变⻓字段的真实数据占用的字节数会按照列的顺序逆序存放（等下会说为什么 要这么设计 ），
所以「变⻓字段⻓度列表」里的内容是「 03 01 」，而不是 「01 03 」。
同样的道理，我们也可以得出第二条记录的行格式中，「变⻓字段⻓度列表」里的内容是「 04 02 」，如下图：
第三条记录中 phone 列的值是 NULL ，NULL 是不会存放在行格式中记录的真实数据部分里的，所
以「变⻓字段⻓度列表」里不需要保存值为 NULL 的变⻓字段的⻓度。
为什么 「变⻓字段⻓度列表」的信息要按照逆序存放？
这个设计 是有想法的，主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记
录的「记录头信息」和「真实数据」之间的位置，这样的好处 是向左读就是记录头信息，向右 读
就是真实数据，比较方便。
「变⻓字段⻓度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实
数据和数据对应的字段⻓度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache
的命中率。
同样的道理， NULL 值列表的信息也需要逆序存放。
如果你不知道什么 是 CPU Cache ，可以看这篇文章 ，这属于计算机组成的知识。
每个数据库表的行格式都有「变⻓字段字节数列表」吗？
其实变⻓字段字节数列表不是必须的。
当数据表没有变⻓字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变
⻓字段⻓度列表」了，因为没必要，不如去掉以节省空间。
所以「变⻓字段⻓度列表」只出现在数据表有变⻓字段的时候。
- NULL 值列表
表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空
间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL 值列表中。
如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit ），二进制位按照列的顺序逆序排
列。
二进制位的值为 1 时，代表该列的值为NULL 。
二进制位的值为 0 时，代表该列的值不为 NULL 。
另外，NULL 值列表必须用整数 个字节的位表示（1字节8位），如果使用的二进制位个数不足整数
个字节，则在字节的高位补 0 。
还是以 t_user 表的这三条记录作为例子：
接下来，我们看看看看 这三条记录的行格式中的 NULL 值列表是怎样存储的。
先来看第一条记录，第一条记录所有列都有值，不存在 NULL 值，所以用二进制来表示是酱紫的：
但是 InnoDB 是用整数 字节的二进制位来表示 NULL 值列表的，现在不足 8 位，所以要在高位补
0，最终用二进制来表示是酱紫的：
所以，对于第一条数据，NULL 值列表用十六进制表示是 0x00 。
接下来看第二条记录，第二条记录 age 列是 NULL 值，所以，对于第二条数据，NULL 值列表用十
六进制表示是 0x04 。
最后第三条记录，第三条记录 phone 列 和 age 列是 NULL 值，所以，对于第三条数据，NULL 值
列表用十六进制表示是 0x06 。
我们把三条记录的 NULL 值列表都填充完毕后，它们的行格式是这样的：
每个数据库表的行格式都有「NULL 值列表」吗？
NULL 值列表也不 是必须的。
当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。
所以在设计 数据库表的时候，通常都是建议将字段设置为 NOT NULL ，这样可以至少节省 1 字节
的空间（NULL 值列表至少占用 1 字节空间）。
「NULL 值列表」是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是
NULL ，这时候怎么表示？
「NULL 值列表」的空间不是固定 1 字节的。
当一条记录有 9 个字段值都是 NULL ，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。
- 记录头信息
记录头信息中包含的内容很多，我就不一一列举了 ，这里说几个比较重要的：
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 的作用了，如果你还不知道 MVCC
机制，可以看完这篇文章 ，一定要掌握 ，面试也很经常问 MVCC 是怎么实现的。
# varchar(n) 中 n 最大取值为多少？我们要清楚一点，MySQL 规定除了 TEXT 、BLOBs 这种大对象类型之外，其他所有的列（不包括
隐藏列和记录头信息）占用的字节⻓度加起来不能超过 65535 个字节。
也就是说，一行记录除了 TEXT 、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总⻓
度，不是一列。
知道了这个前提之后，我们再来看看 这个问题：「varchar(n) 中 n 最大取值为多少？」
varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并不是字节大小哦。
要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1个字
符要占用多少字节，比如 ascii 字符集， 1 个字符占用 1 字节，那么 varchar(100) 意味着最大能允许存储 100 字节的数据。
单字段的情况
前面我们知道了，一行记录最大只能存储 65535 字节的数据。
那假设数据库表只有一个 varchar(n) 类型的列且字符集是 ascii ，在这种情况下， varchar(n) 中 n最大取值是 65535 吗？
不着急说结论，我们先来做个实验验 证一下。
我们定义一个 varchar(65535) 类型的字段，字符集为 ascii 的数据库表。
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 值列表」所占用的字节数是多少？
前面我创建表的时候，字段是允许为 NULL 的，所以会用 1 字节来表示「NULL 值列表」。
本次案例中，「变⻓字段⻓度列表」所占用的字节数是多少？
「变⻓字段⻓度列表」所占用的字节数 = 所有「变⻓字段⻓度」占用的字节数之和。所以，我们要先知道每个变⻓字段的「变⻓字段⻓度」需要用多少字节表示？具 体情况分为：
条件一：如果变⻓字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变⻓字
段⻓度」；
条件二：如果变⻓字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变⻓字段⻓
度」；
我们这里字段类型是 varchar(65535) ，字符集是 ascii ，所以代 表着变⻓字段允许存储的最大字节数是 65535 ，符合条件二，所以会 用 2 字节来表示「变⻓字段⻓度」。
因为我们这个案例是只有 1 个变⻓字段，所以「变⻓字段⻓度列表」= 1 个「变⻓字段⻓度」占用的字节数，也就是 2 字节。
因为我们在算 varchar(n) 中 n 最大值时，需要减去 「变⻓字段⻓度列表」和 「NULL 值列表」所
占用的字节数的。所以，在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，
varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532 。
我们先来测试看看 varchar(65533) 是否可 行？
可以看到，还是不行，接下来看看 varchar(65532) 是否可 行？
可以看到，创建成功了。说明我们的推论是正确的，在算 varchar(n) 中 n 最大值时，需要减去「变⻓字段⻓度列表」和 「NULL 值列表」所占用的字节数的。
当然，我上面这个例子是针对字符集为 ascii 情况，如果采用的是 UTF-8 ，varchar(n) 最多能存储的数据计算方式就不一样了：
在 UTF-8 字符集下，一个字符最多需要三个 字节，varchar(n) 的 n 最大取值就是 65532/3 = 21844 。上面所说的只是针对于一个字段的计算方式。
多字段的情况
如果有 多个字段的话，要保证所有字段的⻓度 + 变⻓字段字节数列表所占用的字节数 + NULL 值列
表所占用的字节数 <= 65535 。这里举个 多字段的情况的例子（感谢@Emoji 同学提供的例子）
行溢出后，MySQL 是怎么处理的？
MySQL 中磁盘和内存交互 的基本单位是⻚，一个⻚的大小一般是 16KB ，也就是 16384 字节 ，而
一个 varchar(n) 类型的列最多可以存储 65532 字节 ，一些大对象如 TEXT 、BLOB 可能存储更多的数据，这时一个⻚可能就存不了 一条记录。这个时候就会发生行溢出，多的数据就会存到另外的
「溢出⻚」中。
如果一个数据⻚存不了 一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出⻚」中。在
一般情况下，InnoDB 的数据都是存放在 「数据⻚」中。但是当发生行溢出时，溢出的数据会存放
到「溢出⻚」中。
当发生行溢出时，在记录的真实数据处只会保 存该列的一部分数据，而把剩余的数据放在「溢出
⻚」中，然后真实数据处用 20 字节存储指向溢出⻚的地址 ，从而可以找到剩 余数据所在的⻚。大
致如下图所示。
上面这个是 Compact 行格式在发生行溢出后的处理。
Compressed 和 Dynamic 这两个 行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。
这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个
字节的指针来指向溢出⻚。而实际的数据都存储在溢出⻚中，看起来就像下面这样：
总结
MySQL 的 NULL 值是怎么存放的？
MySQL 的 Compact 行格式中会用「NULL 值列表」来标记值为 NULL 的列，NULL 值并不会存储在
行格式中的真实数据部分。
NULL 值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL ，行格式中就不会有 NULL
值列表，这样可节省 1 字节的空间。
MySQL 怎么知道 varchar(n) 实际占用数据的大小？MySQL 的 Compact 行格式中会用「变⻓字段⻓度列表」存储变⻓字段实际占用的数据大小。
varchar(n) 中 n 最大取值为多少？一行记录最大能存储 65535 字节的数据，但是这个是包含「变⻓字段字节数列表所占用的字节数」
和「NULL 值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。
如果一张表只有一个 varchar(n) 字段，且允许为 NULL ，字符集为 ascii 。varchar(n) 中 n 最大取值为 65532 。
计算公式：65535 - 变⻓字段字节数列表所占用的字节数 - NULL 值列表所占用的字节数 = 65535 -2 - 1 = 65532 。如果有 多个字段的话，要保证所有字段的⻓度 + 变⻓字段字节数列表所占用的字节数 + NULL 值列
表所占用的字节数 <= 65535 。行溢出后，MySQL 是怎么处理的？
如果一个数据⻚存不了 一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出⻚」中。
Compact 行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保 存该
列的一部分数据，而把剩余的数据放在「溢出⻚」中，然后真实数据处用 20 字节存储指向溢出⻚
的地址 ，从而可以找到剩 余数据所在的⻚。
Compressed 和 Dynamic 这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的
一部分数据，只存储 20 个字节的指针来指向溢出⻚。而实际的数据都存储在溢出⻚中。