MySQL 原理篇

MySQL 索引机制

MySQL 体系结构及存储引擎

MySQL 语句执行过程详解

MySQL 执行计划详解

MySQL InnoDB 缓冲池

MySQL InnoDB 事务

MySQL InnoDB 锁

MySQL InnoDB MVCC

MySQL InnoDB 实现高并发原理

MySQL InnoDB 快照读在RR和RC下有何差异

MVCC

MVCC 的定义

MVCC（Multiversion concurrency control）：多版本并发控制，并发访问（读或写）数据库时，对正在事务内处理的数据做多版本的管理。以达到用来避免写操作的堵塞，从而引发读操作的并发问题。

MVCC 逻辑流程

插入

MySQL 在每一行数据中都会默认添加一些隐藏列 DB_TRX_ID、DB_ROLL_PT。

上面图中的执行步骤如下：

1. 手动开启事务，从 InnoDB 引擎中获取一个全局事务ID（1）
2. 然后往 teacher 表中插入两条数据，同时设置数据行的版本号为当前事务ID，删除版本号为 NULL

思考：如果事务是自动提交的（SET AUTOCOMMIT = NO），且未手动开启事务，执行如下两条 SQL，插入的数据会是什么样子的？

INSERT INTO teacher (NAME, age) VALUE ('seven', 18) ;

INSERT INTO teacher (NAME, age) VALUE ('qingshan', 19) ;

因为事务是自动提交的，所以两条插入语句会分别获取事务ID，所以这里插入的数据行的版本号是1和2。

删除

上面图中的执行步骤如下：

1. 手动开启事务，从 InnoDB 引擎中获取一个全局事务ID（22）
2. 然后执行一条删除语句，InnoDB 会找到这条记录，把它的删除版本号设置为当前事务ID

修改

上面图中的执行步骤如下：

1. 手动开启事务，从 InnoDB 引擎中获取一个全局事务ID（33）
2. 然后执行一条修改语句，InnoDB 会找到这条记录，copy 一份原数据插入到表中，将新行数据的数据行的版本号的值设置为当前事务ID，将原行数据的删除版本号的值设置为当前事务ID

查询

上面图中的执行步骤如下：

1. 手动开启事务，从 InnoDB 引擎中获取一个全局事务ID（44）
2. 根据数据查询规则的描述

1. 查找数据行版本早于当前事务版本的数据行，发现表中三行数据都满足条件
2. 查找删除版本号要么为 NULL，要么大于当前事务版本号的记录，发现只有最后一条数据满足条件（1, seven, 19）

案例分析

数据准备：

CREATE TABLE `teacher` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(32) NOT NULL,

  `age` int(11) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT  INTO teacher(id,NAME,age) VALUES (1,'seven',18);

INSERT  INTO teacher(id,NAME,age) VALUES (2,'qingshan',20);

案例一

-- 事务A执行

BEGIN;                                     --

SELECT * FROM teacher;                       --

COMMIT;

--事务B执行

BEGIN;                                     --

UPDATE teacher SET age =28 WHERE id=1;     --

COMMIT;

案例一的执行步骤是：1,2,3,4,2，执行效果如下图所示：

虽然在执行 3,4 步骤的时候更新 id=1 的数据，但是根据 MVCC 的查询逻辑流程，再次执行2，获取到的数据依然和第一次一样。

案例二

-- 事务A执行

BEGIN;                                     --

SELECT * FROM teacher;                       --

COMMIT;

--事务B执行

BEGIN;                                     --

UPDATE teacher SET age =28 WHERE id=1;     --

COMMIT;

案例二的执行步骤是：3,4,1,2，执行效果如下图所示：

根据 MVCC 的查询逻辑流程，执行1,2，获取到的数据是事务B未提交的数据，这个是有问题的。

分析了案例一和案例二，发现 MVCC 不能解决案例二的问题，InnoDB 会使用 Undo log 解决案例二的问题。

Undo Log

Undo Log 的定义

Undo：意为取消，以撤销操作为目的，返回指定某个状态的操作。

Undo Log：数据库事务提交之前，会将事务修改数据的镜像（即修改前的旧版本）存放到 undo 日志里，当事务回滚时，或者数据库奔溃时，可以利用 undo 日志，即旧版本数据，撤销未提交事务对数据库产生的影响。。

• 对于 insert 操作，undo 日志记录新数据的 PK(ROW_ID)，回滚时直接删除；
• 对于 delete/update 操作，undo 日志记录旧数据 row，回滚时直接恢复；
• 他们分别存放在不同的buffer里。

InnoDB 发现可以基于 Undo Log 来实现多版本并发控制。

分析下图中 SQL 的执行过程。

• 事务A手动开启事务，执行更新操作，首先会把更新命中的数据拷贝到 Undo Buffer 中
• 事务B手动开启事务，执行查询操作，会读取 Undo Log 中数据返回，进行快照度

当前读和快照读

快照读

SQL 读取的数据是快照版本，也就是历史版本，普通的 SELECT 就是快照读。

InnoDB 快照读，数据的读取将由 cache（原本数据）+ Undo Log（事务修改过的数据）两部分组成。

当前读

SQL 读取的数据是最新版本，通过锁机制来保证读取的数据无法通过其他事务进行修改。

UPDATE 、DELETE 、INSERT 、SELECT … LOCK IN SHARE MODE 、SELECT … FOR UPDATE 都是当前读，这些操作在《MySQL InnoDB 锁》这篇文章中有过演示，事务A执行这些 SQL，会阻塞事务B的 SQL 执行。

在 InnoDB 引擎里面，快照读通过 MVCC 解决幻读的问题，当前读通过 Next-Key Locks 解决幻读的问题。

Redo Log

Redo Log 的定义

Redo：顾名思义就是重做。以恢复操作为目的，重现操作。

Redo Log：指事务中操作的任何数据，将最新的数据备份到一个地方（Redo Log）。

Redo Log 的持久化：不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 Redo Log 中，具体的落盘策略可以进行配置。

Redo Log 是为了实现事务的持久性而出现的产物。

Redo Log 实现事务持久性：防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

根据下图分析 Redo Log 的执行流程