MySQL常见面试问题整理（三）：MySQL事务

1.MyISAM 与 InnoDB 的区别是什么？

从八个方面来讲：

1. InnoDB支持事务，MyISAM不支持。

2. InnoDB 支持外键，而 MyISAM 不支持。

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和索引绑在一起的，必须要有主键。MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB 不保存表的具体行数。MyISAM 用一个变量保存了整个表的行数。用一个变量保存整个表的行数能够加快对表行数查询语句的反应，innoDB没有这样做的理由是MVCC（多版本并发控制），即使是在同一个时刻的多个查询，InnoDB
   表“应该返回多少行”也是不确定的。innoDB的处理方式是把数据一行一行地从引擎里面读出来，然后累积计数。但是在执行count(*)操作的时候InnoDB还是做了优化的，
   普通索引树比主键索引树小很多。对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

5. Innodb 有 redolog日志文件，MyISAM 没有。（redolog日志文件可以保证crush-safe）

6. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
   Innodb：frm是表定义文件，ibd是数据文件
   Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

7. InnoDB 支持表、行锁，默认为行级锁。，而 MyISAM 支持表级锁

8. InnoDB 必须有唯一索引(主键),如果没有指定的话 InnoDB 会自己生成一个隐藏列Row_id来充当默认主键(因为数据文件是和索引绑在一起的，必须要有主键)，MyISAM 可以没有。

2.什么是事务？事务的ACID特性指的是？ACID各自的实现原理？

事务的ACID特性

事务是指是程序中一系列操作必须全部成功完成，有一个失败则全部失败。

ACID特性指的是：

1. 原子性（Atomicity）：要么全部执行成功，要么全部不执行。原子性可以理解为不可分割。
2. 一致性（Consistency）：事务前后数据的完整性必须保持一致。
3. 隔离性（Isolation）：隔离性是当多个事务同事触发时，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
4. 持久性（Durability）：事务一旦提交，它对数据库的改变就应该是永久性的，接下来的其他操作或故障不应该对本次事务的修改有任何影响。

ACID各自的实现原理

1. 原子性的实现依赖undolog回滚日志。每一条数据变更（insert/update/delete）操作都伴随一条undo log的生成，并且回滚日志必须先于数据持久化到磁盘上。所谓的回滚就是根据回滚日志做逆向操作，比如delete的逆向操作为insert，insert的逆向操作为delete，update的逆向为update等。

2. 数据库的一致性依赖于其他三种特性：原子性，隔离性，持久性。所谓一致性，指的是数据处于一种有意义的状态,例如从帐户A转一笔钱到帐户B上,A账户上减少的钱数等于B账户上增加的钱数才是一种有意义的状态。

3. 隔离性通过锁机制来实现。使用锁机制，保证每个事务能够看到的数据总是一致的，就好像其他事务不存在一样，多个事务并发执行后的状态和它们串行执行后的状态是等价的。原则上一般是两种类型的锁：乐观锁和悲观锁。

4. 持久性通过redo log来保证。Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。但是如果事务提交前直接把数据写入磁盘太浪费IO资源，因此使用redo log来解决持久性和读写IO消耗严重之间的平衡问题，当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘（redo log一部分在内存一部分在磁盘）。当数据库宕机重启会将redolog中的内容恢复到数据库再根据undo log和binlog内容决定回滚数据还是提交数据。

3.什么是WAL（Write-Ahead Logging，预写日志）机制？为什么需要WAL机制？WAL具体过程？有什么好处？

什么是WAL

主要是指MySQL在执行写操作的时候并不是立刻更新到磁盘上，而是先记录在日志中，之后在合适的时间更新到磁盘中。日志主要分为undo log、redo log、binlog、relaylog。

为什么需要WAL

如果不采用预写日志机制，而直接采用磁盘更新数据，首先会增加IO资源消耗；其次磁盘存数据采用的是随机存储的方式，这就使得在存放数据的时候不仅需要记录下存放的数据值，还需要记录存放数据的地址，存储速度相对比较慢。而日志存储是连续存储，因此在存数据的时候只需要记录下首地址即可，其余数据记录偏移量，可以进一步提高性能。

WAL具体过程

有什么好处

总结而言，好处是保证了数据操作的原子性和持久性，具体如下：

1. 「读和写可以完全地并发执行」，不会互相阻塞
2. 先写入 log 中，磁盘写入从「随机写变为顺序写」，降低了 client 端的延迟。并且，由于顺序写入大概率是在一个磁盘块内，这样产生的 io 次数也大大降低
3. 写入日志当数据库崩溃的时候「可以使用日志来恢复磁盘数据」

4.什么是脏读？幻读？不可重复读？

1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

3、幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读侧重于修改，幻读侧重于新增或删除（多了或少量行），脏读是一个事务回滚影响另外一个事务。

5. 事务的隔离级别

Read Uncommitted（读未提交）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读提交）
这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓 的 不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一 select 可能返回不同结果。

Repeatable Read（可重复读）
这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。

Serializable（可串行化）
通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

四种情况分别举例如下：假设数据表T中只有一列，其中一行的值为1，下面是按照时间顺序执行两个事务的行为。

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

在不同的隔离级别下，事务A会有哪些不同的返回结果，也就是图里面V1、V2、V3
的返回值分别是什么？

1. 若隔离级别是“读未提交”， 则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被
   A看到了。因此，V2、V3也都是2。
2. 若隔离级别是“读提交”，则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以，
   V3的值也是2。
3. 若隔离级别是“可重复读”，则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求：
   事务在执行期间看到的数据前后必须是一致的。
4. 若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，
   事务B才可以继续执行。所以从A的角度看， V1、V2值是1，V3的值是2。

6. 快照读和当前读

7. redolog\undolog\binlog\relaylog的作用和区别

1. redolog: 是 「InnoDB 存储引擎所特有的一种日志」，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。可以做「数据恢复并且提供 crash-safe 能力。当有一条记录需要更新（增删改）的时候，InnoDB引擎就会先把记录写到redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面。
   InnoDB 的 redo log 是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生内存抖动现象，从肉眼的角度来观察会发现 mysql 会宕机一会儿，此时就是正在刷盘了。

2. undolog:undolog 是 InnoDB 存储引擎的日志，用于保证数据的原子性，保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修改之前的数据，主要作用是事务回滚以及实现多版本控制(MVCC)。
   redolog重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

3. binlog:归档日志，属于 Server 层的日志，是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息。主要用于主从复制和数据恢复。

4. relaylog ：是中继日志，在主从同步的时候使用到，它是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容。

8.redolog\undolog\binlog\relaylog的具体工作过程

9. redolog、binlog、undolog的比较

1. undo log是用于事务的回滚、保证事务隔离级别读已提交、可重复读实现的。redo log是用于对暂不更新到磁盘上的操作进行记录，使得其可以延迟落盘，保证程序的效率。bin log是对数据操作进行备份恢复（并不能依靠 bin log 直接完成数据恢复）。

2. undo log 与 redo log 是存储引擎层的日志，只能在 InnoDB 下使用；而bin log 是 Server 层的日志，可以在任何引擎下使用。

3. redo log 空间固定大小有限，超过后会循环写；另外两个是可以追加写入的。“追加写”是指log文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。。

4. undo log 记录的是行记录变化前的数据；redo log 记录的是 sql 的数据页修改逻辑以及 change buffer 的变更；bin log记录操作语句对具体行的操作以及操作前的整行信息（5.7默认）或者sql语句。

5. 单独的 binlog 没有 crash-safe 能力，也就是在异常断电后，之前已经提交但未更新的事务操作到磁盘的操作会丢失，也就是主从复制的一致性无法保障，而 redo log 有 crash-safe 能力，通过与 redo log 的配合实现 “三步提交”，就可以让主从库的数据也能保证一致性。

6. redo log 是物理日志，它记录的是数据页修改逻辑以及 change buffer 的变更，即“在某个数据页上做了什么修改”，只能在当前存储引擎下使用；而 binlog 是逻辑日志，它记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”，在任何存储引擎下都可以使用。

10.为什么要redolog?不能直接写磁盘嘛？

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。

11. 两阶段提交过程？redolog怎么保证crash-safe?

每一个写事务，都会修改BufferPool，从而产生相应的Redo/Undo日志，这些日志信息会被记录到日志文件中。在MySQL中，任何BufferPool中的页被刷新到磁盘之前，都会先写入到日志文件中，如果BufferPool中的数据提交，此时数据库挂了，那么在数据库再次启动之后，就可以通过Redo日志来将其恢复出来，以此来保证写的数据不会丢失。如果数据没有提交，此时数据库挂了，就需要通过Undo日志来实现。

12. undolog配合完成MVCC（多版本并发控制）的过程

13.Mysql 中有哪些锁?

原则上一般是两种类型的锁：乐观锁和悲观锁。

1. 悲观锁，即当前事务将所有涉及操作的对象加锁，操作完成后释放给其它对象使用。为了尽可能提高性能，发明了各种粒度（数据库级/表级/行级……）/各种性质（共享锁/排他锁/共享意向锁/排他意向锁/共享排他意向锁……）的锁。为了解决死锁问题，又发明了两阶段锁协议/死锁检测等一系列的技术。

2. 乐观锁，即不同的事务可以同时看到同一对象（一般是数据行）的不同历史版本。如果有两个事务同时修改了同一数据行，那么在较晚的事务提交时进行冲突检测。实现也有两种，一种是通过日志UNDO的方式来获取数据行的历史版本，一种是简单地在内存中保存同一数据行的多个历史版本，通过时间戳来区分。

基于锁的属性分类：共享锁、排他锁
基于锁的粒度分类：表锁、行锁、记录锁、间隙锁、临键锁
基于锁的状态分类：共享意向锁、排它意向锁、死锁

悲观锁

当前事务设计操作的对象加锁，操作完成后释放给其它对象使用。

提高性能->{各种粒度：数据库/表/行/各种性质：共享/排他/共享意向/排他意向/共享排他意向}

解决死锁->{两阶段协议/死锁检测}

乐观锁

不同事物可同时可能到同一对象的不同历史版本。使用MVCC（多版本并发控制，Multi-Version Concurrency Control），可在事务提交前查看修改事务的版本，如果与读取版本不一致可放弃提交

如果有两个事务同时修改了同一数据行，那么在较晚的事务提交时进行冲突检测。实现:通过undo log来获取历史版本，另一种简单地在内存中保存同一数据行的多个历史版本，通过时间戳来区分。

参考：

【1】公众号【moon聊技术 】：MySQL 常见面试题总结！
【2】Mysql45讲—极客时间 林晓斌
【3】事务的特性——原子性（实现原理）
【4】数据库事务原子性、一致性是怎样实现的？
【5】MySQL 中的WAL机制
【6】数据库——彻底明白超键、候选键、主键、外键
【7】MySQL中In与Exists的区别
【8】MySQL 16 问！IT邦德