MySQL面试题-日志

今天药忘吃喽~ 2023-09-27 15:28 73阅读 0赞

**目录**

1.MySQL 中常见的日志有哪些？

2.慢查询日志有什么用？

3.binlog 主要记录了什么？

4.Mysql的binlog有几种录入格式？分别有什么区别？

5.redo log 如何保证事务的持久性？

6.页修改之后为什么不直接刷盘呢？

7.binlog 和 redolog 有什么区别？

8.怎样让数据库恢复到半个月内任意一秒的状态？

9.redo log和binglog如何保证两份日志之间的逻辑一致

10.undo log 如何保证事务的原子性？

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

## 1.MySQL 中常见的日志有哪些？ ##

1.  redo log：重做日志，用于恢复数据，保证 MySQL 异常宕机后数据的完整性，只记录物理日志（日志记录了对数据页的修改，而不是逻辑修改），循环写，因为不需要回滚，所以可以覆盖写。
2.  undo log：撤销日志，用于回滚事务，可以保证事务的原子性、一致性和隔离性。记录逻辑日志，循环写，记录事务操作之前的值，便于回滚事务。
3.  binlog：二进制日志，记录所有修改数据库的操作，包括增、删、改等操作，用于数据恢复、数据复制等场景。
4.  error log：错误日志，记录 MySQL 引擎在运行过程中出现的错误、警告等信息，用于排查问题。
5.  slow query log：慢查询日志，记录执行时间超过阈值的 SQL 语句，用于性能调优、查询优化等场景。
6.  general log：通用日志，记录所有客户端的查询和状态变更信息，包括查询、连接、断开等操作，通常用于诊断问题和安全审计。
7.  relay log：中继日志，用于 MySQL 主从复制中的数据传输，从 MySQL 主节点复制数据到从节点。

## 2.慢查询日志有什么用？ ##

慢查询日志是 MySQL 中用于记录执行时间超过阈值的 SQL 语句的日志，通常用于分析性能问题。它可以记录每条执行时间超过指定阈值的 SQL 语句，并记录下执行时间、访问的表、使用的索引以及执行 SQL 的用户等信息，从而帮助开发人员找到慢查询的原因和优化方案。

慢查询日志可以帮助开发人员识别哪些 SQL 语句执行时间长，从而可以进行针对性的优化。优化的方法包括但不限于优化查询语句的写法、增加索引、分离大表等。通过分析慢查询日志可以让开发人员更加深入地了解系统的瓶颈和性能问题，从而制定更好的优化策略。

慢查询日志的开启和关闭可以通过在 MySQL 配置文件中设置参数 `slow_query_log` 和 `long_query_time` 来实现。其中，`slow_query_log` 参数用于开启或关闭慢查询日志功能，`long_query_time` 参数用于设置执行时间超过多少秒的 SQL 语句会被记录到慢查询日志中。

## 3.binlog 主要记录了什么？ ##

binlog（二进制日志）是 MySQL 的一种日志文件，它记录了所有对数据的修改操作，包括数据库的增删改等操作。它是 MySQL 的一个重要特性，也是实现数据复制、数据恢复和数据安全的基础。

binlog 主要记录以下几类信息：

1.数据库的增删改操作

binlog 记录了所有对数据库的增删改操作，包括对表结构的修改。在每次写入操作时，MySQL 会将操作的数据写入 binlog 中。

2.事务的提交和回滚信息

当一个事务提交时，MySQL 会将事务提交的信息写入 binlog 中。如果一个事务回滚了，MySQL 也会将回滚信息写入 binlog 中，这个时候的 binlog 记录的信息可以用于数据恢复。

3.数据库的状态信息

binlog 记录了 MySQL 的状态信息，包括 MySQL 的版本、服务器的 ID、执行的线程 ID 等信息。

binlog 的使用场景：

1.  数据复制：MySQL 的主从复制是通过 binlog 实现的。主库将修改操作写入 binlog，从库通过读取主库的 binlog 文件进行复制。
2.  数据恢复：binlog 中记录了所有对数据库的修改操作，因此可以利用 binlog 进行数据恢复。
3.  数据备份：通过将 binlog 文件进行备份，可以在需要的时候恢复到某一个时间点的数据状态。

需要注意的是，binlog 记录的是 SQL 语句的修改操作，而不是记录行级别的修改。因此在进行数据恢复的时候，如果有使用到非 SQL 语句的修改方式，如直接修改文件等方式，则无法通过 binlog 进行恢复。

## 4.Mysql的binlog有几种录入格式？分别有什么区别？ ##

MySQL的binlog有三种格式：statement、row、mixed。

*  statement格式：记录的是SQL语句。在主库上执行的SQL语句会被记录到binlog中，并在从库上重放SQL语句来实现主从复制。这种格式相对简单，适用于数据量较小、写入操作较为简单的场景。
 *  row格式：记录的是行的变化情况。在主库上执行的每个行级别的写操作都会被记录到binlog中，并在从库上对相应的行进行修改，来实现主从复制。这种格式相对复杂，但是适用于数据量较大、写入操作频繁的场景。
 *  mixed格式：混合了statement和row两种格式。MySQL会根据具体的操作选择使用哪种格式。这种格式可以兼顾上述两种格式的优点，但是实现起来比较复杂。

## 5.redo log 如何保证事务的持久性？ ##

在 MySQL 中，redo log 主要是用来保证事务的持久性，其实现方式如下：

当一个事务提交时，InnoDB 引擎会首先将该事务的 redo log 缓存到内存中的 redo log buffer 中，同时在内存中更新相应的数据页；然后在适当的时机，将 redo log buffer 中的 redo log 写入磁盘上的 redo log 文件，以确保该事务在崩溃等异常情况下，能够通过 redo log 进行恢复。

在写入磁盘时，MySQL 会将 redo log 文件写入两个文件组成的循环队列中，每个文件的大小由配置参数 `innodb_log_file_size` 控制，默认值为 48MB。写入时会按照顺序从第一个文件开始写，直到写满，然后继续写入下一个文件。

因为 redo log 采用的是顺序写入磁盘的方式，所以可以提高写入磁盘的效率。同时，redo log 采用循环队列的方式，可以实现覆盖写，节省磁盘空间。

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB

![10a25035b4534d08284278b400b7d57a.jpeg][]

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

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

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

## 6.页修改之后为什么不直接刷盘呢？ ##

在数据库中，对数据的修改是指修改内存中的数据页，而不是直接修改磁盘上的数据文件。这样做的主要原因是为了提高写入性能和保证数据的一致性。

如果每次有修改操作就直接写入磁盘，会严重影响写入性能。而且，频繁的磁盘写入会导致磁盘的寿命缩短。此外，直接将修改操作写入磁盘可能会导致数据不一致的问题，例如操作系统或硬件出现错误或中断等情况下。

因此，数据库通常采用一种称为“写前日志”（Write-Ahead Logging，WAL）的技术，将对数据的修改记录到日志文件中。在写入磁盘之前，会先将修改操作记录到 redo log 中，保证事务的持久性和原子性。只有在事务提交的时候，才会将 redo log 中的修改操作同步到磁盘上的数据文件，这样就可以保证数据的一致性和持久性。

当系统崩溃或重启时，可以通过 redo log 重放来恢复数据文件。这是因为，在数据库中，事务提交时，会先将 redo log 中的数据写入到磁盘上的数据文件中，然后再将事务提交的标识写入到 redo log 中。因此，在数据库启动时，只需要将 redo log 中的操作重放，就可以将数据恢复到最新的状态。

## 7.binlog 和 redolog 有什么区别？ ##

1.  redo log（重做日志）是 InnoDB 存储引擎自己实现的一种日志，用于保证事务的持久性。当事务执行过程中修改了 InnoDB 表中的数据时，InnoDB 会先将修改操作记录到 redo log 中，并更新内存中的数据页。在事务提交前，InnoDB 会将 redo log 写入磁盘，保证数据持久化。redo log 是以循环写的方式写入磁盘，可以重复使用。redo log 的大小是固定的，可以通过参数 `innodb_log_file_size` 来设置。
2.  binlog（归档日志）是 MySQL 数据库服务层实现的一种日志，记录了数据库的所有更新操作，包括对哪个数据库的哪个表进行了什么样的操作。binlog 用于在主从复制、数据库恢复等场景下使用。binlog 中的日志记录是顺序写入的，不会重复使用。binlog 的大小是可变的，可以通过参数 `max_binlog_size` 来设置。

这两种日志有以下三点不同。

1.  redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
2.  redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
3.  redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

## **8.怎样让数据库恢复到半个月内任意一秒的状态？** ##

binlog会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的DBA承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

*  首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
 *  然后，从备份的时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的那个时刻。

这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

## 9.redo log和binglog如何保证两份日志之间的逻辑一致 ##

redo log 和 binlog 的逻辑一致性是通过两阶段提交 (two-phase commit) 来保证的。在执行修改操作时，MySQL 会先将这些操作记录到 redo log 中，然后再将这些操作记录到 binlog 中。由于 redo log 和 binlog 的记录顺序不同，它们之间可能存在逻辑不一致的情况。为了保证逻辑一致性，MySQL 引入了两阶段提交机制。

![277f1052f46172e1f69da8f546856196.png][]

在两阶段提交中，MySQL 会先将修改操作记录到 redo log 中的 prepare 阶段，此时并没有提交事务。然后 MySQL 将这些操作记录到 binlog 中，并将事务标记为 prepare 状态。最后，在 commit 阶段，MySQL 会将事务从 prepare 状态转变为 commit 状态，同时将 redo log 中的操作提交到磁盘中。这样就保证了 redo log 和 binlog 之间的逻辑一致性。

需要注意的是，在两阶段提交中，如果在 prepare 阶段发生了错误，MySQL 会回滚该事务，并将 binlog 中对应的操作标记为 rollback。这种情况下，redo log 和 binlog 之间就不会存在逻辑不一致的问题。

redo log 和 binlog 的逻辑一致性是通过两阶段提交来保证的，这也是 MySQL 实现 ACID 中的 A (原子性) 和 C (一致性) 的关键。

## 10.undo log 如何保证事务的原子性？ ##

undo log 是 InnoDB 存储引擎用来实现事务的原子性和回滚操作的一种机制。在事务进行修改时，InnoDB 会先将修改前的数据写入到 undo log 中，然后进行数据的修改操作，如果事务回滚，则可以利用 undo log 中的信息将数据恢复到修改之前的状态，从而实现事务的原子性。

具体来说，当一个事务开始时，InnoDB 会为该事务开启一个 undo log，所有该事务对数据的修改都会先写入 undo log 中，然后再对数据进行修改。当事务提交时，会将所有对数据的修改一次性写入 redo log 中，然后再将事务的提交状态写入 redo log 中，表示事务提交成功。

如果事务执行过程中发生了错误导致回滚，InnoDB 会利用 undo log 中的信息将数据恢复到修改前的状态。当事务回滚时，InnoDB 会将事务对数据的修改操作反向执行，即将修改后的数据恢复成修改前的数据，然后将这些操作写入 redo log 中，表示事务回滚成功。

undo log 主要用来保证事务的原子性和回滚操作。当事务执行失败时，可以利用 undo log 中的信息将数据恢复到修改之前的状态，从而避免了数据的损坏和不一致。

[10a25035b4534d08284278b400b7d57a.jpeg]: https://img-blog.csdnimg.cn/img_convert/10a25035b4534d08284278b400b7d57a.jpeg
[277f1052f46172e1f69da8f546856196.png]: https://img-blog.csdnimg.cn/img_convert/277f1052f46172e1f69da8f546856196.png