MySQL数据库及InnoDB存储引擎的日志文件

ゞ浴缸里的玫瑰 2023-03-01 13:41 85阅读 0赞

船头上迎面而来的海风特别有劲道但却总有种苦咸的味道，他睁着通红的眼睛，紧绷着嘴。尽管眼前一望无垠的海面平静如清晨的托雷维耶哈盐湖湖面，但他的心仍然翻涌着昨晚暴风雨中的激浪。这是离开陆地，在海上航行这么久以来最可怕的一晚。

船帆在暴风中被撕开一道口子，桅杆眼看着就要被折断，他赶紧指示水手长去抢救。巨浪打在他身上，如果不是大副及时的帮助，他也许已经被冲得撞在舱门上粉身碎骨。恐怖吗？恐怖，这也许是他在海上离死亡最近的一次。但黄金、香料、珠宝和宝石的影子在他脑中浮现，对财富的欲望战胜了恐惧。也许是幸运女神的眷顾，那夜海上的暴风雨没有击垮他的船队。

思绪再次回来了眼前，他凝视着船头激起的浪花在明媚的阳光的照耀下如金子般闪耀，似乎散发着财宝独有的色彩。“哥伦布船长，前面有陆地！”，在瞭望台上的水手长对他喊到。迎面而来的海风似乎没有了苦咸的味道，通红的眼睛闪着光芒，哥伦布紧绷着的嘴角也渐渐上扬。

哥伦布发现美洲新大陆是大航海时代的一个重要标志。而后世人要研究当时航海先驱们的惊险旅程，首先需要看的就是航海日志。像哥伦布的航海日志就让后人得知当时他的心路历程。

**除了在航海领域，日志在其它领域的重要程度也是非常高的。如在MySQL领域，数据库的日志与InnoDB存储引擎的日志对深入了解数据的运作起到非常关键的作用。下面就让我用思维导图的方式带你去一趟关于MySQL数据库及InnoDB存储引擎日志文件的旅程。**

![format_png][]

**我们首先来看一下MySQL数据库的日志文件。**

# 1，MySQL数据库的日志文件 #

![format_png 1][]

MySQL数据库的日志文件记录了影响 MySQL 数据库的各种类型的活动。MySQL数据库的日志文件可以分为4大类，分别是：

*  错误日志（error log）
 *  慢查询日志（slow query log）
 *  查询日志（log）
 *  二进制日志（binlog）

![format_png 2][]

## 1.1 错误日志（error log） ##

错误日志文件会对整个MySQL启动、运行以及关闭的过程进行记录，除了记录错误信息以外，还会记录一些告警信息和正确的信息。当在启动MySQL的时候如果遇到错误，首先可以查看错误日志。

![format_png 3][]

## 1.2 慢查询日志（slow query log） ##

慢查询日志是可以帮助定位可能存在问题的 SQL 语句，以便在 SQL 语句层面进行优化。可以通过参数slow\_query\_log来设置是否记录慢查询日志。

MySQL 会在运行时将超过一定时间阈值的所有 SQL 语句都记录到慢查询日志中。如果SQL的运行时间刚好等于阈值的话就不会记录。慢查询只记录运行花时间大于阈值的SQL。通过参数 long\_query\_time 来是指定慢SQL的阈值。

在生产环境中，SQL如果在运行时没有使用索引的话，可能会出现运行非常慢的情况。我们可以通过设置参数 log\_queries\_not\_using\_indexes 来让MySQL数据库将没有使用索引的SQL记录到慢查询日志文件中，便于查询。没有使用索引的SQL虽然可以帮助我们找出那些可能存在问题的SQL，但如果非常频繁地记录到慢查询日志文件的话，文件大小会过快增长，可能会导致生产问题，因此我们需要权衡记录的频率。此时我们可以使用参数 log\_throttle\_queries\_not\_using\_indexes。这个参数用于设置每分钟允许记录到慢查询日志的未使用索引的 SQL 次数，默认为0，即不限制。

慢查询日志既可以记录到文件中，也可以记录到表里面。参数log\_output可以指定慢查询的输出格式，默认为FILE（记录到文件中），可以将它设置为TABLE（记录到表中）。当设置为TABLE后可以在mysql.slow\_log表中查看慢查询日志。

MySQL 中慢查询日志对于SQL语句的捕获是通过运行时间来进行的，不过在InnoSQL版本中有所增强。InnoSQL版本中增加了逻辑读取（logical reads）和物理读取（physical reads）。逻辑读取是指从磁盘中进行 IO 读取的次数。物理读取是指所有的读取次数，包括对磁盘、缓冲池等地方读取操作。通过参数 slow\_query\_type可以指定slow log的方式（是InnoSQL 为兼容MySQL原有的运行方式二增加的），值有：

*  0，表示不将SQL语句记录为slow log中
 *  1，表示根据运行时间将SQL语句记录到slow log中
 *  2，表示根据逻辑IO次数将SQL语句记录到slow log
 *  3，表示根据运行时间及逻辑IO次数将SQL语句记录到 slow log里面

![format_png 4][]

## 1.3 查询日志（log） ##

查询日志会记录下MySQL数据库受到的所有请求的信息，不管请求是否有被正确执行。查询日志默认文件名为：主机名.log。通过参数 general\_log 可以查看查询日志是否开启。通过参数general\_log\_file可以查看文件的路径。

![format_png 5][]

## 1.4 二进制日志（binlog） ##

二进制日志（下称binlog）记录了所有对MySQL数据库执行更多的操作，包含了更改的时间等信息。但binlog不会记录SELECT和SHOW等对数据本身没有修改的操作，像变更记录数为0的UPDATE SQL之类的也不会记录。

binlog可以起到恢复（recovery）、复制（replication）、审计（audit）等强大作用，但binlog也会带来一定的性能损失，开启binlog后MySQL数据库的性能可能会有1%左右的下降。不过这1%的性能损失对于binlog所提供的强大作用来看，是可以不用担心的。binlog并不是直接写入到二进制日志文件中的，它会先写入binlog缓存中。对于尚未提交的binlog会先被记录到缓存中，在提交的时候再从缓存中写入二进制日志文件。通过参数binlog\_cache\_size（默认为32K）可以设置binlog缓存的大小。binlog缓存的大小需要设置在一个合理值上，如果太大的话会导致分配的空间用不完，造成资源的浪费。如果太小的话当一个事务的记录大于binlog缓存大小时，会将日志写入到一个临时文件中。

对于binlog缓存中的数据写入到磁盘中策略，我们可以通过参数sync\_binlog进行设置。sync\_binlog的默认值为0，即表示MySQL不控制缓存的刷新，刷新的策略交由文件系统来控制。这样设置的性能最好，但一旦出现宕机缓存中未来得及同步的数据会丢失。sync\_binlog被设置为1时，表示每次都采用同步写磁盘的方式来写入二进制日志文件。这样做的好处是安全性高，但同时会带来性能的下降，并且存在事务无法回滚的情况（关于“存在事务无法回滚的情况”这一点的详情，大家可以查看思维导图，这里就不展开讨论）。

binlog的格式有STATEMENT、ROW、MIXED三种，可以通过参数binlog\_format进行设置。三种值可以实现不同的功能，但同时也带来不同的影响。

*  设置为STATEMENT时记录的是日志的逻辑SQL语句。这种方式可能会在主从数据库中出现数据不一致的情况，例如SQL仲使用了函数（如uuid）、触发器等，或者设置了READ COMMOTTED级别，会出现更新丢失的情况。
 *  设置为ROW时记录的是表中行的更改情况，不存在STATEMENT会有数据不一致的情况。但是二进制日志文件会变得更大，需要更多磁盘空间，主从复制时也会增加网络开销。
 *  设置为MIXED时默认按STATEMENT格式保存二进制日志文件，但在下面这些情况下会使用ROW格式：
    
     *  表的存储引擎是NDB；
     *  使用了UUID()，USER()，CURRENT\_USER()，FOUND\_ROWS()，ROW\_COUNT()等具有不确定性的函数；
     *  使用了INSERT DELAY语句；
     *  使用了用户定义的函数；使用了临时表（temporary table）；

![format_png 6][]

# 2，InnoDB存储引擎的日志文件 #

![format_png 7][]

## 2.1 重做日志文件 ##

InnoDB存储引擎的重做日志用于记录InnoDB存储引擎的事务日志，在存储介质或实例出现问题时，对恢复数据有非常大的作用。

## 2.1.1 重做日志文件组 ##

每个InnoDB存储引擎至少有1个重做日志文件组，可以设置多个镜像日志组（mirrored log groups），不同文件组可以存放在不同的磁盘上从而提高重做日志的高可用性。

![format_png 8][]

## 2.1.2，重做日志文件 ##

重做日志文件位于InnoDB存储引擎的数据目录下，至少有两个，默认名称为ib\_logfile0与ib\_logfile1。同一个日志组中的重做日志文件的大小相同，采用循环写入的方式。引擎先写ib\_logfile0，当ib\_logfile0写满后会切换到写ib\_logfile1。当ib\_logfile1也写满后，就又会重新切换到写ib\_logfile0。

![format_png 9][]

## 2.1.3 重做日志的capacity变量 ##

重做日志的capacity变量表示最后的检查点不能超过capacity的值，一旦超过就必须将缓冲池（innodb buffer pool）里面脏页列表（flush list）中的一部分脏页刷新会磁盘中，这样会导致用户线程出现阻塞。

![format_png 10][]

## 2.1.4 重做日志与二进制日志的区别 ##

重做日志与二进制日志看着很相似，但两者其实还是有不小的差别的。

*  记录的日志记录的区别。InnoDB存储引擎重做日志只记录与InnoDB存储引擎本身有关的事务日志。二进制日志会记录所有域MySQL数据库有关的日志记录（包括其他存储引擎的日志）
 *  记录的内容区别。InnoDB存储引擎的重做日志文件记录的是关于每个页的更改的物理情况。二级制日志文件记录的是关于个事务的具体操作内容，也就是逻辑情况
 *  写入时间的区别。重做日志文件再事务进行的过程中会被不断写入重做日志条目（redo entry）。二进制日志文件只在事务（不论这个事务有多大）提交前写入磁盘。

![format_png 11][]

## 2.1.5 重做日志条目（redo entry） ##

重做日志条目分为4部分，格式为：redo\_log\_type|space|page\_no|redo\_log\_body。

*  redo\_log\_type 表示重做日志类型，占1个字节。
 *  space 表示表空间的ID，因为进行了压缩，所以占用的空间可能会小于4个字节。
 *  page\_no 表示页的偏移量，进行过压缩。
 *  redo\_log\_body 表示每个重做日志的数据部分。在进行恢复时，需要调用对应的函数才能成功解析。

![format_png 12][]

## 2.1.6 重做日志缓冲（redo log buffer） ##

重做日志会先写入到重做日志缓冲中，然后再根据一定的条件顺序来写入到重做日志文件中。重做日志缓冲向磁盘写入时是按照一个扇区的大小（512个字节）进行写入的。扇区是写入的最小单位，所以可以保证写入操作是一定会成功的，并且重做日志的写入过程不需要有doublewrite。

重做日志从重做日志缓冲写入到磁盘中是按照一定条件进行的，会根据参数innodb\_flush\_log\_at\_trx\_commit配置的值进行控制。

*  值为0，表示事务被提交是不将事务的重做日志写到磁盘的重做日志文件中，而是等待主线程的每秒刷新。无法保证在数据库，或操作系统，甚至服务器出现宕机时可以通过重做日志文件进行恢复，有可能丢失部分事务
 *  值为1，在执行commit时将重做日志缓冲同步写入磁盘中（会有fsync调用）。可以保证即使宕机也能从重做日志中恢复已提交的事务
 *  值为2，重做日志会采用一步的方式写到磁盘，即利用文件系统的缓存。可保证在数据库宕机而操作系统或服务器正常时从重做日志中恢复数据（因为事务日志保存在文件系统的缓存里），但操作系统或服务器宕机时数据还是会丢失

![format_png 13][]

--------------------

参考资料：《mysql技术内幕 innodb存储引擎》

--------------------

> **想要获得完整版思维导图的朋友可以按如下方式操作：关注微信搜索公众号“IT小皇子”，或者扫描以下二维码直接关注，然后留言“MYSQL3”，即可获得完整版的思维导图。**

![format_png 14][]

**by IT小皇子**

[format_png]: /images/20230208/1573574a141943468e5243021375b61f.png
[format_png 1]: /images/20230208/f9af43eda942461eb89e6449b78ded01.png
[format_png 2]: /images/20230208/73a6b6a0f706491abe62e21e9500ac7f.png
[format_png 3]: /images/20230208/fcaa3a8129514d0abe54f6f625bcc299.png
[format_png 4]: /images/20230208/1b5ba20c925a431083f4f398490c4e8e.png
[format_png 5]: /images/20230208/32b03bd7736f4bae8f2b68b779f6336a.png
[format_png 6]: /images/20230208/559969b22f0443808942f036ff67b98c.png
[format_png 7]: /images/20230208/9735e2fa7ef840138df522286632bfeb.png
[format_png 8]: /images/20230208/298cbf2fe97d4ca5952ee0bc9f6587e0.png
[format_png 9]: /images/20230208/153c155973a64448b2d2e161f77c8b59.png
[format_png 10]: /images/20230208/b90fdc7237334fe28e4616d9330e1b2a.png
[format_png 11]: /images/20230208/0b711f7d865c427984ea3f0f6f64c134.png
[format_png 12]: /images/20230208/e4b19a4674824bdcaeb4d52995b8aa5b.png
[format_png 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9ub3RlLnlvdWRhby5jb20veXdzL3B1YmxpYy9yZXNvdXJjZS9hZGJlZjQxNTM3ZGU4YzUyNzNhZGY5NDEzOTU3MzU1OC94bWxub3RlLzc4OUZGQjA0NTg2MjQxRjQ5RjNGNDQyOUYwNUI4MTlDLzkxNzcw?x-oss-process=image/format,png
[format_png 14]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9ub3RlLnlvdWRhby5jb20veXdzL3B1YmxpYy9yZXNvdXJjZS9hZGJlZjQxNTM3ZGU4YzUyNzNhZGY5NDEzOTU3MzU1OC94bWxub3RlLzY2QjE5OUU5MDRGMjQ0QTZBQjczOEY4NzQwRjI4MjlDLzkxNzUw?x-oss-process=image/format,png