【转载】知乎：关于MySQL底层的一些疑问

作者：木木
链接：https://www.zhihu.com/question/26398102/answer/3021842336

前言

从本文开始，我将分享一下近期学习 MySQL 的笔记，其中大部分来源于极客时间的《MySQL实战45讲》、小林coding、以及部分其他博客和书籍。
这次系列文章着重讲 MySQL 的原理部分，主要是用于面试，也就是我们常说的八股，至于基础的内容就不再介绍了。我将在后续文章中陆续介绍以下内容：存储结构、逻辑架构和执行流程、索引、事务、锁、日志、内存管理、磁盘空间管理、集群相关等。
另外，如果没有额外说明，介绍的主要内容都是关于默认的 InnoDB 引擎。
接下来开始正式讲解逻辑存储架构相关内容。我们都知道，MySQL 是将数据存储到硬盘、磁盘等外部存储器上的，逻辑存储架构，指的就是 MySQL 是如何组织、存储数据的。

逻辑存储结构

InnoDB 的所有数据都被逻辑地存放在表空间（tablespace）中，表空间又由段（segment），区/簇（extent），页（page）组成。

表空间（tablespace）：表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层 ，所有的数据都是存放在表空间中。默认情况下有一个共享表空间 ibdata1 ，所有数据都放在这个表空间内 。
段（segment）：表空间由多个段组成，每一个索引在表空间中都有两个段，分别是叶子节点段（数据段）和非叶子节点段（索引段）。叶子节点保存在一个段内，可以更好的保持叶子节点的连续性，可以提升访问磁盘的效率。
区（extend）：多个页组成一个区，对于不大于16KB 的页来说，一个区是 1MB（64 个连续的 16KB 页或者 128 个 8KB 或者 256 个 4KB），对于大于 16K 的页，都是连续 64 个页组成一个区，比如 128KB 的页对应 2MB 的区，256KB 的页对应 4MB 的区。
页（page）：固定大小的一段连续空间，默认是 16KB，可以通过参数 nnodb_page_size 设置。是读写操作的最小单位。
行（row）：指表中的一条记录，是存储的基本单位。

表空间

系统表空间

System Tablespace，系统表空间可以对应文件系统上一个或多个实际的文件，默认情况下，InnoDB 会在数据目录下创建一个名为 ibdata1 的文件，大小为 12MB，这个文件就是对应的系统表空间在文件系统上的表示。这个文件是自扩展文件，当不够用的时候它会自己增加文件大小。在一个 MySQL 服务器中，系统表空间只有一份。

InnoDB 有四个最基本的系统表：
SYS_TABLES：记录了表的名称、每个表对应唯一的 ID、表拥有列的个数、表的类型（包括一些文件格式，行格式等）、MIX_ID、表所属表空间的 ID 等；
SYS_COLUMNS：记录了列所属表对应的 ID、列是表中的第几列、列名称、主数据类型、精确数据类型（比如是否允许 NULL 等）、列最多占用存储空间的字节数、列的精度【默认都是0】；
SYS_INDEXES：记录了索引所属表对应的 ID、索引的唯一 ID、索引名称、索引包含列的个数、索引类型、索引根页面所在表空间 ID、索引根页面所在的页面号等；
SYS_FIELDS：记录了索引列所属索引的 ID、索引列在某个索引中是第几列、索引列的名称等；

系统表空间与独立表空间的一个非常明显的不同之处就是在表空间开头有许多记录整个系统属性的页面：

独立表空间

File-per-table Tablespace，在 MySQL5.6.6 之后，InnoDB 并不会默认地把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间。创建一个表后，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名为 表名.ibd。
可以指定使用系统表空间还是独立表空间来存储数据，这个功能由启动参数 innodb_file_per_table 控制，在 my.cnf 配置文件中。

独立表空间整体结构如下：

其他表空间
撤销表空间（Undo Tablespaces）：专门用来存放 undolog 的日志文件。
临时表空间（Temporary Tablespaces）：存放用户创建的临时表和磁盘内部临时表。
通用表空间（General Tablespaces）：类似于系统表空间，通用表空间是共享表空间，可以存储多个表的数据。

段

段不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面（碎片区）和一系列完整的区组成。
一个表空间里面会有很多个段组成。常见的段有数据段、索引段、回滚段等。
数据段，leaf node segment：管理叶子节点的数据；
索引段，non-leaf node segment：管理非叶子节点的数据；
回滚段，rollback segment：存储 undo log，用于实现事务回滚；
每向表中插入一条记录，本质上就是向该表的聚簇索引以及所有二级索引代表的 B+ 树的节点中插入数据。
InnoDB 对 B+ 树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段，存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成两个段，一个叶子节点段和一个非叶子节点段。
每个索引段的大小是固定的，通常为 16KB。如果索引键的大小超过了 16KB，则需要使用多个索引段来存储。

InnoDB 为每个段都定义了一个 INODE Entry 结构来记录段中的属性。

Segment ID：段的唯一 ID；
NOT_FULL_N_USED：指在 NOT_FULL 链表中已经使用了多少个页面；
三个 List Base Node：分别为段的 FREE 链表、NOT_FULL 链表、FULL 链表定义了头节点；
Magic Number：用来标记这个 INODE Entry 是否已经被初始化了（初始化的意思就是把各个字段的值都填进去了）。如果这个数字是值的 97937874，表明该 INODE Entry 已经初始化，否则没有被初始化。
Fragment Array Entry：段是一些零散页面和一些完整的区的集合，每个 Fragment Array Entry 结构都对应着一个零散的页面，这个结构占 4 个字节，表示一个零散页面的页号。

段链表

InnoDB 为每个段中的区对应的 XDES Entry 结构建立了三个链表：
FREE 链表：同一个段中，所有页面都是空闲的区对应的 XDES Entry 结构会被加入到这个链表。注意和直属于表空间的FREE链表区别开了，此处的 FREE 链表是附属于某个段的。
NOT_FULL 链表：同一个段中，仍有空闲空间的区对应的 XDES Entry 结构会被加入到这个链表。
FULL 链表：同一个段中，已经没有空闲空间的区对应的 XDES Entry 结构会被加入到这个链表。
每个段都会维护上述的三个链表。

区

不论是系统表空间还是独立表空间，都可以看成是由若干个区组成的，每 256 个区被划分成一组。对于 16KB 的页来说，连续的 64 个页就是一个区，也就是说一个区默认占用 1MB 空间大小。
一个区中的页，在物理空间中是连续的，所以在读取时可以减少随机 IO 的次数，从而提高读取速度。

第一个组最开始的三个页面的类型是固定的，分别是：
FSP_HDR：登记整个表空间的一些整体属性以及本组所有的区。整个表空间只有一个 FSP_HDR 类型的页面。
IBUF_BITMAP：存储本组所有的区的所有页面关于 INSERT BUFFER 的信息。
INODE：存储了许多称为 INODE 的数据结构。
其余各组最开始的两个页面的类型是固定的，分别是：
XDES：全称是 extent descriptor，用来登记本组各个区的属性。
IBUF_BITMAP：同上。

XDES Entry

为了方便管理区，MySQL 设计了 XDES Entry（Extent Descriptor Entry）结构，每个区都对应着一个 XDES Entry，这个结构记录了对应的区的一些属性。
XDES Entry 结构有 40 字节，大致分为 4 个部分：

Segment ID：每一个段都有一个唯一的 ID。Segment ID 字段表示该区所在的段，前提是该区已经被分配给某个段了，不然该字段的值没有意义；
ListNode：这个部分可以将若干个 XDES Entry 结构串联成一个双向链表；
State：区的状态，总共有四种类型。处于 FREE、FREE_FRAG、FULL_FRAG 这三种状态的区都是独立的，算是直属于表空间；而处于 FSEG 状态的区是附属于某个段的：

FREE，空闲的区：现在还没有用到这个区中的任何页面；
FREE_FRAG，有剩余空间的碎片区：表示碎片区中还有可用的页面；
FULL_FRAG，没有剩余空间的碎片区：表示碎片区中的所有页面都被使用，没有空闲页面；
FSEG，附属于某个段的区：每一个索引都可以分为叶子节点段和非叶子节点段，除此之外 InnoDB 还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。

Page State Bitmap：这个部分共占用 16 字节，也就是 128 位，一个区默认有 64 个页，这 128 位被划分为 64 个部分，每个部分有 2 位，对应区中的 1 个页。第一个位表示对应的页是否是空闲的，第二个比特位还没有用。

区链表

把状态为 FREE 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，即 FREE 链表；
把状态为 FREE_FRAG 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，即 FREE_FRAG 链表。
把状态为 FULL_FRAG 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，即 FULL_FRAG 链表。

碎片区

段是以区为单位申请存储空间的，一个区默认占用 1MB 的空间，如果为段中很小的数据量申请区，则会造成大量空间的浪费，所以 InnoDB 提出了碎片区的概念，碎片区中的页并不都是为了存储同一个段中的数据而存在的，比如有的页用于段 A，有的用于段 B，有的页甚至哪个段也不属于。碎片区直属于表空间，并不属于任何一个段。
为段分配存储空间的策略是：
起初向表中插入数据的时候，段是从某个碎片区以单个页面来分配存储空间的；
当某个段已经占用了 32 个碎片区页面（半个区）之后，就会申请以完整的区为单位来分配存储空间。

数据页

记录是按照行来存储的，但是数据库的读取并不是以行为单位的。
InnoDB 的数据是按数据页为单位来读写的，即数据库的 I/O 操作的最小单位是页，是磁盘和内存之间交互的基本单位，默认大小为 16KB。

在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表。
数据页中的记录（User Record）按照主键顺序组成单向链表。
数据页中有一个页目录，起到记录的索引作用。

每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）
页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。
InnoDB 对每个分组中的记录条数都是有规定的，槽内的记录就只有几条：
第一个分组中的记录只能有 1 条记录；
最后一个分组中的记录条数范围只能在 1-8 条之间；
剩下的分组中记录条数范围只能在 4-8 条之间。

数据行

MySQL 有四种行格式：Redundant，Compact，Dynamic，Compressed。我们可以在创建表的时候指定字符集和行格式。
MySQL 5.7 默认使用的是 Dynamic 的行格式。

Redundant

Redundant 是 MySQL5.0 版本之前的行格式，现在一般不会使用，只有 MySQL 的一些系统表会使用。

整个行格式分为记录的额外信息和记录的真实数据两部分，其中记录的额外信息又分为两部分：
字段长度偏移列表：会把所有字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成⼀个字段长度偏移列表，字段长度占用的字节数按照列的顺序逆序存放。
记录头信息：占用 6 字节，即 48 位，具体如下：

记录的真实数据除了列数据以外，还会为每个记录默认添加一些列（也称为隐藏列），具体如下：
DB_ROW_ID(row_id) ：占 6 个字节。当表没有定义主键，则选择 unique 键作为主键；如果仍没有，则默认添加一个名为 DB_ROW_ID 的隐藏列作为主键。也就是说这个列只有当没有主键也没有索引时才存在；
DB_TRX_ID(transaction_id)：占 6 字节。事务 id；
DB_ROLL_PTR(roll_pointer)：占 7 字节。回滚指针，MVCC 的时候会用到；
DB_DELETED_BIT(deleted_bit)：占 1 字节，用于标识记录是否已删除；

Compact

Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的数据行。
Compact 描述信息的格式如下：

变长字段长度列表中存储的是每个非空的变长字段的数据长度，同样也是逆序存放的。如果表中没有变长的数据类型的话，就不需要该列表了。如果长度小于 255B，就用 1B 表示，否则用 2B 表示，最大不会超过 2B，因为 MySQL 中变长类型的最大字节长度限制为 65535。
如果表中没有允许 NULL 的列，那么 NULL 标记位就不存在了。否则将每个允许存储 NULL 的列对应一个二进制位并且逆序存放，然后将允许为 NULL 的位标为 1，不允许的标为 0；且必须用整数个字节的位表示，否则在高位补 0。
Compact 格式的记录头具体信息如下：

相比 Redundant，多了一个 record_type 字段，少了 n_field 和 1byte_offs_flag 两个字段。

Compact 的隐藏列和 Redundant 相同。

Compact 格式下，具有 65535 字节的最大行大小限制，但是设置允许 NULL 需要占用 1 个字节，设置为变长（varchar）需要占用两个字节。在不同的编码格式下，一个字符占用的字节是不同的，比如 encode=utf8 时，三个字节表示一个字符。
数据页默认大小为 16KB，即 16*1024=16384byte，所以当要存储地单行数据超过 16384byte 时，会发生行溢出。
但在 MySQL 的设定中，会提早发生行溢出。当列的类型为 VARCHAR、 VARBINARY、 BLOB、TEXT 时，该列超过 768byte 的数据溢出存放到溢出页中，然后通过一个偏移量指针将两者关联起来，这就是行溢出机制。这样可以有效地防止单个列太大导致单个数据页中存放的行记录过少而让 IO 飙升的情况。

Compact 能比 Redundant 格式节约 20% 的存储空间。

Dynamic

Dynamic 和 Compact 行格式类似，只是在处理行溢出数据时有区别。Dynamic 的数据页不会存储真实数据的前 768 字节，而是存储 20 个字节的指针来指向溢出页，即将所有的溢出数据都存在溢出页中。

Compressed

Compressed 与 Dynamic 相似，区别是 Compressed 存储的行数据会以 zlib 的算法进行压缩以节省空间，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。

数据字典

系统表中保存了许多额外的信息，包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容，这些数据统称为元数据（元信息），这些元数据的集合（系统表）就成为数据字典。
在 MySQL8.0 以前，数据字典是以文件的形式存储的，在 MySQL8.0 以后，全都存放到数据库系统表中了。

最后

本文介绍了 MySQL 底层的逻辑存储结构，每一条数据都是一个数据行，数据行是存储数据的基本单位。若干条数据会组成一个数据页，数据页是读写的基本单位，可以减少磁盘 IO 的次数。若干个连续的页会组成一个区，可以降低随机 IO 的次数，来提高查询效率，同时也便于进行管理。不同的区可以按照逻辑分布到不同的段中，可以更好地进行数据管理以及提高空间利用率。
下一节将介绍 MySQL 的逻辑架构和 SQL 语句的具体执行流程。