概览

对MySQL的基础知识和原理的总结。包括MySQL的架构、索引、事务，以及MVCC的实现原理。

架构

MySQL的整体架构如上图所示，上下大体上可以分为Server层，和存储引擎层。MySQL通过各种各样的存储引擎实现数据的实际读写，存储引擎以插件的方式集成；而Server层主要负责管理客户端连接，认证鉴权，SQL语句分析，以及一些跨存储引擎的功能。比如存储过程、触发器、视图等等。MySQL的内置函数也都是在Server层提供的。Server层通过调用底层的存储引擎，来完成对数据的读写。

Server层绿色模块从上至下分别是连接器、分析器、优化器，和执行器(有些资料上没有这个模块)。蓝色模块则是查询缓存。

存储引擎层则只是对诸如MyISAM、InnoDB、NDB等的存储引擎的集成，实现数据的存储，并提供接口给Server层的执行器调用。

由于不同存储引擎具体的实现差异较大，且内容较多，这里仅对Server层的各个组件进行说明。

Server层组件

连接器

连接器用于和客户端建立连接，并维持和管理连接。在连接建立时会查询用户的权限表获取到该用户的所有权限，并缓存起来在这个连接的对象中，后续的所有权限判断，都是基于这里得到的权限信息。

客户端建立连接的过程大致如下：

首先进行TCP握手，建立TCP连接。
然后进行身份认证。这一步会查询MySQL的权限表，从权限表中拿到用户的所有权限，并保存起来。
通过用户的身份认证之后，连接就建立起来了。

通过show processlist命令可以查看到当前数据库的所有连接：

mysql> show processlist;
+----+-----------------+-----------+-----------+---------+--------+------------------------+------------------+
| Id | User            | Host      | db        | Command | Time   | State                  | Info             |
+----+-----------------+-----------+-----------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL      | Daemon  | 169432 | Waiting on empty queue | NULL             |
| 11 | root            | localhost | employees | Query   |      0 | init                   | show processlist |
+----+-----------------+-----------+-----------+---------+--------+------------------------+------------------+
2 rows in set, 1 warning (0.00 sec)

当客户端太久没有操作时，会被连接器断开。超时时间通过参数wait_timeout控制。

mysql> show variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.03 sec)

和MySQL建立连接时需要注意的问题：

大量的短连接可以改成长连接。

如果每次都进行很少的操作就断开连接，下次查询时再重新建立连接的话，会增加很多不必要的资源浪费。尽量使用长连接，并在多个查询中复用这个连接。或者通过语言的框架维护的连接池，要访问MySQL时就去连接池里拿一个连接回来。

大量的长连接也会造成问题。

如果有大量的长连接，由于连接过程中所执行的一些操作都会保存在连接器的连接对象里，占用很多内存资源，严重时甚至会OOM。如果长连接太多，并且观察到数据库的内存占用超过了阈值，那么要么扩大内存，或者断开一些长连接，再重连。对于MySQL5.7以上的版本，可以在进行了一个大的操作后，使用命令mysql_reset_connection来初始化连接资源，这并不会让这个连接断开，只是清理了连接对象的一些资源，减少问题发生的可能性。

查询缓存

查询缓存是一种用于提高数据库查询性能的机制。当开启了查询缓存，那么在建立了连接后，会先到查询缓存中查找，看能否命中。查询缓存中存储的是key-value形式的数据，key就是查询类型的SQL语句的哈希值，value就是结果集，和操作的所有表的信息。

如果命中了查询缓存，则会检查连接时获取到的用户的权限信息，和查询缓存中记录的访问的所有表进行对比，确认用户拥有访问这些表的权限后，就可以直接把结果集返回给用户，不需要再将请求向下传递。

如果没有命中，就需要继续向后走分析器、优化器，直到从存储引擎中得到数据集。再返回给客户端之前，这个结果集会保存一份到查询缓存中，用户后续如果有相同的查询，则可以命中。

但是查询缓存有一些棘手的问题：

只要有一个表有更新，那么这个表涉及的查询缓存都会被清空。

所以对于更新频繁的表，查询缓存命中率非常的低，除非是那种几乎不会变的表。

查询语句不同，即使结果集相同，也不会命中查询缓存。

由于查询缓存是通过key-value的方式检查是否命中，而key就是SQL语句，所有只要是不同的SQL语句，即使他们查询得到的结果集完全相同，页无法命中。反而会导致查询缓存中保存两份相同的结果集，浪费宝贵的内存空间。

哈希计算，哈希查找，哈希碰撞都会带来额外的性能消耗。

每次查询都需要进行哈希计算查找，如果还没命中，就更得不偿失了。

基于以上的问题，MySQL 8.0开始不再支持查询缓存。

分析器

分析器作用就是解析SQL语句，将SQL语句转换为MySQL可以执行的函数。

分析器对SQL语句的处理过程可以分成词法分析，和语法分析两部分：

词法分析

这个阶段会识别出SQL语句的各个字符串是什么，代表什么，然后生成一颗解析树。不管你SQL写的对不对，最终都能生成一颗解析树，所以在词法分析阶段是不会报错的。

语法分析

在词法分析之后，需要对解析树进行判断，判断是否满足MySQL的语法，这里会访问表结构，检查表名、字段名等是否正确，如果有错误就会报错给客户端。

在分析器执行完词法和语法分析之后会有一次权限验证，叫做 precheck，因为这一步可以知道这个SQL要访问的是哪个数据库，哪个表了，所以可以进行一次预检查，依然是通过连接时保存的用户权限信息进行检查，如果权限没问题，则会进行下一步。否则可以直接返回权限错误，免得再继续向下执行，浪费资源。

优化器

优化器的作用是对分析器分析出来的操作过程进行优化。比如，如果要访问的表里有多个索引，那么使用哪个索引最优，就是优化器要考虑的问题。再比如，多表join时，先查哪个表，再查哪个表，也是优化器需要确定的。

执行器

有些资料上没有这个模块（比如《高性能MySQL》）。不管有没有这个模块，这部分要做的事情都非常简单。

首先在执行器的一开始，会做最后一次的权限校验，如果权限不够则会返回错误。如上所述，在分析器的最后可以确认与SQL语句本身相关的表有哪些，可以确认用户对这些表是否有操作权限。但是还有些其他的操作可能在SQL语句中并未体现，比如如果有个触发器，则必须在实际执行的阶段，才能知道，所以在执行器阶段会做最终的权限校验。

把权限的检查分解到多个步骤中，也是为了提高效率，可以在权限不满足时能够快速返回，避免做无用功。

如果权限认证通过，则会调用表对应的存储引擎的接口拿到数据或完成操作。根据操作类型不同，可能会再对数据做一些处理，比如排序等，然后返回结果给连接器，由连接器返回给客户端。

索引

索引是一种数据结构，可以为不同的场景提供更好的性能。对于数据库来说，索引的功能就是提高数据查询的效率，但同时不可避免的也会增加数据写入的消耗，二者之间需要一个平衡，只要当查询带来的好处远远大于写入时的额外工作时，索引才是有意义的。

数据库表建立索引的主要优势有：

大大减少服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机IO变为顺序IO。

常见的数据库索引类型

哈希表

key-value形式的存储，可以O(1)时间复杂度查询，缺点是只能等值查询，难以进行范围查询。

有序数组

在等值查询和区间查询都非常优秀，等值查找用二分法，时间复杂度是O(logN)，区间查找就用二分先找到一个边界，然后向一侧遍历即可。缺点是插入删除太麻烦，每插入删除一条数据要移动整个数组。所以只适用于静态存储，不怎么需要插入删除的场景下会比较合适。

二叉搜索树

特点是左子节点的值比右子节点小，所以也是二分法的应用，这查找插入删除性能都不差，时间复杂度都是O(logN)，缺点是当左子数或右子树中没值时会退化成链表，查找效率大大降低，并且也不支持范围查找。

二叉平衡树

解决了二叉搜索树可能退化成链表的问题，但是缺点是只有二叉，当数据大的时候，树的层数会很高，如果数据存储在磁盘，那么一次查询可能要很多次访问磁盘，查找效率依然不令人满意，并且同样也不支持范围查找。

跳表

跳表是在链表之上加上多层索引构成的，支持快速插入、查找、删除数据，时间复杂度都是O(logn)，并且跳表也支持按照区间快速查找数据，看起来是比较完美的索引数据结构，Redis里采用了这种数据结构实现了它的Sorted Set。但是和二叉平衡树类似，问题还是在于层数可能过高，适合基于内存型的数据库比如Redis，不适合基于磁盘的数据库。

B树

B树就是多叉平衡树，为了二叉搜索树尽可能减少访问磁盘，把二叉升级为多叉，并控制树的深度。B树是很适合磁盘存储的数据结构。

B+树

B+树是B树的升级，主要对B树做了两点数据结构上的优化：

1.首先是将节点分成了索引节点和数据节点(也叫叶子节点)。索引节点只存索引，不存数据，只让叶子节点存数据。

2.相邻的叶子节点之间连接了起来。

这样做有两点好处：

1.查询效率会更加平衡。B+树一般是2-4层，索引节点不存储数据，只有数据节点存储数据，这就导致所有数据的访问都是 2～4 次磁盘IO就可以拿到数据了，很稳定。而B树所有节点都存储数据，就导致当数据在高层节点时，访问速度可以很快，数据在低层就慢，非常的不平均，稳定性很差。

2.一次IO可以拿到更多的索引值。由于索引节点不存数据，所以一次IO可以拿到更多的索引节点。对于B树来说，索引节点有存储数据，如果不是我们想要的，就浪费了大量的空间，一次磁盘IO从B+树获取的信息量远大于B树。

3.优化范围查询。由于叶子节点被连接起来，所以通过前一个叶子节点可以快速访问到后面的叶子节点，提升范围查询的效率。

MySQL支持的索引类型

MySQL中不同的存储引擎可以支持不同的索引类型，以应对不同的数据存储需求。

B+树索引

MyISAM和InnoDB都支持了B+树索引，在使用上有所不同。

空间数据索引(R-Tree)

MyISAM存储引擎支持空间数据索引，但是MyISAM做的并不完善，PostgreSQL和PostGIS有更好的实现。

哈希索引

Memory存储引擎显式支持哈希索引，InnoDB存储引擎也有自适应哈希索引的存在。

全文索引

MyISAM和InnoDB都支持全文索引。通过倒排索引来实现。倒排索引也是一种数据结构，它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置的映射，通常利用关联数组实现。

分形树索引(Fractal tree)

第三方的存储引擎TokuDB支持这种索引，它有B+树的很多优点，也避免了一些缺点，它可以优化写路径；大部分二级索引的写操作是异步的。分形树是一种写优化的磁盘索引数据结构，写操作性能非常好，同时读操作还能近似于B+树（略低）；并且这种数据结构还天然支持可以在写操作的同时修改表结构。

在写多读少的场景下这种存储引擎比较合适，缺点是资料较少。

接下来我们详细讲述一下当前使用最多的B+树索引的实现方案。

B+树索引

B+树是以页为单位来存储数据的，不管是索引节点还是叶子节点，都是以页为单位存储。InnoDB存储引擎中页的大小默认是16kb。索引节点中只存索引的key，叶子节点中才会存储value。并且数据页之间是以链表的形式连接起来的，包括不同索引页的叶子节点。并且链表的顺序是按照索引key的顺序存储的，当然这是为了方便按索引范围来取数据。

插入操作

B+树的插入操作必须保证插入后叶子节点中的记录依然有序，插入B+树会有三种情况，可能会导致不同的算法：

当索引节点和叶子节点都没有满时，可以直接将数据记录插入到叶子节点上。
当索引节点没满，但是叶子节点满了时，需要拆分叶子节点，拆分叶子节点时会创建出一个新的叶子节点，将旧页中中间记录的索引值放入上层的索引节点中，然后小于该索引的记录放在旧的叶子节点，大于该索引的记录放在新的叶子节点中。也就是叶子节点会被平均分。
当索引节点满了，叶子节点也满了，那么索引节点和叶子节点都要进行拆分。拆分过程和以上类似，都是创建一个新页，然后把原本的索引和数据记录都对半分。

以上由于叶子节点满了导致页的拆分，称为页分裂。频繁的页分裂会有大量的数据移动十分影响性能，并且页分裂都是对半拆分的，会导致页中出现大量的空位造成空间浪费。

页分裂的优化

旋转优化

由于页分裂造成的性能和空间浪费问题，B+树同样提供了类似二叉平衡树的旋转功能，来尽量较少页分裂。当叶子节点满了时，会去检查它兄弟节点是否也满了，一般左兄弟会被优先检查（因为要尽量保证树是完全的），如果有空位，就会将此节点上的数据挪动一些到兄弟节点上，就不用进行页分裂了。

顺序插入优化

如果我们的索引是顺序递增的，更常见的情况是写满一个页后才开始进行页分裂，如果是对半分，而后续又继续在最后一页向后追加插入，那么原本的页就是又一半空间永远都不会被插入数据，造成空间浪费。

为了解决这个问题，InnoDB会在插入时根据情况决定是向左还是向右进行分裂，以及分裂点在哪个位置。当页在插入新行会导致页写满时，InnoDB会检查要插入的这一行的后面原本有没有超过三条记录，如果有，就会取三条记录后作为页分裂点，如下图：

而如果新插入的记录是这个页的最后一条记录的话，则会以自己作为分裂点，如下图：

也就是说，按照顺序插入的话，写满一个页后就会重新创建一个新页继续写，而不会在旧页的中间进行分裂。而如果是随机插入则会遵循对半分的页分裂策略。所以使用B+树，应该尽量保证按照索引的顺序进行插入。

删除操作

B+树使用填充因子来控制树的删除变化，填充因子就是一个页中实际使用的空间和全部空间的占比，50%是填充因子可以设置的最小值。B+树需要保证删除后叶子节点的记录依然有序，和插入类似，删除操作也会有三种情况：

删除后叶子节点和索引节点都不小于填充因子时，可以直接将该记录从叶子节点删掉，如果在索引节点上就从索引节点也删掉。
删除后叶子节点小于填充因子，而索引节点不小于填充因子时，需要在删除后将该叶子节点和它的兄弟节点合并，以保证叶子节点的填充因子满足要求。
删除后叶子节点和索引节点都小于填充因子，那么就需要先合并叶子节点，然后更新索引节点，索引节点也满了，那么在合并索引节点。

以上由于叶子节点的填充因子小于预设值时导致的页合并，页合并同样也需要挪动大量数据，也会造成性能浪费。也需要我们合理的配置填充因子，来平衡性能和空间的浪费。

InnoDB对B+树的使用

InnoDB存储引擎支持主键索引、唯一索引、普通索引和全文索引。其中，前三个都是基于B+树实现的。

InnoDB是索引组织表。InnoDB存储引擎的每张表都会有一个主键，如果没有指定，在创建表时，InnoDB会生成一个名叫row_id的隐藏字段作为该表的主键。然后InnoDB会根据主键生成一颗B+树，并在这颗B+树中存放表中的所有数据。一个表其实就是一颗主键索引的B+树，在B+树的索引节点上存储的是主键，叶子节点上存储的是主键和数据行，因此主键索引也叫做聚簇索引或者聚集索引。

而对于表的每一个其他的索引，也都会创建出一颗B+树，可以说，一个表有几个索引，那么它就有几颗B+树存储在磁盘上。因为主键索引上存储着完整的数据行，所以其他索引就没必要存储完整的数据行，其他索引B+树的索引节点上存储的是索引列，叶子节点上存储的是索引列和主键值。

基于以上差别，在通过主键索引和非主键索引进行查询整行数据时，比如select * from T;，使用主键索引只需要搜索一次B+树，而非主键索引则需要搜索两次B+树，第一次根据索引列找到主键值，然后再根据主键拿到数据行（后面根据主键值再查一次的操作被称为回表），才能获取到整行数据。

覆盖索引

当可以通过直接扫描k索引树就能得到结果，而不需要回表时，这就称之为覆盖索引，覆盖索引可以大幅提升性能，是一种常用的优化手段。

如我们前面所说，非主键索引的B+树叶子节点中存放的的是索引值和主键，那么我们要通过这个索引查询到某个非主键的其他字段时，就需要根据得到的主键值，再查一次主键索引，从主键索引中拿到完整的数据行，再获取想要的字段，我们说过，一颗B+树大概2～4层，访问一次需要2～4次磁盘IO，我们访问了两颗B+树的话，实际上就需要4～8次磁盘IO，这样性能很差。所以如果要查的字段通过非主键索引就能拿到的话，那么性能自然就大大提升了。

使用自增主键

如上所述，B+树为了保持有序性，在插入新值时要做维护。如果插入的行在最后的话，就只需要在树的末尾添加，但如果是在树的中间插值，就需要挪动后面的数据空出位置。如果又恰好此时这一个数据页满了，就还要进行页分裂操作，并且随机插入不能用到B+树的顺序插入优化，性能会受到影响，同时空间也会有大量浪费。

删除数据时，如果页的利用率很低（可能因为页分裂造成了很多空位，也可能是删除操作后没有重新optimize table），会将数据页合并，同样性能也会受影响。

基于页分裂的特性，InnoDB在插入时，尽可能的在最后按顺序插入，这样既不需要在插入时挪动数据，也会减少页分裂的发生，这也是自增主键的好处。同时自增主键的占用空间上也要比UUID之类要小，还不用担心重复的问题。自增主键还可以进行方便的进行范围查询。

Caidinality值

一般我们都知道，像性别，类型等字段我们都不会给它建立索引，因为他们的取值范围很窄，只有固定的几个值，也就是低选择性。这种情况下B+树索引的查询和插入优化几乎等于没有，因为建立起来的索引一共就没几个节点，相反，如果一个字段的取值范围很广，少有重复的值，那它就是高选择性，给它建立一个B+树索引就能提升查询效率。

通过show index from table命令查询一个表的索引信息，其中的Cardinality字段可以观察该索引的选择性，Cardinality是一个预估值，并不是准确值，基本上我们期望 Cardinality/总行数 趋近于1比较好，如果太小，那么就需要考虑是否有必要创建这个字段的索引。

mysql> show index from titles;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| titles |          0 | PRIMARY  |            1 | emp_no      | A         |      207612 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          0 | PRIMARY  |            2 | title       | A         |      215203 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          0 | PRIMARY  |            3 | from_date   | A         |      215616 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Cardinality值的统计是通过采样来判断的，这也是这个值可能不准确的原因。InnoDB会在一定的时机触发执行Cardinality的统计，通过参数innodb_stats_sample_pages可以设置采样的页数，默认是8，显然值越大统计的越准，但同样也会增加消耗。

其他索引类型

前缀索引

InnoDB允许给一个字段建立前缀索引，即可以取字段前缀的一部分作为索引，类似于BLOB、TEXT或者很长的VARCHAR类型的列，必须要使用前缀索引。创建前缀索引的关键就是要尽量选择短一点的前缀，并且还要有比较高的选择性，让前缀索引的选择性尽可能趋近于索引完整列。

比如类似邮箱类型的字段，后缀都是固定的字符串，就非常适合前缀索引；其实对于这样的字段，还可以在数据库中直接只存储前缀，然后给完整的列创建一个索引。

联合索引

InnoDB不仅允许一个字段建一个索引，还可以多个字段一起建立一个索引，以节省空间。联合索引就是多个字段一起建一个索引。联合索引也是一颗B+树，只不过存储的key不是一个字段，而是多个字段。比如我们对字段name和字段age建立一个联合索引。那么生成B+树的索引节点上就会保存类似(<name>, <age>)这样的数据了。

对于联合索引的查询，需要满足最左前缀法则。比如上面这个例子，我可以通过name查询，或者通过（name，age）查询时都可以用到这个索引，但是如果只通过age来查询，则用不到这个索引。如果非要单独查询age，那就只能再给age字段也加一个索引，这种情况也不适合使用联合索引。

最左前缀不仅可以查询完整字段的最左前缀，如果字段是字符串，还可以指定字段一部分的最前缀。比如对(first_name, last_name)建立联合索引，那么当我们查询条件时first_name=“Harry”，last_name LIKE “Y%“时，也符合最左前缀法则。

索引优化

ICP优化

MySQL 5.6 版本以后增加了索引下推优化（Index Condition Pushdown，ICP），用于在仅能利用最左前缀索的场景下（而不是能利用全部联合索引），对不在最左前缀索引中的其他联合索引字段加以利用——在遍历索引时，就用这些其他字段进行过滤。

比如如果我要查name字段第一个字是"张”，并且age=10的行，那么按照最左前缀法则，这个查询用不到联合索引，只能用name字段先查到以"张"开头的行，再回表根据age字段进行过滤。而索引下推就可以在遍历的过程中，也能检查并过滤不满足条件的age的值。

通过explain执行计划中的Extra字段中有"Using index condition”，就代表这个查询会用到索引下推的优化。

MMR优化

MySQL5.6版本以后还支持了Multi-Range Read(MMR)优化，MMR优化的主要目的就是为了减少磁盘的随机访问，将随机访问尽量转换为偏顺序的访问。MMR优化适合于range、ref、eq_red类型的查询。

比如一个表中有(id, age, name)三个字段，id是主键索引，age是普通索引，当我们想查询某个范围的age对应行的name字段时，由于不是覆盖索引，需要先通过age字段的B+树，找到id字段，然后通过id字段查询主键索引的B+树即回表，拿到所有信息后即可拿到name字段。而在访问age字段的索引时，age是一个顺序，可以用到range，并且得到的(age, id)数据集是以age进行排序的，然后再逐行根据id去回表，而id可能此时并不是顺序的，即回表时会有很多的随机IO操作。MMR优化就是当获取到的(age, id)先根据id进行排序，再去主键索引上查询，这样随机IO就变成较为顺序的IO操作了，并且如果主键有连续时，还可以根据主键批量查询，效率就得到了提升。

总结就是，对于使用普通索引范围查询和join查询，并且需要回表时：

1.查询普通索引得到数据集，此时是根据普通索引排序的；

2.将数据集根据主键进行排序；

3.然后可以顺序IO和批量操作完成查询。

MyISAM对B+树的使用

MyISAM的主键索引和InnoDB的主键索引虽然都使用了B+树，但是二者的数据分布不同。InnoDB是索引组织表，即一个表其实就是一颗索引树，MyISAM存储表数据不是这样的，MyISAM的表数据是按顺序存储在磁盘上的，如下图：

可以看到，类似我们从MySQL客户端查询时看到的表一样，数据是一行一行存储的。这种方式类似于前面所说的数组索引，可以通过跳过固定的字节数找到需要的行（MyISAM并不总是使用行号，而是根据字段定长还是变长的行使用不同的策略）。

MyISAM的索引也是一颗B+树，但和InnoDB不同的是，它的叶子节点上存储的是表实际存储位置的行号（或者理解为行实际存储位置的指针），如下图：

MyISAM存储引擎的所有索引都一样，即所有的索引叶子节点上存储的都不是整行数据，而是”行号“。主键索引和普通索引唯一的不同只是它是唯一且非空的而已。

事务

事务就是一组原子性的SQL语句。这一组SQL语句要么全部执行成功，要么全部不执行。只要有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。事务处理系统需要包含以下四个特性(ACID)：

原子性(atomicity)

一个事务必须被看作是不可分割的最小工作单元。不可能执行成功其中的一部分操作，要么全部提交成功，要么全部失败回滚，不能存在部分成功部分失败的状态。

一致性(consistency)

数据库总是从一个一致性的状态，转换到另一个一致性的状态。

这句话很不好理解，并且在其他很多场景下比如分布式系统中也有一致性的概念，但其实不太一样。数据库的一致性指的是要保证约束条件的一致。举个例子，比如某个字段有唯一性约束，那么在事务执行完成之后，该字段也应该还是唯一的，不能因为事务的执行导致约束发生变化，约束可以是数据库层面的，也可以是业务层面的，如果某个约束数据库无法保证的话，那么就需要我们在应用程序中给予保证。

隔离性(isolation)

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务所干扰，多个并发事务之间要相互隔离。这一点大部分数据库都没有完美遵守，比如MySQL就提供了四种隔离等级。

持久性(durability)

一旦事务提交，那么所作的修改就会永久保存到数据库中。换句话说，事务所作的修改要持久化，即使数据库宕机也不会导致数据丢失。

隔离等级

对于事务的隔离性，实际上比较复杂。SQL标准中定义了四种隔离等级，规定了一个事务中所作的修改，哪些在事务内和事务间是可见的，哪些是不可见的。四种隔离等级分别是：读未提交、读已提交、可重复读和串行化。InnoDB存储引擎默认的隔离级别是可重复读，其他数据库大部分采用读已提交作为默认的隔离等级。

读未提交

READ UNCOMMITTED。顾名思义，就是可以读到其他事务还未提交的内容。在未提交读这个级别下，所有事务中的修改，即使没有提交，其他事务也可以看到，这就叫做脏读。显而易见，这个级别会导致很多问题，并且性能上来说，也没有比其他隔离级别好很多，所以几乎是没人用的。

读已提交

READ COMMITTED，也叫RC隔离等级。顾名思义，和上一个等级的差别就在于，该隔离等级下，每个事务是能读取到当前已提交的事务所作的修改，即一个事务在提交之前，对其他事务都是不可见的。这个等级是大多数数据库的默认隔离等级，但不是MySQL的。该级别下存在不可重复读和幻读的问题。

不可重复读

如果在一个事务执行过程中，有另一个事务从未提交状态，转变为了提交状态。那么那个事务所修改的行就会发生改变。如果在当前事务中前后两次查询这个行，就有可能两次查到的信息不一致，这就是不可重复读的问题，这个问题MySQL通过可重复读的隔离级别解决。

幻读

所谓幻读，就是指在某个事务中多次读取某个范围的记录时，另一个事务在该范围中执行了插入操作并提交，则该事务后续再次读取相同范围的行时会出现行数增加的问题，插入的行就叫做幻行，这种问题就叫幻读。

MySQL通过MVCC(多版本并发控制)和间隙锁(Gap Lock)解决了幻读的问题。而间隙锁只有RR隔离级别下才会生效。后续我们会详细讨论。

可重复读

REPEATABLE READ，也叫RR隔离等级。该隔离等级和RC隔离等级的差别在于，这个等级解决了不可重复读的问题，保证了在一次事务中多次读取同一行数据的结果是一致的。

具体的解决方法是通过MVCC机制保证事务中查询到的永远是事务开始前提交的数据。但是标准的RR隔离级别依然无法解决幻读的问题。如上所说这个问题需要通过间隙锁彻底解决。

串行化

SERIALIZABLE，是最高的隔离级别。通过强制事务串行执行，可以避免前述的脏读、幻读、不可重复读的所有问题。可串行化隔离级别下会在每一行数据上都加锁，所以效率也是最低的。只有在非常需要保证数据的一致性的时候且可以接受没有并发的情况，才会采用该级别。实际上应用和读已提交一样非常少。

MySQL中使用事务

MySQL中通过参数数transaction_isolation用于配置MySQL的事务隔离等级，默认是RR的隔离级别。MariaDB和MySQL5.7以下版本用的是参数tx_isolation：

mysql> show variables like "transaction_isolation";
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

显式启动事务，使用begin或者start transaction，要注意的是，执行了这两句之后直到第一个SQL语句执行时，这个事务才真正开始启动。如果要马上启动，可以用start transaction with consistent snapshot命令，即立刻生成一张快照，这样这个事务才真正启动了。

提交用commit，回滚用rollback。

commit work and chain命令，意思是提交一个事务再开启一个新的事务，等价于commit + begin。

设置set autocommit=0，这会将事务的自动提交关掉。

由于InnoDB的默认每条语句都是一个事务，意味着每执行一次语句，这个事务就会一直启动并且不会自动提交，直到主动执行commit或者rollback或者断开连接。因此最好还是设置autocommit=1。

避免长事务

长事务指的就是执行时间很长的事务，事务长时间不提交会造成很多问题。

事务中的写操作还会给行添加写锁，并且只有事务结束后才能释放，导致很多其他事务都被阻塞住。
一个事务中的每次操作都会记录到undo log中，insert undo log可以在事务结束后就被删除，undo log过长也会占用很多内存和磁盘。
长事务还会导致主备延迟。

要避免长事务，下面是一些可以参考的建议

设置autocommit=1，单独的sql语句让他自动提交，避免因为忘了提交造成的长事务。
监控information_schema.Innodb_trx表，设置长事务阈值，超过就报警或者kill掉，可以使用percona的pt-kill工具杀会话。
对于高于 MySQL 5.6 版本的数据库，可以设置innodb_undo_tablespaces=2或者更大的值，这个参数用来设置undo log回滚段的磁盘文件个数，如果不设置，那么undo log会保存到公共表空间ibdata中，如果真的出现大事务导致undo log回滚段过大，这样设置后清理起来也更方便。
在测试阶段要输出所有的general_log，分析日志行为提前发现问题。
确认是否有不必要的只读事务，有些框架会不管什么语句都用begin/commit包起来，但是有些只读的select语句其实并不需要像事务那样读取的，这种事务可以去掉。
业务连接数据库的时候，根据业务本身的预估，设置SET_MAX_EXECUTION_TIME的值，控制每个SQL语句执行的最长时间，避免单个语句意外执行时间太长导致的长事务。

锁

数据库中锁的作用就是确保每一个用户都能以一致的方式读取和写入数据。MySQL中的锁分为两种：闩锁latch和lock锁。

latch：轻量级的锁，用于锁定mysql应用程序中的一些对象，要求锁定的时间必须非常短，mysql中分为mutex（互斥锁）和rwlock（读写锁），是应用程序级别的，就是我们在程序中使用的mutex和rwmutex。通过命令show engine innodb mutex可以查看，一般mysql的开发人员会关注。通过命令show engine innodb mutex可以观察当前数据库中的latch信息。

mysql> show engine innodb mutex;
+--------+----------------------------+---------+
| Type   | Name                       | Status  |
+--------+----------------------------+---------+
| InnoDB | sum rwlock: buf0buf.cc:792 | waits=8 |
+--------+----------------------------+---------+
1 row in set (0.01 sec)

lock：用来锁定数据库中的对象，表、页、行，并且仅在事务提交或回滚之后才能释放（不同事务隔离级别的释放时机不同）。这也是我们接下来主要讨论的锁。

MySQL支持全局锁、表锁、行锁(InnoDB存储引擎支持)。

除了对于锁定的粒度进行区分，按照操作来区分，锁分为读锁和写锁，也叫共享锁(S锁)和排他锁(X锁)。名称根据他们的特点而来，读锁和读锁之间是兼容的，所以叫共享锁；而写锁会阻塞其他所有的写锁和读锁，被锁定的内容只能被当前事务操作，所以叫排他锁。

全局锁

用来锁住整个数据库，命令是Flush tables with read lock，可以让整个库处于只读状态。

表级锁

表锁

用于锁住有一张表，命令是lock tables <T> read/write，可以用unlock命令主动释放锁，或者当连接断开时也会自动释放。一般不用这个锁，影响面太大，并且读锁和写锁的机制还需要注意。

读锁：给表加了读锁后，自己也不能对其进行修改，所有连接都只能读取该表。
写锁：给表加了写锁后，自己可以读写该表，其他连接的读写都被阻塞。

元数据锁

也叫MDL锁。MySQL 5.5 引入，用于隔离DML操作和DDL操作之间的干扰。不需要显式地使用，会在访问一个表时自动加上，元数据锁是保证在读写表数据时，表的元数据（字段，类型等）不会发生变化。

当对一个表做增删改查的DML操作时，自动加MDL读锁，MDL读锁之间不互斥，即所有DML操作都可以并行。

当对表结构做DDL操作时，会加上MDL写锁。写锁和其他锁都互斥，即在修改表结构时，其它线程的DDL操作，和DML操作，都会被阻塞。

意向锁

意向锁也是表级别的锁，而且针对的也是表级别的读锁和写锁。当想要给一个表中的某一行加读锁或者写锁时，就需要先给表加个意向锁，它的作用是为了快速的判断一个表中有没有被加行锁，否则就需要一行行看才能知道这个表有没有被加行锁。如果表被加了行锁，那么对表加表锁时就需要受到限制。

根据读写差异，意向锁也分为意向排他锁(IX)和意向共享锁(IS)。当事务想要给表的某一行添加共享锁时，就会先给表加上意向共享锁；而当事务想要给表的某一行加上排他锁时，就会给表先加上意向排他锁。

所有的意向锁之间都是兼容的，意向排他锁和表的共享锁或排他锁都是不兼容的；表的写锁和其他所有锁都是不兼容的。

当一个事务想要给表的某行加锁时，它会进行如下先加意向锁的过程：

事务要获取一个表中某些行的S锁/X锁时，需要先给表添加IS/IX锁；
当要添加IS锁时，发现表被加了X锁，表X锁和其他所有锁都不兼容，所以需要阻塞；
当要添加IX锁时，发现表被加了S锁，IX锁和S锁不兼容，所以也要阻塞；
要获取IS/IX锁时，发现表被加了其他事务的意向锁，不会阻塞，因为意向锁之间都是不阻塞的，可以继续看想要加锁的这一行有没有被行锁锁住。

当一个事务想给表加表锁时，也需要先判断表的意向锁，根据意向锁判断表有没有被加上行锁。

如果事务要对表加X锁，发现表上被加了某个意向锁，X锁和所有意向锁都不兼容，则会阻塞；
如果事务要加表的S锁，发现表被加了IS锁，则不会阻塞，因为IS锁和S锁是兼容的；
如果事务要加表的S锁，发现表被加了IX锁，则还是会阻塞，因为IX锁和S锁也是不兼容的。

行锁

只有InnoDB存储引擎支持行锁。

两阶段协议

行锁是需要的时候才加上的，可以主动加，或者当修改某行时也会自动加；
行锁必须等到事务提交或回滚才能释放。

基于以上的两阶段协议的存在，所以我们在处理事务时，尽量在一个事务中，并发量最高的行锁往后放，这样能让它锁定的时间尽量短些，提高并发效率。

锁定一致性读

锁定一致性读顾名思义，就是通过加锁的方式，实现一致性读（区别于MVCC的非锁定一致性读）。

在执行select语句时，可以加上以下语句添加行锁：

写锁，通过语句select ... for update可以在读的时候给这些行加上写锁；
读锁，通过语句select ... lock in share mode可以在读的时候给这些行加上读锁。

行锁的算法

Note!
InnoDB的行锁都是针对索引的，只有在需要根据索引进行读写操作时，才可以加上行锁。如果筛选条件不是索引列，那么无论如何，都会加的是表锁。

记录锁(Record Lock)

用于锁住一行的索引记录。

比如，当更新一行数据时，会自动加上写锁；如果是读时，通过执行select * from T where id = 1 for update; ，就会给id为1的那行加上写锁。

间隙锁(Gap Lock)

可以锁住索引之间的间隙，防止间隙内的插入操作，由于只锁定间隙，所以它是个左开右开的区间，即它只能阻塞两个相邻索引之间的插入操作。

比如执行select * from T where id between 1 and 10 for update; 语句，就会在(1, 10)这个区间加上间隙锁。当有其他事务向(1, 10)这个区间插入新行时，就会被阻塞。

间隙锁，只能在RR隔离级别下才会产生。作用是为了阻止多个事务将记录插入到同一个范围内，造成幻读(Phantom Problem)的问题。间隙锁和间隙锁之间是不冲突的，多个间隙锁可以同时加到一个范围上，他们的作用都是保证这个范围不会被插入新行。

间隙锁产生的条件：

使用普通索引的等值查询，而不是主键，唯一这种保证唯一性的索引，那只会产生记录锁。
虽然是唯一索引，但是查的是多个索引值。
虽然是唯一索引，但是查的是个范围时。

临键锁(Next-Key Lock)

既要锁住一个索引的范围，也要锁住范围内的所有行。其实就是间隙锁和记录锁的结合，临键锁的区间是左开右闭的，因为它既要锁住范围，也要锁住右侧的一条记录。

因为间隙锁只有RR级别才会产生，所以临键锁自然也是只有RR隔离级别才存在。

在使用中，临键锁是InnoDB行锁的大多数形态，只有满足一定条件时（比如只涉及一条记录，或者只包含某个范围时），它才会降级成为间隙锁，或者记录锁：

当查询未命中记录时，就会降级成为间隙锁，因为没有记录；
当查询的行是唯一索引的等值查询时，就会降级成为记录锁，因为不存在间隙。

场景总结

当使用唯一索引来等值查询的语句时, 如果这行数据存在，不产生间隙锁，而是记录锁；
当使用唯一索引来等值查询的语句时, 如果这行数据不存在，会产生间隙锁；
当使用唯一索引来范围查询的语句时，对于满足查询条件但不存在的数据产生间隙(gap)锁，如果存在的记录就会产生记录锁，加在一起就是临键锁；
当使用普通索引不管是锁住单条，还是多条记录，都只会产生间隙锁，它会把命中记录前后的范围都进行锁定；
如果一个表上有多个索引，那么根据索引类型的不同，唯一索引上可能会加临键锁，非唯一索引上则会加间隙锁，锁之间是可以叠加的；
对非索引列的操作，不管是锁住单条，还是多条记录，都会产生的是表锁，不涉及行锁。

死锁

死锁是指两个或两个以上的事务在执行的过程中，因争夺锁资源造成的互相等待的现象。若无外力作用，两个事务都会无限制的阻塞下去。

死锁产生的根本原因：事务必须要提交或者回滚后，行锁才会被释放。通过下面一个例子可以容易的分析：

以上两个事务，凑效同时执行了第一条语句，事务1锁住了id=3的行，事务2锁住了id=4的行，那么接下来执行时，事务1要访问id=4的行，发现该行已经被事务2加了排他锁，事务1只能等待事务2回滚或者提交后锁释放了才能继续执行。同样的，事务2接下来要访问id=3的行，发现这一行被事务1加上了排他锁，所以他就也只能等待事务1提交或回滚，才能继续执行，于是就出现了两个事务分别持有对方想要访问的行的锁，同时也等待着对方释放锁。如果不加外力干涉，这两个事务会永远阻塞下去。

解决这类问题，就要求一种一个事务必须回滚，让另一个事务能继续执行下去。MySQL和InnoDB实现了各种死锁检测和死锁超时检测。大体上可以分为被动检测，和主动检测两种：

被动检测：比如超时检测，当两个事务相互等待时，当一个事务的等待时间超过了阈值时，就要回滚其中一个事务。可以通过参数innodb_lock_wait_timeout参数指定超时时间。

mysql> show variables like "innodb_lock_wait_timeout";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)

主动检测：wait-for graph(等待图)的方式进行死锁检测。首先，数据库会保存锁的信息链表，和事务的等待链表，根据这两个链表构造出一张有向图，其中点就是事务，边代表一个事务需要等待另一个事务，如果图中存在回路，则代表有死锁。

如果检测出了死锁存在，InnoDB会根据undo log量、事务锁定的资源数量等等条件，回滚其中一个事务。

MVCC

非锁定的一致性读

前文提到了通过加锁的方式实现一致性读，通过加锁的方式来保证读取数据的正确性。而为了提高效率，InnoDB还实现了非锁定的一致性读，即读取操作不需要等待行锁的释放就可以完成数据读取，提高读的效率。这也是InnoDB默认的读取方式。这种方式就是多版本并发控制(Multi Version Concurrency Control，即MVCC)。

InnoDB的实现方式是通过版本控制的方式读取当前执行时间数据库中的快照数据，快照数据是指该行之前版本的数据，如图所示，当要读取的数据被加了X锁时，可以不等到锁释放，而是直接读取数据旧版本的快照，即可读到满足事务隔离级别下应该读到的数据版本。

其中如何读取旧版本的数据，InnoDB结合自己的undo log和在表中增加了两个隐藏字段实现的。

undo log 用于记录数据被修改的历史日志，当行被修改时，相反的操作会被写入，undo log是逻辑日志，它的作用就是事务回滚时的数据恢复还原。

两个隐藏字段为：

TRX_ID：全局事务ID，即transaction_id，在每个事务开始时被分配，全局递增，在MVCC中可以把它理解为版本号，每当一个事务操作了一行，那么这行的版本就会更新。
DB_ROLL_PTR：行的回滚指针，即指向该行当前版本数据的上一个版本在undo log的指针，通过这个指针，InnoDB就可以从undo log上找到这行数据历史版本的快照。

快照读和当前读

快照读就是执行select语句时的读取数据的方式；而当前读是在执行insert、update、delete、select … for update和select … lock in share mode时进行数据读取的方式。

简言之，快照读可以读到旧版本的数据，而当前读必须要获取最新的版本的数据。所以只有快照读时，才能用到MVCC；如果是当前读，就要通过InnoDB的锁机制来读取最新的值，当前读是一定要加锁的。

MVCC可以说是一个基于乐观锁的实现。我们主要讨论的也是在行被加了X锁时可以在不阻塞的情况下依然读取到正确的数据。而如果是并发写的操作，那么由于写操作都是当前读，在RR隔离级别下就可能会出现写失败的问题。

有一个典型的例子可以说明这个问题。如下，我们有如下的表，表中有四行数据他们的id字段和c字段都是一样的：

mysql> select * from t;
+----+----+
| id | c  |
+----+----+
|  1 |  1 |
|  2 |  2 |
|  3 |  3 |
|  4 |  4 |
+----+----+

然后我们开启一个事务A（注意：执行select语句后该事务才真正开启）。在事务A执行的过程中开启一个事务B，执行update操作把所有c值都变成5并立刻提交（必须立刻提交，如果不提交，则update的行都会被锁住，事务A中的update语句会被阻塞），然后我们在事务A中执行update语句，给所有的c值都变成0，此时A中的操作会不成功，显示修改了0个row：

mysql> update t set c=0 where id=c;
Query OK, 0 rows affected (0.00 sec)
Row matched: 0 Changed: 0 Warning: 0

但当我们在事务A中再次执行select语句时，得到的结果却依然是：

mysql> select * from t;
+----+----+
| id | c  |
+----+----+
|  1 |  1 |
|  2 |  2 |
|  3 |  3 |
|  4 |  4 |
+----+----+

发现事务A中出现了吊诡的一幕，明明通过select语句能看到where条件是满足的，但却无法完成修改。原因就是在事务B中所有行都被修改了，而事务A中的update 操作是当前读，只能读取最新的数据，然后再写，而最新的数据又是不满足条件的，所以就会造成修改了0个row的问题，RR隔离级别又需要保证可重复读，所以无论读多少次，都依然看起来是满足条件的。

解决方式也很简单，可以当失败时再重新起一个事务执行原本的操作（失败的判断依据是affected_rows是不是等于预期的值，以防止出现这种情况）。

Read-View

MVCC中的快照读Read-View是一个数据结构，在事务执行过程中会被创建，它主要包含以下4个字段：

m_ids：当前活跃的事务ID集合。
min_trx_id：最小活跃事务编号。
max_trx_id：预分配事务编号，当前最大事务编号+1。
creator_trx_id：创建此Read-View的事务ID。

InnoDB中只有RC和RR两个隔离等级下，才可以使用MVCC实现非锁定的一致性读，方式都是一样的，只不过是根据隔离等级的要求，读取的历史数据版本不同。RC隔离等级：可以读取所有已经提交的事务，所以可以读取行的最新事务版本。RR隔离等级：要求可重复读，所以要读取到这行数据在该事务开始时的那个版本。

注意：上面所说的读取都是执行Select语句的快照读，而不是写入语句或者自动加锁的当前读！

RC隔离级别下的MVCC过程

RC级别下在事务中每执行一次Select语句，就会生成一个Read-View对象，如下图：

每一次的select语句的快照读，都会根据Read-View，以及对应隔离级别下的访问规则，获取应该得到的数据。会从当前的这行数据开始判断能不能访问，不能访问就要根据DB_ROLL_PTR字段从undo log中找到上一个版本再继续判断。是否可访问的判断规则如下：

read-view的creator_trx_id是否等于要访问行的trx_id？
如果是，就证明是自己这个事务改的这一行，自己改的要认，所以这行数据可以访问。否则需要继续向下判断。
判断行的trx_id是否小于read-view的min_trx_id？
如果是，就说明这一行当前没有事务在访问，并且上一个访问它的事务已经提交了，所以这行数据就可以访问。否则就要继续向下判断。
判断行的trx_id是否大于max_trx_id？
如果是，就说明这一行在read-view生成之后又被另一个新事务访问了，那么这行数据就不能被访问，需要从undo log中找到上一个版本再从规则1开始判断。否则就还可以继续向下判断。
判断trx_id是否在min_trx_id和max_trx_id之间？
由于2、3的访问都过了，所以这里一定会满足，此时就要判断trx_id是否在m_ids中，如果是就说明这一行还有其他事务在访问，不能被访问，需要从undo log找到上一个版本再从规则1开始判断；如果不在，证明这一行的事务已经被提交了，那就可以访问。

RR隔离级别下的MVCC过程

由于RR隔离级别要求事务中只能读取到事务一开始时已经提交的事务，要满足可重复读的条件，所以RR级别下，只会在第一次执行Select语句从而发生快照读时，生成一次Read-View对象，后续的每一次的快照读，都复用这个Read-View，如下图：

由于RR级别的隔离要求，在检查一个行的版本时，需要保证行的trx_id要小于等于creator_trx_id，并且trx_id不能在m_ids中。即只能读到比自己版本小或者是自己版本的，并且是已提交过的版本，这样才能保证可以重复读。

Note!
RR隔离级别下的MVCC，不能完全解决幻读问题！因为虽然Read-View是复用的，但是当事务中两次快照读之间存在当前读，都会导致read-view重新生成（除了create_trx_id不变外，其他字段都可能改变）。就会导致幻读的产生，要完全解决该问题，只有通过前文提到的间隙锁，可以通过显式加锁的方式防止幻读。

参考

[1] 《MySQL技术内幕：InnoDB存储引擎》

[2] 《高性能MySQL》

[3] 极客时间《MySQL实战45讲》

MySQL基础知识总结

概览

架构