MySQL实战45讲 15

15 | 答疑文章(一):日志和索引相关问题

日志相关

binlog(归档日志)和redo log(重做日志)配合崩溃恢复,在两阶段提交的不同瞬间,MySQL如果发生异常重启,是怎么保证数据完整性的?

MySQL实战45讲 15

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 会怎么处理?

崩溃恢复时的判断规则:

  1. 如果 redo log 里面的事务是完整的,也就是已经有了 commit 标识,则直接提交;
  2. 如果 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

MySQL实战45讲 15

这样的流程下,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 更新过去”的情况。

  1. 如果是正常运行的实例的话,数据页被修改以后,跟磁盘的数据页不一致,称为脏页。 最终数据落盘,就是把内存中的数据页写盘。这个过程,甚至与 redo log 毫无关系。(指落盘的过程)
  2. 在崩溃恢复场景中,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;

MySQL实战45讲 15

由于一开始 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/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • python_Xpath入门

    下面列出了最有用的路径表达式: 表达式 nodename 选取此节点的所有子节点。 从根节点选取。 从匹配选择的当前节点选择文档中的节点,而不考虑它们的位置。 选取当前节点。 选取…

    数据库 2023年6月11日
    0117
  • jdbc-使用java连接mysql

    package com.cqust; import com.mysql.jdbc.Driver; import java.sql.Connection;import java.sq…

    数据库 2023年5月24日
    0110
  • Windows安装mysql数据库

    一般我安装mysql用以下两个方法: 一.phpstudy环境下的mysql安装 只需将mysql的bin目录配置到系统环境变量即可, 输入默认密码root即可登录 二.本地直接安…

    数据库 2023年5月24日
    0143
  • Spring Security实现统一登录与权限控制

    1 项目介绍 最开始是一个单体应用,所有功能模块都写在一个项目里,后来觉得项目越来越大,于是决定把一些功能拆分出去,形成一个一个独立的微服务,于是就有个问题了,登录、退出、权限控制…

    数据库 2023年6月14日
    0156
  • Redis 生产架构选型对比,一文整治选择困难症

    前言 在写开源项目的时候,想到了要支持多种redis部署方式,于是对于这块的生产环境的架构选型展开调研。 一、引擎版本 推荐使用更新的引擎版本以支持更多的特性, Redis 6.0…

    数据库 2023年6月14日
    0134
  • MySQL中读页缓冲区buffer pool

    Buffer pool 我们都知道我们读取页面是需要将其从磁盘中读到内存中,然后等待CPU对数据进行处理。我们直到从磁盘中读取数据到内存的过程是十分慢的,所以我们读取的页面需要将其…

    数据库 2023年5月24日
    0162
  • nexus3搭建私有Docker仓库

    Nexus3: version: "3" services: nexus: image: sonatype/nexus3:3.33.0 volumes: – /…

    数据库 2023年6月9日
    0134
  • atomic 原子自增工程案例

    案例 1 : 简单用法 atomic_int id; atomic_fetch_add(&id, 1) atomic_uint id; atomic_fetch_add(&…

    数据库 2023年6月9日
    0146
  • Redis-缓存和数据库一致性问题

    三种策略 Cache Aside 只读缓存模式,即读操作命中缓存直接返回,未命中从后端数据库加载到缓存再返回。写操作直接更新数据库,并删除缓存。👍一切以后端数据库为准,最常用的方式…

    数据库 2023年6月11日
    0168
  • web 前端 基础HTML知识点

    B/S(Browser/Server):浏览器实现 优点: 规范、使用方便、本身实现成本低 容易升级、便于维护 缺点: 没有网络,无法使用 保存数据量有限,和服务器交互频率高、耗费…

    数据库 2023年6月16日
    0114
  • Qt 圆角头像的实现

    在QT 中设置圆形头像,本文记录了两个解决思路。 将头像显示在QLabel 此类控件中,设置QLabel 为一个正方形,接着设置QLabel 的圆角属性,可以实现圆形头像的效果。 …

    数据库 2023年6月16日
    0156
  • 接口测试

    :配置windows中特定应用的抓包(默认抓取不到) :添加备注信息 :重新发起指定请求 :清空指定会话内容 :断点放行 :模式切换 :相应数据解码 :抓取指定进程发出的请求 :关…

    数据库 2023年6月16日
    0171
  • CentOS7源码安装Python、virtualenv虚拟环境安装、uwsgi安装配置

    一:Python源码安装 1:Python源码下载XZ开头的 https://www.python.org/downloads/source/ 2:解压:tar xf Python…

    数据库 2023年6月6日
    0151
  • MySQL之文件

    1.参数文件 当MySQL实例启动时,数据库会先去读一个配置参数文件,用来寻找数据库的各种文件所在的位置以及指定某些初始化参数,这些参数通常定义了某种内存结构有多大等。在默认轻快下…

    数据库 2023年6月6日
    0123
  • 一、SQL高级语句

    摘抄别的博主的博客主要总去CSDN看不太方便自己整理一下加深记忆! 导入文件至数据库 #将脚本导入 source 加文件路径 mysql> source /backup/te…

    数据库 2023年5月24日
    0138
  • JUC的数据库连接池小练习

    JUC练习数据库连接池实现 通过一个连接数组来充当连接池 一个原子的标记数组 通过cas来保持多线程下的安全,用synchronized来进行暂停和唤醒 @Slf4j public…

    数据库 2023年6月11日
    0155
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球