Question

MySQL 中的数据用各种不同的技术存储在文件（或者内存) 中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

而存储引擎说白了就是 如何存储数据 、 如何为存储的数据建立索引 、 如何更新/查询数据 等技术的实现方法。

MySQL 提供了多个存储引擎，在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

常见存储引擎

• InnoDB:主要的事务存储引擎

  • InnoDB，支持事务、行级锁、自增主键、外键、自动灾难恢复。
  • MySQL5.5 之后，成为默认存储引擎。
  • 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎。
  • 数据文件结构:
    • 表名.frm 存储表结构（MySQL8.0 时，合并在表名.ibd 中）
    • 表名.ibd 存储数据和索引
  • 对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。

• MyISAM:主要的非事务存储引擎

  • MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数(GIS) 等，但 MyISAM 不支持事务、行级锁、外键。
  • 5.5 之前默认的存储引擎
  • 优势是 访问的速度快 ，适合对事务完整性没有要求或者以 SELECT、INSERT 为主的应用
  • 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
  • 数据文件结构:
    • 表名.frm 存储表结构
    • 表名.MYD 存储数据 (MYData)
    • 表名.MYI 存储索引 (MYIndex)
  • 应用场景:只读应用或者以读为主的业务

InnoDB 和 MyISAM 对比

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
自带系统表使用	是	是
关注点	性能:节省资源、消耗少、简单业务	事务:并发写、事务、更大资源
默认安装	是	是
默认使用	否	是

InnoDB 详解

参考: mysql 存储引擎 InnoDB 详解，从底层看清 InnoDB 数据结构 - 知乎 (zhihu.com)

数据库存储结构

数据是存放在磁盘上的，会先读取到内存中再由 CPU 进行操作。由于磁盘读取速度很慢，如果每次操作都只读取一行数据的话，磁盘 IO 量会非常高，导致性能很差。因此 InnoDB 将磁盘中的所有数据划分为若干个 页（page） ，以页作为磁盘与内存交互的基本单位，一般页的大小为 16KB 。 页与页之间通过双向链表进行连接 。

这主要是基于两种维度进行考量:

1. 时间维度:如果一条数据正在在被使用，那么在接下来一段时间内大概率还会再被使用。可以认为热点数据缓存都属于这种思路的实现。
2. 空间维度:如果一条数据正在在被使用，那么存储在它附近的数据大概率也会很快被使用。InnoDB 的数据页和操作系统的页缓存则是这种思路的体现。

页的上层结构

除了页之外，在数据库中还存在着区（Extent）、段（Segment）和表空间（Tablespace）的概念。

• 区（Extent）:是比页大一级的存储结构，一个区有 64 个连续的页 ，所以一个区的大小为 1MB 。
• 段（Segment）:由一个或多个区（不要求相邻）组成。 段是数据库中的分配单位 。
• 表空间（Tablespace）:由一个或多个段组成

InnoDB 中的页并非只有一种，比如有存放 Insert Buffer 的页、存放 undo log 的页、存放数据的页等等。其中我们最关注的还是存放我们表数据的页，又称 索引页 ，或者 数据页 ，以下介绍的就是 数据页 。

页的内部结构

一个页由七部分构成:

名称	占用空间	描述
File Header（文件头）	38 字节	页的通用信息（比如页的编号、其上一页、下一页是谁等）
Page Header（页头部）	56 字节	页的专有信息（比如存储了多少条数据、第一条数据的地址等）
Infimum + Supremum（最小和最大记录）	26 字节	指向页中的最小记录和最大记录的指针（比较的是主键大小）
User Records（用户记录）	不确定	存储的数据（单向链表方式存储）
Free Space（空闲空间）	不确定	页中尚未使用的空间
Page Directory（页目录）	不确定	为页中存储的数据记录目录，方便通过二分法查找某条具体数据的位置
File Trailer（文件尾）	8 字节	校验页的完整性

行格式

行格式就是页中真正保存的数据的格式。行格式分为四种: Compact 、 Redundant 、 Dynamic 、 Compressed 。行格式之间都大同小异，主要介绍 Compact 。

几种行格式的具体区别可以参考: Innodb 四种行格式对比_shuifa2008 的博客-CSDN 博客

变长字段长度列表

MySQL 支持一些变长的数据类型，比如 VARCHAR(M)、VARBINARY(M)、TEXT 类型，BLOB 类型，拥有这些数据类型的列称为 变长字段 ，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要 把这些数据实际上占用的字节数也存起来 。在 Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

注意:这里面存储的变长长度和字段顺序是反过来的。比如两个 varchar 字段在表结构的顺序是 a(10)，b(15)。那么在变长字段长度列表中存储的长度顺序就是 15，10。

NULL 值列表

Compact 行格式会 把可以为 NULL 的列存在一个 NULL 值列表中 。如果表中没有允许存储 NULL 的列，则 NULL 值列表也不存在。

例如一个表中有四个列 C1、C2、C3、C4，其中 C2、C3 允许为 NULL，则 NULL 值列表中会有两个 bit 位来 逆序 表示每条 C2、C3 是否为 NULL。

规则为：bit 位的值为 1 时，代表该列的值为 NULL；bit 位的值为 0 时，代表该列的值不为 NULL。

例如一条行记录为 1,NULL,NULL,a ，则它的 NULL 值列表就为 1 1 ；

例如一条行记录为 2,b,NULL,a ，则它的 NULL 值列表就为 1 0 ；

之所以要存储 NULL 值列表是因为数据都是需要对齐的，如果没有标注出来 NULL 值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空置的话，虽然能达到效果，但是这样很浪费空间，所以直接就在行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据，哪些是空数据。

记录头信息

记录头信息分为几个部分:

名称	大小（单位:bit）	描述
预留位 1	1	没有使用
预留位 2	1	没有使用
delete_mask	1	标记该记录是否被删除（1 表示被删除，0 表示未删除）
mini_rec_mask	1	B+树的每层非叶子节点中的（主键）最小记录都会添加该标记（值为 1）
n_owned	4	页目录中每个组中最后一条记录的头信息中会存储该组有多少条记录，就是 n_owned
heap_no	13	表示当前记录在这个页的中的位置
record_type	3	表示当前记录的类型， 0 表示普通记录， 1 表示 B+树非叶子节点记录， 2 表示最小记录， 3 表示最大记录
next_record	16	表示下一条记录的相对位置

隐藏列

记录的真实数据中，除了自定义的列的数据以外，还有三个隐藏列:

列名	是否必须	占用空间	描述
DB_ROW_ID（row_id）	否	6 字节	行 ID，唯一标识一条记录
（DB_TRX_ID）transaction_id	是	6 字节	事务 ID
（DB_ROLL_PTR）roll_pointer	是	7 字节	回滚指针

一个表没有手动定义主键，则会选取一个 Unique 键作为主键，如果连 Unique 键都没有定义的话，则会为表默认添加一个名为 row_id 的隐藏列作为主键。所以 row_id 是在没有自定义主键以及 Unique 键的情况下才会存在的。

行溢出

一个页的大小一般为 16KB，但是 MySQL 中有些不定长的字段可以存储的值很大，例如 VARCHAR 最多可以存储 65533 个字节，这样的话就会导致一个页面连一条记录都存不下，这种现象就是行溢出。

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

在 MySQL8.0 中，默认行格式就是 Dynamic。Dynamic、Compressed 行格式和 Compact 行格式基本一致，只不过在处理行溢出数据时不同:

• Compressed 和 Dynamic 两种记录格式对于存放在 BLOB 中的数据采用了完全的行溢出的方式。在数据页中只存放 20 个字节的指针（溢出页的地址），实际的数据都存放在 Off Page（溢出页）中。
• Compact 和 Redundant 两种格式会在记录的真实数据处存储一部分数据（存放 768 个前缀字节）。

区、段和碎片区（了解）

为什么要有区

B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。B+树索引的使用场景中，范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了。而如果链表中相邻的两个页物理位置离得非常远，就会发生随机 I/O。随机 I/O 是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用顺序 I/O。

所以引入区的概念，一个区就是物理位置上连续的 64 个页。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页的单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足以填充满整个区），但是从性能角度看，可以消除很多的随机 I/O。

为什么要有段

对于范围查询，其实是对 B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以 InnoDB 对 B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成 2 个段，一个叶子节点段，一个非叶子节点段。

除了索引的叶子节点段和非叶子节点段之外，InnoDB 中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有数据段、索引段、回滚段。数据段即为 B+树的叶子节点，索引段即为 B+树的非叶子节点。

在 InnoDB 存储引擎中，对段的管理都是由引擎自身所完成，DBA 不能也没有必要对其进行控制。这从一定程度上简化了 DBA 对于段的管理。

段其实不对应表空间中的某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。

为什么要有碎片区

默认情况下，一个使用 InnoDB 存储引擎的表只有一个聚簇索引，一个索引会生成 2 个段，而段是以区为单位申请存储空间的，一个区默认占用 1M（64*16KB=1024KB）存储空间，所以默认情况下一个只存在几条记录的小表也需要 2M 的存储空间么？以后每次添加一个索引都要多申请 2M 的存储空间么？这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常纯粹的，也就是一个区被整个分配给某一个段，或者说区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。

为了考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况，InnoDB 提出了一个碎片（fragment）区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页面用于段 A，有些页面用于段 B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。

所以此后为某个段分配存储空间的策略是这样的:

• 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
• 当某个段已经占用了 32 个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。

所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。

区的分类

区大体上可以分为 4 种类型:

• 空闲的区(FREE):现在还没有用到这个区中的任何页面。
• 有剩余空间的碎片区(FREE_FRAG):表示碎片区中还有可用的页面。
• 没有剩余空间的碎片区(FULL_FRAG):表示碎片区中的所有页面都被使用，没有空闲页面。
• 附属于某个段的区(FSEG):每一索引都可以分为叶子节点段和非叶子节点段

处于 FREE 、 FREE_FRAG 以及 FULL_FRAG 这三种状态的区都是独立的，直属于表空间。而处于 FSEG 状态的区是附属于某个段的。