# MySQL的数据目录 ## 数据库和文件系统 存储引擎把数据存储在文件系统里，本章讲的是数据库和文件系统的关系。 ## MySQL 数据目录 系统变量`datadir`存储着目录位置。 ``` mysql> SHOW VARIABLES LIKE 'datadir'; +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | datadir | /usr/local/var/mysql/ | +---------------+-----------------------+ 1 row in set (0.00 sec) ``` ## 数据目录的结构 ### InnoDB #### 数据库 `CRESTE DATABASE 数据库名`创建数据库时，会在文件系统创建一个同名目录，同时会在这个目录下创建一个`db.opt`文件保存各类属性，包括字符集、比较规则等。 #### 表 创建表时，`InnoDB`和`MyISAM`都会在数据库目录下创建一个`表名.frm`文件用于描述表结构。 `MySQL5.6.6`之前的版本，数据会存储在一个会自增的`系统表空间`文件里，之后的版本则存在`独立表空间`——`表名.idb`里。这个过程可以通过设置`innodb_file_per_table`变量修改，但只对新增的表起作用。 把系统表空间的表转移到独立空间： ``` ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table; ``` 把独立空间的表转移到系统空间： ``` ALTER TABLE 表名 TABLESPACE [=] innodb_system; ``` ### MyISAM `MyISAM`没有表空间概念，都会放在数据库目录下。一张表会有三个文件： - `表名.frm` - `表名.MYD`：记录数据 - `表名.MYI`：索引文件 ### 视图 视图其实是虚拟的表，就是个查询语句的别名而已。所以存储的时候不存储真实数据，只需要把存储结构存下来即可，因此只会在视图目录下存一个`视图名.frm` ### 其他 - 服务器进程：存储 PID - 日志文件：存储各类日志。 - SSL/RSA证书、秘钥等文件 ## 文件系统对数据库的影响 - 数据库名、表名不能长过文件系统限制。 - 特殊字符会被映射成`@+编码`值。 - 文件长度不能大于文件系统限制。 ## MySQL 系统数据库简介 - `mysql`：用户账户、权限、各类定义等。 - `information_schema`：描述性元数据，比如有哪些表，哪些视图，哪些触发器。 - `performance_schema`：运行状态、性能监控，最近执行了哪些语句、花了多久等。 - `sys`：通过视图的形式把`information_schema`和`performance_schema`结合起来，能更方便的了解 MySQL 的性能信息。 # InnoDB 的表空间 ## 独立表空间结构 ### 区（extent） 由于页实在是太多了，提出了区的概念。每个区包含`64个页`，大小为`1MB`。每`256个区`组成一组。  从上图中可以得知： - 第一个块的前三个页是固定的，分别是： - `FSP_HDR`页：用来登记整个表空间的整体属性，以及本组的所有区。整个表空间只有一个`FSP_HDR`页。 - `IBUF_BITMAP`页：储存表的所有`INSERT BUFFER`信息，后说。 - `INODE`页：存储了许多`INODE`结构，后说。 - 各组的前两个页面也是固定的，分别是： - `XDES`页：全称是`extent descriptor`，用来登记本组的区的属性。其实和`FSP_HDR`的工作类似，但`FSP_HDR`还会存一些其他表空间属性。 - `IBUF_BITMAP`页：见上面。 区的概念实际上是就是磁盘空间上连续的64个页的集合，分配磁盘空间时按照区来分配，在数据很大的时候一次性分配多个区，让磁盘空间尽量挨着，虽然会浪费一些空间，但是能减少随机I/O。 ### 段（segment） 在全表扫描时，其实是对B+树的叶子节点双向链表顺序扫描。如果B+树不把叶子节点和非叶子节点分开，全表扫描就有可能会带来随机I/O，所以 InnoDB 将叶子节点和非叶子节点区别对待了。也就是叶子节点有叶子节点的区，非叶子节点有非叶子节点的区。 叶子节点的区的集合就是叶子节点段，非叶子节点的区的集合就是非叶子节点的段。默认情况下，InnoDB 的表只有一个聚簇索引，一个索引会生成两个段。 但对于数据量过于小的区，一个区专门用于存放它们显得过于浪费，于是提出了`碎片（fragment）区`的概念。碎片区里的数据是可以服务于不同的段的。插入策略是： - 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。 - 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间。 所以段有两类： - 一些零散页面的集合 - 一些完整的区的集合 段的结构会在下面讲。 ### 区的分类 存在表里的区的状态大概可以分为： - `FREE`：空闲的区 - `FREE_FRAG`：有剩余空间的区 - `FULL_FRAG`：没有剩余空间的区 - `FSEG`：附属于某个段的区 前三种状态的区都是独立的，算是直属于表空间，但`FSEG`的段是属于某个段的。 为了管理这些区，设计了`XDES Entry(Extent Descriptor Entry)`结构：  这是一个`40字节`的结构，存放在每张表的第一个区的第一个页，即`XDES FSP_HDR`页里： - `Segment ID`：每个段都有的唯一编号。 - `List Node`：这部分结构如下图，可以把`XDES Entry`串联成链表。  - `State`：标明是出于上面提到的四种状态的哪一种。 - `Page State Bitmap`：一共16字节，也就是128bit。一个区默认有64个区，这128个bit被分为64个部分，每2个bit对应一个页。第一个bit表示是否空闲，第二个bit还没用。 ### XDES Entry 链表 插入数据时： - 先寻找有没有`FREE_FRAG`的区，如果有就把数据放进去，如果没有就到下一个区申请`FREE`状态的区。寻找的过程会通过以下三个链表来寻找，存完数据之后就改变区的状态然后从一个链表删除，挂到另一个链表上。 - `FREE链表`：`FREE`的区的`XDES Entry`结构通过链表连起来。 - `FREE_FRAG链表`：`FREE_FRAG `的区的`XDES Entry`结构通过链表连起来。 - `FULL_FRAG链表`：`FULL_FRAG`的区的`XDES Entry`结构通过链表连起来。 - 如果已经占满32个零散的页以后，就直接申请完整的区来插入数据。 - `FREE`链表：同一个段里，所有页面都是空的区对应的`XDES Entry`结构连起来。 - `NOT_FULL`链表：同一个段里，仍然有空闲的区对应的`XDES Entry`结构连起来。 - `FULL`链表：同一个段里，已经没有空闲空间的区对应的`XDES Entry`结构连起来。 每一个索引都有两个段，每个段都会维护上述3个链表。 ### 链表基节点 上面维护了很多链表，那么如何找到链表的头尾在表空间里的位置呢？答案是`List Base Node`。每个链表都对应了一个基节点结构，放在表空间的固定位置。  ### 段的结构 就像每个区有`XDES Entry`，每个段也有个记录属性的区域，就是`INODE Entry`。  - `Segment ID`：编号 - `NOT_FULL_N_USED`：指`NOT_FULL`链表里被使用的页的数量。 - 三个链表的基节点。 - `Magic Number`：不解释，初始化后是`97937874`。 - `Fragment Array Entry`：一个数组，每一个元素四个字节，表示零散页面的页号。 ### 各类型页的详细情况 #### `FSP_HDR`页  - `File Header`：页面通用信息，示意图补充在列表下面 - `Space ID`：表空间的 ID - `Not Used`：未使用的字段，忽略 - `Size`：当前表空间占有的页面数 - `FREE Limit`：尚未被初始化的页的最小页号。不会在一开始把整个表空间都初始化，只初始化一部分放在`FREE`链表里，大于等于这个页的均还未初始化，未加入`FREE`链表。 - `Space Flags`：一些布尔类型属性 - `FRAG_N_USED`：`FREE_FRAG`链表中已使用的页数量 - `List Base Node for FREE List`：👈 - `List Base Node for FREE_FRAG List`：👈 - `List Base Node for FULL_FRAG List`：👈 - `Next Unused Segment ID`：该表空间最大的段的下一个段的ID。 - `List Base Node for SEG_INODES_FULL List`：👈 - `List Base Node for SEG_INODES_FREE List`：👈 - `File Space Header`：表空间基本信息 - `XDES Entry`：本组的256个区的`XDES Entry`结构 - `Empty Space`：尚未使用的5986个字节 - `File Trailer`：校验完整性 头部的通用信息：  #### `XDES`页 除了第一个组开头是`XDES Entry`以外，其他组的开头都是`XDES`页。相比之下，仅仅是少了一些部分，其他都一样，看图即可。  #### `IBUF_BITMAP`页 每个组的第二个页面都是`IBUF_BITMAP`，后面再讲。 #### `INODE`页 每个段的段首都有`INODE`页。  - `File Header`：头部信息 - `List Node for INODE Page List`：段描述信息。表空间中可能存在超过84个段，所以可能一个`INODE`页不够，为了管理，InnoDB 把这些`INODE`页串联成了两个链表： - `SEG_INODES_FULL`：该链表中的`INODE`页已经没有空闲空间了。 - `SEG_INODES_FREE`：该链表中的`INODE`页还有空间。 - `INODE Entry`：前面介绍过了，包括对应的段内零散页面的地址以及附属于该段的`FREE`、`NOT_FULL`和`FULL`链表的基节点。 - `Empty Space`：未使用区域，忽略 - `File Trailer`：校验页面完整 ### Segment Header 一个索引会产生两个段，每个段都会对应一个`INODE Entry`，那么如何知道某个段对应哪个`INODE Entry`呢？这个信息保存在数据页的头里。 在数据页的结构里，有以下两个字段： - PAGE_BTR_SEG_LEAF - PAGE_BTR_SEG_TOP 这两个字段都是10字节，其实各对应一个`Segment Header`结构，分别记录叶子节点段和非叶子结点段对应哪个表空间哪个页面号多少偏移量。  - `Space ID of the INODE Entry`：`INODE Entry`所在表空间的ID - `Page Number of the INODE Entry`：`INODE Entry`所在的页面页号 - `Byte Offset of the INODE Entry`：`INODE Entry`在该页的偏移量 ## 系统表空间 系统表空间和独立表空间类似，只是整个 MySQL 进程只有一个系统表空间。在这里会记录一些有关整个系统的信息的页面。 系统表空间的 ID 是 0。 ### 整体结构  前三个页面和独立表空间一致，第4个到第8个是系统表空间独有的页。 - `Insert Buffer Header`：Insert Buffer 头部信息 - `Insert Buffer Root`：Insert Buffer 根页 - `Transction System Header`：事务系统相关信息 - `First Rollback Segment`：第一个回滚页 - `Data Dictionary Header`：数据字典头部 此外，系统表空间的`extent 1`和`extent 2`这两个区，也就是`64`〜`127`这`128个页`被称为`双写缓冲区（Doublewrite buffer）`，涉及到事物和多版本控制，后面说。 ### InnoDB 数据字典 在使用时涉及到很多元数据处理，例如在插入记录时候，要看这个表属于哪个表空间，里面有多少列，每个列类型是什么等等。这个结构用来管理元数据。 - `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存储引擎中所有的虚拟生成列的信息 这些系统表也被称为`数据字典`，也是以B+树的形式存放在系统表空间中。其中，`SYS_TABLES`、`SYS_COLUMNS`、`SYS_INDEXES`、`SYS_FIELDS`这四个表尤其重要，称之为`基本系统表（basic system tables）`。 #### SYS_TABLES - `NAME`：表的名称 - `ID`：InnoDB存储引擎中每个表都有一个唯一的ID - `N_COLS`：该表拥有列的个数 - `TYPE`：表的类型，记录了一些文件格式、行格式、压缩等信息 - `MIX_ID`：已过时，忽略 - `MIX_LEN`：表的一些额外的属性 - `CLUSTER_ID`：未使用，忽略 - `SPACE`：该表所属表空间的ID 这个表有两个索引：`NAME`和`ID`。 #### SYS_COLUMNS - `TABLE_ID`：该列所属表对应的ID - `POS`：该列在表中是第几列 - `NAME`：该列的名称 - `MTYPE`：Main Data Type，例如`INT`、`CHAR`、`VARCHAR`等 - `PRTYPE`：Precise Type，例如是否允许 NULL，是否允许负数等 - `LEN`：该列最多占用存储空间的字节数 - `PREC`：该列的精度，不过这列貌似都没有使用，默认值都是0 这个表只有一个以`（TABLE_ID, POS) `为主键的聚簇索引。 #### SYS_INDEXES - `TABLE_ID`：该索引所属表对应的 ID - `ID`：InnoDB 存储引擎中每个索引都有一个唯一的ID - `NAME`：该索引的名称 - `N_FIELDS`：该索引包含列的个数 - `TYPE`：该索引的类型，比如聚簇索引、唯一索引、更改缓冲区的索引、全文索引、普通的二级索引等 - `SPACE`：该列最多占用存储空间的字节数 - `PAGE_NO`：该列的精度，不过这列貌似都没有使用，默认值都是0 - `MERGE_THRESHOLD`：如果页面中的记录被删除到某个比例，就把该页面和相邻页面合并，这个值就是这个比例 这个表只有一个以`(TABLE_ID, ID)`为主键的聚簇索引。 #### SYS_FIELDS - `INDEX_ID`：该索引列所属的索引的ID - `POS`：该索引列在某个索引中是第几列 - `COL_NAME`：该索引列的名称 这个表只有一个以`(INDEX_ID, POS)`为主键的聚簇索引。 ### Data Dictionary Header 页 上述提到的四张表可以服务于其他表，那这四张表的元数据怎么办呢？只能硬编码在代码里了。这些内容被存在了`Data Dictionary Header`页里。  - `File Header`：页的一些通用信息 - `Data Dictionary Header`： - `Max Row ID`：无论哪个表插入记录时，这个值都会自增，可以视为全局唯一的记录 ID。 - `Max Table ID`：InnoDB 中每个表都对应一个唯一的ID，每次新建一个表时，就会把本字段的值作为该表的ID，然后自增本字段的值。 - `Max Index ID`：InnoDB 中每个索引都对应一个唯一的ID，每次新建一个索引时，就会把本字段的值作为该索引的ID，然后自增本字段的值。 - `Max Space ID`：InnoDB 中每个表空间都对应一个唯一的ID，每次新建一个表空间时，就会把本字段的值作为该表空间的ID，然后自增本字段的值。 - `Mix ID Low(Unused)`：没用，忽略 - `Root of SYS_TABLES clust index`：`SYS_TABLES`表聚簇索引的根页面的页号。 - `Root of SYS_TABLE_IDS sec index`：`SYS_TABLES`表为`ID`列建立的二级索引的根页面的页号。 - `Root of SYS_COLUMNS clust index`：`SYS_COLUMNS`表聚簇索引的根页面的页号。 - `Root of SYS_INDEXES clust： index``SYS_INDEXES`表聚簇索引的根页面的页号。 - `Root of SYS_FIELDS clust index`：`SYS_FIELDS`表聚簇索引的根页面的页号。 - `Unused`：没用，忽略 - `Segment Header`：本页面所在段对应的`INODE Entry`位置信息 - `Empty Space`：尚未使用空间，忽略 - `File Trailer`：校验页是否完整 ### information_schema 系统数据库 以上提到的内部表，都是不允许用户访问的，除非直接去解析文件。为了能让用户访问，设计了一个名为`information_schema`的数据库，内置了一些`innodb_sys`开头的表格供用户访问。 ``` mysql> USE information_schema; Database changed mysql> SHOW TABLES LIKE 'innodb_sys%'; +--------------------------------------------+ | Tables_in_information_schema (innodb_sys%) | +--------------------------------------------+ | INNODB_SYS_DATAFILES | | INNODB_SYS_VIRTUAL | | INNODB_SYS_INDEXES | | INNODB_SYS_TABLES | | INNODB_SYS_FIELDS | | INNODB_SYS_TABLESPACES | | INNODB_SYS_FOREIGN_COLS | | INNODB_SYS_COLUMNS | | INNODB_SYS_FOREIGN | | INNODB_SYS_TABLESTATS | +--------------------------------------------+ 10 rows in set (0.00 sec) ``` 这些表是数据库启动时，存储引擎写进去的。