MySql日志

三个日志

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC。
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

为啥需要 undo log？

实现事务回滚，保障事务的原子性。在事务处理过程中，如果出现了错误或者用执行了ROLLBACK语句，MySql可以利用undo log中的历史数据恢复到事务开始之前的状态。
实现MVCC(多版本并发控制)关键因素之一。MVCC是通过ReadView + undo log实现的。undo log 为每条记录保存多份历史数据，MySQL在执行快照读(普通select语句)的时候，会根据事务的Read View里的信息，顺着undo log 的版本链找到满足其可见性的记录。

为啥需要Buffer Pool？

总体原因:

相较于内存的缓存来说，磁盘的速度太慢，这时如果有相应的缓存池，就可以大幅度地提高性能。

当读取数据时，如果数据存在于Buffer Pool中，客户端就会直接读取Buffer Pool中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

Buffer Pool缓存啥？

Buffer Pool的内存空间是一片连续的内存空间，以页的形式进行数据交换，组成部分是一个个页，这种页叫做缓存页。
由数据页，索引页，插入缓存页，undo页，自适应哈希索引，锁信息。
undo页是记录相应的undo log。
当查询一条记录时，InnoDB不是只需要缓存一条记录，当我们查询一条记录时，InnoDB是会把整个页的数据加载到Buffer Pool中，将页加载到Buffer Pool中，再通过页里的页目录去定义到某条具体的记录。

为啥需要redo log？

基于内存的Buffer Pool，如果内存出现错误，那么其中还没来得及落盘的脏页数据就会丢失。为了防止这种情况再次发生引出了rdao log。
可能会出现啥服务端宕机的情况，所以可以通过redo log来进行预防故障。

被修改 Undo 页面，需要记录对应 redo log 吗？

需要的。在内存修改该Undo页面后，也是需要记录对应的redo log, 因为undo log也要实现持久性的保护。

redo log 和 undo log区别在哪？

redo log 记录了此次事务修改后的数据状态，记录的是更新后的值，功能是事务崩溃恢复，保证事务的持久性。
undo log 记录了此次事务修改前的数据状态，记录的是更新之前的值，保证事务的原子性。

redo log 要写到磁盘，数据也要写磁盘，为啥要多次一举？

第一点redo log的写入方式使用了追加操作，所以磁盘操作是顺序写，而对于直接写入数据的写入方式是随机写。所以redo log 的写入更加高效，这是第一点。
第二点的话就是实现事务的持久性，让MySql有crash-safe的能力，能够保证MySql在任何时间段突然崩溃，重启之前已提交的记录都不会丢失。

产生的redo log是直接写入磁盘的吗？

不是

它也有自己的缓存redo log buffer，每当产生一条redo log时，会先写入到redo log buffer，后续在持久化到磁盘中。(redo log buffer默认大小为16MB，可以通过innodb_log_Buffer_size参数动态的调整大小)。

redo log啥时候刷盘？

MySQL正常关闭时。
当redo log buffer中记录的写入量大于redo log buffer 内存空间的一半时，会触发落盘。
InnoDB的后台线程每隔1秒，将redo log buffer持久化到磁盘中
每次事务提交时都将缓存在redo log buffer里的redo log直接持久化到磁盘(这个策略可由innodb_flush_log_at_trx_commit 参数控制)

innodb_flush_log_at_trx_commit参数控制的是啥？

控制的是当事务提交的时候，redo log写入的策略。

当设置该参数为0的时候，表示每次事务提交的时候，还是将redo log 留在redo log buffer中，该模式下在事务提交时不主动触发写入主动触发写入磁盘的操作。
当设置该参数为1的时候，表示每次事务提交的时候，都将缓存在redo log buffer里的redo log直接持久化到磁盘中，这样可以保证MySql异常重启之后数据都不会丢失。
当设置该参数为2时候，表示每次事务提交的时候，都只是缓存在redo log buffer 里的redo log 写到redo log 文件，其实是写入到操作系统的Page Cache。如下图

那innodb_flush_log_at_trx_commit为0和2的时候，啥时候才将redo log 写入磁盘？

当然是交给InnoDB的后台线程了

对于innodb_flush_log_at_trx_commit参数为0的情况，会把缓存在redo log buffer 中的redo log，通过调用write()写到操作系统的Page Cache，然后调用fsync()持久化到磁盘。所以参数为0的策略，MySql进程的崩溃的会导致上一秒的所有的事务数据的丢失。
对于innodb_flush_log_at_trx_commit参数为2的情况，会直接调用fsync()持久化到磁盘。所以参数为2的策略，较取值为0情况下更安全，因为MySQL进程的崩溃并不会丢失数据，只有在操作系统崩溃或者没电的情况下，上一秒钟所有事务数据才可能丢失。

对于这三种参数我们该如何使用？

对于数据安全性的话，参数1的安全性最高，其次是参数2，最后是参数0
对入写入性能的话，参数0的写入性能最好，其次是参数2，最后是参数1

不同的场景我们使用不同的处理方案

在一些数据安全性要求比较高的场景中，显然参数需要设置为1。
在一些可以容忍数据库崩溃时丢失1s的数据的数据，而且同时对性能有要求的话，我们可以将参数设置为0，这样的话可以明显地提高性能。
如果想要折中的方案的话，可以将参数设置为2，这样既有安全性又有性能。

redo log 文件写满了咋办？

MySql有两个日志文件组(redo log Group)，这个日志文件组是以循环写的方式工作的，如果 write pos 追上了checkpoint，就意味着redo log 文件满了，这时MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

这样的话就可一次将脏页刷新到磁盘中变成干净页，然后标记redo log哪些记录可以被覆盖的过程。

为啥需要binlog？

这个跟MySql的时间线有关，最开始MySql里面没有InnoDB引擎，MySQL自带的引擎是MyISAM，但是MYISAM没有crash-cafe的能力，binlog日志只能用于归档。
而InnnoDB是另一个公司以插件形式引入MySQL的，既然只靠binlog是没有crash-sefe能力的，所以InnoDB使用redo log 来实现crash-safe能力。

redo log 和 binlog有啥区别？

1.写入方式不同
- binlog是追加写，写满一个文件，就创建一个新的文件继续写，保存的是全量的日志。
- redo log是循环写，日志空间大小是固定，如果写满的话，就从头开始，保存未被刷入磁盘的脏页日志。
2.用途不同
- binlog用于备份恢复，主从复制。
- redo log 用于掉电等故障恢复。
3.用的对象不同
- binlog是Server层实现的日志，所有的存储引擎都可以使用。
- redo log仅仅适用于Innodb存储引擎实现的日志。
4.文件格式不同
- Binlog（二进制日志）主要有以下三种格式：
  - STATEMENT格式
    - 定义：也称为基于SQL语句的复制（SBR，Statement - Based Replication）。在这种格式下，binlog会记录每一条修改数据的SQL语句。例如，当执行INSERT INTO users (name, age) VALUES ('John', 25)这条SQL语句时，binlog会将这条完整的INSERT语句记录下来。
    - 优点： - 日志内容相对紧凑，因为它记录的是SQL语句，占用的空间比记录每一行数据变化的格式要小。例如，一条UPDATE语句可能会修改多条记录，但在STATEMENT格式下，只需要记录这一条UPDATE语句。 - 比较直观，易于理解。可以直接看到执行的SQL操作，方便数据库管理员（DBA）进行故障排查和审计。
    - 缺点： - 存在一定的安全风险。如果SQL语句中包含了一些具有不确定性的函数，如UUID()、NOW()等，可能会导致主从数据库的数据不一致。因为这些函数在主库和从库执行时，结果可能会不同。 - 对于一些复杂的存储过程和函数，在从库上重新执行时可能会出现问题。因为主库上存储过程内部的操作细节可能依赖于主库的环境，在从库上执行相同的存储过程可能无法得到预期的结果。
  - ROW格式
    - 定义：基于行的复制（RBR，Row - Based Replication）。它会记录每一行数据的修改情况。比如，对一个表进行UPDATE操作，它会记录更新前后每一行的具体内容。如果更新了3行数据，binlog中会详细记录这3行数据从旧值到新值的变化。
    - 优点： - 能够更准确地进行数据复制。因为它记录的是行级别的数据变化，所以不会受到SQL语句中不确定性因素的影响，保证了主从数据库的数据一致性。 - 对于一些包含复杂逻辑的更新操作，能够更精确地在从库上重现数据的修改过程。
    - 缺点：
      - 会产生大量的日志。因为它记录了每一行数据的变化，所以相比STATEMENT格式，日志文件可能会大很多。特别是对于一些批量操作，如批量插入或更新大量数据时，日志文件的增长速度会比较快。
      - 日志内容相对来说不那么直观，不容易直接看出执行的SQL操作，需要通过工具或者一定的解析才能了解数据变化对应的SQL操作。
  - MIXED格式
    - 定义：混合了STATEMENT和ROW两种格式。MySQL会根据具体的SQL操作自动选择合适的日志记录方式。例如，对于一些简单的、确定性的SQL语句，如UPDATE tab SET col1 = 'value' WHERE col2 = 'condition'（其中不包含不确定函数），会以STATEMENT格式记录；而对于一些复杂的操作，如包含UUID()函数的INSERT语句或者UPDATE语句影响了大量行数据，就会以ROW格式记录。
    - 优点：
      - 结合了STATEMENT和ROW格式的优点。在一定程度上减少了日志文件的大小，同时也保证了数据复制的准确性。
      - 可以根据实际的业务场景自动调整日志记录方式，具有较好的灵活性。
    - 缺点：
      - 由于采用了混合的记录方式，日志的解析和管理可能会相对复杂一些。需要考虑两种记录格式的特点，对于故障排查和数据恢复操作，要求DBA对两种格式都比较熟悉。
- redo log是物理日志，记录的是在某个数据页做了啥修改，比如对xxx表空间中的yyy数据页zzz偏移量的地方做了啥更新(这个就是弄了啥变化，具体的实现这个日志没说)

主从复制是如何实现的？

MySQL的主从复制依赖于binlog，binlog记录MySQl上的所有变化并以二进制形式保存在磁盘上，复制的过程就是将binlog中的数据从主库传输到从库中。

MySQL集群的主从复制过程分为3个阶段

写入binlog: 主库写binlog日志，提交事务，并更新本地数据。
同步binlog: 把binlog复制到所有从库中，每个从库把binlog写到暂存日志中。
回放binlog: 回放binlog，并更新存储引擎中的数据。

具体详细过程如下:

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

这样的话你就可以写的时候只写主库，读的时候只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

从库是不是越多越好？

不是

因为从库数量增加，从库连接上来的I/O线程也比较多，主库也要创建同样多的log dump线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。(通常情况下一个主库一般跟2-3个从库 1套数据库，1主2从备主)，这就是一主多从的MySQL集群结构。

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

主要有三种
同步复制: MySQL主库提交事务的线程要等待所有从库的复制响应，才返回客户端结果。这种方式在实际项目中，基本没法用，原因是: 一是性能很差，因为要复制到所有节点才返回响应，二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
异步复制(默认模型): MySQL主库提交事务的线程并不会等待binlog同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
半同步复制: MySQL5.7版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

binlog啥时候刷盘？

MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失。
而当 sync_binlog 设置为 1 的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使主机发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大。
如果能容少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000 中的某个数值。

为啥需要两阶段提交？

事务提交后，redo log和binlog都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成了两份日志之间的数据不一致。

如果在将redo log刷入到磁盘中，MySQL突然宕机了，而binlog还没有来得及写入。这样的话就会造成主库的数据是新的，但是从的数据是旧的。
如果在将binlog刷入到磁盘中，MySQL突然宕机了，而redo log还没有来得及写入。这样的话就会造成主库是旧的数据，而从库的数据是新的，从而导致数据不一致。

两阶段提交的过程是咋样的？

事务的提交过程有两个阶段

prepare阶段: 将XID(内部XA事务的ID)写入到redo log，同时将redo log对应的事务状态设置为prepare，然后将redo log持久化到磁盘中。
commit阶段: 把XID写入到binlog，然后将binlog持久化到磁盘，接着调用引擎的提交事务接口，将redo log状态设置为commit,此时该状态并不需要持久化到磁盘中，只需要write到文件系统的page cache中就够了，因为只要binlog写磁盘成功，就算redo log的状态还是prepare也没有关系，一样会被认为事务已经执行成功。

两阶段提交有啥问题吗？

磁盘I/O次数高: 对于"双1"配置，每个事务提交都会进行两次fsync(刷盘)，一次是redo log刷盘，另一次是binlog刷盘。
锁竞争激烈: 两阶段提交虽然能够保证单个事务两个日志的内容一致，但在多个事务的情况下，却不能保证两者的提交顺序一致。

为啥锁竞争激烈？

在早期的MySQL版本中，对于一个事务来说，获取锁才能进入prepare阶段，一直到commit阶段结束才能释放锁，相当于串行化。

组提交

MySQL引入了binlog组提交机制，当有多个事务提交的时候，会将多个binlog刷盘操作合并成一个，从而减少磁盘I/O的次数。

引入了组提交机制后，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分为三个过程：

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

MySQL磁盘I/O很高，有啥优化的方法没？

设置组提交的两个参数： binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，延迟 binlog 刷盘的时机，从而减少 binlog 的刷盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但即使 MySQL 进程中途挂了，也没有丢失数据的风险，因为 binlog 早被写入到 page cache 了，只要系统没有宕机，缓存在 page cache 里的 binlog 就会被持久化到磁盘。
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000），表示每次提交事务都 write，但累积 N 个事务后才 fsync，相当于延迟了 binlog 刷盘的时机。但是这样做的风险是，主机掉电时会丢 N 个事务的 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存，然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是，主机掉电的时候会丢数据。

具体更新一条记录 UPDATE t_user SET name = ‘jiahao’ WHERE id = 1; 的流程如下:

执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：

如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。

执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：

如果一样的话就不进行后续更新流程；
如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；

开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。

InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。

至此，一条记录更新完了。

在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。

事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：

prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；

至此，一条更新语句执行完成