MySQL日志系统

日志系统负责记录数据库运行期间的各种状态信息。MySQL的日志包含了查询信息、错误信息和事务信息等等。MySQL的日志系统具有缓存机制，只有当内存中的日志超过指定阈值时，才会将日志刷到磁盘上。
MySQL 的日志主要分为以下4类：

错误日志
普通查询日志
二进制日志
慢查询日志

错误日志

错误日志，默认开启错误日志，并且通常名称为hostname.err
注：错误日志中记录的并非全是错误信息，例如 MySQL 如何启动 InnoDB 的表空间文件、如何初始化自己的存储引擎等，这些也记录在错误日志文件中。

普通查询日志

普通查询日志会记录MySQL服务上的所有操作，例如select、insert、update、delete等
注：由于普通查询日志几乎记录了MySQL的所有操作，对于数据访问频繁的数据库服务器而言，如果开启MySQL的普通查询日志将会大幅度的降低数据库的性能，因此建议关闭普通查询日志。只有在特殊时期，如需要追踪某些特殊的查询日志，可以临时打开普通的查询日志。

慢查询日志

慢查询日志可以有效的跟踪执行时间过长或者没有使用索引的查询语句。这种包括select 语句，update语句，delete语句，以及insert语句，为优化查询提供帮助。与普通查询日志不同的另一个区别在于，慢查询日志只包含成功执行过的查询语句。

二进制日志

bin log是MySQL sever层维护的一种二进制日志，其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以"事务"的形式保存在磁盘中。主要有以下作用：

数据恢复
主从复制

查看所有bin log日志列表：

show master logs;

查看bin log的具体内容：

show binlog events in 'binlog.000002';

flush 刷新log日志，自此刻开始产生一个新编号的binlog日志文件;
注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqlddump备份数据时加-F选项也会刷新binlog日志；

flush log;

使用bin log进行数据恢复：

先备份数据库的bin log日志文件（mysql-bin.000002）
刷新一次日志，这样之后的日志记录会写入到下个日志文件中，保证mysql-bin.000002文件不会追加记录信息。
查看mysql-bin.000002日志的内容，找到出错的语句对应的position。
使用mysqlbinlog进行数据恢复

create database study;
flush logs;
show binlog events in 'mysql-bin.000002';

# mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
mysqlbinlog --start-position=316 --stop-position=1094 mysql-bin.000002 | mysql study;

回复数据实际上是根据bin log记录的操作，指定sql依次重新执行。

bin log写入机制
事务执行过程中，先把日志写到bin log的cache中，等到事务提交后再将cache中的内容写入bin log的文件中，并清空cache。每个线程有自己的bin log cache，但是共用同一份bin log文件。

bin log的3中格式

statement
- 每一条修改数据的sql都会记录到master的bin log中，salve在复制sql进程会解析成和原来master执行的相同的sql在执行。
- 不需要记录数据的变化情况，只需要记录sql语句和上下文信息。
- 主从复制时，可能会出现数据不一致的问题（上下文不全、主从mysql版本不一致）。
  例如：delete from t where age>10 and modified_time<='2020-03-04' limit 1
  主库用的是age上的索引，而备库执行却使用了modified_time上的索引，导致了删除操作后数据的不一致。
row
- 日志中会记录每一行数据被修改的形式，因此不会出现数据不一致的现象。
- 由于记录了数据的变化因此会产生大量的日志内容占用大量空间，例如删除了10万条数据statement只记录上下文和删除的sql语句，而row会记录10万条记录。
mixed
- 前两种的混合模式

注：只有bin log是没办法实现crash safe的，bin log是无限大小以追加的形式的日志文件，为逻辑日志，对于数据是否刷盘并不知晓。所以crash后没有明确的指标哪些数据没有刷盘。

InnoDB日志

InnoDB架构图

Buffer Pool：用于加速读（提前读）
Change Buffer：若二级索引页不在 buffer pool 中，则将针对二级索引页的操作暂时缓存起来，等到该页从磁盘读到 buffer pool 中时再批量的（batch）apply 这些操作，从而达到减少磁盘 I/O 的目的。（由于主键索引具有唯一性约束，若将insert id=1缓存起来，下次在insert则会报错）
log buffer用于加速redo log的写入
自适应Hash用于加快查询

Undo log

Undo log是帮助数据库实现原子性的重要手段。
原子性指针对数据库的一系列操作要么全部成功，要么全部失败，不存在部分成功的现象发生。
Undo log主要有以下两个作用：

事务回滚
Undo log记录数据的逻辑变化，例如当进行了insert操作，Undo log会记录反向操作的语句，保证数据回滚时，可以将数据还原回去。
MVCC
MySQL InnoDB引擎中使用Undo log实现MVCC
快照读：SQL读取的数据是快照版本，即历史版本，不需要加锁，普通的select就是快照读。
当前读（锁定读）：SQL读取的数据是当前数据最新版本，通过锁机制保证读取的数据无法被其他事务修改。
在不同的隔离级别下：
- read commited下快照读与当前读结果一致，读取的都是最新的数据。
- repeatable read下快照读可能读取的是历史版本，而当前读是最新的数据
- serializable下普通的select语句也会变成锁定读

Undo log的存储机制
insert对应的Undo log数据结构

其中Undo no在一个事务中是从0开始的，当insert数据时实际上需要考虑聚簇索引，因为在回滚删除聚簇索引时会同步删除辅助索引上的值。

delete对应的Undo log数据结构
删除一条记录实际分为两步：

delete mark：将删除标志位置1，修改事务id、回滚指针等
purge：删除语句所在的事务提交后，由线程将链表移动到垃圾链表中，将其真正的删除

通过old trx_id 和 old roll_pointer形成了版本链
例如：在一个事务中，先增加在删除

update对应的Undo log数据结构
分为不更新主键和更新主键

不更新主键
如果更新后的存储大小一致，则直接更新，若不一致则删除后再更新（此处的删除并不是上面的delete mark，而是执行了完整的删除操作）。
更新主键：先对原纪录进行delete mark，添加修改后的数据。
注：此处并没有真正删除原记录而是delete mark，这么做为了MVCC，不会导致其他事务根据主键查询不到该数据，因为其位置发生了改变。

Undo log页面链表
Undo log链表的第一个即为first Undo page，其余的称为normal Undo page。
InnoDB规定普通表的记录修改与临时表要分开，因此实际上一个事务最多4个Undo页面，并且insert和update链表会采用不同的策略，例如insert链表并不需要为MVCC服务，所以事务提交后可以直接覆盖。

对于first undo page包含一些其他信息：

每一个Undo页面链表都对应一个段（Undo Log Segment），链表中的页面都是从这里申请的。段为逻辑上的一个单位。

回滚段
在多并发的情况下，又定义了Rollback Segment。
在聚簇索引中，叶子节点页面为一个段，非叶子节点页面为一个段，回滚段只有一个Rollback Segment Header，存放了各个Undo链表的first undo page的页号，即undo slot。

MySQL以回滚段的方式来维护Undo log的并发写入和持久化。回滚段实际上是一种Undo文件的组织方式，每个回滚段又包含多个Undo log slot：

从上到下为回滚表空间、回滚段、Undo log slot、page。

rseg0预留在系统表空间ibdata中;
rseg 1~rseg 32这32个回滚段存放于临时表的系统表空间中;
rseg33~ 则根据配置存放到独立Undo表空间中（如果没有打开独立Undo表空间，则存放于ibdata中）

每个回滚段维护了一个段头页（Undo Page Header），在该page中又划分了1024个slot（undo slot即记录first undo log page的页号），每个slot对应一个Undo log对象，理论上InnoDB最多支持96 * 1024个普通事务（128 - 32）* 1024。当开启一个事务时，会为其分配一个回滚段（一个回滚段可能包含多个事务的日志）

回滚段的复用，由于InnoDB一次以16kb与磁盘交互，因此当一个事务提交后，它的undo日志并不会马上删除，当页空间小于3/4时，将其复用，供其他事务使用。

redo log

InnoDB通过redo log协助实现数据的持久性。通过WAL（write ahead logging）即先写日志，再写磁盘。redo log实际上记录的是数据页的变更，这种变更记录没必要全部存下，因此采用大小固定循环写入的方式。

redo log的写入机制

不用写log Buffer，只需要每秒写redo log 磁盘数据一次，性能高，但会造成数据 1s 内的一致性问题。适用于强实时性，弱一致性，比如评论区评论
写log Buffer，同时写入磁盘，性能最差，一致性最高。适用于弱实时性，强一致性,比如支付场景
写log Buffer，同时写到os buffer（其会每秒调用 fsync 将数据刷入磁盘），性能好，安全性也高。这个是实时性适中一致性适中的，比如订单类。

执行过程

(以下部分摘抄自参考链接中第一项)

首先 MySQL 执行器根据执行计划调用存储引擎的API查询数据
存储引擎先从缓存池 buffer pool 中查询数据，如果没有就会去磁盘中查询，如果查询到了就将其放到缓存池中
在数据加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 Undo log 日志文件中（不需要到磁盘读原来的值，只是做一个与当前语句相反的命令）
innodb 会在 Buffer Pool 的 Change Buffer 中执行更新操作在事务提交后，后台会有线程定期刷新数据到磁盘，也就是刷页
注：在正常的情况下, MySQL写数据page时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是doublewrite buffer写到真正的数据文件中。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。(Slave上可以关闭双写缓冲区)
更新后的数据会记录在 redo log buffer 中(prepare)
提交事务在提交的同时会做以下三件事
- 将 redo log buffer 中的数据刷入到redo log文件中（由innodb_flush_log_at_trx_commit策略决定刷盘时机）
- 将本次操作记录写入到 bin log 文件中（由sync_binlog策略决定刷盘时机）
- 将bin log文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记
使用一个后台线程，它会在某个时机将我们Buffer Pool中的更新后的数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了

其中redo log采用了两阶段提交（prepare和commit），目的是为了让两份日志（redo log和bin log）逻辑上一致。

为什么 binlog cache 是每个线程自己维护的，而 redolog buffer 是全局共用的？
1、binlog是不能“被打断的”。一个事务的binlog必须连续写，因此要整个事务完成后，再一起写到文件里。而redo log并没有这个要求，中间有生成的日志可以写到redo log buffer中，其他事务提交的时候可以被一起写到磁盘中。
2、binlog存储是以statement或者row格式存储的，而redo log是以page页格式存储的。page格式，天生就是共有的，而row格式，只跟当前事务相关。

partial page write问题
InnoDB的page size一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以page为单位进行操作的。操作系统写文件是以4KB作为单位的，那么每写一个InnoDB的page到磁盘上，操作系统需要写4个块。而计算机硬件和操作系统，在极端情况下（比如断电）往往并不能保证这一操作的原子性，16K的数据，写入4K时，发生了系统断电或系统崩溃，只有一部分写是成功的，这种情况下就是partial page write（部分页写入）问题。这时page数据出现不一样的情形，从而形成一个"断裂"的page，使数据产生混乱。这个时候InnoDB对这种块错误是无能为力的.
有人会认为系统恢复后，MySQL可以根据redo log进行恢复，而MySQL在恢复的过程中是检查page的checksum，checksum就是page的最后事务号，发生partial page write问题时，page已经损坏，找不到该page中的事务号，就无法恢复。

因此MySQL设计了双写缓冲区来解决此问题,第一遍是写到doublewrite buffer，第二遍是从doublewrite buffer写到真正的数据文件中。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。在这个过程中doublewrite buffer是顺序写因此开销不大。

参考链接

posted @ 2022-09-26 18:07 厚礼蟹！阅读(60) 评论(0) 收藏举报

刷新页面返回顶部

houlixie