4. 高级

4.1 日志

4.1.1 基本概念

MySQL日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中比较重要的就是二进制日志binlog（归档日志）、事务日志redo log（重做日志）和undo log（回滚日志）。

4.1.2 错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysql启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log。查看日志位置:

show variables like '%log_error%'

4.1.3 二进制日志-binlog

redo log是物理日志，记录的是“在某个数据页做了什么修改”，属于Innodb存储引擎。

而binlog日志是逻辑日志，记录内容是语句的原始逻辑，属于MySQL Server层。所有的存储引擎只要发生了数据更新，都会产生binlog日志。

binlog日志的作用

可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依赖binlog来同步数据，保证数据一致性。

binlog会记录所有涉及更新数据的逻辑规则，并且按顺序写。

记录格式

binlog日志有三种格式，可以通过binlog_format参数设置，有以下三种：

日志格式	含义
statement	基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。
row	基于行的日志记录，记录的是每一行的数据变更。(默认)
mixed	混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。

设置statement记录的内容是SQL语句原文，比如执行一条update T set update_time = now() where id = 1，记录内容如下：

同步数据时，会执行记录的SQL语句，但是有个问题update_time = now()这里会获取到当前系统问题，直接执行会导致与原库数据不一致。

为了解决这种问题，我们需要将binlog_format设置成row，记录的不再是简单的SQL语句了，还包含了操作的具体数据，记录内容如下：

row格式记录的内容看不到详细信息，通过mysqlbinlog工具解析出来。

update_time = now()变成了具体的时间，条件后面的@1、@2都是该行数据第1个~2个字段的原始值（假设这张表只有2个字段）。

设置成row带来的好处就是同步数据的一致性，通常情况都设置成row，这样可以为数据库的恢复与同步带来更好的可靠性。但是这种格式需要大量的容量来记录，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度。

所以又有了一种折中方案，设置为mixed，记录的内容是前两者的混合。

MySQL会判断这条SQL语句是否会引起数据不一致，如果是就用row格式，否则就用statement格式。

案例

首先我们输入命令，更改数据

use mockito;
-- 更新数据，让我们来看下binlog记录了什么日志
update user set name='joker-update' where id=7;

然后查看日志更改了啥？

mysqlbinlog DESKTOP-193BN4M-bin.000006 -v

接下来就可以看见了binlog写入的东西了

写入机制

binlog的写入时机为事务执行过程中，先把日志写到binlog cache，事务提交的时候再把binlog cache写到binlog文件中（实际先会写入page cache，然后再由fsync写入binlog文件）。

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

binlog日志刷盘流程如下：

• 上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化硬盘，所以速度比较快。
• 上图的fsync才是将数据库持久化到硬盘的操作。

write和fsync的时机可以由参数sync_binlog控制，可以配置成0、1、N(N>1)。

• 设置成0时：表示每次提交事务都只会write，由系统自行判断什么时候执行fsync。
• 设置成1时：表示每次提交事务都会执行fsync，就和redo log日志刷盘流程一样。
• 设置成N时：表示每次提交事务都会write，但是积累N个事务后才fsync。

设置为0时如下图：

从上图可知，sync_bilog = 0设置成0，只把日志写入page cache虽然性能得到了提高，但是事务提交了fsync的时候宕机了，可能造成binlog日志的丢失。

设置为2时如下图：

在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。

同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

两阶段提交

redo log（重做日志）让InnoDB存储引擎有了崩溃恢复的能力。

binlog（归档日志）保证了MySQL集群架构数据的一致性。

虽然它们都属于持久化的保证，但是侧重点不一样。

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog日志只有在提交事务的时候才会写入，所以它们写入的时机不一样。

比如有这样一个场景，假设有这么一条语句update T set c = 1 where id = 2（c原值为0），假如执行过程中写完redo log日志后，在写入binlog的时候发生了异常，会出现什么情况呢？

由于binlog日志没写完就异常，这个时候binlog日志里面没有对应的修改记录，之后使用binlog同步的数据的时候就会少这一次的更新，这一行数据c = 0，而原库使用redo log日志恢复，这一行数据c = 1，最终数据不一致。如下图：

为了解决两份日志之间的逻辑不一致的问题，InnoDB存储引擎使用两阶段提交方案。

将redo log日志的写入拆分成两个步骤prepare和commit，如下图：

使用两阶段提交后，写入binlog时发生异常也没关系，因为MySQL根据redo log日志恢复数据时，发现redo log日志处于prepare阶段，并且没有对应binlog日志（根据事务id对应），所以就会回滚事务。

再想一个场景，redo lgo设置commit阶段发生异常，事务会不会回滚呢？

并不会回滚事务，虽然redo log是处于prepare阶段，但是存在对应的事务binlog日志，所以MySQL认为是完整的，所以不会回滚事务。

日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志:

指令	含义
reset master	删除全部 binlog 日志，删除之后，日志编号，将从binlog.000001重新开始
purge master logs to 'binlog.******	删除 ****** 编号之前的所有日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss'	删除日志为"yyyy-mm-dd hh24:mi:ss"之前产生的所有日志

在windows中运行

purge master logs to 'DESKTOP-193BN4M-bin.000005';

005之前的日志都没有了

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。

show variables like '%binlog_expire_logs_seconds%';

4.1.4 查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置

show variables like '%general%';

怎么开启呢？

如果是windows呢？？

4.1.5 慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为 10秒，最小为0，精度可以到微秒。

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为 log_queries_not_using_indexes，如下所述。

4.1.6 redo log

出现原因

根据事务的持久性，对于一个已经提交的事务，在事务提交之后即使系统发生了崩溃，这个事务对数据库所在的更改也不能丢失。

在真正访问页面之前，需要先把磁盘中的页加载到内存中的Buffer Pool中，之后才可以访问。如果我们只在内存中的Buffer Pool中修改了页面，假设在事务提交之后突然发生了某个故障，导致内存中的数据都失效了，就无法保证事务的持久性了。

那么如何保证事务的持久性呢？

一个简单的做法就是在事务提交完成之前，把该事务修改的所有页面都刷新到磁盘，但是这个粗暴的做法存在下面这些问题：

(1) 刷新一个完整的数据页太浪费时间了，有时我们仅仅修改了某个页面中的一个字节，但是由于InnoDB是以页为单位进行磁盘IO的，也就是事务提交时不得不将一个完整的页面刷新到磁盘，太浪费了。 (2) 随机IO刷新的比较慢，一个事务可能包含很多语句，即使是一条语句也可能修改许多页面，而且这些页面可能并不相邻。这就意味着在将某个事务修改的Buffer Pool中的页面刷新到磁盘时，需要进行很多的随机IO。

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。

比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

redo log 的好处

相较于在事务提交时将所有修改过的页刷新到磁盘中，只将该事务执行过程中产生的redo日志刷次年到磁盘，有下面的好处：

(1) redo日志降低了刷盘频率。

(2) redo日志占用的空间非常小。

(3) redo日志是顺序写入磁盘的。在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是顺序IO。

InnoDB存储引擎的事务采用了WAL技术（Write-Ahead Logging）,这种技术就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log。当发生宕机且数据未刷新到磁盘的时候，可以通过redo log恢复过来，保证事务的持久性。

redo log跟bin log的区别：redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做了10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次写入bin log文件中。

redo log 的组成

• 重做日志缓冲区（redo log buffer）：保存在内存中，是易丢失的。InnoDB为了解决磁盘速度过慢的问题而引入了Buffer Pool，同理，写入redo log日志时，也不能直接写到磁盘中，实际上在服务器启动时就向操作系统申请了一片称为redo log buffer的连续存储空间。
• 重做日志文件 (redo log file) ：保存在硬盘中，是持久的。redo日志总存放在内存redo log buffer中待着也不是个办法，在一些情况下他们会被刷新到磁盘中。mysql的数据目录下默认有名为ib_logfile0和ib_logfile1两个文件，log buffer中的日志在默认情况下就是刷新到这两个磁盘文件中，即redo log file。

刷盘时机

理想情况下，事务一提交就会进行刷盘操作，但是实际上是刷盘的时机是根据策略来决定的。

InnoDB存储引擎为redo log的刷盘策略提供了innodb_flush_log_at_trx_commit参数，它支持三种策略：

• 0：设置为0的时候，每次提交事务时不刷盘。
• 1：设置为1的时候，每次提交事务时刷盘。
• 2：设置为2的时候，每次提交事务时都只把redo log buffer写入page cache。

innodb_flush_log_at_trx_commit参数默认为1，当事务提交的时候会调用fsync对redo log进行刷盘，将redo log buffer写入redo log文件中。

可以调用一下命令查看，参数设置

show variables like 'innodb_flush_log_at_trx_commit';

另外，Innodb存储引擎有一个后台线程，每隔1秒，就会把会redo log buffer中的内容写入到文件系统缓存page cache，然后调用fsync刷盘。

如上图，所以说一个没有提交事务的redo log记录，也会被刷盘。

下面是各种刷盘策略的流程图。

innodb_flush_log_at_trx_commit = 0

如上图，如果宕机了或者MySQL挂了可能造成1秒内的数据丢失。

innodb_flush_log_at_trx_commit = 1

如上图，只要事务提交成功，redo log记录就一定在磁盘里，不会有任务数据丢失。

如果执行事务的时候MySQL挂了或者宕机了，这部分日志丢失了，但是因为事务没有提交，所以日志丢了也不会有损失。

innodb_flush_log_at_trx_commit = 2

如上图，当事务提交成功时，redo log buffer日志会被写入page cache，然后后台线程会刷盘写入redo log，由于后台线程是1秒执行一次所以宕机或者MySQL挂了可能造成1秒内的数据丢失。

日志文件组

硬盘上存储的redo log日志文件不止一个，而是一个日志文件组的形式出现的，每个的redo log文件大小都是一样的。它采用的是环形数组形式，从头开始写，写到末尾回到头循环写，如下图所示：

在日志文件组中有两个重要的属性，分别是write pos、checkpoint

• write pos：是当前记录的位置，一边写一边后移。
• checkpoint：是当前要擦除的位置，也是后台推移。

更新时机

每次刷盘redo log记录到日志文件组中，write log位置就会后移更新。

每次MySQL加载日志文件组恢复数据时，会清空加载过的redo log，并把checkpoint后移更新。

write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

如果 write pos追上checkpoint，表示日志文件组满了，这时候不能再写入新的redo log记录，MySQL得停下来，清空一些记录，把checkpoint推荐一下。

\

总结

redo log的作用和它的刷盘时机、存储形式。

可以思考一个问题：只要每次把修改后的数据页直接刷盘不就好了，为什么还要用redo log刷盘？不都是刷盘吗？有什么区别？

实际上，数据页大小是16KB，刷盘比较耗时，可能就修改了数据页的几byte数据，没有必要把整页的数据刷盘。而且数据页刷盘都是随机写，因为一个数据页对应的位置可能是在硬盘文件的随机位置，所以性能很差。

如果是写redo log，一行记录就占了几十byte，只要包含了表空间号、数据页号、磁盘文件偏移量、修改值，再加上是顺序写，所以刷盘效率很高。

所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

4.1.7 undo log

想要保证事务的原子性，就需要在发生异常时，对已经执行的操作进行回滚，在MySQL中恢复机制是通过undo log（回滚日志）实现的，所有事务进行的修改都会先被记录到这个回滚日志，然后再执行其他相关的操作。如果执行过程中遇到异常的话，我们直接利用回滚日志中的信息将数据回滚到修改之前的样子。并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

另外，MVCC的实现依赖：隐藏字段、Read View、undo log。在底层实现中，InnoDB通过数据行的DB_TRX_ID和Read View来判断数据的可见性，如不可见，则通过数据行DB_ROLL_PTR找到undo log中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事物里，用户只能看到该事务创建Read View之前已经提交的修改和该事务本身做的修改。

4.2 主从复制

4.3 分库分表

4.4 读写分离

参考资料

MySQL三大日志(binlog,redolog,undolog)详解_mysql_晴天的空间-华为云开发者联盟 (csdn.net)

mysql中的undo log、redo log 、binlog_mysql的undo和redo和binlog_GeGe&YoYo的博客-CSDN博客