Mysql 日志

教主陈
294 阅读5分钟

概述

我们知道mysql的InnoDB存储引擎和MyISAM相比有很多优势，所以我们默认都以InnoDB来讲解。我们都听过mysql是使用的（Write-Ahead Logging）WAL技术，先写日志，再写磁盘。我们今天就来看看为什么先写日志可以保证不丢失数据，以及可以提升mysql的性能呢？

日志类型

mysql中有以下几个常见的日志。

  • bin log: 二进制日志
    bin log是mysql中非常重要的日志，它是mysql的server层实现的，所有的存储引擎都可以使用，另外bin log是逻辑日志记录的是sql语句（根据使用的日志格式不同，记录的可能不是原始sql语句，这个我们后面再看），它是一种追加写的日志，不会覆盖之前的日志。所以他的作用用于归档数据，误操作恢复，主从同步等。

  • redo log：重做日志
    redo log 是InnoDB存储引擎提供的，记录的是物理日志，是循环写的，空间可能用完。用于崩溃恢复。 注意和bin log的区别，bin log是误删数据等恢复，redo log，是mysql进程突然崩溃或者主机掉电重启后的恢复。下图展示来一个由4个日志文件组成的redo log日志空间，可以想象成一个循环数组，write pos就是写入日志的下标，write pos 和check point之间的空间就是可以写入数据的空间

undo log: 回滚日志
undo log 也是InnoDB存储引擎提供的，每次进行写操作时记录。用于事务的回滚和多版本并发控制。 下图是undo log的示意图

数据更新流程

我们以这条sql 举例 mysql> update T set c=c+1 where ID=2;

解读下流程图

  1. 存储引擎取ID等于2这一行
  2. 判断数据页是否在内存中（mysql是以页为单位存储和读取的，InnoDB默认一页16k）
  3. 如果不在内存就从磁盘中读入到内存
  4. 将这行的c值加1
  5. 写入新行
  6. 将新行更新到内存（可以看到这时数据并没有真正写回到磁盘的数据页中）
  7. 写入redo log prepare阶段
  8. 写bin log 日志
  9. redo log 、bin log共同确认可以提交，进行事务提交

上面这个流程图涉及到两个我们经常听到的技术：WAL 和两阶段提交。 WAL(Write-Ahead Logging): 先写日志，上图可以看到当数据更新时，只是更新到内存，然后进行写redo log日志和bin log日志。并没有将数据写回数据页。这就是WAL技术。可以提高性能。 两阶段提交：可以看到redo log刚开始只写入prepare阶段，当bin log日志写入成功以后，redo log再改成commit状态，确认事务提交成功。确保数据不丢失。

这里回答两个问题

  1. 为什么mysql的WAL技术可以提高性能？
    答：1.WAL技术写redo log 和 bin log都是顺序写的，而如果写数据页肯定是随机写，所以WAL可以将随机写转成顺序写，所以会提高性能。 2.其实mysql作者更进一步优化，redo log 和bin log的写入是有组提交的，一次提交多个事务，所以能提高性能。 3.redo log、bin log可以设置，不一定每次必须提交，可以提高性能。（这个文章后面会说到）

  2. mysql（InnoDB）怎么保证数据不丢失？
    答：依赖于两阶段提交，我们知道mysql写数据，已经写到Redo log和bin log里了，当mysql异常重启后，会检查redo log，如果redo log里状态是commit就是事务提交成功，将数据写回数据页，如果redo log里是prepare状态，就查找对应的bin log里是否有该更新，有的话同样说明事务提交成功，将redo log中的数据页写回，但如果redo log里是prepare状态，bin log里没有对应的修改记录，则认为事务是不成功的。

上面我们知道了当保证redo log和bin log完整时，就可以保证数据不丢失，那么怎么才能保证redo log 和bin log完整呢？

保证redo log 不丢失

可以看到redo log也有缓冲区，并不需要立马写到日志文件中。
innodb_flush_log_at_trx_commit 设置redo log写入日志文件的时机。取值为：0 1 2
当值为1时，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。 （建议使用1）
当值为0时，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当mysql崩溃或者主机掉电时，会丢失1秒钟的数据。
当值为2时，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。和值为0的时候的区别是，设置为2更安全，设为2时，如果是mysql进程的崩溃，数据并不会丢，如果出于性能要求，不设为1，建议设为2.

保证bin log不丢失

可以看到bin log也有缓冲（binlog cache）并不一定需要立马写到日志文件中。
sync_binlog 设置bin log写入磁盘的时机。取值 0 1 N
sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync； 建议设为1
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。 同样当出于性能原因，不设为1时，建议设置100～1000的某个值N

参考资料\

avatar
文章
阅读
粉丝