大家好，我是小林。
之前有位读者在面字节的时分，被问到这么个问题：
假如你知道 MySQL 一行记载的存储结构，那么这个问题对你没什么难度。
假如你不知道也不要紧，这次我跟大家聊聊 MySQL 一行记载是怎样存储的？
知道了这个之后，除了能应解锁前面这道面试题，你还会解锁这些面试题：
- MySQL 的 NULL 值会占用空间吗？
- MySQL 怎样知道 varchar(n) 实践占用数据的巨细？
- varchar(n) 中 n 最大取值为多少？
- 行溢出后，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_test
db.opt
t_order.frm
t_order.ibd
能够看到，共有三个文件，这三个文件别离代表着：
- db.opt，用来存储当前数据库的默许字符集和字符校验规矩。
- t_order.frm ，t_order 的表结构会保存在这个文件。在 MySQL 中树立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，首要包括表结构界说。
- t_order.ibd，t_order 的表数据会保存在这个文件。表数据既能够存在同享表空间文件（文件名：ibdata1）里，也能够寄存在独占表空间文件（文件名：表名字.idb）。这个行为是由参数 innodb_file_per_table 操控的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版别开始，它的默许值便是 1 了，因此从这个版别之后， MySQL 中每一张表的数据都寄存在一个独立的 .idb 文件。
好了，现在咱们知道了一张数据库表的数据是保存在「 表名字.idb 」的文件里的，这个文件也称为独占表空间文件。
表空间文件的结构是怎样样的？
表空间由段（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 值列表、记载头信息。
1. 变长字段长度列表
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) NOT 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 类型的字段，这时分表里的行格局就不会有「变长字段长度列表」了，由于没必要，不如去掉以节约空间。
所以「变长字段长度列表」只出现在数据表有变长字段的时分。
2. 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 值列表」，以此类推。
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 的作用了，假如你还不知道 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 个字节的指针来指向溢出页。而实践的数据都存储在溢出页中。
参考资料：
- 《MySQL 是怎样运行的》
- 《MySQL技术内幕 InnoDB存储引擎》
