一个懒散的程序猿

这部分主要跟着大佬的脚步“MySQL 是怎样运行的：从根儿上理解 MySQL”，学习一下InnoDB、MyISAM 这样的存储引擎是如何把表存储在文件系统上的，也就是如何存储到磁盘上的。

MySQL数据目录

数据目录位置

查看方式

目录结构

数据库在文件系统中的表示

每创建一个数据库

• 在 数据目录 下创建一个和数据库名同名的子目录(或者说是文件夹)
• 在该与数据库名同名的字幕侠创建一个名为 db.opt 的文件， 这个文件中包含该数据的各种属性， 比如字符集和比较规则。

每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹

InnoDB表在文件系统中的表示

每创建一张表

• 表结构的定义 ： 在对应的数据子目录下创建一个专门用于描述的表结构文件， 名为 表名.frm

• 表中的数据

  • 系统表空间（system tablespace）
    系统表空间可以对应文件系统上一个或多个实际的文件，默认情况下，InnoDB 会在数据目录下创建一个名为 ibdata1的自扩展文件。
  • 独立表空间(file-per-table tablespace)
    为每一个表建立一个独立表空间，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，名为 表名.ibd
  • 其他类型的表空间
    如通用表空间（general tablespace）、undo表空间（undo tablespace）、临时表空间（temporary tablespace）等等
    

  我们可以指定使用 系统表空间 还是 独立表空间 来存储数据，这个功能由启动参数 innodb_file_per_table. 0为系统表空间， 1为独立表空间

MyISAM表在文件系统中的表示

每创建一张表, 会在对应的数据库子目录下创建三个文件

表名.frm      //表的结构文件
表名.MYD      //表的数据文件
表名.MYI      //表的索引文件

其他文件

• 服务器进程文件
  每运行一个MySQL服务器程序，会把自己的进程ID写入到一个文件中
• 服务器日志文件
• 默认生成的SSL和RSA证书和密钥文件

文件系统对数据库的影响

• 数据库名称和表名称不得超过文件系统所允许的最大长度。
• 特殊字符的问题
  MySQL会把数据库名和表名中所有除数字和拉丁字母以外的所有字符在文件名里都映射成 @+编码值 的形式作为文件名。
• 文件长度受文件系统最大长度限制

MySQL 系统数据库简介

• mysql
  存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
• information_schema
  维护的所有其他数据库的信息，比如有表、视图、触发器、列、索引等信息
• performance_schema
  运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。
• sys
  通过视图的形式把 information_schema 和 performance_schema 结合起来，让程序员可以更方便的了解MySQL服务器的一些性能信息。

表空间可以理解成存放许多 页 的地方, 而之前介绍过 页 分为各种类型,但是他们都有通用的部分

• File Header：记录页面的一些通用信息
  • FIL_PAGE_OFFSET(页号) 是由4个字节组成, 也就是最多有2³²个页, 1个页16KB, 最多支持64TB
  • FIL_PAGE_INDEX(索引类型)的页, 页之前使用双链表的形式串起来的, 所以可以根据 FIL_PAGE_PREV 和FIL_PAGE_NEXT 来存储上一个页和下一个页的页号, 其他类型的页不使用这2个字段.
• File Trailer：校验页是否完整，保证从内存到磁盘刷新时内容的一致性。

独立表空间

所有设计的初衷: 提高向表插入数据的效率又不至于数据量少的表浪费空间

1个 页 16KB, 连续64个 页 合为一个 区(extent), 默认占用1MB, 每256个 区 划分成一个 组, 每个组的最开始的几个页面类型是固定的.

区 的引入是为了当存在大量的数据时, 如果只是单纯以 页 为单位分配控件, 虽然是双链表, 但是相邻2个 页 可能物理地址很远扫描数据, 这就是所谓的 随机I/O. 所以分配空间的时候, 以 区 为单位分配, 尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的 顺序I/O.

因为B+树的 页 是分为2种类型 叶子节点和非叶子节点, 所以引入 段(segment) 的概念, 段 是以 区为单位申请存储控件, 存放叶子节点的区是一个 段, 存放非叶子节点的区也是一个 段.
但是当数据很少的时候, 对于一个索引而言, 需要生成2个段, 也就是2M空间, 所以又引入 碎片区 的概念, 碎片区只属于表空间, 并不属于任何一个段.

某个段分配存储空间的策略是这样的：

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

区的分类

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

为了管理区, 设计了XDES Entry的结构（全称就是Extent Descriptor Entry), 每个区都有的结构一共40个字节

名称	占用字节	描述
Segment ID	8个字节	表示就是该区所在的段
List Node	12个字节	将若干个XDES Entry结构串联成一个链
state	4个字节	这个字段表明区的状态
Page State Bitmap	16个字节	16个字节，也就是128个比特位, 默认有64个页, 每个部分2个比特位，对应区中的一个页。这两个比特位的第一个位表示对应的页是否是空闲的，第二个比特位还没有用。

其中 List Node 的结构如下

名称	占用字节	描述
Pre Node Page Number	4个字节
Pre Node Offset	2个字节
Next Node Page Number	4个字节
Next Node Offset	2个字节

如果我们想定位表空间内的某一个位置的话，只需指定页号以及该位置在指定页号中的页内偏移量

• Pre Node Page Number和Pre Node Offset的组合就是指向前一个XDES Entry的指针
• Next Node Page Number和Next Node Offset的组合就是指向后一个XDES Entry的指针。

XDES Entry链表

直属于表空间的链表分为三种

• 把状态为 FREE 的区对应的XDES Entry结构通过List Node来连接成一个链表，这个链表我们就称之为 FREE链表。
• 把状态为 FREE_FRAG 的区对应的XDES Entry结构通过List Node来连接成一个链表，这个链表我们就称之为 FREE_FRAG链表。
• 把状态为 FULL_FRAG 的区对应的XDES Entry结构通过List Node来连接成一个链表，这个链表我们就称之为 FULL_FRAG链表。

每个段中的区对应的XDES Entry结构建立的链表也分为三种：

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

总结: 对于1张表而言, 每1个索引都对应2个段, 每个段维护3个链表, 另外还有直属于表空间的3个链表

链表基节点

为了找到链表, 为链表设计一种 List Base Node (16个字节) 结构, 放置在表空间中固定的位置，方便定位某个链表

名称	占用字节	描述
List Length	4个字节	表明该链表一共有多少节点
First Node Page Number	4个字节	和First Node Offset一起表明该链表的头节点在表空间中的位置。
First Node Offset	2个字节
Last Node Page Number	4个字节	和Last Node Offset一起表明该链表的尾节点在表空间中的位置
Last Node Offset	2个字节

段的结构

段是一些零散页面和一些完整的区的集合, 每个段都定义了一个 INODE Entry 结构来记录一下段中的属性

名称	描述
Segment ID	对应的段的编号
NOT_FULL_N_USED	在NOT_FULL链表中已经使用了多少个页面
3个List Base Node	指定的链表基节点
Magic Number	标记这个INODE Entry是否已经被初始化了, 如果这个数字是值的97937874，表明该INODE Entry已经初始化
Fragment Array Entry	结构一共4个字节，表示一个零散页面的页号

从组的层面观察

第一个组的第一个页 (FSP_HDR 16KB)

第一个组的第一个页面(表空间的第一个页面，页号为0), 主要存储了表空间的一些整体属性以及第一个组内256个区的对应的XDES Entry结构

名称	中文名	占用空间大小	简单描述
File Header	文件头部	38字节	页的一些通用信息
File Space	Header	表空间头部	112字节
XDES Entry	区描述信息	10240字节	存储本组256个区对应的属性信息
Empty Space	尚未使用空间	5986字节	用于页结构的填充，没啥实际意义
File Trailer	文件尾部	8字节	校验页是否完整

名称	占用空间大小	描述
Space ID	4字节	表空间的ID
Not Used	4字节	这4个字节未被使用，可以忽略
Size	4字节	当前表空间占有的页面数
FREE Limit	4字节	尚未被初始化的最小页号，大于或等于这个页号的区对应的XDES Entry结构都没有被加入FREE链表
Space Flags	4字节	表空间的一些占用存储空间比较小的属性
FRAG_N_USED	4字节	FREE_FRAG链表中已使用的页面数量
List Base Node for FREE List	16字节	FREE链表的基节点
List Base Node for FREE_FRAG List	16字节	FREE_FREG链表的基节点
List Base Node for FULL_FRAG List	16字节	FULL_FREG链表的基节点
Next Unused Segment ID	8字节	当前表空间中下一个未使用的 Segment ID
List Base Node for SEG_INODES_FULL List	16字节	SEG_INODES_FULL链表的基节点
List Base Node for SEG_INODES_FREE List	16字节	SEG_INODES_FREE链表的基节点

第二个组的第一个页 (XDES 16KB)

简单总结一下和第一个组第一个页的区别 : 除了少了 File Space Header 部分之外，也就是除了少了记录表空间整体属性的部分之外，其余的部分是一样一样的

第一个组的第二个页 (IBUF_BITMAP 16KB)

太复杂

第一个组的第三个页 (INODE类型 16KB)

这个页主要管理索引的段信息， 前面提到段的结构 INODE Entry， 这一部分主要就是存储 INODE Entry 结构

名称	中文名	占用空间大小	简单描述
File Header	文件头部	38字节	页的一些通用信息
List Node for INODE Page List	通用链表节点	12字节	存储上一个INODE页面和下一个INODE页面的指针
INODE Entry	段描述信息	16320字节	一个页面里可以存储85个这样的结构
Empty Space	尚未使用空间	6字节	用于页结构的填充，没啥实际意义
File Trailer	文件尾部	8字节	校验页是否完整

由于这个页最多只能存储85个段信息， 超过之后， 为了方便管理， 将所有的 INODE 类型的页串联成2个不同的链表

• SEG_INODES_FULL链表：该链表中的INODE类型的页面中已经没有空闲空间来存储额外的INODE Entry结构了。
• SEG_INODES_FREE链表：该链表中的INODE类型的页面中还有空闲空间来存储额外的INODE Entry结构了。

而第一个组的第一个页中保存链表的基节点信息.

既然保存了段的信息, 现在需要知道哪些页归属于那个段, 所以在 页面头部 中存在2个字段 PAGE_BTR_SEG_LEAF 和 PAGE_BTR_SEG_TOP , 都是10个字节, 具体结构内容如下 (这个结构又称为 Segment Header 结构)

名称	占用字节数	描述
Space ID of the INODE Entry	4	INODE Entry结构所在的表空间ID
Page Number of the INODE Entry	4	INODE Entry结构所在的页面页号
Byte Offset of the INODE Ent	2	INODE Entry结构在该页面中的偏移量

系统表空间

这是小册微信交流群里的一个大佬整理的一个系统表的全局图

相比于独立表表空间, 第一个组的3~7的页面时系统表空间独有的. 另外系统表空间的extent 1和extent 2这两个区，也就是页号从64~191这128个页面被称为Doublewrite buffer，也就是双写缓冲区

页号	页面类型	英文描述	描述
3	SYS	Insert Buffer Header	存储Insert Buffer的头部信息
4	INDEX	Insert Buffer Root	存储Insert Buffer的根页面
5	TRX_SYS	Transction System	事务系统的相关信息
6	SYS	First Rollback Segment	第一个回滚段的页面
7	SYS	Data Dictionary Header	数据字典头部信息

MySQL除了保存用户的数据外, 为了更好的管理我们这些用户数据而不得已引入的一些额外数据，这些数据也称为 元数据

• 表相关信息 列的类型, 索引, 引擎啊,排序方式等等
• 表与表之间的关系, 外键
• 等等等

InnoDB存储引擎特意定义了一些列的内部系统表（internal system table）来记录这些这些元数据, 这些系统表也被称为数据字典，它们都是以B+树的形式保存在系统表空间的某些页面中

表名	描述
SYS_TABLES	整个InnoDB存储引擎中所有的表的信息
SYS_COLUMNS	整个InnoDB存储引擎中所有的列的信息
SYS_INDEXES	整个InnoDB存储引擎中所有的索引的信息
SYS_FIELDS	整个InnoDB存储引擎中所有的索引对应的列的信息
SYS_FOREIGN	整个InnoDB存储引擎中所有的外键的信息
SYS_FOREIGN_COLS	整个InnoDB存储引擎中所有的外键对应列的信息
SYS_TABLESPACES	整个InnoDB存储引擎中所有的表空间信息
SYS_DATAFILES	整个InnoDB存储引擎中所有的表空间对应文件系统的文件路径信息
SYS_VIRTUAL	整个InnoDB存储引擎中所有的虚拟生成列的信息

这些表的详细介绍, 大家去看原文, 我不想总结了(抄了)

因为这些表都是InnoDB 内部系统表, 不能直接访问, MySQL在 information_schema 系统数据库中提供了一些以 innodb_sys开头的表, 这些以 INNODB_SYS 开头的表并不是真正的内部系统表,而是在存储引擎启动时读取这些以 SYS 开头的系统表，然后填充到这些以 INNODB_SYS 开头的表中,以 INNODB_SYS 开头的表和以 SYS 开头的表中的字段并不完全一样.