MySQL innodb引擎的事务执行过程

共计 8768 个字符，预计需要花费 22 分钟才能阅读完成。

通过这篇文章可以了解到下面几个问题

• 问题 1：MySQL innodb 引擎的 update 的流程；
• 问题 2：以及写 redo,undo,binlog 的顺序，然后刷盘的顺序又是什么呢？
• 问题 3：以及刷新 redo 和脏数据的相关进程；

总结以上的三个问题，其实就是关于 MySQL innodb 事务的流程；那么接下来，我将详细总结下一一一：MySQL innodb 的事务流程：

1. 接下来我就以 update 为例，讲解下 MySQL5.6 的 innodb 的事务流程，总结起来就是：

镇对 update he set name=’liuwenhe’ where id=5;

1）事务开始

2）对 id= 5 这条数据上排他锁，并且给 5 两边的临近范围加 gap 锁，防止别的事务 insert 新数据；

3）将需要修改的数据页 PIN 到 innodb_buffer_cache 中；

4）记录 id= 5 的数据到 undo log. 5）记录修改 id= 5 的信息到 redo log.

6）修改 id= 5 的 name=’liuwenhe’.

7）刷新 innodb_buffer_cache 中脏数据到底层磁盘，这个过程和 commit 无关；

8）commit，触发 page cleaner 线程把 redo 从 redo buffer cache 中刷新到底层磁盘，并且刷新 innodb_buffer_cache 中脏数据到底层磁盘也会触发对 redo 的刷新；

9）记录 binlog（记录到 binlog_buffer_cache 中）

10）事务结束；

2. 关于事务的四大特性 ACID

事务的原子性 (Atomicity)：事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生了错误，要回滚(Rollback) 到事务开始前的状态，就像这个事务从来没有执行过。

事务的持久性(Durability)：事务一旦完成，该事务对数据库所做的所有修改都会持久的保存到数据库中。为了保证持久性，数据库系统会将修改后的数据完全的记录到持久的存储上。

事务的隔离性：多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果

事务的一致性：一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

二：redo 和 undo 保证 MySQL innodb 事务的原子性和持久性：

总起来概述可以认为：

undo 用来保存数据更改之前的数据；保证原子性

redo 用来保存数据更改之后的数据（注意是物理的修改信息），保证持久性

1）首先介绍 Undo Log

Undo Log 主要是为了实现事务的原子性，在 MySQL 数据库 InnoDB 存储引擎中，还用 Undo Log 来实现多版本并发控制(简称：MVCC)，之后的文章将会介绍 mvcc；

Undo Log 的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方

也就是 Undo Log，然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK 语句，系统可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

需要注意在 MySQL 5.6 之前，undo log 是放在了共享表空间 ibdata1 中的，MySQL5.6 中开始支持把 undo log 分离到独立的表空间，并放到单独的文件目录下；采用独立 undo 表空间，再也不用担心 undo 会把 ibdata1 文件搞大。

undo log 是为回滚而用，具体内容就是 copy 事务前的数据库内容（行）到 innodb_buffer_pool 中的 undo buffer（或者叫 undo page)，在适合的时间把 undo buffer 中的内容刷新到磁盘。undo buffer 与 redo buffer 一样，也是环形缓冲，但当缓冲满的时候，undo buffer 中的内容也会被刷新到磁盘；并且 innodb_purge_threads 后台线程会清空 undo 页、清理“deleted”page，InnoDB 将 Undo Log 看作数据，因此记录 Undo Log 的操作也会记录到 redo log 中。这样 undo log 就可以象数据一样缓存起来

2）接下来介绍 Redo Log, 注意是先写 redo, 然后才修改 buffer cache 中的页，因为修改是以页为单位的，所以先写 redo 才能保证一个大事务 commit 的时候，redo 已经刷新的差不多了。反过来说假如是先改 buffer cache 中的页，然后再写 redo，就可能会有很多的 redo 需要写，因为一个页可能有很多数据行；而很多数据行产生的 redo 也可能比较多，那么 commit 的时候，就可能会有很多 redo 需要写；

和 Undo Log 相反，Redo Log 记录的是新数据的备份。在事务提交前，只要将 Redo Log 持久化即可，

不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是 Redo Log 已经持久化。系统可以根据 Redo Log 的内容，将所有数据恢复到最新的状态。需要注意的是，事务过程中，先把 redo 写进 redo log buffer 中，然后 MySQL 后台进程 page cleaner thread 适当的去刷新 redo 到低层磁盘永久保存；

因为刷新 buffer pool 的脏数据之前，必须要先刷新 redo（从 redo log buffer 到磁盘），所以触发刷新脏数据 buffer pool 的脏数据的条件也同时会触发刷新 redo。还需要注意：MySQL 5.6 版本之前都是 master thread 来完成刷脏数据的任务（包括 buffer pool 中的脏数据以及 redo log buffer 中的 redo),MySQL 5.6 版本，刷新操作放入到了单独的 Page Cleaner Thread 中；

Checkpoint（检查点）技术目的是解决以下几个问题：1、缩短数据库的恢复时间；2、缓冲池不够用时，将脏页刷新到磁盘；3、重做日志不可用时，刷新脏页。

在 InnoDB 存储引擎内部，有两种 Checkpoint

分别为：Sharp Checkpoint、Fuzzy Checkpoint

Sharp Checkpoint 发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数 innodb_fast_shutdown=1。但是若数据库在运行时也使用 Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在 InnoDB 存储引擎内部使用 Fuzzy Checkpoint 进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。

Fuzzy Checkpoint：

1、Master Thread Checkpoint；

2、FLUSH_LRU_LIST Checkpoint；

3、Async/Sync Flush Checkpoint；

4、Dirty Page too much Checkpoint

1、Master Thread Checkpoint 

以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的，此时 InnoDB 存储引擎可以进行其他的操作，用户查询线程不会阻塞。

2、FLUSH_LRU_LIST Checkpoint

因为 InnoDB 存储引擎需要保证 LRU 列表中需要有差不多 100 个空闲页可供使用。在 InnoDB1.1.x 版本之前，需要检查 LRU 列表中是否有足够的可用空间操作发生在用户查询线程中，显然这会阻塞用户的查询操作。倘若没有 100 个可用空闲页，那么 InnoDB 存储引擎会将 LRU 列表尾端的页移除。如果这些页中有脏页，那么需要进行 Checkpoint，而这些页是来自 LRU 列表的，因此称为 FLUSH_LRU_LIST Checkpoint。

而从 MySQL 5.6 版本，也就是 InnoDB1.2.x 版本开始，这个检查被放在了一个单独的 Page Cleaner 线程中进行，并且用户可以通过参数 innodb_lru_scan_depth 控制 LRU 列表中可用页的数量，该值默认为 1024，如：

mysql> SHOW GLOBAL VARIABLES LIKE ‘innodb_lru_scan_depth’;

+———————–+——-+

| Variable_name | Value |

+———————–+——-+

| innodb_lru_scan_depth | 1024 |

+———————–+——-+

3、Async/Sync Flush Checkpoint

指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。若将已经写入到重做日志的 LSN 记为 redo_lsn，将已经刷新回磁盘最新页的 LSN 记为 checkpoint_lsn，则可定义：

checkpoint_age（可以理解脏页，或者待刷新的脏页）= redo_lsn – checkpoint_lsn

再定义以下的变量：

async_water_mark = 75% * total_redo_log_file_size

sync_water_mark = 90% * total_redo_log_file_size

若每个重做日志文件的大小为 1GB，并且定义了两个重做日志文件，则重做日志文件的总大小为 2GB。那么 async_water_mark=1.5GB，sync_water_mark=1.8GB。则：

当 checkpoint_age<async_water_mark 时，不需要刷新任何脏页到磁盘；</async_water_mark 时，不需要刷新任何脏页到磁盘；<>

当 async_water_mark<checkpoint_age<sync_water_mark 时触发 async flush，从 flush 列表中刷新足够的脏页回磁盘，使得刷新后满足 checkpoint_age<async_water_mark；</checkpoint_age

checkpoint_age>sync_water_mark 这种情况一般很少发生，除非设置的重做日志文件太小，并且在进行类似 LOAD DATA 的 BULK INSERT 操作。此时触发 Sync Flush 操作，从 Flush 列表中刷新足够的脏页回磁盘，使得刷新后满足 checkpoint_age<async_water_mark。</async_water_mark。<>

可见，Async/Sync Flush Checkpoint 是为了保证重做日志的循环使用的可用性。在 InnoDB 1.2.x 版本之前，Async Flush Checkpoint 会阻塞发现问题的用户查询线程，而 Sync Flush Checkpoint 会阻塞所有的用户查询线程，并且等待脏页刷新完成。从 InnoDB 1.2.x 版本开始——也就是 MySQL 5.6 版本，这部分的刷新操作同样放入到了单独的 Page Cleaner Thread 中，故不会阻塞用户查询线程。

解释下为什么重做日志文件不可用时，这时需要强制将一些脏页刷新回磁盘？

因为我们知道 redo 的作用是保证数据库的一致性，当数据库异常停机时，需要借助 redo+undo 进行实例恢复，redo 前滚 — 恢复出 buffer pool 中的脏数据（包括已经 commit 还没有刷新到磁盘的，也可能包括没有 commit，但是已经刷新到磁盘的，）然后借助 undo 完成回滚 — 将没有 commit，但是已经刷新到磁盘的数据，回滚到之前的状态。那么为啥重做日志文件不可用时，这时需要强制将一些脏页刷新回磁盘？原因就在于，redo 是循环覆写的，当 redo log 文件不可用，也就是说此时所有的 redo 文件里面的 redo 都是实例恢复需要的，也就是不能被覆盖的 redo, 那么什么是实例恢复需要的 redo 呢？就是 buffer pool 中的的脏数据，还没有刷新到磁盘，而这些脏数据相关的 redo 是不能被覆盖的，这些 redo 就是实例恢复需要的 redo，所以没有可用的重做日志文件，需要强制将一些脏页刷新回磁盘，这样就会有一些 redo 是实例恢复不需要的了，也就可以被覆盖了。

4、Dirty Page too much

即脏页的数量太多，导致 InnoDB 存储引擎强制进行 Checkpoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。其可由参数 innodb_max_dirty_pages_pct 控制：

mysql> SHOW GLOBAL VARIABLES LIKE ‘innodb_max_dirty_pages_pct’ ;

+—————————-+——-+

| Variable_name | Value |

+—————————-+——-+

| innodb_max_dirty_pages_pct | 75 |

+—————————-+——-+

innodb_max_dirty_pages_pct 值为 75 表示，当缓冲池中���页的数量占据 75% 时，强制进行 Checkpoint，刷新一部分的脏页到磁盘。在 InnoDB 1.0.x 版本之前，该参数默认值为 90，之后的版本都为 75，其可以通过参数 innodb_max_dirty_pages_pct 来设置；

总结下 redo 刷新的条件（因为刷新 innodb_buffer_pool 中的脏数据之前需要刷新 redo, 所以触发刷新 buffer_pool 会同时触发刷新 redo）：

1）当 redo log buffer 达到一定比值后，

2）刷新 innodb_buffer_pool 中的脏数据之前，

3）redo log buffer 满的时候，没有可用 buffer；

4）每秒刷新一次；

5）commit 的时候；

6）数据库关闭时发生 harp Checkpoint，触发将所有脏页刷回磁盘

7）手工 flush logs;

8）重做日志不可用时，触发刷新 innodb_buffer_pool 中的脏数据，进而触发 redo 刷新；

三：MySQL binlog：主从同步 主库 binlog 先写入到 binlog_buffer 中，然后刷新到磁层磁盘也就是 binlog 文件，主库 dump 进程读取的 binlog 文件，发送给从库；

binlog 日志是针对整个 MySQL server 而言的，前面介绍的 redo 和 undo 是针对 innodb 引擎而言的，binlog 的存在就是方便那些不支持事务的引擎表来同步数据到 slave；

那么到底是先刷新 redo 还是先写 binlog 呢？

伴随着这个问题，我重点说下，MySQL innodb 引擎事务 commit 的过程：

MySQL 为了保证 master 和 slave 的数据一致性，就必须保证 binlog 和 InnoDB redo 日志的一致性，为此 MySQL 引入二阶段提交（two phase commit or 2pc），MySQL 通过两阶段提交 (内部 XA 的两阶段提交) 很好地解决了这一问题，两阶段提交关键在于保证 redo 刷盘之后才能刷新 binlog 到底层文件，以 binlog 的写入与否作为事务提交成功与否的标志，最后判断 binlog 中是否有 redo 里的 xid,MySQL5.6 以前，为了保证数据库上层二进制日志的写入顺序和 InnoDB 层的事务提交顺序一致，MySQL 数据库内部使用了 prepare_commit_mutex 锁。但是持有这把锁之后，会导致组提交失败；直到 MySQL5.6 之后，才解决了这个问题，借助序列来保证 binlog 刷新也可以组提交；关于 redo 和 binlog 组提交，请看下一篇文章，

事务崩溃恢复过程如下：

1. 崩溃恢复时，扫描最后一个 Binlog 文件，提取其中的 xid；

2.InnoDB 维持了状态为 Prepare 的事务链表（commit 两阶段提交中的第一阶段，为 Prepare 阶段，会把事务设置为 Prepare 状态）将这些事务的 xid 和 Binlog 中记录的 xid 做比较，如果在 Binlog 中存在，则提交，否则回滚事务。

通过这种方式，可以让 InnoDB redo 和 Binlog 中的事务状态保持一致。

四：简单介绍下 MySQL 的后台进程：

InnoDB 存储引擎是多线程模型，因此其后台有多个不同的后台线程，负责处理不同的任务：

1）Master Thread

Master Thread 是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。

2）IO Thread

InnoDB 存储引擎中大量使用了 Async IO 来处理写 IO 请求，这样可以极大提高数据库的性能，而 IO Thread 的主要工作是负责这些 IO 请求的回调处理，可以使用 show engine innodb status 命令查看 InnoDB 存储引擎中的 IO 进程：

mysql> show engine innodb status\g;

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

I/O thread 1 state: waiting for completed aio requests (log thread)

I/O thread 2 state: waiting for completed aio requests (read thread)

I/O thread 3 state: waiting for completed aio requests (read thread)

I/O thread 4 state: waiting for completed aio requests (read thread)

I/O thread 5 state: waiting for completed aio requests (read thread)

I/O thread 6 state: waiting for completed aio requests (read thread)

I/O thread 7 state: waiting for completed aio requests (read thread)

I/O thread 8 state: waiting for completed aio requests (write thread)

I/O thread 9 state: waiting for completed aio requests (write thread)

I/O thread 10 state: waiting for completed aio requests (write thread)

I/O thread 11 state: waiting for completed aio requests (write thread)

Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,

ibuf aio reads:, log i/o’s:, sync i/o’s:

Pending flushes (fsync) log: 0; buffer pool: 0

451 OS file reads, 54 OS file writes, 7 OS fsyncs

3.77 reads/s, 16384 avg bytes/read, 1.05 writes/s, 0.13 fsyncs/s

如上显示的是 6 个 io read thread 和 4 个 io write thread，但是关于 log 的 io thread 和 insert buffer thread 的 io thread 只有一个；从 MySQL 5.6 开始默认是四个 io read thread 和 4 个 io write thread，并且可以通过 innodb_read_io_threads 和 innodb_write_io_threads 参数进行设置：

mysql> show variables like ‘%io_threads%’;

+————————-+——-+

| Variable_name | Value |

+————————-+——-+

| innodb_read_io_threads | 6 |

| innodb_write_io_threads | 4 |

+————————-+——-+

2 rows in set (0.00 sec)

3）Purge Thread

事务被提交后，其所使用的 undo log 可能不再需要，因此需要 PurgeThread 来回收已经使用并分配的 undo 页。从 InnoDB1.1 版本开始，purge 操作可以独立到单独的线程中进行，以此来减轻 Master Thread 的工作，从而提高 CPU 的使用率、提升存储引擎的性能。可以通过在 MySQL 数据库的配置文件中添加相关的命令来启用独立的 Purge Thread，如下参数：

mysql> show variables like ‘innodb_purge_threads’;

+———————-+——-+

| Variable_name | Value |

+———————-+——-+

| innodb_purge_threads | 1 |

+———————-+——-+

1 row in set (0.00 sec)

Page Cleaner Thread

4）Page Cleaner Thread
是在 InnoDB 1.2.x 版本中引入的，其作用是将之前版本中的脏页的刷新操作都放入到单独的进程中来完成，目的就是为了减轻原 Master Thread 的工作及对于用户查询线程的阻塞，进一步提高 InnoDB 存储引擎的性能。

然后回答最开始的问题:

在内存中先写 undo, 然后写 redo, 至于 redo 和 binlog 顺序不确定,  刷盘是先刷 undo, 然后刷 redo, 最后刷新 binlog;