MySQL面试总结

1.MySQL基本逻辑架构

下面是 MySQL 的基本架构示意图，从中你可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎，索引也是在存储引擎层实现的

其实下面还可以细分出一个存储层， 主要是将数据（如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等）存储在文件系统之上，并完成与存储引擎的交互

从图中不难看出，不同的存储引擎共用一个Server 层，也就是从连接器到执行器的部分

2.一条查询语句是怎么执行的？

比如，执行下面这个查询语句

select * from T where ID=10；

接下来，就根据上面的MySQL逻辑架构来梳理一下：

1.连接器

第一步，通过连接器连接到MySQL服务端。连接器负责跟客户端建立连接、获取权限、维持和管理连接。一般是通过一个连接命令，输入用户名密码，在完成经典的 TCP 握手后，连接器就开始认证你的身份，这个时候用的就是你输入的用户名和密码

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限

2.查询缓存

连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端

如果查不到，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中

需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了

3.分析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析，首先是进行词法分析和语法分析，词法分析就是识别出SQL语句里的字符串分别是什么，比如"select"代表这是一个查询语句，字符串“T”识别成“表名 T”。

做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如下面这个语句 select 少打了开头的字母“s”

4.优化器

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理

比如说在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

5.执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

比如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行
3. 执行器将上述遍历过程中所有满足条件的行组成结果集返回给客户端

至此，这个语句就执行完成了

对于有索引的表，执行的逻辑也差不多。第一次调用的是 “取满足条件的第一行” 这个接口，之后循环取 “满足条件的下一行” 这个接口，这些接口都是引擎中已经定义好的

3.存储引擎

• 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式，不同存储引擎的这些操作实现方式不同

• 存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表类型

三种主要存储引擎：

1.InnoDB

在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎

特点：

• DML 操作遵循 ACID 模型，支持事务
• 行级锁，提高并发访问性能
• 支持外键约束，保证数据的完整性和正确性

文件：

• xxx.ibd：xxx是表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引

2.MyISAM

MyISAM 是 MySQL 早期的默认存储引擎

特点：

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

这种存储引擎的表，在磁盘中涉及三种文件：

• xxx.sdi：存储表结构信息
• xxx.MYD：存储数据（D：数据）
• xxx.MYI：存储索引（I：索引）

3.Memory

Memory 引擎的表数据是存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用

特点：

• 存放在内存中，速度快
• 默认使用的索引是Hash索引

文件：

• xxx.sdi：存储表结构信息

InnoDB引擎与MyISAM引擎的区别 ?

1. InnoDB引擎，支持事务；而MyISAM不支持

2. InnoDB引擎，支持行锁和表锁；而MyISAM仅支持表锁，不支持行锁

3. InnoDB引擎，支持外键；而MyISAM是不支持的

存储引擎的选择？

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合

• InnoDB：如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择
• MyISAM：如果应用是以插入和查询操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的
• Memory：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

日志、电商中的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎

MyISAM和Memory用的比较少：

• 在使用MyISAM的场景中，被NoSQL系列的数据库MongoDB替代了
• 在使用Memory的场景中，被Redis替代了

4.InnoDB存储引擎

了解一下InnoDB存储引擎的结构信息，有助于我们学习后面的事务，日志等

逻辑存储结构

首先是InnoDB的逻辑存储结构，简单来说，从大到小为：表空间（ibd文件）、段、区、页、行

其中，页是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区

InnoDB结构

分为 内存结构 和 磁盘结构

在内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer（日志缓冲区）。 接下来介绍一下这四个部分：

1.Buffer Pool（缓冲池）

缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，**在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘

那么我们为什要这样在缓冲池里操作数据，而不是直接在磁盘上修改数据？

InnoDB存储引擎基于磁盘文件存储，而访问物理硬盘和在内存中进行访问的速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，从而减少磁盘IO，加快处理速度

在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

• free page：空闲page，未被使用。
• clean page：被使用page，数据没有被修改过。
• dirty page：脏页，被使用page，数据被修改过，页中数据与磁盘的数据不一致，就是缓冲区的数据还没刷新到磁盘

2.Change Buffer（更改缓冲区）

Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

Change Buffer的意义是什么呢?

先来看一幅图，这个是二级索引的结构图：

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO

3.Adaptive Hash Index（自适应哈希索引）

自适应hash索引，用于优化对Buffer Pool数据的查询

InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引，自适应哈希索引，无需人工干预，是系统根据情况自动完成，所以叫自适应

4.Log Buffer（日志缓冲区）

日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log buffer、undo log），日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O

下面是磁盘结构：

磁盘结构有好几个，只需要了解下面的就行

1.Undo Tablespaces

撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（undo001 和 undo002）（初始大小16M），用于存储undo log日志

2.Doublewrite Buffer Files

双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，为了保证数据的安全，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据

3.Redo Log（重做日志）

重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时，发生错误时, 进行数据恢复使用

redo log是循环写的，会定期清理。当事务提交后，redo log 的意义就不大了，就会被清理掉

以循环方式写入重做日志文件，涉及两个文件：

5.数据库索引是什么

数据库索引，是数据库管理系统（DBMS)中一个排序的数据结构，以协助快速查询、更新数据库表中数据

在innodb中，索引就是数据，数据就是索引，都在一个后缀为 .ibd的文件里，索引就是在InnoDB存储引擎层实现的

6.为什么 InnoDB 使用 B+ 树？

每一种解决方案都是为了解决某一类问题而产生，所以在问为什么使用某种方案的时候，其本质就是在探索该方案是用来满足什么样的需求，解决什么样的问题。

所以探究 InnoDb 索引为什么使用 B+ 树这个问题，就是要弄清楚 B+ 树是用来满足什么的需求，解决什么样的问题

首先索引是为了让我们可以更快速的查询数据，mysql里查询语句一般有下面3种情况：

# 根据某个确定值来查询对应的信息
select id, name, email from user where id = 1;

# 通过区间值查询
select id, name, email from user where id > 12 and id < 20

# 通过范围查询并进行排序
select id, name, email from user where id < 123 order by id desc limit 10;

从以上的几个常用的 SQL 我们可以看到在对数据库进行查找数据的过程中主要有以下三类需求：

1. 根据某个值精确快速查找
2. 根据区间的上下限来快速查找此区间的数据
3. 查询符合条件的记录并根据某些字段进行排序

所以，需要找到一种符合上面所有需求的方案。目前比较常用于查询的数据结构有以下两种：散列表、树

我们先看散列表：

散列表就是哈希表，它是一种根据 (key, value) 直接进行访问的数据结构，它通过哈希函数将 key 值换算成hash值，映射到散列表对应的位置上，这种方式的优势就是查找效率非常高，通常比 B+ 树 高

但是哈希索引也有它的劣势：

• 只有精确匹配索引所有列的查询才有效，比如我在列（name, address）建立哈希索引，如果只查询数据列 name, 则无法使用该索引
• 哈希索引不是按照索引值顺序存储的，即 key 经过哈希函数计算后的哈希值不是按顺序的，所以也就无法用于排序，就不能根据区间进行查找
• 哈希索引只支持等值比较查询，如 = 和 in()，不支持范围的查找，如 （between、>、<、…）

所以，哈希索引只适用于特定场合，在适当的场景使用，的确能带来很大的性能提升。比如在 InnoDB 里，就有一种特殊的功能叫 “自适应哈希索引”，如果 InnoDB 注意到某些索引列值被频繁使用时，它会在内存基于 B+ 树索引之上再创建一个哈希索引，这样就能让 B+ 树也具有哈希索引的优点

所以散列表结构无法满足上文提到的需求

接着我们来看看树：

首先，我们要明白一点就是，索引的内容是存储在磁盘上的，读取磁盘数据的时候，都是按磁盘块来读取的，而磁盘的速度相比内存的速度是慢很多倍的，所以要尽量减少读取磁盘的次数，通过从内存读取数据来提高速度

在使用树这种结构作为索引的数据结构时，我们每查找一次数据就需要从磁盘中读取一个磁盘块，也就是对应的一个树节点，所以如果我们如果能在一个磁盘块中存放尽可能多的数据，那么每次读取的数据就会比较多，就会减少磁盘IO，从而提升效率

除了上面说的存储尽量多的关键字，还要满足一个需求就是：存储尽量多的有效的索引数据

接下来就看看各种树能否满足这种需求：

平衡二叉树

对于平衡二叉树来说，平衡二叉树可用于查找，且其查找的时间复杂度近似 O(log2n)，但是平衡二叉树不能作为索引的结构，因为平衡二叉树是每个节点只存储一个键值和数据，也就是说，每个磁盘块只存储一个键值和数据，那如果存储了海量的数据，可以想象平衡二叉树的节点将会非常多，树的深度也会非常大，在查找数据的时候就会进行很多次磁盘 IO，效率将会极低

第二点，我们所说的平衡二叉树，指的是逻辑结构上的平衡二叉树，其物理实现是数组。所以在逻辑相近的节点上，其物理位置可能相差会很远。因此，每次读取的磁盘页数据，很多可能是用不上的，即有效的索引数据并不多，所以在查找过程中还是要进行许多次的磁盘读取操作。所以平衡二叉树也无法解决这个问题

所以，就引出了能解决这两个问题的数据结构 —— B树

B 树

B 树就是多路平衡查找树。是从平衡二叉树演化来的，B树相比于平衡二叉树来说，它的每个节点可以存储多个关键字，比如说 n 阶的 B树 能存n-1个数据，n个指针。如果关键字的数量达到 n，中间元素就会向上分裂也就是页分裂

也正因每个节点存储着非常多个关键字，并且每个节点指向子节点的指针更多，所以说相同数据量的情况下，树的节点数就越少，树的深度也会更少。进而要执行的磁盘读取操作次数更少，更多的是在内存中对读取进来的数据进行查找

由于 B 树的每一个节点，即每一个磁盘块存储的数据较多，所以一定程度上解决了上文提到的存储尽量多的索引的问题。也一定程度上的解决了存储尽量多的有效索引的问题

但是在InnoDB引擎中，用的不是 B树，而是 B+树？

B+ 树

因为 B树有一个特点就是非叶子节点和叶子节点都会存放数据，而在 B+ 树中，非叶子节点上是不存储数据的，仅存储键值，起到索引数据的作用，具体的数据都是在叶子节点存放的

因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16 KB，如果不存储数据，那么节点就可以存储更多的键值，相应的树的阶数就会更大，对于同样的数据量来说，需要的树高就会变低，树会更矮胖，这样的话查找数据的时候进行磁盘的 IO 次数就会更少，提升查询效率

并且B+ 树的叶子节点中的索引数据是按顺序排列的，叶子节点（页）间是通过双向链表进行连接的，叶子节点中的数据是通过单向链表连接的。这个特点使 B+ 树在实现范围查找，排序查找，分组查找等操作时性能都会很高，只需要沿着指针查找就可以了

7.索引越多越好吗？

不是：

1.创建太多索引会占用大量磁盘空间

2.如果索引过多，会降低更新数据的效率，因为更新数据会涉及到索引结构的调整，具体来说：

你要是搞了很多索引，那么你在增删查改的时候，每次都需要维护各个索引的数据有序性，因为每个索引B+树都要求页内是按照值大小排序的，页之间也是有序的，下一个也的所有值必须大于上一个页的所有值。

所以你不停的增删改查，必然会导致各个数据页之间的值大小可能会没有顺序，如果下一个数据页里插入了一个比较小的值，居然比上一个数据页的值要小！此时就只能进行数据页的挪动，维护页之间的顺序

8.讲讲SQL 优化

1.性能分析命令

首先mysql给我们提供了几个可以分析sql执行效率的命令：

1.查看SQL访问频次

首先可以通过SHOW GLOBAL/SESSION STATUS LIKE 'Com_______';命令查看当前数据库增删改查的访问频次，如果查询语句较多，我们可以考虑对表中的索引进行优化

2.慢查询日志

如果是以查询为主，我们可以借助慢查询日志定位出执行效率比较低的SQL，从而对其优化，下面的命令可以查看慢查询日志

show variables like 'slow_query_log';

3.profile查看耗时

show profile命令可以查看指定SQL语句各个阶段的耗时情况

4.explain查看执行计划

可以在select语句前面加上explain来查看当前SQL的执行计划，其中有几个比较关键的字段：

• id：select 查询的序列号，表示查询语句中查询表的顺序，比如多表查询会涉及到多张表的查询，对于这几张表的查询顺序就可以通过id字段判断：id相同，执行顺序从上到下；id不同，值越大越先执行

• type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all。SQL尽量往前优化

  • null：不查询表时才会出现null，业务中的sql语句一般不会出现
  • system：查询系统表时会出现
  • const：查询主键或唯一索引时出现
  • ref：查询非唯一性索引时出现
  • all：全表扫描

• Key：SQL语句执行时实际使用到的索引，如果为 NULL，则没有使用索引

• extra：额外信息

好了，上面就是几种可以分析SQL性能的相关命令，可以通过这些命令查看SQL的具体执行情况，帮助我们进行SQL优化，接下来就说说具体的 如何进行SQL优化

2.规范使用索引

首先我们可以通过规范的使用索引，来提高SQL执行性能，大概分为下面几点：

tips：查看某一张表的所有索引show index from 表名;

• 建立联合索引时，考虑到最左前缀法则，要注意字段的顺序，最经常使用的字段放在最左边比较合适

• 使用联合索引时，要满足最左前缀法则，比如有一个联合索引id_name_age，那么查询条件where后面的字段，id必须存在，并且不能跳过name，如果跳过，则后面的字段用不到索引

• 联合索引中，出现范围查询 (>或者<)，范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题

  在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

• 不要在索引列上进行运算操作，否则索引会失效

• 查询条件里有字符串类型的字段使用索引时，要加单引号，否则会隐式类型转换，索引失效

• 在大数据量的情况下，不要 使用 头部模糊匹配，否则会全表查询，不使用索引，可以使用尾部模糊匹配

• 使用or连接条件时，如果其中一个条件没有索引，则涉及的索引都不会用到

• 使用SQL提示来建议数据库使用哪个索引，但是具体用不用由数据库评估后决定

• 尽量使用覆盖索引，避免 **select *** 可能会导致的回表查询

• 当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只将字符串的一部分前缀，建立前缀索引，这样可以大大节约索引空间，从而提高索引效率

• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高，比如用户的电话，身份证号，姓名

除了上面这些规范使用索引的技巧，还可以通过优化SQL语句来提升效率

3.相关SQL优化

1.插入数据

insert优化

1. 采用批量插入（一次插入的数据不建议超过1000条）

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); 

2. 手动提交事务，因为每一条sql的执行都会自动提交事务，可以让多条sql在一个事务里进行

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

3. 主键顺序插入，性能要高于乱序插入

大批量插入（插入百万条数据）：

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入

2.主键优化

主键设计原则：

• 满足业务需求的情况下，尽量降低主键的长度，因为其他二级索引的叶子结点下面挂的数据是主键，如果太长会占用大量的磁盘空间，搜索时也会占用大量的磁盘IO
• 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
• 尽量不要使用 UUID 做主键或者是其他的自然主键，如身份证号
• 业务操作时，避免对主键的修改

3.order by 优化

对于有排序的查询语句，explain执行计划的extra字段有两种形式：

• Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

• Using filesort：就是读取出来的数据乜有顺序，然后在排序缓冲区 sort buffer 中完成排序操作，效率低

当我们的SQL语句里有对于字段的排序时，可以为这个字段创建索引，默认创建的是升序索引

当SQL语句里有多个字段排序时，创建联合索引，默认也都是升序索引，此时，如果SQL语句里这几个字段都是升序排序或者都是降序排序，则可以用得到这个索引，如果有的需要升序有的需要降序，则不能使用这个索引，此时我们可以再创建一个联合索引，精确的为每个字段指定其索引为升序还是降序

如果不可避免出现 filesort 排序，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）

4.limit优化

常见的问题如limit 2000000, 10，此时需要 MySQL 排序前2000000条记录，但仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大

优化方案：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

-- 此语句耗时很长
select * from tb_sku limit 9000000, 10;
-- 通过覆盖索引加快速度，直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 9000000, 10;
-- 下面的语句是错误的，因为 MySQL 不支持 in 里面使用 limit
-- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);

-- 通过连表查询即可实现第一句的效果，并且能达到第二句的速度
select s.* from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a 
where s.id = a.id;

5.count优化

count有几种用法：count(*)、count(主键)、count(字段)、count(1)

按效率排序：count(字段) < count(主键) < count(1) < count(*)，所以尽量使用 count(*)

6.update优化（避免行锁升级为表锁）

在InnoDB引擎中，默认的事务隔离级别 RR ，默认用的是行锁

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

如以下两条语句：

-- 这句由于id有主键索引，所以只会锁这一行；
update student set no = '123' where id = 1;
-- 这句由于name没有索引，需要先去全表扫描对应的name值对应的记录，所以会把整张表都锁住进行数据更新，如果一直不提交事务，就会导致其他的更新的不能成功。解决方法是给name字段添加索引
update student set no = '123' where name = 'test';

所以我们在执行更新操作的时候，where后面的条件字段要建立索引，才能避免行锁升级为表锁

9.全局锁

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是：

Flush tables with read lock (FTWRL)

加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞

全局锁的典型使用场景是，做全库逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性，此时数据库处于只读状态

但是让整库都只读，听上去就很危险：

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
• 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟

所以，在InnoDB引擎中，我们可以在备份时加上参数 –single-transaction 参数来完成不加锁的一致性数据备份

当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的

这种方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎（如MyISAM），就只能通过加锁的方法

10.表级锁

对于表级锁，主要分为以下三类：表锁、元数据锁（meta data lock，MDL）、意向锁

1.表锁

对于表锁，分为两类：

• 表共享读锁（read lock）
• 表独占写锁（write lock）

加表锁的语法是 lock tables 表名 read/write

特点：

• 读锁不会阻塞其他客户端的读，但是会阻塞写

• 写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写

举个例子：

线程 A 执行 lock tables t1 read; ，此时线程 A 只能对 t1 读，不能写，其他线程对 t1 只能读，不能写

线程 A 执行lock tables t1 write;，此时线程 A 可以对 t1 读写，其他线程不能对 t1 读写

由于表锁每次操作会锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低，所以表锁一般是在数据库引擎不支持行锁的时候才会被用到的，如MySIAM

2.元数据锁MDL

元数据，可以简单理解为就是一张表的表结构

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。或者说，MDL作用是防止DDL和DML并发的冲突

你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)

MDL读写锁之间的兼容情况：

• 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
• 读锁与写锁之间，写锁与写锁之间，是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程同时给一个表加字段，其中一个要等另一个执行完才能开始执行

可以通过下面的SQL，来查看数据库中元数据锁的情况：

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;

3.意向锁

意向锁是干嘛的？

在我们执行 DML 语句也就是增删改表中的数据时，会对要操作的行添加一个行锁，为了避免行锁与表锁的冲突，在InnoDB中引入了意向锁，使得加表锁时不用检查每行数据是否加行锁，使用意向锁来减少表锁的检查

举个例子：比如客户端一对表加了行锁后，客户端二如何给表加表锁？

假如没有意向锁：

1. 首先客户端一（线程A），开启一个事务，然后执行DML操作，在MySQL默认隔离级别下，会对这一行加一个行锁

2. 当客户端二，想对这张表加表锁时，会从第一行数据，检查当前表是否有对应的行锁，一直检查到最后一行数据，如果没有，才会添加表锁，效率很低

有了意向锁之后：

1. 客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁
2. 而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁和自己要加的表锁是否兼容，来判定是否可以成功加表锁，而不用逐行判断行锁情况了
   • 如果意向锁和当前要加的表锁兼容，则直接加表锁
   • 不兼容，会处于阻塞状态，直到客户端一提交事务，行锁和意向锁释放自动释放，此时就可以加表锁

意向锁与表锁的兼容情况：

• 意向共享锁（IS）：执行select … lock in share mode时加的就是IS 。 与 表锁中的共享锁(read)兼容，与表锁的排他锁(write)互斥
• 意向排他锁（IX）：执行insert、update、delete、select…for update添加的就是IX。与表锁共享锁(read)及排他锁(write)都互斥

注意：意向锁之间不会互斥，可以看表锁和行锁互相之间的关系以及互斥规则是什么呢？

11.行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。InnoDB引擎支持行锁

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁

对于行级锁，主要分为以下三类：

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持
• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

1.行锁

InnoDB实现了以下两种类型的行锁：

• 共享锁（S）：某一事务拿到某一行的共享锁，允许去读这一行。允许其他事务获取此行的共享锁，阻止获得排它锁

  即：共享锁和共享锁是兼容的，共享锁和排它锁是互斥的

• 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

  即：某一事务获取了这一行数据的排它锁，则其他事务就不能再获取这行数据的共享锁和排它锁

下面是各种SQL语句加的锁类型：

• SELECT：不加任何行级锁
• SELECT … LOCK IN SHARE MODE：加共享锁（S）
• SELECT … FOR UPDATE、INSERT 、UPDATE 、DELETE ：加排它锁（X）

默认情况下，InnoDB在 RR 事务隔离级别运行，InnoDB使用 next-key （临键）锁进行搜索和索引扫描，以防止幻读

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动退化为行锁
• InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁（因为你没有索引没法加行锁，又要保证安全，只能上表锁）

2.间隙锁

默认情况下，InnoDB在 RR 事务隔离级别运行，InnoDB使用 next-key （临键）锁进行搜索和索引扫描，以防止幻读

• 针对唯一索引上的等值查询，给不存在的记录加锁时, 退化为间隙锁，防止了幻读

  比如有唯一索引 id：1,2,5,8,10，查询 where id = 6；他就会给5-8之间加间隙锁，防止在插入id=6导致幻读

• 针对非唯一普通索引上的等值查询，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁

  我们知道InnoDB的B+树索引，叶子节点是有序的双向链表。 假如，我们要根据这个二级索引查询值为18的数据，并加上共享锁，我们是只锁定18这一行就可以了吗？ 并不是，因为是非唯一索引，这个结构中可能有多个18的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也就是29）。此时会对18加临键锁，并对29之前的间隙加锁

间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

3.临键锁

• 针对唯一索引上的范围查询，会访问到不满足条件的第一个值为止

假设有唯一索引 id，1,2,5,15,18,19,25

查询的条件为 id>=19 ，并添加共享锁。 此时我们可以根据数据库表中现有的数据，将数据分为三个部分：[19]、(19,25]、(25,+∞]

所以数据库数据在加锁是，就是将19加了行锁，25的临键锁（包含25及25之前的间隙），正无穷的临键锁（正无穷及之前的间隙）

总结：

首先要明确几个概念：

行级锁加锁规则比较复杂，不同的场景，加锁的形式还不同。行级锁分为以下三种：

• 行锁，记录锁，Record Lock

• 间隙锁，Gap Lock

• 临键锁，next-key-lock

对记录加锁时，加锁的基本单位是 next-key lock，也就是所谓的临键锁，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间

但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。也就是说 只要加行级锁，先加 临键锁，然后根据情况 退化为 记录or间隙锁

下面举例验证一下

上面这个表，id是主键，自带聚集索引，a是普通的列，b是普通的列，但是创建了非唯一索引
主要分为四个维度去分析：

• 唯一索引，等值查询
• 唯一索引，范围查询
• 非唯一索引，等值查询
• 非唯一索引，范围查询

针对等值查询，还要讨论能不能查到这条数据，加锁规则也不相同

下面开始讨论：

id：0, 4, 8, 16, 32

1.唯一索引，等值查询

先说结论：

• 当查询的记录是存在的，先加 next-key lock，next-key lock 会退化成「记录锁」。
• 当查询的记录是不存在的，先加 next-key lock，next-key lock 会退化成「间隙锁」。

所以当一个事务select where id = 16 for update时，先上临键锁，左开右闭，（8,16] ，但是由于数据存在，所以退化为记录锁，只会锁16这一行记录。这个时候别的事务来update id =16会被阻塞

而当一个事务select where id = 10 for update时，先上临键锁，左开右闭，(8,16]，但是由于数据不存在退化为间隙锁，左右都开，（8,16），锁区间。这个时候别的事务去updat where id = 16可以执行，但是insert id = 15会被阻塞

2.唯一索引，范围查询

select where id=8 for update; 只加记录锁。
select where id>=8 and id<9 for update; 加锁[8，16）。

最开始要找的第一行是 id = 8，因此 next-key lock(4,8]，但是由于 id 是唯一索引，且该记录是存在的，因此会退化成记录锁，也就是只会对 id = 8 这一行加锁；

由于是范围查找，就会继续往后找存在的记录，也就是会找到8后面的数据 id = 16 来进行判断，因为16>9所以会在这一行停下来，然后加 next-key lock (8, 16]，（这里加深理解一下InnoDB加锁基本单位是临键锁，不只要加行锁先上临键锁，然后根据情况退化）然后还是因为你查的是8到9，这边远超了，秉着往最小锁区间发展，这里会退化成间隙锁，加锁范围变为 (8, 16)。

所以，此事务执行这个sql后主键索引的锁是记录锁 id=8 和间隙锁(8, 16)

b：0, 4, 8, 16, 32

3.非唯一索引，等值查询

先说结论：

• 当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁
• 当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁

比如你select where b = 8 for update；

1. 先会对普通索引 b 加上 next-key lock，范围是(4,8];

2. 然后因为是非唯一索引，且查询的记录是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是(8,16)

所以最终加锁范围就是，（4,16）

记住，非唯一索引，等值查询能查到的情况下 加两把锁，一把临键一把间隙，最终相当于一个大间隙锁

比如你select where b = 5 for update；

这时数据不存在，就只加一把锁，临键锁（4,8]，并退化为（4,8）间隙锁

4.非唯一索引，范围查询

select where b>=8 and b<9 for update;

跟上面唯一索引大致一样，唯一区别是不会退化为行锁和间隙锁，也就是先加next-key lock(4,8]，再加next-key lock(8,16]

---

再总结一下：

• 对于等值查询，不管是唯一索引还是普通索引，都要分为能否查到记录去讨论
  • 对于唯一索引，如果能查到，就直接加行锁锁这一行，查不到，就锁住这条记录两边的间隙
  • 对于非唯一普通索引，如果能查到，就以这条记录为中心，向左向右遍历到第一个不满足查询条件的记录，然后把这个区间锁住，如果查不到，那就更好办了，只需要锁住这条记录两边的间隙即可
• 对于范围查询，我们的目的就是：把要查询的范围锁住，防止其他事务掺和进来产生幻读。那我们只需要找到一个包含查询范围的最小区间锁住就达到了目的
  • 对于唯一索引，如果查询范围是单边查找，如：> xxx，那么就不用管左边的范围，< xxx也同理。也就是说只用管一边。如果是> x;< y，那就找到一个最小能包裹（x，y）的范围锁起来
  • 对于非唯一普通索引，因为列值是非唯一的，所以即使是单边查找，我们也要锁住另一边，因为其他事物可以在另一边去插入这条数据。如果是> x;< y，那就和唯一索引一样，找到一个最小能包裹（x，y）的范围锁起来即可

这就是rr级别下，当前读（也就是 select for update）如何 解决了幻读。通过加锁解决。这都属于悲观锁

而非for update查询可以通过mvcc实现的乐观锁用可见性算法解决 快照读 下 的 幻读问题

12.事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

事物的四大特性ACID

• 原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态
• 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

在并发情况下，数据库事务会出现一些问题如下

并发事务问题

问题	描述
脏读	一个事务读到另一个事务还没提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同 这是事务 update 时引发的问题
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在，这是 insert 或 delete 时引发的问题

事务并发的三大问题其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

RR的隔离级别解决了脏读和不可重复读，在InnoDB的RR隔离级别，也解决了幻读

隔离级别	脏读	不可重复读	幻读
Read uncommitted（读未提交）	√	√	√
Read committed（读已提交）（Oracle默认）	×	√	√
Repeatable Read（可重复读）（mysql默认）	×	×	√
Serializable（串行化）	×	×	×

对于事务的四大特性，实际上分为两个部分：

• 原子性、一致性、持久性：由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志
• 隔离性：是通过数据库的 锁 或者 MVCC 来保证的

接下来就看看redo log

13.redo log日志

redo log 是InnoDB引擎层特有的日志，binlog是服务层的日志

redo log日志，即重做日志，用来记录事务提交时数据页的物理修改，是用来实现事务的持久性

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者存放在内存中，后者存放在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件（redo log file）中, 用于在刷新脏页到磁盘，发生错误时, 进行数据恢复使用

如果没有redolog，可能会存在什么问题的？ 我们一起来分析一下，下面这一段仔细看

我们知道，在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。 当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲池没有对应的数据，会通过后台线程将磁盘中的数据加载到缓冲池，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。 而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。 而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性

那么，如何解决上述的问题呢？ 在InnoDB中提供了 redo log日志，接下来我们再来分析一下，通过redo log如何解决这个问题

有了redolog之后，当对缓冲池buffer pool的数据进行增删改之后，会首先将操作的数据页的变化，记录在redolog buffer中。在事务提交时，会将redo log buffer中的数据刷新到redo log file（磁盘文件）中。过一段时间之后，如果刷新buffer pool的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。 而如果脏页成功刷新到磁盘或者涉及到的数据已经落盘，此时redolog就没有作用了，就可以删除了，所以存在的两个redolog文件是循环写的

那为什么每一次提交事务，要将redo log buffer中的数据刷新到redo log file（磁盘文件）中，而不是直接将buffer pool中的脏页刷新到磁盘呢 ?

因为在业务操作中，我们操作数据一般都是随机读写磁盘的，这时就会涉及到大量的随机磁盘IO，而不是顺序读写磁盘，性能很低。 而redo log在往磁盘文件中写入数据，由于是日志文件，日志文件都是追加的，所以都是顺序写的，就是顺序磁盘IO。顺序写的效率，要远大于随机写。 这种先写日志的方式，称之为 WAL（Write-Ahead Logging）先写日志

14.undo log日志

前面的redo log实现了事务的持久性，那么现在的 undo log日志，就用来实现事务的原子性

undo log 也叫回滚日志，用于记录数据被修改前的信息 , 作用包含两个：提供回滚(保证事务的原子性) 和MVCC(多版本并发控制的时候也要依赖undo log找到他的历史版本)

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚

Undo log销毁：undo log的作用之一就是用于数据回滚，undo log在事务执行时产生，一旦事务提交或者回滚，就不再需要他了。但事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中

15.MVCC

现在知道了锁、知道了redo log、知道了 undo log，那么就剩下MVCC了

1.基本概念

MVCC

即多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能，因为快照读不加锁。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView

多版本并发控制指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度

可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行

MVCC的实现方式有多种，典型的有乐观(optimistic)并发控制 和 悲观(pessimistic)并发控制

MVCC只在 RC 和 RR 两个隔离级别下工作。其他两个隔离级别和MVCC不兼容，因为 READ UNCOMMITTED 总是读取最新的数据，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁

学习MVCC的工作模式还需要知道下面几个概念：

当前读

当前读读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode（加行锁的共享锁），select …for update、update、insert、delete（加行锁的排他锁）都是一种当前读

这种加锁的操作都符合行锁的加锁规则，什么意思呢？比如有两个事务：

事务1对id为5的记录加了共享锁（S），也就是执行了select … lock in share mode，则事务2只能对这条记录加共享锁（select … lock in share mode），不能加排它锁（X，select …for update、update、insert、delete），否则会阻塞，直到事务1提交。也就是不能修改这条记录

如果事务1对id为5的记录加了排它锁（X），也就是执行了select …for update、update、insert、delete中的任意一个，则事务2不能对这条记录进行任何操作，包括select … lock in share mode、select …for update、update、insert、delete，但是可以select，因为select是不加任何锁的操作，这种select也就是下面要说的快照读

快照读

简单的select（不加锁）就是快照读，读取的是记录数据的可见版本，有可能是历史数据，（这就是mysql默认隔离级别是RR，简单的select是可重复读）。此外，这个操作不加锁，是非阻塞读

• Read Committed：每次普通的select，都生成一个快照读。
• Repeatable Read：开启事务后第一个普通select语句才是快照读，后面的select都是读的第一次select读到的数据。
• Serializable：快照读会退化为当前读

在测试中，我们看到即使事务B提交更新的操作，事务A中也查询不到。 原因就是因为普通的select是快照读，而在当前默认的RR隔离级别下，开启事务后第一个select语句才是快照读的地方，后面执行相同的select语句都是从快照中获取数据，可能不是当前的最新数据，这样也就保证了可重复读

2.三个隐藏字段

前面我们说了MVCC的实现，依赖于数据库记录中的三个隐式字段

当我们创建了上面的这张表，我们在查看表结构的时候，就可以显式的看到这三个字段。 实际上除了这三个字段以外，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是：

隐藏字段	含义
DB_TRX_ID	最近修改这行记录的事务的ID，记录插入这条记录或最后一次修改该记录的事务ID
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段

上述的前两个字段是肯定会添加的， 是否添加最后一个字段DB_ROW_ID，得看当前表有没有主键，如果有主键，则不会添加该隐藏字段

3.undo log版本链

undo log也叫回滚日志，用于记录数据被修改前的信息 , 作用包含两个：提供回滚(保证事务的原子性) 和MVCC(多版本并发控制的时候也要依赖undo log找到他的历史版本)

MVCC要依赖于undo log日志，具体来说就是以来的undo log版本链，那么什么是undo log版本链？下面演示一下：

现在有一张表原始数据为：

DB_TRX_ID : 记录插入这条记录或最后一次修改该记录的事务ID，一开始就是1，然后每修改一次自增1

DB_ROLL_PTR ： 由于这条数据是才插入的，没有被更新过，所以该字段值为null

现在，有四个并发事务在同时访问这张表

流程是：

1. 事务2345同时开启，然后事务2修改这条记录
2. 在修改之前，InnoDB引擎要先记录undo log日志，用于日后的数据回滚，在这个日志中，记录原来这条记录原来的数据，也就是右图最下面1行，注意，其实是记录的sql，这里为了方便看写成具体的数据。
3. 记录完日志后，再执行更新操作。此时，除了age字段发生变化，DB_TRX_ID和DB_ROLL_PTR 也会变化，DB_TRX_ID 记录了事务2的id，DB_ROLL_PTR 记录刚才生成的undo log日志的地址 0x00001
4. 后面如果在提交事务时出错或者手动回滚时，就通过0x00001这个地址找到undo log进行回滚即可
5. 事务345重复上面的步骤，唯一不同的是，后面生成的undo log的DB_ROLL_PTR 字段都会指向前一个版本的undo log

流程结束后，我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录

那么我们在查询这条记录时，到底返回哪个版本？这其实是由readview来控制的，下面来介绍readView

4.readView

ReadView（读视图）是 快照读 SQL执行时，MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id

前面我们说快照读在读取时并不是读取最新的记录，读取的有可能是历史版本，上面的的undo log 形成的版本链，在版本链中的记录都是历史版本，快照读在执行时，到底读哪个记录，就由 ReadView 来决定的

那么readview 是怎么规定版本链数据的访问规则：是通过四个核心字段再加上一些规则实现的

四个核心字段：

字段	含义
m_ids	当前活跃的事务ID集合
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id	ReadView创建者的事务ID

**具体的访问规则：**trx_id 代表当前undolog记录对应的事务ID

那么我们可以理解为，ReadView就是一张表，这个表记录了上面说的四个字段，然后根据一定的规则去判断这四个字段之间的关系，从而决定当前事务在快照读时具体访问哪一个版本

前面我们说过，MVCC 只支持在 RC 和 RR 隔离级别下运行，在RC和RR 隔离级别下，生成ReadView的时机不同：

• READ COMMITTED ：在事务中每一次执行快照读时生成ReadView
• REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView

那么这个生成时机不同，也就影响了我们在快照读时到底读取版本链中的哪个版本，那么就直接影响到，在不同隔离级别下，我们到底该读取到哪些数据

那么接下来，就分别在 RC 和 RR 下演示 MVCC机制 对 快照读版本 的提取过程

RC（读已提交）隔离级别下 快照读 的提取过程

RC隔离级别下，每一次select都是一个快照读，在事务中每一次执行快照读时都会生成一份ReadView

在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，我们就来分析事务5中，两次快照读读取数据，是如何获取数据的？那么两次生成的ReadView如下

第一次快照读：

过程就是，从左下角的版本链里，从上到下依次的拿着每一条记录的DB_TRX_ID字段去对着右下角的规则进行对比，对比完后我们发现：

隔离级别是读已提交，对着版本链找完后发现正好是事务2，事务2是已提交，这也就验证了读已提交

注意：左下图其实有错误，在第一次快照读的时候，此时事务4还没有修改记录，而undo log是在你要修改时，在修改前才会记录，所以此时的版本链最新的记录应该是事务3修改后的记录，此时你拿着事务4修改的记录去匹配规则，无论如何也不会匹配成功的

第二次快照读：

此时事务3已提交，所以我们读到的也应该是事务3修改后的记录

RR隔离级别

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 而RR 是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的

那MySQL是如何做到可重复读的呢? 我们简单分析一下就知道了

我们看到，在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样， 那么最终快照读返回的结果也是一样的。这也就验证了可重复读

5.加深理解

理解一：

为什么RC要在每次执行快照读都生成一个ReadView？

就是因为：我们的要求是读到最新的已提交的数据，那我们在RC下，两次快照读期间，肯定可能会有其他的事务更新并提交了这条数据，所以此时版本链一定会发生变化，活动的事务也发生了变化（也就是ReadView发生了变化），那我们就需要拿着新的版本链和新的ReadView对着那套规则进行匹配

为什么RR只会在第一次快照读生成一个ReadView，后面的快照读都复用这个 ReadView

就是因为：我们的要求是可重复读，也就是在一个事务中，我们先后读取的同一条数据要一致，那么同一事务的两次快照读期间，即便有其他事物对这条数据进行修改，我们也不要理会，此时虽然版本链发生了变化，但由于用的readview都一样，那我们能在版本链里读到的数据依旧没变，所以根据相同的规则匹配后，依然会匹配到第一次快照的的数据

理解二：

总的来说：一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

• 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 ReadView 前已经提交的事务生成的，所以该版本的记录对当前事务可见

• 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见

• 如果记录的 trx_id 值在 Read View 的min_trx_id和max_trx_id之间，需要判断 trx_id 是否在m_ids 列表中：

  • 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见
  • 如果记录的 trx_id 不在 m_ids 列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见

理解三：

现在概念有点多，大体上就是：锁、日志、MVCC、事务、不同的隔离级别，我们来梳理一下

我们的最终目的就是：保证事务的四大特性，即ACID

那MySQL是怎么保证的？分为两个方向，或者说两种策略

• 通过两个日志redo log 和 undo log 保证：原子性、持久性、一致性，这是不变的

• 那么隔离性，也就是不同的隔离级别，可以分两种策略去解决：

  首先，数据库里所有的SQL操作，都分为两类，一个就是普通的select，即快照读，剩下的就都是当前读

  • MVCC配合undo log版本链实现 快照读 的隔离性

    因为快照读不加任何锁，所以要配合MVCC机制，MVCC可以理解成一套算法，这个算法决定了 快照读 在不同隔离级别下应该读到哪些数据，这也就实现了隔离性

  • 锁机制实现 当前读 的隔离性

    因为当前读都是带锁的，lock in share mode共享锁也好，剩下的排它锁也好，通过加锁来实现隔离性，达到不同的隔离级别，这个很好理解，开两个事务，换着顺序去执行 当前读的SQL即可

16.RC级别是如何解决脏读的？

先说结论：通过改变锁的释放时机来解决脏读问题

首先先了解一下为什么会出现脏读？原因就是在 读未提交 这个级别下，当事务A修改了数据之后就立马释放了锁，然后再提交事务，因此事务B可以读取到这个未提交的数据

在 读已提交 级别下写操作加的锁会到事务提交后释放，所以事务B读不到事务A未提交的数据，通过改变锁的释放时机解决了脏读的问题

17.RR级别如何解决不可重复读

解决不可重复读，也可以换种说法，就是怎么实现的可重复读，通过 MVCC 机制来解决不可重复读问题的

当事务A执行一个普通的 select操作(快照读)，MySQL会把维护的事务的一个read view保存下来，事务A再次执行select操作时，复用第一次快照读生成的read view，依据MVCC的机制，read view相同，自然读到的数据也是一致的

18.RR级别下怎么解决的幻读？

对于快照读：

在RR隔离级别下，普通的查询是快照读，对于一般的快照读，可以通过MVCC解决幻读，但是mvcc存在缺陷，就是一旦某个事物在事务中的修改操作覆盖到了其他事务插入的“幻行”，那么这些“幻行”在下次查询时就会再次出现，从而出现幻象问题

原因就是：RR级别下ReadView的生成时机是在事务中的第一次快照读查询，事务结束前该ReadView复用。但是如果在两次快照读期间，当前事务修改了别的事务新插入的数据，此时当前事务再次进行快照读时会重新生成ReadView，也就能看到别的事务新插入的数据，也就产生了幻读

所以说没有完全解决快照读的幻读问题

MySQL 里除了普通查询是快照度，其他都是当前读，比如select … lock in share mode（加行锁的共享锁），select …for update、update、insert、delete（加行锁的排他锁），这些语句执行前都会查询最新版本的数据，然后再做进一步的操作

Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了next-key 临键锁，就是记录锁（行锁）和间隙锁的组合

比如，执行下面这条语句的时候，会锁住，然后期间如果有其他事务在这个锁住的范围插入数据就会被阻塞。这就避免了幻读

select name from t_stu where id >2 for update

总的来说：

RR在都是当前读或都是快照读的场景下，是解决幻读的，只有在一个事务里既有快照读又有当前读才有可能出现幻读。注意是可能，前提是在发生当前读之前有其它事务插入或删除了数据并提交了（由于当前事务是快照读所以不能阻塞其它事务的变更），这个时候当前事务就出现了幻读。
所以，如果一个事务里既有快照读又有当前读，最好的方式是全部转为当前读，即所有select都加上for update