一条DML语句从客户端发出后，服务器都做了哪些处理？

虽然SELECT语句的处理也很复杂，但SELECT语句并不会修改数据库中的数据，也就不会记录诸如redo、undo、binlog这些日志，本文主要是想讨论redo、undo、binlog这些日志是在什么时候生成的，啥时候写到磁盘的。

1.准备工作

为了故事的顺利发展，我们先建立一个表：

  1. CREATE TABLE hero (
  2.     number INT,
  3.     name VARCHAR(100),
  4.     country varchar(100),
  5.     PRIMARY KEY (number),
  6.     KEY idx_name (name)
  7. ) Engine=InnoDB CHARSET=utf8;

然后向这个表里插入几条记录：

  1. INSERT INTO hero VALUES
  2.     (1, 'l刘备', '蜀'),
  3.     (3, 'z诸葛亮', '蜀'),
  4.     (8, 'c曹操', '魏'),
  5.     (15, 'x荀彧', '魏'),
  6.     (20, 's孙权', '吴');

然后现在hero表就有了两个索引（一个二级索引，一个聚簇索引），示意图如下：
1.png

2.执行计划的生成

假设我们想执行下边这条UPDATE语句：

  1. UPDATE hero SET country = '汉' WHERE name >= 'x荀彧';

MySQL优化器首先会分析一下使用不同索引执行查询的成本，然后选取成本最低的那个索引去执行查询。

对于上述语句来说，可选的执行方案有2种：

  • 方案一：使用全表扫描执行查询，即扫描全部聚簇索引记录，我们可以认为此时的扫描区间就是(-∞, +∞)。
  • 方案二：使用二级索引idx_name执行查询，此时的扫描区间就是['x荀彧', +∞)。

优化器会计算上述两种方案的成本，选取成本最低的方案作为最终的执行计划。

我们可以通过EXPLAIN语句来看一下这个语句的执行计划（当然也可以通过MySQL提供的optimizer trace功能查看具体执行计划分析流程）：

2.png

可以看到，MySQL优化器决定采用方案二，即扫描二级索引idx_name在['x荀彧', +∞)这个扫描区间种的记录。

3.真正开始执行

优化器的执行计划中得到了若干个扫描区间（本例中只有1个扫描区间['x荀彧', +∞)），针对每个扫描区间，都会执行下边的步骤：

3.1处理扫描区间的第一条记录

  • 步骤1：首先server层根据执行计划，向InnoDB层索要二级索引idx_name的扫描区间['x荀彧', +∞)的第一条记录。
  • 步骤2：Innodb存储引擎便会通过二级索引idx_name对应的B+树，从B+树根页面一层一层向下查找（在页面中查找是通过页目录的槽进行二分查找的，这个过程很快），快速在叶子节点中定位到扫描区间['x荀彧', +∞)的第一条二级索引记录。接着根据这条二级索引记录中的主键值执行回表操作（即通过聚簇索引的B+树根节点一层一层向下找，直到在叶子节点中找到相应记录），将获取到的聚簇索引记录返回给server层。
  • 步骤3：server层得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样，如果一样的话就不更新了，如果不一样的话就把更新前的记录和更新后的记录都当作参数传给InnoDB层，让InnoDB真正的执行更新记录的操作。
  • 步骤4：InnoDB收到更新请求后，先更新记录的聚簇索引记录，再更新记录的二级索引记录。最后将更新结果返回给server层。

3.2处理扫描区间的第二条记录

  • 步骤1：server层继续向InnoDB索要下一条记录。
  • 步骤2：此时由于已经通过B+树定位到二级索引扫描区间['x荀彧', +∞)的第一条二级索引记录，而记录又是被串联成单向链表，所以InnoDB直接通过记录头信息的next_record的属性即可获取到下一条二级索引记录。然后通过该二级索引的主键值进行回表操作，获取到完整的聚簇索引记录再返回给server层。
  • 步骤3：server层得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样，如果一样的话就不更新了，如果不一样的话就把更新前的记录和更新后的记录都当作参数传给InnoDB层，让InnoDB真正的执行更新记录的操作。
  • 步骤4：InnoDB收到更新请求后，先更新记录的聚簇索引记录，再更新记录的二级索引记录。最后将更新结果返回给server层。

3.3处理扫描区间的剩余记录

该扫描区间中的其他记录的处理就和第2条记录的处理过程是一样。

4.详细的更新过程

MySQL使用mysql_update函数处理我们上述的更新语句：

3.png

最主要的处理流程写在了一个循环里：

4.png

上图所示的while循环就是依次处理各条记录的过程。

其中info.read_record是用于获取扫描区间的一条记录，读取到该记录后随后展开详细的更新操作。处理完了之后再回到这个while循环的起点，通过info.read_record获取下一条记录，然后进行详细的更新操作。

也就是说，其实处理每一条记录的过程都是类似的，只不过定位扫描区间的第一条记录会有点儿麻烦（需要从B+树根页面一层一层向下找）。

我们接下来聚焦于一条记录的更新过程，看看这个过程都发生了什么。

4.1将记录所在的页面加载到buffer pool

我们想更新一条记录，首先就得在B+树中定位这条记录——即进行一次加锁读（上图中的info.read_record函数用于调用Innodb读取记录的接口）。

如果该记录所在的页面已经在内存的buffer pool中，那就可以直接读取，否则还需要将该记录所在的页面读取到内存中的buffer pool中。

不论我们想读写任何页面，都需要先将该页面从硬盘加载到buffer pool中。在定位扫描区间的第一条记录时，我们首先要读取B+树根页面中的记录，所以首先需要先把B+树根页面加载到buffer pool中，然后再读取下一层的页面，然后再读取下下层的页面，直到叶子节点。每当要读取的页面不在buffer pool中，都得将其先加载到buffer pool后才能使用。

Innodb使用row_search_mvcc处理读取一条记录的过程（不论是加锁读还是一致性读都调用这个函数），在该函数内btr_pcur_open_with_no_init用于从B+树定位记录：

5.png

在定位记录时就需要将记录所在的页面加载到buffer pool，完成这个过程的函数是：

6.png

4.2检测更新前后记录是否一样

在mysql_update函数中，当通过info.read_record读取到一条记录之后，就要分析一下这条记录更新前后是否发生变化：

7.png

上图中的compare_records用于比较记录更新前后是否一样。

如果更新前和更新后的记录是一样的，那就直接跳过该记录，否则继续向下处理。

4.3调用存储引擎接口进行更新

8.png

上图中的ha_update_row就是要存储引擎去更新记录，其中的table->record[1]代表旧记录，table->record[0]代表新记录。

4.4更新聚簇索引记录

InnoDB会首先更新聚簇索引记录，然后再更新二级索引记录。

我们先看更新聚簇索引记录时都发生了什么。更新聚簇索引的函数如下所示：

9.png

下边首先会尝试在同一个页面中更新记录，这被称作乐观更新，调用btr_cur_optimistic_update函数：

10.png

如果不能在本页面中完成更新（比方说更新后的记录非常大，本页面容纳不下），就会尝试悲观更新：

11.png

4.5记录undo日志

更新记录前，首先要记录相应的undo日志，调用trx_undo_report_row_operation来记录undo日志：

12.png

首先我们要知道，MySQL的undo日志是要写到一种专门存储undo日志的页面中的。如果一个事务写入的undo日志非常多，需要占用多个Undo页面，那这些页面会被串联成一个链表，称作Undo页面链表。

13.png

trx_undo_page_report_modify函数用于真正的向Undo页面中写入undo日志。另外，由于我们这里是在修改页面，一个事务执行过程中凡是修改页面的地方，都需要记录相应的redo日志，所以在这个函数的末尾，有一个记录修改这个Undo页面的redo日志的函数trx_undof_page_add_undo_rec_log：

14.png

是先将undo日志写入Undo页面，然后再记录修改该页面对应的redo日志吗？是的。

不过这里修改后的页面并没有加入buffer pool的flush链表，记录的redo日志也没有加入到redo log buffer。当这个函数执行完后，才会：

  • 先将这个过程产生的redo日志写入到redo log buffer。•再将这个过程修改的页面加入到buffer pool的flush链表中。

上述过程是在mtr_commit中完成的：

15.png

MySQL把对底层页面的一次原子修改称作一个Mini Trasaction，即MTR。一个MTR中包含若干条redo日志，在崩溃恢复时，要么全部恢复该MTR对应的redo日志，要么全部不恢复。

也就是说实际上虽然先修改Undo页面，后写redo日志，但是此时InnoDB并不认为Undo页面是脏页，就不会将其刷新到硬盘，redo日志也没有写入到redo log buffer，这些redo日志也不会被刷新到redo日志文件。只有当MTR提交时，才先将redo日志复制到redo log buffer，再将修改的Undo页面加入到flush链表。

所以我们可以粗略的认为修改Undo页面的redo日志是先写的，而修改页面的过程是后发生的。

有后台线程不断的将redo log buffer中的redo日志刷新到硬盘的redo日志文件，也有后台线程不断的将buffer pool里的脏页（只有加入到flush链表后的页面才能算作是脏页）刷新到硬盘中的表空间中。InnoDB规定，在刷新一个脏页到硬盘时，该脏页对应的redo日志应该被先刷新到redo日志文件。而redo日志是顺序刷新的，也就是说，在刷新redo log buffer的某条redo日志时，在它之前的redo日志也都应该被刷新到redo日志文件。

4.6修改页面内容

上一步骤是先把undo日志写到Undo页面中以及记录相应的redo日志，接下来该真正修改聚簇索引记录了。

首先更新系统字段trx_id以及roll_pointer：

16.png

然后真正的修改记录内容：

17.png

由于本例中的更新语句更新前后的各个字段占用的存储空间大小是不变的，所以可以直接就地（in place）更新。

然后记录更新的redo日志：

18.png

像向Undo页面写入undo日志一样，InnoDB规定更新一个页面中的一条记录也属于一个MTR。在该MTR提交时，也是先将MTR中的redo日志复制到redo log buffer，然后再将修改的页面加入到flush链表。

所以我们也可以认为在这个过程中，先记录修改页面的redo日志，然后再真正的修改页面。

至此，一条聚簇索引记录就更新完毕了。

4.7更新二级索引记录

更新二级索引记录的函数如下所示：

19.png

更新二级索引记录时不会再记录undo日志，但由于是在修改页面内容，会先记录相应的redo日志。

由于本例子中并不会更新二级索引记录，所以就跳过本步骤了。

5.记录binlog

在一条更新语句执行完成后（也就是将所有待更新记录都更新完了），就需要该语句对应的binlog日志了（下图中的thd->binlog_query函数）：

20.png

不过值得注意的是，此时记录的binlog日志并不会被写到binlog日志文件中，而是被暂时保存到内存的某个地方，等之后事务提交的时候才会真正将该事物执行过程中产生的所有binlog统一写入binlog日志文件。

6.提交事务的时候

在事务提交时，binlog才会被真正刷新到binlog日志文件中，redo日志也会被刷新到redo日志文件中。

7.阶段梳理

具体更新一条记录的流程如下：

  1. 先在B+树中定位到该记录（这个过程也被称作加锁读），如果该记录所在的页面不在buffer pool里，先将其加载到buffer pool里再读取。
  2. 读取到记录后判断记录更新前后是否一样，一样的话就跳过该记录，否则进行后续步骤。
  3. 首先更新聚簇索引记录。更新聚簇索引记录时：
    1. 先向Undo页面写undo日志。不过由于这是在更改页面，所以修改Undo页面前需要先记录一下相应的redo日志。
    2. 真正的更新记录。不过在真正更新记录前也需要记录相应的redo日志。
  4. 更新其他的二级索引记录。

至此，一条记录就更新完了。

然后开始记录该语句对应的binlog日志，此时记录的binlog并没有刷新到硬盘上的binlog日志文件，在事务提交时才会统一将该事务运行过程中的所有binlog日志刷新到硬盘。

剩下的就是两阶段提交。

8.XA规范

有一个名叫X/Open的组织提出了一个名为XA的规范。

这个XA规范提出了2个角色：

  • 一个全局事务由多个小的事务组成，所以我们得在某个地方找一个总揽全局的家伙，这个家伙用于和各个小事务进行沟通，指导它们是提交还是回滚。这个家伙被称作事务协调器（Transaction Coordinator）或者资源管理器（Resource Manager）。
  • 管理一个小事务的家伙被称作事务管理器（Transaction Manager）。

要提交一个全局事务，那么属于该全局事务的若干个小事务就应该全部提交，只要有任何一个小事务无法提交，那么整个全局事务就应该全部回滚。所以此时事务协调器不能让各个事务管理器逐个提交，因为不能保证后面提交的事务管理器是否可能发生错误。此时XA规范中指出，要提交一个全局事务，必须分为2步：

  • Prepare阶段：当事务协调器准备提交一个全局事务时，会依次通知各个事务管理器说：“现在事务中的语句都已经执行完了，我们准备提交了，你这里有没有什么问题？”。如果事务管理器觉得自己没有问题，就把在事务执行过程中所产生的redo日志都刷新到硬盘，然后对事务协调器说：“没有问题”。如果事务管理器遇到了啥突发情况不能提交（比方说磁盘满了，不能写redo了），就对事务协调器说：“不行，提交不了了”。
  • Commit阶段：如果在Prepare阶段各个事务管理器给事务协调器的答复都是:“OK，木有问题”，那事务协调器就要真正通知各个事务管理器去提交事务了。如果在Prepare阶段某个事务管理器给事务协调器的回复是：“NO，做不了”，那事务协调器就得通知所有事务管理器：“遇到突发情况，所有人立即回滚”。事务管理器收到通知便都回滚了。不过在**事务协调器**通知各个**事务管理器**是要提交之前，都需在某个地方记录一下这个全局事务已经提交，以及各个事务管理器都是什么的信息。

XA规范把上述全局事务提交时所经历的两个阶段称作两阶段提交。

如果一个全局事务仅包含一个事务管理器的话，那两阶段提交可以退化成1阶段提交。

XA规范引入了一个在事务提交时的Prepare阶段，这个阶段就是让各个事务做好提交前的准备，具体就是把语句执行过程中产生的redo日志都刷盘。如果语句执行过程中的redo日志都刷盘了，那么即使之后系统崩溃，那么在重启的时候还是可以恢复到该事务各个语句都执行完的样子。

这样的话，在Prepare阶段结束后，即使某个事务管理器因为某些原因而崩溃，在之后重启恢复时，也可以把自己再次恢复成Prepare状态。在崩溃恢复结束后，事务协调器可以继续让事务管理器提交或者回滚。

以上就是XA规范的核心内容，下边可以来看一下MySQL对上述XA规范的实现。

9.MySQL中的XA事务

MySQL中的XA事务分为外部XA和内部XA。

9.1外部XA

在MySQL的外部XA实现中，MySQL服务器充当事务管理器，而连接服务器的客户端程序充当事务协调器。

与使用BEGIN语句开启，使用COMMIT提交的常规事务不同，如果我们想在MySQL中使用XA事务，需要一些特殊的语句：

  • XA {START|BEGIN} xid：该语句用于开启一个XA事务，此时该XA事务处于ACTIVE状态。

在一台MySQL服务器上，每个XA事务都必须有一个唯一的id，被称作xid。这个xid是由发起XA事务的应用程序（客户端）自己指定的，只要我们自己保证它唯一就好了。

这个xid其实是由gtrid、bqual、formatID三个部分组成的：

  1. xid: gtrid [, bqual [, formatID ]]

其中gtrid（global transaction id）是指全局事务id，是一个字符串，bqual是指分支限定符，formatID是指gtrid和bqual所使用的格式。

MySQL会使用默认的值（bqual默认是空字符串’’，formatID默认是1）。也就是说我们后续内容指定xid时仅指定gtrid就好了，也就是指定一个字符串即可。

  • XA END xid：在使用XA START xid开启了一个XA事务后，客户端就可以接着发送属于这个XA事务的各条语句，等所有语句都发送完毕后，就可以接着发送XA END xid来告知服务器由xid标识的XA事务的所有语句都输入完了。此时该XA事务处于IDLE状态。
  • XA PREPARE xid：对于处于IDLE状态的XA事务，应用程序就可以询问MySQL服务器是否准备好提交这个XA事务了，此时就可以给服务器发送XA PREPARE xid语句。当MySQL服务器收到此语句后，就需要做准备提交前的工作了，比如把该事务执行过程中所产生的redo日志刷新到磁盘等。此时XA事务处于PREPARE状态。
  • XA COMMIT xid [ONE PHASE]：对于处于PREPARE状态的XA事务，应用程序可以发送XA COMMIT xid来让MySQL服务器提交XA事务。如果此XA事务尚处于IDEL状态，那应用程序可以不发送XA PREPARE xid，而直接发送XA COMMIT xid ONE PHASE来让MySQL服务器直接提交事务即可。此XA事务处于COMMITE状态。
  • XA ROLLBACK xid：应用程序通过发送此语句来让MySQL服务器回滚xid所标识的事务。此时XA事务处于ABORT状态。
  • XA RECOVER：应用程序想看一下当前MySQL服务器上已经处于Prepare状态的XA事务有哪些，就可以发送该语句。

介绍了在MySQL中使用外部XA的一些语句，接下来可以画一个XA事务的状态转换图了：

21.png

  1. mysql> XA START 'a';    //XA事务进入ACTIVE状态
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> DELETE FROM x WHERE id = 1;  //XA事务中包含的语句
  5. Query OK, 1 row affected (0.00 sec)
  7. mysql> XA END 'a';      //XA事务进入IDEL状态
  8. Query OK, 0 rows affected (0.00 sec)
  10. mysql> XA PREPARE 'a';  //XA事务进入PREPARE状态
  11. Query OK, 0 rows affected (0.01 sec)
  13. mysql> XA COMMIT 'a';   //XA事务进入COMMIT状态
  14. Query OK, 0 rows affected (0.01 sec)

MySQL的外部XA除了被用于跨行转账这种经典的分布式事务应用场景，还被广泛应用于所谓的数据库中间件。

9.2内部XA

对于一台服务器来说，即使客户端使用BEGIN/START TRANSACTION语句开启的普通事务，该事务所包含的语句也有可能涉及多个存储引擎。此时MySQL内部采用XA规范来保证所有支持事务的存储引擎要么全部提交，要么全部回滚，这也被称作MySQL的内部XA。

内部XA除了解决这种设计多个存储引擎的事务之外，还解决保证binlog和存储引擎所做的修改是一致的问题。

在MySQL内部执行一个事务时，存储引擎会修改相应的数据，server层会记录语句对应的binlog。这是两个要么都完成，要么都不完成的事情。否则的话：

  • 如果存储引擎修改了相应数据并且提交了事务，而server层却未写入binlog。在有主从复制的场景中，意味着这个事务不会在从库中得以执行，从而造成主从之间的数据不一致。
  • 如果server层写入了binlog，但存储引擎却回滚了事务。在有主从复制的场景中，意味着这个事务会在从库中得以执行，从而造成主从之间的数据不一致。

那我们需要保证：如果存储引擎提交了事务，server层的binlog日志必须也被写入到硬盘上；如果存储引擎回滚了事务，server层的binlog日志必须不能被写入到硬盘上。

MySQL采用内部XA来实现上述内容，下边以Innodb存储引擎为例，具体讨论一下Innodb事务的提交和binlog日志写入的过程。

10.有binlog参与的内部XA事务

当客户端执行COMMIT语句或者在自动提交的情况下，MySQL内部开启一个XA事务，分两阶段来完成XA事务的提交：

  • Prepare阶段：存储引擎将该事务执行过程中产生的redo日志刷盘，并且将本事务的状态设置为PREPARE。binlog啥也不干。

binlog_prepare是在PREPARE阶段对binlog所做的一些操作：

22.png

binlog_prepare函数基本啥也没干。

innobase_xa_prepare是InnoDB存储引擎实现的XA规范的prepare接口：

23.png

这个函数做了很多事情。首先我们知道事务执行过程中需要写undo日志，这些undo日志被写到若干个页面中，这些页面也被称作Undo页面，这些页面会串成一个链表，称作Undo页面链表。在一个事务对应的Undo页面链表的首个页面中，记录了一些关于这个事务的一些属性，我们贴个图看一下：

24.png

我们先看一下其中的Undo Log Segment Header部分：

25.png

其中的TRX_UNDO_STATE字段就表明该事务目前处于什么状态。当处于Prepare阶段时，调用innobase_xa_prepare函数会将TRX_UNDO_STATE字段的值设置为TRX_UNDO_PREPARED（整数5），表明当前事务处在Prepare阶段。

我们再看一下Undo Log Header部分：

26.png

这个部分体现着这个Undo页面链表所属的事务的各种信息，包括事务id。其中两个属性：

  • TRX_UNDO_XID_EXISTS：表示有没有xid信息。
  • XID信息：表示具体的xid是什么。

当处于Prepare阶段时，调用innobase_xa_prepare函数会将TRX_UNDO_XID_EXISTS设置为TRUE，并将本次内部XA事务的xid（这个xid是MySQL自己生成的）写入XID信息处。

修改Undo页面也是在修改页面，事务凡是修改页面就需要先记录相应的redo日志。

记录了关于该事务的各种属性之后，接下来该将到现在为止所产生的所有redo日志进行刷盘，刷盘的函数如下所示：

27.png

在将redo日志刷盘之后，即使之后系统崩溃，在重启恢复的时候也可以将处于Prepare状态的事务完全恢复。

在MySQL 5.7中，有一个称之为组提交（group commit）的优化。InnoDB觉得各个事务分别刷自己的redo日志和binlog效率太低，他们把并发执行的多个事务所产生的redo日志和binlog在后续的Commit阶段集中起来统一刷新，这样可能提升效率，所以在MySQL 5.7以及之后的版本中，上述在Prepare阶段刷新redo日志的操作会被推迟到Commit阶段才真正执行。关于组提交的优化措施我们并不过多展开，这里就认为在Prepare阶段事务就已经将执行过程中产生的redo日志刷盘就OK。

  • Commit阶段：先将事务执行过程中产生的binlog刷新到硬盘，再执行存储引擎的提交工作。

将binlog刷盘的函数如下：

28.png

InnoDB存储引擎提交事务使用innobase_commit函数完成存储引擎层面的事务提交：

29.png

innobase_commit函数做了很多事情，我们挑一些重要的来说。

首先是更新Undo页面链表的状态，将我们上边说的Undo Log Segment Header部分的STATE字段更新一下。更新规则如下：

30.png

也就是说如果当前事务产生的undo日志比较少，那么就继续让别的事务复用该Undo页面链表，将STATE设置为TRX_UNDO_CACHED；如果Undo页面链表用于存储INSERT操作产生的undo日志，那么就将STATE设置为TRX_UNDO_TO_FREE，稍后会释放Undo页面链表占用的页面；如果Undo页面链表用于存储其他操作产生的undo日志，那么就将STATE设置为TRX_UNDO_TO_PURGE，等待purge线程后台回收该Undo页面链表。

UPDATE、DELETE操作产生的undo日志可能会用于其他事务的MVCC操作，所以不能立即删除。

对于存储UPDATE、DELETE操作产生的undo日志的Undo页面链表，还要将其加入所谓的History链表。

每个Undo页面链表的首个页面的页号会被存储在表空间的某个地方，以便崩溃恢复的时候可以根据该页来进行恢复。如果此时在事务提交时，Undo页面链表的状态被设置为TRX_UNDO_CACHED，那存储Undo页面链表的首个页面的页号的地方也就不需要做改动；如果此时在事务提交时，Undo页面链表的状态被设置为TRX_UNDO_CACHED，那存储Undo页面链表的首个页面的页号的地方就得被设置为空，这样这个地方就可以被其他事务使用了。

至此，这个事务就算是提交完了。

11.崩溃恢复

每当系统重启时，都会先进入恢复过程。

此时首先按照已经刷新到磁盘的redo日志修改页面，把系统恢复到崩溃前的状态。

然后在表空间中找一下各个Undo页面链表的首个页面的页号，然后就可以读取该页面的各种信息。

31.png

通过这个页面，我们可以知道该Undo页面链表对应的事务状态是什么：

  • 如果是TRX_UNDO_ACTIVE状态，也就是活跃状态，直接按照undo日志里记载的内容将其回滚就好了。
  • 如果是TRX_UNDO_PREPARE状态，那么是提交还是回滚就取决于binlog的状态了，我们稍后再说。
  • 如果是其他状态，就将该事务提交即可。

对于处于PREPARE状态的事务，存储引擎既可以提交，也可以回滚，这取决于目前该事务对应的binlog是否已经写入硬盘。这时就会读取最后一个binlog日志文件，从日志文件中找一下有没有该PREPARE事务对应的xid记录，如果有的话，就将该事务提交，否则就回滚好了。