InnoDB 的记录，页与索引

记录

InnoDB 存储引擎是基于磁盘存储的，MySQL 会一段开辟内存空间，称为缓存区，MySQL 对记录的管理是在缓冲区中进行的。InnoDB 会将记录划分为若干个页，页是缓冲区与磁盘交互基本单位，InnoDB 中页的大小为 16384B，即 16KB，通过 innodb_page_size 可以进行配置

虽然在 InnoDB 中，磁盘和缓冲区通过页进行交互，但是对于用户来说，我们输入的是一行行的记录。这些记录以行记录格式（Row Format）的形式一行一行的存储在磁盘当中。InnoDB 支持4种行记录格式，分别为 Compact，Redundant，Dynamic 和 Compressed。MySQL 5.7 开始默认的行记录格式为 Dynamic

为了方便演示，我们创建一个 compact_test 表，它的行记录格式为 Compact，字符集为 utf8

mysql> create table compact_test ( 
c1 int primary key, 
c2 VARCHAR(10), 
c3 VARCHAR(10) NOT NULL, 
c4 CHAR(10), c5 int 
) CHARSET=utf8 ROW_FORMAT=COMPACT;

变长字段长度列表

所谓的变长字段分为以下两种

1. 变长数据类型，MySQL 支持的变长数据类型有 VARCHAR(M)，VARBINARY(M)，各种 TEXT 类型和各种 BLOB 类型
2. 使用变长字符集存储的 CHAR(M)，如 utf8，utf8mb4 等

因为变长字段存储的数据的字节数是不固定的，所以我们才需要有变长字段长度列表将数据长度记录下来，这样存储引擎才能正确的读取列值

VARCHAR(M), M 代表最大能存多少个字符。MySQ L 5.0.3 以前是字节，以后就是字符

首先我们看 compact_test 表，变长字段有 c2，c3 和 c4，我们插入一条数据

c1	c2	c3	c4	c5
1	aaaa	bbb	cc	5

那么行记录格式为

我们注意到变长字段长度列表是逆序存储的，这主要是方便存储引擎使用

变长字段长度默认采用1个字节存储，说明存储的字符串最多255字节长度。如果超过255字节长度，那么变长字段长度会采用2个字节存储，存储的字符串最多65535字节长度。如果还超过就会造成溢出

NULL 标志位

首先说明下为什么需要 NULL 标志位，我们创建数据表的时候是允许某些字段可以为 NULL 值，当我们写入的数据中某包含 NULL，如果我们还专门用列数据去存储，那么是非常浪费存储空间的，这也就是 NULL 标志位存在的意义

我们插入一行数据

c1	c2	c3	c4	c5
2	NULL	bbb	NULL	5

首先从数据表我们知道，允许 NULL 值存储的字段有 c2，c4 和 c5，且 c2 和 c4 的值为 NULL，c5 的值为5，那么行记录格式为

我们现在应该会对 0000 0011 这个非常迷惑。首先要确认一些概念，看完在结合上图你就明白了

1. NULL 标志位的长度为字节的整数倍，也就是0位，8位，16位等等
2. 和变长字段长度列表一样都是逆序存储的
3. 如果列的值为 NULL 则对应的位置位1，没有列数据，否则置为0，有列数据

记住变长字段长度列表和 NULL 标志位都是可选的，如果没有数据表中没有变长字段和允许 NULL 的值字段的话，那么这两个都是占用0字节长度

记录头信息

记录头信息默认由5字节组成，也就是40个二进制位

名称	大小（单位 bit）	描述
预留位1	1
预留位2	1
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+ 树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在记录堆的位置信息
record_type	3	表示当前记录的类型，0表示普通记录，1表示 B+ 树非叶子节点记录，2表示最小记录，3表示最大记录
next_record	16	表示下一条记录的相对位置

这里有个大概的印象就好，以后再说详细说明

数据列

对于 compact_test 表来说数据列不止有 c1，c2，c3，c4 和 c5 这几个列，还会存在3个隐藏列。如下图所示

列名	是否必须	占用空间	描述
DB_ROW_ID	否	6字节	行ID，唯一标识一条记录
DB_TRX_ID	是	6字节	事务 ID
DB_ROLL_PTR	是	7字节	回滚指针

InnoDB 存储引擎是默认规定数据表是需要主键的，如果创建表的时候没有主键，则会去找唯一索引，否则默认会给一个主键，也就是这个 DB_ROW_ID

行溢出数据

我们知道变长字段最多能存储65535个字节，我们首先创建一个表

mysql> create table varchar_size_demo(
c varchar(65535)
) CHARSET=ascii ROW_FORMAT=COMPACT;

然而我们得到的却是一个错误提示

(1118, 'Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs')

这主要的原因是我们存储变长数据的时候，有三个部分要占用存储空间

1. 真实数据
2. 变长字段长度
3. NULL 标志位

• 如果变长字段没有设置 NOT NULL，那么 VARCHAR(M) 最多只能存储 65532 个字节数据
• 如果变长字段有设置 NOT NULL，那么 VARCHAR(M) 最多只能存储 65533 个字节数据

我们创建一个表，同时插入一条记录

mysql> create table varchar_size_demo(
c varchar(65532)
) CHARSET=ascii ROW_FORMAT=COMPACT;

mysql> insert into varchar_size_demo(c) values(repeat('a', 65532));

我们知道记录是存储在页当中的，而页的默认大小为 16KB，也就是16384个字节，那么现在插入一条65532字节大小的记录，一页明显就存不下

对于 Compact 和 Reduntant 行记录格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址（20个字节），然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页

请记住768字节这个数，这里我们做下扩展。数据溢出的情况会存前768个字节，其实 InnoDB 是能对这个768个字节做索引的，MySQL 默认的字符集为 utf8，也就意味着能对前256个 utf8 字符做索引。但是在变长字段长度占1字节的情况下，最多只能存255个字节。基于这两者的考量，所以 InnoDB 规定，当使用 Compact 行记录存储且字符集为 utf8，最多只能对前767（767/3=255.67，向下取整为255）个字节做索引，超出则报错

Compress 和 Redundant 对于溢出页的实现是一样的，最长索引前缀为767字节，Dynamic 和 Compressed 的最长索引前缀长度后面讲

Dynamic 和 Compress 的区别

我们知道 MySQL 5.7 默认的行格式为 Dynamic，但是其实它和 Compress 基本没什么本质上的区别，唯一的区别就是对于行溢出的处理不同。当发生行溢出时，不存储真实数据的前768个字节，只存储一个指向溢出页的地址

这样做的好处在于可以腾挪出更多页空间来存放其他列数据

页

我们知道页是缓冲区与磁盘交互基本单位，默认大小为 16KB。根据使用场景的不同会有不同的名字，比如存放记录的页叫做数据页。首先我们先看下数据页的整体结构

名称	中文名	占用空间大小（字节）	简单描述
File Header	文件头部	38	页的一些通用信息
Page Header	页头部	56	数据页专有的一些信息
Infimum + Supremum	最小记录和最大记录	26	两个虚拟行记录
User Records	用户记录	不确定	实际存储的行记录内容
Free Space	空闲空间	不确定	页中尚未使用的空间
Page Directory	页目录	不确定	页中的某些记录的相对位置
File Trailer	文件尾部	8	校验页是否完整

首先讲下 File Header，它的结构如下

名称	占用空间大小（字节）	描述
FIL_PAGE_SPACE_OR_CHKSUM	4	页的校验和（checksum值）
FIL_PAGE_OFFSET	4	页号
FIL_PAGE_PREV	4	上一页的页号
FIL_PAGE_NEXT	4	下一页的页号
FIL_PAGE_LSN	8	页面被最后修改时对应的日志序列位置（Log Sequence Number）
FIL_PAGE_TYPE	2	该页的类型
FIL_PAGE_FILE_FLUSH_LSN	8	仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的 LSN 值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4	页属于哪个表空间

我们看到有 FIL_PAGE_PREV 和 FIL_PAGE_NEXT，这也说明了页与页的存储是一个双向链表结构

然后我们再来讲 User Records 和 Page Directory，它们是重中之重。行记录以链表的形式存放在 User Records 中，还记得行记录格式中的记录头信息吗，记录头信息中的 next_record 存放着下一条记录的地址

那么 Page Directory，即页目录有什么用呢？我们举一个例子，有如下表

mysql> create table page_directory_test (
id int primary key
);

假设现在我们往 page_directory_test 表，插入了5000条数据。现在我们要找到 id 为3999的数据，由于 User Records 为链表存储，那么我们就只能从1开始依次遍历，直到找到3999。这样是非常耗时的。这也是 Page Directory 存在的意义

Page Directory，页目录，可以说就是页的索引，当我们查找页中的数据时，只需要先通过 Page Directory（索引）来查找，再到 User Records 查找，就能大大提高查询效率。由于 InnoDB 存储行记录的时候是按主键的顺序来存储的，所以我们还可以通过二分查找法来优化页目录查询效率

我们知道每页最多 16KB 大小，如果存储的数据超过 16KB 就会分裂成两个页（页分裂），所以 InnoDB 存储引擎是管理着非常多的页的。那么假设我们要找某一行数据，InnoDB 是怎么知道要到哪一页去找数据呢？答案是 InnoDB 通过页来管理着每一个页目录，这种页称目录页。其实本质上来讲目录页其实还是数据页

目录页的 User Records 存放着每一个页目录中最小的主键值和页目录对应的页号，同样也有 Page Directroy。我们现在稍微整理下图

这不就很像是一颗 B+ 树吗？现在知道 InnoDB 的索引结构是 B+ 树了吧

记住目录页也是页，同样有 16KB 大小的限制，所以当目录页大小超过 16KB 的时候就会出现页分裂，然后 InnoDB 会创建新的目录页来管理目录页中的页目录

索引

通过前面的 B+ 树 我们知道 InnoDB 存储引擎将所有的行记录和索引（以主键为索引）都存储在若干个页中，然后由页串连成一棵 B+ 树，这也就是我们常说的主键索引。InnoDB 存储引擎还能针对其他列来创建索引，这种索引叫做辅助索引

MySQL 可以建立单列索引和多列索引。单列索引和多列索引和主键索引的结构差不多，只是它们叶子节点的行记录不是存储实际的数据，而是存储着主键的值。要想拿到实际的数据需要再通过主键索引找到对应的行记录然后才能拿到实际的数据，这个过程称为回表

我们创建一个表并创建多列索引

mysql> create table test_multi_index(
c1 int primary key,
c2 int,
c3 int
c4 int
c5 int
) CHARSET=utf8;

mysql> alter table test_multi_index add index(c2, c3, c4);

为了演示方便，我们全部字段都用 int 类型，现在我们插入一条数据

c1	c2	c3	c4	c5
1	12	34	56	78

那么在页中它是这么存储的

• Page Directory 存储着由 c2，c3 和 c4 列的组成的索引
• User Records 存储着对应的主键值

mysql> select * from test_multi_index where c2 = 2 and c3 = 3 and c4 = 4;

多列索引在比较大小时，是依次进行比较的，比如这里就是先比较 c2 的大小，再比较 c3 的大小，最后比较 c4 的大小。这就是多列索引的最左匹配原则

mysql> select * from test_multi_index where c3 = 3 and c4 = 4;

这条查询语句就没办法使用到我们创建的多列索引了，因为无法对 c2 进行大小比较，也就没办法对后面的 c3 和 c4 进行大小比较

字符的大小比较要看设置了哪种比较集