MySQL 运行原理 [表]

文章根据《MySQL技术内幕：InnoDB存储引擎（第二版）》、掘金小册《MySQL是怎样运行的：从根儿上理解MySQL》 两本书得到的总结

本篇文章将介绍MySQL表的逻辑存储及实现，也就是数据在表中如果组织和存放的。

表的概述

表就是关于特定实体的数据集合，这也是关系数据库模型的核心。

索引组织表

在innodb存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

每张表都有主键，若没有则会按如下方式选择或创建主键：

• 首先判断表中是否有非空的唯一索引（unique not null）如果有，则该列即为主键
• 如果不符合上述条件，innodb存储引擎自动创建一个6字节的指针
• 当表中有多个非空唯一索引时，innodb存储引擎将选择建表时第一个定义的非空唯一索引为主键

InnoDB存储逻辑结构

所有数据都逻辑地存放在一个空间内，这个空间就是我们所说的表空间，表空间可以看做成InnoDB存储引擎逻辑结构的最高层，结构图如下（表截图自《MySQL技术内幕：InnoDB存储引擎（第二版）》）

从结构图中，可以看到表空间由段、区、页、行四大部分组成

• 段：表空间由各个段组成，常见的段有数据段、索引段、回滚段等
• 区：段由各个区组成，区由连续页组成，在任何情况下区的大小都为 1MB
  • 为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请 4~5个区，默认情况下InnoDB存储引擎页的大小为16KB，即一个区中一个有64个连续的页
• 页：InnoDB存储引擎磁盘管理最小单位【会单独写篇文章来详细介绍数据页】，常见的页类型有：数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲页、未压缩的二进制大对象页、压缩的二进制大对象页
• 行：InnoDB存储引擎是面向列的，也就是说数据是按行进行存放的

InnoDB行记录格式

我们正常都是以行为单位向表中插入数据，这些数据在磁盘上存放的格式被称为 行格式 或者 记录格式 ，行记录格式有多种如： Compact，Redundant，Dynamic，Compressed，MySQL5.1开始默认行格式为 Compact。
可以通过以下命令来修改行记录格式

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

ALTER TABLE 表名 ROW_FORMAT=行格式名称

Compact行记录格式的结构图（表截图自《MySQL技术内幕：InnoDB存储引擎（第二版）》）如下：

• 变长字段长度列表：Compact行记录格式的首部是一个非NULL变长字段长度列表，逆序放置
  • 把所有变长字段的真实数据占用的字节长度都存放至变长字段长度列表中
  • 若列长度小于255字节，用1字节表示，若大于255字节则用2字节表示
• NULL 标志位：指示该行数据中是否有NULL值，有则1表示
• 记录头信息：固定占5个字节，也就是40个二进制位，不同位代表不同意思，如图（）：

• Redundant行记录格式：MySQL5.1版本之前的行记录格式
• 行溢出数据【下面会详细介绍】
• Compressed和Dynamic行记录格式
  • Dynamic行记录格式与Compact行记录格式相似，区别在于处理行溢出数据时不同
  • Compressed行记录格式与Dynamic行记录格式不同的是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间

行溢出数据

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外，一般认为只有BLOB，LOB这种大对象数据类型的存储会把数据存放在页面之外，但是这种理解是有一点偏差的，
BLOB也可以不将数据存放在页面之外，而且即便是VARCHAR类型，依然有可能存放为行溢出数据。

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

不止是BLOB、LOB这种大对象数据太多会产生溢出，VARCHAR类型同样会产生溢出。

行溢出的临界点

在列存储多少个字节的数据时会发生行溢出呢？
MySQL规定一个数据页至少存放两条数据记录，我们可以先分析下页中的空间是如何利用的

• 每个页除了存放我们数据记录之外还需要存放其他信息，比如 File Header，Page Header 等等，共需要 132 个字节空间，其他空间都可以被存放数据记录
• 每个记录需要额外的 27 个字节信息，27个字节信息包括
  • 2个字节用于存储真实数据的长度
  • 1个字节用于存储列是否是NULL值
  • 5个字节大小的头信息
  • 6个字节的row_id列
  • 6个字节的transaction_id列
  • 7个字节的roll_pointer列

假设一个列中存储的数据字节数为n，设计MySQL的大叔规定如果该列不发生溢出的现象，就需要满足下边这个式子：

132 + 2×(27 + n) < 16384

得到 n 为8099，也就是说这个列可以存放8099字节信息，若大于则为溢出，这只是一个列的情况下，若多列的时候，这个数值就不能作为参考，所以最终的结论是：你不用关注这个临界点是什么，只要知道如果我们一条记录的某个列中存储的数据占用的字节数非常多时，该列就可能成为溢出列。

本作品采用《CC 协议》，转载必须注明作者和本文链接