MySQL事务执行过程
MySQL
MySQL分为 Server 层 与 存储引擎层。
-
Server 层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
-
存储引擎层：负责数据的存储和提取。存储引擎层被设计为支持可插拔式，支持 InnoDB、MyISAM、Memory 等多种存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
一条SQL语句的执行过程
一般来说，一条语句，不论是查询还更新，都会走以下的流程：
各个组件的作用如下：
| 组件 | 作用 |
|---|---|
| 连接器 | 用于和客户端建立连接，管理连接。检查连接中的用户名密码是否正确吗，以及是否对表有操作权限。 |
| 分析器 | 进行词法、语法分析，区分sql关键词与非关键词，生成一颗语法树。 |
| 优化器 | 生成sql的执行计划。 |
| 执行器 | 依据执行计划，调用存储引擎的接口，来实现对数据的读写操作。 |
开启事务
事务命令：
START TRANSACTION
[transaction_characteristic [, transaction_characteristic] ...]
transaction_characteristic: {
WITH CONSISTENT SNAPSHOT
| READ WRITE
| READ ONLY
}
BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}
注意，执行「开始事务」命令，并不意味着启动了事务。
在 MySQL 有两种开启事务的命令，分别是：
-
第一种：
BEGIN或者START TRANSACTION命令； -
第二种：
START TRANSACTION WITH CONSISTENT SNAPSHOT命令；
这两种开启事务的命令，事务的启动时机是不同的：
-
执行了
BEGIN或者START TRANSACTION命令后，并不代表事务启动了。只有在执行这个命令后，执行了第一条 select 语句，才是事务真正启动的时机； -
执行了
START TRANSACTION WITH CONSISTENT SNAPSHOT命令，就会马上启动事务。
两阶段提交
InnoDB在写redo log时，并不是一次性写完的，而有两个阶段，Prepare与Commit阶段，用于数据库崩溃恢复。
我们以下面的例子：
# 更新前age = 25
update boy set age = 24 where id = 1;
来介绍执行器与InnoDB引擎是如何更新一条指定的数据的：
在主从架构中，持久化 redo log 和 binlog 两个日志的过程可能出现半成功的状态，从而导致两份日志之间的逻辑不一致，此时，会有两种情况：
-
如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入
MySQL 重启后，通过 redo log 能将 Buffer Pool 中 id = 1 这行数据的 age 字段恢复到新值，但是 binlog 里面没有记录这条更新语句，在主从架构中，binlog 会被复制到从库，由于 binlog 丢失了这条更新语句，从库的这一行 age 字段是旧值，与主库的新值不一致性。
-
如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入
由于 redo log 还没写入，崩溃恢复以后这个事务无效，所以， id = 1 这行数据的 age 字段还是旧值，而 binlog 里面记录了这条更新语句，在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，那么这一行 age 字段是新值，与主库的值不一致性。
可以看到，在持久化 redo log 和 binlog 这两份日志时，如果出现半成功的状态，就会造成主从环境的数据不一致性。
因为 redo log 影响主库的数据，binlog 影响从库的数据，所以， redo log 和 binlog 必须保持一致才能保证主从数据一致。
MySQL的两阶段提交过程
2PC 是 Two-Phase Commit 的缩写，即二阶段提交，是计算机网络尤其是数据库领域内，为了保证分布式系统架构下所有节点在进行事务处理过程中能够保证原子性和一致性而设计的一种算法，同时，2PC 也被认为是一种一致性协议，用来保证分布式系统数据的一致性。
当客户端执行 commit 语句或者在自动提交的情况下，MySQL 内部会开启一个 XA 事务，分两阶段来完成 XA 事务的提交，如下图：
从图中可看出，事务的提交过程有两个阶段，就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入 binlog 的步骤，具体如下：
-
第一阶段：prepare 阶段
将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后，将 redo log 持久化到磁盘；
-
第二阶段：commit 阶段
第二阶段包含两步，过程如下：
-
将 XID 写入到 binlog，然后，将 binlog 持久化到磁盘；
-
调用事务引擎的提交事务接口，将 redo log 状态设置为 commit。
此时，该状态并不需要持久化到磁盘，只需要
write()到操作系统的 Page Cache 中即可。因为，只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功。
-
异常重启会出现什么现象？
我们来看看在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象？下图中有时刻 A 和时刻 B 都有可能发生崩溃：
不管是时刻 A（redo log 已经写入磁盘， binlog 还没写入磁盘），还是时刻 B （redo log 和 binlog 都已经写入磁盘，还没写入 commit 标识）崩溃，此时，redo log 写入事务都处于 prepare 状态。
状态分析：
| 数据库宕机时刻 | redo log | bin log | XA 事务状态 |
|---|---|---|---|
| 时刻 A | 已经写入磁盘 | 未写入磁盘 | prepare |
| 时刻 B | 已经写入磁盘 | 已经写入磁盘 | prepare |
在 MySQL 重启后，会按顺序扫描 redo log 文件，找到处于 prepare 状态的 redo log 写入事务，就使用 redo log 中的 XID 去 binlog 查看是否存在此 XID：
-
如果 binlog 中没有当前内部 XA 事务的 XID
说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。对应时刻 A 崩溃恢复的情况。
-
如果 binlog 中有当前内部 XA 事务的 XID
说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。对应时刻 B 崩溃恢复的情况。
因为，从库同步到完整 binlog 后，该事务就会被从库重放，因此，处于 prepare 阶段的 redo log 加上完整 binlog，如果发生重启就提交该事务。即如果能在 binlog 中查找到与 redo log 相同的 XID，就提交事务；如果没有，则就回滚事务，这样即可保证 redo log 和 binlog 这两份日志的一致性。
两阶段提交是以 binlog 写入成功作为引擎层的事务提交成功的标识，因为 binlog 写成功了，就意味着能在 binlog 中查找到与 redo log 相同的 XID。
需要注意，事务执行中间过程的 redo log 是直接写在 redo log buffer 中的，而这些缓存在 redo log buffer 里的 redo log 也会被后台线程每隔一秒一起持久化到磁盘。
即事务没提交的时候，redo log 也是可能被持久化到磁盘的。
两阶段提交的缺点
两阶段提交虽然保证了两个日志文件的数据一致性，但是性能很差，主要有两个方面的影响：
-
磁盘 I/O 次数高
当 sync_binlog 和 innodb_flush_log_at_trx_commit 都配置为 1 时，每个事务提交都会进行两次
fsync()：一次是 redo log 刷盘，另一次是 binlog 刷盘。 -
锁竞争激烈
两阶段提交虽然能够保证单事务两个日志的内容一致，但在多事务的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致。
组提交
组提交的过程
上面的事务的两阶段提交过程是5.6之前版本中的实现，有严重的缺陷。当sync_binlog=1时，很明显上述的第二阶段中的 write/sync binlog会成为瓶颈，而且还是持有全局锁(prepare_commit_mutex: prepare 和 commit共用一把锁)，这会导致性能急剧下降。解决办法就是MySQL5.6中的 binlog组提交。
MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数，如果说 10 个事务依次排队刷盘的时间成本是 10，那么将这 10 个事务一次性一起刷盘的时间成本则近似于 1。
引入了组提交机制后，prepare 阶段不变，将 commit 阶段拆分为三个过程，每个阶段都分配一个专门的线程处理：
-
flush 阶段
多个事务按进入的顺序，将 binlog 从 cache 写入文件（不刷盘）；
-
sync 阶段
对事务的 binlog 文件执行
fsync()操作（多个事务的 binlog 合并一次刷盘）； -
commit 阶段
各个事务按顺序做引擎层的事务 commit 操作；
上面的每个阶段都会使用一个队列，来维护当前阶段的事务，每个阶段有锁进行保护，因此，保证了事务写入的顺序，第一个进入队列的事务会成为 Leader（后续进入队列的事务就是 Follower），Leader 领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。
对每个阶段引入了队列后，锁就只针对每个队列进行保护，不再锁住提交事务的整个过程，可以看的出来，锁粒度减小了，这样就使得多个阶段可以并发执行，从而提升效率。
以下面的例子，介绍组提交的过程：
组提交的过程如下：
-
事务 T1 第一个进入第一阶段 FLUSH ， 由于 T1 是第一个，所以，T1 是 leader，然后再等待（按照具体算法）；
-
事务 T2 第二个进行第一阶段 FLUSH ， 由于 T2 是第二个，所，T2 是 follower，然后等待 leader 调度；
-
FLUSH 队列等待结束后，开始进入下一阶段 SYNC 阶段，此时，T1 带领 T2 进行一次
fsync()操作，之后进入 commit 阶段，按序提交完成，这就是一次组提交的简要过程了； -
prepare 可以并行，说明两个事务没有任何冲突，有冲突的prepare无法进行进入同一队列；
-
每个队列之间都是可以并行运行的。
MySQL 5.6 不支持 redo log 组提交，MySQL 5.7 支持 redo log 组提交。
MySQL 磁盘 I/O 很高的优化方法
因为，事务在提交的时候，需要将 binlog 和 redo log 持久化到磁盘，那么如果出现 MySQL 磁盘 I/O 很高的现象，我们可以通过控制以下参数，来 “延迟” binlog 和 redo log 刷盘的时机，从而降低磁盘 I/O 的频率：
-
设置组提交的两个参数：
binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数延迟 binlog 刷盘的时机，从而减少 binlog 的刷盘次数。这个方法是基于“额外的故意等待”来实现的，因此，可能会增加语句的响应时间，但即使 MySQL 进程中途挂了，也没有丢失数据的风险，因为 binlog 早被写入到 page cache 了，只要系统没有宕机，缓存在 page cache 里的 binlog 就会被持久化到磁盘。
-
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）
表示每次提交事务都
write()，但累积 N 个事务后才fsync()，相当于延迟了 binlog 刷盘的时机。但是这样做的风险是，主机掉电时会丢 N 个事务的 binlog 日志。 -
将 innodb_flush_log_at_trx_commit 设置为 2
表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到 redo log 文件并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门用来缓存文件数据的，所以写入 redo log文件意味着写入到了操作系统的文件缓存，然后交由操作系统控制持久化到磁盘的时机，但是这样做的风险是，主机掉电的时候会丢数据。
参考：
