乐趣区

重新学习Mysql数据库3Mysql存储引擎与数据存储原理

jiezi

转自：https://draveness.me/mysql-in…

作为一名开发人员，在日常的工作中会难以避免地接触到数据库，无论是基于文件的 sqlite 还是工程上使用非常广泛的 MySQL、PostgreSQL，但是一直以来也没有对数据库有一个非常清晰并且成体系的认知，所以最近两个月的时间看了几本数据库相关的书籍并且阅读了 MySQL 的官方文档，希望对各位了解数据库的、不了解数据库的有所帮助。

本文中对于数据库的介绍以及研究都是在 MySQL 上进行的，如果涉及到了其他数据库的内容或者实现会在文中单独指出。

[](https://draveness.me/mysql-in…

很多开发者在最开始时其实都对数据库有一个比较模糊的认识，觉得数据库就是一堆数据的集合，但是实际却比这复杂的多，数据库领域中有两个词非常容易混淆，也就是_数据库_和_实例_：

  • 数据库：物理操作文件系统或其他形式文件类型的集合；
  • 实例：MySQL 数据库由后台线程以及一个共享内存区组成；

对于数据库和实例的定义都来自于 MySQL 技术内幕：InnoDB 存储引擎 一书，想要了解 InnoDB 存储引擎的读者可以阅读这本书籍。

[](https://draveness.me/mysql-in…

在 MySQL 中，实例和数据库往往都是一一对应的，而我们也无法直接操作数据库，而是要通过数据库实例来操作数据库文件，可以理解为数据库实例是数据库为上层提供的一个专门用于操作的接口。

在 Unix 上，启动一个 MySQL 实例往往会产生两个进程，mysqld 就是真正的数据库服务守护进程，而 mysqld_safe 是一个用于检查和设置 mysqld 启动的控制程序，它负责监控 MySQL 进程的执行，当 mysqld 发生错误时，mysqld_safe 会对其状态进行检查并在合适的条件下重启。

[](https://draveness.me/mysql-in… 的架构

MySQL 从第一个版本发布到现在已经有了 20 多年的历史，在这么多年的发展和演变中，整个应用的体系结构变得越来越复杂：

最上层用于连接、线程处理的部分并不是 MySQL『发明』的，很多服务都有类似的组成部分；第二层中包含了大多数 MySQL 的核心服务，包括了对 SQL 的解析、分析、优化和缓存等功能，存储过程、触发器和视图都是在这里实现的；而第三层就是 MySQL 中真正负责数据的存储和提取的存储引擎，例如：InnoDB、MyISAM 等，文中对存储引擎的介绍都是对 InnoDB 实现的分析。

[](https://draveness.me/mysql-in…

在整个数据库体系结构中，我们可以使用不同的存储引擎来存储数据，而绝大多数存储引擎都以二进制的形式存储数据；这一节会介绍 InnoDB 中对数据是如何存储的。

在 InnoDB 存储引擎中，所有的数据都被逻辑地存放在表空间中，表空间（tablespace）是存储引擎中最高的存储逻辑单位，在表空间的下面又包括段（segment）、区（extent）、页（page）：

同一个数据库实例的所有表空间都有相同的页大小；默认情况下，表空间中的页大小都为 16KB，当然也可以通过改变 innodb_page_size 选项对默认大小进行修改，需要注意的是不同的页大小最终也会导致区大小的不同：

从图中可以看出，在 InnoDB 存储引擎中，一个区的大小最小为 1MB，页的数量最少为 64 个。

[](https://draveness.me/mysql-in…

MySQL 使用 InnoDB 存储表时，会将表的定义和数据索引等信息分开存储，其中前者存储在 .frm 文件中，后者存储在 .ibd 文件中，这一节就会对这两种不同的文件分别进行介绍。

[](https://draveness.me/mysql-in… 文件

无论在 MySQL 中选择了哪个存储引擎，所有的 MySQL 表都会在硬盘上创建一个 .frm 文件用来描述表的格式或者说定义；.frm 文件的格式在不同的平台上都是相同的。

<pre>CREATE TABLE test_frm(column1 CHAR(5),    column2 INTEGER);
</pre>

当我们使用上面的代码创建表时，会在磁盘上的 datadir 文件夹中生成一个 test_frm.frm 的文件，这个文件中就包含了表结构相关的信息：

MySQL 官方文档中的 11.1 MySQL .frm File Format 一文对于 .frm文件格式中的二进制的内容有着非常详细的表述，在这里就不展开介绍了。

[](https://draveness.me/mysql-in… 文件

InnoDB 中用于存储数据的文件总共有两个部分，一是系统表空间文件，包括 ibdata1、ibdata2 等文件，其中存储了 InnoDB 系统信息和用户数据库表数据和索引，是所有表公用的。

当打开 innodb_file_per_table 选项时，.ibd 文件就是每一个表独有的表空间，文件存储了当前表的数据和相关的索引数据。

[](https://draveness.me/mysql-in…

与现有的大多数存储引擎一样，InnoDB 使用页作为磁盘管理的最小单位；数据在 InnoDB 存储引擎中都是按行存储的，每个 16KB 大小的页中可以存放 2-200 行的记录。

当 InnoDB 存储数据时，它可以使用不同的行格式进行存储；MySQL 5.7 版本支持以下格式的行存储方式：

Antelope 是 InnoDB 最开始支持的文件格式，它包含两种行格式 Compact 和 Redundant，它最开始并没有名字；Antelope 的名字是在新的文件格式 Barracuda 出现后才起的，Barracuda 的出现引入了两种新的行格式 Compressed 和 Dynamic；InnoDB 对于文件格式都会向前兼容，而官方文档中也对之后会出现的新文件格式预先定义好了名字：Cheetah、Dragon、Elk 等等。

两种行记录格式 Compact 和 Redundant 在磁盘上按照以下方式存储：

Compact 和 Redundant 格式最大的不同就是记录格式的第一个部分；在 Compact 中，行记录的第一部分倒序存放了一行数据中列的长度（Length），而 Redundant 中存的是每一列的偏移量（Offset），从总体上上看，Compact 行记录格式相比 Redundant 格式能够减少 20% 的存储空间。

[](https://draveness.me/mysql-in…

当 InnoDB 使用 Compact 或者 Redundant 格式存储极长的 VARCHAR 或者 BLOB 这类大对象时，我们并不会直接将所有的内容都存放在数据页节点中，而是将行数据中的前 768 个字节存储在数据页中，后面会通过偏移量指向溢出页。

但是当我们使用新的行记录格式 Compressed 或者 Dynamic 时都只会在行记录中保存 20 个字节的指针，实际的数据都会存放在溢出页面中。

当然在实际存储中，可能会对不同长度的 TEXT 和 BLOB 列进行优化，不过这就不是本文关注的重点了。

想要了解更多与 InnoDB 存储引擎中记录的数据格式的相关信息，可以阅读 InnoDB Record Structure

[](https://draveness.me/mysql-in…

页是 InnoDB 存储引擎管理数据的最小磁盘单位，而 B-Tree 节点就是实际存放表中数据的页面，我们在这里将要介绍页是如何组织和存储记录的；首先，一个 InnoDB 页有以下七个部分：

每一个页中包含了两对 header/trailer：内部的 Page Header/Page Directory 关心的是页的状态信息，而 Fil Header/Fil Trailer 关心的是记录页的头信息。

在页的头部和尾部之间就是用户记录和空闲空间了，每一个数据页中都包含 Infimum 和 Supremum 这两个虚拟的记录（可以理解为占位符），Infimum 记录是比该页中任何主键值都要小的值，Supremum 是该页中的最大值：

User Records 就是整个页面中真正用于存放行记录的部分，而 Free Space 就是空余空间了，它是一个链表的数据结构，为了保证插入和删除的效率，整个页面并不会按照主键顺序对所有记录进行排序，它会自动从左侧向右寻找空白节点进行插入，行记录在物理存储上并不是按照顺序的，它们之间的顺序是由 next_record 这一指针控制的。

B+ 树在查找对应的记录时，并不会直接从树中找出对应的行记录，它只能获取记录所在的页，将整个页加载到内存中，再通过 Page Directory 中存储的稀疏索引和 n_owned、next_record 属性取出对应的记录，不过因为这一操作是在内存中进行的，所以通常会忽略这部分查找的耗时。

InnoDB 存储引擎中对数据的存储是一个非常复杂的话题，这一节中也只是对表、行记录以及页面的存储进行一定的分析和介绍，虽然作者相信这部分知识对于大部分开发者已经足够了，但是想要真正消化这部分内容还需要很多的努力和实践。下文是详细分析。

innodb 数据存储详细分析

本文主要介绍 InnoDB 存储引擎的 逻辑存储结构

[](http://zhongmingmao.me/2017/0… “ 逻辑存储结构 ”)逻辑存储结构

[](http://zhongmingmao.me/2017/0… “Tablespace”)Tablespace

  1. Tablespace 是 InnoDB 存储引擎逻辑存储结构的 最高层 ， 所有数据 都存放在 Tablespace 中

  2. 分类

    • System Tablespace
    • Separate Tablespace
    • General Tablespace

[](http://zhongmingmao.me/2017/0… “System Tablespace”)System Tablespace

  1. System Tablespace即我们常见的 共享表空间 ，变量为innodb_data_file_path，一般为ibdata1 文件
  2. 里面存放着 undo logs，change buffer，doublewrite buffer 等信息（后续将详细介绍），在没有开启 file-per-table 的情况下，还会包含 所有表的索引和数据 信息

  3. 没有开启 file-per-table 时存在的问题

    • 所有的表和索引都会在 System Tablespace 中，占用空间会越来越大
    • 碎片越来越多 （如truncate table 时，占用的磁盘空间依旧保留在System Tablespace）
<pre>12345678910111213141516171819</pre> <pre>mysql>  SHOW VARIABLES LIKE ‘innodb_data_file_path’;+———————–+————————+  Variable_name           Value                   +———————–+————————+  innodb_data_file_path   ibdata1:12M:autoextend  +———————–+————————+1 row in set (0.01 sec)mysql>  SHOW VARIABLES LIKE ‘%datadir%’;+—————+—————–+  Variable_name   Value            +—————+—————–+  datadir         /var/lib/mysql/  +—————+—————–+1 row in set (0.01 sec)mysql> system sudo ls -lh /var/lib/mysql/ibdata1[sudo] password for zhongmingmao:-rw-r—– 1 mysql mysql 76M May  6 20:00 /var/lib/mysql/ibdata1</pre>

[](http://zhongmingmao.me/2017/0… “Separate Tablespace”)Separate Tablespace

  1. MySQL 参考手册中并没有 Separate Tablespace 这个术语，这里只为了行文方便，表示在开启 file-per-table 的情况下，每个表有自己 独立的表空间，变量为innodb_file_per_table
  2. 里面存放在 每个表的索引和数据信息，后缀一般为.ibd
  3. 默认初始大小为96KB

  4. 好处

    • 避免 System Tablespace 越来越大
    • 减少碎片（truncate table，操作系统会 自动回收空间）
<pre>123456789101112131415161718192021222324252627282930313233</pre> <pre>mysql> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+—————-+  Tables_in_test  +—————-+  t               +—————-+1 row in set (0.00 sec)mysql>  SHOW VARIABLES LIKE ‘innodb_file_per_table’;+———————–+——-+  Variable_name           Value  +———————–+——-+  innodb_file_per_table   ON     +———————–+——-+1 row in set (0.00 sec)mysql>  SHOW VARIABLES LIKE ‘%datadir%’;                                                                                               +—————+—————–+  Variable_name   Value            +—————+—————–+  datadir         /var/lib/mysql/  +—————+—————–+1 row in set (0.01 sec)mysql> system sudo ls -lh /var/lib/mysql/testtotal 112K-rw-r—– 1 mysql mysql   61 Apr 28 10:18 db.opt-rw-r—– 1 mysql mysql 8.4K May  7 17:03 t.frm-rw-r—– 1 mysql mysql  96K May  7 17:03 t.ibd</pre>

[](http://zhongmingmao.me/2017/0… “General Tablespace”)General Tablespace

  1. General Tablespace是 MySQL 5.7.6 引入的新特性，具体内容请参照下面链接
    15.7.9 InnoDB General Tablespaces

[](http://zhongmingmao.me/2017/0… “Segment”)Segment

  1. Segment 分为三种

    1. Leaf node segment：数据段，B+Tree 的叶子节点
    2. Non-Leaf node segment：索引段，B+Tree 的非叶子节点
    3. Rollback segment：回滚段，存放undo log，默认是位于System Tablespace
  2. InnoDB 中的 B+Tree 索引，由Leaf node segment 和Non-Leaf node segment组成
  3. 一个 Segment 由 多个 Extent 和 Page组成

[](http://zhongmingmao.me/2017/0… “Extent”)Extent

  1. Extent是由连续页（默认页大小为 16KB）组成，在 默认页大小 时，为64 个连续页，大小为64*16KB=1MB

    • 不同页大小：4KB*256 or 8KB*128 or 16KB*64 or 32KB*64 or 64KB*64
  2. 为了保证 页的连续性，InnoDB 可以一次性从磁盘申请 4 个 Extent
  3. 为了 节省磁盘空间 ，如表的数据量很小（Leaf node segment 和Non-Leaf node segment都很小）或 Rollback segment，Segment 一开始 不会直接申请 Extent，而是先用 32 个碎片页（用于 叶子节点 ）来存放数据，用完之后才继续对Extent(1MB) 的申请

Page

  1. Page是 InnoDB磁盘管理的最小单位，变量为innodb_page_size
<pre>1234567</pre> <pre>mysql>  SHOW VARIABLES LIKE ‘innodb_page_size’;+——————+——-+  Variable_name      Value  +——————+——-+  innodb_page_size   16384  +——————+——-+1 row in set (0.17 sec)</pre>

[](http://zhongmingmao.me/2017/0… “Row”)Row

  1. InnoDB 存储引擎的数据是 按行 进行存放的
  2. 行记录格式 Row_FORMAT 将在后续详细介绍

接下来是 Page 数据页详解，这是最重要的一部分。

本文主要介绍 InnoDB 存储引擎的 数据页结构

[](http://zhongmingmao.me/2017/0… “ 数据页结构 ”)数据页结构

[](http://zhongmingmao.me/2017/0… “File Header”)File Header

参考链接：Fil Header

  1. 总共 38 Bytes，记录页的 头信息
名称 大小（Bytes） 描述
FIL_PAGE_SPACE 4 该页的 checksum 值
FIL_PAGE_OFFSET 4 该页在表空间中的 页偏移量
FIL_PAGE_PREV 4 该页的上一个页
FIL_PAGE_NEXT 4 该页的下一个页
FIL_PAGE_LSN 8 该页最后被修改的 LSN
FIL_PAGE_TYPE 2 该页的类型，0x45BF 为数据页
FIL_PAGE_FILE_FLUSH_LSN 8 独立表空间中为0
FIL_PAGE_ARCH_LOG_NO 4 该页属于哪一个表空间

[](http://zhongmingmao.me/2017/0… “Page Header”)Page Header

参考链接：Page Header

  1. 总共 56 Bytes，记录页的 状态信息
名称 大小（Bytes） 描述
PAGE_N_DIR_SLOTS 2 在 Page Directory 中Slot的数量，初始值为2
PAGE_HEAP_TOP 2 堆中第一个记录的指针
PAGE_N_HEAP 2 堆中的记录数，初始值为2
PAGE_FREE 2 指向 可重用空间 的首指针
PAGE_GARBAGE 2 已标记为删除（deleted_flag）的记录的字节数
PAGE_LAST_INSERT 2 最后插入记录的位置
PAGE_DIRECTION 2 最后插入的方向，PAGE_LEFT(0x01)，PAGE_RIGHT(0x02)，PAGE_NO_DIRECTION(0x05)
PAGE_N_DIRECTION 2 一个方向上连续插入记录的数量
PAGE_N_RECS 2 该页中记录（User Record）的数量
PAGE_MAX_TRX_ID 8 修改该页的最大事务 ID（仅在 辅助索引 中定义）
PAGE_LEVEL 2 该页在索引树中位置，0000 代表叶子节点
PAGE_INDEX_ID 8 索引 ID，表示 该页属于哪个索引
PAGE_BTR_SEG_LEAF 10 B+Tree 叶子节点所在 Leaf Node Segment 的 Segment Header（无关紧要）
PAGE_BTR_SEG_TOP 10 B+Tree 非叶子节点所在 Non-Leaf Node Segment 的 Segment Header（无关紧要）

[](http://zhongmingmao.me/2017/0… “Infimum + Supremum Records”)Infimum + Supremum Records

参考链接：The Infimum and Supremum Records

  1. 每个数据页中都有两个 虚拟的行记录，用来限定记录（User Record）的边界（Infimum 为下界，Supremum 为上界）
  2. Infimum和 Supremum 在页被创建 是自动创建，不会被删除
  3. 在 Compact 和Redundant行记录格式下，Infimum和 Supremum 占用的 字节数是不一样 的

[](http://zhongmingmao.me/2017/0… “User Records”)User Records

参考链接：User Records

  1. 存储 实际插入的行记录

  2. 在 Page Header 中PAGE_HEAP_TOP、PAGE_N_HEAP的HEAP，实际上指的是Unordered User Record List

    • InnoDB 不想每次都 依据 B +Tree 键的顺序 来插入新行 ，因为这可能需要 移动大量的数据
    • 因此 InnoDB 插入新行时，通常是插入到当前行的后面（Free Space 的顶部 ）或者是 已删除行留下来的空间
  3. 为了保证访问 B +Tree 记录的 顺序性 ，在每个记录中都有一个指向 下一条记录的指针 ，以此构成了一条 单向有序链表

[](http://zhongmingmao.me/2017/0… “Free Space”)Free Space

  1. 空闲空间，数据结构是 链表 ，在一个记录 被删除 后，该空间会被加入到空闲链表中

[](http://zhongmingmao.me/2017/0… “Page Directory”)Page Directory

参考链接：Page Directory

  1. 存放着 行记录 （User Record）的 相对位置（不是偏移量）
  2. 这里的 行记录指针称 为Slot或 Directory Slot，每个Slot 占用2Byte
  3. 并不是每一个行记录都有一个 Slot，一个 Slot 中可能包含多条行记录，通过行记录中 n_owned 字段标识
  4. Infimum的 n_owned 总是 1，Supremum 的 n_owned 为 [1,8]，User Record 的 n_owned 为[4,8]
  5. Slot是按照 索引键值的顺序 进行 逆序 存放（Infimum 是下界，Supremum 是上界 ），可以利用 二分查找 快速地定位一个 粗略的结果 ，然后再通过next_record 进行 精确查找

  6. B+Tree 索引 本身并 不能直接找到具体的一行记录 ，只能找到该 行记录所在的页

    • 数据库把页载入到 内存 中，然后通过 Page Directory 再进行 二分查找
    • 二分查找时间复杂度很低，又在内存中进行查找，这部分的时间基本开销可以忽略

[](http://zhongmingmao.me/2017/0… “File Trailer”)File Trailer

参考链接：Fil Trailer

  1. 总共 8 Bytes，为了 检测页是否已经完整地写入磁盘
  2. 变量 innodb_checksums，InnoDB 从磁盘读取一个页 时是否会 检测页的完整性
  3. 变量 innodb_checksum_algorithm， 检验和算法

微信公众号【黄小斜】作者是蚂蚁金服 JAVA 工程师，专注于 JAVA 后端技术栈：SpringBoot、SSM 全家桶、MySQL、分布式、中间件、微服务，同时也懂点投资理财，坚持学习和写作，相信终身学习的力量！关注公众号后回复”架构师“即可领取 Java 基础、进阶、项目和架构师等免费学习资料，更有数据库、分布式、微服务等热门技术学习视频，内容丰富，兼顾原理和实践，另外也将赠送作者原创的 Java 学习指南、Java 程序员面试指南等干货资源

退出移动版