MySQL基础知识——MySQL日志

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。

那么，一条更新语句的执行流程又是怎样的呢？

下面我们从一个表的一条更新语句进行具体介绍：

假设这个表有一个主键ID和一个整型字段c：

mysql> create table T(ID int primary key, c int);

如果要将ID=2这一行的值加1， SQL语句就会这么写：

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

首先，可以确定的说，查询语句的那一套流程，更新语句也是同样会走一遍。

更新语句的执行流程：

1）连接器连接数据库；

2）分析器通过词法分析和语法分析直到这是一条更新语句；

3）优化器决定1使用ID这个索引；

4）执行器负责具体执行，找到这一行，然后更新。

注：在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表T上所有缓存结果都清空。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志），这也是本文的重点，下面对这两个模块做具体介绍。

重要日志模块：redo log

redo log是重做日志，保证crash-safe。

redo log写入逻辑

1）redo log写入逻辑

事务执行过程中，生成的redo log会先写到redo log buffer；（内存）
redo log buffer中的日志并不一定会立即持久化到磁盘；
当结点宕机或者系统掉电后，redo log buffer中的日志丢失，但此时事务没有提交，binlog日志未落盘，丢失数据并不影响一致性；
InnoDB有一个后台线程，每隔1秒会把redo log buffer中的日志，flush到文件系统的page cache中，然后调用fsync持久化到磁盘；

注：redo log buffer是全局共用的。

2）redo log可能的状态

存在redo log buffer中，在进程内存中，即图中红色部分；
写到磁盘(flush)，但未持久化（fsync)，即在文件系统的page cache里，即图中的黄色部分；
持久化到磁盘，对应的是hard disk，即图中的绿色部分。

3）innodb_flush_log_at_trx_commit配置（控制redo log的sync时机）

设置为0，表示每次事务提交时都只是把redo log留在redo log buffer中；
设置为1，表示每次事务提交时都将redo log直接持久化到磁盘；
设置为2，表示每次事务提交时都只是把redo log写到page cache；（可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去）

4）redo log的fsync时机

事务提交时：根据innodb_flush_log_at_trx_commit执行fsync；
1s定时任务：后台线程每隔1s执行一次flush和sync；
redo log buffer使用率过半时：占用空间即将达到innodb_log_buffer_size的一半时，后台线程会主动flush，但不会sync；
其它并行的事务提交时：顺带将这个事务的redo log buffer持久化到磁盘；则其中其它事务的日志也一并flush并sync到磁盘；

注：flush是把redo log buffer中的redo log刷到page cache，但不会持久化到磁盘；sync是把redo log buffer中的redo log持久化到磁盘。

刷脏

1）刷脏：No Stale + No Force流程的后半段，用于持久化已提交的数据；（WAL为前半段）

脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”；
干净页：内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”；
刷脏：将脏页落盘的过程，即为刷脏；

2）刷脏时机

Redo log文件写满：系统停止更新操作，向前推进并记录检查点，腾出redo log空间；
系统内存不足：当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘；
定时刷脏：在空闲时，定时刷脏；
正常关闭：正常关闭mysql时，需要将所有脏页flush到磁盘；

3）检查点的理解

所有的Redo Log文件可以看成是一个环形缓冲区，红色区域为当前可写入区域，绿色区域为待刷脏区域；
把checkpoint位置从CP推进到CP’需要将两个点之间的日志（浅绿色部分），对应的所有脏页都flush到磁盘上。
之后，从write pos到CP’之间就是可以再写入的redo log的区域。

4）缓冲池内存使用策略：缓冲池中的内存页有三种状态

还没有使用的；
使用了并且是干净页；
使用了并且是脏页；

InnoDB的策略是尽量使用内存，因此对于长时间运行的库来说，未被使用的页面很少。

5）数据页读取策略：当要读取的数据不在内存的时候，就需要到缓冲池中申请一个数据页。若没有未使用的数据页，则需要根据LRU算法淘汰

如果要淘汰的是一个干净页，就直接释放出来复用；
如果是脏页，就必须将脏页先刷到磁盘，变成干净页后才能复用；

6）InnoDB刷脏相关参数

innodb_io_capacity：磁盘的io能力，推荐设置成磁盘的IOPS；
innodb_max_dirty_pages_pct：脏页百分比，默认75%；引擎据此以及innodb_io_capacity控制刷脏的速率；
innodb_flush_neighbors：刷脏时，是否连带相邻的脏页一起刷新；1表示开启；

规范1：innodb_flush_neighbors参数可能导致刷脏时，刷新更多的脏页，从而使得查询语句执行更慢；建议使用SSD等IOPS较高的磁盘时，关闭该选项；

规范2：一个内存配置为128GB、innodb_io_capacity设置为20000的大规格实例，建议将redo log设置成4个1GB的文件；

测试磁盘IOPS：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M-numjobs=10 -runtime=10 -group_reporting -name=mytest

重要日志模块：binlog

binlog是归档日志，保证数据可恢复，实现主从复制。

binlog写入逻辑

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog file中，并清空binlog cache。在这个过程中，系统给binlog cache分配了一片内存，线程私有，参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

注：

一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入；
每个线程有自己binlog cache，但是共用同一份binlog file；

flush和fsync

1）binlog flush：是指把binlog file flush到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快；（掉电会丢数据）

2）binlog sync：执行fsync，将数据持久化到磁盘。一般情况下，我们认为fsync才占磁盘的IOPS；（掉电不丢数据）

3）sync_binlog配置：控制binlog sync的执行时机；

sync_binlog=0，每次提交事务都只flush，不执行fsync；
sync_binlog=1，每次提交事务都会执行fsync；
sync_binlog=N(N>1)的时候，表示每次提交事务都flush，但累积N次提交后才执行fsync；

注：在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能，但可能在掉电时丢失没有sync的binlog日志；

redo log和binlog的区别

1）redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用；

2）redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是执行语句的原始逻辑（即SQL语句），比如“给ID=2这一行的c字段加1 ”；

3）redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志；

4）redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。（建议设置成1，保证异常重启后数据不丢失）

5）binlog用于保证可恢复性。 sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

update语句执行流程

有了对这两个日志的概念性理解，我们再来看执行器和InnoDB引擎在执行这个简单的update语句时的内部流程。

执行器先找引擎取ID=2这一行。 ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中（此处内存是指InnoDB中的BuffPool），就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的binlog，并把binlog写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

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

你可能注意到了，最后三步看上去有点“绕”，将redo log的写入拆成了两个步骤： prepare和commit，这就是"两阶段提交"。

保证日志的一致性

两阶段提交

问：为什么必须有“两阶段提交”呢？

这是为了让redo log和binlog之间的逻辑一致。

由于redo log和binlog是两个独立的逻辑，如果不用两阶段提交，就无法保证两份日志的一致性；比如（仍然用前面的update语句来做例子，假设当前ID=2的行，字段c的值是0）：

先写redo log后写binlog，则redo log多数据。假设在redo log写完，binlog还没有写完，MySQL进程异常重启。redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。由于binlog没写完就crash了，之后备份，存起来的binlog里面就没有这条语句。此时，如果要用binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同；
先写binlog后写redo log，则binlog多数据。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同；

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。你可能会说，这个概率是不是很低，平时也没有什么动不动就需要恢复临时库的场景呀？

其实不是的，不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用binlog来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

Crash Recovery逻辑

还用前面的update语句为例进行说明，update语句的执行流程图如下所示，图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的。

问：在update语句执行过程中，如果在时刻A、时刻B发生崩溃，分别会造成什么后果？

答：

1）时刻A：写入redo log 处于prepare阶段之后、写binlog之前，发生了崩溃（crash）。由于此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚；

2）时刻B：也就是binlog写完，redo log还没commit前发生crash：

如果redo log里面的事务是完整的，也就是已经有了commit标识（说明binlog已写完且完整），则直接提交；
如果redo log里面的事务只有完整的prepare（此时可能存在binlog不完整的情况），则判断对应的事务binlog是否存在并完整：

- 如果binlog完整，则提交事务；
- 否则，回滚事务；

组提交

1）双1配置：MySQL的“双1”配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成 1；在双1配置下，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog；为了减少事务提交产生的IO，MySQL使用了组提交机制（group commit）；

未完...后续接着看PPT