InnoDB 引擎

MySQL 架构图

MySQL 服务器中负责对表中数据的读取和写入工作的部分是存储引擎，而服务器又支持不同类型的存储引擎，比如 InnoDB、MyISAM、Memory 等，不同的存储引擎一般是由不同的业务实现而产生不同的特征。真实的数据在不同的存储引擎中存放的格式一般是不同的，甚至存储引擎 Memory 都不用磁盘才存储数据，也就是说关闭了服务器后，表中的数据也就消失了

InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，所以即便关机后重启，我们的数据还是存在，而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果处理的是写入或修改的请求的话，还需要把内存中的数据刷新到磁盘上，而我们知道读写磁盘的速度是非常慢的，和内存读写差了几个数量级，所以当我们想从表中获取某些记录的时候，InnoDB 存储引擎需要一条一条把记录从磁盘上读取出来么？不，那样效率就太低了，InnoDB 采取的方式是：将数据划分为若干页，以页作为和磁盘的交互单位，InnoDB 中页的大小一般是 16KB，也就是说在一般情况下，一次最少从磁盘读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容写入到磁盘中。

InnoDB 数据页结构

页是 InnoDB 管理存储空间的基本单位，一个页的大小一般是 16KB

-- InnoDB 每页大小
show GLOBAL STATUS like '%Innodb_page_size%';
select 16384/1024 as 'KB';

存放数据的页称为数据页，数据页代表这块 16KB 大小的存储空间可以被划分为多个部分，不同的部分有不同的功能，各个部分如图所示：

一个 InnoDB 数据页的存储空间大致被划分成 7 个部分，有的部分占用的字节数是确定的，有的部分是不确定的

在页的 7 个组成部分中，我们自己存储的记录会存储到 User Records 部分，但是在一开始生成页的时候，其实并没有 User Records 整个部分，每当我们插入一条记录，都会从 Free Space 部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分，当 Free Records 部分的空间全部被 User Records 部分替代后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了

InnoDB 表模式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上存放方式也被称为行格式或者记录格式。设计 InnoDB 存储引擎的设计者们，迄今为止设计了 4 种不同类型的行格式，分别是：Compact、Redundant、Dynamic 和 Compressed 行格式，随着时间的推移，他们可能会设计出更多的行格式，但是不管怎么变，在原理上大体都是相同的。

我们可以在创建或者修改的语句中指定行格式：

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

Compac 行格式

记录的额外信息

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为 3 类，分别是变长字段长度列表、NULL 值列表和记录头信息

• 变长字段列表：我们知道 MySQL 支持一些变长的数据类型，比如：VARCHAR(M)、VARBINAYR(M)、各种 TEXT 类型、各种 BLOB 类型，我们也可以把拥有这些特征的列叫做变长字段，变成字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据的占用字节也存起来。在 Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表
• NULL值列表：我们知道某些列可能存储 NULL 值，如果把这些 NULL 值都放到真实数据中存储会非常浪费空间，所以 Compact 行格式把这些值为 NULL 的列统一管理起来，存到 NULL 值列表中，如果表中没有允许存储 NULL 的列，那么 NULL 值列表也就不存在了，将每个允许存储 NULL 的列对应一个二进制数位
  • 二进制位的值为 1 时，代表该列的值为 NULL
  • 二进制位的值位 0 时，代表该列的值不为 NULL
• 记录头信息：除了变长字段列表，NULL 值列表外，还有一个用于描述记录的记录头信息，它是由固定的 5 个字节组成，5 个字节也就是 40 个二进制位，不同的位代表不同的含义，如下图：

• 记录真实的数据：记录真实的数据，除了我们自己定义的列的数据以外，MySQL 会为每个记录默认添加一些列（也称为隐藏列），具体的列如下：

InnoDB 表对主键的生成策略：优先使用用户自定义的主键作为主键，如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 也没有定义的话，则 InnoDB 会默认添加一个 row_id 的隐藏列作为主键。所以从上述表我们可以看出：InnoDB 存储引擎会为每条记录添加一个 transaction_id 和 roll_pointer 这两个列，但是 row_id 是可选的（在没有自定义主键以及 Unique 键的情况下才会添加该列）。这些隐藏列的值不需要我们维护，InnoDB 存储引擎会自动帮我们生成与维护。

行溢出数据

对于 VARCHAR(M) 类型的列最多可以占用 65535 个字节，其中 M 表示该类型最多存储的字符数量，如果我们使用 ASCII 字符集的话，一个字符就代表一个字节，我们看看 VARCHAR(65535) 是否可用：

CREATE TABLE varchat_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
> 时间: 0.001s

从报错信息可以看出，MySQL 对一条记录占用最大存储空间是有限制的，除了 BLOB 或者 TEXT 类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节，所以 MySQL 服务器建议我们把存储类型改为 TEXT 或 BLOB 的类型。这个 65535 个字节除了本身的数据之外，还包括其他的一些数据，比如说我们为了存储一个 VARCHAR(M) 类型的列，其实还需要 3 部分存储空间：

• 真实数据
• 真实数据占用字节的长度
• NULL 值标识（如果该列有 NOT NULL 数据，则可以没有这部分存储空间）

如果该 VARCHAR 类型的列，没有 NOT NLL 属性，那最多能存储 65532 个字节数据，因为真实数据长度可能占了 2 个字节，NULL 值标识需要占用 1 个字节
**

记录中的数据太多产生的溢出

MySQL 中磁盘和内存交互的基本单位是页，也就是说 MySQL 是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中进行存储，而一个页的大小一般是 16 KB，也就是 16384 个字节，而一个 VARCHAR(M) 类型的列最多可以存储 65533 个字节，这样就可能造成一个页存放不了一条记录的情况。

• 在 Compact 和 Reduntant 行格式中：对于占用存储空间非常大的列，在记录的真实数据处只会该列的一部分数据，把剩余部分的数据存储在其他页中，然后记录的真实数据处用 20 个字节存储指向其他页的地址（当然这 20 个字节还包括这些分散在其他页的数据占用的字节数），从而可以找到剩余数据所在的页。

• Dynamic 和 Compressed 行格式中：只不过在处理行溢出数据时有点分歧，它们不会在记录的真实数据处存储一部分数据去记录其他页的地址，而是把所有数据都存储到其他页面中，只在记录真实数据处存储其他页面的地址。另外，Compressed 行格式会采用压缩算法对页面进行压缩