之前有位读者在面字节的时候被问到这么个问题如果你知道 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 目录看看里面有什么文件[rootxiaolin ~]#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.ibdt_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 树(opens new window)总之知道表中的记录存储在「数据页」里面就行。#3、区extent我们知道 InnoDB 存储引擎是用 B 树来组织数据的。B 树中每一层都是通过双向链表连接起来的如果是以页为单位来分配存储空间那么链表中相邻的两个页之间的物理位置并不是连续的可能离得非常远那么磁盘查询时就会有大量的随机I/O随机 I/O 是非常慢的。解决这个问题也很简单就是让链表中相邻的页的物理位置也相邻这样就可以使用顺序 I/O 了那么在范围查询扫描叶子节点的时候性能就会很高。那具体怎么解决呢在表中数据量大的时候为某个索引分配空间的时候就不再按照页为单位分配了而是按照区extent为单位分配。每个区的大小为 1MB对于 16KB 的页来说连续的 64 个页会被划为一个区这样就使得链表中相邻的页的物理位置也相邻就能使用顺序 I/O 了。#4、段segment表空间是由各个段segment组成的段是由多个区extent组成的。段一般分为数据段、索引段和回滚段等。索引段存放 B 树的非叶子节点的区的集合数据段存放 B 树的叶子节点的区的集合回滚段存放的是回滚数据的区的集合之前讲事务隔离 (opens new window)的时候就介绍到了 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 字节行格式是 Compactt_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 字节十六进制 0x01phone 列的值为 123真实数据占用的字节数是 3 字节十六进制 0x03age 列和 id 列不是变长字段所以这里不用管。这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放等下会说为什么要这么设计所以「变长字段长度列表」里的内容是「 03 01」而不是 「01 03」。同样的道理我们也可以得出第二条记录的行格式中「变长字段长度列表」里的内容是「 04 02」如下图第三条记录中 phone 列的值是 NULLNULL 是不会存放在行格式中记录的真实数据部分里的所以「变长字段长度列表」里不需要保存值为 NULL 的变长字段的长度。为什么「变长字段长度列表」的信息要按照逆序存放这个设计是有想法的主要是因为「记录头信息」中指向下一个记录的指针指向的是下一条记录的「记录头信息」和「真实数据」之间的位置这样的好处是向左读就是记录头信息向右读就是真实数据比较方便。「变长字段长度列表」中的信息之所以要逆序存放是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中这样就可以提高 CPU Cache 的命中率。同样的道理 NULL 值列表的信息也需要逆序存放。如果你不知道什么是 CPU Cache可以看这篇文章 (opens new window)这属于计算机组成的知识。每个数据库表的行格式都有「变长字段字节数列表」吗其实变长字段字节数列表不是必须的。当数据表没有变长字段的时候比如全部都是 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 机制可以看完这篇文章 (opens new window)一定要掌握面试也很经常问 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-8varchar(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存储引擎》
MySQL 一行记录是怎么存储的？
之前有位读者在面字节的时候被问到这么个问题如果你知道 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 目录看看里面有什么文件[rootxiaolin ~]#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.ibdt_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 树(opens new window)总之知道表中的记录存储在「数据页」里面就行。#3、区extent我们知道 InnoDB 存储引擎是用 B 树来组织数据的。B 树中每一层都是通过双向链表连接起来的如果是以页为单位来分配存储空间那么链表中相邻的两个页之间的物理位置并不是连续的可能离得非常远那么磁盘查询时就会有大量的随机I/O随机 I/O 是非常慢的。解决这个问题也很简单就是让链表中相邻的页的物理位置也相邻这样就可以使用顺序 I/O 了那么在范围查询扫描叶子节点的时候性能就会很高。那具体怎么解决呢在表中数据量大的时候为某个索引分配空间的时候就不再按照页为单位分配了而是按照区extent为单位分配。每个区的大小为 1MB对于 16KB 的页来说连续的 64 个页会被划为一个区这样就使得链表中相邻的页的物理位置也相邻就能使用顺序 I/O 了。#4、段segment表空间是由各个段segment组成的段是由多个区extent组成的。段一般分为数据段、索引段和回滚段等。索引段存放 B 树的非叶子节点的区的集合数据段存放 B 树的叶子节点的区的集合回滚段存放的是回滚数据的区的集合之前讲事务隔离 (opens new window)的时候就介绍到了 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 字节行格式是 Compactt_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 字节十六进制 0x01phone 列的值为 123真实数据占用的字节数是 3 字节十六进制 0x03age 列和 id 列不是变长字段所以这里不用管。这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放等下会说为什么要这么设计所以「变长字段长度列表」里的内容是「 03 01」而不是 「01 03」。同样的道理我们也可以得出第二条记录的行格式中「变长字段长度列表」里的内容是「 04 02」如下图第三条记录中 phone 列的值是 NULLNULL 是不会存放在行格式中记录的真实数据部分里的所以「变长字段长度列表」里不需要保存值为 NULL 的变长字段的长度。为什么「变长字段长度列表」的信息要按照逆序存放这个设计是有想法的主要是因为「记录头信息」中指向下一个记录的指针指向的是下一条记录的「记录头信息」和「真实数据」之间的位置这样的好处是向左读就是记录头信息向右读就是真实数据比较方便。「变长字段长度列表」中的信息之所以要逆序存放是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中这样就可以提高 CPU Cache 的命中率。同样的道理 NULL 值列表的信息也需要逆序存放。如果你不知道什么是 CPU Cache可以看这篇文章 (opens new window)这属于计算机组成的知识。每个数据库表的行格式都有「变长字段字节数列表」吗其实变长字段字节数列表不是必须的。当数据表没有变长字段的时候比如全部都是 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 机制可以看完这篇文章 (opens new window)一定要掌握面试也很经常问 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-8varchar(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存储引擎》
