By

前言

Github：https://github.com/HealerJean

博客：http://blog.healerjean.com

1. 重做日志（redo log）`
`2. 回滚日志（undo log）`
`3. 二进制日志（**binlog**）`
`4. 错误日志（errorlog）`
`5. 慢查询日志（slow query log）`
`6. 一般查询日志（general log）

binlog文件记录了mysql所有的增、删、改语句，只要有了这个binlog，我们就拥有了mysql的完整备份了。

1、事务原子性原理

1.1、定义

一次操作是不可分割的，要么全部成功，要么全部失败。比如我们的转账操作，不允许出款方成功，收款方失败这种情况，要么都成功，要么多失败，不可能出现中间状态。

1.2、实现 (undo log)

InnoDB 引擎使用 undo log（回滚日志）来保证原子性操作，你对数据库的每一条数据的改动（INSERT、DELETE、UPDATE）都会被记录到 undo log 中，比如以下这些操作：

⬤ 你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。

⬤ 你删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。

⬤ 你修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。

当事务执行失败或者调用了 rollback 方法时，就会触发回滚事件，利用 undo log 中记录将数据回滚到修改之前的样子。

更多关于 undo log 的信息

2、事务持久性原理

2.1、定义

事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

2.2、实现 (redo log + bin log + undo log)

要保证持久性很简单，就是每次事务提交的时候，都将数据刷磁盘上，这样一定保证了安全性，但是要知道如果每次事务提交都将数据写入到磁盘的话，频繁的 IO 操作，成本太高，数据库的性能极低，所以这种方式不可取。

问题1：InnoDB 引擎是怎么解决频繁的 IO 操作的？

答案：InnoDB 引擎引入了一个中间层来解决这个持久性的问题，我们把这个叫做 redo log（归档日志）

问题2：为什么要引入 redo log？

答案：redo log 可以保证持久化又可以保证数据库的性能，相比于直接刷盘，redo log 有以下两个优势：

⬤ redo log 体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。

⬤ redo log 是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

问题3：InnoDB 引擎是怎么做的？

答案：当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。当数据库宕机重启的时候，会将 redo log 中的内容恢复到数据库中，再根据 undo log 和 binlog 内容决定回滚数据还是提交数据

3、事务一致性原理

3.1、定义

一致性简单一点说就是数据执行前后都要处于一种合法的状态，

比如身份证号不能重复，性别只能是男或者女，高考的分数只能在0～750之间，红绿灯只有3种颜色，房价不能为负的等等， 只有符合这些约束的数据才是有效的，比如有个小孩儿跟你说他高考考了1000分，你一听就知道他胡扯呢。数据库世界只是现实世界的一个映射，现实世界中存在的约束当然也要在数据库世界中有所体现。如果数据库中的数据全部符合现实世界中的约束（all defined rules），我们说这些数据就是一致的，或者说符合一致性的。

3.2、实现 (undo log)

要保证数据库的数据一致性，要在以下两个方面做努力：

⬤ 利用数据库的一些特性来保证部分一致性需求：比如声明某个列为 NOT NULL 来拒绝 NULL值得插入等。

⬤ 绝大部分还是需要我们程序员在编写业务代码得时候来保证。

4、事务隔离性隔离性

4.1、定义

多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4.2、实现

隔离性可能会引入脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）等问题，为了解决这些问题就引入了“隔离级别”的概念。

InnoDB 引擎是如何保证隔离性的？利用锁和 MVCC 机制。这里简单的介绍一下 MVCC 机制，也叫多版本并发控制，在使用 READ COMMITTD、REPEATABLE READ 这两种隔离级别的事务下，每条记录在更新的时候都会同时记录一条回滚操作，就会形成一个版本链，在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable）：

⬤ 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。

⬤ 读提交：一个事务提交之后，它做的变更才会被其他事务看到。

⬤ 可重复读： 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

⬤ 串行化： 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

SQL标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：

隔离级别 脏读 不可重复读 幻读
读未提交 可能 可能 可能
读提交 不可能 可能 可能
可重复读 不可能 不可能 可能
串行化 不可能 不可能 不可能

5、日志原理

image-20210801223453555

redo log 是物理日志，undo log 和 binlog 是逻辑日志

⬤ 逻辑日志：可以简单理解为记录的就是sql 语句。

⬤ 物理日志：因为mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

⬤ redo log 重做日志是 InnoDB存储引擎层的，用来保证事务安全

⬤ undo log 回滚日志保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

⬤ binlog 二进制日志是server 层的无论 MySQL用什么引擎，都会有的，主要是做主从复制，时间点恢复使用

5.1、binlog

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql 的逻辑日志，并且由Server层进行记录，使用任何存储引擎的 mysql 数据库都会记录binlog日志。

特点：binlog 是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

5.1.1、使用场景

在实际应用中，binlog的主要使用场景有两个，分别是主从复制和数据恢复。

⬤ 主从复制：在Master 端开启 binlog，然后将 binlog 发送到各个 Slave端，Slave端重放 binlog 从而达到主从数据一致。

⬤ 数据恢复：通过使用mysqlbinlog工具来恢复数据。

5.1.2、binlog 的写入机制

其实，binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache写到binlog文件中。 一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。

1、系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

2、事务提交的时候，执行器把 binlog cache里的完整事务写入到 binlog中，并清空 binlog cache。状态如图1所示。

每个线程有自己 binlog cache，但是共用同一份binlog文件。

图中的write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。

图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IOPS。

write 和 fsync 的时机，是由参数sync_binlog控制的：

⬤ sync_binlog=0 的时候，表示每次提交事务都只write，不fsync，由系统自行判断何时写入磁盘

⬤ sync_binlog=1 的时候，表示每次提交事务都会执行fsync；

⬤ sync_binlog=N(N>1) 的时候，表示每次提交事务都write，但累积N个事务后才fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为100~1000中的某个数值 （sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能）。

但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

image-20210801212451254

5.1.1、日志格式

binlog日志有三种格式，分别为STATMENT、ROW和MIXED。

在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定。

5.1.1.1、STATMENT

基于SQL 语句的复制 (statement-based replication, SBR)， 每一条修改数据的 sql 都会记录到 master 的 binlog 中，slave 在复制的时候，sql 进程会解析成和原来在 master 端执行时的相同的sql 再执行。

⬤ 优点：在 statement 模式下首先就是解决了 row 模式的缺点，不需要记录每一行数据的变化，从而减少了 binlog 的日志量，，节约了IO, 从而提高了性能；

⬤ 缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。 在 statement 模式下，由于它是记录的执行语句，所以，为了让这些语句在 slave 端也能正确执行，那么它还必须记录每条语句在执行的时候的一些相关信息，即上下文信息，以保证所有语句在 slave 端和在master 端执行结果相同。 另外就是，由于 MySQL 现在发展比较快，很多新功能不断的加入，使 MySQL 的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug 也就越容易出现。在statement 中，目前已经发现不少情况会造成 MySQL 的复制出现问题，主要是在修改数据的时候使用了某些特定的函数或者功能才会出现，比如：sleep() 函数在有些版本中就不能被正确复制，在存储过程中使用了 last_insert_id() 函数，可能会使 slave 和 master 上得到不一致的 id 等等。由于 row 模式是基于每一行来记录变化的，所以不会出现类似的问题。

5.1.1.2、ROW

基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。然后在 slave 端再对相同的数据进行修改。row 模式只记录要修改的数据，只有 value，不会有 sql 多表关联的情况。

⬤ 优点：在 row 模式下，binlog 中可以不记录执行的 sql 语句的上下文相关的信息，仅仅只需要记录哪一条记录被修改了，修改成什么样了，所以 row 的日志内容会非常清楚的记录下每一行数据的修改细节，非常容易理，而且不会出现某些特定情况下的存储过程和 function，以及 trigger的调用和触发无法被正确复制问题。；

⬤ 缺点：会产生大量的日志，尤其是alter table 的时候会让日志暴涨

5.1.1.3、MIXED

基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

MySQL 会根据执行的每一条具体的SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 也对 row 模式做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

5.1、redo log 重做日志

redo log 是物理日志，记载着每次在某个页上做了什么修改（用来保证事务安全）。写redo log 也是需要写磁盘的，但它的好处就是顺序IO（我们都知道顺序 IO 比随机 IO快非常多）。写入的速度很快

持久性就是靠redo log来实现的（如果写入内存成功，但数据还没真正刷到磁盘，如果此时的数据库挂了，我们可以靠redo log来恢复内存的数据，这就实现了持久性）

5.1.1、为什么会出现 redo log？

答案： mysql 设计了 redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

1、因为Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！

2、一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机 IO写入性能太差！

a

5.1.2、redo log 基本概念

redo log 包括两部分：一个是内存中的日志缓冲 ( redo log buffer )，另一个是磁盘上的日志文件(redo log file)。

mysql 每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的 WAL (Write-Ahead Logging) 技术。

5.1.2.1、缓存到磁盘方式

在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间(kernel space)缓冲区(OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用 fsync()将其刷到redo log file中，过程如下：

image-20210801210630378

mysql 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过 innodb_flush_log_at_trx_commit 参数配置，各参数值含义如下：

为了控制redo log的写入策略，InnoDB提供了innodb_flush_log_at_trx_commit参数，它有三种可能取值：

image-20210801221605883

image-20210801210811917

5.1.2.1、问题

问题1：一个没有提交的事务的 redo log，可经持久化到磁盘吗?

答案：有可能的，比如

**一种是，redo log buffer占用的空间即将达到 ` innodb_log_buffer_size一半的时候，后台线程会主动写盘。**注意，由于这个事务并没有提交，所以这个写盘动作只是write，而没有调用fsync，也就是只留在了文件系统的page cache`。

另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘。假设一个事务A执行到一半，已经写了一些redo log到buffer中，这时候有另外一个线程的事务B提交，如果innodb_flush_log_at_trx_commit设置的是1，那么按照这个参数的逻辑，事务B要把redo log buffer里的日志全部持久化到磁盘。这时候，就会带上事务A在redo log buffer里的日志一起持久化到磁盘。

5.1.3、生命周期

1、事务开始之后，就开始产生 redo log 日志了，在事务执行的过程中，redo log 开始逐步落盘

2、当对应事务的脏页写入到磁盘之后，redo log 的使命就完成了，它所占用的空间也就可以被覆盖了。

3、InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB。从头开始写，写到末尾就又回到开头循环写

image-20210801205548187

5.1.4、binlog 和 redo log 写入的细节 - 两阶段提交

MySQL通过两阶段提交来保证redo log和 binlog的数据是一致的

update T set c=c+1 where ID=2;

这里我给出这个 update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的

image-20210801224151641

5.1.5、redo log与binlog区别

由binlog 和 redo log的区别可知：

1、binlog日志只用于归档，只依靠binlog是没有crash-safe能力的。

2、但只有redo log也不行，因为redo log是InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要binlog和redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

crash-safe概念

InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

举个列子：当我们修改的时候，写完内存了（buffer），但数据还没真正写到磁盘的时候。此时我们的数据库挂了，我们可以对数据进行恢复

ContactAuthor