1 InnoDB页简介

InnoDB将表中数据存储在磁盘上，真正处理数据的过程发生在内存中，如果是处理写入或修改请求，还需要把内存中的内容重新刷新到磁盘上。

InnoDB以**==页==**作为磁盘和内存之间交互的基本单位。页的大小默认为==16KB==（16*1024=16384）。对应系统变量是innodb_page_size，只能在第一次初始化MySQL数据目录时指定，服务器运行时不能更改页面大小。

2 InnoDB行格式

平常以==记录==为单位向表中插入数据，这些记录在磁盘上的存放方式被成为**==行格式==（也叫作==记录格式==），4种==行格式==**：Compact、Redundant、Dynamic、Compressed。

1️⃣指定行格式的语法

1
2
3

Create Table 表名 (列的信息) Row_Format=行格式名称;

Alter Table 表名 Row_Format=行格式名称;

1
2
3
4
5
6
7
8

Create Table record_format_demo (
	c1 varchar(10),
  c2 varchar(10) not null,
  c3 char(10),
  c4 varchar(10)
) Charset=ascii Row_Format=Compact;

Insert into record_format_demo(c1, c2, c3, c4) values('aaaa', 'bbb', 'cc', 'd'), ('eeee', 'fff', Null, Null);

1
2
3
4
5
6
7
8

mysql> select * from record_format_demo;
+------+-----+------+------+
| c1   | c2  | c3   | c4   |
+------+-----+------+------+
| aaaa | bbb | cc   | d    |
| eeee | fff | NULL | NULL |
+------+-----+------+------+
2 rows in set (0.00 sec)

2️⃣Compact行格式

一条完整的记录分为两大部分：

1.==记录的额外信息==
a 变长字段长度列表

MySQL支持一些变长的数据类型：Varchar(M)、Varbinary(M)、各种Text类型、各种Blob类型。【变长字段】

变长字段占用的存储空间分为两个部分：真正的数据内容；该数据占用的字节数。

在COMPACT行格式中，所有变长字段的真实数据占用的字节数都存放在记录的开头位置，从而形成一个==变长字段长度列表==，各变长字段的真实数据占用的字节数按照列的顺序**==逆序存放==**（如下面的c4、c2、c1）。

表record_format_demo的c1、c2、c4字段都是varchar(10)，这3个列的值占用的存储空间字节数保存在记录开头处。

变长字段的真实数据占用的字节数是用1字节还是2字节表示？ InnoDB通过W、M、L是三个符号制定一套规则，这个三个符号的意义：

  • 某个字符集表示一个字符最多需要W字节。（比如utf8mb4的W是4，utf8是3，gbk是2，ascii是1）
  • 对于varchar(M)，表示这种类型最多能存储M个字符。那么这种类型表示的字符串最多占用的字节数就是M * W。
  • L表示变长字段实际存储的字符串占用的字节数。

规则：

  • M * W <= 255，使用1个字节表示变长字段的真实数据占用的字节数。

  • M * W > 255

    L <= 127，1字节

    L > 127，2字节

总结：如果该变长字段允许存储的最大字节数（M * W）超过255字节，并且真实数据占用的字节数(L）超过 127字节，则使用2字节来表示真实数据占用的字节数，否则使用 1字节。

变长字段长度列表中只存储值为非NULL的列的内容长度。

如果表中所有的列都不是变长的数据类型或所有列的值都是NULL，那么就不需要变长字段长度列表。

b NULL值列表

Compact行格式把一条记录中值为NULL的列统一管理起来（节省空间），存储在NULL值列表中。

处理过程：

  1. 首先统计表中允许存储NULL的列有哪些。

    主键列和NOT NULL列不会被统计。

  2. 每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列。二进制位为1表示该列的值为NULL，0表示不为NULL。

    如果表中没有允许存储NULL的列，则NULL值列表就不存在。

  3. 规定NULL值列表必须使用整数个字节的位表示，如果不够，字节的高位补0。

    如之前表的表只有3个列允许为NULL（c2是NOT NULL，不统计）：

    第一条记录的c1、c3、c4都不为NULL，二进制位都为0；第二条记录的c3、c4为NULL，二进制位为1：

这两条记录的填充NULL值列表后：

c 记录头信息

固定5字节，也就是40个二进制位。前4位称为info bit。

record_format_demo表中的两条记录的记录头信息：

2.==记录的真实数据==

MySQL会为每个记录默认地添加一些隐藏列：

InoDB表的主键生成策略：

  • 优先使用用户自定义的主键作为主键；
  • 如果用户没有定义主键，则选取一个不允许存储NULL值的Unique键作为主键；
  • 如果表中连不允许存储NULL值的Unique键都没有定义，则InnoDB会为表默认添加个名为row_id的隐藏列作为主键。（否则默认是不会有row_id的）

加上记录的真实数据的两条记录：

  • 表record_forat_demo使用的是ascii字符集，所以Ox61616161 就表示字符串“aaaa’，Ox626262就表示字符串「bbb’；依此类推。

  • 第一条记录c3列式CHAR(10)类型（固定），实际存储的是只占2个字节的字符串'cc'（0x6363），后面8个字节用空格（0x2020）填充。

  • 注意第二条记录中c3和c4列的值都为NULL，它们被存储在了前面的NULL值列表处，在记录的真实数据处就不再冗余存储，从而节省了存储空间，NULL列表应该是110也就是06。

3.Char(M)列的存储格式

表record_format_demo的c3列类型是CHAR(10)，也就是c3列不属于变长字段，其它三个VARCHAR(10)列占用的字节长度逆序存到变长字段长度列表中：

表record_format_demo采用的是ascii字符集（一个字符用一个字节编码）。

如果采用变长编码的字符集（也就是一个字符不确定几个字节编码，如gbk是1-2个、utf8是1-3个），设计者规定此时CHAR(10)类型的c3列占用的字节数也会被存储到变长字段长度列表中。

1

Alter Table record_format_demo Modify Column c3 Char(10) Character Set utf8;

修改后：

总结：

对于CHAR(M)类型的列来说，当列采用的是==定长编码的字符集==时，该列占用的字节数不会被加到变长字段长度列表；而如果采用==变长编码的字符集==时，则会。

其它几种行格式，都可以以COMPACT格式"依葫芦画瓢"了。

3️⃣Redundant行格式

MySQL5.0之前使用的，比较原始，占用空间页比较大。比较少的使用了。

4️⃣溢出列

1
2
3
4
5

Create Table off_page_demo (
	c Varchar(65532)
) Charset=ascii Row_Format=Compact;

Insert Into off_page_demo (c) Values(Repeat('a', 65532));

一个页一般大小16KB（16384B），上面的c列占用65532B。

在COMPACT和Redundant行格式中，对于占用存储空间非常多的列，在记录的真实数据处只会存储该列的一部分数据，而把剩余的数据分散存储在其它的页中，然后在记录的真实数据处用==20字节==存储志向这些页的地址和其在其它页面中占用的字节数，从而可以找到剩余数据所在的页，

上面存储768字节之外的数据的页面叫作==溢出页==。

需要溢出页来存储的列叫作==溢出列==。Varchar(M)、Text、Blob等类型都可能成为溢出列。

产生溢出页的临界点

MySQL中规定一个页中至少存放两行记录。

重点：不用关注这个临界点是什么，只要知道如果一条记录的某个列中存储的数据占用的字节数非常多时，该列就可能成为溢出列。

5️⃣Dynamic行格式和Compressed行格式

这两种类似于Compact，处理溢出列数据有点不同：它们不会在记录的真实数据处存储列真实数据的前768字节，而是把所有的数据都存储到所谓的溢出页中，只在记录的真实数据处存储指向这些溢出页的地址。

另外Compressed会采用压缩算法对页面进行压缩。

MySQL 5.7 及更高版本（含 MySQL 8）中，innodb_default_row_format 的全局默认值为 DYNAMIC。

3 小结

页是InnoDB中磁盘和内存交互的基本单位，也是InnoDB管理存储空间的基本单位，默认大小为16KB。

指定和修改行格式的语法如下:

1
2

CREATE TABLE 表名(列的信息) ROW_FORMAT=行格式名称;
ALTER TABLE 表名 ROW_FORMAT=行格式名称;

InnoDB的4种行格式：

  • Compact行格式

  • REDUNDANT行格式

  • DYNAMIC和COMPRESSED行格式

这两种行格式类似于COMPACT行格式,只不过在处理溢出列数据时有点儿分歧:它们不会在记录的真实数据处存储列真实数据的前768字节，而是把所有的数据都存储到所谓的溢出页中，只在记录的真实数据处存储指向这些溢出页的地址。另外，COMPRESSED行格式会采用压缩算法对页面进行压缩。