1. MySQL 体系架构

MySQL 的架构整体上可以分为服务层和引擎层:

Server 层涵盖了 MySQL 大多数核心服务, 包括请求的接收, 以及绝大多数内置函数(如 DATE()等)
Engine 层负责数据的读写

1.1 连接器

负责建立与客户端建立连接, 获取权限, 维持和管理连接

通过 TCP 连接, 验证用户身份, 当连接到达时获取用户当前所有权限, 而权限的获取是一次性的, 也就是说即使登录后对该用户的权限做了修改, 也无法立即生效, 需要等到用户下一次登录 MySQL 才能体现.

常用的登录命令:

1 2	~$ mysql -h$IP -P$PORT -u$USER -p Enter password: # 此时再输入密码

然后再输入密码, 虽然 -p 后可以直接跟密码, 但此时界面不会对密码进行隐藏, 为了安全起见还是建议使用前者

在登录之后, 可以通过 show processlist 命令查询当前所有生效的连接, 下图是我通过两个终端分别登录本机的 MySQL, 并使用第二个连接执行该命令的结果

登录成功后, 如果没有后续的操作, 连接会处于 Sleep 状态, 如上图中 Id 为 3 的连接, 表示系统中存在的一个空闲连接. 而 Id 为 4 的连接此时由于正在执行 show processlist 命令, 因此 Command 列值为 Query

MySQL 连接默认的超时时间为 8 小时, 意味着该连接如果 8 小时内没有进行任何的操作, 就会被系统逐出. 超时失效后的连接如果试图再执行任何操作, 都会被告知 Lost connection to MySQL server during query

1.2 查询缓存

MySQL 所有的查询请求都会先从查询缓存中查找, 其内容可以看做一个一个典型的映射关系

1	Map<SQL 语句, 结果集>

如果查询语句命中缓存就不会执行后面的操作

虽然看起来很美好, 但 MySQL 为此做了相对复杂的缓存一致性的维护, 对表的任何写操作都会导致使用该表所对应的缓存全部失效.

为什么需要全部失效呢? 因为 MySQL 对于范围查询的侦测基本上无能为力, 假设我们有如下语句:

1	SELECT name, score FROM student WHERE score > 90;

这样一个典型的区间查询, 假设有如下操作:

插入一条 score 为 92 的字段;
假设有一个 name 为 Bob 的记录, score 为 80, 现在将其修改为 91;

执行这样的操作时, MySQL 难以实现也没有必要去完成对缓存细粒度的更新, 因此任何写操作都会导致该表的全部缓存失效.

这样的机制就带来了一个问题: 对于写操作比较频繁的表, 对应缓存失效非常频繁, 导致白白浪费内存和 CPU. 因此可以在配置中禁用缓存模块, 甚至在 MySQL8.0 之后, 官方已经彻底将缓存模块删除.

1.3 分析器

分析器是执行 SQL 的第一步

1.3.1 词法分析

解析字符串中每个单词的含义, 建立连接后, 客户端都是已一条字符串格式的 SQL 语句与 MySQL 进行交互, 假设客户端传入了如下一条 SQL 语句

1	SELECT id, name, gender, score FROM student WHERE grade = 4 ORDER BY score LIMIT 0, 100;

在进行词法分析的时候, 会进行如下操作:

从 SELECT 判断出这是一条查询语句
从 id, name, gender, score 识别为列名
从 student 识别出表名
…

分析的的输出是一棵语法树, 语法树的节点主要分为以下两种类型:

单个元素, 例如关键字, 表名, 运算符等
子语句, 例如子查询, 而每个子语句也有一棵语法树用来表示自身的所有单个元素和子语句

1.3.2 语法分析

根据词法分析的结果和语法规则判断输入的 SQL 语句是否满足 MySQL 语法

1.3.3 语义分析

1.4 优化器

经过分析器, MySQL 已经理解了 SQL 语句要做什么, 现在需要进行优化操作

根据规则(扫描行数/是否排序等)决定使用哪条索引
进行多表关联的时候, 决定表的连接顺序

最终确定执行方案

1.5 执行器

先判断用户对表有没有相应的执行权限, 如果有权限, 根据表所属的引擎调用不同接口. 至于为什么在此处才查询是否有权限, 是因为有时候 SQL 语句需要操作的表不只是 SQL 语句中使用的, 例如当有触发器需要执行时, 涉及的表就没有体现在 SQL 语句中. 查询语句会优先执行 获取满足条件的第一行 接口, 然后再循环调用 查询满足条件的下一行 接口

2. MySQL 日志系统

这里主要介绍两种日志, 慢查询日志和二进制日志(BinLog)

RedoLog(重做日志) 和 UndoLog(回滚日志)属于 InnoDB 提供的特性, 而非 MySQL 提供, 对二者的介绍会放在事务的实现一章.

2.1 慢查询日志

2.2 BinLog

BinLog 记录了对 MySQL 数据库执行更改的所有操作, BinLog 功能会将所有事务的操作通过日志的形式追加到磁盘中持久化, 不存在被自动覆盖的情况.

BinLog 是 MySQL server 层的概念, 与存储引擎无关, 但大部分支持事务的存储引擎都实现了 BinLog 的整合, 例如 InnoDB 中 BinLog 的持久化是事务中的一个步骤, InnoDB 会等待 MySQL 返回 BinLog 持久化的结果, 再决定自身是提交还是回滚, 因此对 InnoDB 来说, 任何提交的事务必然存在 BinLog.

2.2.1 BinLog 内容

BinLog 有两种形式:

形式	描述
STATEMENT	BinLog 记录的是执行的 SQL 语句本身, 优点是节省空间, 缺点是有些特定的函数在不同情况下得到的结果不同
ROW	BinLog 记录的是记录的修改情况, 假设一条 SQL 语句修改了 100 条语句, 该模式下 BinLog 会记录这 100 条语句的被修改情况, 缺点是浪费空间, 优点是记录的更为准确, 也不会出现 STATEMENT 模式的问题
MIXED	是以上两种模式的混合, 一般的语句修改使用 STATEMENT 保存, 而如果存在某些 STATEMENT 无法完成主从复制的操作, 则采用 ROW 格式保存.

假设有如下表:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_t_modified`(`t_modified`)
) ENGINE=InnoDB;

当我们执行:

1	delete from t where a >= 4 and t_modified <= '2019-09-04' limit 1;

如果 BinLog 格式为 statement, BinLog 中记录的就是 SQL 语句的原文, 但这样一条看似没有问题的 BinLog 如果用来复现数据, MySQL 会抛出一条警告, 因为这条 delete 语句带 limit, 贸然执行可能会带来数据不一致的场景:

如果 delete 语句使用的是索引 idx_a, 那么会根据 idx_a 找到第一个满足条件的行
如果 delete 语句使用的索引是 idx_t_midified, 那么会根据 idx_t_midified 删除第一个满足条件的行.

如果 BinLog 格式为 row, 此时的 BinLog 原文中不会存在 SQL 语句原文, 而是替换成了两个 event:

Table_map event: 标识后面的操作是基于哪张表;
Delete_rows event: 用于定义删除行为.

此时 BinLog 中记录的是真实被删除记录的主键, 当然不会出现主备删除不同行的问题.

此外, statement 模式下对一些函数做了处理, 例如 NOW(), 不会出现在主库从库分别记录当前时间的情况, 原理是在BinLog 生成时, 多记了一条命令: SET TIMESTAMP=1567611268, 通过这条命令, 让 MySQL 显式确保主备数据的一致性.

2.2.2 BinLog 功能

总的来说, BinLog 具有以下功能:

恢复数据, 数据库存在误操作的可能, 假设某个时间被删库跑路应该如何防范?
比较常见的方式是采用定级备份 + BinLog 恢复. 定时备份可以选择每日或者每周进行一次, BinLog 会一直追加. 假设数据库在 t1 时刻被删库跑路, 而距离 t1 最近的一次全量备份发生在 t0, 那么首先需要将 t0 时刻的副本覆盖, 然后就可以通过全量执行 t0 ~ t1 期间的全量 BinLog 来将数据库恢复到 t1 时刻的状态.
主备复制, 目前数据库集群在主备模式下, 一般都使用 BinLog 来实现主从复制. 每个备库会定时从主库进行 BinLog 的同步去执行. 每个备库都维护了自身的同步进度, 同步时会根据自己当前额进度去获取其后的 BinLog.
业务需求, 业务系统间有时会通过监听 BinLog 的方式去实现通信. 如某个系统本身逻辑比较复杂, 但只需要关心其写入 DB 的数据情况, 此时就可以通过监听该系统所用数据库的 BinLog 即可. 常见的工具有 Cannal, Maxwell 等.

BinLog 的日志文件格式为二进制, 其产生的二进制文件不能通过 vim, cat, tail 等命令直接查看, 需要使用 MySQL 提供的专用查看工具 mysqlbinlog 进行查看.

BinLog 的写入机制

事务执行过程中, 先把日志写到 BinLog Cache, 事务提交的时候再把 BinLog Cache 写入到 BinLog 文件中.

一个事务的 BinLog 不能被拆开, 再大的事务也要确保一次性写入. MySQL 给每个线程分配了一块 BinLog Cache 的内存, 如果超过了这个大小就需要暂存到磁盘, 事务提交的时候执行器把 BinLog Cache 里完整事务写入到 BinLog 中, 并清空 BinLog Cache.

每个线程都有自己的 BinLog Cache, 但是共用一份 BinLog 文件.

write 操作指的是将日志写入文件系统的 Page cache, 并没有落盘, 速度较快;
fsync 会落盘

wirte 和 fsync 的时机由 sync_binlog 控制:

sync_binlog = 0, 每次提交只 write, 不 fsync
sync_binlog = 1, 每次提交既 write, 又 fsync
sync_binlog = N(N > 1), 每次提交都 write, 累计 N 个后再 fsync

实际业务场景中, 考虑到丢失日志量的可控性, 通常会设置为 100~1000 之间, 但这样的话如果 MySQL 宕机重启, 会丢失最新一部分事务的 BinLog 日志.

3. MySQL 索引

3.1 索引概述

常见的索引有如下几种:

哈希表
搜索树

哈希表示一种以 k-v 形式存储数据的结构, 其典型的实现有 Java 中的 HashMap 等, 只要输入查询的 key, 就可以找到其对应的 value. 哈希表的实现方式比较简单, 根据 key 计算出一个哈希值, 然后放在数组的某个特定位置, 常见的 数组+链表挂链 的形式就是对哈希表的实现.

哈希表的插入和查询性能十分优秀, 通常可以认为其 get/set 方法的时间复杂度是 O(1). 对于等值查询通常是首选, 在 Redis, Memcache 中均有广泛应用. 但由于其 key 的排布无序, 虽然在 put 新元素时由于不需要考虑顺序因此非常快, 但却无法处理区间查询(大于和小于)

最典型的搜索树结构就是二叉查找树, 二叉树的特点是左子树的值小于等于双亲结点, 右子树的值大于双亲结点, 在查询的时候应用二分查找的原理能够做到理想情况下 O(log(N)) 级别的插入和查询, 并且由于其本身就是有序的, 因此天然支持区间查询.

如果能够加上自平衡的功能, 例如红黑树, 确实作为索引的性能已经比较理想, 但是这样的结论仅限于内存中的数据结构.

由于数据库系统的数据和索引需要存储在磁盘上, 而对于正常的机械磁盘来说, 一次随机读平均耗时 10ms, 其实时间主要消耗在寻到和旋转磁头的延迟上了. 而顺序读一条数据的消耗大概不到前者的 1%, 因此如果想作为一个对磁盘友好的索引结构, 不能只考虑内存中的性能, 还需要尽可能的降低随机读的频率.

那么应该如何去降低随机读的频率呢? 以红黑树为例, 假设有一千万条数据, 红黑树最少需要 24 层能够容纳得下, 那就意味着如果想根据磁盘中的红黑树找到磁盘中的数据, 就需要随机读 24 次磁盘, 这显然是不能接受的, 因此想降低随机读的频率, 首先就需要尽可能降低查询次数, 也就是降低树的深度. 在数据总量保持不变的前提下, 如果想降低树的深度, 最可行的办法就是将二叉树变为多叉树. 每个节点变成多叉树之后, 其双亲结点内部相应的需要维护一个小索引(假设是 10 叉树, 则双亲节点内部需要维护其负责的 10 个区间对应的指针), 但其实这个成本是可以忽略不计的, 因为我们一次将其从磁盘中取出, 每个节点内部的索引操作都是在内存中完成. 这样就能避免在一次查询中过多操作磁盘.

因此引出了 InnoDB 索引的实现: B+树, B+树就是为了充分利用磁盘预读功能而设计的一种数据结构:

磁盘预读与局部性原理:
由于存储介质的特性, 磁盘的IO 速度远远低于主存, 因此为了提高效率, 要尽量减少磁盘 IO, 因此磁盘往往不是严格按需读取, 而是每次会预读一块数据, 即使只读一个字节, 磁盘也会从这个位置开始, 顺序向后读取一定长度(默认 4k)的数据放入内存, 这样做的理论依据是注明的局部性原理:
当一个数据被用到时, 其附近的数据也通常马上会被使用.

B+ 数每个节点可以存储多个关键字, 它将节点大小设置为磁盘页的大小, 充分利用了磁盘预读的功能, 每次读取磁盘页的时候就会读取整个节点, 也正因为每个节点存储着非常多的关键字(InnoDB 每个双亲结点大概可以存储 1200 个子节点), 会使得树深度很小, 进而要执行的磁盘读取操作次数就会非常少, 更多的是在内存中对读取的数据进行查询操作, 而这部分操作的消耗往往可以忽略不计.

3.2 InnoDB 索引模型

InnoDB 支持以下几种索引:

B+ 树索引
全文索引
自适应性哈希索引

B+ 树索引就是传统意义上的索引, 是目前关系型数据系统中查找数据最为常用和有效的索引. 结构类似于一棵多叉树, 根据键快速找到数据. 自适应性哈希索引, 顾名思义是由 InnoDB 根据实时的查询情况自动为表生成的索引, 不能人为干预.

B+ 数索引的本质就是 B+ 树在数据库中的实现. 在 InnoDB 中, 每个 B+ 数的双亲节点大致可以保存 1200 个子节点, 可以近似理解为 1200 叉树, 那么即使在面对亿级数据量时, 也能够做到不超过 4 层, 并且 B+ 数的第二层基本会常驻内存, 因此平均场景下, InnoDB 通过索引查询一条记录最多只需要 2~~4 次磁盘 IO, 意味着查询时间大致需要 20~~40ms.

在 InnoDB 中, 表都是根据主键顺序以索引的形式存放的, 这种存储方式称之为索引组织表. 所有的数据都存储在主键的 B+ 树中.
除了主键以外的其他索引被称为辅助索引, 叶子节点存储着索引字段和主键的映射, 在通过辅助索引查询时, 需要先从辅助索引中找到记录的主键, 再回到主索引查询对应记录.

下面通过一个例子来解释一下 InnoDB 是如何通过索引快速定位数据的.

假设有以下表:

CREATE TABLE t (
    id INT PRIMARY KEY,
    k int not null,
    name varchar(16),
    index(k)
) engine=InnoDB

INSERT INTO t VALUES(10, 1, 'Alice');
INSERT INTO t VALUES(20, 2, 'Bob');
INSERT INTO t VALUES(30, 3, 'Carl');
INSERT INTO t VALUES(50, 5, 'David');
INSERT INTO t VALUES(60, 6, 'Eartha');

此时表中记录为

id	k	name
10	1	Alice
20	2	Bob
30	3	Carl
10	1	David
10	1	Eartha
10	1	Frank

如果查询语句是 SELECT * FROM t WHERE id = 10;, 及主键查询, 则只需所搜主索引;
如果查询语句是 SELECT * FROM t where k = 5;, 即普通索引查询, 则需要先搜索 k 索引树, 得到 id 值为 50, 再去主索引中搜索一次, 这个过程被称为回表.

3.3 索引维护

B+ 树是一种相对较为复杂的数据结构, 为了能够最大程度优化磁盘的读写, 引入了很多较为复杂的特性, 这里简单介绍一下 B+ 树节点的分裂与合并

B+ 树在插入和删除元素的时候, 都需要维护其有序性:

以上图为例, 加入插入的新行 id 为 70, 则只需要在 R5 后追加一条记录. 如果插入的 id 值为 40, 就相对麻烦一些, 需要将 R4 后的数据在逻辑上向后挪, 并将 id 为 40 的记录插入到 R3 之后, 更糟的情况是, 如果该叶子节点已满, 就需要申请一个新的数据页, 然后挪动一部分数据过去, 这个过程称为页的分裂, 频繁的分裂会对性能造成影响

叶子节点满	双亲节点满	操作
NO	NO	直接将记录插入到叶子节点
YES	NO	1. 拆分叶子节点 2. 将中间的节点放入双亲结点 3. 小于中间节点的记录放左边 4. 大于或等于中间节点的记录放右边
YES	YES	1. 拆分叶子节点 2. 小于中间节点的记录放在左边 3. 大于中间节点的记录放在右边 4. 拆分双亲结点 5. 小于中间节点的记录放左边 6. 大于中间节点的记录放右边 7. 中间节点放入上一层双亲结点

为什么 InnoDB 的表推荐使用自增主键
从上图的插入过程可以发现, 对 B+ 树来说, 效率最高的插入方式就是插入 id 最大的元素(未必需要递增, 只需要保证每次插入最大即可), 这样的插入永远是在最后一个叶子节点中向后最佳元素. 而其他情况下的插入则需考虑节点分裂. 对数据库来说, 实现 永远插入最大值 最简单的方式就是自增

3.4 联合索引

联合索引指的是对表上的多个列进行索引, 联合索引的创建方法也和单个索引相同, 唯一的不同之处在于有多个索引列. 底层结构也与普通索引基本相同, 不同之处在于联合索引的叶子节点中, key 是由多个值组成的, 并且 key 之间时按照多个列从左到右的顺序排序

联合索引能够解决相对复杂的查询逻辑, 同时对多个字段进行查询, 但其使用时必须遵循最左匹配原则.

上图是对两个 int 列进行联合索引的示意图, 可以看到, 联合索引中 key 的顺序先按照列 a 排序, 列 a 相同再按照列 b 排序, 这样类似字典序的排序方式.

由于这种特性, 如果想使用某条联合索引, 筛选条件中列的顺序必须严格符合联合索引的最左匹配, 因为如果跳过了某一列, 索引就不再有序, 假设把上图中的列 a 去掉, 列 b 的索引就变成了 [10, 15, 3, 5, 5] 显然无法发挥索引的功能.

假设现在有一条 a, b, c, d 列组成的联合索引, 那么能匹配该索引的查询语句为:
a -> b -> c -> d
a -> b -> c
a -> b
a

3.5 覆盖索引

如果一条查询语句能够从辅助索引中获得全部需要的信息, 那么就不再需要回表, 我们就将这样的索引成为覆盖索引.

对于 InnoDB 的辅助索引而言, 叶子节点的 key 为参与索引的所有字段, value 为主键信息, 假设该索引的字段为k1, k2, 那么如下查询语句都可以使用覆盖所以, 免去回表操作.

1 2	SELECT k1 FROM t WHERE k2 = ?; SELECT id, k1 FROM t WHERE k2 = ?;

3.6 索引的选择

由于一张表中可以存在多个索引(建议索引的数量不要超过 16 条), 但目前一条 SQL 语句只会选择一条索引去执行, 当 SQL 语句中没有明确规定走哪一条索引时, 就会由查询优化器来选择一条.

下面我们来聊一聊优化器是如何选择索引的.

查询优化器选择索引的目的, 是为了找到一个最优的方案, 最终以最小的代价去执行语句. 在绝大部分情况下, 查询优化器的行为都是符合预期的, 但既然查询优化器的行为也是由代码逻辑控制, 就可能在特定的情况下与预期不符.

先说说优化器选择的几个主要标准:

扫描行数: 这是最直接的指标, 扫描行数越多就意味着访问磁盘的次数越多, 消耗的 CPU 越多;
是否需要回表
是否使用临时表;
是否排序;

3.6.1 扫描行数

首先需要明确一个概念, MySQL 在真正开始执行语句前, 无法准确知道满足条件的记录有多少条, 只能根据 统计信息 来估算记录数.

统计信息就是索引的区分度, 我们在建立索引时普遍会选择区分度更高, 也就是值的离散程度更高的列作为索引. 而一个索引上不同值的个数, 我们称之为 基数, 基数越大, 索引的区分度越高.

在 MySQL 中, 可以使用 show index 方法查看一个索引的基数.

而 MySQL 获取索引基数的方式是通过采样统计, 也就是说这里的 cardinality 列只是一个估算的值. 真正执行一遍 SQL 语句再统计虽然可以得到较为准确的值, 但是一旦表中数据过大, 这项统计工作就会变得异常耗时.

在进行统计工作的时候, MySQL 会默认选择 N 个数据页, 统计这些页上不同的值, 得到这些页上的基数后, 得到一个平均值, 再乘以这个索引的数据页数, 就得到整条索引的基数. 而表的数据是会持续更新的, 因此索引的统计信息也不是一成不变的. 从上一次统计开始, 当整条索引上的数据行变更超过 1/M 的时候, 会自动触发重新进行一次索引统计.
MySQL 可以使用 innodb_stats_persistent 参数控制索引统计的行为

当设置为 on 的时候, 表示会将统计信息持久化存储, 此时默认 N 为 20, M 为 10.

当设置为 off 的时候, 表示统计信息只存储在内存中, 此时默认的 N 为 8, M 为 16.

我们可以看到当设置为 off 的时候, 统计采样的页数更少, 并且更新的更不活跃, 一般情况下设置为 on 会获得更好的统计效果. 但不论哪种采样方式, 与实际情况依然会存在一定偏差.

对 MySQL 的查询优化器来说, 大部分查询操作如果能通过主索引完成, 哪怕预计的扫描行数会更多, 也会优先选择主索引, 因为回表也是一种比较耗时的操作, 从辅助索引取出的没一行记录都需要再从主索引中找到整行记录在大部分情况下都会比直接走主索引更加耗时. 这一点, 在统计行数基本无误的情况下, 是没有问题的, 但假如统计行数出现了问题, 就可能会出现通过某一条辅助索引能很快定位, 优化器却选择了另一条扫描行数更多的索引.

而什么情况会导致 MySQL 对索引的采样统计出现偏差呢?

最容易想到的就是索引记录进行了大量的修改, 却没有到达触发下次采样统计的行为时
在数据库短时间进行了大量的删除和插入语句时, 由于 MySQL 是使用标记删除来删除记录的,并不从索引和数据文件中真正的删除, 如果 delete 和 insert 中间的间隔相对较小,purge线程还没有来得及清理该记录. 如果主键相同的情况下, 新插入的insert会沿用之前删除的delete的记录的空间. 由于相同的近似的以及表大小,所以导致了统计信息没有变化

遇到由于索引基数采样统计不准确而导致的索引选择问题, 可以通过重新统计索引信息的命令来处理:

1	ANALYZE TABLE t

3.6.2 是否会回表

回表也是一个相对耗时的操作, 对于一个满足覆盖索引的查询语句来说, 执行步骤通常是这样的:

从辅助索引中找到第一条符合条件的记录, 并将其需要的字段放入结果集中
从辅助索引中找到下一条符合条件的记录, 并将其需要的字段放入结果集中
重复第 2 步, 直到辅助索引中下一条记录不再符合条件
向客户端返回

而对于需要回表的查询语句, 执行步骤会变成:

从辅助索引中找到第一条符合条件的记录
拿到其主键, 通过主索引找到整行记录, 并将其需要的字段防入结果集中
从辅助索引中找到吓一跳符合条件的记录
拿到其主键, 通过主索引找到整行记录, 并将其需要的字段放入结果集中
重复 3/4 步骤, 直到辅助索引中下一条记录不再符合条件

不难看出, 一旦脱离覆盖索引, 最坏情况下辅助索引筛选出的每条记录都需要进行一次磁盘 IO, 这个代价是比较大的, 因此会出现如果一条查询语句同时通过主键和辅助索引筛选, 即便辅助索引扫描行数小于主键, 优化器也会选择使用主键

3.6.3 是否需要排序

排序的情况也和回表类似, 排序也是一个相对耗时的操作, 尤其是大数据量的排序, 如果无法直接在内存中完成, MySQL 会借助临时文件进行基于归并思想的外部排序. 在查询优化器的决策思路中, 也会尽量选择排序使用的索引而非前面筛选使用的索引.

假设我们的表 t 中有 a, b 两个字段, a 是主键, b 使用辅助索引, 我们向该表插入 100000 条记录, a, b 两列均从 1 开始递增, 现在执行查询语句:

1	SELECT * FROM t WHERE (a BETWEEN 1 AND 1000) AND (b BETWEEN 50000 AND 100000) ORDER BY b limit 1;

正常情况下, 查询优化器会得出索引 a 的扫描行数 ≈ 1000, 索引 b 的扫描行数 ≈ 50000 的结论, 但最终会选择 b 索引, 原因就在于 ORDER BY 语句, 会使得查询优化器更倾向于选择能够直接排序的索引, 选择 b 的好处是从该索引上获取的数据天然有序, 不必再去进行额外的排序操作.

我们来简单解释一下 MySQL 是如何执行 ORDER BY 语句的

当我们对一条 SQL 语句执行 Explain 时, 如果 Extra 字段值为 Using filesort 就表示需要排序, MySQL 会给每个线程分配一块内存(sort_buffer)用于排序.
正常的排序语句执行过程:

初始化 sort_buffer, 并确定需要参与的字段(MySQL 的原则是内存足够的情况下尽量将 select 的全部字段放入, 否则排序完还需要回表)

从主索引(可能需要回表)取出整行, 再去 select 的字段存入 sort_buffer 中

从主索引再取一行记录进行相同操作, 直到不满足查询条件为止.

在 sort_buffer 中对 ORDER BY 字段进行快速排序

按照结果返回给客户端

这是一条 ORDER BY 语句最理想的执行情况, sort_buffer 大小大于需要排序的总数据量, 一旦 MySQL 发现内存放不下, 就需要借助磁盘临时文件辅助排序, MySQL 需要将总数据量分为 N 份, 每一份单独排序后存在这些临时文件中, 然后把这 N 有序文件再合并成一个有序的大文件. 这个 N 与排序的总数据量和 sort_buffer 大小有关.

此外, 如果 MySQL 认为单行数据量太大, 超过 max_length_for_sort_data 的值, 就会换成另外一种算法, 只在 sort_buffer 中对 ORDER BY 字段 + id 进行排序, 得到结果后再进行回表.

因此在出现可能的排序场景时, 有如下优化措施:

我们大部分情况下尽量让走排序字段的索引, 这样数据就天然有序, 不需要再额外进行排序操作.
也可以利用覆盖索引的特性, 尽可能不进行额外的回表操作
只 SELECT 必要的字段, 过多的字段可能会触发 MySQL 只对 ORDER BY 字段排序, 再利用 ID 回表.

3.6.4 如何选择正确的索引

在前面我们分析了查询优化器选择索引的原理, 也分析了几个查询优化器误选索引的场景, 现在来解决不同情况下误选索引的问题

由于 MySQL 索引基数采样不准确引起的, 这类问题可以通过 SHOW INDEX FROM t 语句确定, 再通过 ANALYZE TABLE t 重新触发采样解决
由于回表/排序问题导致的误选索引, 在确定该语句绝大部分情况下都会误选的前提下(因为范围查询未必总会出现上述情况), 可以通过 FORCE INDEX(idx_name) 来强制使用某条索引.
此外对于排序问题, 还可以将其他的索引字段也加入 ORDER BY 子句中, 通过这样的方式让查询优化器明白, 无论选择哪条索引都无法避免排序, 从而强迫它放弃这一筛选条件, 如上述的 SQL 语句改成 SELECT * FROM t WHERE (a BETWEEN 1 AND 1000) AND (b BETWEEN 50000 AND 100000) ORDER BY b, a limit 1; 后, 查询优化器就会根据索引的扫描行数去决定.

3.7 索引没有生效的场景

上一节我们讨论了 MySQL 查询优化器选错索引的原因, 这节继续讨论设置了索引但是却意外的没有生效的场景

3.7.1 条件字段做函数计算

假设表 t 中包含一个类型为 datetime 类型的字段 create_time, 并为该字段建立索引 idx_create_time, 如果查询语句为:

1	select * from t where create_time = '2019-8-24'

此时可以正常通过 idx_create_time 查询, 但是如果使用如下语句查询 create_time 字段月数为 8 的全部记录:

1	select * from t where MONTH(create_time) = 8

此时 MySQL 会直接执行全表扫描.

这样的执行方式并不符合我们的预期结果, 因为月份和日期一样, 都是有序的, 但此时为什么不能通过 idx_create_time 进行快速查找呢, 这需要从 InnoDB 索引查询记录的方式说起:

之前我们提到过, B+ 树的本质是一棵多叉树, 通过在一个节点上尽可能多放节点来降低树的深度, 且 B+树一个节点内的数据是有序的, 查找的方式是从根节点开始, 找到目标记录出现的下层指针, 直到查询到叶子节点, 这样的查询必须依赖与每层跨界点有序, 不然在遍历当前层级的时候, 记录旧可能出现在多个叶子节点, 这样 B+树的查询就会失去意义. 在上图叶子节点的绿色数据中, 我列出了每个 k 的 MONTH() 函数值, 显然并不满足同一层级跨节点有序. 因此 InnoDB 无法通过这样的一条索引去完成 MONTH() 查询.

但是优化器并不是完全放弃使用这个索引, 优化器可以选择遍历主键索引, 也可以选择遍历 idx_create_time, 这需要优化器按照查询计划分别计算出两种方式预计的耗时.

对于会改变有序性的函数, 优化器的决定毋庸置疑, 但对于本身就不会改变有序性的函数来说, 优化器由于场景比较复杂, 依然直接采用放弃索引的方式规避麻烦.

3.7.2 隐式类型转换

假设表 t 有字段 uid, 类型为 varchar(64), 当执行如下 SQL 语句时, 会直接走全表扫描:

1	select * from t where uid = 1234;

原因是查询语句发生了隐式的类型转换, MySQL 的类型转换规则是如果字符型和数字做比较的话, 会将字符型转换成数字.

因此上面的 SQL 语句实际被优化器转换为:

1	select * from t where CAST(uid AS signed int) = 1234;

隐式类型转换的问题本质上还是由于 对索引字段做函数操作, 优化器会放弃走索引树的搜索功能, 触发主索引或辅助索引的全表扫描

4. MySQL 锁

锁是一个用于管理对共享资源并发访问的数据结构.

对于写操作, InnoDB 会在行记录上加锁, 使用 lock 功能的对象是事务, 锁定的对象是数据库存储中的对象, 包括表, 页, 行. 并且一般锁会在事务 commit 或 rollback 后释放.

InnoDB 实现了两种标准的锁:

共享锁(S 锁), 允许事务读一行数据, 与其他 S 锁兼容, 与 X 锁不兼容
排他锁(X 锁), 允许事务删除或更新一行数据, 与其他 S 锁或 X 锁均不兼容

4.1 锁的分类

根据加锁的范围, MySQL 中的锁大致可以分为全局锁, 表级锁和行级锁.

4.1.1 全局锁

全局锁会对整个数据库实例加锁.

Flush Tables With Read Lock (FTWRL), 可以让整个数据库全局加读锁, 处于只读状态, 所有更新 DDL, DML 和写事务的提交均会被阻塞.

一般用来做全库逻辑备份, 备份过程中整个库处于只读状态. 如果不加锁备份得到的库不是同一个逻辑时间点.

MySQL 自带的备份工具 mysqldump, 当使用 -single-transaction 时, 执行 dump 前会启动一个事务来获取一致性视图, MVCC 可以保证其他写操作正常.

4.1.2 表级锁

表锁 lock tables t read/write, 限制接下来所有线程的读/写
元数据锁(metadata lock), 访问时会被自动加上, 保证读写操作的正确性. 防止事务 A 读期间事务 B 对表结构做修改. 普通的增删改查 DML 会对元数据加 S 锁, DDL 操作会对元数据加 X 锁.

如果安全的执行 ALTER TABLE:

解决长事务, 可以通过 information_schema 库的 innodb_trx 表查看执行中的事务.
读写频繁, 由于对元数据的修改会阻塞其后的所有事务, 可以给 ALTER TABLE 设定超时时间, 超时后扔拿不到锁就会直接放弃.

4.1.3 InnoDB 行锁的实现

InnoDB 实现了 3 种行锁的算法, 分别是:

记录锁: 单个记录上的锁, 总是会去锁住索引记录.
间隙锁: 锁定一个范围, 但不包含记录本身
临键锁: 实现的方式是记录锁+间隙锁, 锁定一个范围, 并且锁定记录本身.

在 InnoDB 事务中, 行锁是在需要的时候才加上的, 但并不是不需要就立即释放, 需要等事务结束后再统一释放.

行锁在 InnoDB 中是基于索引实现的, 因此一旦某个加锁操作没有使用索引, 那么该锁就会退化为表锁.

4.1.3.1 记录锁(Record Locks)

为某行记录加锁, 会封锁该行的索引记录:

1
2
3

-- id 列必须为主键或唯一索引
SELECT * FROM t WHERE id = 1 FOR UPDATE;
UPDATE t SET grade = 100 WHERE id = 1;

在使用 SELECT FOR UPDATE 和 UPDATE 时, id 为 1 的记录行会被锁住, 但锁住的索引必须是主索引或者唯一索引, 否则加的锁就是临键锁, 同时, 查询语句必须为精确匹配, 不能为 <, > 或 LIKE, BETWEEN 等, 否则也只会加临键锁

4.1.3.2 间隙锁(Gap Locks)

间隙锁作用域普通索引(非主索引或唯一索引), 间隙锁锁住的是一个区间, 而不仅仅是这个区间中的每一条记录.

1	UPDATE t SET grade = grade + 10 WHERE id BETWEEN 10 AND 15;

即所有在(10, 20)区间范围内的行记录都会被锁住, 即 id 为 11, 12, 13, 14 的记录. 但 10 和 15 两条记录不会被锁住.

4.1.3.3 临键锁

可以理解为特殊的间隙锁. 每个数据行上的普通索引列都会存在一把临键锁, 当某个事务持有该行的临键锁时, 会锁住一段 左开右闭区间 的数据, InnoDB 中的行锁是基于索引实现, 临键锁只与普通索引有关, 在主索引和唯一键索引上不存在临键锁.

假设有如下数据表:

create table t (
    id bigint primary key,
    age int,
    name varchar(64),
    index(age),
)

内容如下:

id	age	name
1	10	Lee
3	24	soraka
5	32	Zed
7	45	Talon

此时, age 索引上潜在的临键锁有:

(-∞, 10]
(10, 24]
(24, 32]
(32, 45]
(45, +∞)

此时进行如下操作:

时刻	事务 A	事务 B	情况
t1	`SELECT * FROM t WHERE id BETWEEN 10 AND 24 FOR UPDATE`	无	获得间隙锁
t2	无	`INSERT INTO t(age, name) VALUES(15, 'Tom')`	插入操作被阻塞

使用这样的方式保证事务执行期间不会出现幻读.

4.1.4 解决幻读

大部分数据库都是通过最高事务隔离级别 SERIALIZABLE 去解决幻读问题, 但 InnoDB 不同, 它是通过 MVCC 和临键锁, 在 REPEATABLE READ 隔离级别下避免幻读. 我们下面就来解释一下原因

幻读: 在同一事务下, 连续执行两次 SQL 语句可能导致不同的结果, 第二次 SQL 语句可能会返回之前不存在的行或者没有返回之前存在的行, 即无法感知当前事务执行期间其他事务的 INSERT 或 DELETE 操作.

假设有如下表记录:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);

当我们执行如下查询语句时:

1
2
3

begin;
select * from t where d=5 for update;
commit;

InnoDB 的加锁顺序是这样的:

字段 d 没有索引, 只能通过主索引全表扫描, 将主索引上全部记录加行锁
将主索引上全部 间隙 加锁

时刻	tx-a	tx-b	tx-c
t1	`select * from t where d=5 for update;`
t2		`update t set d=5 where id=0;`
t3	`select * from t where d=5 for update`
t4			`update t set d=5 where id=0;`
t5	`select * from t where d=5 for update`

如果只对 id=5 这一行加锁, 而其他行不加锁的话, 那么在事务 a 中, 三次 select 语句执行的结果均不相同. 这种情况就被成为幻读, 一个事务在前后两次查询同一个范围时, 第二次查询到了前一次没有看到的行.

幻读带来的问题:

破坏语义: T1 时刻事务 a 想做的事情是 把所有 d=5 的行锁住, 不准别的事务进行读写操作, 但幻读显然破坏了这样的语义
数据一致: 假设在事务 a 执行范围修改后, 提交前其他事务插入了符合 a 修改条件的记录, 并直接提交, 那么事务 a 提交后的 BinLog(STATEMENT 模式) 中涉及的修改就会包含其他事务添加的行.

幻读问题的根源在于即使把所有记录都加锁, 依然无法阻止新纪录的插入, 因此为了解决幻读, InnoDB 引入了间隙锁, 用于锁住两个值之前的空隙. 例如初始化后的表 t 有 6 条记录, 就会产生 7 个间隙:

(-∞, 0) (0, 5) (5, 10) (10, 15) (15, 20) (25, +∞)

当执行 SELECT * FROM t WHERE d=5 FOR UPDATE; 时, 由于 d 字段没有索引, 不止会给已有的 6 条记录加上行锁, 还同时给 7 个间隙全部加上间隙锁, 这样就保证无法再插入新的数据.

在 InnoDB 中, 数据行是可以加锁的实体, 数据行之间的间隙也是. 但间隙锁不像行锁会分为读锁和写锁, 与间隙锁存在冲突的是 向这个间隙中插入记录 这个操作, 不同间隙锁之间不存在冲突关系.

5. 事务

事务是数据库系统区别于文件系统的重要特性之一, 在文件系统中, 如果在写文件的时候进程退出, 这个文件就很有可能被损坏. 还有在顺序写入多个文件的场景, 如果执行到中间某个状态时进程退出, 就会产生复杂的中间状态.

数据库引入了事务, 就是希望能够安全的将数据库从一种一致状态转换到另一种一致的状态上来, 当数据库提交工作时, 可以确保要么所有的修改都已经成功保存, 要么所有的修改都被废弃. 而保证这些功能的关键就在于满足 ACID 特性.

概念	描述
A(Atomicity) 原子性	原子性需要保证一系列的更新操作要么全部执行成功, 要么全部被废弃
C(Consisteny) 一致性	事务将数据库从一种抑制状态转变为下一种一致的状态, 在事务的开始和结束前后, 数据库的完整性约束没有被破坏
I(Isolation) 隔离性	隔离性保证每个读写事务的对象对其他事务的操作独享能相互分离, 即该事务提交前对其他事务都不可见
D(Durability) 持久性	事务一旦提交, 其结果就是持久性的, 即使发生宕机等事故, 数据库也能将数据恢复.

5.1 隔离级别

当数据库上有多个事务同时执行的时候, 可能出现脏读, 不可重复读, 幻读等问题, 为了解决这些问题, 就有了隔离级别的概念.

在理解隔离级别的时候, 我们可以先想象机场的安检级别, 在机场中, 由于客流量较大, 并且安全问题非常重要, 因此通常会使用不同安检预案来应对不同的情况. 在常规情况下, 安检级别可能不高, 此时安检项目不多, 吞吐量较高; 但如果有国家政要等情况, 安检级别就会相对升高, 甚至当机场受到了恐怖威胁可能安检级别会更高.
总之安检级别越高, 相对吞吐量就会降低, 但可以保证更高的安全性.

隔离级别也是同理, 是由 SQL92 标准定义的一套预案, 各个数据库自己来实现, 实际使用场景中, 需要由开发人员根据实际业务特点来灵活选择. 目前提供的标准事务隔离级别主要包括:

隔离级别	描述
读未提交	一个事务还没有提交时, 它做的变更就能被别的事务看到
读已提交	一个事务提交后, 它的变更才能被其他事物看到
可重复读	一个事务执行过程中看到的数据, 总是跟这个事务在启动过程中看到的数据是一致的.
可串行化	对于同一行记录, 写加 X 锁, 读加 S 锁, 当读写冲突的时候, 后访问的事务必须等前一个事务执行完成才能继续执行.

读未提交的实现方式比较简单, 写操作在完成之前就能被看到说明读写可以同时对一个事务加锁, 目前绝大部分数据库的默认隔离级别都不会是读未提交, 并且在绝大多数场景中都不能使用读未提交.

目前 InnoDB 的默认隔离级别是可重复读, Oracle 的默认隔离级别是读已提交.

5.1.2 事务隔离级别的实现

在 InnoDB 中, 每条记录在更新的时候都会同时记录一条回滚操作. 记录上的最新值通过回滚操作介意得到前一个状态的值. 假设一个值从 1 被依次改为 2, 3, 4, 在回滚段中会有类似记录:

当前的值为 4, 但是在查询这条记录时, 不同时刻启动的事务会有不同的 ReadView(视图), 同一条记录可以存在多个版本, 这就是数据库的多版本并发控制(Multi-Version-Concurrency-Control, MVCC), 对于在该条记录为值为 1 时启动的事务, 会使用 ReadView-A 去查询该记录, 查询的原理是通过从最新值开始, 依次向前比较直到找到提交时间早于该事物启动时间的第一条记录, 然后返回.

回滚段的删除比较特殊, 需要等到整个系统中没有比这个回滚段更早的 ReadView 时, 才可以删除, 因为 InnoDB 不能确定哪些 ReadView 会访问这条数据, 只有等真正执行的时候才知道.

在可重复读隔离级别下, 事务启动时会同时启动一份快照, 这个快照是基于整个数据库的. 但它不是真的对整个数据库做一次备份.

InnoDB 中每个事务都有一个唯一的事务 ID, 叫做 txId, 当事务启动时统一分配并且严格递增. 每条记录也有多个版本, 每次更新都会创建一个新的版本, 并且记录修改的 txId 作为 row rx-id. 同时旧的数据版本就放在回滚段中.
但是记录的多个版本只是逻辑上的概念, InnoDB 并不是真的存储数据, 存储的是能够将数据恢复到上一个版本的 undo log.
对于可重复读, 一个事务启动的时候, 能够看到所以已经提交的事务结果, 也就是该事物只能看到每条记录所有已提交的 row tx_id 小于自己 tx_id 的版本. InnoDB 为每个事务构造了一个数组, 用来保存这个事务启动时未提交的事务 id. 对于该事物, 通过 未提交事务列表中最小值 和 当前数据库最大事务 id + 1 两个值将当前时刻的全部事务分为三部分:

已创建, 并且确定提交的事务
已创建, 但需要进一步确认是否提交的事务
还未创建的事务

此时, 对数据库中全部记录的 row tx_id 来说, 分为四种类型:

蓝色部分: 由已创建且已提交的事务生成, 可见
绿色部分:
1. 在当前事务 未提交事务列表 中, 代表由已创建但未提交的事务生成, 不可见
2. 不在当前事务 未提交事务列表 中, 代表由已创建且已提交的事务生成, 可见
黄色部分: 由未来启动的事务生成的, 不可见

InnoDB 利用 redo log 实现了 MVCC, 再利用 MVCC 实现秒级创建快照的能力.

而读已提交和可重复读的实现都利用了快照, 不同之处在于:

读已提交级别下, 每一条语句执行前都会重新计算出一个快照
可重复读级别下, 只在事务创建时计算一次快照, 之后事务里的其他查询都共用这一个视图.

可序列化隔离级别, 不需要视图以及其他额外的特性, 每条记录都按照 S 锁和 X 锁的定义依次执行即可.

5.2 事务的实现

事务的隔离性由锁来实现, 原子性和持久性由 redo log 实现, 一致性由 undo log 实现. redo log 用来恢复提交事务修改的页操作, undo log 用来将行记录回滚到某个特定版本.

5.2.1 Redo log

我们先想象一个最直接的 UPDATE 语句执行方式:

根据索引从磁盘中读出记录所在的数据页
在内存中修改数据页对应的值
将数据页刷新回磁盘

在不考虑性能的前提下, 这是完成一条更新操作最直观的方式.

但往往越直观的方式, 性能越差. UPDATE 操作是一个典型的随机写, 对于机械硬盘来说, 一次随机写平均花费 10ms, 并且一个事务中可能存在多条写操作, 在保证其能执行成功的同时还要保证原子性, 由此可见这并不是一个理想的方案.

InnoDB 引入了 WAL 思想, 其关键在于先写日志, 再写磁盘, 当有一条记录需要更新的时候, InnoDB 就会先把记录写到 RedoLog 中, 并更新内存, 此时更新操作就完成了. InnoDB 会在 适当 的时候, 将这个操作记录更新到磁盘中, 这样的更新都是在系统相对比较空闲的时候.

这里借用极客时间的图来说明 RedoLog 的实现方式, 磁盘中的 RedoLog 是固定大小的(并不像 BinLog 可以在磁盘空间未满的情况下无限追加), 写入的方式类似环形队列, write pos 是当前记录的位置, 一边写一边后移, checkpoint 是当前需要擦除的位置, 也是往后推移并且循环, 擦除记录前要把记录更新到数据文件. 假设我们为 RedoLog 文件定义两个操作:

push: 向 RedoLog 文件写入数据, 并增加 write pos, 当事务执行写操作触发
pop: 从 RedoLog 中删除数据, 并增加 check point, 当该事务的更新操作落盘时触发, 代表该条 RedoLog 不再需要.

RedoLog 的写入机制:

事务在执行的时候, 生成的 RedoLog 会先写入 RedoLog Buffer, 当事务提交时再统一持久化到磁盘.

我们先从一条更新 SQL 语句的执行过程来体会 RedoLog 的功能.

1	UPDATE t SET grade = grade + 1 WHERE id = 2;

执行过程如下:

需要着重解释的几个点:

5.2.1.1 BinLog 和 RedoLog 能否相互替代

答案是不能, 首先 BinLog 是 MySQL Server 层提供的功能, 旨在提供数据恢复, 集群同步等功能; RedoLog 是 InnoDB 独有的概念, 用来实现事务的原子性和持久性. 简单来说二者的设计方向不同, BinLog 在磁盘空间足够的前提下可以无限增加, 用来复现某个时间点之后的全部写操作. RedoLog 文件在磁盘中大小固定, 循环队列的结构会使得较早的日志被清理掉.

BinLog 功能: 保证数据库能够从某个时间点正确恢复以及主从一直.
RedoLog 功能: 保证事务原子性和持久性, RedoLog 落盘后数据库即使宕机重启更新依然不丢.

5.2.1.2 为什么 RedoLog 需要先 Prepare

答案是为了保证 RedoLog 和 BinLog 的一致性.

我们可以做一个假设, 如果不使用两阶段提交, 分别提交 BinLog 和 RedoLog 看看会出现什么情况.

场景	问题
先提交 BinLog, 提交 RedoLog 前数据库宕机	此时 BinLog 落盘成功, 从库可以拉取到该 BinLog, 会将该更新在自己身上提交, 主库恢复后无法复现该事务, 此时主从不一致. 此外, 主库如果从某个时刻想通过 BinLog 恢复到当前状态, 恢复出来的时候就会多出一个事务, 该记录的值与原库值不同.
先提交 RedoLog, 提交 BinLog 时数据库宕机	此时 RedoLog 落盘成功, 即使数据库宕机, 主库恢复后依然可以复现. 但由于 BinLog 没有写入成功, 此时如果用这个 BinLog 来恢复临时库或者主从同步, 恢复出来的行记录就会少一条事务, 依然与原库值不同

因此我们可以看到, RedoLog 影响宕机重启后的事务重新执行, BinLog 影响可能需要的恢复和主从同步, 要想一致就必须使用两阶段提交.

5.2.1.3 两阶段提交如何保证 RedoLog 与 BinLog 一致

RedoLog 的两阶段提交一共分为三步:

写入 RedoLog, 处于 Prepare 状态
写入 BinLog
提交事务, 处于 commit 状态

问题可能出现在步骤 1 后和步骤 2 后, 我们分情况来讨论下:

写入 Prepare 状态的 RedoLog 后 MySQL 宕机: 此时 BinLog 没有写入, RedoLog 也没有提交, 此时可以当做事务提交失败.
写入 BinLog 后 MySQL 宕机: 崩溃恢复的规则如下:
1. 如果 RedoLog 中事务是完整的, 也就是有了 commit 标识, 则可以直接提交;
2. 如果 RedoLog 中事务只有完整的 Prepare, 则判断对应事务的 BinLog 是否完整, BinLog 如果完整就可以提交事务, 否则回滚.

对于 MySQL 来说, 每个事务的 BinLog 都有完整的格式, 通过识别该格式就可以判断事务额 BinLog 是否完整.

此外, BinLog 和 RedoLog 都有一个共同的字段 XID, 在崩溃恢复的时候会按顺序扫描 RedoLog:

如果碰到既有 Prepare 又有 commit 的 RedoLog, 就直接提交;
如果碰到只有 Prepare 但没有 commit 的 RedoLog, 就需要通过 TXID 去 BinLog 中查询, 再通过 BinLog 是否完整决定提交或回滚.

5.2.2 回滚日志 UndoLog

RedoLog 记录了事务的行为, 可以通过其对数据页进行重做. 但事务如果需要进行回滚, 就需要 UndoLog. 当事务执行失败或者显式执行 ROLLBACK 的时候, 就可以利用 UndoLog 将数据回滚到某个特定的版本.

UndoLog 存放在数据库内部的回滚段中, UndoLog 本身不是快照, 只是逻辑地将数据库恢复到原来的样子, 比如某个字段自增, UndoLog 中就会记录将该字段 -1 可以得到上一个版本

5.2.3 组提交

我们通常给 sync_binlog 和 innodb_flush_log_at_trx_commit 都会设置为 1, 也就是说一个完整的事务提交前, 需要进行两次 fsync 操作, 依次是 RedoLog(prepare), 另一次是 BinLog. 然而磁盘的 fsync 性能是有限的, 甚至磁盘 fsync 的速度很大程度上限制了数据库的 TPS 上限, 为了提高磁盘 fsync 的效率, MySQL 提供了 group commit 的功能, 即一次 fsync 可以刷新确保多个事务日志被写入文件.

事务提交时, 会进行两个阶段的操作:

修改内存中事务对应的信息, 并且将日志写入 RedoLog Buffer
调用 fsync 将确保日志都从 RedoLog Buffer 写入磁盘

步骤 2 的耗时远大于步骤 1, 此时我们就可以当某个事务进行步骤 2 的时候, 让其他事务先执行步骤 1, 这样就可以将多个事务的重做日志通过一次 fsync 刷新到磁盘, 这样可以减轻磁盘的压力.

MySQL 甚至提供了把 RedoLog 做 fsync 时间拖到步骤 1 之后的功能:

binlog_group_commit_sync_delay 参数, 表示延迟多少微秒后才调用 fsync;
binlog_group_commit_sync_no_delay_count 参数, 表示累计多少次以后才调用 fsync

因此 WAL 机制主要能带来两方面提升:

RedoLog 和 BinLog 都是顺序写, 速率远大于随机写.
组提交机制, 大幅降低磁盘的 IOPS 消耗.

6. 集群与高可用

6.1 通过 BinLog 保证主备一致

在 MySQL 的高可用场景中, 最简单和常用的就是主备复制, 客户端的读写都直接访问主库, 而备库只负责将主库的更新同步到本地执行, 当主库出现问题的时候, 可以将主库下线, 并将备库立即提升为主库.

MySQL 是通过 BinLog 的同步完成主备的数据同步功能的.

在主备同步时, 备库与主库维持了一个长连接, 主库有一个单独的线程用于处理备库的长连接, 日志的同步过程如下:

备库通过 change master 命令指定主库的 ip, 端口, 用户名, 密码以及请求 BinLog 的文件名和日志偏移量;
备库通过 start slave 命令启动两个线程: 负责与主库建立连接的 io_thread 和负责复现数据的 sql_thread;
主库建立连接后, 会按照备库传来的位置从本地读取 BinLog 发给备库;
备库拿到 BinLog 后, 写入到本地文件, 成为中转日志(relay log);
sql_thread 读取中转日志, 解析出日志中的命令并执行.

6.2 主从延迟的来源

在主从复制中, 主要由三步构成:

主库提交事务, 写入 BinLog;
从库获得 BinLog, 放入 RelayLog;
备库执行完成.

在备库上可以执行 show salve status 命令, 返回结果中有 seconds_behind_master, 用于表示当前备库延迟多少秒, MySQL 会统计BinLog 中主库记录的时间域当前系统时间的差值.

在正常情况下, BinLog 传给备库的延迟很低, 主备延迟的主要来源是备库接收完 BinLog 和提交事务的时间. 本质上说, 是从库消费中转日志(RelayLog) 的速度比主库生产 BinLog 的速度要慢.

产生这种情况的原因:

很多情况下从库性能低于主库
备库除了同步数据, 还需要处理其他请求
主库频繁执行大事务
备库的并行复制能力.

主备切换的时候, 正常情况下应该采用可靠性优先策略:

判断从库当前的 second_behind_master, 如果大于某个值(如 5s), 则等待并重试, 这一步是为了尽可能在从库压力不大的时候进行.
如果小于某阈值, 把主库改成只读状态;
循环判断从库的 second_behind_master 直到为 0, 代表主库所有内容都已经同步到从库中;
把从库改为可写状态;
把业务请求转移到从库.

这种方案下, 在步骤 3~5 期间整个数据库系统对外不可写.

MySQL 的高可用是依赖于主从延迟的, 延迟时间越小在故障转移的时候服务恢复需要的时间就越短.

在某些短暂的大事务或者备份时, 对备库延迟的影响可能会到达分钟级, 但通常备库是可以跟上进度的, 但如果备库同步的是一个持续压力较高的主库, 延迟可能达到小时级甚至永远追不上来.

究其原因就是大部分情况下从库消费中转日志的速度都会低于主库处理写操作的能力.

在主库上, 影响并发度最主要的因素就是锁, InnoDB 支持行锁, 因此除了大量并发事务更新同一行的极端情况, 大部分情况下并发度都不低.
但是备库在执行的时候, 不论是单线程还是多线程, 都有一定的局限性, 导致理论上执行的速度会低于主库写操作.

在 MySQL 5.6 之前, 从库采用单线程复制, 因此速度低于主库比较好理解. 但后续版本 MySQL 开始采用多线程复制之后问题依然没有得到很好的解决:

中转日志中的全部记录, 首先由 Coordinator 读取, 然后分发给不同的 worker 负责执行.

主库的写操作是由客户端触发的, 在从库中, 想要把主库写操作完整的复现出来, 并不能简单的让 Coordinator 随机分给不同 Worker, 这中间存在一定的限制. 比如对同一条记录的两个写操作, 如果分给了两个 Worker, 由于 CPU 的调度策略不可控, 很可能会出现第二个写早于第一个完成这样会直接导致主备不一致.
此外, 同一个事务分别更新了两张表, 如果放在两个 worker 中, 在其中一个 worker 完成而另一个没有完成时, 主备会出现短暂的不一致.

因此, 多线程消费 RelayLog 的时候, 需要保证如下行为:

不能出现覆盖更新, 因此对同一个行的两个事务必须由同一个 worker 顺序执行
一个事务的所有操作都需要由同一个 worker 顺序执行

6.3 并行复制策略

MySQL 5.5

在 MySQL 5.5 的时候, 由于官方没有提供并行复制的策略, 因此 《MySQL 实战 45 讲》作者林晓斌 自己实现了并行复制策略:

按表分发

如果两个事务更新的是不同的表, 就可以交给不同的 worker 完成, 按表分发可以保证两个 worker 不会冲突. 但如果有跨表的事务, 还是需要放在一起

上述方案中, 在每个 worker 中维护了一个 HashTable, key 是 库名.表名, value 是该 worker 当前有多少个事务正在执行. 当有事务分配给该 worker 时, 涉及的表会加到 HashTable 中, 执行完成后再去掉.

每个事务在分发的时候, 跟所有的 worker 冲突关系包括三种情况:

和所有的 worker 都不冲突, coordinator 会分配给最空闲的 worker;
只和一个 worker 冲突, coordinator 必须分配给该 worker;
和多个 worker 冲突, coordinator 进入等待状态, 直到和这个事务存在冲突的 worker 只剩一个, 分配给该 worker

该方案在不同表之间负载均匀的场景中效果最好. 但如果出现热点表, 那么大量事务就会被集中分配给某个 worker, 退化为单线程复制.

按行分发

该方案用来解决热点表并行复制的问题: 如果两个事务没有更新相同的行, 那么在备库上可以并行执行, 按行复制的前提是 BinLog 格式为 row. (否则无法知道具体更新的行) 此时判断是否冲突的标准是是否修改了同一行记录.

此时 worker 上的 HashTable 的 key 就变成了 库名+表名+主键+所有唯一键 因为涉及唯一键的更新也可能存在冲突, 比如主库中 r1 让出了某个唯一键的值, r2 将唯一键更新为 r1 的值, 如果在备库中交给两个 worker 执行, 可能顺序会被打乱, 就存在问题.

按行分发的并发度更高, 但是也更消耗内存和 CPU 资源, 因为每一行记录都会作为 HashTable 的一个 key.

MySQL 5.6

MySQL 官方开始支持并行复制, 但粒度只有库, 从实现上说就是以库名作为 HashTable 的 key.

如果MySQL 实例上的各个库负载均匀就会起到一定作用.