文章目录
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
联合唯一索引
扫码关注