摘要
Mysql Version：5.7.36
# 一：概览
# 1.1 搜索引擎比较
| 存储引擎 | 描述 |
|---|---|
| ARCHIVE | 用于数据存档(行被插入后不能再修改) |
| BLACKHOLE | 丢弃写操作，读操作会返回空内容 |
| CSV | 在存储数据时，以逗号分隔各个数据项 |
| FEDERATED | 用来访问远程表 |
| InnoDB | 具备外键支持功能的事务存储引擎 |
| MEMORY | 置于内存的表 |
| MERGE | 用来管理多个MyISAM表构成的表集合 |
| MyISAM | 主要的非事务处理存储引擎 |
| NDB | MySQL集群专用存储引擎 |
# 1.2 支持情况
| Feature | MyISAM | Memory | InnoDB | Archive | NDB |
|---|---|---|---|---|---|
| B-tree indexes | yes | yes | yes | no | no |
| Backup/point-in-time recovery | yes | yes | yes | yes | yes |
| Cluster database support | no | no | no | no | yes |
| Clustered indexes | no | no | yes | no | no |
| Compressed data | yes | no | yes | yes | no |
| Data caches | no | N/A | yes | no | yes |
| Encrypted data | yes | yes | yes | yes | yes |
| Foreign key support | no | no | yes | no | yes |
| Full-text search indexes | yes | no | yes | no | no |
| Geospatial data type support | yes | no | yes | yes | yes |
| Geospatial indexing support | yes | no | yes | no | no |
| Hash indexes | no | yes | no | no | yes |
| Index caches | yes | N/A | yes | no | yes |
| Locking granularity | Table | Table | Row | Row | Row |
| MVCC | no | no | yes | no | no |
| Query cache support | yes | yes | yes | yes | yes |
| Replication support | yes | Limited | yes | yes | yes |
| Storage limits | 256TB | RAM | 64TB | None | 384EB |
| T-tree indexes | no | no | no | no | yes |
| Transactions | no | no | yes | no | yes |
| Update statistics for data dictionary | yes | yes | yes | yes | yes |
# 1.3 存储引擎查询
查看当前服务器程序支持的存储引擎
SHOW ENGINES;
- Engine：代表搜索引擎名；
- Support：代表该存储引擎是否可用；
- DEFAULT：代表是当前服务器程序的默认存储引擎；
- Comment：代表对存储引擎的一个描述；
- Transactions：代表该存储引擎是否支持事务处理；
- XA：代表着该存储引擎是否支持分布式事务；
- Savepoints：代表着该存储引擎是否支持部分事务回滚。
# 1.4 设置表的存储引擎
创建表时指定：
CREATE TABLE engine_demo (
i int
) ENGINE = MyISAM
2
3
修改表的存储引擎
ALTER TABLE engine_demo ENGINE = InnoDB
# 二：InnoDB 记录存储结构
InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。当我们想从表中获取某些记录时，InnoDB 采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
# 2.1 行格式
平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。目前设计了4种不同类型的行格式，分别是 Compact、Redundant、Dynamic和Compressed行格式。
指定行格式的语法
可以在创建或修改表的语句中指定行格式：
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
ALTER TABLE 表名 ROW_FORMAT=行格式名称;
2
3
例如：
CREATE TABLE record_format_demo (
c1 VARCHAR(10),
c2 VARCHAR(10) NOT NULL,
c3 CHAR(10),
c4 VARCHAR(10)
) CHARSET=ascii ROW_FORMAT=COMPACT;
2
3
4
5
6
插入一条记录
INSERT INTO record_format_demo(c1, c2, c3, c4)
VALUES('aaaa', 'bbb', 'cc', 'd'),
('eeee', 'fff', NULL, NULL);
2
3
# 2.2 Compact行格式
一条完整的记录可以被分为 记录的额外信息 和 记录的真实数据 两大部分。
记录的额外信息
这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是变长字段长度列表、NULL值列表和记录头信息。
变长字段长度列表
变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来，所以这些变长字段占用的存储空间分为两部分：真正的数据内容 和 占用的字节数。
在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放
使用上面insert的第一条语句作为示例：(ascii每个字符占1字节)
| 列名 | 存储内容 | 内容长度(十进制表示) | 内容长度(十六进制表示) |
|---|---|---|---|
| c1 | 'aaaa' | 4 | 0x04 |
| c2 | 'bbb' | 3 | 0x03 |
| c4 | 'd' | 1 | 0x01 |
又因为这些长度值需要按照列的逆序存放，所以最后变长字段长度列表的字节串用十六进制表示的效果就是（各个字节之间实际上没有空格，用空格隔开只是方便理解）：01 02 04
Q：如果变长列的内容占用的字节数可能需要2字节表示，又如何？
InnoDB有它的一套规则，首先声明一下W、M和L的意思：
假设某个字符集中表示一个字符最多需要使用的字节数为W，也就是使用SHOW CHARSET语句的结果中的Maxlen列，比方说utf8字符集中的W就是3，gbk字符集中的W就是2，ascii 字符集中的W就是1；
对于变长类型VARCHAR(M)来说，这种类型表示能存储最多M个字符(注意是字符不是字节)，所以这个类型能表示的字符串最多占用的字节数就是M×W；
假设它实际存储的字符串占用的字节数是L。
定使用1个字节还是2个字节表示真正字符串占用的字节数的规则就是这样：
- 如果M×W <= 255 ，那么使用1个字节来表示真正字符串占用的字节数。
也就是说InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最大字节数不大于255时，可以认为只使用1个字节来表示真正字符串占用的字节数。
- 如果M×W > 255 ，则分为两种情况：
如果L <= 127 ，则用1个字节来表示真正字符串占用的字节数。
如果L > 127 ，则用2个字节来表示真正字符串占用的字节数。
如果某个变长字段允许存储的最大字节数大于255时，该怎么区分它正在读的某个字节是一个单独的字段长度还是半个字段长度呢？
使用该字节的第一个二进制位作为标志位：如果该字节的第一个位为0，那该字节就是一个单独的字段长度(使用一个字节表示不大于127的二进制的第一个位都为0)，如果该字节的第一个位为1，那该字节就是半个字段长度。对于一些占用字节数非常多的字段，比方说某个字段长度大于了16KB，那么如果该记录在单个页面中无法存储时，InnoDB会把一部分数据存放到所谓的溢出页中，在变长字段长度列表处只存储留在本页面中的长度，所以使用两个字节也可以存放下来。
另外需要注意的一点是，变长字段长度列表中只存储值为非NULL的列内容占用的长度，值为 NULL的列的长度是不储存的。也就是说对于第二条记录来说，因为c4 列的值为NULL，所以第二条记录的变长字段长度列表只需要存储c1 和c2 列的长度即可。
并不是所有记录都有这个变长字段长度列表部分，比方说表中所有的列都不是变长的数据类型的话，这一部分就不需要有。
NULL值列表
表中的某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中，它的处理过程是这样的：
统计表中允许存储NULL的列有哪些（record_format_demo表c2不允许存储NULL）
将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下：
二进制位的值为1 时，代表该列的值为NULL； 二进制位的值为0 时，代表该列的值不为NULL。
MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。
表record_format_demo只有3个值允许为NULL的列，对应3个二进制位，不足一个字节，所以在字节的高位补0，效果就是这样：
上面两条记录在填充了NULL值列表之后的示意图就变成如下：
如果表中没有允许存储NULL的列，则NULL值列表也不存在了。
记录头信息
由固定的5个字节组成。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 | 表示下一条记录的相对位置 |
第一条记录的头信息：
记录的真实数据
对于record_format_demo表来说，记录的真实数据除了 c1、c2、c3、c4 这几个自己定义的列的数据以外，MySQL会为每个记录默认的添加一些列（也称为隐藏列 ），具体的列如下：
| 列 名 | 是否必须 | 占用空间 | 描 述 |
|---|---|---|---|
| DB_ROW_ID | 否 | 6 字节 | 行ID，唯一标识一条记录 |
| DB_TRX_ID | 是 | 6 字节 | 事务ID |
| DB_ROLL_PTR | 是 | 7 字节 | 回滚指针 |
InnoDB表对主键的生成策略： 优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为 DB_ROW_ID 的隐藏列作为主键。所以 InnoDB存储引擎会为每条记录都添加 DB_TRX_ID 和 DB_ROLL_PTR 这两个列，但是 DB_ROW_ID 是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）
上面两条记录加入上了 记录的真实数据 的两条记录如下：
注意第1条记录中c3列的值，它是CHAR(10)类型的，它实际存储的字符串是：'cc'，而ascii字符集中的字节表示是'0x6363'，虽然表示这个字符串只占用了2个字节，但整个c3 列仍然占用了10个字节的空间，除真实数据以外的8个字节的统统都用 空格字符 填充，空格字符在ascii字符集的表示就是 0x20
record_format_demo 表采用的是 ascii 字符集，这个字符集是一个定长字符集，也就是说表示一个字符采用固定的一个字节，如果采用变长的字符集（也就是表示一个字符需要的字节数不确定，比如gbk 表示一个字符要1~2个字节、utf8 表示一个字符要1~3个字节等）的话，c3 列的长度也会被存储到 变长字段长度列表 中
比如修改一下 record_format_demo 表的字符集：
ALTER TABLE record_format_demo MODIFY COLUMN c3 CHAR(10) CHARACTER SET utf8;
修改该列字符集后记录的 变长字段长度列表 也发生了变化，如图：
这就意味着：对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。
另外有一点还需要注意，变长字符集的CHAR(M) 类型的列要求至少占用M 个字节，而VARCHAR(M) 却没有这个要求。比方说对于使用utf8 字符集的CHAR(10) 的列来说，该列存储的数据字节长度的范围是10～30个字节。即使我们向该列中存储一个空字符串也会占用10 个字节，这是怕将来更新该列的值的字节长度大于原有值的字节长度而小于10个字节时，可以在该记录处直接更新，而不是在存储空间中重新分配一个新的记录空间，导致原有的记录空间成为所谓的碎片。
# 2.3 Redundant行格式
Redundant行格式是 MySQL5.0 之前用的一种行格式，非常旧。
行格式修改为Redundant：
ALTER TABLE record_format_demo ROW_FORMAT=Redundant;
之前插入的两条记录的存储数据如下：
字段长度偏移列表
Compact行格式的开头是变长字段长度列表，而Redundant行格式的开头是字段长度偏移列表，与 变长字段长度列表 有两处不同：
- 没有了 变长 两个字，意味着Redundant 行格式会把该条记录中 所有列（包括隐藏列 ）的长度信息都按照 逆序 存储到字段长度偏移列表
- 多了个 偏移 两个字，这意味着计算列值长度的方式不像Compact 行格式那么直观，它是采用两个相邻数值的 差值 来计算各个列值的长度。
比如第一条记录的 字段长度偏移列表 就是：25 24 1A 17 13 0C 06，因为它是逆序排放的，所以按照列的顺序排列就是：06 0C 13 17 1A 24 25
按照 两个相邻数值的差值来计算各个列值的长度 的意思就是：
- 第一列(
DB_ROW_ID) 的长度就是 0x06 个字节，也就是6个字节； - 第二列(
DB_TRX_ID) 的长度就是（0x0C - 0x06）个字节，也就是6个字节； - 第三列(
DB_ROLL_PTR)的长度就是（0x13 - 0x0C）个字节，也就是7个字节； - 第四列(
c1)的长度就是（0x17 - 0x13）个字节，也就是4个字节； - 第五列(
c2)的长度就是（0x1A - 0x17）个字节，也就是3个字节； - 第六列(
c3)的长度就是（0x24 - 0x1A）个字节，也就是10个字节； - 第七列(
c4)的长度就是（0x25 - 0x24）个字节，也就是1个字节。
记录头信息
Redundant行格式的记录头信息占用6字节，48 个二进制位，这些二进制位代表的意思如下：
| 名 称 | 大小(单位：bit) | 描 述 |
|---|---|---|
| 预留位1 | 1 | 没有使用 |
| 预留位2 | 1 | 没有使用 |
| delete_mask | 1 | 标记该记录是否被删除 |
| min_rec_mask | 1 | B+树的每层非叶子节点中的最小记录都会添加该标记 |
| n_owned | 4 | 表示当前记录拥有的记录数 |
| heap_no | 13 | 表示当前记录在页面堆的位置信息 |
| n_field | 10 | 表示记录中列的数量 |
| 1byte_offs_flag | 1 | 标记字段长度偏移列表中每个列对应的偏移量是使用1字节还是2字节表示的 |
| next_record | 16 | 表示下一条记录的相对位置 |
第一条记录中的头信息是：00 00 10 0F 00 BC
根据这六个字节可以计算出各个属性的值，如下：
预留位1：0x00
预留位2：0x00
delete_mask: 0x00
min_rec_mask: 0x00
n_owned: 0x00
heap_no: 0x02
n_field: 0x07
1byte_offs_flag: 0x01
next_record:0xBC
2
3
4
5
6
7
8
9
与Compact 行格式的记录头信息对比来看，有两处不同：
- Redundant行格式多了
n_field和1byte_offs_flag这两个属性； - Redundant行格式没有
record_type这个属性。
1byte_offs_flag
为了在解析记录时知道每个列的偏移量是使用1个字节还是2个字节表示的，MySQL在记录头信息里放置了 1byte_offs_flag 属性：
- 当它的值为1时，表明使用1个字节存储；
- 当它的值为0时，表明使用2个字节存储。
字段长度偏移列表 实质上是存储每个列中的值占用的空间在记录的真实数据 处结束的位置。以record_format_demo 第一条记录为例，0x06 代表第一个列在记录的真实数据 第6个字节处结束，0x0C 代表第二个列在记录的真实数据 第12个字节处结束，0x13 代表第三个列在记录的真实数据 第19个字节处结束，等等，最后一个列对应的偏移量值为0x25，也就意味着最后一个列在记录的真实数据 第37个字节处结束，也就意味着整条记录的真实数据 实际上占用37 个字节。
Q：每个列对应的偏移量可以占用1个字节或2个字节来存储，那何时用1个字节，何时用2个字节呢？
- 当记录的真实数据占用的字节数不大于127（十六进制0x7F，二进制01111111）时，每个列对应的偏移量占用1个字节；
- 当记录的真实数据占用的字节数大于127，但不大于32767（十六进制0x7FFF，二进制0111111111111111）时，每个列对应的偏移量占用2个字节。
有没有记录的真实数据大于32767的情况呢？有，不过此时的记录已经存放到了溢出页中，在本页中只保留前768个字节和20个字节的溢出页面地址（当然这20个字节中还记录了一些别的信息）。因为字段长度偏移列表 处只需要记录每个列在本页面中的偏移就好了，所以每个列使用2个字节来存储偏移量就够了。
Q：一个字节能表示的范围是0～255，为啥在记录的真实数据占用的存储空间大于127时就采用2个字节表示各个列的偏移量呢？
因为Redundant行格式并没有NULL值列表，MySQL在 字段长度偏移列表 中的各个列对应的偏移量处做了一些特殊处理 —— 将列对应的偏移量值的第一个比特位作为是否为NULL 的依据，该比特位也可以被称之为NULL比特位。也就是说在解析一条记录的某个列时，首先看一下该列对应的偏移量的NULL比特位 是不是为1，如果为1，那么该列的值就是NULL，否则不是NULL。
这就是为什么只要记录的真实数据大于127（十六进制0x7F，二进制01111111）时，就采用2个字节来表示一个列对应的偏移量，主要是第一个比特位是所谓的NULL比特位，用来标记该列的值是否为NULL。
对于值为NULL 的列来说，该列的类型是否为定长类型决定了NULL 值的实际存储方式，接下来分析一下record_format_demo表的第二条记录，它对应的 字段长度偏移列表 如下：A4 A4 1A 17 13 0C 06 按照列的顺序排放就是：06 0C 13 17 1A A4 A4
如果存储NULL值的字段是定长类型的，比方说 CHAR(M) 数据类型的，则NULL值也将占用记录的真实数据部分，并把该字段对应的数据使用0x00字节填充；
第二条记录的c3列的值是NULL，而c3列的类型是CHAR(10)，占用记录的真实数据部分10字节，所以可以看到在Redundant 行格式中使用0x00000000000000000000 来表示NULL值；
另外，c3 列对应的偏移量为0xA4，它对应的二进制实际是：10100100，可以看到最高位为1，意味着该列的值是NULL。将最高位去掉后的值变成了0100100，对应的十进制值为36，而c2列对应的偏移量为0x1A，也就是十进制的26。36 - 26 = 10，也就是说最终c3列占用的存储空间为10个字节；
如果该存储NULL值的字段是变长数据类型的，则不在记录的真实数据处占用任何存储空间；
c4列是VARCHAR(10) 类型的，VARCHAR(10) 是一个变长数据类型，c4列对应的偏移量为0xA4，与c3列对应的偏移量相同，这也就意味着它的值也为NULL，将0xA4的最高位去掉后对应的十进制值也是36，36 - 36 = 0，也就意味着c4列本身不占用任何记录的 实际数据 处的空间。
CHAR(M)列的存储格式
Compact行格式在 CHAR(M) 类型的列中存储数据的时候还挺麻烦，分变长字符集和定长字符集的情况，而在Redundant行格式中十分干脆，不管该列使用的字符集是啥，只要是使用CHAR(M) 类型，占用的真实数据空间就是该字符集表示一个字符最多需要的字节数和M的乘积。比方说使用utf8 字符集的CHAR(10) 类型的列占用的真实数据空间始终为30 个字节，使用gbk 字符集的CHAR(10) 类型的列占用的真实数据空间始终为20 个字节。由此可以看出来，使用Redundant 行格式的CHAR(M) 类型的列是不会产生碎片的。
行溢出数据
CREATE TABLE varchar_size_demo(
c VARCHAR(65535)
) CHARSET=ascii ROW_FORMAT=Compact;
2
3
从报错信息里可以看出，MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535 个字节。所以MySQL 服务器建议我们把存储类型改为TEXT或者BLOB类型。这个65535个字节除了列本身的数据之外，还包括一些其他的数据（storage overhead），比如说为了存储一个VARCHAR(M) 类型的列，其实需要占用3部分存储空间：
- 真实数据
- 真实数据占用字节的长度
- NULL 值标识，如果该列有NOT NULL 属性则可以没有这部分存储空间
如果该VARCHAR类型的列没有 NOT NULL 属性，那最多只能存储 65532 个字节的数据，因为真实数据的长度可能占用2个字节，NULL 值标识需要占用1个字节：
CREATE TABLE varchar_size_demo(
c VARCHAR(65532)
) CHARSET=ascii ROW_FORMAT=Compact;
2
3
记录中的数据太多产生的溢出
INSERT INTO varchar_size_demo(c) VALUES(REPEAT('a', 65532));
REPEAT('a', 65532) 表示生成一个把字符 'a' 重复65532 次的字符串。MySQL中磁盘和内存交互的基本单位是页，也就是说MySQL是以 页 为基本单位来管理存储空间的，记录都会被分配到某个页中存储。而一个页的大小一般是 16KB，也就是 16384字节，而一个VARCHAR(M) 类型的列就最多可以存储65532 个字节，这样就可能造成一个页存放不了一条记录的情况。
在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在 记录的真实数据 处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后 记录的真实数据 处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页，如下图所示：
从图中可以看出来，对于Compact 和Reduntant 行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前 768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做 行溢出，存储超出768字节的那些页面也被称为 溢出页。画一个简图就是如下：
不只是 VARCHAR(M) 类型的列，其他的 TEXT、BLOB 类型的列在存储数据非常多的时候也会发生行溢出
行溢出的临界点
Q：在列存储多少字节的数据时就会发生行溢出？
MySQL中规定 一个页中至少存放两行记录，以 varchar_size_demo 表为例，它只有一个列c，往这个表中插入两条记录，每条记录最少插入多少字节的数据才会 行溢出 的现象呢？这得分析一下页中的空间都是如何利用的。
- 每个记录需要的额外信息是27 字节。包括下边这些部分：
- 2个字节用于存储真实数据的长度；
- 1个字节用于存储列是否是NULL值；
- 5个字节大小的头信息；
- 6个字节的 DB_ROW_ID 列；
- 6个字节的 DB_TRX_ID 列；
- 7个字节的 DB_ROLL_PTR 列。
- 每个页除了存放我们的记录以外，也需要存储一些额外的信息，乱七八糟的额外信息加起来需要132个字节的空间。
假设一个列中存储的数据字节数为n，就需要满足这个式子：132 + 2×(27 + n) < 16384
求解这个式子得出的解是：n < 8099。也就是说如果一个列中存储的数据小于8099 个字节，那么该列就不会成为溢出列，否则该列就需要成为溢出列。不过这个8099个字节的结论只是针对只有一个列的varchar_size_demo表来说的，如果表中有多个列，那上边的式子和结论都需要改一改了，所以重点就是：只要知道如果一条记录的某个列中存储的数据占用的字节数非常多时，该列就可能成为溢出列。
# 2.4 Dynamic和Compressed行格式
MySQL 5.7默认行格式就是Dynamic，这俩行格式和Compact 行格式挺像，只不过在处理 行溢出 数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址，就像这样：
Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。
# 三：InnoDB 索引页结构
存放表中记录的页为索引页。索引页代表的这块16KB 大小的存储空间可以被划分为多个部分，不同部分有不同的功能，各个部分如图所示：
| 名称 | 中文名 | 占用空间大小 | 简单描述 |
|---|---|---|---|
| File Header | 文件头部 | 38 字节 | 页的一些通用信息 |
| Page Header | 页面头部 | 56 字节 | 索引页专有的一些信息 |
| Infimum + Supremum | 最小记录和最大记录 | 26 字节 | 两个虚拟的行记录 |
| User Records | 用户记录 | 不确定 | 实际存储的行记录内容 |
| Free Space | 空闲空间 | 不确定 | 页中尚未使用的空间 |
| Page Directory | 页面目录 | 不确定 | 页中的某些记录的相对位置 |
| File Trailer | 文件尾部 | 8 字节 | 校验页是否完整 |
# 3.1 记录在页中的存储
在页的7个组成部分中，我们自己存储的记录会按照我们指定的 行格式 存储到 User Records 部分。但是在一开始生成页的时候，其实并没有User Records 这个部分，每当我们插入一条记录，都会从 Free Space 部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records 部分，当Free Space 部分的空间全部被User Records 部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：
# 3.2 记录头信息
创建新表
CREATE TABLE page_demo(
c1 INT,
c2 INT,
c3 VARCHAR(10000),
PRIMARY KEY (c1)
) CHARSET=ascii ROW_FORMAT=Compact;
2
3
4
5
6
表中记录的行格式示意图如下：
新增几条记录：
INSERT INTO page_demo VALUES
(1, 100, 'aaaa'),
(2, 200, 'bbbb'),
(3, 300, 'cccc'),
(4, 400, 'dddd');
2
3
4
5
下面是这些记录在 页 的 User Records 部分 中的表示（只展示记录中头信息的一些字段和实际的列数据都用十进制表示出来了(实际是一堆二进制位，这里为了方便理解)，这些记录的示意图如下：
对照上图说明记录头信息中的各个属性的意思：
| 属 性 | 说 明 |
|---|---|
| delete_mask | 标记着当前记录是否被删除，占用1个二进制位，值为0的时候代表记录并没有被删除，为1的时候代表记录被删除掉了。被删除的记录还在页中，不会立即从磁盘移除，移除记录在磁盘上重新排列需要性能损耗，所以只打一个删除标记。所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉 |
| min_rec_mask | 值都是0，意味着它们都不是B+树的非叶子节点中的最小记录 |
| n_owned | 后面详述 |
| heap_no | 这个属性表示当前记录在本 页 中的位置，上面四条记录分别2~5缺了0和1。MySQL自动给每个页里边儿加了两个记录，称为伪记录或虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。对于 一条完整的记录 来说，比较记录的大小就是比较 主键 的大小 |
| record_type | 这个属性表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。他们插入的记录就是普通记录，它们的record_type值都是0 |
| next_record | 它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比方说第一条记录的next_record值为32，意味着从第一条记录的真实数据的地址处向后找32个字节便是下一条记录的真实数据。这其实是个链表，可以通过一条记录找到它的下一条记录。但是需要注意一点，下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定Infimum记录(也就是最小记录)的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是Supremum记录(也就是最大记录),如图: |
最小记录与最大记录
这两条记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定的部分组成的，如图所示：
由于这两条记录不是用户自己定义的记录，所以它们并不存放在页的User Records部分，他们被单独放在一个称为 Infimum + Supremum的部分，如图所示：
Q：next_record这个指针为啥要指向记录头信息和真实数据之间的位置呢？为啥不干脆指向整条记录的开头位置？
A：因为这个位置刚刚好，向左读取就是记录头信息，向右读取就是真实数据。变长字段长度列表、NULL值列表中的信息都是逆序存放，这样可以使记录中位置靠前的字段和它们对应的字段长度信息在内存中的距离更近，可能会提高高速缓存的命中率。
如果这时删除掉第二条记录，示意图如下：
DELETE FROM page_demo WHERE c1 = 2;
主要变化：
- 第2条记录并没有从存储空间中移除，而是把该条记录的delete_mask值设置为1；
- 第2条记录的next_record值变为了0，意味着该记录没有下一条记录了；
- 第1条记录的next_record指向了第3条记录；
- 最大记录的n_owned值从5变成了4。
再次把这条那条记录插入到表中
INSERT INTO page_demo VALUES(2, 200, 'bbbb');
从图中可以看到，InnoDB并没有因为新记录的插入而为它申请新的存储空间，而是直接复用了原来被删除记录的存储空间。最大记录的n_owned值从4变成了5。
# 3.3 Page Directory（页目录）
MySql为记录也制作了一个类似的目录，他们的制作过程是这样的：
- 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
- 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
- 将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录。页面目录中的这些地址偏移量被称为槽(英文名：Slot)，所以这个页面目录就是由槽组成的。
比方说现在的page_dem 表中正常的记录共有6条，InnoDB会把它们分成两组，第一组中只有一个最小记录，第二组中是剩余的5条记录，如下：
美化上图，单纯从逻辑上看一下这些记录和页目录的关系
Q：为什么最小记录的n_owned值为1，而最大记录的n_owned值为5呢？
对于最小记录所在的分组只能有1条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间 。
所以，分组按照下面步骤进行：
- 初始情况下一个索引页里只有最小记录和最大记录两条记录，它们分属于两个分组；
- 之后每插入一条记录，都会从页目录 中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个；
- 在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。
新增数据：
INSERT INTO page_demo VALUES(5, 500, 'eeee'),
(6, 600, 'ffff'),
(7, 700, 'gggg'),
(8, 800, 'hhhh'),
(9, 900, 'iiii'),
(10, 1000, 'jjjj'),
(11, 1100, 'kkkk'),
(12, 1200, 'llll'),
(13, 1300, 'mmmm'),
(14, 1400, 'nnnn'),
(15, 1500, 'oooo'),
(16, 1600, 'pppp');
2
3
4
5
6
7
8
9
10
11
12
这时候分组情况如下：
因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用所谓的二分法来进行快速查找。所以在一个索引页中查找指定主键值的记录的过程分为两步：
- 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录；
- 通过记录的next_record属性遍历该槽所在的组中的各个记录。
# 3.4 Page Header（页面头部）
MySQL为了能得到一个 索引页 中存储的记录的状态信息，比如本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等，特意在页中定义了一个叫 Page Header 的部分，这个部分占用固定的56个字节，专门存储各种状态信息，具体各个字节如下表：
| 名 称 | 占用空间大小 | 描 述 |
|---|---|---|
| PAGE_N_DIR_SLOTS | 2 字节 | 在页目录中的槽数量 |
| PAGE_HEAP_TOP | 2 字节 | 还未使用的空间最小地址，也就是说从该地址之后就是Free Space |
| PAGE_N_HEAP | 2 字节 | 本页中的记录的数量(包括最小和最大记录以及标记为删除的记录) |
| PAGE_FREE | 2 字节 | 第一个已经标记为删除的记录地址（各个已删除的记录通过next_record 也会组成一个单链表，这个单链表中的记录可以被重新利用） |
| PAGE_GARBAGE | 2 字节 | 已删除记录占用的字节数 |
| PAGE_LAST_INSERT | 2 字节 | 最后插入记录的位置 |
| PAGE_DIRECTION | 2 字节 | 记录插入的方向。假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是 PAGE_DIRECTION |
| PAGE_N_DIRECTION | 2 字节 | 一个方向连续插入的记录数量。假设连续几次插入新记录的方向都是一致的，InnoDB会把沿着同一个方向插入记录的条数记下来，这个条数就用PAGE_N_DIRECTION这个状态表示。当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计 |
| PAGE_N_RECS | 2 字节 | 该页中记录的数量（不包括最小和最大记录以及被标记为删除的记录） |
| PAGE_MAX_TRX_ID | 8 字节 | 修改当前页的最大事务ID，该值仅在二级索引中定义 |
| PAGE_LEVEL | 2 字节 | 当前页在B+树中所处的层级 |
| PAGE_INDEX_ID | 8 字节 | 索引ID，表示当前页属于哪个索引 |
| PAGE_BTR_SEG_LEAF | 10 字节 | B+树叶子段的头部信息，仅在B+树的Root页定义 |
| PAGE_BTR_SEG_TOP | 10 字节 | B+树非叶子段的头部信息，仅在B+树的Root页定义 |
# 3.5 File Header（文件头部）
File Header针对各种类型的页都通用，即不同类型的页都会以File Header作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页等等。这个部分占用固定的38个字节。
| 名 称 | 占用空间大小 | 描 述 |
|---|---|---|
| FIL_PAGE_SPACE_OR_CHKSUM | 4 字节 | 代表当前页的校验和(checksum值)。对于一个很长的字节串，会通过某种算法来计算一个比较短的值来代表这个很长的字节串，这个比较短的值就称为校验和。这样在比较两个很长的字节串之前先比较这两个长字节串的校验和，如果校验和都不一样两个长字节串肯定是不同的，所以省去了直接比较两个比较长的字节串的时间损耗 |
| FIL_PAGE_OFFSET | 4 字节 | 页号，每一个页都有一个单独的页号，InnoDB通过页号来可以唯一定位一个页 |
| 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 字节 | 页属于哪个表空间 |
InnoDB 为了不同的目的而把页分为不同的类型，FIL_PAGE_TYPE 其他页类型选项。
| 类型名称 | 十六进制 | 描 述 |
|---|---|---|
| FIL_PAGE_TYPE_ALLOCATED | 0x0000 | 最新分配，还没使用 |
| FIL_PAGE_UNDO_LOG | 0x0002 | Undo日志页 |
| FIL_PAGE_INODE | 0x0003 | 段信息节点 |
| FIL_PAGE_IBUF_FREE_LIST | 0x0004 | Insert Buffer空闲列表 |
| FIL_PAGE_IBUF_BITMAP | 0x0005 | Insert Buffer位图 |
| FIL_PAGE_TYPE_SYS 0x0006 | 系统页 | |
| FIL_PAGE_TYPE_TRX_SYS | 0x0007 | 事务系统数据 |
| FIL_PAGE_TYPE_FSP_HDR | 0x0008 | 表空间头部信息 |
| FIL_PAGE_TYPE_XDES | 0x0009 | 扩展描述页 |
| FIL_PAGE_TYPE_BLOB | 0x000A | BLOB页 |
| FIL_PAGE_INDEX | 0x45BF | 索引页 |
InnoDB都是以页为单位存放数据的，有时候我们存放某种类型的数据占用的空间非常大（比方说一张表中可以有成千上万条记录），InnoDB 可能不可以一次性为这么多数据分配一个非常大的存储空间，如果分散到多个不连续的页中存储的话需要把这些页关联起来，FIL_PAGE_PREV 和 FIL_PAGE_NEXT 就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了，而无需这些页在物理上真正连着。需要注意的是，并不是所有类型的页都有上一个和下一个页的属性，索引页是有这两个属性的，所以所有的数据页其实是一个双链表，就像这样：
# 3.6 File Trailer
Q：InnoDB 存储引擎会把数据存储到磁盘上，但是磁盘速度太慢，需要以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候中断电了咋办？
为了检测一个页是否完整(也就是在同步的时候有没有发生只同步一半的尴尬情况)，MySQL在每个页的尾部都加了一个File Trailer部分，这个部分由8 个字节组成，可以分成2个小部分：
- 前4个字节代表页的校验和
这个部分是和File Header中的校验和相对应的。每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header 在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了，那么在File Header中的校验和就代表着已经修改过的页，而在File Trialer 中的校验和代表着原先的页，二者不同则意味着同步中间出了错。Header中的校验和就代表着已经修改过的页，而在File Trialer中的校验和代表着原先的页，二者不同则意味着同步中间出了错。
- 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）
这个部分也是为了校验页的完整性的。
# 四：参考文献
- 《MySQL 是怎样运行的 - 小孩子4919》