Mysql 是怎样运行的

本书是一本专业技术图书，致力于覆盖工作和面试过程中遇到的一些关于 MySql 的核心概念

初识Mysql

mysql 是由客户端程序和服务端程序两部分组成，日常使用场景如下

1. 启动 MySql 服务程序
2. 启动 MySql 客户端程序，并连接到服务器程序
3. 在客户端输入命令语句，将其作为请求发送给服务器程序。服务器收到请求后操作具体数据并返回给客户端

mysql 的安装目录下的bin目录下有很多可执行文件，可以通过绝对路径或是加入到环境变量进行执行

启动服务器程序

可执行文件

• mysqld可直接启动一个 MySql 服务器进程，但是这个可执行文件并不常用
• mysqld_safe是一个启动脚本，会简介调用mysqld并持续监控服务器运行状态，当服务器进程出现错误时，它还可以帮助重启服务器程序，还会将程序的出错信息和其他诊断信息输出到错误日志。对于传递给mysqld_safe的启动选项来说，如果mysqld_safe不处理，则会传递给mysqld程序处理
• mysql.server是一个启动脚本，会间接调用mysqld_save，后面添加start参数可直接启动服务器程序。它实际是一个链接文件，它对应的实际文件是../support-files/mysql.server
• mysld_multi可以运行多个服务器实例

启动客户端程序

启动服务器文件后就能启动客户端程序来连接到服务器，可执行文件需要重点关注mysql，启动这个可执行文件时，一般需要一些参数

• -h | --host表示服务器进程所在计算机的域名或IP地址
• -u | --user用户名
• -p | --password密码

使用时最好不要在一行命令中输入密码，最好是mysql -h localhost -u root -p来不显式地写出密码。如果省略-u会把登录操作系统的用户名当做Mysql去处理

服务器处理客户端请求

主要分为三个部分，连接管理、解析与优化、存储引擎

• 连接管理：每当有个客户端进程连接到服务器进程时，服务器进程就会创建一个线程专门处理这个客户端的交互。如果客户端退出时会与服务器断开连接，服务器并不会立即把与客户端交互的进程销毁，而是缓存起来。当连接建立后，与客户端关联的服务器线程会一直等待客户端发送的请求，MySql 接收的请求只是一个文本消息
• 解析与优化：其中几个重要的部分是查询缓存，语法解析和查询优化
• 查询缓存 mysql 会把刚处理过的查询请求和结果缓存起来，下次同样的请求发送过来时，直接从缓存中查找结果。如果两个查询请求有任何字符上的不同（空格、注释、大小写），都会导致缓存不会命中。另外如果查询请求中包含某些系统函数、用户自定义变量和函数、系统表，则这个请求不会被缓存。 myslq的缓存系统会检测涉及到的每张表，只要该表的结构或数据被修改，比如使用了INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或DROP DATABASE语句，则与该表有关的所有查询缓存都将变为无效并从查询缓存中移除
• 语法解析 如果缓存没有命中，就会进入正式的查询阶段，判断请求的语法是否正确，然后从文本中将要查询的表、各种查询条件都提取出来放到 mysql 服务器内部使用的一些数据结构上
• 查询优化 语法解析之后，服务器会获得需要的信息，比如行和列，mysql 的优化程序会对我们的语句做一些优化，比如外连接转换为内连接，优化的结果是生成一个执行计划
• 存储引擎 mysql 服务器把数据的存储和提取操作都封装到了一个名为存储引擎的模块中。为了实现不同的功能，mysql 提供了各式各样的引擎，不同存储引擎管理的表可能有不同的存储结构，采用的存取算法也可能不同

为了方便管理，mysql 服务器处理请求的过程简单分为server层和存储引擎层。各种不同的存储引擎为server层提供统一的调用接口，其中包含了几十个不同用途的底层函数

启动选项和系统变量

启动选项和配置文件

mysqld --skip-networking禁止客户端使用TCP/IP进行通信 mysqld --default-storaeg-engine=MYISAM改变表的默认引擎 mysqld -P 3307切换端口

把需要配置的启动选项都写在配置文件中，这样每次启动服务器时都从文件中加载启动相应的启动选项l。类UNIX操作系统有会按以下路径按顺序寻找配置文件

• /etc/my.cnf
• /etc/mysql/my.cnf
• SYSCONFDIR/my.cnf表示使用CMake构建MySQL时使用SYSCONFDIR指定的目录 -$MYSQL_HOME/my.cnf仅限服务器，该变量代表一个路径，可以在该路径下创建一个my.cnf配置文件
• defaults-extra-file命令行指定的额外配置文件
• ~/.my.cnf特定于用户的选项
• ~/.mylogin.cnf特定于用户的登录路径选项，仅限客户端，只能使用mysql_config_editor实用程序去创建或修改，用于存放客户端登录服务器时的相关选项

如果不想让mysql到特定路径下搜索配置文件，可以在命令行指定defaults-file选项，这样，程序启动时将只在指定路径下搜索文件

配置文件的中的选项分为很多个组，如果不同组里出现了多个有效的配置项，以最后出现的为准

• [server]作用于所有服务器程序
• [mysqld]作用于mysqld服务器程序
• [mysqld_safe]可作用于mysqld_safe
• [client]作用于所有客户端程序
• [mysql]作用于mysql客户端程序
• [mysqladmin]可作用于mysqladmin

[mysql-5.7]可作用于特定版本的mysql

系统变量

mysql 服务器在运行过程中会用到许多影响程序行为的变量，它们被称为系统变量，每个系统变量都有一个默认值可以通过命令行或配置文件中的选项在启动服务器时改变一些系统变量的值

SHOW VARIABLES [LIKE 匹配的模式]用于查看系统变量以及当前值

系统变量的作用范围分为两种

• GLOBAL影响服务器整体操作，称为全局变量
• SESSION影响客户端连接的操作，称为会话变量 服务器在启动时会把每个全局变量初始化为其默认值，同时还会为每个客户端维护一组会话变量，在连接时使用相应全局变量的当前值进行初始化，通过以下命令设置系统变量

SET [GLOBAL|SESSION] 系统变量名=值或 SET [@@(GLOBAL|SESSION)] 系统变量名=值

如果省略了作用范围，默认的作用范围就是SESSION。并不是所有的系统变量都具有GLOBAL和SESSION

状态变量使用来显示服务器程序运行状态的，所以它们的值只能由服务器程序自己来设置，不能人为设置

字符集和比较规则

字符集和比较规则简介

计算机实际存储的是二进制数据，通过建立字符串与二进制数据的映射关系来存储字符串。将字符映射成二进制数据的过程叫做编码，反过来叫做解码。

一些重要的字符集

• ASCII：收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符
• ISO 8859-1：收录 256 个字符，包括一些西欧常见字符
• GB2312：收录汉子6763个，其他文字符号682个，如果该字符在 ASCII 中，则采用一字节，否则二字节 使用不同字节数来表示一个字符的编码方式称为边长编码方式
• GBK字符集：兼容 GB2312 并进行了扩充
• UTF-8：收录当今世界各个国家的字符，而且还在不断扩充。它兼容 ASCII，采用变长编码方式，编码一个字符需要使用 14 个字节。UTF-8 知识 Unicode 的一种编码方案，UTF-16 使用 24 字节编码一个字符，UTF-32 使用 4 字节编码一个字符

mysql 支持的字符集和比较规则

• utfmb3：阉割过的 utf-8，使用 1~3 字节表示字符
• utfmb4：正宗 utf-8

SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];来查看支持的字符集。其中，CHARACTER SET和CHARSET是同义词 SHOW COLLATION [LIKE 匹配的模式];查看支持的比较规则

字符集和比较规则的应用

MYSQL 有 4 个级别的字符集合和比较规则

1. 服务器级别。包含两个系统变量

   • character_set_server服务器级别的字符集
   • character_server服务器级别的比较规则

2. 数据库级别。可以在创建和修改数据库是可以指定该数据库的字符集和比较规则

   CREATE DATABASE 数据库名
       [[DEFAULT]] CHARACTER SET 字符集名称]
       [[DEFAULT]] COLLATE 比较规则名称]
   
   
   ALTER DATABASE 数据库名
       [[DEFAULT]] CHARACTER SET 字符集名称]
       [[DEFAULT]] COLLATE 比较规则名称]

   • character_set_database当前数据库的字符集
   • collation_database当前数据库的比较规则

3. 表级别。可以在创建和修改表的时候指定表的字符集和比较规则

   CREATE DATABASE 表名
       [[DEFAULT]] CHARACTER SET 字符集名称]
       [COLLATE 比较规则名称]
   
   
   ALTER DATABASE 数据库名
       [[DEFAULT]] CHARACTER SET 字符集名称]
       [COLLATE 比较规则名称]

4. 列级别。创建和修改列的时候可以指定该列的字符集和比较规则

   CREATE TABLE 表名 {
       列名 字符串类型 [CHARACTER SET 字符串名称] [COLLATE 比较规则名称],
       其他列...
   }
   
   ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [比较规则名称];

由于字符集和比较规则之间互相关联，只修改一边，另一边也会跟着变化。如果每个级别不设置，则会引用上个级别的规则

** mysql 的字符集转换过程 在*客户端发送请求，服务器返回响应*的过程中，其实经历了多个字符集转换

• 客户端发送请求 一般情况下，客户端编码请求字符串使用的字符集与操作系统当前的使用的字符集一致，当使用类UNIX操作系统时，LC_：ALL，LC_CTYPE、LANG这三个环境变量的值决定了操作系统使用的是那种字符集
• 服务器接收请求 本质上来说，服务器接收到的请求是一个字节序列。服务器将这个字节序列看作是使用系统变量character_set_client代表的字符集进行编码的字节序列（这个变量是 SESSION 级别的）。 服务器在编码请求字符串使用的字符集和服务器在接收到一个字节序列认为该字节序列所采用的编码字符集，是两个独立的字符集，一般情况下，需要保证两个字符集是一致的
• 服务器处理请求 服务器会将请求的字节序列当做采用character_set_client对应的字符集进行编码的字节序列，在处理请求时又会将其转换为 SESSION 级别的系统变量character_set_connection对应的字符集进行编码的字节序列。同时服务器里对字符串的比较就是通过这个系统变量
• 服务器生成响应 通过系统变量character_set_results对列里保存的字符集进行编码后返回给客户端

这三个系统变量的作用范围都是 SESSION 级别的。在连接服务器时，客户端会将默认的字符集信息与用户名、密码一起发送给服务器，服务器会将这三个系统变量的值初始化为客户端的默认字符集，可以使用下面的语句一次性修改这几个系统变量的值

SET NAMES charset_name

• 客户端接收到响应 会使用客户端默认的字符集解释接收到的字节序列

InnoDB 记录存储结构

简介

InooDB 是 MySQL 的默认存储引擎，它将表中的数据存储到磁盘中。当从表中获取记录时，会将数据划分为若干个页，以页作为自盘和内存之间的基本单位。InnoDB中页的大小一般为 16KB，也就是说一次最少从磁盘中读取 16KB 到内存中，或最多少从内存中把 16kb 刷新到磁盘中。 系统变量innodb_page_size表明了页的大小，默认值为16384。该变量只能在第一次初始化MySQL 数据目录时指定，之后不可更改，也就是服务器运行时不能更改

行格式

我们平时是以记录为单位从表中插入数据的，这些记录在磁盘上的存放形式被称为行格式或记录格式。有四种不同类型的行格式COMPACT、REDUNDANT、DYNAMIC、COMPRESSED

可以在创建或修改表的语句中指定记录所使用的行格式

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
ALTER TABLE 表名 ROW_FORMAT=行格式名称;

• COMPACT 行格式 一条完整的记录可以分为两部分组成

  1. 记录的额外信息。这些额外信息分为三部分，分别是变长字段长度列表、NULL 值列表和记录头信息。

     • 变长字段长度列表。mysql 支持一些边长的数据类型，比如VARCHAR(M)、VARBINARY(M)、各种TEXT类型、各种Blob类型，我们会在存储真实数据时顺便把这些数据占用的字节数也存起来。 占用的字节数都存放在记录的开头位置，从而形成一个变长字段长度列表，按列的顺序逆序存放。如果变长字段允许存储的最大字节数超过 255 字节，并且真实数据占用的字节数超过 127 字节，则使用 2 字节来表示真实数据占用的字节数，否则使用 1 字节，如果所有列都不是变长数据类型或所有列都是NULL就不需要有变长字段长度列表

     • NULL 值列表。会把一条记录中值为NULL的列统一管理起来，存储到NULL列表中。它会先统计允许存储NULL 的列有哪些，之后会将每个允许存储NULL的列对应一个二进制位，二进制位按列的顺序逆序排列，二进制位的值为1时，值为NULL，按列的顺序逆序排列。NULL值列表必须用整数个字节的位表示，如果不是整数个字节，则在字节的高位补0。

     • 记录头信息。由固定的5字节组成，用于描述记录的一些属性

       名称	大小（位）	描述
       预留位	1	没有使用
       预留位	2	没有使用
       delete_mask	1	标记该记录是否被删除
       min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
       n_owned	4	表示当前记录拥有的记录数
       heap_no	13	表示当前记录在记录堆的位置信息
       record_type	3	表示当前记录的类型， 0 表示普通记录， 1 表示B+树非叶子节点记录， 2 表示最小记录， 3 表示最大记录
       next_record	16	表示下一条记录的相对位置

  2. 记录的真实数据。MySQL会为每个记录默认添加列 Innodb 优先使用用户自定义的键作为主键，如果用户没有定义主键，则选取一个不允许存储NULL值的UNIQUE键作为主键，如果都没有定义，则默认添加一个名为row_id的隐藏列作为主键

  3. CHAR(M)列的存储格式。CHAR(M)不属于变长字段，如果采用变长编码的字符集，比如utf8需要 1~3 个字节，此时该列的值占用的字节数也会被存储到变长字段长度列表中，另外，采用变长编码字符集的CHAR(M)类型的列至少占用M个字节，用空格占用

• 溢出列 InnoDB 中的磁盘和内存交互的基本单位是页，我们的记录都会被分配到某个页中存储。而一个页的大小是16384字节。如果一个列的实际数据超过了这个大小，则在记录的真实数据处只会存储该列的一部分数据，而把剩余的数据分散存储在几个其他的页中，然后在记录的真实数据处用 20 字节存储指向这些页的地址。如果这条记录的列的数据需要使用溢出页来存储，那么这个列就叫做溢出页，不只是VARCHAR(M)、TEXT、BLOB这些类型的列也会成为溢出页

MySQL 规定一个页中至少存放两行记录，也需要存储一些 132 字节的额外信息。假设一个列的真实数据占用的字节数为n，该列不发生溢出的话，需要满足下面不等式

132 + 2*(27 + n) < 16384

DYNAMIC行格式和COMPACT很像，但它会把所有真实数据都存储到溢出页中，只在记录的真实数据处存储20字节指向溢出页的地址

COMPRESSED行格式会采用压缩算法对页面进行压缩，以节省空间

InnoDB 数据页结构

存放记录的页为索引INDEX页，下面称为数据页

一个InnoDB 数据页的存储空间大致分为 7 个部分，有的部分占用字节数是确定的，有的是不确定的

记录在页中的存储

每当插入一条记录时，会从Free Space部分申请一个记录大小的空间，并将这个空间划分到User Records部分。当Free Space空间被User Records部分替代掉之后，这个页就使用完了，如果还有新纪录插入，需要申请新的页了

• 记录头信息的秘密 下面是记录头信息5字节的数据

  名称	大小（单位：bit）	描述
  预留位1	1	没有使用
  预留位2	1	没有使用
  delete_mask	1	标记该记录是否被删除
  min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
  n_owned	4	表示当前记录拥有的记录数
  heap_no	13	表示当前记录在记录堆的位置信息
  record_type	3	表示当前记录的类型，0 表示普通记录，1 表示B+树非叶节点记录，2 表示Infimum记录，3 表示Supremum记录
  next_record	16	表示下一条记录的相对位置

  • delete_flag这个属性用来标记当前记录是否被删除，占用 1 比特，为 1 时表示被删除。被删除的记录之所以不会被移除，是因为已处置后还需要在磁盘里重新排列其他记录。所有被删除的记录会形成一个垃圾链表，记录在这个链表中占用的空间称为可重用空间。之后如果有新记录插入到表中会覆盖掉被删除的这些记录占用的存储空间
  • min_rec_flag为 B+ 树每层非页子节点中最小的目录项纪录都会添加该标记
  • n_owned
  • heap_no把记录一条一条排列的结构称为堆，把一条记录在堆中的相对位置称为heap_no，新申请一条记录的存储空间时，该条记录比物理位置在它前面的那条纪录的head_no值大 1。另外每个页会自动加两条伪记录，一条为页面的最小记录Infimum，一条为页面的最大记录Supremum，它们都是由 5 字节的记录头信息和 8 字节大小的固定单词组成。这两条也为堆的一部分，heap_no分别为 0 和 1，在堆中的位置相对靠前，即便删除了堆中的某条记录，这条被删除记录的heap_no值也保持不变
  • record_type表示当前记录的类型。一共有 4 种类型的记录，其中 0 表示普通记录，1 表示 B+ 树非叶节点的目录项记录，2 表示Infimum记录，3 表示Supremum记录
  • next_record表示从当前记录的真实记录到下一条记录真实数据的局距离，如果为正，说明下一条记录在当前记录前，为负则在后。比如值为 32，则表示从当前字节往后 32 字节的便是下一条记录的真实数据。这个其实就是链表，通过当前记录找到下一条记录。下一条记录指的是按主键值大小由小到大顺序排列的下一条记录。Infimum记录的下一条记录就是本页中键值最小的记录，本页中键值最大的记录下一条就是Supremum记录。  如果删除记录，会发生以下步骤
    1. 被删除记录的deleted_flag值设置为 1
    2. 被删除记录的next_record值为 0，意味着没有下一条记录
    3. 上一条记录的next_record指向为下一条记录 next_record指针指向的是记录头信息和真实数据之间的位置，它向左是记录头信息，向右是真实数据。所以变长字段长度列表、NULL值列表都是逆序存放的，可以提高高速缓存的命中率

Page Directory(页目录)

记录在页中是按主键值由小到大的顺序串联成一个单向链表，如果要通过主键值查找页中记录需要先看目录，制作目录过程如下

1. 将所有正常记录（包括Infimum和Supremum，但不包括已经移除到垃圾链表的记录）划分为几个组
2. 每个组的最后一条记录（也就是组内最大的记录）的头信息中的n_owned属性表示该组内有几条记录
3. 将每个组最后一条记录在页面的地址偏移量（也就是该记录的真实记录与页中第0 个字节之间的距离）单独提取出来，按顺序存储到靠近页尾部的地方。这个地方就是Page Directory。页目录中的这些地址偏移量称为槽Slot，每个槽占用两字节，页目录由多个槽组成

对于Infimum记录所在的分组只有一条记录，Supremum记录所在的分组拥有的记录条数只能在 18 条之间，剩下的分组记录的条数范围只能在 48 条之间。所以给记录进行分组是按下列步骤进行

1. 初始情况下，一个数据页中Infimum和Supremum两条记录，分数与两个分组，页目录中只有两个槽，分别代表Infimum和Supremum在页面中的地址偏移量
2. 每插入一条记录，都会从页目录中找到对应记录的主键值比待插入记录的主键值大并且差值最小的槽，之后把该槽对应的记录的n_owned值加 1，表示本组又添加了一条记录
3. 当一个组的记录数等于 8 后，再插入一条记录，会将组中的记录拆分成两个组，其中一个 4 条，一个 5 条。同时这个拆分过程会新增一个槽，记录新增分组中最大的那条记录的偏移量

在一个数据页中查找指定主键值的记录时，过程分为两步

1. 通过二分法确定该记录所在分组对应的槽，然后找到该槽所在分组中主键值最小的那条记录，可以通过找到上一个槽对应记录的主键值+1
2. 通过记录的next_record属性遍历该槽所在组的各个记录

File Header（文件头部）

File Header 通用于各种类型的页，也就是各种类型的页都会以File Header作为第一个组成部分，它描述了一些通用于各种页的信息

• FIL_PAGE_SPACE_OR_CHKSUM校验和，即通过某种算法计算出一个比较短的值来代表这个很长的字节串，这个比较短的值就是校验和，在比较两个很长的字节串之前，会先比较校验和，这样就省去了直接比较两个长字节串的损耗
• FIL_PAGE_OFFSET每个页都有一个单独的页号，InnoDB 通过页号来唯一定位一个页
• FIL_PAGE_TYPE当前页的类型,还有很多其他类型的页

• FIL_PAGE_PREV和FIL_PAGE_NEXT某种类型数据可能占用的空间非常大，InnoDB 无法一次性为大量数据分配一个非常大的存储空间，如果分散到不同页存储，需要把这些页关联起来，这两个值就是上一页和下一页的页。这样案通过一个双向链表把许多页串联起来

File Trailer（文件尾部）

InnoDB 会把数据存储到磁盘上，但是如果在存储时断电，则这个时候需要检验一个页是否完整，会在每个页的尾部加一个File Trailer部分，这个部分由 8 个字节组成，分成两个部分

1. 前四字节代表页的校验和。和File Header校验和相对应，每当一个页面在内存中发生修改时，在刷新之前就要把页面的校验和算出来。File Header的校验和会先刷新到磁盘，完全写完后，校验和会写到页的尾部，如果页面刷新成功，则页首和页尾的校验和应该是一致的。
2. 后四字节表示页面在最后修改时对应的 LSN 的后四个字节，正常情况下应该和File Header的FIL_PAGE_LSN的后四字节相同，也是用于校验文件完整性

B+ 数索引

页可以不在物理结构上相连，只要通过双向链表相关联

没有索引时进行查找

比如搜索条件为某个列等于某个常数的情况

SELECT [查询列表] FROM 表名 WHERE 列名 = xxx;

• 以主键为搜索条件：在页目录中使用二分法快速定位到对应的槽；遍历该槽对应分组中的记录，即可快速找到指定的记录
• 以其他列为搜索条件：没有索引的话只能通过遍历

索引

可以快速定位记录所在的数据页而建立一个别的目录，建这个目录的过程中必须完成两件事

1. 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。新分配的数据页编号可能并不是连续的，也就是说我们使用的页在磁盘上可能并不挨着，它们只是通过维护上一页和下一页的编号而建立了链表关系。如果新页用户记录的主键值小于上一页的主键值，则会对记录进行一个记录移动，相当于互换位置
2. 给所有页建立一个目录项，如果想从多页中根据主键值快速定位某些记录所在的页，需要编制目录，每个页对应一个目录项，每个目录项包括以下内容

• 页的用户记录中最小的主键值，用key来表示
• 页号，用page_no表示 只需要把目录项在物理存储器上连续存储，比如放在一个数组中，就可以实现根据主键值快速查找某条记录的功能了，具体过程分两步

1. 先从目录项中根据二分法快速确定出具体主键值的记录在某个目录项中，找出对应页的页号
2. 根据上面讲的在页中查找记录的方式去页中定位具体记录 这个目录就是索引

根据上面的做法有下面几个问题

1. 表中记录越来越多，这时需要非常大的连续存储空间才能把所有目录项放下，这不现实
2. 假设删除某个页面所有的记录，那么目录项怎么处理都比较麻烦

数据库会复用之前存储用户记录的数据项来存储目录项。通过记录头信息中的record_type来区分用户记录和目录项纪录。下面是目录项记录和普通用户记录的不同点

1. 目录项记录的record_type值是1，普通用户记录的record_type值是0
2. 目录项记录只有主键值和页的编号两个列，普通用户记录的列是用户自己定义，还有隐藏列
3. 目录项记录头信息的min_rec_flag属性才为1，普通用户记录的min_rec_flag属性为0 根据主键查找记录的步骤可以分为两步
4. 先到存储目录项记录的页通过二分法快速定位到对应的目录项纪录，定位到对应的用户记录所在的页
5. 再到对应的页中根据二分法快速定位到对应主键值的记录 如果一个数据页不足以存放所有的目录项记录，此时会新增一个存储目录项记录的页。在进行上述查找方式前会先用二分法根据每个目录页的主键值范围找到对应的目录项记录的页 如果存储目录项记录的页太多，此时会为这些存储目录项记录的页再生成一个更高级的目录，就像一个多级目录一样

上面这个无限层级的目录结构就是B+树，真正的用户记录都存放在B+树最底层的节点上，这些节点也被称为叶子节点或叶节点。其余用来存放目录项记录的节点称为非叶节点或内节点。最上面的节点称为根节点

最底层的节点就是第零层，在Page Header里有一个PAGE_LEVEL的属性，它代表着这个数据也作为节点在B+树上的层级。

1. 聚簇索引。前面介绍的B+树本身就是一个目录，或者说本身就是一个索引，它有下面两个特点

   • 使用记录主键值的大小进行记录和页的排序，包括三方面的意义
     1. 页内的记录按主键的大小顺序排成一个单向链表，页内的记录被分成若干个组，每个组中主键值最大的记录在页内的偏移量会被当作槽依次存放在页目录中，可以在页目录中通过二分法快速定位到主键列等于某个值的记录
     2. 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
     3. 存放目录项记录的页分为不同的层级，在同一层级中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
   • B+ 树的叶子节点存储的是完整的用户记录，即所有列的值

2. 二级索引。如果我们想以别的列作为搜索条件时，会发现 MySql 会多建几棵 B+ 树，并且树中的数据采用不同的排序规则，比如通过列的大小作为数据页、页中记录的排序规则，它与聚簇索引有几处不同

   • 使用列的大小作为记录和页的排序，包括三方面的含义
     1. 页（包括叶子节点和内节点）内的记录是按照列的大小顺序排成一个单向链表，页内的记录被划分成若干个组，每个组中列值最大的记录在页内的偏移量会被当作槽依次存放在页目录中，可在页目录中通过二分法快速定位到列等于某个值的记录
     2. 各个存放用户记录的页也是根据页中记录的列大小顺序排成一个双向链表
     3. 存放目录项记录的页分为不同的层级，在同一层级中的页也是根据页中记录项记录的列大小顺序排成一个双向链表
   • B+ 树的叶子节点存储的并不是完整的用户记录，只是列 + 主键这两个列的值
   • 目录项记录中不再是主键 + 页号的搭配，而成了列 + 页号的搭配

   假如我们想查找满足搜索条件c2=4的记录，查找过程如下

   1. 确定目录项记录页。根据根页面，也就是页44 ，可以快速定位到目录项记录所在的页为页42 （因为2 < 4 < 9 ）。
   2. 通过目录项记录页确定用户记录真实所在的页。在页42 中可以快速定位到实际存储用户记录的页，但是由于c2 列并没有唯一性约束，所以c2 列值为4 的 记录可能分布在多个数据页中，又因为2 < 4 ≤ 4 ，所以确定实际存储用户记录的页在页34 和页35 中。
   3. 在真实存储用户记录的页中定位到具体的记录。 到页34 和页35 中定位到具体的记录。
   4. 但是这个B+ 树的叶子节点中的记录只存储了c2 和c1 （也就是主键）两个列，所以我们必须再根据主键 值去聚簇索引中再查找一遍完整的用户记录。这个通过携带主键信息到聚簇索引中重新定位完整用户记录的过程也叫做回表。

   回表是因为如果将完整的用户记录放置在叶子结点的话，每个 B+ 树都得复制一遍用户记录，太占地方了。这种以非主键列的大小为排序规则而建立的 B+ 树需要执行回表操作才能定位到完整用户记录的 B+ 树称为二级索引或辅助索引

3. 联合索引。可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。比如，我们想让 B+ 树按 c2 和 c3 列的大小进行排序，这里包含两层含义

   • 先把各个记录和页按 c2 列进行排序
   • 在记录的 c2 列相同的情况下，再采用 c3 列进行排序
   • 叶子节点处的用户记录由 c2、c3 列和主键组成

   需要注意的是，以 c2 和 c3 列的大小为排序规则建立的 B+ 树称为联合索引，也称为复合索引或多列索引。它本质上也是一个二级索引

InnoDB 中 B+ 树索引的注意事项

1. 根节点万年不动窝。B+ 树的形成过程是下面这样的

   • 当为某个表创建一个 B+ 树索引时，都会为这个索引创建一个根节点页面。最开始表中没数据的时候，每个 B+ 树索引对应的根节点既没有用户记录，也没有目录项记录
   • 向表中插入用户记录时，先把用户记录存储到根节点中
   • 在根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页面，然后对这个新分配的页做页分裂操作，得到另一个新页。这是新插入的记录会根据键值的大小分配在对应页中，根节点此时便升级为存储目录项记录的页，也就是需要把目录项记录插入到根节点中

   注意的是，根节点的页号会保存在一个不变的地方，当InnoDB需要用到索引时，都会从固定的地方取出根节点的页号，从而访问这个索引

2. 内节点中记录项记录的唯一性 为了让相信插入的记录能够找到自己在那个页中，也就是要保证B+树同一层内节点的目录项记录除了页号这个字段以外是唯一的，此时二级索引的内节点的目录项记录的内容实际上由三部分组成 - 索引列的值 - 主键值 - 页号 这样在插入记录时，会先比较对应列的值，如果列的值相同，接着比较主键，最后就能定位到唯一的一条目录项记录

   对于二级索引来说，先按二级索引列的值进行排序，在列值相同的情况下，再按主键值进行排序，所以为列进行索引其实相当于为主键列和列建立了一个联合索引

3. 一个页面至少容纳两条记录。主要是为了防止 B+ 树的层级增长过高

MySql 中创建和删除索引的语句 InnoDB 会自动为主键或带有UNIQUE属性的列建立索引。如果需要为其他的列建立索引，需要显式指明。每建立一个索引都会建立一棵 B+ 树，而每次增删改一条记录时都需要维护各个记录、数据页的排序关系，很费性能和存储空间。可以在创建表的时候，指定需要建立索引的单个列或建立联合索引的多个列

CREATE TABLE 表名 {
    各个列的信息...,
    (KEY|INDEX) 索引名 (需要被索引的单个列或多个列)
}

修改表结构时添加索引

    ALTER TABLE 表名 ADD (KEY|INDEX) 索引名 (需要被索引的单个列或多个列)

B+ 树索引的使用

索引的代价

• 空间上的代价。每建立一个索引，就要建立一棵 B+ 树，每一棵 B+ 树的每一个节点就是一个数据页。一个数据页会默认占用 16KB 的空间
• 时间上的代价。每当对表中的数据进行增删改操作时，都需要修改各个 B+ 树索引。B+ 树中的每层节点都按照索引列的值从大到小的顺序排序组成了双向链表。无论是用户记录还是目录项记录，都按照索引列的值从小到大的顺序形成了一个单向链表。而增删改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行页面分裂、页面回收等操作，以维护节点和记录的排序。还有一点是在执行查询语句前，要生成一个执行计划。一般情况下，一条查询语句在执行过程中最多使用一个二级索引。在生成执行计划时需要计算使用不同索引执行查询时所需要的成本，最后选取成本最低的那个索引执行查询，如果索引太多，会导致成本分析过程耗时太多，影响查询语句的执行性能

应用 B+ 树索引

对于下面这个查询语句

SELECT * FROM single_table WHERE key2 IN (1436, 6328) OR (key2 >= 38 AND key2 <= 79);

相当于从下面的 3 个扫描区间中获取二级索引记录

• [1438, 1438]：对应的边界条件是key2 IN (1438)，边界条件只有单个值称为单点扫描区间
• [6328, 6328]：对应的边界条件是key2 IN (6328)，也为单点扫描区间
• [38, 79]：对应的边界条件是key2 >= 38 AND key2 <= 79，多个值称为范围扫描区间 由于查询列表是*，也就是需要读取完整的用户记录，所以从上述区间每获取一条二级索引记录，就需要根据该二级索引记录的 id 列的值进行回表操作

不是所有的搜索条件都能成为边界条件，比如下面这个查询语句

SELECT * FROM single_table WHERE key1 < 'a' AND key3 > 'z' AND common_field = 'abc';

如果使用idx_key1索引执行查询，则扫描区间是(-无限大， 'a')，形成该扫描区间的边界条件就是key1 < 'a'，而key3 > 'z' AND common_field = 'abc'就是普通的搜索条件，这些普通的搜索条件需要在获取到idx_key1的二级索引记录后，再执行回表操作，在获取到完整的用户记录后才能判断它们是否成立

从上述描述可以看到，在使用某个索引执行查询时，关键的问题就是通过搜索条件找出合适的扫描区间，然后再到对应的 B+ 树中扫描索引列值在这些扫描区间的记录。对于每个扫描区间来说，仅需要通过 B+ 树定位到该扫描区间的第一条记录，然后就可以沿着记录所在的单向链表向后扫描，直到某条记录不符合形成该扫描区间的边界条件为止。使用操作符形成扫描区间时，下面几点需要注意

• IN操作符的语义与=之间用OR连起来的语义是一样的
• !=产生的扫描区间是(-无限大， 条件)和(条件，无限大)
• LIKE只在匹配完整的字符串或是字符串前缀时才产生合适的扫描区间

索引用于排序。编写查询语句时，经常需要使用ORDER_BY子句对查询出来的记录按照某种规则进行排序。一般情况下，只能把记录加载内存中，然后再用一些排序算法进行排序。有时查询的结果集可能太大以至于无法再内存中进行排序，此时就需要暂时借助磁盘的空间存放中间结果，在排序操作完成后再把排好序的结果集返回给客户端。 在 mysql 中，这种在内存或磁盘中进行排序的方式统称为文件排序，但是如果ORDER_BY子句中使用了索引列，就能不在文件中进行排序 比如下面这个简单的查询语句

SELECT * FROM single_table ORDER BY key_part1, key_part2, key_part3 LIMIT 10;

这个查询语句的排序顺序和二级索引的记录排序顺序是一致的，此时只需要从第一条二级索引记录开始向后扫描取 10 条索引记录，再对每条记录回表返回给客户端即可，这样就省去了排序的时间

1. 使用联合索引排序的注意事项。ORDER_BY子句后面的列的顺序必须按照索引列的顺序给出，比如ORDER_BY key_part1、ORDER_BY key_part1, key_part2。如果联合索引列的左边连续的列是常量时，也可以使用联合索引对右边列进行排序。比如

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' ORDER BY key_part3 LIMIT 10;

2. 不可以使用索引进行排序的几种情况。
   • ASC、DSC 混用。如果想使用联合索引进行排序，那么各个排序列的排序规则都是一致的，要么都是 ASC，要么就是 DSC。不然不会使用李娜和索引进行排序操作
   • 排序列包含非同一个索引的列。有时用来排序的多个列不是同一个索引中的
   • 排序列是某个联合索引的索引列，但是这些排序列在联合索引中并不连续。比如ORDER BY key_part1, key_part3
   • 用来形成扫描区间的索引列与排序列不同

   SELECT * FROM single_table WHERE key1 = 'a' ORDER BY key2 LIMIT 10;

   • 排序列不是以单独列名的形式出现在ORDER_BY子句中，不能是修饰过的形式，比如

   SELECT * FROM single_table ORDER BY UPPER(key1) LIMIT 10;

索引用于分组。如果没有索引，就需要建立一个用于统计的临时表，在扫描聚簇索引的记录时将统计的中间结果填入这个临时表。扫描完记录后，再把临时表中的结果作为结果集发送给客户端。而如果有了索引，只要分组顺序与二级索引顺序一致，就能不建立临时表，与进行排序差不多，分组列的顺序不能和索引列顺序不一致

回表的代价

对于下面这个查询语句来说

SELECT * FROM single_table WHERE key1 > 'a' AND key1 < 'c';

可以选择下面两种方式来执行

• 全表扫描。直接扫描全部的聚簇索引记录，针对每一条记录，都判断搜索条件是否成立，成立即发送客户端
• 使用idx_key1执行该查询。可以根据搜索条件key1 > 'a' AND key1 < 'c'得到对应的扫描区间('a', 'c')，然后扫描该扫描区间中的二级索引记录。根据二级索引记录找到聚簇索引记录，也就是回表操作，获取到完整的用户记录后发送客户端 二级索引记录所在的页面页号会尽可能相邻，但是二级索引记录对应的 id 值大小是毫无规律的，每读取一条二级索引记录，就需要根据该二级索引记录的 id 值到聚簇索引中执行回表操作。如果聚簇索引记录所在的页面不在内存中，就需要将该页面加载到内存中。由于很多 id 值不连续的聚簇索引记录，而且这些聚簇索引记录分布在不同的数据页中，会造成大量的随机 I/opt/

执行回表操作的记录越多，使用二级索引进行查询的性能就越低，某些查询宁愿使用全表扫描也不使用二级索引，比如 key1 值在'a' - 'c'之间的用户数量占用全部用户数量的99%

执行全表扫描和二级索引 + 回表的选择工作是由查询优化器来进行。查询优化器会针对表中的记录计算一些统计数据，再利用这些统计数据或访问表中的少量记录来计算需要执行回表操作的记录数。如果执行回表操作的记录数越多，就越倾向于全表扫描。

一般情况下，可以给查询语句指定LIMIT语句来限制查询返回的记录数，这会让查询优化器倾向于使用二级索引 + 回表的方式进行查询

低于需要对结果进行排序的查询，如果采用二级索引执行查询需要执行回表操作的记录特别多，也倾向于使用全表扫描 + 文件排序的方式执行查询

更好地创建和使用索引

我们只为出现在 WHERE 子句中的列、连接子句中的连接列，或出现在 ORDER BY 或 GROUP BY 子句中的列创建索引。仅出现在查询列表中的列没必要建立索引

在为某个列创建索引是，需要考虑该列中不重复值的个数占全部记录条数的比例，如果比例太低，则说明该列包含过多重复值，通过二级索引 + 回表的方式执行查询时，有可能执行太多回表操作

索引列的类型尽量小。数据类型越小，占用的存储空间就越少，一个数据页内可以存放更多记录，磁盘I/O带来的性能损耗也越小，读写效率就越高。对于表的主键来说更加适用

为列前缀建立索引。如果字符串很长，那么在存储这个字符串时就需要占用很大的存储空间。此时可以将字符串的前几个字符存放到索引中

ALTER TABLE single_table ADD INDEX idx_key1(key1(10))

这种做法职能定位到前十个字符的二级索引字符，如果搜索条件大于十个字符，会在扫描这些二级索引记录时再次进行判断，而且这种方式不能进行排序，只能进行全表扫描

覆盖索引。为了彻底回表操作带来的性能损耗，建议最好在查询列表中只包含索引列，比如

SELECT key1, id FROM single_table WHERE key1 > 'a' AND key1 < 'c';

这样就不需要执行回表操作，这种索引中已经包含所有需要读取的列的查询方式称为覆盖索引。如无必要，最好仅把业务中需要的列放在查询列表中，而不是简单地以*代替

让索引列以列名的形式在搜索条件中单独出现。比如下面两个语句的搜索条件一样

SELECT * FROM single_table WHERE key2 * 2 < 4;
SELECT * FROM single_table WHERE key2 < 4/2;

第一个条件中MYSQL并不会尝试简化key2 * 2，而是直接认为这个搜索条件不能形成合适的扫描区间来减少需要扫描的记录数量，所以只能全表扫描。而第二个条件可以分析扫描区间

新插入记录时主键大小对效率的影响。最好让插入的主键值依次递增，如果忽大忽小会对页进行分裂，意味着性能损耗。可以让主键 id 具有AUTO_INCREMENT属性

冗余和重复索引。也就是没有必要对同一个列创建多个索引

MySQL 数据目录

MySQL 数据目录

MySQL 服务器程序在启动时，回到文件系统的某个目录下加载一些数据，之后在运行过程中产生的数据也会存储到这个目录下的某些文件中。这个目录就称为数据目录

通过以下命令确定数据目录

SHOW VARIABLES LIKE 'datadir';

数据目录的结构

当使用CREATE DATABASE 数据库名创建数据库时，MySQL 会帮我们做两件事

• 在数据目录下创建一个与数据库名同名的子目录（或者说是文件夹）；
• 在与该数据库名同名的子目录下创建一个名为db.opt的文件。这个文件包含了该数据库的一些属性，比如数据库的字符集和比较规则 下面命令可以查看计算机当前有哪些数据库

SHOW DATABASE;

我们的数据其实都是以记录的形式插入到表中。每个表的信息可以分为两种：

• 表结构的定义
• 表中的数据 表结构指的是该表的名称是啥、表里有多少列、每个列的数据类型是啥、有啥约束条件和索引、用的是什么字符集和比较规则等各种信息。在目录结构下对应的数据库子目录中创建了一个专门用于描述表结构的文件，文件名是表名.frm。这个文件是以二进制格式存储的

1. InnoDB 是如何存储表数据的。为了更好地存储页，InnoDB 提出了表空间或文件空间的概念，表空间是一个抽象的概念，它可以对应文件系统上一个或多个真是文件。每个表空间可以被划分成很多个页，表数据就存放在某个表空间下的某些页中。表空间划分为几种不同的类型
   • 系统表空间。可以对应文件系统上一个或多个实际的文件。默认情况下，InnoDB 会在数据目录下创建一个名为ibdata1、大小为 12MB 的文件。这个文件就是对应的系统表空间在文件系统上的表示，而且这个文件是自扩展文件，当不够用时会自己增加文件大小。如果想自己修改系统表空间对应的文件，可以像下面一样修改

   [server]
   innodb_data_file_path=data1:512M;data2:512M:autoextend

   这样MySQL启动之后就会创建 data1 和 data2 这两个各自 512MB 大小的文件作为系统空间。如果这两个文件不够用，会自动扩展 data2 文件的大小。 我们也可以把系统表空间配置到单独的磁盘分区上，这时涉及的系统选项是innodb_data_file_path和innodb_data_home_dir
   • 独立表空间。在 Mysql5.6.6 以及以后的版本中，InnoDB 会为每一个表建立一个独立表空间。会在该表所属数据库对应的子目录下创建一个表表示该独立表空间的文件表名.ibd。如果想使用系统表空间来存储数据，可以使用下面的配置

   [server]
   innodb_file_per_table=0

   不过这个表空间只对新建的表起作用，如果想把已经存储到系统表空间的表转移到独立表空间时可以使用下面语法

   ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;

   如果想把独立表空间移到系统表空间

   ALTER TABLE 表名 TABLESPACE [=] innodb_system;

   上面的 = 号可有可无
   • 其他表空间。
2. MyISAM 使用不同的文件来存储数据文件和索引，都存放在对应的数据库子目录下。会存在三个文件test.frm、test.MYD、test.MYI

数据目录还包含了一些额外文件，包括

• 服务器进程文件：每运行一个 MySQL 服务器程序，都意味着启动一个进程。会把自己的进程 ID 写入到这个文件中
• 服务器日志文件：查询日志、错误日志、二进制日志、redo 日志等
• SSL 和 RSA 证书与密钥文件：主要是为了客户端和服务端安全通信创建的文件

文件系统对数据库的影响

由于 MySQL 的文件存储在文件系统中，因此会受到文件系统的制约

• 数据库名称和表名称不得超过文件系统所允许的最大长度
• 特殊字符的问题。为了避免因为数据库名和表名出现某些特殊字符而造成文件系统不支持的情况，MySQL 会把数据库名和表名中所有除数字和拉丁字母以外的任何字符在文件名中都映射成@+编码值的形式，并作为文件名
• 文件长度受文件系统最大长度的限制。

MySQL 系统数据库简介

下面是 MySql 的几个系统数据库

• mysql：存储了 MySQL 的用户账户和权限信息、一些存储过程和事件的定义信息、一些运行过程中产生的日志信息、一些帮助信息以及时区信息
• information_schema: 保存着 MySQL 维护的所有其他数据库的信息，比如有哪些表、视图、触发器、列、索引等。是一些描述信息，或者称之为元数据
• performance_schema: 保存服务器运行过程中的一些状态信息，相当于性能监控。比如执行语句，执行过程花费的时间，内存的使用情况等
• sys: 通过视图的形式吧information_schema和performance_schema结合起来，让开发人员更方便地了解服务器的性能信息

InnoDB 的表空间

回忆旧知识

对于 File Header 强调几点

• 表空间每一个页都对应着一个页号，也就是FIL_PAGE_OFFSET，可以通过这个页号在表空间中快速定位到指定的页面。这个页号由 4 字节组成，也就是 32 位，所以一个表空间最多可以拥有 2^32 个页，如果按页的默认大小为 16KB 来算，一个表空间最多支持 64TB 的数据。表空间第一个页的页号为 0，之后的页号分别为 1，2，3...
• 某些类型的页可以组成链表，链表中的相邻的两个页面的页号可以不连续，也就是它们可以不按照在表空间中的物理空间相邻存储，而是根据FIL_PAGE_PREV和FIL_PAGE_NEXT来存储上一个和下一个的页号。这两个字段主要是用于数据页。建立 B+ 树后，使用这两个字段为每层节点建立双向链表。
• 每个页的类型由FIL_PAGE_TYPE表示，不同类型的页在该字段上的值是不同的

独立表空间

区的概念 为了更好地管理页面，InnoDB 提出了区extent的概念，对于 16KB 的页来说，连续的 64 个页就是一个区，也就是说一个区默认占用 1MB 的大小，无论是系统表空间还是独立表空间，都可以看做是由若干个连续地区组成的，每 256 个区划分成一组。这些组的头几个页面的类型都是类似的。

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

段的概念 B+ 树的每一层中的页都会形成双向链表，而双向链表相邻的两个页之间的物理位置可能离的非常远，此时由于两个页的物理位置不连续，对于传统的机械硬盘来说，需要重新定位磁盘位置，也就是会产生随机 I/O，这样会影响磁盘性能。所以应该尽量让页面相邻页的物理位置也相邻，这样才可是使用顺序 I/O。 所以才引入了区的概念，一个区就是在物理位置上连续的 64 个页，在表中的数据量很大时，为每个索引分配空间的时候会按区为单位进行分配。在非常多的时候，可以一次性分配多个连续的区。 在使用 B+ 树进行查询时我们需要区分叶子节点和非叶子节点，它们会被分配到各自独有的区。存放叶子结点的区的集合就算是一个段segment，存放非叶子节点的区的集合也算是一个段。 默认情况下 InnoDB 的表只有一个聚簇索引，一个索引会生成两个段。而段是以区为单位申请空间的，一个区默认占用 1MB 存储空间。但是，假如数据量少的话，每次添加一个索引就要多申请 2MB 的空间太浪费了，所以 InnoDB 提出了碎片fragment区的概念，也就是说在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的。碎片区的页可以用于不同目的，比如属于不同的段，或不属于任何段。碎片区直属于表空间，并不属于任何一个段。所以未某个段分配存储空间的策略如下

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

区的分类 区大致可以分为 4 种类型

• 空闲的区：现在还没有用到这个区的任何页面
• 有剩余空闲页面的碎片区：表示碎片区中还有可被分配的空闲页面
• 没有剩余空闲页的碎片区：表示碎片区中所有页面都被分配使用，没有空闲页面
• 附属于某个段的区：Innodb 会另外定义一些特殊用途的段。当这些段中的数据量很大时，将会使用区作为基本的分配单位，这些区中的页面完全用于存储该段的数据。这四种区也可称为区的 4 种状态

为了方便管理这些区，InnoDB 设计了一个称为XDES Entry(Extent Descriptor Entry)的结构。每一个区都对应一个XDES Entry结构，这个结构记录了对应的区的一些属性

XDES Entry结构有 40 个字节，分为四个部分

• Segment ID: 每个段都有唯一一个编号，用 ID 表示。它表示的就是该区所在的段，前提是该区已经被分配给某个段了
• List Node: 可以将若干个XDES Entry结构串连成一个链表，如果想定位表空间的某个位置，只需指定页号以及该位置在指定页号中的页内偏移量即可
• State: 表明区的状态，也就是上表的四个
• Page State Bitmap: 一个区默认 64 页，这 128 位被划分为 64 个部分，每个部分有 2 位，对应区中的一个页。比如第一位和第二位对应着第一页，第三位和第四位对应第二页，以此类推。这两个位的第一位表示对应的页是否是空闲的，第二位还没用到

1. XDES Entry 链表。当段中数据较少时，会查看表中是否有状态为FREE_FRAG的区。如果有，那么从该区中取一个零散页把数据插进去；否则到表空间中申请一个状态为FREE的区，把该区的状态变为FREE_FRAG，然后从新申请的区中取一个零散页把数据插进去。之后，在不同的段使用零散页时都从该区中取，直到该区中没有空闲页面；然后该区的状态变成了FULL_FRAG。 现在的问题是怎么区分区的状态，不可能每次遍历每个区的XDES_Entry状态吧，这时List Node就发挥作用了，可以通过该指针做 3 件事
   • 把状态为FREE的区对应的XDES_Entry连接成一个链表，称为FREE链表
   • 把状态为FREE_FRAG的区对应的XDES_Entry连接成一个链表，称为FREE_FRAG链表
   • 把状态为FULL_FRAG的区对应的XDES_Entry连接成一个链表，称为FULL_FRAG链表 当要查找一个FREE_FRAG的区时，直接把FREE_FRAG链表的头节点拿出来，从这个节点对应的区中取一些零散页来插入数据。当这个节点对应的区中没有空闲页面时，就修改它的State字段的值，然后将其从FREE_FRAG链表中移到FULL_FRAG链表中。同理，如果FREE_FRAG链表没有节点，那么直接东FREE链表中取一个节点移动到FREE_FRAG链表，并修改该节点的 STATE 字段值为FREE_FRAG。 当段中的数据已经占满 32 个零散的页后，就直接申请完整的区来插入数据了。 那么我们如何知道哪个区对应哪个段呢，可以基于链表来快速查找只属于某个段的区，每个段中的区对应的XDES_Entry结构建立了三个链表
   • FREE链表：同一个段中，所有页面都是空闲页面的区对应的XDES_Entry结构会被加入到这个链表中
   • NOT_FULL链表：同一个段中，仍有空闲空间对应的XDES_Entry结构会被加入到这个链表中
   • FULL链表：同一个段中，没有空闲页面的区对应的XDES_Entry结构会被加入到这个链表中 再强调一遍，每个索引都对应两个段，每个段都会维护上面三个链表
2. 链表基节点。前面介绍的每个链表都对应这么一个链表基节点List Base Node结构，其中：
   • List Length表明该链表一共有多少个节点
   • First Node Page Number和First Node Offset表明该链表的头节点在表空间的位置
   • Last Node Page Number和Last Node Offset表明该链表的尾节点在表空间的位置 一般会把某个链表对应的List Node Base结构放置在表空间的固定位置，这样就可以很容易地定位某个链表了

段的结构。段并不对应表空间中某个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成，InnoDB 为每个段也定义了一个INODE Entry结构来记录段中的属性。各个部分含义如下

• Segment ID: 这个INODE Entry结构对应段的编号
• NOT_FULL_N_USED: 在NOT_FULL链表中已经使用了多少个页面
• 3 个List Base Node: 分别为段的FREE链表、NOT_FULL链表、FULL链表定义了List Base Node，这样可以直接到这个部分找到对应链表的List Base Node，从而找到首尾节点
• Magic Number: 用来标记这个INODE Entry是否已经被初始化（即把各个字段的值都填进去）。如果这个值是97937874，表明该INODE Entry已经被初始化
• Fragment Array Entry: 每个Fragment Array Entry结构都对应一个零散的页面，这个结构一共四个字节，表示一个零散页面的页号

各类型页面详细情况。每 256 个连续的区算是一个组，需要从每个组开头的一些类型相同的页面说起。1. FSP_HDR类型。第一个组的第一个页，同时也是表空间的第一个页面，页号为 0。它存储了表空间的一些整体属性以及第一个组内 256 个区对应的XDES Entry结构。大致由五个部分组成

1. File Space Header。用来存储表空间的一些整体属性

   名称	占用空间大小	描述
   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链表的基节点

2. XDES Entry 部分。每个XDES Entry结构对应表空间的一个区。我们把表空间对应的区分为若干个组，每组开头的一个页面记录着本组内所有区对应的XDES Entry结构，还记录着表空间的一些整体属性，这个页面的类型就是FSP_HDR类型，整个表空间只有一个这种类型的页面。除了第一个分组以外，每之后每个分组的第一个页面只需要记录本组内所有的区对应的XDES Entry即可，不需要再记录表空间的属性。所以之后每个分组第一个页面的类型定义为XDES，与FSP_HDR非常类似，也就是除了File Space Header部分，其余的部分都是一样的。

3. IBUF_BITMAP 类型。这种类型的页中类型记录了一些关于 Change Buffer 的东西。在修改非唯一二级索引页面时，如果该页面尚未加载到内存中，那么该修改将暂时缓存在 Change Buffer 中，之后服务器空闲或其他原因导致对应的页面从磁盘加载到内存时，再将修改合并到对应页面。

4. INODE 类型。第一个分组中第三个页面的类型是 INODE。这个类型的页就是为了存储 INODE Entry 结构而存在的。

如果一个表空间中存在的段超过 85 个，那么就需要额外的 INODE 类型的页来存储这些结构，为了方便管理这些 INODE 类型的页面，InnoDB 将这些 INODE 类型的页面串联成连个不同的链表 - SEG_INODES_FULL链表：在该链表中，INODE 类型的页面已经没有空闲空间来存储额外的 INODE Entry 结构 - SEG_INODES_FREE链表：在该链表中，INODE 类型的页面还有空闲空间来存储额外的 INODE Entry 结构 这两个链表的基节点就存储在FSP_HDR类型页面的File Space Header中，也就是说这两个链表的基节点是固定的。当新创建一个段时，都会创建一个与之对应的 INODE Entry 结构。存储过程如下

1. 先看SEG_INODES_FREE链表是否为空。如果不为空，从该链表中获取一个节点，也就相当于获取到一个仍有空闲空间的 INODE 类型的页面，然后把该 INODE Entry 结构放到该页面中
2. 如果SEG_INODES_FREE链表为空。需要从表空间的FREE_FRAG链表中申请一个页面，并将该页面的类型修改为 INODE，把该页面放到SEG_INODES_FREE链表中，同时把该 INODE Entry 结构放入到该页面

Segment Header 结构的运用。一个索引会产生两个段，分别是叶子节点段和非叶子节点段，每个段都会对应一个 INODE Entry 结构。我们需要一个地方来知道某个段对应某个 INODE Entry 结构。数据页有一个 Page Header 部分，它的PAGE_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP都占用 10 字节，对应一个 Segment Header 结构，各个部分的具体含义如下表所示

这样子索引和其对应的段的关系就建立起来了，只会在索引的根页面中记录这两个结构

系统表空间

系统表空间与独立表空间基本类似，但会记录一些与整个系统相关的信息，会多出来一些记录这些信息的页面，它的表空间 ID 是0。

系统表空间的前三个页面和独立表空间的前三个页面是一样的，也就是FSP_HDR、IBUF_BITMAP、INODE类型，但是 3~7 的页面是系统表空间特有的。

除了这几个记录系统属性的页面之外，系统表空间的extent 1和extent 2这两个区，也就是页号从 64~191 的这 128 个页面称为Doublewrite Buffer(双写缓冲区)。上面的大部分只是都涉及事务和多版本控制的问题

InnoDB 数据字典。平时使用 INSERT 语句向表中插入的那些记录成为用户数据，每当向一个表中插入一条记录是，MySql 先要校验插入语句所对应的表是否存在，以及插入的列和表中的列是否符合。如果语法没问题，还需要知道该表的聚簇索引和所有二级索引对应的根页面是哪个表空间的哪个页面。然后把记录插入对应索引的 B+ 树中。所以 MySQL 还保存着许多额外的信息

• 某个表属于哪个表空间，表里面多少列
• 表对应的每个每个列的类型是什么
• 该表有多少个索引，每个索引对应哪几个字段，该索引对应的根页面在哪个表空间的哪个页面
• 该表有哪些外键，外键对应哪个表的那些列
• 某个表空间对应的文件系统上的文件路径是什么 上述信息是元数据。InnoDB 特意定义了一系列内部系统表internal system table来记录这些元数据

这些表也被称为数据字典，都是以 B+ 树的形式保存在系统表空间的某些页面中。自重前四个表尤其重要，称为基本系统表basic system table。

1. SYS_TABLES 表。下面为 SYS_TABLES 的列

有两个索引：以 Name 列为主列的聚簇索引，以 ID 列建立的二级索引

2. SYS_COLUMNS 表

只有以(TABLE_ID, POS)列为主键的聚簇索引

3. SYS_INDEXES 表

以(TABLE_ID, ID)为主键的聚簇索引

4. SYS_FIELD表

以(INDEX_ID, POS)列为主键的聚簇索引

5. Data Dictionary Header 页面。有了上述四个基本系统表，就可以获取其他系统表以及用户定义的表的所有元数据。 这四个表的元数据是硬编码到代码中的，同时会拿出一个固定的页面来记录这四个表的聚簇索引和二级索引对应的 B+ 树位置。这个页面就是页号为 7 的页面，类型为 SYS，记录了Data Dictionary Header，除了这些信息，还记录了真个 InooDB 存储引擎的全局属性。这个页面由下表组成

由于有 Segment Header，所以 InnoDB 是把这些有关数据字典的信息当成一个段来分配存储空间，称之为数据字典段。该段只有一个碎片页用于存储非常少的字典信息。接下来是 Data Dictionary Header 部分的各个字段

• Max Row ID: 所有表都对应一个唯一的 ID，每次新建表时，该字段的值 +1，然后将其作为该表 ID
• Max Index ID: 所有索引都对应一个唯一的 ID，每次新建索引时，该字段的值 +1，然后将其作为索引 ID
• Max Space ID: 所有表空间都对应一个唯一的 ID，每次新建表空间时，该字段的值 +1，然后将其作为表空间 ID
• Mix ID Low: 这个字段没啥用
• Root of SYS_TABLES clust index: 表示 SYS_TABLES 表聚簇索引的根页面的页号
• Root of SYS_TABLES_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 表聚簇索引的根页面的页号

用户不能直接访问这些系统表，但是在系统数据库information_schema中提供了一些以INNODB_SYS开头的表，用以下命令调出

USE information_schema;

SHOW TABLES LIKE 'INNODB_SYS%'

上面的表并不是真正的内部系统表，它们是被 Innodb 启动时读取 SYS 开头的表然后填充进去的，但供大家参考足矣

下面为表空间总结一个全局图