MySQL学习笔记-日志和索引相关问题小结

1.笔记图

2.日志相关问题

2.1 在两阶段提交的不同瞬间，MySQL 如果发生异常重启，是怎么保证数据完整性的？

• 情况一：
  

• 描述：若写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃 （crash）
  
• 现象：由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库
  

• 情况二：
  

• 描述：若 binlog 写完，redo log 还没 commit 前发生 crash
  
• 现象：如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：如果是，则提交事务，否则，回滚事务
  

• 追问1：
  

• 描述：MySQL 怎么知道 binlog 是完整的?
  
• 回答：一个事务的 binlog 是有完整格式的，statement 格式的 binlog，最后会有 COMMIT，row 格式的 binlog，最后会有一个 XID event，在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性
  

• 追问 2：
  

• 描述：redo log 和 binlog 是怎么关联起来的?
  
• 回答：它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log，如果碰到既有 prepare、又有 commit 的 redo log，就直接提交，如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务
  

• 追问 3：
  

• 描述：处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?
  
• 回答：这个问题跟数据与备份的一致性有关。在时刻 B，也就是 binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用，采用这个策略，主库和备库的数据就保证了一致性
  

• 追问 4：
  

• 描述：如果这样的话，为什么还要两阶段提交呢？干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？
  
• 回答：其实，两阶段提交是经典的分布式系统问题，并不是 MySQL 独有的。如果必须要举一个场景，来说明这么做的必要性的话，那就是事务的持久性问题。对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了。
  

• 追问 5：
  

• 描述：不引入两个日志，也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复，又能支持归档，不就可以了？
  
• 回答：只保留 binlog，然后可以把提交流程改成这样：… -> “数据更新到内存” -> “写 binlog” -> “提交事务”，是不是也可以提供崩溃恢复的能力？答案是不可以
  

• 历史原因：InnoDB 并不是 MySQL 的原生存储引擎。MySQL 的原生引擎是 MyISAM，设计之初就有没有支持崩溃恢复，InnoDB 在作为 MySQL 的插件加入 MySQL 引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了，InnoDB 接入了 MySQL 后，发现既然 binlog 没有崩溃恢复的能力，那就用 InnoDB 原有的 redo log 好了
  
• 实现上的原因：binlog 还是不能支持崩溃恢复的，如 binlog 没有能力恢复数据页，现在的 binlog 能力，还不能支持崩溃恢复，将来可能会合并
  

• 追问 6：
  

• 描述：那能不能反过来，只用 redo log，不要 binlog？
  
• 回答：如果只从崩溃恢复的角度来讲是可以的。你可以把 binlog 关掉，这样就没有两阶段提交了，但系统依然是 crash-safe 的。但是，如果你了解一下业界各个公司的使用场景的话，就会发现在正式的生产库上，binlog 都是开着的。因为 binlog 有着 redo log 无法替代的功能
  

• 追问 7：
  

• 描述：redo log 一般设置多大？
  
• 回答：redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样 WAL 机制的能力就发挥不出来了。所以，如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧
  

• 追问 8
  

• 描述：正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？
  
• 回答：redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在数据最终落盘，是由 redo log 更新过去的情况，如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系，在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态
  

• 追问 9：
  

• 描述：redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？
  
• 回答：redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的。单独执行一个更新语句的时候，InnoDB 会自己启动一个事务，在语句执行完成的时候提交。过程跟上面是一样的，只不过是压缩到了一个语句里面完成
  

2.2 commit 的概念混淆说明

• 事务中 commit 语句，是指 MySQL 语法中，用于提交一个事务的命令。一般跟 begin/start transaction 配对使用
  
• 而前面用到的这个 commit 步骤，指的是事务提交过程中的一个小步骤，也是最后一步。当这个步骤执行完成后，这个事务就提交完成了
  
• commit 语句执行的时候，会包含 commit 步骤
  

3.业务设计问题

• 问题描述：业务上有这样的需求，A、B 两个用户，如果互相关注，则成为好友。设计上是有两张表，一个是 like 表，一个是 friend 表，like 表有 user_id、liker_id 两个字段，我设置为复合唯一索引即 uk_user_id_liker_id。语句执行逻辑是这样的：

1.以 A 关注 B 为例：第一步，先查询对方有没有关注自己（B 有没有关注 A）
select * from like where user_id = B and liker_id = A;
2.如果有，则成为好友 insert into friend;
3.没有，则只是单向关注关系 insert into like;
4.但是如果 A、B 同时关注对方，会出现不会成为好友的情况。
因为上面第 1 步，双方  都没关注对方。第 1 步即使使用了排他锁也不行，
因为记录不存在，行锁无法生效。
请问这种情况，在 MySQL 锁层面有没有办法处理？

• 并发带来的问题：由于一开始 A 和 B 之间没有关注关系，所以两个事务里面的 select 语句查出来的结果都是空。因此，session 1 的逻辑就是既然 B 没有关注 A，那就只插入一个单向关注关系。session 2 也同样是这个逻辑。这个结果对业务来说就是 bug 了。因为在业务设定里面，这两个逻辑都执行完成以后，是应该在 friend 表里面插入一行记录的。
  
• 解决办法
  

• 首先，要给like表增加一个字段，比如叫作 relation_ship，并设为整型，取值 1、2、3
  
• 值是 1 的时候，表示 user_id 关注 liker_id;值是 2 的时候，表示 liker_id 关注 user_id;值是 3 的时候，表示互相关注
  
• 应用代码里面，比较 A 和 B 的大小
  
• 这个设计里，让like表里的数据保证 user_id < liker_id，这样不论是 A 关注 B，还是 B 关注 A，在操作like表的时候，如果反向的关系已经存在，就会出现行锁冲突
  
• 建立 user_id、liker_id 联合唯一索引