MySQL（六）日志和内存

MySQL中常见的日志有哪些

事务日志(redo log 和 undo log) ：redo log 是重做日志，undo log 是回滚日志；
二进制日志（binary log，binlog）：主要记录的是更改数据库数据的 SQL 语句；
慢查询日志（slow query log）：执行时间超过 long_query_time秒钟的查询，解决 SQL 慢查询问题的时候会用到；
错误日志（error log）：对 MySQL 的启动、运行、关闭过程进行了记录。

MySQL三大日志是什么

undo log是Innodb存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC。在事务没提交之前，Innodb会先记录更新前的数据记录undo log中，回滚时利用undo log来进行回滚。
redo log也是Innodb存储引擎层的日志，属于物理日志，记录了某个数据页做了什么修改，实现了事务的持久性，主要用于掉电等故障恢复。比如某个事务提交了，脏页数据还没有刷盘，如果MySQL机器断电了，脏页的数据就丢失了，MySQL重启后可以通过redolog日志，可以将已提交事务的数据恢复回来。
binlog是Server层生成的日志，主要用于数据备份和主从复制。在完成一条更新操作后，Server层会生成一条binlog,等之后事务提交的时候，会将该事务执行过程中产生的所有binlog统一写入binlog文件。binlog文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作。

undo log

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。

实现事务回滚：

每一个事务对数据的修改都会被记录到 undo log ，当执行事务过程中出现错误或者需要执行回滚操作的话，MySQL 可以利用 undo log 将数据恢复到事务开始之前的状态；
undo log 属于逻辑日志，记录的是 SQL 语句，比如说事务执行一条 DELETE 语句，那 undo log 就会记录一条相对应的 INSERT 语句；
- 当进行INSERT操作的时候，产生的undo log只有在事务回滚的时候需要，如果不回滚在事务提交之后就会被删除（对于 INSERT 操作，事务提交后新插入的记录就立即对其他事务可见。因此，不需要利用 undo log 来实现多版本控制）；
- 当进行UPDATE和DELETE的时候，产生的undo log不仅仅在事务回滚的时候需要，在快照读的时候也是需要的，所以不会立即删除，会加入 history list，由后台线程 purge 进行清理。
undo log 是采用 segment（段）的方式来记录的，每个 undo 操作在记录的时候占用一个 undo log segment（undo 日志段），undo log segment 包含在 rollback segment（回滚段）中。事务开始时，需要为其分配一个 rollback segment。每个 rollback segment 有 1024 个 undo log segment，这有助于管理多个并发事务的回滚需求；
undo log 的持久化：
- undo log 和数据页的刷盘策略是一样的，都需要通过 redo log 保证持久化。buffer pool 中有 undo 页，对 undo 页的修改也都会记录到 redo log。redo log 会每秒刷盘，提交事务时也会刷盘，数据页和 undo 页都是靠这个机制保证持久化的。

实现 MVCC（多版本并发控制）：

什么是MVCC：

对于「读提交」和「可重复读」隔离级别的事务来说，它们的快照读（普通 select 语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：

「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列（trx_id 和 roll_pointer）」的比对，如果不满足可见性，就会顺着 undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）

MVCC实现：

MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

undo log 的版本链：一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

通过 trx_id 可以知道该记录是被哪个事务修改的；
通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链，每次对每条聚簇索引进行改动的时候，都会将旧的版本信息写入undo log中，通过回滚指针就能找到记录修改前的信息。

redo log

redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复(一个事务提交之后，对 Buffer Pool 中对应的页的修改可能还未持久化到磁盘)。

实现持久化：

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志；
为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了，后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging预写日志）技术；
- WAL 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。（写入 redo log 的方式使用了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写）（ MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在redo log上，然后在合适的时间再更新到磁盘上，脏页要刷盘，redo log也要刷盘，redo log 是保障持久化）
通过redo log + WAL让 MySQL 有 crash-safe （崩溃恢复）的能力，当系统崩溃时，虽然脏页数据没有持久化，但是在事务提交时，我们会将 redo log 按照刷盘策略刷到磁盘上去， redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态；

redo log 文件写满了怎么办？

redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置，如下图：

write pos 和 checkpoint 的移动都是顺时针方向；
write pos ～ checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作；
check point ～ write pos 之间的部分（图中蓝色部分）：待落盘的脏数据页记录；

如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

所以，一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程。

什么情况下会出现数据丢失？

redo log 写入redo log buffer 但还未写入 page cache ，此时数据库崩溃，就会出现数据丢失（刷盘策略innodb_flush_log_at_trx_commit 的值为 0 时可能会出现这种数据丢失）；
redo log 已经写入 page cache 但还未写入磁盘，操作系统崩溃，也可能出现数据丢失（刷盘策略innodb_flush_log_at_trx_commit 的值为 2 时可能会出现这种数据丢失）。

页修改之后为什么不直接刷盘呢？

为什么每次修改 Buffer Pool 中的页之后不直接刷盘呢？这样不就不需要 redo log 了。

性能非常差。最大的问题就是 InnoDB 页的大小一般为 16KB，而页又是磁盘和内存交互的基本单位。这就导致即使我们只修改了页中的几个字节数据，一次刷盘操作也需要将 16KB 大小的页整个都刷新到磁盘中。而且，这些修改的页可能并不相邻，也就是说这还是随机 IO。

采用 redo log 的方式就可以避免这种性能问题，因为 redo log 的刷盘性能很好。首先，redo log 的写入属于顺序 IO。其次，一行 redo log 记录只占几十个字节。

Buffer Pool 中的脏页在某些情况下（比如 redo log 快写满了）也会进行刷盘操作。不过，这里的刷盘操作会合并写入（合并成一个较大的写入操作），并且减少了磁盘 I/O 的次数，更高效地顺序写入到磁盘。

binlog

binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制。

binlog(binary log 即二进制日志文件) 主要记录了对 MySQL 数据库执行了更改的所有操作(数据库执行的所有 DDL 和 DML 语句)，包括表结构变更（CREATE、ALTER、DROP TABLE…）、表数据修改（INSERT、UPDATE、DELETE...），但不包括 SELECT、SHOW 这类不会对数据库造成更改的操作

binlog 的格式有哪几种?

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：

STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

主从复制是怎么实现?

MySQL 集群的主从复制过程梳理成 3 个阶段：

写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
回放 Binlog：回放 binlog，并更新存储引擎中的数据。

具体详细过程如下：

MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应（Relay Log 作为一个缓冲区，可以减轻从库对主库的压力）。
从库会创建一个用于回放 binlog 的SQL线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性（回放也就是重新执行主库上发生的事务操作）。

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

binlog 什么时候刷盘?

对于InnoDB存储引擎而言，事务在执行过程中，会先把日志写入到binlog cache中，只有在事务提交的时候，才会把binlog cache中的日志持久化到磁盘上的binlog文件中。写入内存的速度更快，这样做也是为了效率考虑。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

那么 binlog 是什么时候刷到磁盘中的呢？可以通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N，默认为 0 ：

0：不去强制要求，由系统自行判断何时写入磁盘；
1：每次提交事务的时候都要将binlog写入磁盘；
N：每 N 个事务，才会将binlog写入磁盘。

什么情况下会重新生成 binlog？

当遇到以下 3 种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：

MySQL服务器停止或重启；
使用 flush logs 命令后；
binlog 文件大小超过 max_binlog_size变量的阈值后。

redo log 和 binlog 有什么区别？

redo log是InnoDB引擎实现的日志，属于物理日志，记录了Innodb存储引擎对数据页所做的修改操作，主要用于崩溃恢复，比如某个事务提交了，脏页数据还没有刷盘，如果MySQL机器断电了，脏页的数据就丢失了，MySQL重启后可以通过重做日志，可以将已提交事务的数据恢复回来。
binlog是server层实现的日志，保存了所有对数据库的增删改操作，binlog有三种日志格式，日志的内容可能是SQL语句、数据本身或两者的混合，主要用于数据库备份和归档，也用于主从复制。

1、适用对象不同：

binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
redo log 是 Innodb 存储引擎实现的日志；

2、文件格式不同：

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED；
redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

3、写入方式不同：

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4、用途不同：

binlog 用于备份恢复、主从复制；
redo log 用于掉电等故障恢复。

redo log和binlog在恢复数据库有什么区别

binlog是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存了所有对数据库的更新操作，可以用来恢复数据库某个时刻的数据或者全量恢复数据库数据。
redo log是循环写，日志空间大小是固定，全部写满就从头开始，保存的是Innodb存储引擎对数据页所做的修改操作，用来恢复因中途MySQL断电丢失的脏页数据。

为什么崩溃恢复不用binlog而用redolog

binlog是server层的日志，不会记录innodb存储引擎层中有哪些数据页没有被刷盘，

redolog是innodb层的日志，可以记录哪些脏页没有被刷盘，崩溃恢复的时候，恢复的粒度更细粒，可以精确到需要恢复的数据页，

而binlog保存的是全量日志，没办法做到这一点，所以崩溃恢复用的是redolog

redo log 和 undo log 区别在哪？

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务。

redo log除了崩溃恢复还有什么其他作用

写Redolog日志是追加的形式，所以redolog写磁盘是一个顺序写的过程，而数据页写磁盘是一个随机写的过程顺序写的性能是比随机写性能高的，事务在提交的时候，是先写日志再写数据的机制，相当于把MySQL写入磁盘的操作从磁盘随机写成了顺序写，所以redo log还可以起到提升MySQL写入磁盘性能的作用。

redo log buffer

redo log刷盘策略

刷盘时机：

事务提交：当事务提交时，redo log buffer 里的 redo log 会被刷新到磁盘（可以通过innodb_flush_log_at_trx_commit参数控制)；
redo log buffer 空间不足时：当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时；
Checkpoint（检查点）：InnoDB 定期会执行检查点操作，将内存中的脏数据（已修改但尚未写入磁盘的数据）刷新到磁盘，并且会将相应的重做日志一同刷新，以确保数据的一致性；
后台刷新线程：InnoDB 启动了一个后台线程，每隔 1 秒将脏页刷新到磁盘，并将相关的重做日志一同刷新。也就是说，一个没有提交事务的 redo log 记录，也可能会被刷盘；
MySQL 正常关闭时。

对于在事务提交时的redo log 刷盘时机，这个默认的行为。

可以由参数 innodb_flush_log_at_trx_commit 参数控制，可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：

当设置该参数为 0 时，表示每次事务提交时，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作；
当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失；
当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存。

InnoDB 的后台线程每隔 1 秒：

针对参数 0 ：会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;
针对参数 2 ：调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，较取值为 0 情况下更安全，因为 MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

性能：0 > 2 > 1 ，安全性：1 > 2 > 0

两阶段提交过程

事务提交后，redo log和binlog都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，

如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入或者，

如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。就会造成两份日志之间逻辑不一致

MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决

两阶段提交把事务的提交拆分成了2个阶段，分别是准备阶段和提交阶段。

准备阶段会将redo log状态设置为prepare状态，然后将redo log刷入磁盘；
提交阶段会将binlog刷入磁盘，然后设置redo log设置为commit状态，到这里两阶段就已经完成了。

在两阶段提交中，是以binlog刷入磁盘时机作为事务提交成功的标志的：

如果binlog还没刷入磁盘的时候，MySQL就发生了崩溃，MySQL重启的时候就需要回滚事务；
如果binlog刷入磁盘，即使redo log没有设置commit状态，MySQL就发生了崩溃，MySQL重启的时候就会提交事务。

Buffer Pool

机制：

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页
当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘
Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

Note
查询一条记录，就只需要缓冲一条记录吗？
不是的。
当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

脏页什么时候会被刷入磁盘

当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；
MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

参考：

mysql的mvcc详解_mysql mvcc-CSDN博客

https://xiaolincoding.com/