mysql 优化学习---mysql几个重要的日志

一、前言

  MySQL中有以下日志文件，分别是：

  1、redo log（重做日志，物理日志）：属于 InnoDB引擎，redo log记载的是物理变化（xxxx页做了xxx修改）,因为redo log是顺序IO，所以写入的速度很快。作用是为持久化，写完内存挂了恢复数据。

  2、bin log（归档日志，逻辑日志）：Server层日志。bin log记载的是update/delete/insert这样的SQL语句。

     作用 ：   1)复制数据：主从复制主要是根据这个日志来.。2）恢复数据：数据库的数据被干掉了，我们可以通过binlog来对数据进行恢复。

  3、undo log：记录事务变更前数据，保证数据库原子性。undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。

     作用 1)回滚：在事务开始、记录数据库变更前的数据，便于回滚。 2) MVCC：多版本控制

  4、慢查询日志（slow query log）：记录查询时间大于设置阈值的sql语句，方便后继优化。

  5、一般查询日志（general log）：记录建立的客户端连接和执行的语句。

  6、中继日志（relay log）

  7、错误日志（errorlog）：记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。

  其中redo log和undo log与事务操作息息相关，bin log也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。

二、redo log（重做日志，物理日志）

    属于 InnoDB引擎，redo log记载的是物理变化（xxxx页做了xxx修改）,因为redo log是顺序IO，所以写入的速度很快。作用是为持久化，写完内存挂了恢复数据。

    在 MySQL 里有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高

     其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称 是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

     当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

2.1、什么是redo log

假设我们有一条sql语句：

update user_table set name='java3y' where id = '3'

MySQL执行这条SQL语句，肯定是先把id=3的这条记录查出来，然后将name字段给改掉。这没问题吧？

实际上Mysql的基本存储结构是页(记录都存在页里边，一页大小16k)，所以MySQL是先把这条记录所在的页找到，然后把该页加载到内存中，将对应记录进行修改。

现在就可能存在一个问题：如果在内存中把数据改了，还没来得及落磁盘，而此时的数据库挂了怎么办？显然这次更改就丢了。

如果每个请求都需要将数据立马落磁盘之后，那速度会很慢，MySQL可能也顶不住。所以MySQL是怎么做的呢？

MySQL引入了redo log，内存写完了，然后会写一份redo log，这份redo log记载着这次在某个页上做了什么修改。

其实写redo log的时候，也会有buffer，是先写buffer，再真正落到磁盘中的。至于从buffer什么时候落磁盘，会有配置供我们配置。

写redo log也是需要写磁盘的，但它的好处就是顺序IO（我们都知道顺序IO比随机IO快非常多）。

所以，redo log的存在为了：当我们修改的时候，写完内存了，但数据还没真正写到磁盘的时候。此时我们的数据库挂了，我们可以根据redo log来对数据进行恢复。因为redo log是顺序IO，所以写入的速度很快，并且redo log记载的是物理变化（xxxx页做了xxx修改），文件的体积很小，恢复速度很快。

什么时候产生：

　　事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

什么时候释放：

　　当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

对应的物理文件：

       指定Redo log 记录在{datadir}/ib_logfile1&ib_logfile2 可通过innodb_log_group_home_dir 配置指定 目录存储

　　默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1&ib_logfile2

　　innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。

　　innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2

关于文件的大小和数量，由以下两个参数配置：

　　innodb_log_file_size 重做日志文件的大小。

　　innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

以Redo log的写入是日志文件循环写入的

1.     指定Redo log日志文件组中的数量 innodb_log_files_in_group 默认为2
2.     指定Redo log每一个日志文件最大存储量innodb_log_file_size 默认48M
3.     指定Redo log在cache/buffer中的buffer池大小innodb_log_buffer_size 默认16M

Redo buffer 持久化Redo log的策略， Innodb_flush_log_at_trx_commit：

1.      取值 0 每秒提交 Redo buffer --> Redo log OS cache -->flush cache to disk[可能丢失一秒内 的事务数据]
2.     取值 1 默认值，每次事务提交执行Redo buffer --> Redo log OS cache -->flush cache to disk [最安全，性能最差的方式]
3.      取值 2 每次事务提交执行Redo buffer --> Redo log OS cache 再每一秒执行 ->flush cache to disk操作

2.2、原理

    InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文 件的大小是 1GB，那么这块日志总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下图面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件 开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录 更新到数据文件。

write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如 果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下 来先擦掉一些记录，把 checkpoint 推进一下。

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

3.3、数据更新处理

三、bin log（归档日志，逻辑日志）

3.1、什么是binlog

   binlog其实在日常的开发中是听得很多的，因为很多时候数据的更新就依赖着binlog。

         举个很简单的例子：我们的数据是保存在数据库里边的嘛，现在我们对某个商品的某个字段的内容改了（数据库变更），而用户检索的出来数据是走搜索引擎的。为了让用户能搜到最新的数据，我们需要把引擎的数据也改掉。

         一句话：数据库的变更，搜索引擎的数据也需要变更。

        于是，我们就会监听binlog的变更，如果binlog有变更了，那我们就需要将变更写到对应的数据源。

binlog记录了什么？

        binlog记录了数据库表结构和表数据变更，比如update/delete/insert/truncate/create。它不会记录select（因为这没有对表没有进行变更）     

binlog长什么样？

binlog我们可以简单理解为：存储着每条变更的SQL语句（当然从下面的图看来看，不止SQL，还有XID「事务Id」等等）

3.2、作用：

主要有两个作用：复制和恢复数据

1. MySQL在公司使用的时候往往都是一主多从结构的，从服务器需要与主服务器的数据保持一致，这就是通过binlog来实现
2. 数据库的数据被干掉了，我们可以通过binlog来对数据进行恢复。

因为binlog记录了数据库表的变更，所以我们可以用binlog进行复制（主从复制)和恢复数据。

3.3、什么时候产生：

　　事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。

　　这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

　　因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。

　　这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

3.4、对应的物理文件：

　　配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。

　　对于每个binlog日志文件，通过一个统一的index文件来组织。

四、binlog和redo log 

看到这里，你可能会想：binlog和redo log 这俩也太像了吧，都是用作”恢复“的。

其实他俩除了"恢复"这块是相似的，很多都不一样，下面看我列一下。

4.1 存储的内容

  binlog记载的是update/delete/insert这样的SQL语句，而redo log记载的是物理修改的内容（xxxx页修改了xxx）。

     所以在搜索资料的时候会有这样的说法：redo log 记录的是数据的物理变化，binlog 记录的是数据的逻辑变化。

4.2、功能   

redo log的作用是为持久化而生的。写完内存，如果数据库挂了，那我们可以通过redo log来恢复内存还没来得及刷到磁盘的数据，将redo log加载到内存里边，那内存就能恢复到挂掉之前的数据了。

binlog的作用是复制和恢复而生的。

• 主从服务器需要保持数据的一致性，通过binlog来同步数据。

• 如果整个数据库的数据都被删除了，binlog存储着所有的数据变更情况，那么可以通过binlog来对数据进行恢复。

又看到这里，你会想：”如果整个数据库的数据都被删除了，那我可以用redo log的记录来恢复吗？“不能

因为功能的不同，redo log 存储的是物理数据的变更，如果我们内存的数据已经刷到了磁盘了，那redo log的数据就无效了。所以redo log不会存储着历史所有数据的变更，文件的内容会被覆盖的。

4.3、binlog和redo log 写入的细节

redo log是MySQL的InnoDB引擎所产生的。

binlog无论MySQL用什么引擎，都会有的。

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

上面也提到，在修改的数据的时候，binlog会记载着变更的类容，redo log也会记载着变更的内容。（只不过一个存储的是物理变化，一个存储的是逻辑变化）。那他们的写入顺序是什么样的呢？

redo log事务开始的时候，就开始记录每次的变更信息，而binlog是在事务提交的时候才记录。

于是新有的问题又出现了：我写其中的某一个log，失败了，那会怎么办？现在我们的前提是先写redo log，再写binlog，我们来看看：

• 如果写redo log失败了，那我们就认为这次事务有问题，回滚，不再写binlog。

• 如果写redo log成功了，写binlog，写binlog写一半了，但失败了怎么办？我们还是会对这次的事务回滚，将无效的binlog给删除（因为binlog会影响从库的数据，所以需要做删除操作）

• 如果写redo log和binlog都成功了，那这次算是事务才会真正成功。

简单来说：MySQL需要保证redo log和binlog的数据是一致的，如果不一致，那就乱套了。

• 如果redo log写失败了，而binlog写成功了。那假设内存的数据还没来得及落磁盘，机器就挂掉了。那主从服务器的数据就不一致了。（从服务器通过binlog得到最新的数据，而主服务器由于redo log没有记载，没法恢复数据）

• 如果redo log写成功了，而binlog写失败了。那从服务器就拿不到最新的数据了。

4.4、两阶段提交

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

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

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

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

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

过程：

• 阶段1：InnoDBredo log 写盘，InnoDB 事务进入 prepare 状态

• 阶段2：binlog 写盘，InooDB 事务进入 commit 状态

• 每个事务binlog的末尾，会记录一个 XID event，标志着事务是否提交成功，也就是说，恢复过程中，binlog 最后一个 XID event 之后的内容都应该被 purge。

五、undo log

Undo Log 是什么：

       undo意为取消，以撤销操作为目的，返回指定某个状态的操作 undo log指事务开始之前，在操作任何数据之前,首先将需操作的数据备份到一个地方 (Undo Log)

 UndoLog是为了实现事务的原子性而出现的产物

     Undo Log实现事务原子性： 事务处理过程中如果出现了错误或者用户执行了 ROLLBACK语句,Mysql可以利用Undo Log中的备份 将数据恢复到事务开始之前的状态

    UndoLog在Mysql innodb存储引擎中用来实现多版本并发控制

    Undo log实现多版本并发控制： 事务未提交之前，Undo保存了未提交之前的版本数据，Undo 中的数据可作为数据旧版本快照供 其他并发事务进行快照读。

下面是其执行过程

 

在数据修改的时候，不仅记录了redo log，还记录undo log，如果因为某些原因导致事务失败或回滚了，可以用undo log进行回滚。

undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。

这也应该容易理解，毕竟回滚嘛，跟需要修改的操作相反就好，这样就能达到回滚的目的。因为支持回滚操作，所以我们就能保证：“一个事务包含多个操作，这些操作要么全部执行，要么全都不执行”。【原子性】

因为undo log存储着修改之前的数据，相当于一个前版本，MVCC实现的是读写不阻塞，读的时候只要返回前一个版本的数据就行了。

六、慢查询日志 

    慢日志记录执行时间过长和没有使用索引的查询语句，报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句。
　　1. 查看慢查询时间： 
　　show variables like “long_query_time”;默认10s

　　2. 查看慢查询配置情况： 
　　show status like “%slow_queries%”;

　　3. 查看慢查询日志路径： 
　　show variables like “%slow%”;

　　4. 开启慢日志

　　查看已经开启：

参考资料：https://mp.weixin.qq.com/s/Lx4TNPLQzYaknR7D3gmOmQ

     丁奇  MySQL实战45讲