吃透Mysql

Mysql概述

数据库是一个易于访问和修改的信息集合。它允许使用事务来确保数据的安全性和一致性，并能快速处理百万条以上的数据。数据库一般可以用如下图形来理解：

数据库功能模块图

数据库是由多种互相交互的组件构成的。大致可以分为三层，最上层是面向用户的组件：查询管理器、数据管理器、工具包，中间层是数据库的核心实现模块，最底层是数据存储引擎。下面简单介绍一下各个模块的功能：
查询管理器
查询解析器（Query parser）：用于检查查询是否合法
查询重写器（Query rewriter）：用于预优化查询
查询优化器（Query optimizer）：用于优化查询
查询执行器（Query executor）：用于编译和执行查询
数据管理器
事务管理器（Transaction manager）：用于处理事务
缓存管理器（Cache manager）：数据被使用之前置于内存，或者数据写入磁盘之前置于内存
数据访问管理器（Data access manager）：访问磁盘中的数据
工具
备份管理器（Backup manager）：用于保存和恢复数据。
复原管理器（Recovery manager）：用于崩溃后重启数据库到一个一致状态。
监控管理器（Monitor manager）：用于记录数据库活动信息和提供监控数据库的工具。
Administration管理器（Administration manager）：用于保存元数据（比如表的名称和结构），提供管理数据库、模式、表空间的工具。
核心组件
进程管理器（process manager）：管理进程/线程池。
网络管理器（network manager）：网路I/O是个大问题，尤其是对于分布式数据库，所以一些数据库具备自己的网络管理器。
文件系统管理器（File system manager）：磁盘I/O是数据库的首要瓶颈。具备一个文件系统管理器来完美地处理OS文件系统甚至取代OS文件系统，是非常重要的。
内存管理器（memory manager）：为了避免磁盘I/O带来的性能损失，需要大量的内存。处理大容量内存需要高效的内存管理器，尤其是有很多查询同时使用内存的时候。
安全管理器（Security Manager）：用于对用户的验证和授权。
客户端管理器（Client manager）：用于管理客户端连接。
存储引擎
存储引擎是真正存放数据的地方，实现mysql预定义的接口，提供数据获取、管理的功能。mysql预定义了存储引擎接口，用户可以根据自己业务需求选择合适的第三方存储引擎，也可以实现自己的存储引擎。我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。
本文的目的是为了帮助开发理解数据的原理，所以本文不会讨论工具类组件。本文剩余部分，我们会集中探讨数据库的查询管理器、数据管理器以及存储引擎InnoDB。

查询管理器

查询管理器分为查询解析器、查询优化器（查询重写器也属于优化器的一部分）、查询执行器。查询解析器的工作主要是对查询语句进行语法分析，得到一颗“语法树”，查询优化器选择一种最优的查询计划，最后查询执行器编译和执行查询。下面介绍一下查询解析器和查询优化器具体如果实现的。

查询解析器

连接线程接收到客户端的一条查询请求后，会直接将该查询请求转交给一个专门负责解析SQL、对SQL语句进行语法分析，将SQL语句转换成按某种更利于程序识别的方式表示的语法树，这个模块就是查询解析器。
查询解析器的工作主要是对查询语句进行语法分析，语法分析的结果是MySql内部定义的一个树状的数据结构，称为“语法树”。“语法树”的每个结点都是以下两类之一：第一类是单个的元素，比如关键字、表或属性等的名字、常数、括号、运算符等；第二类是语法类，比如子查询等。
比如有如下的SQL语句：

SELECT movieTitle
FROM StarsIn
WHERE starName IN (
    SELECT name
    FROM MovieStar
    WHERE birthdate LIKE ‘%1960’
);

它被解析成的语法树类似于：

image.png

查询优化器

查询优化分为逻辑查询优化（查询重写）和物理查询优化（查询优化）两个部分。逻辑查询优化着重对查询语句的本身进行调整优化，物理查询优化则着力于对查询语句对应的某几种可能的执行计划进行代价估算，也就是选择一种最优的查询计划，这个过程一般需要从存储引擎读取相关统计数据，是种基于现有数据进行查询代价估算的方法。

逻辑查询优化

逻辑查询优化主要包括以下几部分：
视图重写
视图重写就是将对视图的引用重写为对基本表的引用。视图重写后的SQL多被作为子查询进行进一步优化。所有的视图都可以被子查询替换。MySQL只支持对简单视图的视图重写优化。
子查询优化
MySQL支持使用子查询展开算法优化子查询，又称子查询反嵌套或子查询上拉。把一些子查询置于外层的父查询中，作为连接关系与外层父查询并列，其实质是把某些子查询重写为等价的多表连接操作。带来的好处是，减少表扫描次数的同时，有关的访问路径、连接方法和连接顺序可能被有效使用。MySQL支持对简单SELECT查询中的子查询优化，包括：
简单SELECT查询中的子查询；带有DISTINCT、ORDER BY、LIMIT操作的简单SELECT查询中的子查询。MySQL不支持对如下情况的子查询进行优化：带有UNION操作；带有GROUP BY、HAVING、聚集函数；使用ORDER BY中带有LIMIT；内表、外表的各种超过MySQL支持的最大表的连接数。
等价谓词重写
通过分析处理查询中的谓词，把逻辑表达式改写成等价的且效率更高的形式，能有效提高查询效率。这就是等价谓词重写。
等价谓词重写有很多规则和算法提供支持，MySQL对等价谓词重写优化技术的支持情况如下表所示：

可以看出，MySQL只对NOT谓词进行了谓词重写优化，因此在编写SQL时应根据情况进行适当的SQL优化来提高执行效率。还有其他几种谓词重写优化规则，因为MySQL并不支持优化因此未全部列出。
条件化简
WHERE、HAVING和ON条件由许多表达式组成，而这些表达式在某些时候彼此之间存在一定的联系。利用等式和不等式的性质，可以将WHERE、HAVING和ON化简。MySQL支持的条件化简规则如下：

外连接消除
MySQL支持外连接转换为内连接，转换的条件是内表符合"空值拒绝"。所谓"空值拒绝"一般指的是一下两种情形：条件可以保证从结果中排除外连接右侧生成的值为NULL的行（即条件确保应用在右表带有空值的对象上时，条件不满足，条件的结果值为FALSE或UNKNOWN，这样右表就不会有值为NULL的行生成），所以能使该查询在语义上等效于内连接。外连接的提供空值的一侧（可能是左侧的外表也可能是右侧的内表）为另一侧的每行只返回一行。如果该条件为真，则不存在提供空值的行，并且外连接等价于内连接。
嵌套连接消除
嵌套连接消除的意义在于：对嵌套连接消除为多表的直接连接，表之间的连接次序是可以交换的，这样能灵活求解不同连接方式的花费，进而得到最小花费的连接方式。而嵌套连接则不能利用交换表的位置而获得优化。
语义优化
MySQL通过add_not_null_conds函数，部分实现了语义优化的功能(这是通过对DDL中定义了非空的属性，而SQL查询语句中限定了IS NULL)，比如创建表的DDL中指定了name为NOT NULL，而SQL查询语句如下所示：

SELECT s.name,s.age FROM student s WHERE name IS NULL AND age>18;

优化后的SQL为：

SELECT s.name,s.age FROM student s WHERE 0；

非SPJ优化
GROUP BY优化：MySQL对GROUP BY的处理通常采用的方式是扫描整个表，创建一个临时表用以执行分组操作。所以查询执行计划中出现Using temporary字样就表示MySQL采用了常规处理方式。MySQL对GROUP BY的优化方法就是尽量利用索引。利用索引的条件是：分组子句中的列对象源自同一个b树索引（不支持hash索引优化）的全部或前缀部分的部分有序的键（分组使用的索引列与索引索引简历的顺序不匹配则不能使用索引）。主要方式有松散索引扫描和严密索引扫描。这两个扫描都是以WHERE中的列对象为依据，用索引匹配，如果全部匹配上，则表示是严密索引扫描；如果部分匹配上，则表示是松散索引扫描，如果没有可以匹配的，则表示不能利用索引进行分组操作的优化。
ORDER BY优化： 所谓的ORDER BY优化其实是使用基于对索引的扫描对排序进行优化。通过扫描索引来减少扫描表带来的IO，但该优化只支持查询的目标列都是索引列的情况。
DISTINCT优化： 对于DISTINCT的优化，MySQL支持基于索引的DISTINCT优化和DISTINCT迁移的优化技术。基于索引的优化同其他优化类似，都是使用基于索引的扫描来代替表扫描来减少IO从而提供性能。而DISTINCT迁移的优化是指在对连接操作的结果执行DISTINCT时，可能可以把DISTINCT迁移到一个子查询中优先进行。
LIMIT优化：MySQL支持对不带HAVING子句的LIMIT进行优化。主要优化点有1)LIMIT对单表扫描的影响，如果索引扫描可用且花费低于全表扫描，则用索引扫描实现LIMIT（如果LIMIT取的行数过大，则优化器可能会使用全表扫描）； 2)LIMIT对排序的影响：如果LIMIT和ORDER BY子句一起使用，当取到LIMIT设定个数的有序元组数后，后续的排序操作将不再进行；3）LIMIT对去重的影响，如果LIMIT和DISTINCT子句一起使用，当取到LIMIT设定个数的唯一的元组数后，后续的去重操作将不再进行。4）LIMIT受分组的影响：如果LIMIT和GROUP BY子句一起使用，GROUP BY按索引有序计算每个组的总数的过程中，LIMIT操作不必计数直到下一个分组开始计算。5）LIMIT 0，直接返回空结果集。

物理查询优化

物理查询优化阶段，MySQL对转变后的语法树，进一步根据代价估算模型，评估单表扫描的方式和多表连接最优的连接方式。即先对多个表的连接次序进行组合，得到多种不同的连接次序下形成的执行计划，然后用贪婪算法完成每种访问方式的代价估算。
查询代价估算基于CPU代价和IO代价，代价模型可表示为：

总代价 = IO代价 + CPU代价，即COST = P*a_page_cpu_time + W*T

其中：
P为该计划执行时可能访问的页面数，a_page_cpu_time是每个页面读取花费的时间，其乘积反映了IO代价；T为访问的记录数，反映了CPU花费（访问记录需要解析记录的结构，才能把记录上的字段读出，这消耗的是cpu）。如果是索引扫描，还会包括索引读取的代价。W为权重因子，表示IO到CPU的相关性，又称为选择率。选择率表示满足条件的记录数与表中总记录数的比值。需要注意的是，选择率的计算不可能全表扫描得出，一般采用数据抽样方法评估和采用数据结构提前维护属性值的一个直方图描述分布情况。
特别地，对于单表扫描，在使用基于索引的扫描和全表扫描时的代价估算公式分别如下：

其中：

• a_page_IO_time, 一个页面的IO花费

• N_page, 读取的页面数

• N_page_index, 索引页面数

• a_tuple_CPU_time, 一条记录从页面解析的CPU花费

• N_tuple, 记录数

• C_index, 索引的IO花费，C_index = N_page_index * a_page_IO_time

• N_tuple_index, 索引作用下的记录数， N_tuple_index = N_tuple * 索引选择率

从以上公式中可以看出，使用索引扫描能明显减少IO代价，索引是提高查询效率的有效手段。但是某个列上存在索引，并不意味着索引能够被使用。通常查询优化器使用索引的原则如下：

• 索引列作为条件出现在WHERE、HAVING、ON子句中，这样有利于利用索引过滤出记录；

• 索引列是被连接的表的列且存在于连接条件中；

除了以上两种情况，还有一些特殊情况可以使用索引，比如基于索引列的排序操作、在索引列上求MIN、MAX值等。
对于有索引的列，索引可用的条件总结如下：

• 在WHERE、JOIN/ON、HAVING的条件中出现"列名<操作符>常量"格式的条件子句（索引列不能参与带有变量的表达式计算）；

• 操作符不能是"<>"操作符（不等于操作符在任何类型的列上不能使用索引，可以认为这是一个优化规则，在这种情况下，顺序扫描的效果通常好于索引扫描）；

• 索引列的选择率越低，索引越有效，通常认为索引选择率小于0.1则索引扫描效果会更好。

• 需要注意的是在以下情况中不一定会使用索引：

• 做连接条件的记录定位不一定用索引（代价估算决定哪种扫描方式最优）；

• 索引在WHERE中出现，但是是与子查询比较，不会使用索引，比如SELECT t.t1 IN (SELECT a.a1 FROM A a); 其中t1是索引列；

• 索引列出现在GROUP BY子句中，而不在WHERE子句中，不触发索引扫描；

联合索引对索引使用的影响：
联合索引只由多个列组合形成的组合索引，如果在WHERE子句中出现了部分索引列，但是这部分索引列不是联合索引的前缀部分，不会使用索引。例如有如下的联合索引（r1, r2, r3, r4）则有如下情形：

多个索引对使用的影响

• WHERE条件中出现两个可利用的索引，优选最简单的索引；

• WEHRE条件中包含一个索引但在两个条件中出现，优选最简单的索引；

• 独立索引优先于联合索引；

• 等值比较优先于范围扫描；

数据管理器

数据管理器包含缓冲管理器、事务管理器以及数据访问管理器。事务管理器用于处理事务，缓存管理器负责数据被使用之前和写入磁盘之前置于内存。数据访问管理器通过定义的接口访问磁盘中的数据。这部分主要深入聊一下缓存管理器和事务管理器。

缓存管理器

数据库的主要瓶颈是磁盘 I/O。为了提高性能，现代数据库使用缓存管理器。查询执行器不会直接从文件系统拿数据，而是向缓存管理器要。缓存管理器有一个内存缓存区，叫做缓冲池，从内存读取数据显著地提升数据库性能。缓存的操作和管理由存储引擎实现，MYSQL只定义缓存的接口。
缓冲只是容量有限的内存空间，因此，为了加载新的数据，它需要移除一些数据。加载和清除缓存需要一些磁盘和网络I/O的成本。如果你有个经常执行的查询，那么每次都把查询结果加载然后清除，效率就太低了。现代数据库用缓冲区置换策略来解决这个问题。

缓冲区置换策略

多数现代数据库(至少 SQL Server, MYSQL, Oracle 和 DB2)使用 LRU 算法。LRU代表最近最少使用（Least Recently Used）算法，背后的原理是：在缓存里保留的数据是最近使用的，所以更有可能再次使用。这个算法效果很好，但是有些限制。如果对一个大表执行全表扫描怎么办？换句话说，当表/索引的大小超出缓冲区会发生什么？使用这个算法会清除之前缓存内所有的数据，而且全扫描的数据很可能只使用一次。为了解决这个问题，LRU算法产生了很多衍生的优化算法，大致原理类似，新数据不是直接插到队列头部，而是中间某个位置，当达到一定访问次数之后，才进入热数据区域。

写缓冲区

我只探讨了读缓存--在使用之前预先加载数据。缓冲区域也可以用来保存数据，然后成批刷入磁盘，而不是逐条写入数据从而造成很多单次磁盘访问。要记住，缓冲区保存的是页（最小的数据单位）而不是行（逻辑上/人类习惯的观察数据的方式）。缓冲池内的页如果被修改了但还没有写入磁盘，就是脏页。MYSQL会有另外一颗共享B+树记录脏页，当相同页的其他行数据再次修改，可以先在内存中进行Merge，MYSQL会定期（每秒和每10秒）进行刷脏，每次读取涉及脏页也会触发刷脏。

Innodb事务管理

事务（Transaction）是数据库区别于文件系统的重要特性之一，事务会把数据库从一种一致性状态转换为另一种一致性状态。在数据库提交时，可以确保要么所有修改都已保存，要么所有修改都不保存。事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。事务是访问并更新数据库中各种数据项的一个程序执行单元。
InnoDB存储引擎中的事务满足ACID的特性:

• 原子性(atomicity )

• 一致性(consistency)

• 隔离性(isolation )

• 持久性(durability)

事务分类

从事务理论的角度可以把事务分为以下几种类型：

• 扁平事务（Flat Transactions）

• 带有保存节点的扁平事务（Flat Transactions with Savepoints）

• 链事务（Chained Transactions）

• 嵌套事务（Nested Transactions）

• 分布式事务（Distributed Transactions）
  扁平事务
  扁平事务（Flat Transactions）是事务类型中最简单但使用最频繁的事务。在扁平事务中，所有的操作都处于同一层次，由BEGIN/START TRANSACTION开始事务，由COMMIT/ROLLBACK结束，且都是原子的，要么都执行，要么都回滚。因此扁平事务是应用程序成为原子操作的基本组成模块。
  扁平事务的主要限制是不能提交或者回滚事务的某一部分。如果某一事务中有多个操作，在一个操作有异常时并不希望操作全部回滚，而是保存前面操作的更改，扁平事务是不能支持这样的事例。因此就出现了带有保存节点的扁平事务。
  带有保存节点的扁平事务
  带有保存节点的扁平事务（Flat Transactions with Savepoints）允许事务在执行过程中回滚到较早的一个状态，而不是回滚所有的操作。保存点（Savepoint）用来通知系统应该记住事务当前的状态，以便当之后发生错误时，事务能回到保存点当时的状态。对于扁平事务来说，在事务开始时隐式地设置了一个保存点，回滚时只能回滚到事务开始时的状态。
  链事务
  链事务（Chained Transaction）是指一个事务由多个子事务链式组成。前一个子事务的提交操作和下一个子事务的开始操作合并成一个原子操作，这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的一样。这样，在提交子事务时就可以释放不需要的数据对象，而不必等到整个事务完成后才释放。
  链事务与带保存节点的扁平事务不同的是，链事务中的回滚仅限于当前事务，相当于只能恢复到最近的一个保存节点，而带保存节点的扁平事务能回滚到任意正确的保存点。但是，带有保存节点的扁平事务中的保存点是易失的，当发生系统崩溃是，所有的保存点都将消失，这意味着当进行恢复时，事务需要从开始处重新执行。
  嵌套事务
  嵌套事务（Nested Transaction）是一个层次结构框架。由一个顶层事务（top-level transaction）控制着各个层次的事务。顶层事务之下嵌套的事务成为子事务（subtransaction）,其控制着每一个局部的操作，子事务本身也可以是嵌套事务。因此，嵌套事务的层次结构可以看成是一颗树，其结构如下图所示。
  

  

  嵌套事务结构图

  
  分布式事务
  分布式事务（Distributed Transactions）通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中不同节点的数据库资源。

事务实现原理

redo log（重做日志）用来保证事务的持久性。undo log用来保证事务的原子性和MVCC。锁机制来保证事务的隔离性。
redo和undo的作用都可以视为是一种恢复操作，redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。因此两者记录的内容不同，redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。
redo日志
重做日志用来实现事务的持久性，即事务ACID中的D。其由两部分组成:一是内存中的重做日志缓冲(redo log buffer)，其是易失的；二是重做日志文件(redo log file)，其是持久的。
InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交(COMMIT)时，必须先将该事务的所有日志缓存写人到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。
redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作。为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写人重做日志文件后，InnoDB存储引擎都需要调用一次fsync操作。由于fsync的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。数据写入磁盘的时候会触发检查点(log chechkpoint)。检查点就是系统恢复的最早的那个点，也就是写入到磁盘的最新脏块的redo log的位置（被记录到ibdata中即最早脏的那个数据块 系统崩溃恢复的时候，检查点作为崩溃恢复检查的起点）。
InnoDB存储引擎允许用户手工设置非持久性的情况发生（通过设置innodb_flush_log_at_trx_commit参数，默认为1，表示事务提交时必须调用一次fcync操作。还可以设置该参数的值为0和2。0表示事务提交时不进行写入重做日志操作，这个操作仅在maser thread中完成，而在master thread中每1秒会进行一次重做日志文件的fsync操作。2表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。），以此提高数据库的性能。即当事务提交时，日志不写入重做日志文件，而是等待一个时间周期后再执行fsync操作。由于并非强制在事务提交时进行一次fsync操作，显然这可以显著提高数据库的性能。但是当数据库发生宕机时，由于部分日志未刷新到磁盘，因此会丢失最后一段时间的事务。
日志、数据、事务提交的步骤如下：
1）事务提交出发，在事务完成前，log buffer刷新到日志文件中；
2）数据块的脏块写入磁盘；
3）日志和数据都完成后，事务才结束。
所以存在以下三种场景：

• 事务已经提交，redo log一定已经写入了磁盘；

• 事务没有提交，但脏块已经写入到了磁盘，此时，redo log 一定也写入到了磁盘。这个事务没有提交，因此回滚的数据一直存在，这个回滚的数据被redo log保护，保证崩溃恢复的时候undo的正确回滚。

• 事务没有提交，redo log也没有写入磁盘。此时数据脏块也没写入磁盘，无需处理。

undo 日志

重做日志记录了事务的行为，可以很好地通过其对页进行"重做"操作。但是事务有时还需要进行回滚操作（redo日志和数据块写成功了，但事务未提交），这时就需要undo。因此在对数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。
redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段(segment)中，这个段称为undo段(undo segment ) 。undo段位于共享表空间内，ibdata 共享表空间里第五个页就是属于 undo的默认128个段。128个段各自又有多个块，每个段的第一个块成为段头块，uodo的第一个块被称为系统事务表，分别指向不同段的段头块。一个段头块最多可以有1024的slot（可以认为每个段最多可以有1024个事务，即一个数据库最多可以并发128*1024个事务）。
事务开始是会生成一个事务id：xid（依次递增），在系统事务表里找到一个相对空闲（活跃事务较少的段）的undo段，将自己的xid写进段头块的一个空闲槽位slot，事务修改数据前，将数据存放到空闲的块中，需要多个块的时候，地址依次相连（undo块使用了链条进行链接），最后一个用到的块会指向段头块的那个槽位。
commit： 修改slot 为已提交
rollback：事务没有被提交，slot不会被覆盖，undo块也不会被覆盖；找到最后一个块，依次往前回滚，如果是一个大事务，回滚时间会很长。
崩溃恢复：redo日志回滚完，开始前滚，记录了事务的最大id，那说明所有未提交的事务都小于该id。但是并不会马上回滚，当新的事务修改数据块时，发现该数据块的事务未提交，就会前去回滚。当事务没提交但是被写入磁盘则需要undo回滚。

undo页需要回收。 通过purge线程，可以用py_innodb_page_info.py工具来查看当前共享表空间的undo的数量。
用户通常对undo有这样的误解:undo用于将数据库物理地恢复到执行语句或事务之前的样子--但事实并非如此。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。
例如，用户执行了一个INSERT 10W条记录的事务，这个事务会导致分配一个新的段，即表空间会增大。在用户执行ROLLBACK时，会将插入的事务进行回滚，但是表空间的大小并不会因此而收缩。因此，当InnoDB存储引擎回滚时，它实际上做的是与先前相反的工作。对于每个INSERT,  InnoDB存储引擎会完成一个DELETE；对于每个DELETE，InnoDB存储引擎会执行一个INSERT；对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去。
除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。
最后也是最为重要的一点是，undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。

事务的隔离级别

SQL标准定义的四个隔离级别为:

• READ UNCOMMITTED

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE

Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。脏页是因为数据库实例内存和磁盘的异步造成的，这并不影响数据的一致性(或者说两者最终会达到一致性，即当脏页都刷回到磁盘)。并且因为脏页的刷新是异步的，不影响数据库的可用性，因此可以带来性能的提高。
Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。不保证一个事务重新读的时候能读到相同的数据，因为在每次数据读完之后其他事务可以修改刚才读到的数据。在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这种隔离级别也称为不可重复读（Nonrepeatable Read）。
不可重复读和脏读的区别是:脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据，但是其违反了数据库事务一致性的要求。一般来说，不可重复读的问题是可以接受的，因为其读到的是已经提交的数据，本身并不会带来很大的问题。因此，很多数据库厂商(如Oracle, Microsoft SQL Server) 将其数据库事务的默认隔离级别设置为READ COMMITTED，在这种隔离级别下允许不可重复读的现象。
Repeatable Read（可重读）
这是大部分公司MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行（这是通过“范围锁(range-locks)”实现的）。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的"全部数据行"。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入"一行新数据"。那么，以后就会发生操作第一个事务的用户发现表中还存在没有修改的数据行，就好象发生了幻觉一样.
Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上锁。在这个级别，可能导致大量的超时现象和锁竞争。

√: 可能出现    ×: 不会出现

注意： 幻读的问题理应由更高的隔离级别来解决，但MySQL和其它数据不一样，它同样在可重复读的隔离级别解决了这个问题。也就是说, MySQL的可重复读的隔离级别解决了"不可重复读" 和 "幻读" 2个问题，其中"幻读"需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-keylocks。MySQL默认的隔离级别也是: REPEATABLE READ(可重复读)。下面我们来看InnoDB如何通过锁机制解决以上问题：

锁机制

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。InnoDB存储引擎会在行级别上对表数据上锁，这固然不错。不过InnoDB存储引擎也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。例如，操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素，为了保证一致性，必须有锁的介人。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。
锁的类型
InnoDB存储引擎实现了如下两种标准的行级锁:

• 共享锁((S Lock)，允许事务读一行数据。

• 排他锁(X Lock)，允许事务删除或更新一行数据。

如果一个事务T1已经获得了行t的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容(Lock Compatible)。但若有其他的事务T3想获得行r的排他锁，则其必须等待事务T1, T2释放行r上的共享锁—这种情况称为锁不兼容。下表显示了共享锁和排他锁的兼容性。

排他锁和共享锁的兼容性

从上表可以发现X锁与任何的锁都不兼容，而S锁仅和S锁兼容。需要特别注意的是，S和X锁都是行锁，兼容是指对同一记录(row)锁的兼容性情况。此外，InnoDB存储引擎支持多粒度(granular)锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁(Intention Lock )。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁。若将上锁的对象看成一棵树，那么要对最下层的对象上锁，比如行上X锁，那么首先需要对粗粒度的对象上锁。也就是对最细粒度的对象如果需要对页上的记录r上锁，那么分别需要对数据库、表、页上意向锁IX，最后对记录r上x锁。若其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。举例来说，在对记录r加X锁之前，已经有事务对表1进行了S表锁，那么表i上已存在S锁，之后事务需要对记录r在表1上加上IX，由于不兼容，所以该事务需要等待表锁操作的完成。
InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁:

• 意向共享锁(IS Lock)，事务想要获得一张表中某几行的共享锁

• 意向排他锁(IX Lock)，事务想要获得一张表中某几行的排他锁

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下标所示。

作者：彦帧
链接：https://www.jianshu.com/p/a1d04881e72c