MySQL 学习手札

发表于 更新于 分类于 阅读次数： 本文字数： 40k 阅读时长 ≈ 2:27

MySQL 学习手札

”MySQL 是怎样运行的“读书笔记

初识MySQL

一. 服务器处理客户端请求

服务器处理流程图：

二. 常用存储引擎

存储引擎 描述
ARCHIVE 用于数据存档（行被插入后不能再修改）
BLACKHOLE 丢弃写操作，读操作会返回空内容
CSV 在存储数据时，以逗号分隔各个数据项
FEDERATED 用来访问远程表
InnoDB 具备外键支持功能的事务存储引擎
MEMORY 置于内存的表
MERGE 用来管理多个MyISAM表构成的表集合
MyISAM 主要的非事务处理存储引擎
NDB MySQL集群专用存储引擎

其中 InnoDB 是 MySQL 默认的存储引擎。

三. 关于存储引擎的一些操作

查看当前服务器程序支持的存储引擎

1
SHOW ENGINES;

设置表的存储引擎

创建表时指定存储引擎

1
2
3
CREATE TABLE 表名(
    建表语句;
) ENGINE = 存储引擎名称;

修改表的存储引擎

1
ALTER TABLE 表名 ENGINE = 存储引擎名称;

四. 启动选项和配置文件

启动选项

长形式 短形式 含义
--host -h 主机名
--user -u 用户名
--password -p 密码
--port -P 端口
--version -V 版本信息
启动命令 类别 能读取的组
mysqld 启动服务器 [mysqld] 、 [server]
mysqld_safe 启动服务器 [mysqld] 、 [server] 、 [mysqld_safe]
mysql.server 启动服务器 [mysqld] 、 [server] 、 [mysql.server]
mysql 启动客户端 [mysql] 、 [client]
mysqladmin 启动客户端 [mysqladmin] 、 [client]
mysqldump 启动客户端 [mysqldump] 、 [client]

配置文件

在 Windows 操作系统中， MySQL 会按照下列路径来寻找配置文件：

路径名 备注
%WINDIR%\my.ini ， %WINDIR%\my.cnf
C:\my.ini ， C:\my.cnf
BASEDIR\my.ini ， BASEDIR\my.cnf
defaults-extra-file 命令行指定的额外配置文件路径
%APPDATA%\MySQL\.mylogin.cnf 登录路径选项（仅限客户端）

在类 UNIX 操作系统中， MySQL 会按照下列路径来寻找配置文件：

路径名 备注
/etc/my.cnf
/etc/mysql/my.cnf
SYSCONFDIR/my.cnf
$MYSQL_HOME/my.cnf 特定于服务器的选项（仅限服务器）
defaults-extra-file 命令行指定的额外配置文件路径
~/.my.cnf 用户特定选项
~/.mylogin.cnf 用户特定的登录路径选项（仅限客户端）

系统变量

查看系统变量

1
SHOW VARIABLES [LIKE 匹配的模式]; // 支持通配符模糊查询

状态变量

查看状态变量

1
SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式]; // 支持通配符模糊查询

注：

  • 系统变量和状态变量均有GLOBAL和SESSION作用范围
  • 默认为SESSION作用范围
  • 状态变量不可由用户更改，只能由服务器程序设置

字符集和比较规则

一. 常用字符集

不同字符集具有不同的编码规则：

  • ASCII 字符集
  • ISO 8859-1 字符集
  • GB2312 字符集
  • GBK 字符集
  • utf8 字符集

MySQL 有4个级别的字符集和比较规则，分别是：

  • 服务器级别
  • 数据库级别
  • 表级别
  • 列级别

MySQL 中 的默认字符集是utf8，utf8 是 utf8mb3 的别名。

二. 字符集的查看

1
SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];

三. 比较规则的查看

1
SHOW COLLATION [LIKE 匹配的模式];

四. 客户端和服务器通信中的字符集

从发送请求到接收结果过程中发生的字符集转换：

  • 客户端使用操作系统的字符集编码请求字符串，向服务器发送的是经过编码的一个字节串。
  • 服务器将客户端发送来的字节串采用 character_set_client 代表的字符集进行解码，将解码后的字符串再按照 character_set_connection 代表的字符集进行编码。
  • 如果 character_set_connection 代表的字符集和具体操作的列使用的字符集一致，则直接进行相应操作，否则的话需要将请求中的字符串从 character_set_connection 代表的字符集转换为具体操作的列使用的字符集之后再进行操作。
  • 将从某个列获取到的字节串从该列使用的字符集转换为 character_set_results 代表的字符集后发送到客户端。
  • 客户端使用操作系统的字符集解析收到的结果集字节串。

在这个过程中各个系统变量的含义如下：

系统变量 描述
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

处理流程：

InnoDB 记录结构

一. InnoDB 页简介

InnoDB 存储的方式是： 将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB 。也就是在一般情况下，一次最少从磁盘中读取 16 KB 的内容到内存中，一次最少把内存中的 16 KB 内容刷新到磁盘中。

二. InnoDB 行格式

指定行格式的语法

我们可以在创建或修改表的语句中指定 行格式 ：

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

COMPACT行格式

注：

  • 变长字段长度列表按照列的顺序逆序存放

  • NULL值列表将每个允许存储NULL值的列对应一个二进制位，按照列的顺序逆序排列

    • 二进制位的值为 1 时，代表该列的值为 NULL 。
    • 二进制位的值为 0 时，代表该列的值不为 NULL 。

  • 记录头信息

    名称 大小（单位：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 表示最小记录， 3 表示最大记录
    next_record 16 表示下一条记录的相对位置

  • 记录的真实数据会默认添加一些隐藏列

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

Redundant行格式

注：

  • 字段长度偏移列表

    对比Compact行格式：

    • 没有了 变长 两个字，意味着 Redundant 行格式会把该条记录中 所有列 （包括 隐藏列 ）的长度信息都按照 逆序 存储到 字段长度偏移列表 。
    • 多了个 偏移 两个字，这意味着计算列值长度的方式不像 Compact 行格式那么直观，它是采用两个相邻数值的 差值 来计算各个列值的长度。

  • 记录头信息

    名称 大小（单位：bit） 描述
    预留位1 1 没有使用
    预留位2 1 没有使用
    delete_mask 1 标记该记录是否被删除
    min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
    n_owned 4 表示当前记录拥有的记录数
    heap_no 13 表示当前记录在页面堆的位置信息
    n_field 10 表示记录中列的数量
    1byte_offs_flag 1 标记字段长度偏移列表中每个列对应的偏移量是使用1字节还是2字节表示的
    next_record 16 表示下一条记录的绝对位置

行溢出

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

Dynamic和Compressed行格式

Compressed 行格式和 Dynamic 不同的一点是， Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间。

InnoDB 数据页结构

一. 数据页结构

功能展示：

名称 中文名 占用空间大小 简单描述
File Header 文件头部 38 字节 页的一些通用信息
Page Header 页面头部 56 字节 数据页专有的一些信息
Infimum + Supremum 最小记录和最大记录 26 字节 两个虚拟的行记录
User Records 用户记录 不确定 实际存储的行记录内容
Free Space 空闲空间 不确定 页中尚未使用的空间
Page Directory 页面目录 不确定 页中的某些记录的相对位置
File Trailer 文件尾部 8 字节 校验页是否完整

小结：

  1. InnoDB为了不同的目的而设计了不同类型的页，我们把用于存放记录的页叫做 数据页 。
  2. 一个数据页可以被大致划分为7个部分，分别是
    • File Header ，表示页的一些通用信息，占固定的38字节。
    • Page Header ，表示数据页专有的一些信息，占固定的56个字节。
    • Infimum + Supremum ，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的 26 个字节。
    • User Records ：真实存储我们插入的记录的部分，大小不固定。
    • Free Space ：页中尚未使用的部分，大小不确定。
    • Page Directory ：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多。
    • File Trailer ：用于检验页是否完整的部分，占用固定的8个字节。
  3. 每个记录的头信息中都有一个 next_record 属性，从而使页中的所有记录串联成一个 单链表 。
  4. InnoDB 会把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个 槽 ，存放在 Page Directory 中，所以在一个页中根据主键查找记录是非常快的，分为两步：
    • 通过二分法确定该记录所在的槽。
    • 通过记录的next_record属性遍历该槽所在的组中的各个记录。
  5. 每个数据页的 File Header 部分都有上一个和下一个页的编号，所以所有的数据页会组成一个 双链表 。
  6. 为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的 LSN 值，如果首部和尾部的校验和和 LSN 值校验不成功的话，就说明同步过程出现了问题。

B+ 树索引

一. InnoDB 中的索引方案

索引结构（B+ 树）：

聚簇索引

两个特点：

  1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

    • 页内的记录是按照主键的大小顺序排成一个单向链表。
    • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

  2. B+ 树的叶子节点存储的是完整的用户记录。

    所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。我们把具有这两种特性的 B+ 树称为 聚簇索引 ，所有完整的用户记录都存放在这个 聚簇索引 的叶子节点处。这就是所谓的索引即数据，数据即索引。

二级索引

与聚簇索引区别：

  • 叶子节点只存放索引列和主键的值，并非完整的用户记录
  • 搜索完毕后若想得到完整的用户记录，还需要根据搜索得到的主键值到聚簇索引中再查找一遍，即回表

联合索引

与二级索引区别：

  • 建立联合索引只会建立一棵B+树
  • 若分别建立索引则会产生多棵B+树

唯一性

为了让新插入记录能找到自己在那个页里， 我们需要保证在B+树的同一层内节点的目录项记录除 页号 这个字段以外是唯一的 。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

  • 索引列的值
  • 主键值
  • 页号

有了主键值我们便能确保唯一性。

二. B+ 树索引的使用

小结：

  1. B+ 树索引在空间和时间上都有代价，所以没事儿别瞎建索引。
  2. B+ 树索引适用于下边这些情况：
    • 全值匹配
    • 匹配左边的列
    • 匹配范围值
    • 精确匹配某一列并范围匹配另外一列
    • 用于排序
    • 用于分组
  3. 在使用索引时需要注意下边这些事项：
    • 只为用于搜索、排序或分组的列创建索引
    • 为列的基数大的列创建索引
    • 索引列的类型尽量小
    • 可以只对字符串值的前缀建立索引
    • 只有索引列在比较表达式中单独出现才可以适用索引
    • 为了尽可能少的让 聚簇索引 发生页面分裂和记录移位的情况，建议让主键拥有 AUTO_INCREMENT 属性。
    • 定位并删除表中的重复和冗余索引
    • 尽量使用 覆盖索引 进行查询，避免 回表 带来的性能损耗。

MySQL 的数据目录

一. 数据目录

查看数据目录位置

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/var/mysql/ |
+---------------+-----------------------+
1 row in set (0.00 sec)

二. 数据目录的结构

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

    • 对应数据目录下的一个同名子目录
    • 在改子目录下创建一个名为 db.opt 的文件，用来描述数据库的属性

  • 表在文件系统中的表示

    • 对应数据库子目录下创建一个“表名.frm”文件，用来描述表的结构

    • InnoDB 存储表数据

      • 系统表空间（system tablespace）

        • 数据目录下有一个ibdata1文件，大小为12M，是自扩展文件
        • 在一个MySQL服务器中，系统表空间只有一份
        • 从MySQL5.5.7到MySQL5.6.6之间的各个版本中，我们表中的数据都会被默认存储到这个 系统表空间 。

      • 独立表空间(file-per-table tablespace)

        • 每个表对应一个独立表空间，文件名为“表名.ibd”

        • 移动表空间

          1
          ALTER TABLE 表名 TABLESPACE [=] innodb_system/innodb_file_per_table;

    • MyISAM 存储表数据

      对应数据库子目录下创建：

      1
      2
      3
      test.frm // 表结构信息
      test.MYD // 数据文件
      test.MYI // 索引文件

  • 视图在文件系统中的表示

    存储 视图 的时候是不需要存储真实的数据的， 只需要把它的结构存储起来就行了 。

    对应数据库子目录下创建一个 视图名.frm 的文件。

三. 文件系统对数据库的影响

  • 数据库名称和表名称不得超过文件系统所允许的最大长度。

  • 特殊字符的问题

    特殊字符会被映射成编码值

  • 文件长度受文件系统最大长度限制

四. MySQL 系统数据库

  • mysql

    这个数据库贼核心，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

  • information_schema

    这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引吧啦吧啦。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。

  • performance_schema

    这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。

  • sys

    这个数据库主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，让程序员可以更方便的了解MySQL服务器的一些性能信息。

InnoDB 的表空间

页面通用部分

一. 独立表空间结构

1. 区（extent）

对于16KB的页来说，连续的64个页就是一个 区 ，也就是说一个区默认占用1MB空间大小。不论是系统表空间还是独立表空间，都可以看成是由若干个区组成的，每256个区被划分成一组。画个图表示就是这样：

这些组的头几个页面的类型都是类似的，就像这样：

信息：

  • FSP_HDR 类型：登记整个表空间的一些整体属性以及本组所有的区的属性，整个表空间只有一个 FSP_HDR 类型的页面。
  • XDES 类型：登记本组所有区的属性。
  • IBUF_BITMAP 类型：存储本组所有的区的所有页面关于 INSERT BUFFER 的信息。
  • INODE 类型：存储了许多称为 INODE 的数据结构。

2. 段（segment）

叶子节点有自己独有的 区 ，非叶子节点也有自己独有的 区 。存放叶子节点的区的集合就算是一个 段 （ segment ），存放非叶子节点的区的集合也算是一个 段 。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。

碎片区：在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是可以用于不同的段。

段分配存储空间的策略：

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

3. 区的分类

状态名 含义
FREE 空闲的区
FREE_FRAG 有剩余空间的碎片区
FULL_FRAG 没有剩余空间的碎片区
FSEG 附属于某个段的区

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

1. XDES Entry 结构

记录对应的区的一些属性：

  • Segment ID （8字节）

    表示段的唯一编号。

  • List Node （12字节）

    定位表空间的某一个位置。

  • State （4字节）

    表明区的状态。

  • Page State Bitmap （16字节）

    一个区默认有64个页，这128个比特位被划分为64个部分，每个部分2个比特位，对应区中的一个页。

    这两个比特位的第一个位表示对应的页是否是空闲的，第二个比特位还没有用。

2. XDES Entry 链表

对于 FREE 、 FREE_FRAG 、 FULL_FRAG 三种区，分别通过List Node 建立链表。

对于FSEG，对每个不同的段分别建立以下三个链表：

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

为了寻找到某个链表的头节点或尾节点，产生了 List Base Node 结构：

一般我们把某个链表对应的 List Base Node 结构放置在表空间中固定的位置，这样想定位某个链表就变得很容易了。

4. 链表小结

综上所述，表空间是由若干个区组成的，每个区都对应一个 XDES Entry 的结构，直属于表空间的区对应的 XDES Entry 结构可以分成 FREE 、 FREE_FRAG 和 FULL_FRAG 这3个链表；每个段可以附属若干个区，每个段中的区对应的 XDES Entry 结构可以分成 FREE 、 NOT_FULL 和 FULL 这3个链表。每个链表都对应一个 List Base Node 的结构，这个结构里记录了链表的头、尾节点的位置以及该链表中包含的节点数。正是因为这些链表的存在，管理这些区才变成了一件容易的事情。

4. 段的结构

段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。像每个区都有对应的 XDES Entry 来记录这个区中的属性一样，设计 InnoDB 的大叔为每个段都定义了一个 INODE Entry 结构来记录一下段中的属性。

  • Segment ID

    段的唯一编号。

  • NOT_FULL_N_USED

    这个字段指的是在 NOT_FULL 链表中已经使用了多少个页面。

  • 3个 List Base Node

    分别为段的 FREE 链表、 NOT_FULL 链表、 FULL 链表定义了 List Base Node 。

  • Magic Number ：

    这个值是用来标记这个 INODE Entry 是否已经被初始化了（初始化的意思就是把各个字段的值都填进去了）。如果这个值是 97937874 ，表明该 INODE Entry 已经初始化，否则没有被初始化。（不用纠结这个值有啥特殊含义，人家规定的）。

  • Fragment Array Entry

    我们前边强调过无数次段是一些零散页面和一些完整的区的集合，每个 Fragment Array Entry 结构都对应着一个零散的页面，这个结构一共4个字节，表示一个零散页面的页号。

5. 各类型页面详细情况

1. FSP_HDR 类型

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_FRAG链表的基节点
List Base Node for FULL_FRAG List 16 字节 FULL_FRAG链表的基节点
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链表的基节点

  • FRAG_N_USED

    这个字段表明在 FREE_FRAG 链表中已经使用的页面数量。

  • FREE Limit

    在该字段表示的页号之前的区都被初始化了，之后的区尚未被初始化。

  • Next Unused Segment ID

    该字段表明当前表空间中最大的段ID的下一个ID，这样在创建新段的时候赋予新段一个唯一的ID值就so easy啦，直接使用这个字段的值就好了。

  • Space Flags

    表空间对于一些布尔类型的属性。

  • List Base Node for SEG_INODES_FULL List 和 List Base Node for SEG_INODES_FREE List

    每个段对应的 INODE Entry 结构会集中存放到一个类型为 INODE 的页中，如果表空间中的段特别多，则会有多个 INODE Entry 结构，可能一个页放不下，这些 INODE 类型的页会组成两种列表：

    • SEG_INODES_FULL 链表，该链表中的 INODE 类型的页面都已经被 INODE Entry 结构填充满了，没空闲空间存放额外的 INODE Entry 了。
    • SEG_INODES_FREE 链表，该链表中的 INODE 类型的页面仍有空闲空间来存放 INODE Entry 结构。
2. XDES 类型

与 FSP_HDR 类型的页面对比，除了少了 File Space Header 部分之外，也就是除了少了记录表空间整体属性的部分之外，其余的部分是一样一样的。

3. IBUF_BITMAP 类型

这种类型的页里边记录了一些有关 Change Buffer 的信息。

4. INODE 类型

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

6. Segment Header 结构的运用

索引页中有一个Page Header部分：

Page Header部分 （为突出重点，省略了好多属性）

名称 占用空间大小 描述
… … …
PAGE_BTR_SEG_LEAF 10 字节 B+树叶子段的头部信息，仅在B+树的根页定义
PAGE_BTR_SEG_TOP 10 字节 B+树非叶子段的头部信息，仅在B+树的根页定义

对应的结构：

各个部分的具体释义如下：

名称 占用字节数 描述
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结构在该页面中的偏移量

这样便可以知道哪个段对应哪个 INODE Entry 结构。

二. 系统表空间

1. 系统表空间的整体结构

页号 页面类型 英文描述 描述
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 数据字典头部信息

2. 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存储引擎中所有的虚拟生成列的信息

为了存储获取到数据字典的信息，还需要数据字典的头部信息，即 Data Dictionary Header ：

  • Max Row ID ：我们说过如果我们不显式的为表定义主键，而且表中也没有 UNIQUE 索引，那么 InnoDB 存储引擎会默认为我们生成一个名为 row_id 的列作为主键。因为它是主键，所以每条记录的 row_id 列的值不能重复。原则上只要一个表中的 row_id 列不重复就可以了，也就是说表a和表b拥有一样的 row_id 列也没啥关系，不过设计InnoDB的大叔只提供了这个 Max Row ID 字段，不论哪个拥有 row_id 列的表插入一条记录时，该记录的 row_id 列的值就是 Max Row ID 对应的值，然后再把 Max Row ID 对应的值加1，也就是说这个 Max Row 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 ：这4个字节没用，跳过。

三. 总结图

单表访问方法

一条查询语句经过查询优化器优化后生成执行计划，最后根据执行计划调用存储引擎提供的方法进行查询

一. 访问方法（access method）的概念

设计 MySQL 的大叔把 MySQL 执行查询语句的方式称之为 访问方法 或者 访问类型 。一条语句可以使用不同的访问方法来执行，但最终的查询结果是一样的。

1. const

通过主键或者唯一二级索引列来定位一条记录的访问方法定义为： const ，意思是常数级别的，代价是可以忽略不计的。

条件：

  • 只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效
  • 主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。

对于NULL值，由于可能访问到多条记录，因而无法使用const方法。

2. ref

搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为： ref 。

注意两种情况：

  • 二级索引列值为 NULL 的情况

    最多只能使用ref访问方法，而不是const访问方法。

  • 对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用 ref 的访问方法。

3. range

利用索引进行范围匹配的访问方法称之为： range 。

索引列等值匹配的情况称之为 单点区间 ，否则称为连续范围区间。

4. index

条件：

  • 查询列表中只包含某个索引的列，而不包含其它不属于该索引的列
  • 搜索条件中的列部分或全部包含在该索引中

此时我们只需要遍历该索引的B+ 树，找到满足条件的记录，又因为我们只需查询出指定的几个列，而且这几个列又恰好在索引中，那么直接将查询出的记录中提取出所需列的值加入到结果集中即可，这样无需进行回表操作，成本小很多，这种方式称为：index。

5. all

使用全表扫描执行查询的方式称之为： all 。

二. 注意事项

1. 单个索引

一般情况下，优化器只会利用单个二级索引执行查询，然后再从查询结果中根据其它条件进行过滤，最后返回结果。

range 的范围区间

其实对于 B+ 树索引来说，只要索引列和常数使用 = 、 <=> 、 IN 、 NOT IN 、 IS NULL 、 IS NOT NULL 、 > 、 < 、 >= 、 <= 、 BETWEEN 、 != （不等于也可以写成 <> ）或者 LIKE 操作符连接起来，就可以产生一个所谓的 区间 。

LIKE操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引

IN操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样的，也就是说会产生多个单点区间

优化查询时先把其它用不到索引的搜索条件替换为 TRUE ，即不考虑其对查询的影响，最后再过滤。

2. 索引合并

某些特殊情况下，也可能在一个查询中使用到多个二级索引。

Intersection 合并

比方说下边这个查询：

1
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

假设这个查询使用 Intersection 合并的方式执行的话，那这个过程就是这样的：

  • 从 idx_key1 二级索引对应的 B+ 树中取出 key1 = 'a' 的相关记录。
  • 从 idx_key3 二级索引对应的 B+ 树中取出 key3 = 'b' 的相关记录。
  • 二级索引的记录都是由 索引列 + 主键 构成的，所以我们可以计算出这两个结果集中 id 值的交集。
  • 按照上一步生成的 id 值列表进行回表操作，也就是从聚簇索引中把指定 id 值的完整用户记录取出来，返回给用户。

读取多个二级索引得到的记录数肯定不多于读取一个二级索引得到的记录数。

若 回表 造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

条件：

  • 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

    这样可以在保证二级索引列值相同的情况下，查询的记录按照主键值进行排序，取交集时无需排序，性能更高。

  • 主键列可以是范围匹配

    此时没必要用到主键索引，只需在二级索引查询出的记录中根据主键列的范围进行过滤即可。

这些只是发生 Intersection 索引合并的必要条件，不是充分条件。

优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过 Intersection 索引合并后需要回表的记录数大大减少时才会使用 Intersection 索引合并。

Union 合并

条件同Intersection合并相似，均为必要条件，不是充分条件。

优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过 Union 索引合并后进行访问的代价比全表扫描更小时才会使用 Union 索引合并。

Sort-Union 合并

Sort-Union 索引合并比单纯的 Union 索引合并多了一步对二级索引记录的主键值排序的过程。

这样也拓宽了合并范围，不必满足等值匹配。

为啥有Sort-Union索引合并，就没有Sort-Intersection索引合并么？是的，的确没有Sort-Intersection索引合并这么一说，

Sort-Union的适用场景是单独根据搜索条件从某个二级索引中获取的记录数比较少，这样即使对这些二级索引记录按照主键值进行排序的成本也不会太高

而Intersection索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，合并后可以明显降低回表开销，但是如果加入Sort-Intersection后，就需要为大量的二级索引记录按照主键值进行排序，这个成本可能比回表查询都高了，所以也就没有引入Sort-Intersection这个玩意儿。

联合索引替代Intersection索引合并

在搜索条件中出现的多个二级索引在一定条件下如果可以合并成一个联合索引，那么可以直接使用该联合索引进行查询，这样无需访问多棵B+ 树，减少开销。

如果有对某个索引列进行单独查询的情况或者范围匹配等情况，尽量避免替代索引。

连接的原理

一. 连接简介

1. 连接的本质

连接 的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。

连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为 笛卡尔积 。

2. 连接过程

以两表查询为例：

第一个需要查询的表为驱动表，将其单表查询后得出的结果集放到第二个表中过滤查询，第二个表被称为被驱动表，最后返回结果。

在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

内连接和外连接

本质：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集 。

  • 对于 内连接 的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的 内连接 。

  • 对于 外连接 的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

    在 MySQL 中，根据选取驱动表的不同，外连接仍然可以细分为2种：

    • 左外连接

      选取左侧的表为驱动表。

    • 右外连接

      选取右侧的表为驱动表。

过滤条件：

  • WHERE 子句中的过滤条件

    WHERE 子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。

  • ON 子句中的过滤条件

    对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充。

    内连接条件下，WHERE子句和ON子句是等价的 。

一般情况下，我们都把只涉及单表的过滤条件放到 WHERE 子句中，把涉及两表的过滤条件都放到 ON 子句中，我们也一般把放到 ON 子句中的过滤条件也称之为 连接条件 。

外连接必须使用 ON 子句来指出连接条件 。

语法

1
2
3
SELECT * FROM 表名 [INNER | CROSS] JOIN 表名 [ON 连接条件] [WHERE 普通过滤条件]; // 内连接
SELECT * FROM 表名 LEFT [OUTER] JOIN 表名 ON 连接条件 [WHERE 普通过滤条件]; // 左连接
SELECT * FROM 表名 RIGHT [OUTER] JOIN 表名 ON 连接条件 [WHERE 普通过滤条件]; // 右连接

注意事项

  • 由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句
  • 对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果
  • 左外连接和右外连接的驱动表和被驱动表不能轻易互换

3. 连接的原理

嵌套循环连接（Nested-Loop Join）

驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数 的连接执行方式称之为 嵌套循环连接 （ Nested-Loop Join ），这是最简单，也是最笨拙的一种连接查询算法。

基于块的嵌套循环连接（Block Nested-Loop Join）

为了尽量减少访问被驱动表的次数，设计 MySQL 的大叔提出了一个 join buffer 的概念， join buffer 就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个 join buffer 中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和 join buffer 中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的 I/O 代价。使用 join buffer 的过程如下图所示：

最好的情况是 join buffer 足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。设计 MySQL 的大叔把这种加入了 join buffer 的嵌套循环连接算法称之为 基于块的嵌套连接 （Block Nested-Loop Join）算法。

这个 join buffer 的大小是可以通过启动参数或者系统变量 join_buffer_size 进行配置，默认大小为 262144字节 （也就是 256KB ），最小可以设置为 128字节 。当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大 join_buffer_size 的值来对连接查询进行优化。

MySQL 基于成本的优化

在 MySQL 中一条查询语句的执行成本由两个方面组成：

  • I/O 成本（成本常数 1.0）

    从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。

  • CPU 成本（成本常数 0.2）

    读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。

一. 单表查询的成本

1. 优化步骤

流程：

  1. 根据搜索条件，找出所有可能使用的索引
  2. 计算全表扫描的代价
  3. 计算使用不同索引执行查询的代价
  4. 对比各种执行方案的代价，找出成本最低的那一个
1. 根据搜索条件，找出所有可能使用的索引

搜索条件中可能使用到的索引称为possible keys。

2. 计算全表扫描的代价

计算全表扫描的代价需要两个信息：

  • 聚簇索引占用的页面数
  • 该表中的记录数

查看表的统计信息

1
SHOW TABLE STATUS LIKE 表名;

  • I/O 成本

    1
    聚簇索引占用的页面数 × 1.0 + 1.1

    1.0 为成本常数，1.1 是规定的微调值。

  • CPU 成本

    1
    查询出的记录总数 × 0.2 + 1.0

    0.2 为成本常数，1.0 是规定的微调值。

3. 计算使用不同索引执行查询的代价

计算这种查询的成本依赖两个方面的数据：

  • 范围区间数量

    1
    区间数量 × 1.0

    查询优化器粗暴的认为读取索引的一个范围区间的 I/O 成本和读取一个页面是相同的。

  • 需要回表的记录数

    1
    记录总数 × 0.2 + 0.01

    0.01 是规定的微调值。

在通过二级索引获取到记录之后，还需要干两件事儿：

  • 根据这些记录里的主键值到聚簇索引中做回表操作

    1
    二级索引记录数 × 1.0

  • 回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立

    1
    记录数 × 0.2
4. 对比各种执行方案的代价，找出成本最低的那一个

2. 基于索引统计数据的成本计算

通过直接访问索引对应的 B+ 树来计算某个范围区间对应的索引记录条数的方式称之为 index dive 。

当区间过多时，计算这些区间对应的记录条数成本可能非常大，因而我们需要采取索引统计数据的方法。

查看索引统计数据

1
SHOW INDEX FROM 表名;
属性名 描述
Table 索引所属表的名称。
Non_unique 索引列的值是否是唯一的，聚簇索引和唯一二级索引的该列值为 0 ，普通二级索引该列值为 1 。
Key_name 索引的名称。
Seq_in_index 索引列在索引中的位置，从1开始计数。比如对于联合索引 idx_key_part ，来说， key_part1 、 key_part2 和 key_part3 对应的位置分别是1、2、3。
Column_name 索引列的名称。
Collation 索引列中的值是按照何种排序方式存放的，值为 A 时代表升序存放，为 NULL 时代表降序存放。
Cardinality 索引列中不重复值的数量。后边我们会重点看这个属性的。（估计值，即基数）
Sub_part 对于存储字符串或者字节串的列来说，有时候我们只想对这些串的前 n 个字符或字节建立索引，这个属性表示的就是那个 n 值。如果对完整的列建立索引的话，该属性的值就是 NULL 。
Packed 索引列如何被压缩， NULL 值表示未被压缩。这个属性我们暂时不了解，可以先忽略掉。
Null 该索引列是否允许存储 NULL 值。
Index_type 使用索引的类型，我们最常见的就是 BTREE ，其实也就是 B+ 树索引。
Comment 索引列注释信息。
Index_comment 索引注释信息。

这里所指的 索引统计数据 指的是这两个值：

  • 使用 SHOW TABLE STATUS 展示出的 Rows 值，也就是一个表中有多少条记录。

  • 使用 SHOW INDEX 语句展示出的 Cardinality 属性。

    结合上一个 Rows 统计数据，我们可以针对索引列，计算出平均一个值重复多少次。

    1
    一个值的重复次数 ≈ Rows ÷ Cardinality

二. 连接查询的成本

Condition filtering

我们把对驱动表进行查询后得到的记录条数称之为驱动表的 扇出 （英文名： fanout ）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。

这个过程即为Condition filtering

两表连接的成本分析

连接查询的成本计算公式是这样的：

1
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

对于外连接，考虑：

  • 分别为驱动表和被驱动表选择成本最低的访问方法。

对于内连接，考虑：

  • 不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。
  • 然后分别为驱动表和被驱动表选择成本最低的访问方法。

连接查询成本占大头的其实是 驱动表扇出数 x 单次访问被驱动表的成本 ，所以我们的优化重点其实是下边这两个部分：

  • 尽量减少驱动表的扇出

  • 对被驱动表的访问成本尽量低

    尽量在被驱动表的连接列上建立索引

多表连接的成本分析

减少计算非常多种连接顺序的成本的方法：

  • 提前结束某种顺序的成本评估

    维护一个全局的变量，这个变量表示当前最小的连接查询成本。若在分析某个连接顺序的成本时，其成本已经超过当前最小的连接查询成本，则无需再往下分析。

  • 系统变量 optimizer_search_depth

    为了防止无穷无尽的分析各种连接顺序的成本，设计 MySQL 的大叔们提出了 optimizer_search_depth 系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与 optimizer_search_depth 值相同数量的表进行穷举分析。

  • 根据某些规则压根儿就不考虑某些连接顺序

    根据一些启发式规则直接过滤掉一些连接顺序。系统变量 optimizer_prune_level 来控制到底是不是用这些启发式规则。

三. 调节成本常数

成本常数被存储到了mysql数据库（一个系统数据库）。

查看成本常数

1
SHOW TABLES FROM mysql LIKE '%cost%';

修改成本常数

修改指定表的内容，然后重新加载这个表：

1
FLUSH OPTIMIZER_COSTS; // 重新加载

主要分为server层和engine层：

  • server

    主要是创建临时表的成本

  • engine

    主要是读取块的成本

InnoDB 统计数据是如何收集的

两种不同的统计数据存储方式：

  • 永久性的统计数据

    这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。

  • 非永久性的统计数据

    这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

系统变量 innodb_stats_persistent 控制到底采用哪种方式去存储统计数据。

InnoDB 默认是 以表为单位来收集和存储统计数据的。

创建和修改表的时候通过指定 STATS_PERSISTENT 属性来指明该表的统计数据存储方式：

1
2
3
CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);

ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

1 表示永久，0 表示非永久，如果我们在创建表时未指定 STATS_PERSISTENT 属性，那默认采用系统变量 innodb_stats_persistent 的值作为该属性的值。

一. 基于磁盘的永久性统计数据

mysql 系统数据库下的两个表：

  • innodb_table_stats 存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
  • innodb_index_stats 存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。

innodb_table_stats

字段名 描述
database_name 数据库名
table_name 表名
last_update 本条记录最后更新时间
n_rows 表中记录的条数
clustered_index_size 表的聚簇索引占用的页面数量
sum_of_other_index_sizes 表的其他索引占用的页面数量

注意这个表的主键是 (database_name,table_name) ，也就是 innodb_table_stats表的每条记录代表着一个表的统计信息 。

n_rows统计项的收集

按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的 n_rows 值。

innodb_stats_persistent_sample_pages 系统变量来控制 使用永久性的统计数据时，计算统计数据时采样的页面数量 。

创建或修改表的时候通过指定 STATS_SAMPLE_PAGES 属性来指明该表的统计数据存储方式：

1
2
3
CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;
clustered_index_size和sum_of_other_index_sizes统计项的收集

收集过程：

  • 从数据字典里找到表的各个索引对应的根页面位置。
  • 从根页面的 Page Header 里找到叶子节点段和非叶子节点段对应的 Segment Header 。
  • 从叶子节点段和非叶子节点段的 Segment Header 中找到这两个段对应的 INODE Entry 结构。
  • 从对应的 INODE Entry 结构中可以找到该段对应所有零散的页面地址以及 FREE 、 NOT_FULL 、 FULL 链表的基节点。
  • 直接统计零散的页面有多少个，然后从那三个链表的 List Length 字段中读出该段占用的区的大小，每个区占用 64 个页，所以就可以统计出整个段占用的页面。
  • 分别计算聚簇索引的叶子结点段和非叶子节点段占用的页面数，它们的和就是 clustered_index_size 的值，按照同样的套路把其余索引占用的页面数都算出来，加起来之后就是 sum_of_other_index_sizes 的值。

innodb_index_stats

字段名 描述
database_name 数据库名
table_name 表名
index_name 索引名
last_update 本条记录最后更新时间
stat_name 统计项的名称
stat_value 对应的统计项的值
sample_size 为生成统计数据而采样的页面数量
stat_description 对应的统计项的描述

注意这个表的主键是 (database_name,table_name,index_name,stat_name) ，其中的 stat_name 是指统计项的名称，也就是说 innodb_index_stats表的每条记录代表着一个索引的一个统计项 。

  • n_leaf_pages ：表示该索引的叶子节点占用多少页面。
  • size ：表示该索引共占用多少页面。
  • n_diff_pfx NN ：表示对应的索引列不重复的值有多少。
  • 在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样， sample_size 列就表明了采样的页面数量是多少。

定期更新统计数据

  • 开启 innodb_stats_auto_recalc 。

    如果发生变动的记录数量超过了表大小的 10% ，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进行一次统计数据的计算，并且更新 innodb_table_stats 和 innodb_index_stats 表。不过 自动重新计算统计数据的过程是异步发生的 ，也就是即使表中变动的记录数超过了 10% ，自动重新计算统计数据也不会立即发生，可能会延迟几秒才会进行计算。

  • 手动调用 ANALYZE TABLE 语句来更新统计信息

    ANALYZE TABLE语句会立即重新计算统计数据，也就是这个过程是同步的 ，在表中索引多或者采样页面特别多时这个过程可能会特别慢.

手动更新 innodb_table_stats 和 innodb_index_stats 表

更新指定表的内容，然后重新加载表：

1
FLUSH TABLE 表名;

二. 基于内存的非永久性统计数据

与永久性的统计数据不同，非永久性的统计数据采样的页面数量是由 innodb_stats_transient_sample_pages 控制的，这个系统变量的默认值是 8 。

对于NULL值的处理：

  • nulls_equal ：认为所有 NULL 值都是相等的。这个值也是 innodb_stats_method 的默认值。
  • nulls_unequal ：认为所有 NULL 值都是不相等的。
  • nulls_ignored ：直接把 NULL 值忽略掉。

最好不在索引列中存放NULL值才是正解。

总结

  • InnoDB 以表为单位来收集统计数据，这些统计数据可以是基于磁盘的永久性统计数据，也可以是基于内存的非永久性统计数据。
  • innodb_stats_persistent 控制着使用永久性统计数据还是非永久性统计数据； innodb_stats_persistent_sample_pages 控制着永久性统计数据的采样页面数量； innodb_stats_transient_sample_pages 控制着非永久性统计数据的采样页面数量； innodb_stats_auto_recalc 控制着是否自动重新计算统计数据。
  • 我们可以针对某个具体的表，在创建和修改表时通过指定 STATS_PERSISTENT 、 STATS_AUTO_RECALC 、 STATS_SAMPLE_PAGES 的值来控制相关统计数据属性。
  • innodb_stats_method 决定着在统计某个索引列不重复值的数量时如何对待 NULL 值。

MySQL 基于规则的优化

一. 条件化简

  • 移除不必要的括号

  • 常量传递（constant_propagation）

  • 等值传递（equality_propagation）

  • 移除没用的条件（trivial_condition_removal）

  • 表达式计算

    如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，就像这样：

    1
    ABS(a) > 5

    或者：

    1
    -a < -8

    优化器是不会尝试对这些表达式进行化简的 。

  • HAVING子句和WHERE子句的合并

    如果查询语句中没有出现诸如 SUM 、 MAX 等等的聚集函数以及 GROUP BY 子句，优化器就把 HAVING 子句和 WHERE 子句合并起来。

  • 常量表检测

    • 查询的表中一条记录没有，或者只有一条记录。
    • 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。

    设计 MySQL 的大叔觉得这两种查询花费的时间特别少，少到可以忽略，所以也把通过这两种方式查询的表称之为 常量表 （英文名： constant tables ）。优化器在分析一个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本。

  • 外连接消除

    外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃 。

    在外连接查询中，指定的 WHERE 子句中包含被驱动表中的列不为 NULL 值的条件称之为 空值拒绝 （英文名： reject-NULL ）。 在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换 。

二. 子查询优化

1. 按返回的结果集区分子查询

  • 标量子查询

    示例：SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);

    只返回一个单一值的子查询称之为 标量子查询 。

  • 行子查询

    示例：SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

    返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。

  • 列子查询

    示例：SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);

    查询出一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。

  • 表子查询

    示例：SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);

    子查询的结果既包含很多条记录，又包含很多个列。

2. 按与外层查询关系来区分子查询

  • 相关子查询

    如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为 不相关子查询 。

  • 不相关子查询

    如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为 相关子查询 。

3. 子查询在布尔表达式中的使用

  • 使用 = 、 > 、 < 、 >= 、 <= 、 <> 、 != 、 <=> 作为布尔表达式的操作符

    操作数 comparison_operator (子查询)

    这里的子查询只能是标量子查询或者行子查询，也就是子查询的结果只能返回一个单一的值或者只能是一条记录 。

  • [NOT] IN/ANY/SOME/ALL子查询

    • IN 或者 NOT IN

      1
      操作数 [NOT] IN (子查询)

    • ANY/SOME （ ANY 和 SOME 是同义词）

      1
      操作数 comparison_operator ANY/SOME(子查询)

    • ALL

      1
      操作数 comparison_operator ALL(子查询)

    • EXISTS子查询

      1
      [NOT] EXISTS (子查询)

注意事项

  • 子查询必须用小括号扩起来。
  • 在 SELECT 子句中的子查询必须是标量子查询。
  • 在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用 LIMIT 1 语句来限制记录数量。
  • 对于 [NOT] IN/ANY/SOME/ALL 子查询来说，子查询中不允许有 LIMIT 语句。
  • 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询。

4. 子查询的执行

不相关子查询：

  • 先单独执行这个子查询。
  • 将上一步得到的结果当作外层查询的参数再执行外层查询 。

相关子查询：

  • 先从外层查询中获取一条记录。
  • 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，然后执行子查询。
  • 最后根据子查询的查询结果来检测外层查询 WHERE 子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
  • 再次执行第一步，获取第二条外层查询中的记录，依次类推～

5. IN子查询优化

物化表的提出

不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里 。写入临时表的过程是这样的：

  • 该临时表的列就是子查询结果集中的列。

  • 写入临时表的记录会被去重。

  • 一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。

    如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size ，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+ 树索引。

这个将子查询结果集中的记录保存到临时表的过程称之为 物化 （英文名： Materialize ）。为了方便起见，我们就把那个存储子查询结果集的临时表称之为 物化表 。由于物化表索引的存在，子查询语句性能等到提升。

物化表转连接

子查询结果集转成物化表后就相当于两表查询，某些情况下可以转换成连接。

子查询转换为semi-join

半连接 （英文名： semi-join ）：对于一个表的某条记录来说，我们只关心在另一个表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留第一个表的记录 。

实现方法：

  • Table pullout （子查询中的表上拉）

    当 子查询的查询列表处只有主键或者唯一索引列 时，可以直接把子查询中的表 上拉 到外层查询的 FROM 子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中。

  • DuplicateWeedout execution strategy （重复值消除）

    对于这个查询来说：

    1
    2
    SELECT * FROM s1 
        WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

    转换为半连接查询后， s1 表中的某条记录可能在 s2 表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，我们可以建立一个临时表，比方说这个临时表长这样：

    1
    2
    3
    CREATE TABLE tmp (
        id PRIMARY KEY
    );

    这样在执行连接查询的过程中，每当某条 s1 表中的记录要加入结果集时，就首先把这条记录的 id 值加入到这个临时表里，如果添加成功，说明之前这条 s1 表中的记录并没有加入最终的结果集，现在把该记录添加到最终的结果集；如果添加失败，说明之前这条 s1 表中的记录已经加入过最终的结果集，这里直接把它丢弃就好了，这种使用临时表消除 semi-join 结果集中的重复值的方式称之为 DuplicateWeedout 。

  • LooseScan execution strategy （松散扫描）

    扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为 松散扫描 。

  • Semi-join Materialization execution strategy

    即物化表。

  • FirstMatch execution strategy （首次匹配）

    即相关子查询的执行方式。

    由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询。

semi-join的适用条件
  • 该子查询必须是和 IN 语句组成的布尔表达式，并且在外层查询的 WHERE 或者 ON 子句中出现。
  • 外层查询也可以有其他的搜索条件，只不过和 IN 子查询的搜索条件必须使用 AND 连接起来。
  • 该子查询必须是一个单一的查询，不能是由若干查询由 UNION 连接起来的形式。
  • 该子查询不能包含 GROUP BY 或者 HAVING 语句或者聚集函数。
不适用于semi-join的情况
  • 外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用 OR 连接起来
  • 使用 NOT IN 而不是 IN 的情况
  • 在 SELECT 子句中的IN子查询的情况
  • 子查询中包含 GROUP BY 、 HAVING 或者聚集函数的情况
  • 子查询中包含 UNION 的情况

对于不能转为 semi-join 查询的子查询：

  • 对于不相关子查询来说，可以尝试把它们物化之后再参与查询
  • 不管子查询是相关的还是不相关的，都可以把 IN 子查询尝试转为 EXISTS 子查询
小结

  • 如果 IN 子查询符合转换为 semi-join 的条件，查询优化器会优先把该子查询转换为 semi-join ，然后再考虑下边5种执行半连接的策略中哪个成本最低：

    • Table pullout
    • DuplicateWeedout
    • LooseScan
    • Materialization
    • FirstMatch

    选择成本最低的那种执行策略来执行子查询。

  • 如果 IN 子查询不符合转换为 semi-join 的条件，那么查询优化器会从下边两种策略中找出一种成本更低的方式执行子查询：

    • 先将子查询物化之后再执行查询
    • 执行 IN to EXISTS 转换。

6. 派生表优化

子查询放在外层查询的 FROM 子句后，那么这个子查询的结果相当于一个 派生表 。

  • 派生表物化
  • 将派生表和外层的表合并，也就是将查询重写为没有派生表的形式

Explain 详解

Explain 输出列作用：

列名 描述
id 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id
select_type SELECT 关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

一. 执行计划输出中各列详解

table

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

id

查询语句中每出现一个 SELECT 关键字，优化器就会为它分配一个唯一的 id 值。

对于连接查询来说，一个 SELECT 关键字后边的 FROM 子句中可以跟随多个表，所以在连接查询的执行计划中， 每个表都会对应一条记录，但是这些记录的id值都是相同的。在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表。

对于包含子查询的查询语句来说，就可能涉及多个 SELECT 关键字，所以在包含子查询的查询语句的执行计划中，每个 SELECT 关键字都会对应一个唯一的 id 值。需要注意的是，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询，此时它们的id值便是相同的，因此可以判断优化器是否将子查询转化成了连接查询。

对于UNION查询，需要将多个查询的结果集合并起来并去重，需要构建临时表，因而执行计划中会产生一个id为NULL的记录，但对于UNION ALL则无需去重。

select_type

名称 描述
SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT
UNION Second or later SELECT statement in a UNION
UNION RESULT Result of a UNION
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
DERIVED Derived table
MATERIALIZED Materialized subquery
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

  • SIMPLE

    查询语句中不包含 UNION 或者子查询的查询都算作是 SIMPLE 类型

  • PRIMARY

    对于包含 UNION 、 UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的 select_type 值就是 PRIMARY

  • UNION

    对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的 select_type 值就是 UNION

  • UNION RESULT

    使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT

  • SUBQUERY

    如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 SUBQUERY。

    由于select_type为SUBQUERY的子查询会被物化，所以只需要执行一遍。

  • DEPENDENT SUBQUERY

    如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是相关子查询，则该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY 。

    select_type为DEPENDENT SUBQUERY的查询可能会被执行多次。

  • DEPENDENT UNION

    在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type 的值就是 DEPENDENT UNION

  • DERIVED

    对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED

  • MATERIALIZED

    当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的 select_type 属性就是 MATERIALIZED

partitions

一般情况下为NULL。

type

type 列表明单表访问方法，完整的单表访问方法： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

  • system

    当表中只有一条记录并且 该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory ，那么对该表的访问方法就是 system 。

  • const

    根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const

  • eq_ref

    在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref

  • ref

    通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就 可能 是 ref

  • fulltext

    全文索引

  • ref_or_null

    当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就 可能 是 ref_or_null

  • index_merge

    索引合并

  • unique_subquery

    类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery

  • index_subquery

    index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引

  • range

    如果使用索引获取某些 范围区间 的记录，那么就 可能 使用到 range 访问方法

  • index

    当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index

  • ALL

    全表扫描

一般来说，这些访问方法按照我们介绍它们的顺序性能依次变差。其中除了 All 这个访问方法外，其余的访问方法都能用到索引，除了 index_merge 访问方法外，其余的访问方法都最多只能用到一个索引。

possible_keys和key

注意事项

  • 在使用 index 访问方法来查询某个表时， possible_keys 列是空的，而 key 列展示的是实际使用到的索引
  • possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引

key_len

key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：

  • 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占用的最大存储空间就是 100 × 3 = 300 个字节。
  • 如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节。
  • 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const 、 eq_ref 、 ref 、 ref_or_null 、 unique_subquery 、 index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的东东是个啥，比如只是一个常数或者是某个列。

rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。

filtered

估计扇出值。

1
扇出值 = rows × filtered%

Extra

  • No tables used

    查询语句的没有 FROM 子句时将会提示该额外信息

  • Impossible WHERE

    查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息

  • No matching min/max row

    查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该额外信息

  • Using index

    当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在 Extra 列将会提示该额外信息。

  • Using index condition

    有些搜索条件中虽然出现了索引列，但却并不能用来形成范围区间，也就是不能被用来减少需要扫描的记录数量，通常情况下我们是通过索引回表查询出所有信息然后过滤，但这样会增加很多I/0开销。

    索引条件下推 （英文名： Index Condition Pushdown ）特性，通过二级索引查询出的记录中直接进行过滤，然后再回表得到完整的用户记录，极大减少了I/O开销，这个特性只适用于二级索引。

  • Using where

    当某个搜索条件需要在 server层 进行判断时，在 Extra 列中会提示 Using where 。

  • Using join buffer

    在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的 基于块的嵌套循环算法

  • Not exists

    当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的，那么在该表的执行计划的 Extra 列就会提示 Not exists 额外信息

  • Using intersect(…) 、 Using union(…) 和 Using sort_union(…)

    索引合并方式

  • Zero limit

    当我们的 LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息

  • Using filesort

    在内存中或者磁盘上进行排序的方式统称为文件排序（英文名： filesort ）。即查询出的记录需要排序。

  • Using temporary

    如果查询中使用到了内部的临时表，在执行计划的 Extra 列将会显示 Using temporary 提示

  • Start temporary, End temporary

    在将 In 子查询转为 semi-join 时，当执行策略为 DuplicateWeedout 时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的 Extra 列将显示 Start temporary 提示，被驱动表查询执行计划的 Extra 列将显示 End temporary 提示

  • LooseScan

    在将 In 子查询转为 semi-join 时，如果采用的是 LooseScan 执行策略，则在驱动表执行计划的 Extra 列就是显示 LooseScan 提示

  • FirstMatch(tb_name)

    在将 In 子查询转为 semi-join 时，如果采用的是 FirstMatch 执行策略，则在被驱动表执行计划的 Extra 列就是显示 FirstMatch(tb_name) 提示

二. Json 格式的执行计划

查看某个执行计划花费的成本的方式：

  • 在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON 。
1
2
3
4
5
6
"cost_info": {
    "read_cost": "1840.84",
    "eval_cost": "193.76",
    "prefix_cost": "2034.60",
    "data_read_per_join": "1M"
}

  • read_cost

    • IO 成本
    • 检测 rows × (1 - filter) 条记录的 CPU 成本

  • eval_cost

    检测 rows × filter 条记录的成本。

  • prefix_cost

    单表查询成本

    read_cost + eval_cost

  • data_read_per_join

    此次查询中需要读取的数据量

三. Extented EXPLAIN

SHOW WARNINGS 语句查看与这个查询的执行计划有关的一些扩展信息：

  • Level

  • Code

  • Message

类似于 查询优化器将我们的查询语句重写后的语句

InnoDB 的 Buffer Pool

一. 缓存的重要性

InnoDB 存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说 即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中 。将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其 缓存 起来，这样将来有请求再次访问该页面时，就可以省去磁盘 IO 的开销了。

二. InnoDB 的Buffer Pool

Buffer Pool 即为缓冲池，默认为 128 M。

1. Buffer Pool内部组成

由控制块和缓存页组成：

控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边。

每个控制块大约占用缓存页大小的5%

2. free链表的管理

为了在某个地方记录一下Buffer Pool中哪些缓存页是可用的 ，这个时候缓存页对应的 控制块 就派上大用场了，我们可以 把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中 ，这个链表也可以被称作 free链表 （或者说空闲链表）。

链表的基节点占用的内存空间并不包含在为 Buffer Pool 申请的一大片连续内存空间之内，而是单独申请的一块内存空间。

3. 缓存页的哈希处理

表空间号 + 页号 作为 key ， 缓存页 作为 value 创建一个哈希表，在需要访问某个页的数据时，先从哈希表中根据 表空间号 + 页号 看看有没有对应的缓存页，如果有，直接使用该缓存页就好，如果没有，那就从 free链表 中选一个空闲的缓存页，然后把磁盘中对应的页加载到该缓存页的位置。

4. flush链表的管理

如果我们修改了 Buffer Pool 中某个缓存页的数据，那它就和磁盘上的页 不一致 了，这样的缓存页也被称为 脏页 （英文名： dirty page ）。

创建一个存储脏页的链表，凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，因为这个链表节点对应的缓存页都是需要被刷新到磁盘上的，所以也叫 flush链表 。

5. LRU链表的管理

简单的LRU链表

LRU链表 ：

  • 如果该页不在 Buffer Pool 中，在把该页从磁盘加载到 Buffer Pool 中的缓存页时，就把该缓存页对应的 控制块 作为节点塞到链表的头部。
  • 如果该页已经缓存在 Buffer Pool 中，则直接把该页对应的 控制块 移动到 LRU链表 的头部。
划分区域的LRU链表

两种情况：

  • 预读 ，就是 InnoDB 认为执行当前的请求可能之后会读取某些页面，就预先把它们加载到 Buffer Pool 中。根据触发方式的不同， 预读 又可以细分为下边两种：

  • 线性预读

    设计 InnoDB 的大叔提供了一个系统变量 innodb_read_ahead_threshold ，如果顺序访问了某个区（ extent ）的页面超过这个系统变量的值，就会触发一次 异步 读取下一个区中全部的页面到 Buffer Pool 的请求，注意 异步 读取意味着从磁盘中加载这些被预读的页面并不会影响到当前工作线程的正常执行。

  • 随机预读

    如果 Buffer Pool 中已经缓存了某个区的13个连续的页面，不论这些页面是不是顺序读取的，都会触发一次 异步 读取本区中所有其的页面到 Buffer Pool 的请求。设计 InnoDB 的大叔同时提供了 innodb_random_read_ahead 系统变量，它的默认值为 OFF ，也就意味着 InnoDB 并不会默认开启随机预读的功能，如果我们想开启该功能，可以通过修改启动参数或者直接使用 SET GLOBAL 命令把该变量的值设置为 ON 。

预读 本来是个好事儿，如果预读到 Buffer Pool 中的页成功的被使用到，那就可以极大的提高语句执行的效率。可是如果用不到呢？这些预读的页都会放到 LRU 链表的头部，但是如果此时 Buffer Pool 的容量不太大而且很多预读的页面都没有用到的话，这就会导致处在 LRU链表 尾部的一些缓存页会很快的被淘汰掉，也就是所谓的 劣币驱逐良币 ， 会大大降低缓存命中率 。

  • 全表扫描

    全表扫描的语句意味着，每次执行都要把 Buffer Pool 中的缓存页换一次血，这严重的影响到其他查询对 Buffer Pool 的使用，从而 大大降低了缓存命中率 。

简单的 LRU链表 存在的问题：

  • 加载到 Buffer Pool 中的页不一定被用到。
  • 如果非常多的使用频率偏低的页被同时加载到 Buffer Pool 时，可能会把那些使用频率非常高的页从 Buffer Pool 中淘汰掉。

将 LRU链表 按照一定比例分成两截：

  • 一部分存储使用频率非常高的缓存页，所以这一部分链表也叫做 热数据 ，或者称 young区域 。
  • 另一部分存储使用频率不是很高的缓存页，所以这一部分链表也叫做 冷数据 ，或者称 old区域 。

优化方案：

  • 针对预读的页面可能不进行后续访问情况的优化

    设计 InnoDB 的大叔规定， 当磁盘上的某个页面在初次加载到Buffer Pool中的某个缓存页时，该缓存页对应的控制块会被放到old区域的头部 。这样针对预读到 Buffer Pool 却不进行后续访问的页面就会被逐渐从 old 区域逐出，而不会影响 young 区域中被使用比较频繁的缓存页。

  • 针对全表扫描时，短时间内访问大量使用频率非常低的页面情况的优化

    在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从old区域移动到young区域的头部，否则将它移动到young区域的头部 。

6. 刷新脏页到磁盘

  • 从 LRU链表 的冷数据中刷新一部分页面到磁盘。

    后台线程会定时从 LRU链表 尾部开始扫描一些页面，扫描的页面数量可以通过系统变量 innodb_lru_scan_depth 来指定，如果从里边儿发现脏页，会把它们刷新到磁盘。这种刷新页面的方式被称之为 BUF_FLUSH_LRU 。

  • 从 flush链表 中刷新一部分页面到磁盘。

    后台线程也会定时从 flush链表 中刷新一部分页面到磁盘，刷新的速率取决于当时系统是不是很繁忙。这种刷新页面的方式被称之为 BUF_FLUSH_LIST 。

有时候后台线程刷新脏页的进度比较慢，导致用户线程在准备加载一个磁盘页到 Buffer Pool 时没有可用的缓存页，这时就会尝试看看 LRU链表 尾部有没有可以直接释放掉的未修改页面，如果没有的话会不得不将 LRU链表 尾部的一个脏页同步刷新到磁盘（和磁盘交互是很慢的，这会降低处理用户请求的速度）。这种刷新单个页面到磁盘中的刷新方式被称之为 BUF_FLUSH_SINGLE_PAGE 。

三. 多个Buffer Pool实例

Buffer Pool 本质是 InnoDB 向操作系统申请的一块连续的内存空间，在多线程环境下，访问 Buffer Pool 中的各种链表都需要加锁处理啥的，在 Buffer Pool 特别大而且多线程并发访问特别高的情况下，单一的 Buffer Pool 可能会影响请求的处理速度。所以在 Buffer Pool 特别大的时候，我们可以把它们拆分成若干个小的 Buffer Pool ，每个 Buffer Pool 都称为一个 实例 ，它们都是独立的。它们在多线程并发访问时不会相互影响，从而提高并发处理能力。

规定：当innodb_buffer_pool_size的值小于1G的时候设置多个实例是无效的，InnoDB会默认把innodb_buffer_pool_instances 的值修改为1。

innodb_buffer_pool_chunk_size

一个 Buffer Pool 实例其实是由若干个 chunk 组成的，一个 chunk 就代表一片连续的内存空间，里边儿包含了若干缓存页与其对应的控制块，画个图表示就是这样：

innodb_buffer_pool_chunk_size的值只能在服务器启动时指定，在服务器运行过程中是不可以修改的 。

注意事项：

  • innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances 的倍数（这主要是想保证每一个 Buffer Pool 实例中包含的 chunk 数量相同）。

    若非倍数，则向上取整。

  • 如果在服务器启动时， innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances 的值已经大于 innodb_buffer_pool_size 的值，那么 innodb_buffer_pool_chunk_size 的值会被服务器自动设置为 innodb_buffer_pool_size/innodb_buffer_pool_instances 的值。

四. 总结

  1. 磁盘太慢，用内存作为缓存很有必要。

  2. Buffer Pool 本质上是 InnoDB 向操作系统申请的一段连续的内存空间，可以通过 innodb_buffer_pool_size 来调整它的大小。

  3. Buffer Pool 向操作系统申请的连续内存由控制块和缓存页组成，每个控制块和缓存页都是一一对应的，在填充足够多的控制块和缓存页的组合后， Buffer Pool 剩余的空间可能产生不够填充一组控制块和缓存页，这部分空间不能被使用，也被称为 碎片 。

  4. InnoDB 使用了许多 链表 来管理 Buffer Pool 。

  5. free链表 中每一个节点都代表一个空闲的缓存页，在将磁盘中的页加载到 Buffer Pool 时，会从 free链表 中寻找空闲的缓存页。

  6. 为了快速定位某个页是否被加载到 Buffer Pool ，使用 表空间号 + 页号 作为 key ，缓存页作为 value ，建立哈希表。

  7. 在 Buffer Pool 中被修改的页称为 脏页 ，脏页并不是立即刷新，而是被加入到 flush链表 中，待之后的某个时刻同步到磁盘上。

  8. LRU链表 分为 young 和 old 两个区域，可以通过 innodb_old_blocks_pct 来调节 old 区域所占的比例。首次从磁盘上加载到 Buffer Pool 的页会被放到 old 区域的头部，在 innodb_old_blocks_time 间隔时间内访问该页不会把它移动到 young 区域头部。在 Buffer Pool 没有可用的空闲缓存页时，会首先淘汰掉 old 区域的一些页。

  9. 我们可以通过指定 innodb_buffer_pool_instances 来控制 Buffer Pool 实例的个数，每个 Buffer Pool 实例中都有各自独立的链表，互不干扰。

  10. 自 MySQL 5.7.5 版本之后，可以在服务器运行过程中调整 Buffer Pool 大小。每个 Buffer Pool 实例由若干个 chunk 组成，每个 chunk 的大小可以在服务器启动时通过启动参数调整。

  11. 可以用下边的命令查看 Buffer Pool 的状态信息：

    1
    SHOW ENGINE INNODB STATUS\G

事务简介

四大特性（ACID）：

  • 原子性（Atomicity）

    要么全做，要么全不做的规则称之为 原子性 。

  • 隔离性（Isolation）

    保证其它的状态转换不会影响到本次状态转换，这个规则被称之为 隔离性 。

  • 一致性（Consistency）

    如果数据库中的数据全部符合现实世界中的约束（all defined rules），我们说这些数据就是一致的，或者说符合 一致性 的。

    数据库某些操作的原子性和隔离性都是保证一致性的一种手段，在操作执行完成后保证符合所有既定的约束则是一种结果 。

  • 持久性（Durability）

    当现实世界的一个状态转换完成后，这个转换的结果将永久的保留，这个规则被设计数据库的大叔们称为 持久性 。

一. 事务的概念

设计数据库的大叔为了方便起见，把需要保证 原子性 、 隔离性 、 一致性 和 持久性 的一个或多个数据库操作称之为一个 事务 （英文名是： transaction ）。

事务 大致上划分成了这么几个状态：

  • 活动的（active）

    事务对应的数据库操作正在执行过程中时，我们就说该事务处在 活动的 状态。

  • 部分提交的（partially committed）

    当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在 部分提交的 状态。

  • 失败的（failed）

    当事务处在 活动的 或者 部分提交的 状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在 失败的 状态。

  • 中止的（aborted）

    如果事务执行了半截而变为 失败的 状态，就要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为 回滚 。当 回滚 操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了 中止的 状态。

  • 提交的（committed）

    当一个处在 部分提交的 状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了 提交的 状态。

随着事务对应的数据库操作执行到不同阶段，事务的状态也在不断变化，一个基本的状态转换图如下所示：

只有当事务处于提交的或者中止的状态时，一个事务的生命周期才算是结束了 。

二. MySQL中事务的语法

开启事务

我们可以使用下边两种语句之一来开启一个事务：

  • BEGIN [WORK];

    BEGIN 语句代表开启一个事务，后边的单词 WORK 可有可无。开启事务后，就可以继续写若干条语句，这些语句都属于刚刚开启的这个事务。

  • START TRANSACTION;

    START TRANSACTION 语句和 BEGIN 语句有着相同的功效，都标志着开启一个事务，但可以在其后面跟随几个修饰符：

    • READ ONLY ：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。只读事务中只是不允许修改那些其他事务也能访问到的表中的数据，对于临时表来说（我们使用CREATE TMEPORARY TABLE创建的表），由于它们只能在当前会话中可见，所以只读事务其实也是可以对临时表进行增、删、改操作的。
    • READ WRITE ：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
    • WITH CONSISTENT SNAPSHOT ：启动一致性读。

READ ONLY 和 READ WRITE 是用来设置所谓的事务 访问模式 的，就是以只读还是读写的方式来访问数据库中的数据，一个事务的访问模式不能同时既设置为 只读 的也设置为 读写 的，所以我们不能同时把 READ ONLY 和 READ WRITE 放到 START TRANSACTION 语句后边。另外，如果我们不显式指定事务的访问模式，那么该事务的访问模式就是 读写 模式。

提交事务

1
COMMIT [WORK]

手动终止事务

将数据库恢复到事务执行之前的样子：

1
ROLLBACK [WORK]

ROLLBACK 语句是我们程序员手动的去回滚事务时才去使用的，如果事务在执行过程中遇到了某些错误而无法继续执行的话，事务自身会自动的回滚。

支持事务的存储引擎

MySQL 中并不是所有存储引擎都支持事务的功能，目前只有 InnoDB 和 NDB 存储引擎支持（NDB存储引擎不是我们的重点），如果某个事务中包含了修改使用不支持事务的存储引擎的表，那么对该使用不支持事务的存储引擎的表所做的修改将无法进行回滚。

自动提交

MySQL 中有一个系统变量 autocommit ，默认情况下为on。

默认情况下，如果我们不显式的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的 自动提交 。

关闭自动提交的两种方法：

  • 显式的的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务。

    这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。

  • 把系统变量 autocommit 的值设置为 OFF ，就像这样：

    1
    SET autocommit = OFF;

    这样的话，我们写入的多条语句就算是属于同一个事务了，直到我们显式的写出 COMMIT 语句来把这个事务提交掉，或者显式的写出 ROLLBACK 语句来把这个事务回滚掉。

隐式提交

如果我们输入了某些语句之后就会 悄悄的 提交掉，就像我们输入了 COMMIT 语句了一样，这种因为某些特殊的语句而导致事务提交的情况称为 隐式提交 。

  • 定义或修改数据库对象的数据定义语言（Data definition language，缩写为： DDL ）。
  • 隐式使用或修改 mysql 数据库中的表
  • 事务控制或关于锁定的语句
  • 加载数据的语句
  • 关于 MySQL 复制的一些语句
  • 其它的一些语句

保存点

保存点 （英文： savepoint ）的概念，就是在事务对应的数据库语句中打几个点，我们在调用 ROLLBACK 语句时可以指定会滚到哪个点，而不是回到最初的原点。

定义保存点

1
SAVEPOINT 保存点名称;

回滚

1
ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;

删除保存点

1
RELEASE SAVEPOINT 保存点名称;

redo 日志

一. redo 日志的引入

要保证持久性，一个简单的做法是在事务提交完成之前把该事务所修改的所有页面都刷新到磁盘，但是这个做法会产生两个问题：

  • 刷新一个完整的数据页太浪费了

    有时候我们仅仅修改了某个页面中的一个字节，但是我们知道在 InnoDB 中是以页为单位来进行磁盘IO的，也就是说我们在该事务提交时不得不将一个完整的页面从内存中刷新到磁盘，我们又知道一个页面默认是16KB大小，只修改一个字节就要刷新16KB的数据到磁盘上显然是太浪费了。

  • 随机IO刷起来比较慢

    一个事务可能包含很多语句，即使是一条语句也可能修改许多页面，倒霉催的是该事务修改的这些页面可能并不相邻，这就意味着在将某个事务修改的 Buffer Pool 中的页面刷新到磁盘时，需要进行很多的随机IO，随机IO比顺序IO要慢，尤其对于传统的机械硬盘来说。

其实没有必要在每次事务提交时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要 把修改了哪些东西记录一下就好，这便产生了 重做日志 ，英文名为 redo log 。与在事务提交时将所有修改过的内存中的页面刷新到磁盘中相比，只将该事务执行过程中产生的 redo 日志刷新到磁盘的好处如下：

  • redo 日志占用的空间非常小
  • redo 日志是顺序写入磁盘的（顺序IO）

二. redo 日志格式

通用结构：

  • type ：该条 redo 日志的类型。
  • space ID ：表空间ID。
  • page number ：页号。
  • data ：该条 redo 日志的具体内容。

1. 简单的redo日志类型

设计 InnoDB 的大叔把这种极其简单的 redo 日志称之为 物理日志 ：

  • MLOG_1BYTE （ type 字段对应的十进制数字为 1 ）：表示在页面的某个偏移量处写入1个字节的 redo 日志类型。
  • MLOG_2BYTE （ type 字段对应的十进制数字为 2 ）：表示在页面的某个偏移量处写入2个字节的 redo 日志类型。
  • MLOG_4BYTE （ type 字段对应的十进制数字为 4 ）：表示在页面的某个偏移量处写入4个字节的 redo 日志类型。
  • MLOG_8BYTE （ type 字段对应的十进制数字为 8 ）：表示在页面的某个偏移量处写入8个字节的 redo 日志类型。
  • MLOG_WRITE_STRING （ type 字段对应的十进制数字为 30 ）：表示在页面的某个偏移量处写入一串数据。

MLOG_8BYTE 的 redo 日志结构如下所示：

MLOG_WRITE_STRING 的redo日志结构如下所示：

2. 复杂的redo日志类型

把一条记录插入到一个页面时可能需要更改的地方非常多 。这时我们如果使用上边介绍的简单的物理 redo 日志来记录这些修改时，可以有两种解决方案：

  • 在每个修改的地方都记录一条 redo 日志
  • 将整个页面的 第一个被修改的字节 到 最后一个修改的字节 之间所有的数据当成是一条物理 redo 日志中的具体数据

这两种方案都很浪费空间，因而有一些新的redo日志类型：

  • MLOG_REC_INSERT （对应的十进制数字为 9 ）：表示插入一条使用非紧凑行格式的记录时的 redo 日志类型。

  • MLOG_COMP_REC_INSERT （对应的十进制数字为 38 ）：表示插入一条使用紧凑行格式的记录时的 redo 日志类型。

  • MLOG_COMP_PAGE_CREATE （ type 字段对应的十进制数字为 58 ）：表示创建一个存储紧凑行格式记录的页面的 redo 日志类型。

  • MLOG_COMP_REC_DELETE （ type 字段对应的十进制数字为 42 ）：表示删除一条使用紧凑行格式记录的 redo 日志类型。

  • MLOG_COMP_LIST_START_DELETE （ type 字段对应的十进制数字为 44 ）：表示从某条给定记录开始删除页面中的一系列使用紧凑行格式记录的 redo 日志类型。

  • MLOG_COMP_LIST_END_DELETE （ type 字段对应的十进制数字为 43 ）：与 MLOG_COMP_LIST_START_DELETE 类型的 redo 日志呼应，表示删除一系列记录直到 MLOG_COMP_LIST_END_DELETE 类型的 redo 日志对应的记录为止。

  • MLOG_ZIP_PAGE_COMPRESS （ type 字段对应的十进制数字为 51 ）：表示压缩一个数据页的 redo 日志类型。

注：Redundant是一种比较原始的行格式，它就是非紧凑的。而Compact、Dynamic以及Compressed行格式是较新的行格式，它们是紧凑的（占用更小的存储空间）。

这些类型的 redo 日志既包含 物理 层面的意思，也包含 逻辑 层面的意思，具体指：

  • 物理层面看，这些日志都指明了对哪个表空间的哪个页进行了修改。
  • 逻辑层面看，在系统崩溃重启时，并不能直接根据这些日志里的记载，将页面内的某个偏移量处恢复成某个数据，而是需要调用一些事先准备好的函数，执行完这些函数后才可以将页面恢复成系统崩溃前的样子。

MLOG_COMP_REC_INSERT 的 redo 日志的结构：

  • n_uniques

    表示需要几个字段的值才能确保记录的唯一性，这样当插入一条记录时就可以按照记录的前 n_uniques 个字段进行排序。

    对于聚簇索引来说， n_uniques 的值为主键的列数，对于其他二级索引来说，该值为索引列数+主键列数。

  • field1_len ~ fieldn_len

    代表着该记录若干个字段占用存储空间的大小。

  • offset

    代表的是该记录的前一条记录在页面中的地址，便于更新前一条记录的 next_record 属性。

很显然这个类型为 MLOG_COMP_REC_INSERT 的 redo 日志并没有记录 PAGE_N_DIR_SLOTS 的值修改为了啥， PAGE_HEAP_TOP 的值修改为了啥， PAGE_N_HEAP 的值修改为了啥等等这些信息，而只是把在本页面中插入一条记录所有必备的要素记了下来，之后系统崩溃重启时，服务器会调用相关向某个页面插入一条记录的那个函数，而 redo 日志中的那些数据就可以被当成是调用这个函数所需的参数，在调用完该函数后，页面中的 PAGE_N_DIR_SLOTS 、 PAGE_HEAP_TOP 、 PAGE_N_HEAP 等等的值也就都被恢复到系统崩溃前的样子了。这就是所谓的 逻辑 日志的意思。

3. redo日志格式小结

redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来。

三. Mini-Transaction

1. 以组的形式写入redo日志

插入记录时可能出现的两种情况：

  • 该数据页的剩余的空闲空间充足，足够容纳这一条待插入记录，那么事情很简单，直接把记录插入到这个数据页中，这种情况称为乐观插入。
  • 该数据页剩余的空闲空间不足，那么我们需要进行页分裂操作，也就是新建一个叶子节点，然后把原先数据页中的一部分记录复制到这个新的数据页中，然后再把记录插入进去，把这个叶子节点插入到叶子节点链表中，最后还要在内节点中添加一条 目录项记录 指向这个新创建的页面。很显然，这个过程要对多个页面进行修改，也就意味着会产生多条 redo 日志，我们把这种情况称之为 悲观插入 。

设计 InnoDB 的大叔们认为向某个索引对应的 B+ 树中插入一条记录的这个过程必须是原子的，不能说插了一半之后就停止了。比方说在悲观插入过程中，新的页面已经分配好了，数据也复制过去了，新的记录也插入到页面中了，可是没有向内节点中插入一条 目录项记录 ，这个插入过程就是不完整的，这样会形成一棵不正确的 B+ 树。我们知道 redo 日志是为了在系统崩溃重启时恢复崩溃前的状态，如果在悲观插入的过程中只记录了一部分 redo 日志，那么在系统崩溃重启时会将索引对应的 B+ 树恢复成一种不正确的状态，这是设计 InnoDB 的大叔们所不能忍受的。所以他们规定在执行这些需要保证原子性的操作时必须以 组 的形式来记录的 redo 日志，在进行系统崩溃重启恢复时，针对某个组中的 redo 日志，要么把全部的日志都恢复掉，要么一条也不恢复。怎么做到的呢？这得分情况讨论：

  • 有的需要保证原子性的操作会生成多条 redo 日志，比如向某个索引对应的 B+ 树中进行一次悲观插入就需要生成许多条 redo 日志。

    在该组中的最后一条 redo 日志后边加上一条特殊类型的 redo 日志，该类型名称为 MLOG_MULTI_REC_END ， type 字段对应的十进制数字为 31 ，该类型的 redo 日志结构很简单，只有一个 type 字段。所以某个需要保证原子性的操作产生的一系列 redo 日志必须要以一个类型为 MLOG_MULTI_REC_END 结尾，就像这样：

  • 有的需要保证原子性的操作只生成一条 redo 日志，比如更新 Max Row ID 属性的操作就只会生成一条 redo 日志。

    通过type字段的第一个比特位表示，如果 type 字段的第一个比特位为 1 ，代表该需要保证原子性的操作只产生了单一的一条 redo 日志，否则表示该需要保证原子性的操作产生了一系列的 redo 日志。

2. Mini-Transaction的概念

设计 MySQL 的大叔把对底层页面中的一次原子访问的过程称之为一个 Mini-Transaction ，简称 mtr ，比如上边所说的修改一次 Max Row ID 的值算是一个 Mini-Transaction ，向某个索引对应的 B+ 树中插入一条记录的过程也算是一个 Mini-Transaction 。通过上边的叙述我们也知道，一个所谓的 mtr 可以包含一组 redo 日志，在进行崩溃恢复时这一组 redo 日志作为一个不可分割的整体。

事务对应关系图：

四. redo 日志的写入过程

1. redo log block

设计 InnoDB 的大叔为了更好的进行系统崩溃恢复，他们把通过 mtr 生成的 redo 日志都放在了大小为 512字节 的 页 中。

真正的 redo 日志都是存储到占用 496 字节大小的 log block body 中，图中的 log block header 和 log block trailer 存储的是一些管理信息。我们来看看这些所谓的 管理信息 都是啥：

log block header ：

  • LOG_BLOCK_HDR_NO ：唯一标号。

  • LOG_BLOCK_HDR_DATA_LEN ：表示block中已经使用了多少字节，初始值为 12 （因为 log block body 从第12个字节处开始）。随着往block中写入的redo日志越来也多，本属性值也跟着增长。如果 log block body 已经被全部写满，那么本属性的值被设置为 512 。

  • LOG_BLOCK_FIRST_REC_GROUP ：一条 redo 日志也可以称之为一条 redo 日志记录（ redo log record ），一个 mtr 会生产多条 redo 日志记录，这些 redo 日志记录被称之为一个 redo 日志记录组（ redo log record group ）。 LOG_BLOCK_FIRST_REC_GROUP 就代表该block中第一个 mtr 生成的 redo 日志记录组的偏移量。

  • LOG_BLOCK_CHECKPOINT_NO ：checkpoint 的序号。

log block trailer :

  • LOG_BLOCK_CHECKSUM ：校验值，用于正确性校验。

2. redo 日志缓冲区

同Buffer Pool，称为redo log buffer，

redo日志写入log buffer

顺序写入，当我们想往 log buffer 中写入 redo 日志时，第一个遇到的问题就是应该写在哪个 block 的哪个偏移量处，所以设计 InnoDB 的大叔特意提供了一个称之为 buf_free 的全局变量，该变量指明后续写入的 redo 日志应该写入到 log buffer 中的哪个位置，如图所示：

五. redo 日志文件

1. redo 日志刷盘时机

  • log buffer 空间不足时

    设计 InnoDB 的大叔认为如果当前写入 log buffer 的 redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。

  • 事务提交时

  • 将某个脏页刷新到磁盘前，会保证先将该脏页对应的 redo 日志刷新到磁盘中

  • 后台线程不停的刷盘

    后台有一个线程，大约每秒都会刷新一次 log buffer 中的 redo 日志到磁盘。

  • 正常关闭服务器时

  • 做checkpoint 时

2. redo 日志文件组

磁盘上的 redo 日志文件不只一个，而是以一个 日志文件组 的形式出现的。

几个参数：

  • innodb_log_group_home_dir

    该参数指定了 redo 日志文件所在的目录，默认值就是当前的数据目录。

  • innodb_log_file_size

    该参数指定了每个 redo 日志文件的大小，在 MySQL 5.7.21 这个版本中的默认值为 48MB ，

  • innodb_log_files_in_group

    该参数指定 redo 日志文件的个数，默认值为2，最大值为100。

图示：

redo 日志文件大小其实就是： innodb_log_file_size × innodb_log_files_in_group 。

3. redo 日志文件格式

我们前边说过 log buffer 本质上是一片连续的内存空间，被划分成了若干个 512 字节大小的 block 。 将log buffer中的redo日志刷新到磁盘的本质就是把block的镜像写入日志文件中 ，所以 redo 日志文件其实也是由若干个 512 字节大小的block组成。

redo 日志文件组中的每个文件大小都一样，格式也一样，都是由两部分组成：

  • 前2048个字节，也就是前4个block是用来存储一些管理信息的。
  • 从第2048字节往后是用来存储 log buffer 中的block镜像的。

所以我们前边所说的 循环 使用redo日志文件，其实是从每个日志文件的第2048个字节开始算，画个示意图就是这样：

前4个block：

六. Log Sequence Number

设计 InnoDB 的大叔为记录已经写入的 redo 日志量，设计了一个称之为 Log Sequence Number 的全局变量，翻译过来就是： 日志序列号 ，简称 lsn 。规定初始的 lsn 值为 8704。

统计 lsn 的增长量时，是按照实际写入的日志量加上占用的 log block header 和 log block trailer 来计算的。

每一组由mtr生成的redo日志都有一个唯一的LSN值与其对应，LSN值越小，说明redo日志产生的越早 。

flushed_to_disk_lsn

redo 日志是首先写到 log buffer 中，之后才会被刷新到磁盘上的 redo 日志文件。所以设计 InnoDB 的大叔提出了一个称之为 buf_next_to_write 的全局变量，标记当前 log buffer 中已经有哪些日志被刷新到磁盘中了。画个图表示就是这样：

对应 lsn ，设计 InnoDB 的大叔提出了一个表示刷新到磁盘中的 redo 日志量的全局变量，称之为 flushed_to_disk_lsn 。系统第一次启动时，该变量的值和初始的 lsn 值是相同的，都是 8704 。

当有新的 redo 日志写入到 log buffer 时，首先 lsn 的值会增长，但 flushed_to_disk_lsn 不变，随后随着不断有 log buffer 中的日志被刷新到磁盘上， flushed_to_disk_lsn 的值也跟着增长。 如果两者的值相同时，说明log buffer中的所有redo日志都已经刷新到磁盘中了 。

tips: 应用程序向磁盘写入文件时其实是先写到操作系统的缓冲区中去，如果某个写入操作要等到操作系统确认已经写到磁盘时才返回，那需要调用一下操作系统提供的fsync函数。其实只有当系统执行了fsync函数后，flushed_to_disk_lsn的值才会跟着增长，当仅仅把log buffer中的日志写入到操作系统缓冲区却没有显式的刷新到磁盘时，另外的一个称之为write_lsn的值跟着增长。不过为了大家理解上的方便，我们在讲述时把flushed_to_disk_lsn和write_lsn的概念混淆了起来。

flush链表中的LSN

当第一次修改某个缓存在 Buffer Pool 中的页面时，就会把这个页面对应的控制块插入到 flush链表 的头部，之后再修改该页面时由于它已经在 flush 链表中了，就不再次插入了。也就是说 flush链表中的脏页是按照页面的第一次修改时间从大到小进行排序的 。在这个过程中会在缓存页对应的控制块中记录两个关于页面何时修改的属性：

  • oldest_modification ：如果某个页面被加载到 Buffer Pool 后进行第一次修改，那么就将修改该页面的 mtr 开始时对应的 lsn 值写入这个属性。
  • newest_modification ：每修改一次页面，都会将修改该页面的 mtr 结束时对应的 lsn 值写入这个属性。也就是说该属性表示页面最近一次修改后对应的系统 lsn 值。

flush链表中的脏页按照修改发生的时间顺序进行排序，也就是按照oldest_modification代表的LSN值进行排序，被多次更新的页面不会重复插入到flush链表中，但是会更新newest_modification属性的值 。

checkpoint

循环使用 redo 日志文件组会产生追尾问题，此时便需要checkpoint，这时应该想到： redo日志只是为了系统崩溃后恢复脏页用的，如果对应的脏页已经刷新到了磁盘，也就是说即使现在系统崩溃，那么在重启后也用不着使用redo日志恢复该页面了，所以该redo日志也就没有存在的必要了，那么它占用的磁盘空间就可以被后续的redo日志所重用 。设计 InnoDB 的大叔提出了一个全局变量 checkpoint_lsn 来代表当前系统中可以被覆盖的 redo 日志总量是多少，这个变量初始值也是 8704 。

做一次 checkpoint 可以分为两个步骤：

  • 步骤一：计算一下当前系统中可以被覆盖的 redo 日志对应的 lsn 值最大是多少。

    只要我们计算出当前系统中被最早修改的脏页对应的 oldest_modification 值，那么凡是在系统lsn值小于该节点的oldest_modification值时产生的redo日志都是可以被覆盖掉的 ，我们就把该脏页的 oldest_modification 赋值给 checkpoint_lsn 。

  • 步骤二：将 checkpoint_lsn 和对应的 redo 日志文件组偏移量以及此次 checkpint 的编号写到日志文件的管理信息（就是 checkpoint1 或者 checkpoint2 ）中。

    设计 InnoDB 的大叔维护了一个目前系统做了多少次 checkpoint 的变量 checkpoint_no ，每做一次 checkpoint ，该变量的值就加1。我们前边说过计算一个 lsn 值对应的 redo 日志文件组偏移量是很容易的，所以可以计算得到该 checkpoint_lsn 在 redo 日志文件组中对应的偏移量 checkpoint_offset ，然后把这三个值都写到 redo 日志文件组的管理信息中。

    规定，当 checkpoint_no 的值是偶数时，就写到 checkpoint1 中，是奇数时，就写到 checkpoint2 中。

查看系统中的各种LSN值

1
mysql> SHOW ENGINE INNODB STATUS\G
  • Log sequence number ：代表系统中的 lsn 值，也就是当前系统已经写入的 redo 日志量，包括写入 log buffer 中的日志。
  • Log flushed up to ：代表 flushed_to_disk_lsn 的值，也就是当前系统已经写入磁盘的 redo 日志量。
  • Pages flushed up to ：代表 flush链表 中被最早修改的那个页面对应的 oldest_modification 属性值。
  • Last checkpoint at ：当前系统的 checkpoint_lsn 值。

为了保证事务的 持久性 ，可以设置 innodb_flush_log_at_trx_commit 的系统变量的值：

  • 0 ：当该系统变量值为0时，表示在事务提交时不立即向磁盘中同步 redo 日志，这个任务是交给后台线程做的。
  • 1 ：当该系统变量值为1时，表示在事务提交时需要将 redo 日志同步到磁盘，可以保证事务的 持久性 。 1 也是 innodb_flush_log_at_trx_commit 的默认值。
  • 2 ：当该系统变量值为2时，表示在事务提交时需要将 redo 日志写到操作系统的缓冲区中，但并不需要保证将日志真正的刷新到磁盘。

七. 崩溃恢复

确定恢复的起点

获取最近发生的 checkpoint 对应的 checkpoint_lsn 值以及它在 redo 日志文件组中的偏移量 checkpoint_offset 。

确定恢复的终点

普通block的 log block header 部分有一个称之为 LOG_BLOCK_HDR_DATA_LEN 的属性，该属性值记录了当前block里使用了多少字节的空间。对于被填满的block来说，该值永远为 512 。如果该属性的值不为 512 ，那么就是它了，它就是此次崩溃恢复中需要扫描的最后一个block。

怎么恢复

示例：

可以直接按照顺序恢复，但是这样较慢。

优化：

  • 使用哈希表

    根据 redo 日志的 space ID 和 page number 属性计算出散列值，把 space ID 和 page number 相同的 redo 日志放到哈希表的同一个槽里，如果有多个 space ID 和 page number 都相同的 redo 日志，那么它们之间使用链表连接起来，按照生成的先后顺序链接起来的，如图所示：

    这样可以一次性将一个页面恢复好（避免了很多读取页面的随机IO），加快了恢复速度，恢复的时候要按照redo日志的生成顺序恢复。

  • 跳过已经刷新到磁盘的页面

    checkpoint_lsn 之前的 redo 日志对应的脏页确定都已经刷到磁盘了，但是 checkpoint_lsn 之后的 redo 日志我们不能确定是否已经刷到磁盘，主要是因为在最近做的一次 checkpoint 后，可能后台线程又不断的从 LRU链表 和 flush链表 中将一些脏页刷出 Buffer Pool 。这些在 checkpoint_lsn 之后的 redo 日志，如果它们对应的脏页在崩溃发生时已经刷新到磁盘，那在恢复时也就没有必要根据 redo 日志的内容修改该页面了。

    那在恢复时怎么知道某个 redo 日志对应的脏页是否在崩溃发生时已经刷新到磁盘了呢？这还得从页面的结构说起，我们前边说过每个页面都有一个称之为 File Header 的部分，在 File Header 里有一个称之为 FIL_PAGE_LSN 的属性，该属性记载了最近一次修改页面时对应的 lsn 值（其实就是页面控制块中的 newest_modification 值）。如果在做了某次 checkpoint 之后有脏页被刷新到磁盘中，那么该页对应的 FIL_PAGE_LSN 代表的 lsn 值肯定大于 checkpoint_lsn 的值，凡是符合这种情况的页面就不需要重复执行lsn值小于 FIL_PAGE_LSN 的redo日志了，所以更进一步提升了崩溃恢复的速度。

LOG_BLOCK_HDR_NO是如何计算的

LOG_BLOCK_HDR_NO 这个属性代表一个唯一的标号。

公式：

1
((lsn / 512) & 0x3FFFFFFFUL) + 1

显然计算结果在 1 ~ 0x40000000UL 之间， 0x40000000UL 就代表1GB。也就是说系统最多能产生不重复的 LOG_BLOCK_HDR_NO 值只有 1GB 个。

设计InnoDB的大叔规定 redo 日志文件组中包含的所有文件大小总和不得超过512GB，一个block大小是512字节，也就是说redo日志文件组中包含的block块最多为1GB个，所以有1GB个不重复的编号值也就够用了。

另外， LOG_BLOCK_HDR_NO 值的第一个比特位比较特殊，称之为 flush bit ，如果该值为1，代表着本block是在某次将 log buffer 中的block刷新到磁盘的操作中的第一个被刷入的block。

undo 日志

设计数据库的大叔把这些为了回滚而记录的东东称之为撤销日志，英文名为 undo log。

由于查询操作（ SELECT ）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的 undo日志 。

一. 事务id

分配时机

如果某个事务执行过程中对某个表执行了增、删、改操作，那么 InnoDB 存储引擎就会给它分配一个独一无二的 事务id ，分配方式如下：

  • 对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个 事务id ，否则的话是不分配 事务id 的。

    SELECT 语句中创建的内部临时表不会被分配事务id，只有用户使用CREATE TEMPORARY TABLE创建的临时表才会被分配事务id

  • 对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个 事务id ，否则的话也是不分配 事务id 的。

生成策略

事务id 本质上就是一个数字，它的分配策略和我们前边提到的对隐藏列 row_id （当用户没有为表创建主键和 UNIQUE 键时 InnoDB 自动创建的列）的分配策略大抵相同，具体策略如下：

  • 服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个 事务id 时，就会把该变量的值当作 事务id 分配给该事务，并且把该变量自增1。
  • 每当这个变量的值为 256 的倍数时，就会将该变量的值刷新到系统表空间的页号为 5 的页面中一个称之为 Max Trx ID 的属性处，这个属性占用 8 个字节的存储空间。
  • 当系统下一次重新启动时，会将上边提到的 Max Trx ID 属性加载到内存中，将该值加上256之后赋值给我们前边提到的全局变量（因为在上次关机时该全局变量的值可能大于 Max Trx ID 属性值）。

聚簇索引的记录除了会保存完整的用户数据以外，而且还会自动添加名为trx_id、roll_pointer的隐藏列，如果用户没有在表中定义主键以及UNIQUE键，还会自动添加一个名为row_id的隐藏列。

二. undo 日志的格式

1. INSERT操作对应的undo日志

TRX_UNDO_INSERT_REC 的 undo日志 结构：

当我们向某个表中插入一条记录时，实际上需要向聚簇索引和所有的二级索引都插入一条记录。不过记录undo日志时，我们只需要考虑向聚簇索引插入记录时的情况就好了，因为其实聚簇索引记录和二级索引记录是一一对应的，我们在回滚插入操作时，只需要知道这条记录的主键信息，然后根据主键信息做对应的删除操作，做删除操作时就会顺带着把所有二级索引中相应的记录也删除掉。后边说到的DELETE操作和UPDATE操作对应的undo日志也都是针对聚簇索引记录而言的，我们之后就不强调了。

roll_pointer 的含义

roll_pointer 本质就是一个指针，指向记录对应的undo日志 。

2. DELETE操作对应的undo日志

假设此刻某个页面中的记录分布情况是这样的：

使用 DELETE 语句把 正常记录链表 中的记录删除时要经历两个阶段：

  • 阶段一：仅仅将记录的 delete_mask 标识位设置为 1 ，其他的不做修改（其实会修改记录的 trx_id 、 roll_pointer 这些隐藏列的值）。设计 InnoDB 的大叔把这个阶段称之为 delete mark 。

    这种中间状态最终是为了实现MVCC功能。

  • 阶段二： 当该删除语句所在的事务提交之后 ，会有 专门的线程后 来真正的把记录删除掉。所谓真正的删除就是把该记录从 正常记录链表 中移除，并且加入到 垃圾链表 中，然后还要调整一些页面的其他信息，比如页面中的用户记录数量 PAGE_N_RECS 、上次插入记录的位置 PAGE_LAST_INSERT 、垃圾链表头节点的指针 PAGE_FREE 、页面中可重用的字节数量 PAGE_GARBAGE 、还有页目录的一些信息等等。设计 InnoDB 的大叔把这个阶段称之为 purge 。

页面的Page Header部分有一个PAGE_GARBAGE属性，该属性记录着当前页面中可重用存储空间占用的总字节数。当新插入一条记录时，首先判断垃圾链表的头节点代表的记录空间是否足够容纳该条记录，如果足够，则直接复用，否则申请一块新的空间。此时若可以容纳，但是新插入的记录占用的空间小于垃圾链表头节点占用的空间，就会产生碎片空间，这部分空间也被统计在PAGE_GARBAGE中。当该页面快要满时，若页面已经无法分配一条完整记录的空间，那么就会将PAGE_GARBAGE和剩余可用空间加起来，看是否能够容纳一条记录，如果能，则InnoDB会重新组织空间结构，即先开辟一个临时页面，把页面内的记录依次插入一遍，因为依次插入时并不会产生碎片，之后再把临时页面的内容复制到本页面，这样碎片空间便得以利用，但是重新组织较为消耗性能。

在删除语句所在的事务提交之前，只会经历 阶段一 ，也就是 delete mark 阶段，我们只需考虑这个阶段的回滚。

TRX_UNDO_DEL_MARK_REC 类型的undo日志结构：

  • 在对一条记录进行 delete mark 操作前，需要把该记录的旧的 trx_id 和 roll_pointer 隐藏列的值都给记到对应的 undo日志 中来，就是我们图中显示的 old trx_id 和 old roll_pointer 属性。这样有一个好处，那就是可以通过 undo日志 的 old roll_pointer 找到记录在修改之前对应的 undo 日志。比方说在一个事务中，我们先插入了一条记录，然后又执行对该记录的删除操作，这个过程的示意图就是这样：

    这个undo日志构成的链表被称为版本链。

3. UPDATE操作对应的undo日志

不更新主键的情况

在不更新主键的情况下，又可以细分为被更新的列占用的存储空间不发生变化和发生变化的情况。

  • 就地更新（in-place update）

    更新记录时，对于被更新的 每个列 来说，如果更新后的列和更新前的列占用的存储空间都一样大，那么就可以进行 就地更新 ，也就是直接在原记录的基础上修改对应列的值。

  • 先删除掉旧记录，再插入新记录

    在不更新主键的情况下，如果有 任何一个 被更新的列更新前和更新后占用的存储空间大小不一致，那么就需要先把这条旧的记录从聚簇索引页面中删除掉，然后再根据更新后列的值创建一条新的记录插入到页面中。

    这里所说的 删除 并不是 delete mark 操作，而是真正的删除掉，也就是把这条记录从 正常记录链表 中移除并加入到 垃圾链表 中，此时是由用户线程同步执行真正的删除操作，而非做 purge 操作时使用的另外专门的线程，真正删除之后紧接着就要根据各个列更新后的值创建的新记录插入。

TRX_UNDO_UPD_EXIST_REC 的 undo日志 结构：

更新主键的情况

  • 将旧记录进行 delete mark 操作

    之所以只对旧记录做delete mark操作，是因为别的事务同时也可能访问这条记录，如果把它真正的删除加入到垃圾链表后，别的事务就访问不到了，这就是所谓的MVCC。

  • 根据更新后各列的值创建一条新记录，并将其插入到聚簇索引中（需重新定位插入的位置）。

针对 UPDATE 语句更新记录主键值的这种情况，在对该记录进行 delete mark 操作前，会记录一条类型为 TRX_UNDO_DEL_MARK_REC 的 undo日志 ；之后插入新记录时，会记录一条类型为 TRX_UNDO_INSERT_REC 的 undo日志 ，也就是说每对一条记录的主键值做改动时，会记录2条 undo日志 。

三. FIL_PAGE_UNDO_LOG页面

页面结构：

Undo Page Header ：

  • TRX_UNDO_PAGE_TYPE ：本页面准备存储什么种类的 undo日志 。

    主要分为两个大类：

    • TRX_UNDO_INSERT （使用十进制 1 表示，可直接删除）：类型为 TRX_UNDO_INSERT_REC 的 undo日志 属于此大类，一般由 INSERT 语句产生，或者在 UPDATE 语句中有更新主键的情况也会产生此类型的 undo日志 。
    • TRX_UNDO_UPDATE （使用十进制 2 表示，不可直接删除，为MVCC服务），除了类型为 TRX_UNDO_INSERT_REC 的 undo日志 ，其他类型的 undo日志 都属于这个大类，比如我们前边说的 TRX_UNDO_DEL_MARK_REC 、 TRX_UNDO_UPD_EXIST_REC 啥的，一般由 DELETE 、 UPDATE 语句产生的 undo日志 属于这个大类。

  • TRX_UNDO_PAGE_START ：表示在当前页面中是从什么位置开始存储 undo日志 的，或者说表示第一条 undo日志 在本页面中的起始偏移量。

  • TRX_UNDO_PAGE_FREE ：与上边的 TRX_UNDO_PAGE_START 对应，表示当前页面中存储的最后一条 undo 日志结束时的偏移量，或者说从这个位置开始，可以继续写入新的 undo日志 。

  • TRX_UNDO_PAGE_NODE ：代表一个 List Node 结构。

四. Undo页面链表

单个事务中的Undo页面链表

第一个 Undo页面 给标了出来，称它为 first undo page ，其余的 Undo页面 称之为 normal undo page ，这是因为在 first undo page 中除了记录 Undo Page Header 之外，还会记录其他的一些管理信息。

链表的分类：

初始时这些链表都不存在，按需分配，啥时候需要啥时候再分配，不需要就不分配 。

多个事务中的Undo页面链表

分事务即可。

五. undo日志写入过程

Undo Log Segment Header

设计 InnoDB 的大叔规定，每一个 Undo页面 链表都对应着一个 段 ，称之为 Undo Log Segment 。

仅存在于 first undo page 。

  • TRX_UNDO_STATE ：本 Undo页面 链表处在什么状态。

    一个 Undo Log Segment 可能处在的状态包括：

    • TRX_UNDO_ACTIVE ：活跃状态，也就是一个活跃的事务正在往这个段里边写入 undo日志 。
    • TRX_UNDO_CACHED ：被缓存的状态。处在该状态的 Undo页面 链表等待着之后被其他事务重用。
    • TRX_UNDO_TO_FREE ：对于 insert undo 链表来说，如果在它对应的事务提交之后，该链表不能被重用，那么就会处于这种状态。
    • TRX_UNDO_TO_PURGE ：对于 update undo 链表来说，如果在它对应的事务提交之后，该链表不能被重用，那么就会处于这种状态。
    • TRX_UNDO_PREPARED ：包含处于 PREPARE 阶段的事务产生的 undo日志 。

  • TRX_UNDO_LAST_LOG ：本 Undo页面 链表中最后一个 Undo Log Header 的位置。

  • TRX_UNDO_FSEG_HEADER ：本 Undo页面 链表对应的段的 Segment Header 信息。

  • TRX_UNDO_PAGE_LIST ： Undo页面 链表的基节点。

Undo Log Header

在每写入一组 undo日志 时，都会在这组 undo日志 前先记录一下关于这个组的一些属性，设计 InnoDB 的大叔把存储这些属性的地方称之为 Undo Log Header 。

仅存在于 first undo page 。

小结

对于没有被重用的 Undo页面 链表来说，链表的第一个页面，也就是 first undo page 在真正写入 undo日志 前，会填充 Undo Page Header 、 Undo Log Segment Header 、 Undo Log Header 这3个部分，之后才开始正式写入 undo日志 。对于其他的页面来说，也就是 normal undo page 在真正写入 undo日志 前，只会填充 Undo Page Header 。链表的 List Base Node 存放到 first undo page 的 Undo Log Segment Header 部分， List Node 信息存放到每一个 Undo页面 的 undo Page Header 部分，所以画一个 Undo页面 链表的示意图就是这样：

六. 重用Undo页面

条件：

  • 该链表中只包含一个 Undo页面 。
  • 该 Undo页面 已经使用的空间小于整个页面空间的3/4。

Undo页面 链表按照存储的 undo日志 所属的大类可以被分为 insert undo链表 和 update undo链表 两种，这两种链表在被重用时的策略也是不同的，我们分别看一下：

  • insert undo链表

    insert undo链表 中只存储类型为 TRX_UNDO_INSERT_REC 的 undo日志 ，这种类型的 undo日志 在事务提交之后就没用了，就可以被清除掉。所以在某个事务提交后，重用这个事务的 insert undo链表 （这个链表中只有一个页面）时，可以直接把之前事务写入的一组 undo日志 覆盖掉，从头开始写入新事务的一组 undo日志 。

  • update undo链表

    update undo链表 中的 undo日志 不能立即删除掉（为了MVCC），只能在后面接着写。

七. 回滚段

一个事务在执行过程中最多可以分配4个 Undo页面 链表，在同一时刻不同事务拥有的 Undo页面 链表是不一样的，所以在同一时刻系统里其实可以有许许多多个 Undo页面 链表存在。为了更好的管理这些链表，设计 InnoDB 的大叔又设计了一个称之为 Rollback Segment Header 的页面，在这个页面中存放了各个 Undo页面 链表的 frist undo page 的 页号 ，他们把这些 页号 称之为 undo slot 。

从回滚段中申请Undo页面链表

初始情况下，由于未向任何事务分配任何 Undo页面 链表，所以对于一个 Rollback Segment Header 页面来说，它的各个 undo slot 都被设置成了一个特殊的值： FIL_NULL （对应的十六进制就是 0xFFFFFFFF ），表示该 undo slot 不指向任何页面。

分配过程即寻找第一个不为FIL_NULL的undo slot。

当一个事务提交时，它所占用的 undo slot 有两种命运：

  • 如果该 undo slot 指向的 Undo页面 链表符合被重用的条件（就是我们上边说的 Undo页面 链表只占用一个页面并且已使用空间小于整个页面的3/4）。

    该 undo slot 就处于被缓存的状态，设计 InnoDB 的大叔规定这时该 Undo页面 链表的 TRX_UNDO_STATE 属性（该属性在 first undo page 的 Undo Log Segment Header 部分）会被设置为 TRX_UNDO_CACHED 。

  • 如果该 undo slot 指向的 Undo页面 链表不符合被重用的条件，那么针对该 undo slot 对应的 Undo页面 链表类型不同，也会有不同的处理：

    • insert undo链表

      TRX_UNDO_STATE 属性会被设置为 TRX_UNDO_TO_FREE ，直接释放，undo slot 的值设置为 FIL_NULL 。

    • update undo链表

      TRX_UNDO_STATE 属性会被设置为 TRX_UNDO_TO_PRUGE ， undo slot 的值设置为 FIL_NULL ，然后将本次事务写入的一组 undo 日志放到所谓的 History链表

多个回滚段

为事务分配Undo页面链表详细过程

  • 事务在执行过程中对普通表的记录首次做改动之前，首先会到系统表空间的第 5 号页面中分配一个回滚段（其实就是获取一个 Rollback Segment Header 页面的地址）。一旦某个回滚段被分配给了这个事务，那么之后该事务中再对普通表的记录做改动时，就不会重复分配了。

    使用传说中的 round-robin （循环使用）方式来分配回滚段。比如当前事务分配了第 0 号回滚段，那么下一个事务就要分配第 33 号回滚段，下下个事务就要分配第 34 号回滚段，简单一点的说就是这些回滚段被轮着分配给不同的事务（就是这么简单粗暴，没啥好说的）。

  • 在分配到回滚段后，首先看一下这个回滚段的两个 cached链表 有没有已经缓存了的 undo slot ，比如如果事务做的是 INSERT 操作，就去回滚段对应的 insert undo cached链表 中看看有没有缓存的 undo slot ；如果事务做的是 DELETE 操作，就去回滚段对应的 update undo cached链表 中看看有没有缓存的 undo slot 。如果有缓存的 undo slot ，那么就把这个缓存的 undo slot 分配给该事务。

  • 如果没有缓存的 undo slot 可供分配，那么就要到 Rollback Segment Header 页面中找一个可用的 undo slot 分配给当前事务。

    从 Rollback Segment Header 页面中分配可用的 undo slot 的方式我们上边也说过了，就是从第 0 个 undo slot 开始，如果该 undo slot 的值为 FIL_NULL ，意味着这个 undo slot 是空闲的，就把这个 undo slot 分配给当前事务，否则查看第 1 个 undo slot 是否满足条件，依次类推，直到最后一个 undo slot 。如果这 1024 个 undo slot 都没有值为 FIL_NULL 的情况，就直接报错喽（一般不会出现这种情况）～

  • 找到可用的 undo slot 后，如果该 undo slot 是从 cached链表 中获取的，那么它对应的 Undo Log Segment 已经分配了，否则的话需要重新分配一个 Undo Log Segment ，然后从该 Undo Log Segment 中申请一个页面作为 Undo页面 链表的 first undo page 。

  • 然后事务就可以把 undo日志 写入到上边申请的 Undo页面 链表了！

  • 事务在执行过程中对普通表的记录首次做改动之前，首先会到系统表空间的第 5 号页面中分配一个回滚段（其实就是获取一个 Rollback Segment Header 页面的地址）。一旦某个回滚段被分配给了这个事务，那么之后该事务中再对普通表的记录做改动时，就不会重复分配了。

    使用传说中的 round-robin （循环使用）方式来分配回滚段。比如当前事务分配了第 0 号回滚段，那么下一个事务就要分配第 33 号回滚段，下下个事务就要分配第 34 号回滚段，简单一点的说就是这些回滚段被轮着分配给不同的事务（就是这么简单粗暴，没啥好说的）。

  • 在分配到回滚段后，首先看一下这个回滚段的两个 cached链表 有没有已经缓存了的 undo slot ，比如如果事务做的是 INSERT 操作，就去回滚段对应的 insert undo cached链表 中看看有没有缓存的 undo slot ；如果事务做的是 DELETE 操作，就去回滚段对应的 update undo cached链表 中看看有没有缓存的 undo slot 。如果有缓存的 undo slot ，那么就把这个缓存的 undo slot 分配给该事务。

  • 如果没有缓存的 undo slot 可供分配，那么就要到 Rollback Segment Header 页面中找一个可用的 undo slot 分配给当前事务。

    从 Rollback Segment Header 页面中分配可用的 undo slot 的方式我们上边也说过了，就是从第 0 个 undo slot 开始，如果该 undo slot 的值为 FIL_NULL ，意味着这个 undo slot 是空闲的，就把这个 undo slot 分配给当前事务，否则查看第 1 个 undo slot 是否满足条件，依次类推，直到最后一个 undo slot 。如果这 1024 个 undo slot 都没有值为 FIL_NULL 的情况，就直接报错喽（一般不会出现这种情况）～

  • 找到可用的 undo slot 后，如果该 undo slot 是从 cached链表 中获取的，那么它对应的 Undo Log Segment 已经分配了，否则的话需要重新分配一个 Undo Log Segment ，然后从该 Undo Log Segment 中申请一个页面作为 Undo页面 链表的 first undo page 。

  • 然后事务就可以把 undo日志 写入到上边申请的 Undo页面 链表了！

事务隔离级别和MVCC

一. 事务隔离级别

访问相同数据的事务在不保证串行执行的情况下可能会出现的问题：

  • 脏写（ Dirty Write ）

    如果 一个事务修改了另一个未提交事务修改过的数据 ，那就意味着发生了 脏写。

  • 脏读（ Dirty Read ）

    如果 一个事务读到了另一个未提交事务修改过的数据 ，那就意味着发生了 脏读 。

  • 不可重复读（Non-Repeatable Read）

    如果 一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值 ，那就意味着发生了 不可重复读 。

  • 幻读（Phantom）

    如果 一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来 ，那就意味着发生了 幻读 。

    对于先前已经读到的记录，之后又读取不到这种情况，相当于对每一条记录都发生了不可重复读的现象。幻读只是重点强调了读取到了之前读取没有获取到的记录。

SQL标准中的四种隔离级别

严重性排序：

1
脏写 > 脏读 > 不可重复读 > 幻读

SQL标准 ，四个隔离级别：

  • READ UNCOMMITTED ：未提交读。
  • READ COMMITTED ：已提交读。
  • REPEATABLE READ ：可重复读。
  • SERIALIZABLE ：可串行化。
隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED Not Possible Possible Possible
REPEATABLE READ Not Possible Not Possible Possible
SERIALIZABLE Not Possible Not Possible Not Possible

MySQL中支持的四种隔离级别

不同的数据库厂商对 SQL标准 中规定的四种隔离级别支持不一样，比方说 Oracle 就只支持 READ COMMITTED 和 SERIALIZABLE 隔离级别。本书中所讨论的 MySQL 虽然支持4种隔离级别，但与 SQL标准 中所规定的各级隔离级别允许发生的问题却有些出入， MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的。

MySQL 的默认隔离级别为 REPEATABLE READ ，我们可以手动修改一下事务的隔离级别。

设置事务的隔离级别

1
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

  • 使用 GLOBAL 关键字（在全局范围影响）：

    • 只对执行完该语句之后产生的会话起作用。
    • 当前已经存在的会话无效。

  • 使用 SESSION 关键字（在会话范围影响）：

    • 对当前会话的所有后续的事务有效
    • 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。
    • 如果在事务之间执行，则对后续的事务有效。

  • 上述两个关键字都不用（只对执行语句后的下一个事务产生影响）：

    • 只对当前会话中下一个即将开启的事务有效。
    • 下一个事务执行完后，后续事务将恢复到之前的隔离级别。
    • 该语句不能在已经开启的事务中间执行，会报错的。

二. MVCC原理

版本链

对记录每次更新后，都会将旧值放到一条 undo日志 中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称之为 版本链 ， 版本链的头节点就是当前记录最新的值 。

ReadView

对于使用 READ UNCOMMITTED 隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了；对于使用 SERIALIZABLE 隔离级别的事务来说，设计 InnoDB 的大叔规定使用加锁的方式来访问记录；对于使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是： 需要判断一下版本链中的哪个版本是当前事务可见的 。为此，设计 InnoDB 的大叔提出了一个 ReadView 的概念，这个 ReadView 中主要包含4个比较重要的内容：

  • m_ids ：表示在生成 ReadView 时当前系统中活跃的读写事务的 事务id 列表。
  • min_trx_id ：表示在生成 ReadView 时当前系统中活跃的读写事务中最小的 事务id ，也就是 m_ids 中的最小值。
  • max_trx_id ：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。
  • creator_trx_id ：表示生成该 ReadView 的事务的 事务id 。

判断版本是否可见：

  • 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  • 如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
  • 如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
  • 如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

READ COMMITTED 和 REPEATABLE READ 生成ReadView的时机不同：

  • READ COMMITTED —— 每次读取数据前都生成一个ReadView

  • REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

小结

MVCC （Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的 读-写 、 写-读 操作并发执行，从而提升系统性能。

锁

并发事务访问相同记录的情况大致可以划分为3种：

  • 读-读 情况：即并发事务相继读取相同的记录。

    读取操作对记录本身没有任何影响，允许这种情况发生。

  • 写-写 情况：即并发事务相继对相同的记录做出改动。

    即脏写，不允许这种情况发生，加锁解决。

  • 读-写 或 写-读 情况：也就是一个事务进行读取操作，另一个进行改动操作。

    • 方案一：读操作利用多版本并发控制（ MVCC ），写操作进行 加锁 。（性能高）
    • 方案二：读、写操作都采用 加锁 的方式。（性能低）

一. 解决并发事务带来问题的两种基本方式

一致性读（Consistent Reads）

事务利用 MVCC 进行的读取操作称之为 一致性读 ，或者 一致性无锁读 ，有的地方也称之为 快照读 。

锁定读（Locking Reads）

锁的分类：

  • 共享锁 ，英文名： Shared Locks ，简称 S锁 。在事务要读取一条记录时，需要先获取该记录的 S锁 。
  • 独占锁 ，也常称 排他锁 ，英文名： Exclusive Locks ，简称 X锁 。在事务要改动一条记录时，需要先获取该记录的 X锁 。
兼容性 X S
X 不兼容 不兼容
S 不兼容 兼容

  • 对读取的记录加 S锁 ：

    其它事务可以获取该记录的S锁，不可以获取X锁。

  • 对读取的记录加 X锁 ：

    其它事务不允许获取该记录的S锁和X锁。

写操作

  • DELETE ：加X锁

  • UPDATE ：

    • 未修改主键值：

      • 就地更新：加X锁
      • 非就地更新：对删除操作加X锁，对插入操作有隐式锁

    • 修改主键值：

      按照DELETE和INSERT操作加锁规则

  • INSERT ：一般情况下不加锁，具有隐式锁

二. 多粒度锁

兼容性 X IX S IS
X 不兼容 不兼容 不兼容 不兼容
IX 不兼容 兼容 不兼容 兼容
S 不兼容 不兼容 兼容 兼容
IS 不兼容 兼容 兼容 兼容

IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。

三. MySQL中的行锁和表锁

对于 MyISAM 、 MEMORY 、 MERGE 这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。

InnoDB存储引擎中的锁

InnoDB中的表级锁

  • 表级别的 S锁 、 X锁

    在对某个表执行 SELECT 、 INSERT 、 DELETE 、 UPDATE 语句时， InnoDB 存储引擎是不会为这个表添加表级别的 S锁 或者 X锁 的。

    另外，在对某个表执行一些诸如 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时，其他事务对这个表并发执行诸如 SELECT 、 INSERT 、 DELETE 、 UPDATE 的语句会发生阻塞，同理，某个事务中对某个表执行 SELECT 、 INSERT 、 DELETE 、 UPDATE 语句时，在其他会话中对这个表执行 DDL 语句也会发生阻塞。这个过程其实是通过在 server层 使用一种称之为 元数据锁 （英文名： Metadata Locks ，简称 MDL ）东东来实现的，一般情况下也不会使用 InnoDB 存储引擎自己提供的表级别的 S锁 和 X锁 。

  • 表级别的 IS锁 、 IX锁

  • 表级别的 AUTO-INC锁

    在使用 MySQL 过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值，这个赋值便需要保证唯一性，因此需要加锁。

    • 采用 AUTO-INC 锁，也就是在执行插入语句时就在表级别加一个 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把 AUTO-INC 锁释放掉。这样一个事务在持有 AUTO-INC 锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。

      AUTO-INC锁的作用范围只是单个插入语句，插入语句执行完成后，这个锁就被释放了。

    • 采用一个轻量级的锁，在为插入语句生成 AUTO_INCREMENT 修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的 AUTO_INCREMENT 列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。

    一般情况下，若插入记录的数量是确定的，采用轻量级锁，否则采用AUTO_INC锁。

InnoDB中的行级锁

  • Record Locks （ LOCK_REC_NOT_GAP ）：

    即记录锁，对记录加锁。

  • Gap Locks （ LOCK_GAP ）：

    gap锁 的提出 仅仅是为了防止插入幻影记录而提出的，不会影响其它记录对该记录再加记录锁和gap锁。

  • Next-Key Locks （ LOCK_ORDINARY ）：

    next-key锁 的本质就是一个 正经记录锁 和一个 gap锁 的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的 间隙 。

  • Insert Intention Locks （ LOCK_INSERT_INTENTION）：

    我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了所谓的 gap锁 （ next-key锁 也包含 gap锁 ，后边就不强调了），如果有的话，插入操作需要等待，直到拥有 gap锁 的那个事务提交。但是设计 InnoDB 的大叔规定事务在等待的时候也需要在内存中生成一个 锁结构 ，表明有事务想在某个 间隙 中插入新记录，但是现在在等待。插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

  • 隐式锁

    如果一个事务首先插入了一条记录（此时并没有与该记录关联的锁结构），然后另一个事务想要读取或修改该记录，则会产生脏读和脏写。

    • 对于聚簇索引记录来说，有一个 trx_id 隐藏列，该隐藏列记录着最后改动该记录的 事务id 。那么如果在当前事务中新插入一条聚簇索引记录后，该记录的 trx_id 隐藏列代表的的就是当前事务的 事务id ，如果其他事务此时想对该记录添加 S锁 或者 X锁 时，首先会看一下该记录的 trx_id 隐藏列代表的事务是否是当前的活跃事务，如果是的话，那么就帮助当前事务创建一个 X锁 （也就是为当前事务创建一个锁结构， is_waiting 属性是 false ），然后自己进入等待状态（也就是为自己也创建一个锁结构， is_waiting 属性是 true ）。
    • 情景二：对于二级索引记录来说，本身并没有 trx_id 隐藏列，但是在二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的 事务id ，如果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃 事务id ，那么说明对该页面做修改的事务都已经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记录，然后再重复 情景一 的做法。

    即其它事务会帮当前事务生成锁。

InnoDB锁的内存结构

锁结构：

  • 锁所在的事务信息 ：

    不论是 表锁 还是 行锁 ，都是在事务执行过程中生成的，哪个事务生成了这个 锁结构 ，这里就记载着这个事务的信息。

    本质上是一个指向内存当中事务信息的指针。

  • 索引信息 ：

    对于 行锁 来说，需要记录一下加锁的记录是属于哪个索引的。本质也为指针。

  • 表锁／行锁信息 ：

    表锁结构 和 行锁结构 在这个位置的内容是不同的：

    • 表锁：

      记载着这是对哪个表加的锁，还有其他的一些信息。

    • 行锁：

      记载了三个重要的信息：

      • Space ID ：记录所在表空间。

      • Page Number ：记录所在页号。

      • n_bits ：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个 n_bits 属性代表使用了多少比特位。

        一般比记录总数大，为了防止以后再添加记录时重新分配锁结构。

  • type_mode ：

    这是一个32位的数，被分成了 lock_mode 、 lock_type 和 rec_lock_type 三个部分，如图所示：

    • 锁的模式（ lock_mode ），占用低4位，可选的值如下：
      • LOCK_IS （十进制的 0 ）：表示共享意向锁，也就是 IS锁 。
      • LOCK_IX （十进制的 1 ）：表示独占意向锁，也就是 IX锁 。
      • LOCK_S （十进制的 2 ）：表示共享锁，也就是 S锁 。
      • LOCK_X （十进制的 3 ）：表示独占锁，也就是 X锁 。
      • LOCK_AUTO_INC （十进制的 4 ）：表示 AUTO-INC锁 。
    • 锁的类型（ lock_type ），占用第5～8位，不过现阶段只有第5位和第6位被使用：
      • LOCK_TABLE （十进制的 16 ），也就是当第5个比特位置为1时，表示表级锁。
      • LOCK_REC （十进制的 32 ），也就是当第6个比特位置为1时，表示行级锁。
    • 行锁的具体类型（ rec_lock_type ），使用其余的位来表示。只有在 lock_type 的值为 LOCK_REC 时，也就是只有在该锁为行级锁时，才会被细分为更多的类型：
      • LOCK_ORDINARY （十进制的 0 ）：表示 next-key锁 。
      • LOCK_GAP （十进制的 512 ）：也就是当第10个比特位置为1时，表示 gap锁 。
      • LOCK_REC_NOT_GAP （十进制的 1024 ）：也就是当第11个比特位置为1时，表示 正经记录锁 。
      • LOCK_INSERT_INTENTION （十进制的 2048 ）：也就是当第12个比特位置为1时，表示插入意向锁。
      • 其他的类型：还有一些不常用的类型我们就不多说了。
      • LOCK_WAIT （十进制的 256 ） ：也就是当第9个比特位置为 1 时，表示 is_waiting 为 true ，也就是当前事务尚未获取到锁，处在等待状态；当这个比特位为 0 时，表示 is_waiting 为 false ，也就是当前事务获取锁成功。

  • 一堆比特位 ：

    一个比特位映射一个 heap_no 即一条记录，映射方式：