字节方面，一行记录在MySQL中是如何存储的？有位读者在面对字节时被问到这个问题：如果你知道一行MySQL记录的存储结构，那么这道题对你来说并不难。不知道也没关系，这次我就和大家聊一聊MySQL中的一行记录是如何存储的？知道这些之后，除了解锁之前的面试题，你还将解锁这些面试题：MySQL的NULL值是否占用空间？MySQL如何知道varchar(n)实际占用的数据大小呢？varchar(n)中n的最大值是多少？MySQL是如何处理行溢出的？这些问题看似无关紧要，其实都是围绕着“一行MySQL记录的存储结构”这个知识点展开的。所以，突破了这个知识点之后，这些问题就迎刃而解了。好了，废话不多说，走吧！MySQL数据存储在哪个文件中？大家都知道MySQL的数据是存放在磁盘上的，那么存放在哪个文件中呢？MySQL存储的行为是由存储引擎实现的。MySQL支持多种存储引擎，不同的存储引擎保存的文件自然不同。InnoDB是我们常用的存储引擎，也是MySQL默认的存储引擎。因此，本文主要讨论InnoDB存储引擎。我们来看看MySQL数据库的文件存放在哪个目录下？mysql>显示像'datadir'这样的变量;+----------------+----------------+|变量名|值|+--------------+----------------+|数据目录|/var/lib/mysql/|+--------------+----------------+1rowinset(0.00sec)我们每次创建数据库（database）都会在/var/lib/mysql/目录下创建一个名为database的目录，然后保存表结构和表数据文件都会存放在这个目录下。比如我有一个名为my_test的数据库，在这个数据库中有一个名为t_order的数据库表。然后，我们进入/var/lib/mysql/my_test目录，看看里面都有哪些文件？[root@xiaolin~]#ls/var/lib/mysql/my_testdb.optt_order.frmt_order.ibd可以看到一共有三个文件，这三个文件分别代表：db.opt，用来存储当前数据库的默认字符集和字符验证规则。t_order.frm，这个文件中会保存t_order的表结构。在MySQL中创建表会生成一个.frm文件，用于保存每张表的元数据信息，主要包括表结构定义。t_order.ibd，t_order的表格数据会保存在这个文件中。表数据可以存储在共享表空间文件（文件名：ibdata1）或独占表空间文件（文件名：tablename.idb）中。此行为由参数innodb_file_per_table控制。如果将参数innodb_file_per_table设置为1，存储的数据、索引等信息将单独存储在一个独占的表空间中。从MySQL5.6.6开始，它的默认值为1.，所以在这个版本之后，MySQL中每张表的数据都存储在一个单独的.idb文件中。好了，现在我们知道了一个数据库表的数据是保存在“表名.idb”这个文件中的，这个文件也叫独占表空间文件。表空间文件的结构是什么？表空间由段、区、页和行组成。InnoDB存储引擎的逻辑存储结构大致如下图所示：下面我们从下到上一一来看。1、行（row）数据库表中的记录是按行（row）存储的，每行记录根据不同的行格式有不同的存储结构。后面会详细介绍InnoDB存储引擎的行格式，这也是本文的重点。2、页（page）记录是按行存储的，但数据库的读取不是按“行”来的，否则一次读取（即一次I/O操作）只能处理一行数据，而效率会很低。因此，InnoDB的数据读写是以“页”为单位的。也就是说，当需要读取一条记录时，并不是从磁盘中读取该行记录，而是以页为单位对整条行记录进行读写。读入内存。每页默认大小为16KB，即最多可以保证16KB的连续存储空间。页是InnoDB存储引擎磁盘管理的最小单位，也就是说数据库的每次读写都是以16KB为单位。一次至少有16K的内容从磁盘读入内存，一次至少有16K的内容刷新到内存。磁盘。页的种类很多，常见的有数据页、undolog页、溢出页等。数据表中的行记录由“数据页”管理。数据页的结构这里就不赘述了。我在之前的文章中已经提到了。有兴趣的可以看这篇文章：换个角度看B+简而言之，树知道表中的记录是存放在“数据页”中的。3.Extent（范围）我们知道InnoDB存储引擎使用B+树来组织数据。B+树中的每一层都由双向链表连接。如果以页为单位分配存储空间，链表中相邻的两个页之间的物理位置是不连续的，可能相距很远，那么磁盘查询的时候就会有很多随机I/O，而且随机I/O很慢。解决这个问题也很简单，就是让链表中相邻页的物理位置相邻，这样就可以使用顺序I/O，在范围查询（扫描叶子）的时候性能会很高节点）。如何解决？当表的数据量很大时，在为索引分配空间时，不再以page为单位分配，而是以extents为单位分配。每个区域的大小为1MB。对于16KB的页面，会把64个连续的页面分成一个区域，这样链表中相邻页面的物理位置也相邻，可以使用顺序I/O。向上。4.段表空间由各种段组成，段又由多个extent组成。段一般分为数据段、索引段和回滚段。索引段：存放B+树非叶子节点的区域集合；数据段：存放B+树叶子节点的区域集合；rollbacksegment：存储回??滚数据的区域的集合，之前我们讲事务隔离，在介绍MVCC的时候，就是使用rollbacksegment来实现多版本查询数据。好了，表空间的结构终于完成了。下面详细说一下InnoDB的行格式。之所以说行记录的格式之前要绕个大圈，主要是为了让大家知道行记录存放在哪个文件中，以及行记录存放在表空间文件中的哪个区域。有一个从上到下的透视，这样理解的时候不会觉得很抽象。InnoDB的行格式是什么？行格式（row_format）是一条记录的存储结构。InnoDB提供了4种行格式，分别是Redundant、Compact、Dynamic和Compressed行格式。冗余是一种非常古老的行格式。MySQL5.0之前使用的行格式，现在基本不用了。由于Redundant不是紧凑行格式，因此MySQL5.0之后引入了紧凑行记录存储方式。Compact是一种紧凑的行格式。设计的初衷是为了让一个数据页可以存储更多的行记录，从MySQL5.1以后，行格式默认设置为Compact。Dynamic和Compressed都是compactlineformats，它们的lineformats和Compact类似，都是在Compact的基础上做了一点改进。MySQL5.7以后，默认使用Dynamicrowformat。多余的行格式这里就不说了，因为现在几乎没人用了。这次重点说一下compactlineformat，因为Dynamic和Compressed这两个lineformat和Compact非常相似。所以，理解了Compactrowformat之后，再去理解其他的rowformat，很快就可以理解了。COMPACT行格式是什么样的？先熟悉一下Compact行格式，它是这样的：可以看到一条完整的记录分为两部分：“记录的附加信息”和“记录的真实数据”。接下来，让我们详细谈谈每一个。记录的额外信息记录的额外信息包含3部分：变长字段长度列表、NULL值列表、记录头信息。1.变长字段长度列表varchar(n)和char(n)有什么区别，相信大家都很清楚，char是定长的，varchar是变长的，实际存??储数据的长度在可变长度字段（大小）中不固定。因此，在存储数据时，这些数据占用的字节数也应该存储在“可变字段长度列表”中。读取数据时，可以根据这个“可变字段长度列表”读取对应的数据。数据的长度。其他可变长度字段（如TEXT和BLOB）也是如此。为了展示“变长字段长度表”具体是如何保存变长字段占用的字节数的，我们先创建这样一张表，字符集为ascii（所以每个字符占1个字节），而行格式为Compact，t_user表中的name和phone字段为可变长度字段：CREATETABLE`t_user`(`id`int(11)NOTNULL,`name`VARCHAR(20)NOTNULL,`phone`VARCHAR(20)DEFAULTNULL,`age`int(11)DEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARACTERSET=asciiROW_FORMAT=COMPACT;现在t_user表中有这3条记录：接下来我们来看看这3条记录的行格式中的“可变字段长度列表”是如何存储的。先看第一条记录：name列的值为a，长度为1字节，16进制的0x01phone列的值为123，长度为3字节，16进制的0x03age列和id列不可变length字段，所以这里不用担心。这些变长字段的长度值会按照列的顺序倒序存储（后面会解释为什么要这样设计），所以“变长字段长度列表”中的内容是“0301”而不是“0103”。同理，我们也可以得出，在第二条记录的行格式中，“可变字段长度列表”中的内容为“0402”，如下图：第三条记录为NULL，NULL不会存储在行格式的记录的真实数据部分，因此值为NULL的变长字段的长度不需要存储在“变长字段”中长度列表”。为什么“可变字段长度列表”中的信息要倒序存储？这种设计是有思想的，主要是因为“记录头信息”中指向下一条记录的指针指向了下一条记录的“记录头信息”和“真实数据”之间的位置。向左读是记录表头信息，向右读是真正的数据，比较方便。之所以要把“变长字段长度列表”中的信息倒序存放，是因为前面记录的真实数据和该数据对应的字段长度信息可以存放在一个CPUCacheLine中同时，可以提高CPU的性能。缓存命中率。同样的道理，NULL值列表的信息也需要倒序存储。如果你不知道什么是CPUCache，可以看这篇文章：面试官：如何编写让CPU跑得更快的代码？，属于计算机组成的知识。每个数据库表的行格式是否有“变长字段字节计数列表”？事实上，变长字段字节列表并不是必需的。当数据表没有变长字段时，比如所有字段都是int类型，那么表中的行格式不会有“变长字段长度列表”，因为没必要，最好是删除它以节省空间。因此，“变长字段长度列表”只有在数据表有变长字段时才会出现。2.NULL值列表表中的某些列可能存储NULL值。如果将这些NULL值放在记录的真实数据中，会造成空间的浪费，所以Compact行格式将这些NULL列存储为NULL值。列表。如果有允许NULL值的列，则每一列对应一个二进制位（bit），二进制位按照列的顺序倒序排列。当二进制位的值为1时，表示该列的值为NULL。当二进制位的值为0时，表示该列的值不为NULL。此外，NULL值列表必须由整数字节数表示（1字节为8位）。如果使用的二进制位数小于整数个字节，则在字节的高位添加0。还是以t_user表的这3条记录为例：接下来我们看看这3条记录的行格式的NULL值列表是如何存储的。我们先来看第一条记录。第一条记录的所有列都有值，没有NULL值，所以用二进制表示很容易混淆：但是InnoDB使用整数字节的二进制位来表示NULL值列表。现在还不到8位，所以要在高位补0，最终二进制表示为红色：所以，对于第一条数据，NULL值列表十六进制为0x00。接下来，看第二条记录。第二条记录的年龄列是NULL值。因此，对于第二个数据，NULL值列表以十六进制表示为0x04。最后的第三条记录，第三条记录的phone列和age列都是NULL值，所以，对于第三条数据，NULL值列表十六进制为0x06。三个记录的NULL值列表我们填好之后，它们的行格式是这样的：每个数据库表的行格式是否都有一个“NULL值列表”？也不需要NULL值列表。当数据表的字段都定义为NOTNULL时，表中的行格式将不会有NULL值列表。因此，在设计数据库表时，通常建议将该字段设置为NOTNULL，这样可以节省1个字节的空间（NULL值的列表占用1个字节的空间）。3、记录头信息记录头信息中的内容很多，就不一一列举了。这里有一些重要的：delete_mask：表示是否删除这条数据。由此我们可以知道，当我们执行delete删除一条记录时，并没有真正删除这条记录，只是将这条记录的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的最大值是多少？varchar(n)字段类型的n表示最大存储字符数，那么n最大可以设置多少个数呢？这个问题需要考虑两个因素：“可变长度字段长度列表”在行格式中可以表示多少字节？只有知道了这一点，我们才能知道一行数据可以存储多少字节的数据。数据库表的字符集，确定了这个之后，我们就可以知道一个字符占多少字节了。行格式中的“可变字段长度列表”有时占1字节，有时占2字节：如果字段允许存储的最大字节数小于等于255字节，“可变字段长度列表”"只占用1个字节；如果一个字段允许存储的最大字节数大于255字节，则“变长字段长度列表”占用2个字节；可以看到，“变长字段长度列表”占用的字节数最多不会超过2个字节。2个字节的最大值为65535（十进制）。由此可以推测，一行记录最多可以存储65535字节的数据。真的是这样吗？我这里以ascii字符集为例，也就是说1个字符占1个字节。那么varchar(65535)表示最多可以存储65535个ascii字符，刚好可以存储一行记录中最多65535字节的数据。我们定义了一个varchar(65535)类型的字段和一个字符集为ascii的数据库表。CREATETABLEtest(`name`VARCHAR(65535)NULL)ENGINE=InnoDBDEFAULTCHARACTERSET=asciiROW_FORMAT=COMPACT;查看是否可以成功创建表：可以看到，创建失败。从报错信息中我们可以知道，一行数据的最大字节数是65535（不包括TEXT、BLOB等大对象类型），其中包含了存储开销。问题是，这个存储开销是多少？其实就是一个变长字段长度列表和一个NULL值列表，也就是说一行数据的最大字节数是65535，实际上包括了“变长”占用的字节数字段长度列表”和“NULL值列表”。当我们存储字段类型为varchar(n)的数据时，实际上是分成三部分存储：真实数据占用的字节数和NULL标志。如果不允许为NULL，这部分在我建表的时候就不需要用了，该字段允许为NULL，所以会占用1个字节来存储NULL标志，该字段为变长字段且变长字段允许存储的最大字节数大于255bytes,soitwilltake2bytestostorerealdata占用的字节数，所以最多可以存储65535-2-1=65532字节。我们先测试下varchar(65533)是否可行？可以看出，还是不行，我们看看varchar(65532)可行不可行？可以看到，创建成功。当然，我上面的例子是针对字符集是ascii的情况。如果使用UTF-8，varchar(n)可以存储的最大数据的计算方法不同：在UTF-8字符集中，一个字符串最多可以要求三个字节，n的最大值在varchar(n)就是65532/3=21844，上面说的只是针对某个字段的计算方式。如果有多个字段，保证所有字段的长度+变长字段字节列表占用的字节数+NULL值列表占用的字节数<=65535。MySQL如何处理行溢出？MySQL中磁盘和内存交互的基本单位是页面。一个页面的大小一般是16KB，也就是16384字节，一个varchar(n)类型的列最多可以存储65532字节。一些大对象如TEXT，ABLOB可能存放的数据比较多，此时一个页面可能无法存放单条记录。这时候就会发生行溢出，更多的数据会存储到另一个“溢出页”中。如果一个数据页不能存储一条记录，InnoDB存储引擎会自动将溢出的数据存储在“溢出页”中。通常，InnoDB数据存储在“数据页”中。但是当发生行溢出时，溢出的数据会存储在“溢出页”中。当发生行溢出时，只有该列的一部分数据会被保存在记录的真实数据中，其余数据会被放在“溢出页”中，然后真实数据会使用20个字节来存储指向溢出页的地址，以便找到剩余数据所在的页。大致如下图所示。以上就是Compactrowformat发生rowoverflow后的处理。Compressed和Dynamic这两种行格式与Compact非常相似，主要区别在于在处理行溢出数据方面存在一些差异。这两种格式采用完全行溢出的方法。列的部分数据不会存储在记录的真实数据中，只存储一个20字节的指针指向溢出页。实际数据存储在溢出页中，看起来像这样：摘要NULL值如何存储在MySQL中？MySQL的Compactrowformat会使用“NULLvaluelist”来标记具有NULL值的列，NULL值不会存储在rowformat的真实数据部分。NULL值列表将占用1个字节的空间。当表中的所有字段都定义为NOTNULL时，行格式中将没有NULL值列表，可以节省1个字节的空间。MySQL如何知道varchar(n)实际占用的数据大小呢？在MySQL的Compact行格式中，“变长字段长度列表”用于存储变长字段实际占用的数据大小。varchar(n)中n的最大值是多少？一行记录最多可以存储65535字节的数据，但这包括“变长字段字节列表占用的字节数”和“NULL值列表占用的字节数”。如果一张表只有一个varchar(n)字段，并且允许NULL，则字符集为ascii。varchar(n)中n的最大值为65532。计算公式：65535-变长字段字节列表占用的字节数-NULL值列表占用的字节数=65535-2-1=65532行溢出，MySQL如何处理？如果一个数据页不能存储一条记录，InnoDB存储引擎会自动将溢出的数据存储在“溢出页”中。紧凑行格式对行溢出的处理是这样的：当发生行溢出时，只会将列中的一部分数据保存在记录的真实数据处，其余数据将放在“溢出页”中，然后真正的数据将存储在“溢出页面”中。用20个字节存放指向溢出页的地址，这样就可以找到剩余数据所在的页。Compressed和Dynamic这两种格式使用了完整的行溢出方法。列中的部分数据不会存储在记录的真实数据中，只会存储一个20字节的指针指向溢出页。实际数据存储在溢出页中。参考资料：《MySQL 是怎样运行的》《MySQL技术内幕 InnoDB存储引擎》
