MySQL 面试题

引言

今天回头继续讲讲数据库系列的文章。这篇文章属于mysql数据库系列，我们来谈谈事务方面的常见面试题。

那么，具体题目有下面这些:
1、讲讲为什么用事务？事务的四大特性？事务的隔离级别知道吧，你们生产用哪种?
2、Innodb中ACID具体是如何实现的？
3、redo log和binlog的一致性如何保证？
4、大事务有哪些坏处?生产上遇到过大事务么？你怎么排查和解决的？
5、你有遇到过数据库宕机重启，事务丢失的情况么?
6、可重复读是怎么实现的？

再三强调，每个问题都仔细看！都是高频题！切勿遗漏！

正文

1、讲讲为什么用事务？事务的四大特性？事务的隔离级别知道吧，你们生产用哪种?

回答:为什么用事务？

这个问题从事务的四大特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)，这四个角度去答。例如原子性的角度，张三给李四转账，只有当张三账户的钱转走了，并且李四账户的钱收到了之后转账事务才能提交。如果原子性无法保证，就会出现张三的钱转走了，李四却没收到钱的情况！

Come on!这种easy回答，我就不一个个举例了，过！

至于生产用哪种隔离级别？
答Read Commited或者Repeatable都行，有道理即可。例如，我用了Read Commited,因为这个隔离级别够用了，用不上间隙锁！详情可以参照《MySQL 应该选什么事务隔离级别》这篇文章！

另外，额外记住Repeatable是默认的隔离级别即可！至于另外两个隔离级别，Read uncommitted，一个事务能读到另一个事务未提交的数据，隔离性都无法满足，不用这个隔离级别。另外一个隔离级别Seriallzable,在这个隔离级别下，MVCC机制都无法满足，数据库并发性非常差，不用这个隔离级别。

注意：这个问题其实考察的是你对各个隔离级别的理解，所以务必牢记各个隔离级别的区别！

2、Innodb中ACID具体是如何实现的？

回答:老题了，详细版，可以看这篇文章《MySQL 事务的 ACID 原理》

这里给出简单回答，

(1)利用undo log保证原子性

(2)利用redo log保证持久性

(3)利用锁和MVCC机制保证隔离性

(4)通过原子性、持久性、隔离性来保证一致性

3、redo log和binlog的一致性如何保证？

回答:此题，先回忆一下redo log和binlog的区别！

这道题蓝绿大厂，开水团，宇宙条都问过！

• redo log 记录的是数据的物理变化，所以叫物理日志，记录的是是物理修改的内容（xxxx页修改了xxx）。当我们修改数据的时候，写完内存了，但数据还没真正写到磁盘的时候。此时我们的数据库挂了，我们可以根据redo log来对数据进行恢复!

• binlog 记录的是数据的逻辑变化，所以又叫逻辑日志，statement模式下记载的是update/delete/insert这样的SQL语句，主要用来主从复制和恢复数据用。

这二者功能很像，都是用作”恢复“的！因此这二两个日志必须保证逻辑上一致，否则就会出现数据错乱。例如，我们先写redo log再写binlog。在redo log写完后，系统鸡掰了，此时binlog来不及写。那么重启后，数据能够根据redo log进行恢复，但是binlog没记录这个语句。那么，我们在利用这个binlog恢复数据的时候，就会出现丢失数据的情形！

mysql怎么解决的?

这里考察的是mysql的内部XA事务！俗称日志的两阶段提交协议！
也就是说，将事务提交分为了两个阶段，prepare阶段和commit阶段！

prepare：写入redo log，并将回滚段置为prepared状态，此时binlog不做操作。

commit：innodb释放锁，释放回滚段，设置提交状态，写入binlog，然后存储引擎层提交。

mysql数据库怎么进行崩溃恢复的?

1> 崩溃恢复时，扫描最后一个Binlog文件，提取其中的xid；
2> InnoDB维持了状态为Prepare的事务链表，将这些事务的xid和binlog中记录的xid做比较，如果在binlog中存在，则提交，否则回滚事务。

4、大事务有哪些坏处?生产上遇到过大事务么？你怎么排查和解决的？

回答:大事务，有的文章又称之为长事务，顾名思义，执行时间很长的事务！

至于坏处，例如事务执行时间太长，会造成大量的阻塞和锁超时，容易造成主从延迟.另外，大事务如果执行失败，回滚也会很耗时…(省略一千字)

怎么排查?so easy！监控information_schema.Innodb_trx表，设置长事务阈值，超过就报警 / 或者 kill;

下面语句是查询持续时间超过60s的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;

一般在生产中，会将监控大事务的语句，配成定时脚本，进行监控。

至于怎么解决？结合业务场景，优化SQL，将一个大事务拆成多个小事务执行，或者缩短事务执行时间即可。

事务拆分那个是不是这样理解的:
原本事务A(线程1)，拆分成事务B(线程2)、事务C(线程3)
流程:
执行第一步: 线程1先设置redis 键值对 例如:  T1(key) 0(value) 30(过期时间，执行有效时间)
执行第二步: 线程1 异步 调用线程2 ，线程2执行事务B ,如果执行成功，就将T1 的 value + 1( redis命令: set T1 (当前值)+1),执行失败不做处理
执行第三步: 线程1 异步 调用线程3 ，线程3执行事务C 同上一样操作
执行第四步: 线程1 执行下列操作
Integer t1 = redis.get('T1');
while(t1  != null &&  t1 < 2){// =2表示两个线程2、3都+1，<2表示两个线程2、3未全部执行完
    //休眠500ms
    thread.sleep(500);
    t1 = redis.get('T1');
}
if(t1 == null){// key到期，大事务超时
    //调用 事务A 预先写好的回滚代码
    businessA.rollBack();
    //调用 事务B 预先写好的回滚代码
    businessB.rollBack();
}
//执行成功返回数据
return data;

5、你有遇到过数据库宕机重启，事务丢失的情况么?

回答:此题考查的是，sync_binlog配置(控制binlog刷盘时机)和innodb_flush_log_at_trx_commit配置(配置redolog刷盘时机)

由于这两个配置的搭配组合很多种，我随意举几个例子

1、innodb_flush_log_at_trx_commit=1 和 sync_binlog=0

这是mysql的默认配置，表示每次事务提交时都将 redo log 直接持久化到磁盘.但是MySQL不控制binlog的刷新，由操作系统自己控制它的缓存的刷新。

如果你答的是这套配置，那风险就是一旦系统宕机，在binlog_cache中的所有binlog信息都会被丢失。于是乎，你可以再结合上自己的公司背景来答。例如，我们公司规模不大，大家没有技术沉淀，因此没有修改过mysql的默认配置。但是，我私底下有过了解(突出自己勤奋好学)，在该配置情况下，这两个参数的值为XXX，理论上操作系统宕机，有XXX的风险。但是，操作系统宕机的几率很低，因此我们也没怎么遇见过事务丢失的情况。

2、innodb_flush_log_at_trx_commit=1 和 sync_binlog=1
MySQL官方说了，双1配置在InnoDB与事务一起使用的复制设置中获得最大可能的持久性和一致性
sync_binlog 参数 — 官方文档

很多文章提到的双1配置。表示每次事务提交时都将redolog直接持久化到磁盘，binlog也会持久化到磁盘。

无疑，这个配置一致性最好2021-06-23…08.45.31.md，不会丢数据，但是性能是最差的。如果你答的是这套配置，你可以这么答，我们系统涉及到一些金钱相关的业务逻辑，宁愿慢，也不能一致性出错，因此我们的配置为XXX。至于性能问题，我们可以通过一些缓存，或者异步化设计进行改良。

3、innodb_flush_log_at_trx_commit=2 和 sync_binlog=0

性能最好的一套配置，表示每次事务提交时,只是把redolog写到OS cache，隔一秒，MySQL主动将OS cache中的数据批量fsync。而MySQL不控制binlog的刷新，由操作系统自己控制它的缓存的刷新。风险就是，操作系统一旦宕机，会丢数据。

这套配置下并发性最好。如果答这个配置，你可以说我们业务对并发的要求相对高一些，因此修改过XX参数。但是有操作系统宕机，丢数据的风险。但是，在实际环境中，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多…(自由发挥)

不全部列举，言之有理即可！记住，全靠一张嘴，说得通就是你的！说不通，回去总结一下，换一家继续说！无他，唯熟尔！

6、可重复读是怎么实现的？

回答:多版本+锁控制。

这道题我只能说一下我的理解，主要有以下两个原因：

（1）、利用间隙锁，防止幻读的出现，保证了可重复读
幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题，这时使用不同的行锁已经没有办法满足要求，需要对一定范围内的数据进行加锁。

（2）、MVCC的快照生成时机不同
在可重复读这个隔离级别下，遇到当事务中的第一个SELECT请求才创建read view，因此你是无法读到其他事务提交的更改。而在读已提交这个隔离级别下，每个SELECT都会获取最新的read view，因此你能读到其他事务提交的数据。

因为，这个View生成时机不同，所以实现了可重复读。
此题答案，有不同见解可以讨论一下。反正我是这样理解的！

总结

OK，希望本文大家有所收获！

本文其实对事务方面的问题没讲全，因为在面试的时候通常会结合spring的事务和分布式事务来问，例如什么情况下spring事务会失效啊，巴拉巴拉。

补充面试题

2021-06-24 07:42:57

参考自：mysql底层原理解析（一）之日志

1、WAL 机制是减少磁盘写，可是每次提交事务都要写 redo log 和 binlog，这磁盘读写次数也没变少呀？

WAL 机制主要得益于两个方面：
（1）redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
（2）组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

2、如果 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？

（1）设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
（2）将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
（3）将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。

3、为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？(主从同步后后来讨论)

（1）MySQL 这么设计的主要原因是，binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。（一个线程只能同时有一个事务在执行，由于这个设定，所以每当执行一个begin/start transaction的时候，就会默认提交上一个事务，这样如果一个事务的binlog被拆开的时候，在备库执行就会被当做多个事务分段自行，这样破坏了原子性，是有问题的。）
（2）而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中。redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

4、事务执行期间，还没到提交阶段，如果发生 crash 的话，redo log 肯定丢了，这会不会导致主备不一致呢？

不会。因为这时候 binlog 也还在 binlog cache 里，没发给备库。crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的。

5、只用 redo log，不要 binlog？

如果只从崩溃恢复的角度来讲是可以的。你可以把 binlog 关掉，这样就没有两阶段提交了，但系统依然是 crash-safe 的。
redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用。

6、redo log 一般设置多大？

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

7、正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

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

8、redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;
insert into t1 ...
insert into t2 ...
commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。

所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。

但是，真正把日志写到 redo log 文件，是在执行 commit 语句的时候做的。
可参考：两阶段提交 — MySQL 的 crash-safe 原理解析

9、redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：
（1）如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
（2）如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务，找到提交，找不到去找undo log回滚。
可参考：数据恢复流程 — MySQL 的 crash-safe 原理解析

10、只用 binlog 来支持崩溃恢复，又能支持归档？

binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”，所以，binlog 没有能力恢复“数据页”。
如果优化一下 binlog 的内容，让它来记录数据页的更改可以吗？但，这其实就是又做了一个 redo log 出来。