通过面试题学MySQL基础篇

欢迎来我的个人网站，里面有最新的版本

这篇介绍了下Mysql面试题常考内容，并做了延伸；个人觉得MVCC部分讲的应该是网上较为清楚的了。

参考：

https://www.codercto.com/a/88775.html

https://baijiahao.baidu.com/s?id=1629409989970483292&wfr=spider&for=pc

如果有图片失效的情况，可能是因为我用的github图库，网站无法爬到，大家可以进入个人网站进行阅读。

一、常见面试题

mysql 有那些存储引擎，有哪些区别（innodb 与myisam 的区别？ ）

mysql 索引在什么情况下会失效

mysql 的索引模型

数据库为什么用B+树。联合索引特点

mysql 主从同步怎么搞的？分哪几个过程？如果有一台新机器要加到从机里，怎么个过程。

乐观锁与悲观锁的区别？

binlog 日志是 master 推的还是 salve 来拉的？

数据库如何实现并发；可重复读是什么；有一段代码，两个session，判断结果是什么（和重复读有关）；数据库用什么实现可重复读。

二、存储引擎

2.1 什么是存储引擎

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

2.2 存储引擎分类

使用show engines;可查看引擎种类，有MEMORY、ARCHIVE、MERGE等等，主要的是MyISAM和InnoDB。

2.3 MyISAM和InnoDB的区别

1. 存储结构

   • MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义，数据文件的扩展名为.MYD(MYD)，索引文件的扩展名是.MYI(MYIndex)。

   • InnoDB:所在的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2. 存储空间

   • MyISAM:可被压缩，存储空间较小。支持三种不同的存储格式：静态表、动态表、压缩表。

   • InnoDB:需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3. 事物支持

   • MyISAM:强调的是性能，每次查询具有原子性，其执行速度比Innodb类型更快，但是不提供事物支持。

   • InnoDB:提供事务支持。具有提交（commit）、回滚（rollback）和崩溃修复能力（crach recovery capabilities）的事务安全（transaction-safe ACID compliant）型表。

4. CURD操作

   • MyISAM: 如果执行大量的select, MyISAM是更好的选择。（因为没有支持行级锁），在增删的时候需要锁定整个表格，效率会低一些。相关的是

   • InnoDB:如果你的数据执行大量的insert或update，出于性能方面的考虑，应该使用InnoDB表。innoDB支持行级锁，删除插入的时候只需要锁定该行就行，效率较高。但delete from table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

5. 外键

   • MyISAM: 不支持。

   • InoDB:支持。

6. 索引

   • MyISAM:采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。支持全文索引和空间索引。

   • InnoDB:主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问主键索引。

三、索引

3.1 索引是什么

索引(Index)是帮助MySQL高效获取数据的数据结构。可以简单理解为：快速查找排好序的一种数据结构，可以提高数据检索的效率，降低数据库的IO成本

3.1.1为什么要使用索引

查询数据时需要从磁盘中全表扫描读取数据，会遇上两个问题

• 数据量过大的时候，从磁盘中读数据到内存，内存是否有足够容量存放
• 如果表非常大，那么我们不可能一次将所有的数据读到内存，需要分多次读取磁盘，而操作磁盘相对于内存来说是一个非常耗时的操作

优点：

1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
2. 大大加快数据的查询速度
3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点：

1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

2. 索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值

3. 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

使用原则：

通过上面说的优点和缺点，我们应该可以知道，并不是每个字段度设置索引就好，也不是索引越多越好，而是需要自己合理的使用。

加在哪些列上？

1. 在经常需要搜索的列上，可以加快搜索的速度；
2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

不该加在哪些列上？

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
2. 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3. 第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
4. 第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

3.2 索引分类

按照索引形式不同可以分为：

• 普通索引：仅加速查询

• 唯一索引：加速查询 + 列值唯一（可以有null）

• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• 全文索引：对文本的内容进行分词，进行搜索

• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

按照索引实现方式不同可以分为：

	描述	特点	使用场景
B+ Tree索引	使用B+ Tree作为底层实现	对树进行搜索，查找速度快分为聚簇索引和非聚簇索引	查找、排序、分组
哈希索引	使用哈希作为底层实现	无法用于排序与分组只支持精确查找，时间复杂度为O(1)	当索引值使用的频繁时，会在B+ Tree索引之上再创建一个哈希索引
全文索引	全文索引使用倒排索引实现，记录着关键词到其所在文档的映射		查找文本中的关键词
空间索引		从所有维度来索引数据	用于地理数据存储

3.3 索引数据结构

数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而“矮胖”就是b树的特征之一，它的每个节点最多包含m个孩子，m称为b树的阶，m的大小取决于磁盘页的大小。

通过对比各种数据结构的特点，可以回答为什么要用B+Tree作为数据库的索引的问题

和其他数据结构对比来看：

二叉树：如果数据是单边增长的情况，那么出现的就是和链表一样的数据结构了，树高度大。

红黑树：在二叉树的基础上多了树平衡，也叫二叉平衡树，不像二叉树那样极端的情况会往一个方向发展，但数据量大的话，红黑树的深度会很深，也就是说深度不可控，这样一来查找数据还是会很耗时。

Hash表：通过hash函数计算出数据映射位置，相比较于红黑树，hash可以固定“深度”，且映射到磁盘存储引用，但是 hash 还是有些不足：只能用于精确查找的场景，无法进行范围查询。

BTree：每个节点是一个二元数组，存放着key，value， 从左到右递增排列。节点之间存放着指向这相应区间范围内的节点。然而也存在着一些缺陷：

1. 在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。造成IO操作频繁。
2. 区间查找可能需要返回上层节点重复遍历，IO操作繁琐。

B+Tree：非叶子结点不存储data，只存储索引（减小内存占用，这样可以多读入一些节点）

叶子结点包含所有索引字段，存放所有的data

叶子结点用指针连接，提高区间访问的性能（不用回到上层节点再往下找）

b+树相比于b树的查询优势：

1. 一次IO只能加载一个磁盘页（对应一个节点），b树的节点是由key,data组成的，而b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，可以使得树更加矮胖；
2. b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；
3. 对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历，如下两图：

总结：B+Tree 既减少查询次数又提供了很好的范围查询

3.4 MyISAM和InnoDB索引实现

索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。它们实现的都是B+Tree索引模型

3.4.1 MyISAM实现

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

• MyISAM的主索引图：索引文件的每个数据域存储指向数据文件的指针(每个索引指向了数据地址)

  

• MyISAM的辅索引：索引文件的每个数据域存储指向数据文件的指针(每个索引指向了数据地址)，辐索引不用保证唯一性。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

  

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分

3.4.2 InnoDB实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

Innodb的主索引图: 叶子节点存储数据本身

Innodb的辐索引图: 叶子结点存储主键的值。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

3.5 其他关于索引的问题

主键索引三问：

• 为什么非主键索引结构叶子节点存储的是主键值？

• 一是保证一致性，更新数据的时候只需要更新主键索引树，二是节省存储空间。

• 为什么推荐InnoDB表必须有主键？

• 保证会有主键索引树的存在（因为数据存放在主键索引树上面），如果没有mysql会自己生成一个rowid作为自增的主键主键索引

• 为什么推荐使用整型的自增主键？

  一是方便查找比较，而是新增数据的时候只需要在最后加入，不会大规模调整树结构，如果是UUID的话，大小不好比较，新增的时候也极有可能在中间插入数据，会导致树结构大规调整，造成插入数据变慢。

索引合并：使用多个单列索引组合搜索

索引覆盖：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

索引下推：如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

对于user_table表，我们现在有（username,age）联合索引
如果现在有一个需求，查出名称中以“张”开头且年龄小于等于10的用户信息，语句C如下：

select * from user_table where username like '张%' and age > 10

语句C有两种执行可能：

1. 根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄小于等于10的用户数据。

2. 根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据。

明显的，第二种方式需要回表查询的全行数据比较少，这就是mysql的索引下推

注意点：
1、innodb引擎的表，索引下推只能用于二级索引。

就像之前提到的，innodb的主键索引树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

2、索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。

假设表t有联合索引（a,b）,下面语句可以使用索引下推提高效率
select * from t where a > 2 and b > 10;

索引失效

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

2. 使用查询的时候遵循mysql组合索引的"最左前缀"规则，假设现在有组合索引（a，b，c），查询语句就只能是a=1或a=1and b=1或a=1 and b=1 and c=1。这里有两点需要注意

   • a=1 and b=1和b=1 and a=1一样，没有区别，都会使用索引。b = 1和b = 1 and c=1无法使用索引
   • 组合索引（a，b，c）的最左前缀是a；组合索引（c，b，a）的最左前缀是c，最左前缀和表字段顺序无关。在组合索引中，如果where查询条件中某个列使用了范围查询（不管%在哪），则其右边的所有列都无法使用索引优化查询

3. like查询以%开头

4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

6. 索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询

SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');

四、事务

4.1 特性ACID

原子性(Atomicity)：指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做

一致性(Consistency)：事务使得系统从一个一致的状态转换到另一个一致状态。事务的一致性决定了一个系统设计和实现的复杂度，也导致了事务的不同隔离级别。

隔离型(Isolation)：事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性(Durability)：事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

4.2 实现原理

首先思考事务想要做到什么效果？

按我理解，无非是要做到可靠性以及并发处理

可靠性：数据库要保证当insert或update操作时抛异常或者数据库crash的时候需要保障数据的操作前后的一致，想要做到这个，我需要知道我修改之前和修改之后的状态，所以就有了undo log和redo log。

并发处理：也就是说当多个并发请求过来，并且其中有一个请求是对数据修改操作的时候会有影响，为了避免读到脏数据，所以需要对事务之间的读写进行隔离，至于隔离到啥程度得看业务系统的场景了，实现这个就得用MySQL 的隔离级别。

4.2.1 原子性

要实现原子性，即需要保证操作要么全做，要么全不做，对于做到一半无法完成的操作就应该进行回滚。那么就需要有日志文件记录数据被修改前的信息，这样才能达到回到修改前的状态，undo log 回滚日志就实现了这一需求。

4.2.2 持久性

事务一旦提交，对数据库的改变就该是永久性的。那么由于数据是存放在磁盘中的，如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。但如果MySQL宕机了，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是引入redo log，当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

4.2.3 隔离性

事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。主要考虑读写操作下的隔离，可以分为两个方面：

• (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
• (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

4.2.4 一致性

事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。

实现一致性的措施包括：

• 保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
• 数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
• 应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

4.2.5 并发一致性问题

• 丢失修改：一个事务对数据进行了修改，在事务提交之前，另一个事务对同一个数据进行了修改，覆盖了之前的修改；

• 脏读（Dirty Read）：一个事务读取了被另一个事务修改、但未提交（进行了回滚）的数据，造成两个事务得到的数据不一致；

• 不可重复读（Nonrepeatable Read）：在同一个事务中，某查询操作在一个时间读取某一行数据和之后一个时间读取该行数据，发现数据已经发生修改；

• 幻读（Phantom Read）：当同一查询多次执行时，由于其它事务在这个数据范围内执行了插入删除操作，会导致每次返回不同的结果集（和不可重复读的区别：针对的是一个数据整体/范围；并且需要是插入操作或删除）

4.2.6 隔离级别

MySQL数据的四种隔离级别：

1. Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

2. Read committed (读已提交)：可避免脏读的发生。

3. Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

4. Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。
像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，
所以平时选用何种隔离级别应该根据实际情况。
在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

五、锁机制-行锁与表锁

锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

5.1 什么是乐观锁和悲观锁

• 悲观锁：认为数据随时会被修改，因此每次读取数据之前都会上锁，防止其它事务读取或修改数据；应用于数据更新比较频繁的场景；
• 乐观锁：操作数据时不会上锁，但是更新时会判断在此期间有没有别的事务更新这个数据，若被更新过，则失败重试；适用于读多写少的场景。

乐观锁的实现方式有：

• 加一个版本号或者时间戳字段，每次数据更新时同时更新这个字段；
• 先读取想要更新的字段或者所有字段，更新的时候比较一下，只有字段没有变化才进行更新

六、MVCC

6.1 什么是MVCC

多版本并发控制 ，是现代数据库（包括 MySQL 、 Oracle 、 PostgreSQL 等）引擎实现中常用的处理读写冲突的手段， 目的在于提高数据库高并发场景下的吞吐性能 。

作用：

如此一来不同的事务在并发过程中， SELECT 操作可以不加锁而是通过 MVCC 机制读取指定的版本历史记录，并通过一些手段保证保证读取的记录值符合事务所处的隔离级别，从而解决并发场景下的读写冲突。

6.2 什么是读写冲突

先来看下什么是版本冲突，也就知道为什么要引入MVCC解决版本冲突问题。

设想一种常见，事务A begin之后修改了数据但还没commit或者rollback这段时间内，事务B select查找数据，那事务B读到的值应该是什么？很明显，理论上来说，既然还没提交，那肯定B读到的是旧数据，但在不同隔离级别下，B读到的值也会不一样！

1. 如果事务 B 的隔离级别是读未提交（RU），那么两次读取均读取到 x 的最新值，即 20 。
2. 如果事务 B 的隔离级别是读已提交（RC），那么第一次读取到旧值 10 ，第二次因为事务 A 已经提交，则读取到新值 20。
3. 如果事务 B 的隔离级别是可重复读或者串行（RR，S），则两次均读到旧值 10 ，不论事务 A 是否已经提交。

可见在不同的隔离级别下，数据库通过 MVCC 和隔离级别，让事务之间并行操作遵循了某种规则，来保证单个事务内前后数据的一致性。

注意：这里大家会发现RR和S隔离级别读到的数据是一样的，但实现原理不同，RR是由MVCC实现，S由加锁来实现。因此，MVCC用于RC和RR级别下。

6.3 为什么需要MVCC

6.3.1 事务所带来的问题

InnoDB 相比 MyISAM 有两大特点，一是支持事务而是支持行级锁，事务的引入带来了一些新的挑战。相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况：

1. 更新丢失（ Lost Update ）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题 —— 最后的更新覆盖了其他事务所做的更新。如何避免这个问题呢，最好在一个事务对数据进行更改但还未提交时，其他事务不能访问修改同一个数据。
2. 脏读（ Dirty Reads ）：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些尚未提交的脏数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做 “脏读” 。
3. 不可重复读（ Non-Repeatable Reads ）：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。
4. 幻读（ Phantom Reads ）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为 “幻读” 。

以上是并发事务过程中会存在的问题，解决更新丢失可以交给应用，但是后三者需要数据库提供事务间的隔离机制来解决。 实现隔离机制的方法主要有两种 ：

1. 加读写锁
2. 一致性快照读，即 MVCC

但本质上，隔离级别是一种在并发性能和并发产生的副作用间的妥协，通常数据库均倾向于采用 Weak Isolation

总结来说，使用MVCC的开销比使用加读写锁的开销来的小，所以需要MVCC。

6.4 MVCC原理

6.4.1 行记录结构

InnoDB 中 MVCC 的实现方式为：每一行记录都有两个隐藏列： DATA_TRX_ID 、 DATA_ROLL_PTR

column1	column1	……	DATA_TRX_ID	DATA_ROLL_PTR	DB_ROW_ID
id	name		事务版本号	上一版本地址	没有主键的时候才出现

实现MVCC，最主要的就是 DATA_TRX_ID 、 DATA_ROLL_PTR这两个隐藏列

DATA_TRX_ID：记录最近更新这条行记录的 事务 ID ，大小为 6 个字节。也就是标记是哪个事务修改的。

DATA_ROLL_PTR：表示指向该行回滚段 （rollback segment） 的指针，大小为 7 个字节， InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在 undo 中都通过链表的形式组织。

这里需要大家了解了上文讲到的事务原子性由undo log记录更改前的版本来实现这部分知识。

6.4.2 版本链

在多个事务并行操作某行数据的情况下，不同事务对该行数据的 UPDATE 会产生多个版本，然后通过回滚指针组织成一条 Undo Log 链，这节我们通过一个简单的例子来看一下 Undo Log 链是如何组织的， DATA_TRX_ID 和 DATA_ROLL_PTR 两个参数在其中又起到什么样的作用。

事务200的操作过程

1. 对 column1 = 20 的这行记录加排他锁
2. 将column1 = 20 的这一行原本的值原样拷贝到 undo log 中
3. 修改该行的值这时产生一个新版本，更新 DATA_TRX_ID 为修改记录的事务 ID ，将 DATA_ROLL_PTR 指向刚刚拷贝到 undo log 链中的旧版本记录。如果对同一行记录执行连续的 UPDATE ， Undo Log 会组成一个链表，遍历这个链表可以看到这条记录的变迁。
4. 记录 redo log ，包括 undo log 中的修改

那么 INSERT 和 DELETE 会怎么做呢？其实相比 UPDATE 这二者很简单， INSERT 会产生一条新纪录，它的 DATA_TRX_ID 为当前插入记录的事务 ID ； DELETE 某条记录时可看成是一种特殊的 UPDATE ，其实是软删，真正执行删除操作会在 commit 时， DATA_TRX_ID 则记录下删除该记录的事务 ID 。

6.4.3 ReadView

上面说了，通过版本链的方式控制不同隔离级别下，并发事务读到的版本不一样。那事务怎么知道自己能读哪些版本呢？这就由ReadView来解决，读已提交（RC）和可重复读（RR）的生成ReadView策略是不一样的。

ReadView中主要就是有个列表来存储我们系统中当前活跃着的读写事务，也就是begin了还未提交的事务。通过这个列表来判断记录的某个版本是否对当前事务可见。

总结：ReadView记录了当前活跃着的读写事务id的列表，称之为 m_ids，它有个[ up_limit_id，low_limit_id]范围（是的，没写错，up_limit_id就是左界限），这个左右界限就决定了哪些版本的数据是事务可以访问的。

1. 如果被访问版本的 trx_id 小于 m_ids 中的最小值 up_limit_id ，说明生成该版本的事务在 ReadView 生成前就已经提交了，所以该版本可以被当前事务访问。
2. 如果被访问版本的 trx_id 大于 m_ids 列表中的最大值 low_limit_id ，说明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。需要根据 Undo Log 链找到前一个版本，然后根据该版本的 DB_TRX_ID 重新判断可见性。
3. 如果被访问版本的 trx_id 属性值在 m_ids 列表中最大值和最小值之间（包含），那就需要判断一下 trx_id 的值是不是在 m_ids 列表中。如果在，说明创建 ReadView 时生成该版本所属事务还是活跃的，因此该版本不可以被访问，需要查找 Undo Log 链得到上一个版本，然后根据该版本的 DB_TRX_ID 再从头计算一次可见性；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。
4. 此时经过一系列判断我们已经得到了这条记录相对 ReadView 来说的可见结果。此时，如果这条记录的 delete_flag 为 true ，说明这条记录已被删除，不返回。否则说明此记录可以安全返回给客户端。

举个例子吧

假设当前列表里的事务id为[80,100]。

• 如果你要访问的记录版本的事务id为50，比当前列表最小的id80小，那说明这个事务在之前就提交了，所以对当前活动的事务来说是可访问的。
• 如果你要访问的记录版本的事务id为90, 发现此事务在列表id最大值和最小值之间，那就再判断一下是否在列表内，如果在那就说明此事务还未提交，所以版本不能被访问。如果不在那说明事务已经提交，所以版本可以被访问。
• 如果你要访问的记录版本的事务id为110，那比事务列表最大id100都大，那说明这个版本是在ReadView生成之后才发生的，所以不能被访问。

这些记录都是去版本链里面找的，先找最近记录，如果最近这一条记录事务id不符合条件，不可见的话，再去找上一个版本再比较当前事务的id和这个版本事务id看能不能访问，以此类推直到返回可见的版本或者结束。

6.4.4 不同隔离级别下的ReadView

大致的流程就像上面说讲的那样，但是根据不同的ReadView也会有不一样的结果。这是因为已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView

1. RC下的MVCC判断流程

   当事务 A 未提交时，事务 B 进行查询，假设事务 B 的事务 ID 为 300 ，此时生成 ReadView 的 m_ids 为 [200，300]，而最新版本的 trx_id 为 200 ，处于 m_ids 中，则该版本记录不可被访问，查询版本链得到上一条记录的 trx_id 为 100 ，小于 m_ids 的最小值 200 ，因此可以被访问，此时事务 B 就查询到值 10 而非 20 。

   待事务 A 提交之后，事务 B 进行查询，此时生成的 ReadView 的 m_ids 为 [300]，而最新的版本记录中 trx_id 为 200 ，小于 m_ids 的最小值 300 ，因此可以被访问到，此时事务 B 就查询到 20 。

2. RR下的MVCC判断流程

   如果在 RR 隔离级别下，为什么事务 B 前后两次均查询到 10 呢？ RR 下生成 ReadView 是在事务开始时，m_ids 为 [200,300]，后面不发生变化，因此即使事务 A 提交了， trx_id 为 200 的记录依旧处于 m_ids 中，不能被访问，只能访问版本链中的记录 10 。