15 | 答疑文章（一）：日志和索引相关问题
日志相关
binlog（归档日志）和redo log（重做日志）配合崩溃恢复，在两阶段提交的不同瞬间，MySQL如果发生异常重启，是怎么保证数据完整性的？
Q：这个图不是一个update 语句的执行流程吗，怎么还会调用 commit 语句？
A：
两个”commit”的概念
- “commit 语句”是指 MySQL 语法中，用于提交一个事务的命令。一般跟begin/start transaction 配对使用。
- 而图中用到的这个”commit 步骤”，指的是事务提交过程中的一个小步骤，也是最后一步。当这个步骤执行完成后，这个事务就提交完成了。
- “commit 语句”执行的时候，会包含”commit 步骤”。
而这个例子里面，没有显式地开启事务，因此这个 update 语句自己就是一个事务， 在执行完成后提交事务时，就会用到这个”commit 步骤”。
分析在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象
如果在图中时刻 A 的地方，也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个 事务会回滚。这时候，binlog 还没写，所以也不会传到备库。
在时刻 B，也就是 binlog 写完，redo log 还没 commit前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？
崩溃恢复时的判断规则：
- 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
- 如果 redo log 里面的事务 只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
a. 如果是，则提交事务；
b. 否则，回滚事务。
这里，时刻 B 发生 crash 对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交。
Q：MySQL 怎么知道 binlog 是完整的?
A：一个事务的 binlog 是有完整格式的：
- statement格式的binlog，最后会有COMMIT；
- row格式的binlog，最后会有一个XID event。
另外，在MySQL 5.6.2版本以后，还引入了 binlog-checksum参数，用来验证binlog内容的正确性。
对于binlog日志由于磁盘原因，可能会在日志中间出错的情况，MySQL可以通过 校验checksum的结果来发现。所以，MySQL还是有办法验证事务binlog的完整性的。
Q：redo log 和 binlog 是怎么关联起来的?
A：
它们有一个共同的数据字段，叫 XID。崩溃恢复的时候， 会按顺序扫描 redo log：
- 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
- 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。
Q：处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?
A：这个问题和数据与备份的一致性有关。在时刻B，也就是 binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。 所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。
Q：如果这样的话，为什么还要两阶段提交呢？干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？
A：
回答：其实，两阶段提交是经典的分布式系统问题，并不是MySQL独有的。
如果必须给出一个场景来说明这一需求，那就是事务持久化。
[En]
If you have to give a scenario to illustrate the need for this, it is * transaction persistence * .
对于InnoDB引擎来说， 如果redo log提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。 而如果redo log直接提交，然后binlog写入的时候失败，InnoDB又回滚不了，数据和binlog日志又不一致了。
两阶段提交就是为了给 所有人一个机会，当每个人都说”我ok”的时候，再一起提交。
Q ：不引入两个日志，也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复，又能支持归档，不就可以了？
A：意思是只保留 binlog，然后可以把提交流程改成这样：… -> “数据更新到内存” -> “写 binlog” -> “提交事务”，是不是也可以提供崩溃恢复的能力？
不可以。
历史原因： InnoDB 并不是 MySQL 的原生存储引擎。MySQL 的原生引擎是 MyISAM，设计之初就有没有支持崩溃恢复。InnoDB 在作为 MySQL 的插件加入 MySQL 引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。InnoDB 接入了 MySQL 后，发现既然 binlog 没有崩溃恢复的能力，那就用 InnoDB 原有的 redo log 好了。
实现上的原因：
假设只用binlog
这样的流程下，binlog 还是不能支持崩溃恢复的。 binlog 没有能力恢复”数据页”。
如果在图中标的位置，也就是 binlog2 写完了，但是整个事务还没有 commit 的时候，MySQL 发生了 crash。
重启后， 引擎内部事务 2 会回滚，然后应用 binlog2 可以补回来；但是 对于事务 1 来说，系统已经认为提交完成了，不会再应用一次 binlog1。
但是，InnoDB 引擎使用的是 WAL 技术，执行事务的时候，写完内存和日志，事务就算完成了。 如果之后崩溃，要依赖于日志来恢复数据页。
也就是说在图中这个位置发生崩溃的话，事务 1 也是可能丢失了的，而且是 数据页级的丢失。此时，binlog 里面并没有记录数据页的更新细节，是补不回来的。
如果要说，优化一下 binlog 的内容，让它来 记录数据页的更改可以吗？但，这其实就是又做了一个 redo log 出来。所以，至少现在的 binlog 能力，还不能支持崩溃恢复。
Q：那能不能反过来，只用 redo log，不要 binlog？
A： 如果只从崩溃恢复的角度来讲是可以的。你可以把 binlog 关掉，这样就没有两阶段提交了，但系统依然是 crash-safe 的。
但 binlog 有着 redo log 无法替代的功能。
一个是归档。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用。
一个就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。
还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费 MySQL 的binlog 来更新自己的数据。 关掉 binlog 的话，这些下游系统就没法输入了。
Q：redo log 一般设置多大？
A：redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样WAL 机制的能力就发挥不出来了。直接将 redo log 设置为4 个文件、每个文件 1GB 。
Q：正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？
A：
实际上， redo log 并没有记录数据页的完整数据，所以 它并没有能力自己去更新磁盘数据页，也就不存在”数据最终落盘，是由 redo log 更新过去”的情况。
- 如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。 最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。（指落盘的过程）
- 在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。
Q：redo log buffer 是什么？是先修改内存，还是先写 redo log文件？
A：
在事务的更新过程中，日志被多次写入。例如，以下事务：
[En]
During the update process of a transaction, the log is written multiple times. For example, the following transaction:
begin;
insert into t1 ...
insert into t2 ...
commit;
这个事务要往两个表中插入记录，插入数据的过程中， 生成的日志都得先保存起来， 但又不能在还没 commit 的时候就直接写到 redo log 文件里。
所以， redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。但 是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit语句的时候做的。
这里说的是事务执行过程中不会” 主动去刷盘“，以减少不必要的 IO 消耗。但是可能会出现”被动写入磁盘”，比如内存不够、其他事务提交等情况。
业务设计问题
业务上有这样的需求，A、B 两个用户， 如果互相关注，则成为好友。
设计上是有两张表，一个是 like 表，一个是 friend 表，like 表有 user_id、liker_id 两个字段，我设置为复合唯一索引即 uk_user_id_liker_id。语句执行逻辑是这样的：
以 A 关注 B 为例：
第一步，先查询对方有没有关注自己（B 有没有关注 A）
select * from like where user_id = B and liker_id = A;
如果有，则成为好友
insert into friend;
不，这只是一段单向的关系。
[En]
No, it’s just an one-way relationship.
insert into like;
但是 如果 A、B 同时关注对方，会出现不会成为好友的情况。 因为上面第 1步，双方都没关注对方。第 1 步即使使用了排他锁也不行，因为记录不存在，行锁无法生效。请问这种情况，在 MySQL 锁层面有没有办法处理？
即： 在并发场景下，同时有两个人，设置为关注对方，就可能导致无法成功加为朋友关系。
模拟出表：
PS：业务根本就是保证”我一定会插入重复数据，数据库一定要要有唯一性约束”，这时直接建 唯一索引。不用考虑在”业务开发保证不会插入重复记录”的情况下，着重要解决性能问题的时候，才建议尽量使用 普通索引。
CREATE TABLE like (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
liker_id int(11) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_user_id_liker_id (user_id,liker_id)
) ENGINE=InnoDB;
CREATE TABLE friend (
id int(11) NOT NULL AUTO_INCREMENT,
friend_1_id int(11) NOT NULL,
firned_2_id int(11) NOT NULL,
UNIQUE KEY uk_friend (friend_1_id,firned_2_id)
PRIMARY KEY (`)
) ENGINE=InnoDB;
由于一开始 A 和 B 之间没有关注关系，所以两个事务里面的 select 语句查出来的结果都是空。
因此，session 1 的逻辑就是”既然 B 没有关注 A，那就只插入一个单向关注关系”。session 2 也同样是这个逻辑。
这个结果对业务来说就是 bug 了。因为在业务设定里面，这两个逻辑都执行完成以后，是应该在 friend 表里面插入一行记录的。
如提问里面说的，”第 1 步即使使用了排他锁也不行，因为记录不存在，行锁无法生效”。不过，使用另外一个方法，来解决这个问题。
首先，要给”like”表增加一个字段，比如叫作 relation_ship，并设为整型，取值 1、2、3。
值是 1 的时候，表示 user_id 关注 liker_id;
值是 2 的时候，表示 liker_id 关注 user_id;
值是 3 的时候，表示互相关注
然后，当 A 关注 B 的时候，逻辑改成如下所示的样子：
应用代码里面， 比较 A 和 B 的大小，如果 A
Original: https://www.cnblogs.com/ydssx7/p/16517601.html
Author: ydssx
Title: MySQL实战45讲 15
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505150/
转载文章受原作者版权保护。转载请注明原作者出处！