JZTXT

MySQL_日志

发布时间 2023-12-06 17:04:54作者: Espre-sso

MySQL_日志

3种日志

  1. undo log 回滚日志，InnoDB存储引擎层生成，原子性，用于事务回滚、MVCC。
  2. redo log 重做日志，InnoDB存储引擎层生成，持久性，用于掉电、故障恢复。
  3. binlog 归档日志，Server层生成，用于数据备份、主从复制。

undo log

是什么：
undo log是一种用于撤销回退的日志，在事务提交之前，MySQL会记录更新前的数据到undo log日志文件。用于回滚。

记录了什么：
记录了回滚需要的信息。

  1. insert，记录主键值。回滚：根据主键delete
  2. delete，记录该条记录的内容。回滚：把该条记录insert
  3. update，记录更新的列的旧值。回滚：把旧值update

作用：

  1. 实现事务回滚，保证事务的原子性：出现错误或ROLLBACK。
  2. 实现MVCC（Read View + undo log）的关键因素之一。快照读（普通SELECT）时，根据事务Read View里的信息，顺着undo log找到满足其可见性的记录。

格式：

  1. roll_pointer：将undo_log串成一个链表：版本链
  2. trx_id：该记录是被哪个事务修改的。

undo log是如何刷盘（持久化到磁盘）的
和数据页的刷盘策略一样。都需要redo log保证持久化。
buffer pool中有undo页，对undo页的修改也会记录到redo log中。redo log每秒刷盘，提交事务时也会刷盘。数据页和undo页都是通过这个机制保证持久化的。

buffer pool

目的：
缓冲池，提高数据库的读写性能。
位置：
在InnodDB存储引擎中。
作用：

  1. 读数据：优先从buffer pool中读取。
  2. 改数据：优先改buffer pool中的数据页，将给页标记为脏页（该页内存和磁盘中的数据不一样），后台线程选择一个合适的时机写入磁盘（为了减少磁盘IO）。

缓存什么：
数据页，索引页，undo 页，插入缓存页，自适应hash索引，锁信息

buffer pool工作原理：
MySQL启动后，InnoDB为Buffer pool申请一块连续的内存，按16KB划分出一个个页，buffer pool中的页叫缓存页。（MySQL刚启动时，虚拟内存很大，物理内存很小，这是因为只有当这些虚拟内存被访问后，操作系统才会发生缺页中断，申请物理内存，将物理内存和虚拟内存建立映射关系）

只缓存一条记录吗：
不是，缓存整个数据页，根据页目录定位某条具体的记录。

redo log

怎么样算数据更新完成了：
InnoDB存储引擎更新内存（同时标记为脏页），将对这个页的修改用redo log记录下来。这时候更新就算完成了。
后续，InnoDB存储引擎通过后台线程将缓存在Buffer pool里的脏页刷新到磁盘里，这就是WAL技术。

WAL（Write-Ahead Logging）技术：
MySQL的写操作不是立刻写到磁盘上的，而是先写日志，然后在合适的时间写到磁盘上。

事务提交时：
只用将redo log持久化到磁盘，不用等到将缓存在磁盘中的Buffer pool里的脏页数据持久化到磁盘。

undo页面被修改，需要记录redo log吗
需要，在内存修改该Undo页后，需要记录对应的redo log。

redo log和undo log的区别

  1. redo log记录的是此次事务完成后的数据状态，记录的是更新之后的值。解决事务提交后发生的崩溃。
  2. undo log记录的是此次事务开始前的数据状态，记录的是更新之前的值。解决事务提交前发生的崩溃。

ACID中的持久性：crash-safe崩溃恢复能力靠： redo log + WAL 实现。

redo log要写磁盘，数据也要写磁盘，为什么多此一举
写入redo log：顺序写。更高效。
写入数据：随机写。
WAL的另一个优点：MySQL的写操作从随机写变成了顺序写，提升了效率，因为MySQL的写操作并不是立刻更新到磁盘上，而是先记录到日志上，然后在合适的时间再更新到磁盘上。

产生的redo log直接写到磁盘上吗
不是
redo log也有自己的缓存：redo log buffer。（InnoDB（Log Buffer（redo log buffer）））
每当产生一条redo log，会先写入redo log buffer（默认16MB）中，后续再持久化到磁盘中。（增大redo log buffer的大小，可以让MySQL处理大事务是不用写入磁盘，从而提升IO效率）

redo log 什么时候刷盘

  1. MySQL正常关闭时
  2. redo log buffer的写入量大于redo log buffer内存空间的一半时。
  3. InnoDB后台线程每隔1秒，将redo log buffer持久化到磁盘。
  4. 每次事务提交。（可由innodb_flush_log_at_trx_commit参数控制）

innodb_flush_log_at_trx_commit参数控制的是什么

参数可以为0，1，2

  1. 参数为0时，事务提交不会触发写入磁盘的操作
  2. 参数为1时，事务提交会触发写入磁盘
  3. 参数为2时，事务提交写入redo log文件（并不意味着写入了磁盘），实际是写入操作系统的文件缓存Page Cache（在内核空间）

比较：
线程安全性：1 > 2 > 0
写入性能：0 > 2 > 1

redo log文件写满了怎么办

redo log的目的：
防止buffer pool中的脏页丢失。

InnoDB存储引擎有一个重做日志组（redo log Group），由2个redo log文件组成（ib_logfile0，ib_logfile1），通过循环写的方式工作，指针write pos表示innoDB记录写到的位置，checkpoint表示当前要擦除的位置。

如果写满了：
也就是write pos追上了checkpoint。MySQL会阻塞，将buffer pool中的脏页刷新到磁盘中，标记redo log中哪些记录可以被擦除，对旧的redo log进行擦除，等擦除完了旧记录腾出了空间，checkpoint会向后移动。
一次checkpoint的移动，就是脏页刷新到磁盘中变成干净页，然后标记redo log哪些记录可以被覆盖的过程。

binlog

记录什么：
记录所有数据库表结构的变更和表数据的修改。不会记录查询类的操作。

binlog和redo log的区别

  1. 适用对象：
    binlog在server层，所有存储引擎都可以使用。
    redo log是innoDB存储引擎实现的日志。
  2. 文件格式
    binlog：
    STATEMENT（默认格式）记录逻辑操作（每一条修改数据的SQL），此时binlog叫逻辑日志。ROW记录行数据最终被修改的样子，此时不能叫逻辑日志了，问题：binlog文件大。MIXED根据不同情况选用STATEMENT或ROW。
    redo log：
    物理日志。记录的是在某个数据页做了什么修改。如对AAA表空间的BBB数据页CCC偏移量的地方做了DDD更新
  3. 写入方式
    binlog追加写。写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
    redo log循环写。日志空间的大小是固定的，写满就从头开始，保存未被刷入磁盘的脏页日志。
  4. 用途
    binlog用于备份恢复、主从复制。（用于归档）
    redo log用于掉电等故障恢复。（实现了crash-safe功能）

不小心将整个数据库的数据删了，用哪种日志文件恢复
用binlog，

主从复制是怎么实现的

依赖于binlog，复制的过程就是将binlog中的数据从主库传输到从库上。一般是异步的。
三个阶段：

  1. 写入Binlog：主库写binlog日志，提交事务，并更新本地存储数据。
  2. 同步Binlog：把binlog复制到从库上，每个从库把binlog写到relay log的中继日志中。
  3. 回放Binlog：回放binlog，并更新存储引擎中的数据。

具体过程：

  1. MySQL主库收到客户端提交事务的请求后，会先写入binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
  2. 从库会创建一个专门的I/O线程，连接主库的log dump线程，来接收主库的binlog日志，再把binlog信息写入relay log的中继日志里，再返回给主库“复制成功”的响应。
  3. 从库会创建一个用于回放binlog的线程，去读relay log中继日志，然后回放binlog更新存储引擎中的数据，最终实现主从的数据一致性。

从库的个数
从库并不是越多越好。（主库要创建同样多的log dump线程来处理复制的请求，对主库资源消耗比较多，同时还受限于主库的网络带宽）
一主多从的MySQL集群结构：实际使用中，1个主库一般跟2～3个从库（一套数据库，1主2从1备主）

MySQL的主从复制还有哪些模型

  1. 同步复制：主库提交事务的线程，等待所有从库的复制成功响应，才返回给客户端结果。（性能差、可用性差）
  2. 异步复制（默认）：主库提交事务的线程不会等待binlog同步到各从库，就返回客户端结果。（主库一旦宕机，数据就会丢失）
  3. 半同步复制：介于两者之间，主库提交事务的线程，只用等待部分从库复制成功的响应即可。

binlog什么时候刷盘

事务执行过程中，先把日志写到binlog cache中（Server层的cache），事务提交的时候，再把binlog cache写到binlog文件中。
MySQL为每一个线程分配了一块内存用于缓冲binlog，这块内存叫binlog cache。（参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小，如果超过这个限制，就要暂存在磁盘中）
一个事务的binlog是不能拆开的，如果拆开，在备库执行就会被当作多个事务分段执行，破坏了原子性，是有问题的。（存在一个设定：一个线程只能有一个事务在执行，每当执行一次start/begin trasaction的时候，就会默认提交上一个事务。）

什么时候binlog cache会写到binlog文件

事务提交的时候，执行器把binlog cache里的完整事务写到binlog文件中，并清空binlog cache。
每个线程都有自己的binlog cache，但最终都写到同一个binlog文件。
过程：

  1. write：把binlog cache写到binlog文件中（在文件系统的page cache里），速度快，不涉及磁盘I/O

  2. fsync：将数据持久化到磁盘，涉及到磁盘I/O。频繁的fsync会使磁盘I/O升高。

syn_binlog参数，控制binlog刷到磁盘上的速度：

  1. 参数为0，默认，每次事务提交只write，不fsync，由操作系统决定何时将数据持久化到磁盘。
  2. 参数为1，每次事务提交都会write和fsync。
  3. 参数为N，每次事务提交都write，积累N个事务再fsync。
    该参数一般设置为100～1000。

update语句的执行过程

  1. 优化器确定执行计划，执行器具体执行，调用存储引擎的接口，找到需要更新的记录。如果在buffer pool中，则返回给执行器更新。如果不在，则从磁盘读入buffer pool，返回给执行器。
  2. 执行器得到需要更新的记录的聚簇索引记录后。看一下更新前后的记录是否一样。一样：不进行后续的更新操作。不一样：将更新前后的记录传给InnoDB，由InnoDB真正执行更新记录的操作。
  3. 开启事务，InnoDB更新记录前，记录相应的undo log，undo log写入buffer pool中的Undo页面，Undo页面修改后，Undo页面的redo log也要记录。
  4. 更新记录，InnoDB先更新内存（同时标记为脏页），然后将记录写到redo log里。这个时候更新就算完成了。之后通过WAL技术，后台线程选择一个合适的时机将脏页写入磁盘。
  5. 一条记录就更新完了。
  6. 一条更新语句执行完后，开始记录binlog，binlog会保存在binlog cache中，事务提交时才会把所有的binlog刷新到磁盘。
  7. 事务提交，接下来就是两阶段提交了。
    7.1. Prepare阶段：将redo log的事务状态设置为prepare，然后将redo log刷新到硬盘。
    7.2. Commit阶段：将binlog刷新到磁盘，接着调用引擎的提交事务接口，将redo log的状态设置为commit。
  8. 至此，一条更新语句执行完成。

XA事务

两阶段提交

原因：
事务提交后，redo log和binlog都要持久化到磁盘，但这两个是独立的逻辑，可能出现半成功状态，会导致主从数据不一致。（redo log影响主库数据，binlog影响从库的数据）
实现：
MySQL使用了内部XA事务，当客户端commit或自动提交的时候，MySQL开启一个XA事务。

  1. Prepare：将XID（内部XA事务的id）写入redo log，同时将redo log对应的事务状态设置为prepare，将redo log持久化到磁盘。
  2. Commit：将XID写入binlog，将binlog持久化到磁盘，将redo log的状态设置为commit，此时该commit状态只要write到文件系统的Page cache就可以。（该标识其实并不重要，最终看的还是XID）

MySQL异常重启的2种情况

  1. redo log状态“prepare”，binlog中没有当前内部事务的XID，说明redo log完成刷盘，binlog还没有刷盘，则回滚事务。
  2. redo log状态“prepare”，binlog中有当前内部事务的XID，说明redo log、binlog都完成了刷盘，则提交事务。

可以看到，在"prepare"阶段的redo log，既可以回滚事务，也可以提交事务，取决于是否能在binlog中查找到与redo log相同的XID（有：提交。没有：回滚。这样就可以保证redo log和binlog这两份日志的一致性了）

所以说，两阶段提交是以binlog写成功作为事务提交成功的标识，因为binlog写成功了，就意味着能在binlog中查找到与redo log相同的XID。

两阶段提交的问题

  1. 磁盘I/O次数多。（对于“双1”配置，每个事务提交都要进行2次fsync（刷盘））
  2. 锁竞争激烈。（多事务情况下，要加一个锁来保证提交的原子性，从而保证多事务情况下，两个日志的提交顺序一致）（prepare_commit_mutex锁:一个事务获取锁->进入prepare阶段->commit阶段结束->释放锁）。

组提交

MySQL中引入binlog组提交，当有多个事务提交的时候，会将多个binlog刷盘操作合并成一个，从而减少磁盘I/O的次数。
将commit阶段拆分成3个过程：

  1. flush阶段：多个事务按进入的顺序将binlog从cache写入文件（不刷盘）。
  2. sync阶段：对binlog文件做fsync操作（多个事务的binlog合并一次刷盘）。
  3. commit阶段：各个事务按顺序做InnoDB commit操作。

每个阶段都有一个队列。锁是加在每个阶段的队列上的。锁的粒度减小了，这样就使得多个阶段可以并发执行，从而提升效率。

redo log有组提交吗
MySQL5.7以后有redo log组提交，prepare融合到了flush阶段。

sync_binlog 和 binlog_group_commit_sync_no_delay_count 的区别

  1. sync_binlog 是 binlog的刷盘策略，参数0，1，N：0每次事务提交只write，1每次事务提交write和fsync，N每次事务提交write，积累N个事务再fsync。
  2. binlog_commit_sync_no_delay_count 控制redo log的刷盘策略，参数0，1，2:0事务提交触发刷盘，1事务提交不触发刷盘，2事务提交半刷盘，写入操作系统的文件缓存Page Cache中，写入的是内核空间而不是磁盘。