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

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,最后会有 COMMITrow 格式的 binlog,最后会有一个 XID event,在 MySQL 5.6.2 版本以后,还引入了 binlog-checksum 参数,用来验证 binlog 内容的正确性
  • 追问 2
  • 描述redo logbinlog 是怎么关联起来的?
  • 回答:它们有一个共同的数据字段,叫 XID。崩溃恢复的时候,会按顺序扫描 redo log,如果碰到既有 prepare、又有 commitredo log,就直接提交,如果碰到只有 parepare、而没有 commitredo log,就拿着 XIDbinlog 找对应的事务
  • 追问 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 锁层面有没有办法处理?
  • 并发带来的问题:由于一开始 AB 之间没有关注关系,所以两个事务里面的 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 的时候,表示互相关注
  • 应用代码里面,比较 AB 的大小
  • 这个设计里,让like表里的数据保证 user_id < liker_id,这样不论是 A 关注 B,还是 B 关注 A,在操作like表的时候,如果反向的关系已经存在,就会出现行锁冲突
  • 建立 user_id、liker_id 联合唯一索引

扫码关注
在这里插入图片描述

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值