【读书笔记】MySQL存储引擎_第六天

最新推荐文章于 2024-10-01 17:33:05 发布

走出半生仍是少年

最新推荐文章于 2024-10-01 17:33:05 发布

阅读量285

点赞数

分类专栏： mysql 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/m0_46761060/article/details/124761405

版权

mysql 专栏收录该内容

22 篇文章 2 订阅

订阅专栏

一、事务

事务会把数据库从一种抑制状态转换为另一种一致状态。在数据库提交工作时，可以确保要嘛所有修改都已经保存了，要嘛所有修改都不保存。这也是事务模型区别于文件系统的重要特征之一。

InnoDB存储引擎中的事务完全符合ACID的特性。ACID试一下4个词的缩写：

原子性（atomicity）
一致性（consistency）
隔离性（isolation）
持久性（durability）

理论上说，事务有着极其严格的定义，它必须同时满足四个特性，即通常所说的事务的ACID特性。值得注意的是，虽然理论上定义了严格的事务要求，但是数据库厂商出于各种目的，并没有严格去满足ACID的标准。

NDB Cluster引擎虽然支持事务，但是不满足D（持久性）的要求
Oracle默认事务隔离级别是READ COMMITTED，不满足I（隔离性）的要求

1.1 A（Atomicity）原子性

要么都做要么都不做

1.2 C（Consistency）一致性

事务将数据库从一种状态转变为下一种一致的状态

1.3 I（Isolation）隔离性

隔离性还有别的称呼：并发控制、可串行化、锁
要求每个读写事务的对象对其他事务的操作对象能相互分离（该事务提交前对其他事务不可见），通常使用锁来完成

1.4 D（Durabolity）持久性

事务一旦提交，其结果就是永久性的，即时发生宕机等事故，数据库也能将数据恢复

二、事务分类

2.1 扁平事务

最简单也是平时使用最频繁的一种

就是常用的

BEGIN
XXXXXXX操作
COMMIT

缺点：如果中间操作的数据量很大，那么回滚起来代价就很大，是否能只回滚某一部分？——>带有保存点的扁平事务

2.2 带有保存点的扁平事务

因为某些事务可能在执行过程中出现的错误并不会导致所有操作都无效，放弃整个事务不合乎要求，开销也太大。保存点(SavePoint)用来通知系统应该记住事务当前的状态，以便当之后发生错误时，事务能回到保存点当时的状态。
在这里插入图片描述

灰色背景部分的操作表示由ROLLBACK WORK而导致部分回滚，实际并没有执行的操作
当用BEGIN WORK开启一个事务时，隐式地包含了一个保存点，当事务通过 ROLLBACK WORK:2发出部分回滚命令时，事务回滚到保存点2，接着依次执行，并再次执行到 ROLLBACK WORK:7，直到最后的 COMMIT WORK操作，这时表示事务结束，除灰色阴影部分的操作外，其余操作都已经执行，并且提交
另一点需要注意的是，保存点在事务内部是递增的，这从上图中也能看出。有人可能会想，返回保存点2以后，下一个保存点可以为3，因为之前的工作都终止了。然而新的保存点编号为5，这意味着ROLLBACK不影响保存点的计数，并且单调递增的编号能保持事务执行的整个历史过程，包括在执行过程中想法的改变
此外，当事务通过 ROLLBACK WORK:2命令发出部分回滚命令时，要记住事务并没有完全被回滚，只是回滚到了保存点2而已。这代表当前事务还是活跃的，如果想要完全回滚事务，还需要再执行命令ROLLBACK WORK

2.3 链事务

它保存点模式的一种变种
带有保存点的扁平事务，当发生系统崩溃时，所有的保存点都将消失，因为其保存点是易失的（volatile），而非持久的( persistent)。这意味着当进行恢复时，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行
链事务的思想是：在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务
注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的一样。下图显示了链事务的工作方式：

⭐：链事务与带有保存点的扁平事务不同：

带有保存点的扁平事务能回滚到任意正确的保存点
而链事务中的回滚仅限于当前事务，即只能恢复到最近一个的保存点
对于锁的处理，两者也不相同：
- 链事务在执行COMMIT后即释放了当前事务所持有的锁
- 而带有保存点的扁平事务不影响迄今为止所持有的锁

2.4 嵌套事务

嵌套事务是一个层次结构框架。由一个顶层事务控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务（subtransaction），其控制每一个局部的变换
嵌套事务的层次结构如图所示：
子事务既可以提交也可以回滚。但是它的提交操作并不马上生效，除非其父事务已经提交。因此可以推论出，任何子事物都在顶层事务提交后才真正的提交
树中的任意一个事务的回滚会引起它的所有子事务一同回滚，故子事务仅保留A、C、I特性，不具有D的特性

2.5 分布式事务

通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点

假设一个用户在ATM机进行银行的转账操作，例如持卡人从招商银行的储蓄卡转账10000元到工商银行的储蓄卡。在这种情况下，可以将ATM机视为节点A，招商银行的后台数据库视为节点B，工商银行的后台数据库视为C，这个转账的操作可分解为以下的步骤：

节点A发出转账命令
节点B执行储蓄卡中的余额值减去10000。
节点C执行储蓄卡中的余额值加上10000
节点A通知用户操作完成或者节点A通知用户操作失败。

这里需要使用分布式事务，因为节点A不能通过调用一台数据库就完成任务。其需要访问网络中两个节点的数据库，而在每个节点的数据库执行的事务操作又都是扁平的。对于分布式事务，其同样需要满足ACID特性，要么都发生，要么都失效。对于上述的例子，如果2)、3)步中任何一个操作失败，都会导致整个分布式事务回滚。若非这样，结果会非常可怕

2.6 InnoDB支持的事务

默认模式下，MySQL是AUTOCOMMIT模式，所有数据库更新操作都会立即提交。这表示除非显式地开始一个事务，否则每个查询都被当作一个单独的事务处理

对于InnoDB来说，它支持扁平事务、带保存点的扁平事务、链事务、分布式事务。它不支持嵌套事务。因此对于有并发事务需求的用户来说MySQL数据库或InnoDB存储引擎显得无能为力。

三、事务的实现

原子性、一致性、持久性通过数据库的redo log和undo log来实现

redo和undo的作用都可以视为一种恢复操作,redo恢复提交事务修改的页操作,而undo回滚行记录到某个特定版本。redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行进行记录

3.1 redo log

3.1.1 基本概念

重做日志来实现事务的持久性。它由两部分组成：一个是重做日志缓冲（redo log buffer），这是易失的；二是重做日志文件（redo log file），这是持久的

当时事务提交时，必须先将事务的所有日志写入重做日志文件进行持久化，等事务的COMMIT操作完成才算完成。

redo log用来保证事务的持久性，undo log用来保证事务的原子性，它用来帮助事务的回滚以及MVCC功能。redo log基本都是顺序写的，数据库运行时不需要对redo log文件进行读取操作。而undo log需要进行随机读写。

InnoDB允许用户手动设置非持久性的情况发生，以此来提高数据库的性能。
当事务提交时，日志不写入重做日志中，而是等一段时间后执行fsync操作持久化到磁盘。这确实可以提高性能，但是容易造成如果突然宕机，数据来不及持久化而造成的损失。

3.1.2 binlog

binlog的作用是：进行POINT-IN-TIME的恢复及主从复制（Replication）环境的建立。

binlog和redo log的区别：

redolog是InnoDB引擎层产生的，binlog是在MySQL数据库的Server层产生的
redolog是一种物理日志，记录的是每个页的修改；binlog是一种逻辑日志，记录的是对应的SQL语句（实际上binlog有三种形式，statement形式是SQL语句，rows形式是具体的行的页的变化，mixed是两者结合）
redolog在事务进行中不断写入；binlog旨在事务完成提交后进行一次写入（实际上为了保证两个log的一致性，redolog采用了两阶段理论，commit前的prepare状态和commit之后的commit状态）

3.1.3 log block

重做日志都是以512字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块的方式进行保存的，称之为重做日志快。

若一个页中产生的重做日志数量大于512字节，那么需要分割为多个重做日志块进行存储
由于重做日志块的大小和磁盘扇区大小一样，都是512字节，因此重做日志的写入可以保证原子性，不需要doublewrite技术

重做日志块除了日志本身之外，还由日志块头（log block header）及日志块尾(log block tailer)两部分组成。重做日志头一共占用12字节，重做日志尾占用8字。故每个重做日志块实际可以存储的大小为492字节（512-12-8），如下：
在这里插入图片描述
log block header由4部分组成，如下：

log buffer(日志缓存)是由log block组成，在内部log buffer就好似一个数组，因此LOG_BLOCK_HDR_NO用来标记log block在这个数组中的位置。其是递增并且循环使用的，占用4个字节，但是由于第一位用来判断是否是flush bit，所以最大的值为2G
LOG_BLOCK_HDR_DATA_LEN占用2字节，表示log block所占用的大小，当log block被写满时，该值为0x200，表示使用全部log block空间，即占用512字节
LOG_BLOCK_FIRST_REC_GROUP占用2个字节，表示log block中第一个日志所在的偏移量
如果该值的大小和 LOG_BLOCK_HDR_DATA_LEN相同，则表示当前log block不包含新的日志

如下，事务T1的重做日志1占用762字节，事务T2的重做日志占用100字节
由于每个log block实际只能保存492个字节
因此其在log buffer中的情况如下所示：
在这里插入图片描述
从上图可以看到，由于事务T1的重做日志占用792字节，因此需要占用两个log block，左边整个492 + 右边的一部分(270)
左侧的log block中LOG_BLOCK_FIRST_REC_GROUP为12，即log block中第一个日志的开始位置
在第二个log block中，由于包含了之前事务T1的重做日志，事务T2的日志才是log block中第一个日志，因此该log block的LOG_BLOCK_FIRST_REC_GROUP为282（270+12）

LOG_BLOCK_CHECKPOINT_NO占用4字节，表示该log block 最后被写入时的检查点第4字节的值

log block tailer-重做日志块尾
log block tailer只由1个部分组成，且其值和LOG_BLOCK_HDR_NO相同，并在函数log_block_init中被初始化
在这里插入图片描述

3.1.4 log group

log group为重做日志组，其中有多个重做日志文件
log group是一个逻辑上的概念，并没有一个实际存储的物理文件来表示 log group信息
log group 由多个重做日志文件组成，每个log group中的日志文件大小是相同的

重做日志文件中存储的就是之前在log buffer(日志缓冲)中保存的log block(日志块)，因此其也是根据块的方式进行物理存储的管理，(重做日志文件)的每个块的大小与log block一样，同样为512字节

对于log block的写入追加（append）在redo log file的最后部分
当一个redo log file被写满时，会接着写入下一个redo log file，其使用方式为round-robin(轮询)

每个redo log file的前2KB的部分不保存log block的信息
对于log group中的第一个redo log file，其前2KB的部分保存4个512字节大小的块，如下图：
在这里插入图片描述
上述信息仅在每个log group 的第一个redo log file中进行存储，log group中的其余redo log file 仅保留这些空间，但不保存上述信息

正因为保存了这些信息，就意味着对redo log file的写入并不是完全顺序的
因为其除了log block的写入操作，还需要更新前2KB部分的信息，这些信息对于InnoDB存储引擎的恢复操作来说非常关键和重要
故log group与redo log file之间的关系如下所示：

在这里插入图片描述
在log filer header后面的部分(上图中的CP1) 为 InnoDB存储引擎保存的 checkpoint（检查点）值，其设计是交替写入，这样的设计避免了因介质失败而导致无法找到可用的checkpoint的情况

重做日志刷入磁盘时机：

事务提交时
当log buffer中有一半的内存空间已经被使用时
Master Thread 每秒一次执行刷新Innodb_log_buffer到重做日志文件

3.1.5 重做日志格式

重做日志格式(指的是重做日志块中那492个字节的部分)
不同的数据库操作会有对应的重做日志格式
由于InnoDB存储引擎的存储管理是基于页的，故其重做日志格式也是基于页的
虽然有着不同的重做日志格式，但是它们有着通用的头部格式
在这里插入图片描述
重做日志的头部格式由以下3部分组成：

redo_log_type：重做日志的类型
space：表空间的ID
page_no：页的偏移量

之后redo log body的部分，根据重做日志类型的不同，会有不同的存储内容，下面分别是一个插入和删除的重做日志格式
在这里插入图片描述
到InnoDB1.2版本时，一共有51种重做日志类型

3.1.6 LSN日志序列号

LSN是Log Sequence Number的缩写，其代表的是日志序列号
在InnoDB存储引擎中，LSN占用8字节，并且单调递增
LSN表示的含义有：

重做日志写入的总量
checkpoint的位置
页的版本

LSN 表示事务写入重做日志的字节的总量
例如当前重做日志的LSN为1000，有一个事务T1写入了100字节的重做日志，那么LSN就变为了1100，若又有事务T2写入了200字节的重做日志，那么LSN就变为了1300
LSN记录的是重做日志的总量，其单位为字节

LSN不仅记录在重做日志中，还存在于每个页中
在每个页的头部，有一个值FIL_PAGE_LSN，记录了该页的LSN
在页中，LSN 表示该页最后刷新时 LSN的大小
因为 重做日志记录的是每个页的日志，因此 页中的LSN 用来判断页是否需要进行恢复操作

例如，页P1的LSN为10000，而数据库启动时，InnoDB检测到写入重做日志中的LSN为13000，并且该事务已经提交，那么数据库需要进行恢复操作，将重做日志应用到P1页中
同样的，对于重做日志中LSN 小于 P1页的LSN，不需要进行重做，因为P1页中的LSN表示页已经被刷新到该位置
可以通过命令SHOW ENGINE INNODB STATUS查看LSN的情况

---
LOG
---
Log sequence number          359165668
Log buffer assigned up to    359165668
Log buffer completed up to   359165668
Log written up to            359165668
Log flushed up to            359165200
Added dirty pages up to      359165668
Pages flushed up to          359164533
Last checkpoint at           359164533
1257893 log i/o's done, 0.88 log i/o's/second

Log sequence number 表示当前的LSN
Log flushed up to 表示刷新到重做日志文件的 LSN
Last checkpoint at 表示刷新到磁盘的 LSN

3.1.7 redo log的恢复

InnoDB存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作
因为重做日志记录的是物理日志，因此恢复的速度比逻辑日志，如二进制日志，要快很多
与此同时，InnoDB存储引擎自身也对恢复进行了一定程度的优化，如顺序读取及并行应用重做日志，这样可以进一步地提高数据库恢复的速度
由于checkpoint表示已经刷新到磁盘页上的LSN，因此在恢复过程中仅需恢复checkpoint开始的日志部分
在这里插入图片描述
当数据库在checkpoint的LSN为10 000时发生宕机，恢复操作仅恢复LSN 10 000～13 000范围内的日志
InnoDB存储引擎的重做日志是物理日志，因此其恢复速度较之二进制日志恢复快得多

3.2 undo log

3.2.1 主要概念

undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment）。undo段位于共享表空间内。可以通过py_innodb_page_info.py工具来查看当前共享表空间中undo的数量。

用户通常对undo有这样的误解：undo用于将数据库物理地恢复到执行语句或事务以前的样子——但事实并非如此。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。

比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

对于每个INSERT，InnoDB存储引擎会完成一个DELETE；对于每个DELETE，InnoDB存储引擎会执行一个INSERT；对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去。

undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取

undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。

3.2.2 undo存储管理

undo采用的也是段的形式，但是这个段和之前的段不同。InnoDB存储引擎有rollback segment，每个回滚段中记录了1024个undo segment，而在每个undo log segment段中进行undo页的申请。共享表空间偏移量为5的页(0,5)记录了所有rollback segment header所在的页，这个页的类型为FIL PAGE_TYPE_SYS。

1.1版本之前InnoDB只有一个rollback segment
1.1版本及以后有128个segment，一个segment限制事务为1024个。1.1之后就有128*1024个了

需要注意的是，事务在undo log segment分配也并写入undo log的这个过程中同样需要写入重做日志。当事务提交时，InnoDB存储引擎会做以下两件事：

将undo log放入列表中，以供之后的purge操作
判断undo log所在的页是否可以重用，如果可以就把他分配给下一个事务使用

事务提交之后不能马上删除undo log以及undo log所在的页。因为可能还有其他事务需要通过undo log来得到行记录之前的版本。

在InnoDB存储引擎的设计中对undo页可以进行重用。具体说来，当事务提交时，首先将undo log放入链表中，然后判断undo页的使用空间是否小于3/4，若是则表示该undo页可以重用，之后新的undo log记录可能放着不同事务的undo log，因此purge操作需要设计磁盘的离散读取操作，是一个比较缓慢的操作。

3.2.3 undo log格式

在InnoDB存储引擎中，undo log分为：

insert undo log
update undo log

insert undo log指在insert操作中产生的undo log，因为insert操作的记录，只对事务本身可见，对其他事务不可见（这是事务隔离性的要求），故该undo log可以在事务提交后直接删除。不需要进行purge操作

insert undo log结构如下：
在这里插入图片描述

上图中*表示对存储的字段进行了压缩
insert undo log开始的前两个字节next 记录的是下一个undo log的位置，通过该next的字节可以知道一个undo log所占的空间字节数
类似地，尾部的两个字节start记录的是undo log的开始位置
type_cmpl占用一个字节，记录的是undo的类型，对于insert undo log，该值总是为11
undo_no记录事务的ID，table_id记录undo log所对应的表对象。这两个值都是在压缩后保存的
接着的部分记录了所有主键的列和值。在进行rollback操作时，根据这些值可以定位到具体的记录，然后进行删除即可

记录的是对delete和update操作产生的undo log
该undo log 可能需要提供MVCC机制，因此不能在事务提交时就进行删除，事务提交时放入undo log链表，等待purge线程进行最后的删除

update undo log结构如下：
在这里插入图片描述
update undo log相对于之前介绍的insert undo log，记录的内容更多，所需占用的空间也更大

next、start、undo_no、table_id与之前介绍的insert undo log部分相同
这里的type_cmpl，由于update undo log本身还有分类，故其可能的值如下：
1. TRX_UNDO_UPD_EXIST_REC更新non-delete-mark的记录
2. TRX_UNDO_UPD_DEL_REC将delete的记录标记为not delete
3. TRX_UNDO_DEL_MARK_REC将记录标记为delete
接着的部分记录update_vector信息，update_vector表示update操作导致发生改变的列。每个修改的列信息都要记录的undo log中。对于不同的undo log类型，可能还需要记录对索引列所做的修改

delet操作并不直接删除记录，而只是将记录标记为删除，也就是将记录的delete flag设置为1。最终删除是在purge操作中完成。

update操作分两步，先将记录标记为已删除，之后插入一条新的记录

3.2.4 purge操作

delete和update操作可能并不直接删除原有的数据
例如，DELETE FROM t WHERE a=1;，表t上列a有聚集索引，列b上有辅助索引对于上述的delete操作，通过undo log的介绍已经知道仅是将主键列等于1的记录delete flag设置为1，记录并没有被删除，即记录还是存在于B+树中；其次，对辅助索引上a=1，b=1的记录同样没有做任何处理，甚至没有产生undo log而真正删除这行记录的操作其实被“延时”了，最终在purge线程操作中完成

purge线程用于最终完成 delete和update操作
这样设计是因为 InnoDB存储引擎支持MVCC，所以记录不能在事务提交时立即进行处理，这时其他事物可能正在引用这行，故InnoDB存储引擎需要保存记录之前的版本而是否可以删除该条记录通过purge线程来进行判断，若该行记录已不被任何其他事务引用，那么就可以进行真正的delete操作可见，purge操作是清理之前的delete和update操作，将上述操作“最终”完成而实际执行的操作为 delete操作，清理之前行记录的版本

上面提到过，InnoDB存储引擎的undo log设计是这样的：一个页上允许多个事务的undo log 存在.虽然这不代表事务在全局过程中提交的顺序，但是后面的事务产生的undo log 总在最后此外，InnoDB存储引擎还有一个history列表，它根据事务提交的顺序，将undo log进行链接
在这里插入图片描述
在上图的例子中，history list 表示按照事务提交的顺序将undo log进行组织

在InnoDB存储引擎的设计中，先提交的事务总在尾端
undo page(页)存放了undo log，由于可以重用，因此一个undo page中可能存放了多个不同事务的undo log
trx5的灰色阴影表示该undo log还被其他事务引用

在执行purge的过程中，InnoDB存储引擎首先从history list中找到第一个需要被清理的记录，这里为trx1，清理之后InnoDB存储引擎会在 trx1的undo log所在的页中继续寻找是否存在可以被清理的记录，这里会找到事务trx3，接着找到trx5，但是发现trx5被其他事务所引用而不能清理，故去再次去history list中查找，发现这时最尾端的记录为trx2，接着找到trx2所在的页，然后依次再把事务trx6、trx4的记录进行清理。由于undo page2中所有的页都被清理了，因此该undo page可以被重用

InnoDB存储引擎这种先从history list中找undo log，然后再从undo page中找undo log的设计模式是为了避免大量的随机读取操作，从而提高purge的效率

全局动态参数innodb_purge_batch_size 用来设置每次purge操作需要清理的 undo page数量，如下：
在这里插入图片描述
当InnoDB存储引擎的压力非常大时，并不能高效地进行purge操作
那么history list的长度会变得越来越长。全局动态参数innodb_max_purge_lag用来控制history list的长度，若长度大于该参数时，其会“延缓”DML的操作，如下：
在这里插入图片描述
该参数默认值为0，表示不对history list做任何限制
当大于0时，就会延缓DML的操作，其延缓的算法为：
delay=（（length(history_list)-innodb_max_purge_lag）*10)-5

InnoDB1.2版本引入了新的全局动态参数innodb_max_purge_lag_delay，其用来控制delay的最大毫秒数
也就是当上述计算得到的delay值大于该参数时，将delay设置为innodb_max_purge_lag_delay，避免由于purge操作缓慢导致其他SQL线程出现无限制的等待，如下：
在这里插入图片描述

3.2.5 group commit

若事务为非只读事务，则每次事务提交时需要进行一次fsync操作，以此保证重做日志都已经写入磁盘

为了提高磁盘fsync的效率，当前数据库都提供了group commit的功能，即一次fsync可以刷新确保多个事务日志被写入文件

对于InnoDB存储引擎来说，事务提交时会进行两个阶段的操作：

修改内存中事务对应的信息，并且将日志写入重做日志缓冲
调用fsync 将确保日志都从重做日志缓冲写入磁盘

正在提交的事物完成提交操作后，再次进行步骤2时，可以将多个事务的重做日志通过一次fsync刷新到磁盘，这样就大大地减少了磁盘的压力，从而提高了数据库的整体性能。对于写入或更新较为频繁的操作，group commit的效果尤为明显

导致这个问题的原因是在开启二进制日志后，为了保证存储引擎层中的事务和二进制日志的一致性，二者之间使用了两阶段事务，其步骤如下：

当事务提交时InnoDB存储引擎进行prepare操作
MySQL数据库上层写入二进制日志
InnoDB存储引擎层将日志写入重做日志文件
3.1 修改内存中事务对应的信息，并且将 (重做)日志写入重做日志缓冲
3.2 调用fsync 将确保 (重做)日志都从重做日志缓冲写入磁盘

每个步骤都需要进行一次fsync操作才能保证上下两层数据的一致性
步骤2的fsync由参数sync_binlog控制
步骤3的fsync由参数innodb_flush_log_at_trx_commit控制
在这里插入图片描述
为了保证MySQL数据库上层二进制日志的写入顺序和 InnoDB层的事务提交顺序一致，MySQL数据库内部使用了prepare_commit_mutex这个锁
但是在启用这个锁之后，步骤3中的步骤3.1 不可以在其他事务执行步骤3.2时进行，从而导致了group commit失效

为什么需要保证MySQL数据库上层二进制日志的写入顺序和InnoDB层的事务提交顺序一致呢？
这时因为备份及恢复的需要，例如通过工具xtrabackup或者ibbackup进行备份，并用来建立replication，如下所示：
在这里插入图片描述
可以看到若通过在线备份进行数据库恢复来重新建立replication，事务T1的数据会产生丢失
因为在InnoDB存储引擎层会检测事务T3在上下两层都完成了提交，不需要再进行恢复
因此通过锁prepare_commit_mutex以串行的方式来保证顺序性，然而这会使group commit无法生效，如下所示：
在这里插入图片描述
MySQL5.6采用了类似的实现方式，并将其称为Binary Log Group Commit(BLGC)。

MySQL5.6 BLGC的实现方式是将事务提交的过程分为几个步骤来完成：

Flush阶段，将每个事务的二进制日志写入内存中
Sync阶段，将内存中的二进制日志刷新到磁盘，若队列中有多个事务，那么仅一次fsync操作就完成了二进制日志的写入，这就是BLGC
Commit阶段，leader根据顺序调用存储引擎层事务的提交，InnoDB存储引擎本就支持group commit，因此修复了原先由于锁prepare_commit_mutex导致group commit失效的问题。

3.3 隐式提交的SQL语句

DDL语句: ALTER DATABASEUPGRADE DATA DIRECTORY NAME,ALTER EVENT, ALTER PROCEDURE, ALTER TABLE, ALTER VIEW，CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER, CREATE VIEW,DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE，DROP TABLE, DROP TRIGGER, DROP VIEW, RENAME TABLE，TRUNCATE TABLE。
用来隐式地修改 MySQL架构的操作: CREATE USER、 DROP USER、 GRANT 、RENAME USER、 REVOKE、 SET PASSWORD。
管理语句: ANALYZE TABLE、 CACHE INDEX、 CHECK TABLE、 LOAD INDEX INTO CACHE、 OPTIMIZE TABLE、 REPAIR TABLE。

3.4 分布式事务

使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE
InnoDB引擎提供了XA事务的支持，并通过XA事务来支持分布式事务的实现。
XA事务允许不同数据库之间的分布式事务，比如一个是MySQL、一个是Oracle；只要参与在全局事务中的每个节点支持XA事务就行。

XA事务由一个或多个资源管理器、一个事务管理器以及一个应用程序组成。

资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器
事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信
应用程序：定义事务的边界，指定全局事务中的操作

在 Mysql 数据库的分布式事务中，资源管理器就是 Mysql 数据库，事务管理器为连接 Mysql 服务器的客户端。
在这里插入图片描述
分布式事务使用两段式提交的方式。在第一阶段，所有参与全局事务的结点都开始准备（PREPARE），告诉事务管理器他们准备好提交了。在第二阶段，事务管理器告诉资源管理器执行 ROLLBACK 还是 COMMIT。如果任何一个结点显示不能提交，则所有的结点都被告知需要回滚。
在这里插入图片描述

3.4.1 内部XA事务

上述的分布式事务是外部事务，即资源管理器是 Mysql 数据库本身。在 Mysql 数据库中还存在另一种分布式事务，其在存储引擎与插件之间，又或者在存储引擎与存储引擎之间，称之为内部XA事务。

最常见的内部XA事务存在于 binlog 与 InnoDB 存储引擎之间。由于复制的需要，因此目前绝大多数的数据库都开启了 binlog 功能。在事务提交时，先写二进制日志，再写 InnoDB 存储引擎的重做日志。上述两个操作必须是原子的。
在这里插入图片描述

3.4.2 不好的事务习惯

1. 循环中提交事务

我们先来比较两个存储过程

CREATE PROCEDURE load1 (count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a', 80);
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
COMMIT;
SET s=s+1;
END WHILE;
END;

CREATE PROCEDURE load2(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a', 80)
START TRANSACTION;
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
SET s = s+1;
END WHILE;
COMMIT;
END;

因为InnoDB存储引擎默认是自动提交的，所以load1中如果去掉Commit语句，结果也是一样的。单纯比较两个存储过程我们就可以知道，load1中存在一个问题，例如当用户需要插入10000条记录，但是在插入第5000条时，发生了错误，这时前5000条记录已经存放在数据库中。而load2中整体都是放在一个事务中，所以不会出现这个问题。除此之外，这两个存储过程的性能也存在差别，load2要比load1快出许多，这是因为每一次提交都要写一次redo log，存储过程load1实际上写了10000次重做日志，而对于存储过程load2来说，实际只写了1次redo log。因此执行时间会有巨大的差距。
在这里插入图片描述
在别的一些数据库中，可能总是要求对事务尽快地释放，不能有长时间的事务；其次，可能担心存在Oracle数据库中由于没有足够undo产生的Snapshot Too Old的经典问题。MySQL的InnoDB引擎没有上述的问题，因此无论是何种角度，都不应该在一个循环中反复进行提交操作，不论是显式的还是隐式的提交。

2. 使用自动提交

自动提交不是一个好的习惯，因为这可能使开发人员产生错误的理解。我们可以使用如下语句来改变当前自动提交的方式：

SET autocommit=0;

也可以使用START TRANSACTION, BEGIN来显式地开启一个事务。在显式开启事务后，在默认设置下（即参数completion_type等于0），MySQL会自动地执行SET AUTOCOMMIT=0的命令，并在COMMIT或ROLLBACK结束一个事务后执行SET AUTOCOMMIT=1。此外对于不同语言的API，自动提交是不同的，因此在选用不同的语言来编写数据库应用程序前，应该对连接MySQL的API做好研究。

3. 使用自动回滚

InnoDB存储引擎支持通过定义一个HANDLER来进行自动事务的回滚操作，如在一个存储过程中发生了错误会自动对其进行回滚操作。因此我发现很多开发人员喜欢在应用程序的存储过程中使用自动回滚操作，例如下面所示的一个存储过程：

CREATE TABLE 'b' (
'a' int(11) NOT NULL DEFAULT '0',
PRIMARY KEY ('a')
) ENGINE=InnoDB DEFAULT CHARSET=latin1
```　　


```sql
CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 2;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 3;
COMMIT;
END;

存储过程sp_auto_rollback_demo首先定义了一个exit类型的HANDLER,当捕获到错误时进行回滚。因此插入第二个记录1时会发生错误，但是因为启用了自动回滚操作，因此这个存储过程的执行结果是没有问题的，看起来非常正常。但我们并不能看出这个存储过程的结果到底是正确还是错误的。为了得到执行正确与否的结果，开发人员可能会进行这样的处理：

CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; SELECT -1; END;
START TRANSACTION;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 2;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 3;
COMMIT;
SELECT 1;
END;

这样如果发生错误，先回滚然后返回-1，表示运行有错误。运行正常返回值1。但是其实问题还是没有解决，对开发人员来说，重要的不仅是知道是否发生了错误，还要知道发生了什么错误。因此自动回滚存在这样一个问题。这里其实有一个建议是：对事务的BEGIN,COMMIT和ROLLBACK操作应该交给程序端来完成，存储过程需要完成的只是一个逻辑操作，即对逻辑进行封装。在程序中控制事务的好处是，用户可以得知发生错误的原因。

4. 长事务

顾名思义，长事务是执行时间较长的事务。比如，对于银行系统的数据库，每过一个阶段可能需要更新对应账户的利息。如果对应账号的数量非常大，例如对有1亿用户的表account，需要执行下列语句：

UPDATE account
SET account_total = account_total + (1 + interest_rate)

这个事务可能需要非常长的时间来完成。由于事务ACID的特性，这个操作被封装在一个事务中完成，这就产生了一个问题，在执行过程中，当数据库或操作系统，硬件等发生问题时，重新开始事务的代价变得不可接受。数据库需要回滚所有已经发生的变化，而这个过程可能比产生这些变化的时间还要长。因此，对于长事务的问题，有时候可以通过转化为小批量的事务来进行处理。当事务发生错误时，只需要回滚一部分数据，然后接着上次已经完成的事务继续进行。