吃透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规则	NOT(age!=8) OR NOT(age!=sno) OR NOT(age=sno) OR NOT(age<sno) OR NOT(age>sno)	(test.age=8) AND (test.sno=test.age) AND (test.age<>test.sno) AND (test.age>=test.sno) AND (test.age>=test.sno)	NOT规则重写的好处是，如果age列上建立了索引，则可以用索引扫描代替原来的全表扫描	支持
LIKE规则	name LIKE 'abc%'	test.name >'abc' AND name < 'abd'	LIKE谓词只能进行全表扫描，如果name列上存在索引则转换后可以进行索引范围扫描	不支持
BETWEEN-AND规则	BETWEEN 10 AND 20	test.sno >= 10 and sno <=20	如果sno上简历了索引，则可以避免BETWEEN-AND限定的全表扫描	不支持

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

规则	表达式原型	支持情况
去除表达式中冗余的括号	((a AND b) AND (c AND d))	支持
常量传递	name=age AND age=3	支持
消除死码	(0>1 OR a=5)	支持
合取项只要有一个为假，即整个表达式为假	(0>1 AND s1=5)	支持
AND操作符是可交换的	name+age=100 AND name>1 AND age>2	支持，MySQL支持对条件按表达式的连接顺序进行排序，有限判断连接的表涉及的条件
表达式计算	name = 1+2	部分支持如果操作数都是常量则能计算求值；

外连接消除
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的相关性，又称为选择率。选择率表示满足条件的记录数与表中总记录数的比值。需要注意的是，选择率的计算不可能全表扫描得出，一般采用数据抽样方法评估和采用数据结构提前维护属性值的一个直方图描述分布情况。
特别地，对于单表扫描，在使用基于索引的扫描和全表扫描时的代价估算公式分别如下：

扫描方式	代价估算公式
全表扫描	N_pagea_tuple_IO_time + N_tuplea_tuple_CPU_time
索引扫描	C_index + N_page_index * a_tuple_IO_time

其中：

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中出现的索引列	是否使用索引	哪些索引列起作用	解释
r1, r2	是	r1, r2	r1, r2是r1, r2, r3, r4的前缀
r2, r3	否	-	r2, r3是r1, r2, r3, r4的前缀
r1, r2, r4	是	r1, r2	只有r1, r2是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（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上锁。在这个级别，可能导致大量的超时现象和锁竞争。

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

	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable 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
X	不兼容	不兼容
S	不兼容	兼容

从上表可以发现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存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下标所示。

Innodb存储引擎中锁的兼容性

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

多版本并发控制（MVCC）

在并发读写数据库时，为了避免出现读操作出现不一致的情况，需要实现数据库的并发访问控制，最简单的方式就是加锁访问。由于，加锁会将读写操作串行化，所以不会出现不一致的状态。但是，读操作会被写操作阻塞，大幅降低读性能。为了提高性能，解决并发访问的问题，InnoDB使用MVCC（Multi-Version Concurrent Control，即多版本并发控制）来解决这个问题。
在MVCC协议下，每个读操作会看到一个一致性的snapshot，并且可以实现非阻塞的读。MVCC允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务ID，在同一个时间点，不同的事务看到的数据是不同的。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB存储引擎会去读取行的一个快照数据。快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。
可以看到，非锁定读机制极大地提高了数据库的并发性。在InnoDB存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在READ COMMITTED事务隔离级别下，快照数据读取的是被锁定行的最新一份快照数据。而在REPEATABLE READ事务隔离级别下，快照数据读取的是事务开始时的行数据版本。
在事务的隔离级别为REPEATABLE READ模式下，InnoDB存储引擎的SELECT操作是不需要加锁的。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读(locking read )操作:

SELECT…FOR UPDATE
SELECT…LOCK IN SHARE MODE

SELECT FOR UPDATE对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。SELECT…LOCK IN SNARE MODE对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。
结论：当一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之如果请求不兼容，则该事务就等待锁释放。
对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）；对于一般的Select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。

共享锁：select \* from table\_name where .....lock in share mode
排他锁：select \* from table\_name where .....for update

锁的算法
行锁的3种算法
InnoDB存储引擎有3种行锁的算法，其分别是:

Record Lock:单个行记录上的锁
Gap Lock:间隙锁，锁定一个范围，但不包含记录本身
Next-Key Lock:Gap Lock+Record Lock锁定一个范围，并且锁定记录本身

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。
Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。例如一个索引有10,11,13和20这四个值，那么该索引可能被Next-Key Locking的区间为:
(-∞，10]、(10，11]、(11，13]、(13，20]、(20，+∞)
若事务T1已经通过next-key locking 锁定了如下范围：
（10,11）、(11，13)
当插入新的记录12时，则锁定的范围会变成：
（10,11）、(11，12) 、(12，13)
然而，当查询的索引含有唯一属性时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。例如，根据代码创建表t:

DROP TABLE IF EXISTS t;
CREATE TABLE t(a INT PRIMARY KEY);
INSERT INTO t SELECT 1;
INSERT INTO t SELECT 2;
INSERT INTO t SELECT 5;

接着执行下表中的sql语句

时间	会话A	会话B
1	BEGIN;
2	SELECT * FROM t WHERE a=5 FOR UPDATE;
3		BEGIN;
4		INSERT INTO t SELECT 4;
5		COMMIT;
6	COMMIT;

表t共有1, 2, 5三个值。在上面的例子中，在会话A中首先对a=5进行X锁定。而由于a是主键且唯一，因此锁定的仅是5这个值，而不是(2,5)这个范围，这样在会话B中插入值4而不会阻塞，可以立即插人并返回。即锁定由Next-Key Lock算法降级为了Record Lock，从而提高应用的并发性。
Next-Key Lock降级为Record Lock仅在查询的列是唯一索引情况下。若是辅助索引，则情况会完全不同。同样，首先根据如下代码创建测试表z:

CREATE TABLE z(a INT, b INT,PRIMARY KEY(a), KEY(b));
INSERT INTO z SELECT 1,1;
INSERT INTO z SELECT 3,1;
INSERT INTO z SELECT 5,3;
INSERT INTO z SELECT 7,6;
INSERT INTO z SELECT 10,8;

表z的列b是辅助索引，若在会话A中执行下面sql语句

SELECT \* FROM z WHERE b=3 FOR UPDATE;

很明显，这时SQL语句通过索引列b进行查询，因此其使用传统的Next-Key Locking技术加锁，并且由于有两个索引，其需要分别进行锁定。对于聚集索引，其仅对列a等于5的索引加上Record Lock。而对于辅助索引，其加上的是Next-Key Lock. 锁定的范围是(1, 3]，特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock，即还有一个辅助索引范围为(3,6]的锁。因此，若在新会话B中运行下面的SQL语句，都会被阻塞:

SELECT \* FROM z WHERE a=5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4,2;
INSERT INTO z SELECT 6,5;

第一个SQL语句不能执行，因为在会话A中执行的SQL语句已经对聚集索引中列a=5的值加上X锁，因此执行会被阻塞。第二个SQL语句，主键插入4，没有问题，但是插入的辅助索引值2在锁定的范围(1,3]中，因此执行同样会被阻塞。第三个SQL语句，插入的主键6没有被锁定，5也不在范围(1, 3)之间。.但插入的值5在另一个锁定的范围(3 ,6]中，故同样需要等待。而下面的SQL语句，不会被阻塞，可以立即执行:

INSERT INTO z SELECT 8,6;
INSERT INTO z SELECT 2,0;
INSERT INTO z SELECT 6,7

从上面的例子中可以看到，Gap Lock的作用是为了阻止多个事务将记录插入到同一范围内，而这会导致Phantom Problem问题的产生。例如在上面的例子中，会话A中用户已经锁定了b=3的记录。若此时没有Gap Lock锁定(3, 6)，那么用户可以插人索引b列为3的记录，这会导致会话A中的用户再次执行同样查询时会返回不同的记录，即导致Phantom Problem问题的产生。用户可以通过以下两种方式来显式地关闭Gap Lock:将事务的隔离级别设置为READ COMMITTED；将参数innodb_locks_unsafese_for_binlog设置为1。

在上述的配置下，除了外键约束和唯一性检查依然需要的Gap Lock，其余情况仅使用Record Lock进行锁定。但需要牢记的是，上述设置破坏了事务的隔离性，并且对于replication，可能会导致主从数据的不一致。此外，从性能上来看，READ COMMITTED也不会优于默认的事务隔离级别READ REPEATABLE.
在InnoDB存储引擎中，对于INSERT的操作，其会检查插人记录的下一条记录是否被锁定，若已经被锁定，则不允许查询。对于上面的例子，会话A已经锁定了表Z中b=3的记录，即已经锁定了(1, 3)的范围，这时若在其他会话中进行如下的插人同样会导致阻塞:

INSERT INTO z SELECT 2,2;

因为在辅助索引列b上插人值为2的记录时，会监测到下一个记录3已经被索引。而将插入修改为如下的值，可以立即执行:

INSERT INTO z SELECT 2,0;

最后需再次提醒的是，对于唯一键值的锁定，Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么查询其实是range类型查询，而不是point类型查询，故InnoDB存储引擎依然使用Next-Key Lock进行锁定。
锁选择
sql语句会根据查询计划（where条件和索引）来决定加何种类型的锁。如果更新条件没有走索引，进行全表扫描，扫表的时候，要阻止其他任何的更新操作，所以上升为表锁。如果更新条件为索引字段，但是并非唯一索引（包括主键索引），那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因：首先要保证在符合条件的记录上加上排他锁，会锁定当前非唯一索引和对应的主键索引的值；还要保证锁定的区间不能插入新的数据。如果更新条件为唯一索引，则使用Record Lock（记录锁）。

死锁与检测

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阂值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来设置超时的时间。超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据FIFO的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的undo log，这时采用FIFO的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。
因此，除了超时机制，当前数据库还都普遍采用wait-for graph(等待图)的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式。wait-for graph要求数据库保存以下两种信息:

锁的信息链表
事务等待链表

通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。在wait-for graph中，事务为图中的节点。而在图中，事务T1指向T2边的定义为:

事务T1等待事务T2所占用的资源
事务T1最终等待T2所占用的资源，也就是事务之间在等待相同的资源，而事务T1发生在事务T2的后面

下面来看一个例子，当前事务和锁的状态下图所示。

image.png

在Transaction Wait Lists中可以看到共有4个事务t1. t2, t3, t4，故在wait-for graph中应有4个节点。而事务t2对row 1占用X锁，事务t1对row2占用s锁。事务t1需要等待事务t2中row 1的资源，因此在wait-for graph中有条边从节点tt指向节点t2。事务t2需要等待事务t1 . t4所占用的row2对象，故而存在节点t2到节点t1, t4的边。同样，存在节点t3到节点t1. t2. t4的边，因此最终的wait-for graph如下图所示。

通过上图可以发现存在回路(tl，t2)，因此存在死锁。通过上述的介绍，可以发现wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。
在InnoDB中，使用行锁机制，于是，锁通常是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。
死锁产生的四要素(跟操作系统的死锁条件一样)：

互斥条件：一个资源每次只能被一个进程使用；
请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；
不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺；
循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

死锁的形式是多样的，但分析到innodb加锁情况的最底层，因循环等待条件而产生的死锁只有可能是四种形式：

两张表两行记录交叉申请互斥锁；
同一张表则存在主键索引锁冲突；
主键索引锁与非聚簇索引锁冲突；
锁升级导致的锁等待队列阻塞。

一些发生死锁的情况：
1.同一索引上,两个session相反的顺序加锁多行记录；

Primary key和Secondary index，通过primary key找到记录，更新Secondary index字段与通过Secondary index更新记录；
UPDATE/DELETE通过不同的二级索引更新多条记录,可能造成在Primary key上不同的加锁顺序；

Innodb检测死锁有两种策略：超时和等待图。但是在我们开发中以单个SQL语句组成的隐式事务来说，建议的避免死锁的方法如下：
1.如果使用insert…select语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用select into outfile加上load data infile代替 insert…select，这样不仅快，而且不会要求锁定

一个锁定记录集的事务，其操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。
3.更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。
sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等待资源，导致与其他事务冲突。
对定点运行脚本的情况，避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句。
6.应用程序中增加对死锁的判断，如果事务意外结束，重新运行该事务，减少对功能的影响。

mysql存储引擎

由于mysql开源的特性，mysql预定义了存储引擎接口，用户可以根据自己业务需求选择合适的第三方存储引擎，也可以实现自己的存储引擎。这个也是mysql区别于oracle的一个主要特性。Innodb存储引擎是其中应用最为广泛的OLTP（在线事务处理）存储引擎。下面简单介绍一下常用的mysql存储引擎。
innodb存储引擎，特点支持外键、行锁、非锁定读(默认情况下读取不会产生锁)、mysql-4.1开始支持每个innodb引擎的表单独放到一个表空间里。innodb通过使用MVCC来获取高并发性，并且实现sql标准的4种隔离级别，同时使用一种被称成next-key locking的策略来避免换读(phantom)现象。除此之外innodb引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead)等高性能技术。
myisam存储引擎，myisam特点是不支持事务，适合OLAP（在线分析处理）应用，myisam表由MYD和MYI组成。mysql-5.0版本之前，myisam默认支持的表大小为4G，从mysql-5.0以后，myisam默认支持256T的表单数据。myisam只缓存索引数据。
NDB存储引擎，特点是数据放在内存中，mysql-5.1版本开始可以将非索引数据放到磁盘上。NDB之前的缺陷是join查询是mysql数据库层完成的，而不是存储引擎完成的，复杂的join查询需要巨大的网络开销，速度很慢。当前mysql cluster7.2版本中已经解决此问题，join查询效率提高了70倍。
memeory存储引擎，将数据放到内存中，默认使用hash索引，不支持text和blob类型，varchara是按照char的方式来存储的。mysql数据库使用memory存储引擎作为临时表还存储中间结果集(intermediate result)，如果中间集结果大于memorg表的容量设置，又或者中间结果集包含text和blog列类型字段，则mysql会把他们转换到myisam存储引擎表而放到磁盘上，会对查询产生性能影响。
archive存储引擎，压缩能力较强，主要用于归档存储。
下面这张表列出了MySQL中5种常用的存储引擎以及它们支持的特性。

Feature	MyISAM	Memory	InnoDB	Archive	NDB
Storage limits	256TB	Yes	64TB	No	Yes
Transactions	No	No	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC (snapshot read)	No	No	Yes	Yes	No
Geospatial support	Yes	No	Yes	Yes	Yes
B-tree indexes	Yes	Yes	Yes	No	Yes
Hash indexes	No	Yes	No	No	Yes
Full-text search indexes	Yes	No	No	No	No
Clustered indexes	No	No	Yes	No	No
Data caches	No	N/A	Yes	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Compressed data	Yes	No	No	Yes	No
Encrypted data	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Replication support	Yes	Yes	Yes	Yes	Yes
Foreign key support	No	No	Yes	No	No
Backup / point-in-time recovery	Yes	Yes	Yes	Yes	Yes
Query cache support	Yes	Yes	Yes	Yes	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes

使用mysql绝大多数场景都是使用innodb，目前innodb也是mysql的默认引擎，以下将从引擎架构、文件、表、索引四个方面介绍innodb。

innodb引擎架构

InnoDB体系架构图如下：

image.png

后台线程简介：

1、Master Thread：Master Thread 是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERT BUFFER）、回滚页（UNDO PAGE）的回收等。
2、IO Thread在InnoDB存储引擎中大量使用了AIO（Async IO）来处理IO请求，这样可以极大提高数据库的性能。而IO Thread（insert buffer thread、log thread、read thread、write thread）的工作主要是负责这些IO请求的回调（call back）处理。
3、Purge Thread：事务被提交后，其所使用的undo log可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页。
4、Page Cleaner Thread：Page Cleaner Thread是在InnoDB 1.2.x版本中引人的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

InnoDB内存数据对象简介

在数据库系统中，由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。缓冲池简单来说就是一块内存区域。在数据库中进行读取页的操作，首先将从磁盘读到的页存放到缓冲池中，这个过程称为将页"FIX"在缓冲池。下一次在读到相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。
为了避免发生数据丢失的问题，当前事务数据库系统普遍使用WAL（Write Ahead Log）策略，即当事务提交时，先写重做日志，再写修改页。当数据库发生宕机而导致丢失数据时，可以通过重做日志来完成数据的恢复，从而满足事务ACID中Durability持久性的要求。CheckPoint技术是为了缩短恢复时间而对持久到硬盘的数据页设置检查点，只需要对CheckPoint之后的重做日志进行恢复。CheckPoint的触发条件：1）Master Thread每秒或者每10秒会进行一次刷脏；2）当LRU中少于100个空闲页时；3）重做日志是一块循环使用的空间（8M），重做日志不可用时会触发；4）当脏页太多时，达到某个阈值。
InnoDB内存数据对象：数据页（undo page）、索引页（index page）、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、锁信息（lock info）、数据字典信息（data dictionary）、重做日志缓冲（redo log_buffer）、额外内存池（innodb_additional_mem_pool_size）。
数据库是一种基于磁盘存储系统，存储的数据量远大于内存的大小，故内存中的数据存在置换，数据库中的缓存池是通过LRU算法进行管理的。缓存池中的最小单元是页（一个页的默认大小是16kb）。Innodb对LRU进行了优化，默认配置下，新访问的数据是放到LRU列表的5/8处（防止遍历等导致常用的数据别刷出LRU列表），当达到一定时间后，才会被放入LRU的热端。
重做日志缓冲
重做日志缓冲是用于存放重做日志信息的，重做日志缓冲一般不需要设置的很大，因为一般情况下每一秒会将重做日志缓冲刷新到日志中去，默认大小是8M，重做日志在以下三种情况下会刷新到重做日志文件中去：1）Master Thread每一秒将重做日志缓冲刷新到重做日志文件；2）每个事物提交时会将重做日志缓冲刷新到重做日志；3）当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志。
插入缓存
在InnoDB存储引擎中，主键是行唯一的标识符。通常应用程序中行记录的插入是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。当一个表有非聚集索引时，对于非聚集索引的叶子节点的插入不是顺序的，这时候需要离散的访问非聚集索引页，性能就在这里降低了，这是由于B+树的原理导致的。插入缓存就是用来解决这个问题的。
对于非聚集索引的插入和更新操作，不是每一次都直接插入索引页，而是先判断插入的非聚集索引页是否在缓存中，如果在就直接插入，如果不在就放入到一个插入缓冲区中，好似欺骗数据库这个非聚集索引已经插入到叶子节点了。然后再以一定的频率插入缓存和非聚集索引页字节点的合并操作。插入缓存的使用需要满足以下两个条件(也就是非唯一的辅助索引)：索引是辅助索引；索引不是唯一的。插入索引自身也是一颗B+树，全局所有的表共享这颗树，这颗树存放在共享表空间。非叶结点存放的是表数据页的偏移量信息，叶子结点存放表数据页信息偏移量+非聚集索引页记录。
关于插入缓存什么时候合并到真正的辅助索引页，合并插入索引在以下三种情况下发生。1）辅助索引页被读取到缓冲池时，当执行select操作时，这时会检查插入缓冲Bitmap页，当确认辅助索引页有记录在插入缓冲中，则将插入缓冲中的记录插入到辅助索引页中；2）插入缓冲Bitmap页追踪到辅助索引页可用空间小于1/32，会强制进行一次合并。3）Master Thread每秒或者每10秒会进行一次插入缓冲合并到辅助索引页。
自适应哈希索引
由于innodb不支持hash索引，但是在某些情况下hash索引的效率很高，于是出现了 adaptive hash index功能，innodb存储引擎会监控对表上索引的查找，如果观察到建立hash索引可以提高性能的时候，则自动建立hash索引。
双写
重做日志结合double write来保证写入的持久性。两次写给innodb带来的是可靠性，主要用来解决部分写失败(partial page write)。在应用重做日志之前，我们需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做。
Double write由两部分组成，一部分是内存中的doublewrite buffer，大小为2M，另外一部分就是物理磁盘上的共享表空间中连续的128个页，即两个区，大小同样为2M。当缓冲池的脏页有刷新时，并不直接写硬盘，而是会通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次写，每次写入1M到共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘。

文件

Mysql 数据库的文件包括以下6中类型文件：

参数文件：my.cnf
日志文件，包括错误日志、查询日志、慢查询日志、二进制日志
Mysql表结构文件：用来存放mysql表结构的文件，一般以.frm为后缀
Socket文件：当用unix域套接字方式进行连接时需要的文件
Pid文件：mysql实例的进程ID文件
innodb存储引擎文件：每个存储引擎都有自己的文件夹来保存各种数据，这些存储引擎真正存储了数据和索引等数据。

参数文件

当MySQL示例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大等。在默认情况下，MySQL实例会按照一定的顺序在指定的位置进行读取，可以通过命令mysql --help | grep my.cnf来寻找。如果启动时没有找到参数文件，这时所有的参数值取决于编译MySQL时指定的默认值和源代码中指定参数的默认值。（Oracle数据库启动时，如果找不到参数文件，是不能进行装载操作的。）
可以通过show variables like 'innodb_buffer%'命令查看数据库所有参数，也可以通过查看information_schema架构下的GLOBAL_VARIABLES视图进行查找（不建议）。
参数类型分为动态参数和静态参数，动态参数可以在MySQL实例运行中进行更改，静态参数不能更改。可以通过SET | [global | session] system_var_name = expr | [@@global. | @@session. | @@] system_var_name = expr来更改

日志文件

日志文件记录了影响MySQL数据库的各种类型活动，包括：

错误日志
二进制日志
慢查询日志
查询日志

错误日志（error log）
对MySQL的启动、运行、关闭过程进行了记录。文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息。用户可以通过命令show variables like 'log_error'定位该文件。
慢查询日志（slow log）
慢查询日志可以帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。例如，可以在MySQL启动时设置一个阈值，将运行时间超过该值得所有SQL语句都记录到慢查询日志文件中。通过参数long_query_time来设置，默认值为10，代表10秒。如果运行的SQL没有使用索引，则MySQL数据库同样会将SQL语句记录到慢查询日志文件。MySQL 5.6.5版本开始新增参数log_throttle_queries_not_using_indexs，用来表示每分钟允许记录到show log的且未使用索引的SQL语句次数。该值默认为0，表示没有限制。
查询日志
查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否正确执行。默认文件名为：主机名.log。查询日志从MySQL 5.1开始也支持放入mysql架构下的general_log表中。
二进制日志(binlog)
二进制日志记录了对MySQL数据库执行更改的所有操作，但是不包括select和show这里操作，因为这类操作对数据库本身没有修改。然而，若操作本身没有导致数据库发生变化，那么该操作可能也会写入二进制日志。
二进制日志的几个作用：
恢复：某些数据的恢复需要二进制日志，例如，在一个数据库全备份文件恢复后，用户可以通过二进制日志进行point_in_time的恢复。
复制：通过复制和执行二进制日志使一台运行的MySQL数据库与一台MySQL数据库进行实时同步。
审计：用户可以通过二进制日志中的信息来进行审计，判断是否对数据库进行注入的攻击。
可以通过参数binlog_format配置二进制存储的格式。在使用恢复和复制同步时，有不同的效果。
binlog更多些信息参考我另一篇文章 Binlog详解

Socket文件

在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，这种方式需要一个套接字文件。show variables like 'socket'\G;命名可以查看套接字文件。

pid文件

当MySQL实例启动时，会将自己的进程ID写入一个文件中——该文件即为pid文件。由参数pid_file控制。

表结构定义文件

因为MySQL插件式存储引擎的体系结构的关系，MySQL数据的存储时根据表进行的，每个表都会有与之对应的文件。但是不论采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义，还用来记录视图的定义。

InnoDB存储引擎文件

表空间文件
InnoDB采用将存储的数据按表空间进行存放的设计。在默认配置下会有一个初始化大小为10MB，名为ibdata1的文件。该文件就是默认的表空间文件（共享表空间），可以通过参数innodb_data_file_path设置。设置innodb_data_file_path参数后，所有基于InnoDB存储引擎的表数据都会记录到该共享表空间中。若设置参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间。独立表空间的命名规则为：表名.idb。需要注意：独立的表空间文件仅存储该表的数据，索引和插入缓冲BITMAP等信息，其余信息还是存放在默认表空间中。
重做日志文件
在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。每个InnoDB存储引擎至少有1个重做日志文件组，每个文件组下至少有2个重做日志文件。影响重做日志文件的属性有：innodb_log_file_size指定每个重做日志文件的大小；innodb_log_files_in_group指定了日志文件组中重做日志文件的数量，默认为2； innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，表示只有一个文件组，没有镜像； innodb_log_group_home_dir指定了日志文件所在路径，默认为./，表示在MySQL数据库的数据目录下。

数据表

在innoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式存储的表称为索引组织表，数据即索引，索引即数据，B+树的叶子结点即为就是数据段，非叶子结点即为索引段。每张表都会有主键，如果用户没有指定，会找第一个非空唯一健，如果没有，innodb会长生一个6字节大小的指针。
所有的数据都被逻辑存放在一个空间中，称之为表空间，表空间由段、区、页组成。Innodb引擎有一个共享表空间ibdata1，即所有的数据都存放在这个表空间内。如果用户启动了innodb_file_per_table，表内的数据都可以单独放到一个表空间，每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页。表空间常见的段有数据段、索引段、回滚段等。区为连续页组成的空间，区的大小为1M（即64个页）。页在也是我们常说的数据块。在InnoDB存储引擎中，常见的页类型有：数据页、undo页、系统页、事务页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页等。页盘管理的最小单位，下面是InnoDB的表空间结构图：

image.png

Page页面存储格式如下图所示：

image.png

一个页面的存储由以下几部分组成：

页头（Page Header）：记录页面的控制信息，共占56字节，包括页的左右兄弟页面指针、页面空间使用情况等。
最小虚记录、最大虚记录：两个固定位置存储的虚记录，本身并不存储数据。最小虚记录比任何记录主键都小，而最大虚记录比任何记录主键都大。
记录堆（record heap）：指上图的橙黄色部分。表示页面已分配的记录空间，也是索引数据的真正存储区域，存储行记录的数据。记录堆分为两种，即有效记录和已删除记录。有效记录就是索引正常使用的记录，而已删除记录表示索引已经删除，不在使用的记录，如上图的深蓝色部分。随着记录的更新和删除越来越频繁，记录堆中已删除记录将会越多，即会出现越来越多的空洞（碎片）。这些已删除记录连接起来，就会成为页面的自由空间链表。
未分配空间：指页面未使用的存储空间，随着页面不断使用，未分配空间将会越来越小。当新插入一条记录时，首先尝试从自由空间链表中获得合适的存储位置（空间足够），如果没有满足的，就会在未分配空间中申请。
slot区：slot是一些页面有效记录的指针，每个slot占两个字节，存储了记录相对页面首地址的偏移。如果页面有n条有效记录，那么slot的数量就在n/8+2~n/4+2之间。它是记录页面有序和二分查找的关键。
页尾（Page Tailer）：页面最后部分，占8个字节，主要存储页面的校验信息。
页面中的页头，最大/最小虚记录以及页尾都是页面中有固定的存储位置。
InnoDB和大多数行式数据库一样，记录以行的格式存储，它提供了两种格式：Compact（5.0版本之后）和Redundant（5.0版本之前），我们只讲Compact格式，Redundant可以参考其他资料。
Compact
Compact格式是在MySQL5.0时才被引入，它是新的行格式，其设计目标是高效存放数据，示意图如下：

image.png

1）变长字段长度列表。按列的逆序放置，当列长度小于255字节，用1字节表示，若大于255字节，用2个字节表示，至多为2字节（这也很好解释了InnoDB中varchar的最大长度为什么是65535，因为2个字节为16位，2的16次方-1）。注意，对于固定长度字段（如char），是不需要记录其长度的。
2）NULL标志位。指示该行中的列是否为NULL，1表示NULL。
3）记录头信息。固定占用5个字节（40位）。
4）最后就是实际存储的该行每列的数据了，注意：NULL不在该部分中占用存储。
5）此外还有两个隐藏部分，分别存放事务ID和回滚指针，大小分别为6字节和7字节，如果表没有定义主键，每行还会自动增加6字节的ROWID。

变长字符字段最大支持65532个字符，当行数据大小超过16Kb时，会在数据页中存放部分字节的前缀数据，之后都是偏移量，指向益出页（Uncompressed Blob Page）。一个页中至少存放两行记录。当行数据的大小很小（即便是varchar、blob、text字段），也可能存放在数据页中。

索引

InnoDB使用B+Tree的方式存储索引， Innodb的一个表可能包含多个索引，每个索引都使用B+树来存储。而索引包括聚集索引和二级索引，聚集索引使用表的主键作为索引键，包含表的所有字段。二级索引只包含索引键和聚集索引键（主键）的内容，不包括其他字段。每一个索引都是一棵B+树，每棵B+树由很多页面组成，而每个页面大小一般为16K。从B+树的组织结构来看，B+树的页面可分为：

叶子节点：B+树层次为0的页面，存储记录的所有内容
非叶子节点：B+树层次大于0的页面，只存储索引键和页面指针。

一棵典型的B+树结构：

B+树结构

从上图可知，相同层次的页面是用一个双向链表连接起来的。一般情况下，从B+树的最左边叶子节点开始，一直向右扫描，就可以得到B+树的从小到大的所有数据。因此，对于叶子节点，有如下特征：

页内数据是按索引键排序的。
页面的任一记录的索引键值不小于其左兄弟页面的任何记录。
不可能在非叶子结点命中，只有达到叶子结点才命中；
非叶子结点相当于是叶子结点的索引，叶子结点是存储（关键字）数据的数据层；

B+树的特性决定了索引的限制：

必须从索引的最左列开始寻找
不能跳过索引中的列
范围查找列之后的索引列无法使用

聚簇索引VS辅助索引

聚簇索引 ：索引指针直接指向数据页的索引，聚簇索引对数据物理页按索引键值进行物理组织排序；
主键索引 ：建在主键上的索引；innodb中，聚簇索引在有主键的情况下，默认指定主键为聚簇索引,因此,innodb中，主键索引一般都是聚簇索引。
二级索引：除了聚簇索引的，都称为2级索引；innodb中，二级索引查找数据行，需要先找到对应的主键，然后根据主键查找对应的行值。

innodb中，聚簇索引是mysql自己来决定的。选择的顺序是：
（1）定义了主键，就选择主键；
（2）没有主键，选择第一个非空的唯一索引；
（3）两者都没有，innodb自己生成一个占6byte的自增长列。然后以它作为聚簇索引列

索引执行计划—Explain字段解析

参考附录三

附录

附录一：MySQL基本数据类型

数据类型	描述	字节	推荐使用
SMALLINT	整数，从-32000到 +32000范围	2	存储相对比较小的整数。比如: 年纪，数量
INT	整数，从-2000000000 到 +2000000000 范围	4	存储中等整数例如: 距离
BIGINT	不能用SMALLINT 或 INT描述的超大整数。	8	存储超大的整数例如: 科学/数学数据
FLOAT	单精度浮点型数据	4	存储小数数据例如:测量，温度
DOUBLE	双精度浮点型数据	8	需要双精度存储的小数数据例如:科学数据
DECIMAL	用户自定义精度的浮点型数据	变量;取决于精度与长度	以特别高的精度存储小数数据。例如:货币数额，科学数据
CHAR	固定长度的字符串	特定字符串长度(高达255字符)	存储通常包含预定义字符串的变量例如: 定期航线，国家或邮编
VARCHAR	具有最大限制的可变长度的字符串	1 + 实际字符串长度	存储不同长度的字符串值(高达一个特定的最大限度).例如:名字，密码，短文标签
TEXT	没有最大长度限制的可变长度的字符串	2 +实际字符串长度	存储大型文本数据例如: 新闻故事，产品描述
BLOB	二进制字符串	变量；2 + 实际字符串长度	存储二进制数据例如:图片，附件，二进制文档
DATE	以 yyyy-mm-dd格式的日期	3	存储日期例如:生日，产品满期
TIME	以 hh:mm:ss格式的时间	3	存储时间或时间间隔例如:报警声，两时间之间的间隔，任务开始/结束时间
DATETIME	以yyyy-mm-ddhh:mm:ss格式结合日期和时间	8	存储包含日期和时间的数据例如:提醒的人，事件
TIMESTAMP	以yyyy-mm-ddhh:mm:ss格式结合日期和时间	4	记录即时时间例如：事件提醒器，"最后进入"的时间标记
YEAR	以 yyyy格式的年份	1	存储年份例如:毕业年，出生年
ENUM	一组数据，用户可从中选择其中一个	1或 2个字节	存储字符属性，只能从中选择之一例如:布尔量选择，如性别
SET	一组数据，用户可从中选择其中0，1或更多。	从1到8字节;取决于设置的大小	存储字符属性，可从中选择多个字符的联合。例如:多选项选择，比如业余爱好和兴趣。

附录二：MySQL库表设计规范

表设计

表必须定义主键，默认为ID，整型自增，如果不采用默认设计必须咨询DBA进行设计评估
2)ID字段作为自增主键，禁止在非事务内作为上下文作为条件进行数据传递，禁止非自增非数字类型主键设计出现
3)禁止使用外键,触发器,存储过程
4)多表中的相同列，必须保证列定义一致
5)表默认使用InnoDB，国内表字符集默认使用gbk，国际默认使用utf8的表
6)表必须包含gmt_create和gmt_modified字段，即表必须包含记录创建时间和修改时间的字段
7)单表一到两年内数据量超过500w或数据容量超过10G考虑分表，且需要提前考虑历史数据迁移或应用自行删除历史数据
8)单条记录大小禁止超过8k（列长度(中文)*2（gbk）/3(utf8)+列长度(英文)*1）
9)日志类数据不建议存储在MySQL上，优先考虑Hbase或OB，如需要存储请找DBA评估使用压缩表存储
10)主键不允许修改，如特殊需求，需要提前一个月和DBA沟通方案
11)绝对禁止使用MySQL保留关键字作为表名、列名、索引名等
关键字列表：
MySQL5.6：http://dev.mysql.com/doc/refman/5.6/en/keywords.html
MySQL5.5：http://dev.mysql.com/doc/refman/5.5/en/keywords.html
12)可变长度设计(例如varchar)一定要按照需要设计，同时控制单行长度，如果过大会降低数据库Buffer命中率，导致更新、查询性能下降

字段设计

表被索引列必须定义为not null，并设置default值
禁止使用float、double类型，建议使用decimal替代
3)禁止使用blob、text类型保留大文本、文件、图片，建议使用其他方式存储（TFS/SFS），数据库(MySQL/Oracle)只保存指针信息
4)禁止使用varchar类型作为主键语句设计

附录三：Mysql Explain 详解

语法： explain <table_name>;
例如: explain select * from t3 where id=3952602;

explain输出解释