MySQL——日志、事务、索引

「爱情、让人受尽委屈。」 2022-04-23 12:26 236阅读 0赞

前提概要：MySQL的原生引擎是MyISAM。因为MyISAM在日志层面不支持redo log，在事务层面不支持事务。所以才引入存储引擎InnoDB。MySQL用的是WAL（write-ahead log）机制，先写日志再写磁盘——这样子的好处在于可以实现crash safe。先介绍完概念再分析。

日志篇：

在MySQL引入InnoDB后，redo log和binlog是两种MySQL的日志。两种日志是独立的，

1）redo log ：

MySQL引入InnoDB后，支持redo log。redo log是“循环”写入日志的方式——开辟一个内存区，这个内存区形象的描述成一个**环**，环上有两个指针，前面的叫写指针，后面的叫擦除指针。写指针一直把日志写到内存里，擦除指针把内存数据删掉，变成新的空闲地方可以使用。所以，当写指针与探险指针相遇（也就是追赶上了），那就是redo log内已经没有空闲的地方可以记日志了。那么，需要把redo log里的一部分日志内容写入磁盘。这样子做的好处在于，可以减少磁盘写操作的数次（据说可以减少一半的量），从而提高数据库I/O性能效率。

redo log是物理日志，顺序读写，不具有持久性——所以没有binlog的归档功能。redo log日志是**记录未完成的事务日志**。分为两个状态：prepare 和 commit。

来一下官方介绍：

The redo log is a disk-based data structure used during **crash recovery** to correct data written by incomplete transactions. During normal operations, the redo log encodes requests to change table data that result from SQL statements or low-level API calls. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically during initialization, and before the connections are accepted.

根据官方描述，我们可以知道redo log是为了应付系统临时崩溃恢复用的。后面会详细介绍。

2）binlog:

binlog的全称是**binary log**——二进制日志。官方描述如下：The binary log contains “events” that describe database changes such as table creation operations or cha4nges to table data. It also contains events for statements that potentially could have made changes (for example, a [`DELETE`][DELETE] which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data.

binlog是MySQl的server层的归档日志。**什么叫归档日志呢？**——归档日志(Archive Log)是非活动的重做日志备份。通过使用归档日志，可以保留所有重做历史记录，当数据库处于ARCHIVELOG模式并进行日志切换式，后台进程ARCH会将重做日志的内容保存到归档日志中。当数据库出现介质失败时，使用数据文件备份，归档日志和重做日志可以完全恢复数据库。

binlog用于只记录对数据库数据发现变化的操作（update/insert/delete等）（如果是select这种，还有一个query log数据）。因此binlog用于记录**维护**数据库操作和恢复个别数据。

下面用一张图来解析redo log和binlog的执行顺序和遇到crash的时候的恢复逻辑：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTA0ODc0Ng_size_16_color_FFFFFF_t_70][]

redo log处理prepare状态时，代表执行器执行成功，可以随时commit数据 。然后执行器生成update操作的binlog，并把binlog写入磁盘。执行器调用存储引擎的事务接交接口，把redo log的状态改成commit，数据才真正提交。

redo log两个状态是为了保证恢复时数据保持一致。假设redo log只有一个状态，那就有两种场景：

1）如果是先记录redo log，再记binlog。那么在【记redo log完成，还没成功记binlog】的情况下（即图中的**时刻A**），redo log恢复数据时因为执行器已执行完成，所以C的值为1。但是binlog没有任何日志，所以如果用binlogC的值恢复为0。**两个日志恢复出来的数据不一致**。

2）如果是先记binlog，再记redo log。那么在【记binlog完成，还没成功记redo log】的情况下，同理可得，**两个日志恢复出来的数据不一致**。

所以，只有redo log是两个状态的情况——时刻A崩溃时，redo log数据恢复成还未提交到数据库，所以可以把执行器的状态变成c=1待提交状态。binlog也没有写入，所以binlog恢复数据库里的数据c=0，所以成功恢复c=0未提交状态，此时，因为binlog没有记录，所以redo log未完成的事务可以被丢弃，保持c=0。时刻B崩溃时，redolog数据恢复，binlog已经写入，则可以把数据恢复成c=1待提交状态。综上所述，redo log和binlog可以实现crash-safe。**但是需要注意的是，通过数据恢复，不一定可以完全恢复数据。只可以恢复某个时间前的数据，不可以试图将某个操作前后的数据进行合并（比如发生误删除操作后，想把误删除前的数据+误删除后还持续发生的业务数据），只能恢复误删除前的，误删除后的需要手工加进数据库中**。

（还没写完……ing）

`select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started)) > 60`

[DELETE]: https://dev.mysql.com/doc/refman/8.0/en/delete.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTA0ODc0Ng_size_16_color_FFFFFF_t_70]: /images/20220319/fdec37a288064194ad71ae785eceb7bb.png