[关闭]
@Catyee 2021-08-08T15:31:10.000000Z 字数 19136 阅读 412

Mysql

mysql

一、mysql的架构

如图是mysql8之前的一个架构示意图：

mysql逻辑架构图

mysql大体上可以分为server层和存储引擎层。
server层包括连接器、分析器、优化器和执行器，mysql8之前的版本还有查询缓存，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储，mysql将存储引擎层设计为插件式的，可以根据实际需要来选择不同的存储引擎。一版来说mysql有两个默认引擎，一个是MyISAM，是一个不支持事务的存储引擎，另外一个就是著名的事务存储引擎InnoDB，mysql5.7的时候系统表默认是MyISAM存储引擎，用户表默认是InnoDB存储引擎，到了mysql8.0的时候系统表也默认是MyISAM存储引擎了。

这里多说一句，其实大部分的单体数据库架构都是大同小异的，都是数据库服务器接收到一条sql之后先进行词法和语法分析（比如使用antlr），这一步可以检查出sql语句是否符合语法，以及可以分析出sql语句的每一部分是什么，但单纯的语法分析是无法知道整个sql的语义的，只有结合语法分析的结果以及系统上下文信息才能知道sql的语义，知道语义之后就可以生成逻辑执行计划了，但是一般还会检查逻辑执行计划是否还有优化的余地，最终按照优化后的结果生成物理执行计划交给执行层去执行。

二、mysql中一条查询sql语句如何执行

要执行一条sql首先是要客户端连接上服务器，然后客户端才能发送sql给服务器，客户端连接上服务端之后，连接器首先会去到系统权限表中查出连接用户拥有的权限，之后这个连接里面的权限判断逻辑就都依赖于此时读到的权限，即使更改了权限，只要不刷新也不影响当前连接中的权限，当然也可以使用刷新语句刷新权限：

  1. flush privileges;

当一条sql语句从客户端发送到服务端之后，连接器虽然获取到了权限，但连接器暂时还不会做权限的检查，因为这个时候还只是一条sql语句，也就是一个字符串，连接器根本不认识，也不知道这条sql做了哪些操作需要哪些权限。
在mysql8之前，会先拿这条sql语句去查询缓存中获取缓存结果，如果能够命中就直接返回了，这里会对sql做一些有限的分析，保证sql是一个查询语句，但不会像分析器那样做完整的分析。查询缓存实际上是一个key-value键值对的结构，key就直接是sql语句，而value是以前查询的结果，但是查询缓存是可能经常失效的，比如表数据发生了变化，那必须清理缓存，另一个原因是sql语句变化，虽然语义一样但是写法不一样了，也无法缓存命中，所以这个功能实际很鸡肋，在mysql8的时候已经被去除了。

正常流程是mysql服务端接收到一条sql语句之后先交给分析器进行词法和语法分析，这一步可以分析出一个字符串形式的sql语句中有哪些成分，也能知道一个sql语句是否符合语法，但单纯的词法语法分析是无法推断出整个sql的语义的，比如这样一条sql：

  1. select * from test;

仅仅只是词法和语法解析只能知道sql语句每一部分的成分，比如sql语句中含有select这个关键词，但是没办法知道test是一张数据表还是一个视图，只有结合系统中本身存储的信息才能知道这到底是一张表还是一个视图。所以要结合语法分析的结果和系统本身存储的信息才能获取到整个sql具体的语义，所谓语义就是这个sql的具体含义，比如上面这个sql，语义是"查询表test中的所有字段的所有数据"，人可以一眼识别出具体语义，但机器做不到，机器要分析出语义就需要经过上述的步骤。由于这一步已经知道具体的语义，所以还可以做一些基础检查，比如表是否存在，列是否存在之类的：

  1. select * from test where k > 1;

比如这样一个sql，如果字段k不在test表中，在分析器阶段也可以检查出来。

总之知道sql具体的语义之后就可以生成逻辑执行计划了，然后交给优化器去看是否还有优化的余地，优化器做的事情：比如表里有多个索引的时候决定使用哪个索引，再比如在多表join的时候决定各个表的连接顺序，另外还有一些计算下推的优化。

优化器优化结束之后会生成物理执行计划，再交给执行器去实际执行。这个时候执行器才会进行权限的检查，因为这个时候服务器已经理解这个sql的语义，知道执行这个sql要做哪些操作，每个操作需要哪些权限，所以这个时候就可以做权限检查了，如果有权限才会继续执行，执行器会去调执行引擎的接口达成具体的操作。

三、B+树索引

索引是存储引擎用于快速找到记录的一种数据结构，索引也有很多种，比如B+树索引，Hash索引、全文索引，函数索引等等。数据库中最为常用的是B+树索引，不光是Mysql，其它关系库也是一样的。下面的索引都是针对B+树索引，后面会有一个章节专门介绍其它索引。

索引在MySQL中也叫做"键"或者"key"（primary key，unique key，index key），就像字典的目录一样，用来缩小数据检索的范围，达到提高检索效率的目的。

主键和唯一键除了具有索引的功能还提供约束的功能，但是普通的index key就只具有索引的功能，不具有约束的功能。

3.1 索引数据结构的选取

减少IO次数
一般而言关系数据库的数据都不会全部放到内存中（内存数据库除外），而是存放在磁盘上，数据库操作数据需要先将数据从磁盘上读到内存中，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，总之是一个很重的IO操作，如果想提升性能，硬件性能是一方面，另一方面我们可以通过减少IO操作的次数来提升性能。

操作系统的预读：
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不仅把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4kb或8kb（页都是磁盘块的倍），当读取一页内的数据时候，实际上才发生了一次IO，能否有效利用磁盘预读的原理对索引结构的选取页很重要。

所以在选取或者设计索引结构的时候至少要考虑两方面的因素，一个是尽量减少IO次数，另外一个是能够很好的利用磁盘预读原理。先从减少IO次数着手，我们知道索引通过缩小数据的搜索范围来提升搜索速度，这方面的典型数据结构是二叉搜索树，但是和放在内存中的二叉搜索树不一样，在数据库场景下每访问一个节点，就需要从磁盘中读取数据，就是一次IO操作，为了减少IO操作，就需要有效的控制树的高度，我们需要一种高度可控的自平衡多路搜索树，那AVL树、红黑树就被淘汰了，而B树正好就是这样一种树。

3.1.1 B树与B+树

如图是一个简化的B树示意图：
朴素B树

可以看到B树具有以下特点：

多叉有效降低了树高度，所以B树是一个扁平的树，如果每访问一个节点就需要一次IO的话可以有效的降低IO的次数，同时由于磁盘预读的特性又可以很好的将临近的数据预读到内存。看起来很美好，但是我们知道Mysql最终用的是B+树，而不是朴素的B树，这是什么原因呢？

实际上数据库要面临的情况非常复杂，除了精确查询之外还要面对范围查询，模糊查询、并集查询等等情况。为了简化范围查询，最好是能把节点组织成链表的形式，基于这样的思路演变的结果就是B+树。

B+树实际上是B树的变体，是B树的进阶版，如图是一个简化的B+树的示意图：

B+树

如图，浅蓝色的块我们称之为一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇区，可以看到每个磁盘块包含几个数据项和指针（黄色为指针，非叶子节点深蓝色是指引收获方向的数据项，叶子节点中深蓝色才是真正的数据）。

可以看到B+树具有以下特点：

对于B树来说要进行顺序的全表扫描只能进行中序遍历，而对于B+树来说只要从叶子节点直接遍历一边就可以了。这里多说一句，一个经常会被忽略的特性是B+树是一个有序的数据结构(从二叉查找树演化而来)，表现出来的性质就是一张表的索引字段都是有序的，比如max()、min()这样的函数在索引字段上不需要进行全表扫描就能计算出来，效率非常高，但如果对一个普通字段执行max()和min()函数就需要全表扫描来计算，效率是很低的。

B+树与磁盘预读：
当读到某个值的时候（比如图中磁盘块6）磁盘预读原理会将临近的数据页也读进内存（页的大小是磁盘块大小的倍，如果n是0，页和磁盘块是一样大的，以下都假设页和磁盘块一样大，这种前提下图中磁盘块5和7也会读进内存），由于B+树叶子节点数据是顺序的，通过预读读进内存的值就在当前读到值的附近，这些预读的值确实是接下来大概率会被访问的值。但如果是红黑树或者AVL树情况就不同了，磁盘预读是操作系统的优化，所以使用红黑树或这AVL树也会发生磁盘预读，但是红黑树和AVL树两个相邻的节点真实数据可能可能离的很远，所以即使发生磁盘预读，预读到内存的数据可能也不是接下来大概率被访问的值，这也是除了高度因素外另一个不使用红黑树或者AVl树的理由。

3.2 使用B+索引的注意事项

3.2.1 索引字段的值要尽量小

B+树的非叶子节点存储的都是索引数据项，假设整张表的数据大小是N，每个磁盘块的数据项的数量为m(磁盘块的大小一定)，那么B+树的高度，当数据量N一定的前提下，m越大树的高度h越小，由于磁盘块的大小是固定的，那数据项的数量m越大就要求每个数据项的大小越小，所以得出的结论就是总数据量一定的情况下，单个数据项的大小越小，树的高度就越低，性能就越好。所以我们在选用索引字段的时候要尽可能的小，比如int占4个字节，而bigint要占8个字节。

另外这也是为什么b+树不在非叶子节点存储数据的原因，存储数据就会导致每个磁盘块存储的索引数据项变少，从而导致树的高度增高。

这里要多说一句，从索引的角度来说，索引字段当然是越小越好，但实际开发过程中，索引字段数据类型的选取是一个综合考虑，比如int类型的自增主键当然是比uuid小的，数据库用锁来保证自增，在并发度高的情况下会频繁竞争锁导致性能下降，所以这是一个综合考虑的结果，不能单从一个角度思考。

3.2.2 索引的最左匹配特性

简单来说，当有多个字段构成复合索引，在利用复合索引进行检索的时候，复合索引总是按照索引字段从左往右的顺序来依次缩小数据搜索范围，在当前字段无法进一步缩小范围的时候就会用下一个字段来进一步缩小范围，比如(name,age,sex)共同构成了一个索引，B+树索引会先拿name来确定下一步搜索的方向，直到name相同的时候，也就是通过name无法确定搜索方向的时候，就会用age来确定下一步的搜索方向，依次类推，最终得到检索的数据。要注意where条件中如果用and连接，则字段的顺序并不影响索引的选取，mysql做了优化，and连接会自动按照索引的顺序来检索，比如：

  1. select * from user where age = 12 and sex = 'M' and name = 'Tom';

复合索引(name,age,sex)在这个sql上依然生效，但是如果where条件中缺少了索引最左的字段，这个复合索引第一步就没法确定下一步的搜索方向了，就只能进行全表扫描：

  1. select * from user where age = 12 and sex = 'M';

上面这个sql会进行全表扫描，如果where条件中缺少的是复合索引中间的索引字段，那么复合索引前面的索引字段依然生效，后面的索引字段将不会生效，比如：

  1. select * from user where sex = 'M' and name = 'Tom';

这条sql执行的时候，先按照name来确定下一步的搜索方向，直到遇到name相同的情况，无法通过name进行进一步的范围缩小，这个时候就会用age来进一步缩小搜索范围，但是where条件中没有age，索引就没办法进行进一步的范围缩小了，只能进行范围扫描来逐个匹配。这个sql中name依然是生效的，但是sex已经不生效了。这就是B+树索引的最左匹配特性。

3.2.3 索引对数据表的影响

3.3 索引的数据结构考虑总结

3.3.1 为什么不选用红黑树和AVL树

从前面分析可以总结出不选红黑树和AVL树至少有两个原因：

3.3.2 为什么不选用B树

从前面的分析可以总结出为什么不使用B树来作为索引结构，至少有如下两方面原因：

3.4 聚集索引与辅助索引

观察mysql8表的实际存储文件就会发现，对于mysql8的innodb表，一张表只有一个以.idb为后缀结尾的文件（这个文件也被称为这张表的表空间），假如这张表只有主键，那主键索引的数据存储在哪儿呢？没错，主键索引和真实数据存储在同一个文件里面，这种索引和数据存储在一起的索引叫做聚集索引，也就是说mysql实际上就是通过主键索引来组织数据在文件中的存储格式（将数据构造成一颗B+树），如果一张表没有设置主键字段，那么Innodb会寻找一个非空(not null 约束)的唯一键当作主键，用这个键作为聚集索引组织数据，如果找不到这样的唯一键，Innodb会生成一个隐藏的列，这个列只有六个字节，然后用这个列来当作主键组织数据。所以可以认为所用Innodb表都有主键，只是有些可能是隐藏的。

每张Innodb表只需要一个组织成一颗B+树就可以，这颗树的叶子节点存储了所有数据，而组织一颗B+树只需要一个聚集索引，所以InnoDB表都有且只有一个聚集索引

聚集索引(主键)之外的索引都叫做辅助索引，辅助索引和聚集索引一样也是B+树的结构，但是聚集索引的叶子节点存储着所有数据，而辅助索引的叶子节点只存储了索引字段的数据以及主键字段的数据。

3.4.1 覆盖索引与回表

我们知道辅助索引的叶子节点存储的是辅助索引字段的数据以及主键字段的数据，当我们用辅助索引来查询数据的时候，如果要查的字段都是辅助索引的字段，那么在辅助索引的叶子节点中就可以获取到数据值，这种情况就叫做覆盖索引，这种情况查询效率和主键查询效率一样高。比如：

  1. -- (name,age,sex)是复合索引，由于只检索了这几个字段，在辅助索引的叶子节点就可以获取到值
  2. select name,age,sex from user where age = 12 and sex = 'M' and name = 'Tom';

那如果待查询的字段还有其它的字段呢？这个时候通过辅助索引不足以获得所有的数据，当到达辅助索引的叶子节点之后，还要根据叶子节点中保存的主键值再走聚集索引去检索完整的数据，这种情况就叫做回表，这种情况效率依然很高，但是要比覆盖索引的情况效率低一点。

3.5 索引未命中(索引失效)

我们来看什么情况下索引会失效：

3.5.1 范围搜索过大

这种情况实际上不是索引失效了，而是索引发挥不了太大作用，因为一个范围搜索最终是要在范围内进行逐个遍历的。范围搜索有两种情况，一种是用大于号和小于号或者between明确了范围：

  1. select * from table where id > 10;
  2. select * from table where id between 1 and 30000;

这种情况会先通过索引找到叶子节点，在通过叶子节点的链表逐个遍历来返回符合条件的数据，如果遍历的范围很大，那么依然会慢。第二种情况是使用了不等于符号：

  1. select * from table where id != 10;

这种情况依然近乎于全表扫描，当然会慢。

3.5.2 like子句使用通配符

比如一张表的某个字段是字符串类型，并且加了索引，我们用like来进行模糊搜索。
如果like中没有通配符，实际上是精确匹配，其实和=没有区别，这种情况性能也很好:

  1. select * from user where name like 'Tom';

如果like中有通配符，但是统配符在末尾：

  1. select * from user where name like 'Tom%'

这种情况下索引依然有效，一直可以搜索到叶子节点，由于叶子节点是有序的，所以从这个叶子节点往后遍历就可以找到所有匹配的数据。
如果like中有统配符，但是通配符在最前面：

  1. select * from user where name like '%Tom';

这种情况下就没法引用索引了，只能全表扫描。

3.5.3 索引列参与计算

索引列不能参与计算，比如from_unixtime(create_time)='2014-05-29'就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time=unix_timestamp('2014-05-29')，如果一定要参与计算，可以在这一列上创建函数式索引，函数索引是mysql8才支持的功能，并且还远不如oracle的函数索引强大。

3.5.4 违反最左匹配特性

在前面已经分析过，B+树索引从复合索引的最左一个字段开始匹配，如果where条件中没有最左字段，会导致索引完全失效，如果where条件中缺失中间的一些字段，会导致索引部分失效。

3.5.5 隐式的数据转换

比如一个字段是String类型的，这个字段上建有索引，但是在进行查询的时候where条件使用了数字：

  1. select * from  table where password = 123456;

这种情况下由于数据类型不一致，会进行隐式的数据转换，导致索引失效。
具体的隐式转换规则见官网描述：mysql8对隐式转换的说明

3.5.6 小心使用or条件

在前面提到过，where条件中使用and连接的时候字段的顺序并不影响复合索引的使用，mysql在查询的时候会进行优化：

  1. -- 比如有(name,age,sex)复合索引，下面的sql索引依然可以正常工作
  2. select * from user where age = 12 and sex = 'M' and name = 'Tom';

但如果使用or条件，对于innodb表，如果where条件是用or连接的，即使每个字段都是复合索引的一部分，也不会应用到索引，原因就在于or的语义是或，两个条件符合其中一个就可以，没办法通过符合索引来缩小范围，只能全表扫描：

  1. -- 创建了联合索引(username,version)，可以看到执行计划中没有用到索引
  2. mysql> explain select * from par_table where username = 'test' or version = 5\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: par_table
  7.    partitions: NULL
  8.          type: ALL
  9. possible_keys: idx_name_version
  10.           key: NULL
  11.       key_len: NULL
  12.           ref: NULL
  13.          rows: 99985
  14.      filtered: 10.00
  15.         Extra: Using where
  16. 1 row in set, 1 warning (0.00 sec)

但并不是所有使用or来连接的情况都不会应用索引，如果or连接的每个条件都有单独的索引，这个时候其实每个索引都会使用到，原因也很简单，这种情况下可以先用索引查询第一个字段，符合条件直接返回，如果不符合再用第二个索引查询第二个字段，符合条件直接返回，依次类推，这种情况可以通过索引来缩小范围。但如果其中有一个字段没有索引，这个字段就需要全表扫描，所以就不会再使用索引了，因为全表扫描无法避免。

  1. -- id是主键，password字段单独创建了索引，可以看到执行计划中两个索引都有使用到
  2. mysql> explain select * from par_table where id = 1 or password = 'test'\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: par_table
  7.    partitions: NULL
  8.          type: index_merge
  9. possible_keys: PRIMARY,idx_pass
  10.           key: PRIMARY,idx_pass
  11.       key_len: 4,83
  12.           ref: NULL
  13.          rows: 2
  14.      filtered: 100.00
  15.         Extra: Using union(PRIMARY,idx_pass); Using where
  16. 1 row in set, 1 warning (0.00 sec)

所以看是不是真的能应用索引一是看mysql的执行原理，能不能通过索引缩小范围，另外一个就是使用explain来查看执行计划。

3.6 hash索引和全文索引

hash索引是Memory引擎的默认索引类型，类似于hash表，建立hash索引之后单条查询速度很快，但是hash索引无法应对范围查询。Innodb不支持hash索引。

全文索引是MyISAM引擎的默认索引类型。在mysql5.6以后的版本Innodb也支持全文索引了，全文索引只能在字符串数据类型的列上创建，比如char、varchar、text等。当我们希望通过关键词来从大量的文字当中（非结构化数据，又叫全文数据，比如一篇文章）搜索过滤的时候就可以使用全文索引，全文索引并不是精确索引，而是相似度匹配，底层使用的是倒排索引，所谓倒排索引就是先对全文数据进行分词，然后统计全文数据中出现的有效单词(所谓有效单词就是停词之外的词，比如or、and、is这些就是所谓停词)和词频，根据有效单词来建立单词到全文数据的反向指针，这样通过关键词就可以搜索出全文数据了，这就是所谓的倒排索引。

四、数据库事务

所谓事务就是一组不可拆分的工作单元，在数据库中事务表示事务内的sql语句要么全部执行成功，要么全部执行失败，这样可以保证数据库始终从一种一致性的状态转换为另外一种一致性的状态。所以说事务就是为了解决数据库状态一致性问题的，这里多提一下，数据库的状态一致性是强一致性。

4.1 事务特性(ACID)

任何事务处理系统，都必须具有ACID的标准特征，所谓ACID也就是我们熟知的原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）。

4.2 事务日志

事务的原子性、一致性、和持久性通过Innodb的redo log和undo log来实现，而隔离性由多版本并发控制和锁来实现。

4.2.1 redo log

redo log又叫做重做日志，用来保证事务的持久性，redo log中记录的是一个事务执行对数据页产生的具体操作，是物理日志，根据一个事务的redo log和事务开始前的一致性数据就可以可以进行事务的回放，所以能够在达到崩溃恢复的目的，从而实现持久性。这里要特别强调，redo log记录的不是逻辑操作，而是对数据页的物理操作，比如"在第几页的多少偏移做了什么操作，这个操作导致了B+树节点的分裂，是如何进行分裂，或者导致了B+树节点的合并，是如何进行合并的"。数据页、B+树的操作都是对文件的直接操作，对一条具体的sql来说是透明的，所以叫做物理日志。仅仅只有一份redo log是无法进行数据恢复的，因为redo log记录是对文件的操作，如果没有文件，或者说数据页不是之前的一致性状态，redo log都没办法进行恢复。所以redo log只能用来进行崩溃恢复，而不会单纯用redo log来做数据备份或者做数据同步（如果要进行物理备份，一定是物理文件和redo log一起备份，比如xtrabackup）。

另外，redo log的物理文件是循环写的，就是说当文件写到末尾之后，会回过头来重新写，覆盖之前的记录，所以redo log文件大小是固定的。

Innodb采用WAL策略（预写日志策略）来提高效率，预写日志是指Innodb为了提升效率，一个事务造成的数据修改不会立马落盘，而是先写入缓存，然后按照策略来落盘，那如何保证缓存中的事务在宕机重启之后不丢失呢？这就需要保证每个事务的事务日志已经落盘，这样即使缓存中的数据丢失还可以通过redo log来进行恢复，从而保证事务的持久性；所谓wal策略就是指事务日志先于记录落盘。

4.2.2 undo log

undo log又叫做回滚日志，用来将数据回滚到事务开始之前的状态，undo log还用来实现多版本并发控制(MVCC)。undo log并不是redo log的逆过程，在Innodb中undo log记录的是逻辑日志，比如一条insert语句，产生的undo log是delete语句，是将数据库逻辑恢复到事务开始之前的一致性状态，它不关心恢复过程中对物理文件做了哪些操作，也不关心恢复结束后物理文件是不是和事务开始之前的结构一样，所以undo log是逻辑日志，另外undo log也有可能会丢失，所以undo log也会产生redo log。

mysql8之前undo log使用共享表空间来保存，而到了mysql8 undo log和redo log一样有了独立的物理文件进行保存。

undo log日志主要分为两种：

InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）

4.3 隔离级别

完全的事务隔离是指事务之间彼此完全互不影响，这种情况几乎只能通过加锁来保证事务顺序执行，这样可以做到绝对的互不影响，但是也会大大降低执行效率，所以数据库系统都会设置不同的隔离级别，不同隔离级别会有不同的隔离程度，但是隔离级别并不是随便定的，sql标准定义了四种隔离级别，刚好每一种级别都对应一种一致性问题。

各种隔离级别下的数据问题：

如果是普通select语句，使用的是当前读，读取的是数据快照，不会出现幻读。但是特殊的select语句，insert、update都是当前读，会读取当前最新的数据，所以如果不特别设计是会出现幻读情况的，出现幻读根本原因在于如果只是用锁锁住记录行，即使锁住所有行也无法组织新插入的记录，为了解决这个问题，innodb只得引入新的锁，也就是间隙锁，锁住的是一个开区间，间隙锁和行锁一起叫做next-key lock，也就是下一键锁，这种锁锁住的是左开右闭的区间。间隙锁和下一键锁的引入，才真正解决当前读情况下的幻读问题。

五、多版本并发控制(MVCC)

所谓多版本并发控制是指保存并维护每一条数据的多个快照版本，使得读写操作不会互相阻塞，或者说减少相互阻塞的情况，从而提高并发效率。通过MVCC，Innodb实现了快照读，这是一种非阻塞的读功能，这种读取是去读取快照的数据，所以不用加锁。但如果某些操作不得不读取最新的数据，还要防止其它事务修改数据的话，就必须加锁并读取最新的数据，这就是当前读，快照读是一种乐观锁的体现，而当前读就是悲观锁的体现。

5.2 一致性非锁定读（快照读）

一致性的非锁定读又叫快照读，是指Innodb通过行上的多版本并发控制来读取当前执行时间数据库中行的数据，比如某一行数据正在被其它事务执行delete或者update操作，这个时候这行数据是加了排他锁的，但是当前事务并不阻塞当前事务读取这一行数据，原因是当前事务读取的是这一行数据的快照数据，快照数据是通过undo段来实现的，undo log本身还用于事务回滚数据，所以快照数据是必要的，不算额外开销，此外读取快照数据不需要上锁，因为不会有事务需要修改历史数据。

5.2.1 快照读的实现原理

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。MVCC模型在Innodb中的具体实现则是由3个隐式字段、undo日志和Read View 共同去完成的。

所以我们知道Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

5.2.2 不同隔离级别下的快照读

不同事务隔离级别的快照读是不一样的，在提交读和可重复读的隔离级别下默认使用的就是一致性的非锁定读，但是两种隔离级别读取的快照数据是不一样的，在提交读的隔离级别下，非锁定一致性读总是读取锁定行的最新快照数据，这是为了保证"提交读"隔离级别的语义，所谓提交读就是在当前事务中能够读到其它事务已经提交的数据，所以当前的读操作一定是读取最新的快照数据；在提交读的隔离级别下按照序号的顺序执行以下操作：

序号 session A session B
1 begin begin
2 select * from test1
3 select * from test1
4 update test1 set col1 = 'd' where id = 2
5 select * from test1
6 commit
7 select * from test1
8 commit

注意序号的顺序就是执行的顺序，结果如图，注意第7步，读到的是最新的快照：

提交读隔离界别下的非锁定一致性读

在可重复读的隔离级别下，非锁定一致性读总是读取事务开始时的行数据版本(实际是第一次执行select时的数据，并不是begin执行时的数据)，这同样是为了保证"可重复读"的隔离级别语义，所谓可重复读就是指当前事务中读取到的数据始终是一致的，哪怕这个过程中其它事务已经提交了，所以这种情况下的的一致性非锁定读在事务中第一次读取之后就会保留读取的数据，一直到事务提交之前都是同样的数据。
同样是上表的操作顺序，在可重复读的隔离级别下，结果如图，注意第7步，读到的依然是之前的快照：

可重复读隔离界别下的非锁定一致性读

5.3 一致性锁定读(当前读)

在某些情况下，数据库读取操作会进行显示或隐式的加锁来保证数据逻辑的一致性，注意，再强调一下，这种读取操作会加锁，innodb有如下这些锁定读操作：

在这种情况下读取的数据都是当前最新的数据，并且还会加锁防止其它事务修改数据。

六、锁

对于MyISAM存储引擎，由于没有事务的支持，所以锁的实现相对来说简单，就是表锁，并发情况下，读没有问题，但是写的性能就差很多了。

6.1 Innodb中锁的类型

InnoDB中有两种行级锁类型，注意锁类型并不是具体的锁实现：

如果一个事务已经获得某一行的共享锁，其它事务如果需要读这一行数据，是可以立即获得这一行的共享锁的，因为读操作不会改变这一行的数据，这种情况称为锁兼容，而如果其它事务想修改或者删除这一行数据，就需要获取这一行数据的排他锁，获取排他锁要先等到其它事务释放已经占有的锁，这种情况称为锁不兼容。排他锁与其它任何锁都不兼容，而共享锁只与共享锁兼容。

InnoDB还支持多粒度锁定，就是说允许事务在行级上的锁和表级上的锁同时存在，为了支持这种不同粒度的锁，Innodb实现了一种额外的锁方式，也就是意向锁，意向锁是将锁定的对象分为多个层次，在innodb中是分为表和行两个级别，Innodb中的意向锁就是表级别锁，有两种意向锁：

IS和IX互相可以共存，IS和IX和所有的行锁都能共存，所以，意向锁只会阻塞表级的读写锁（例如：LOCK TABLES ... WRITE），不会阻塞其他任何行锁。比如一个事务以及持有一张表的行锁，那么这个事务也持有了这张表的IX锁，另外一个事务如果想修改这张表的其它行，也需要申请行锁，这个时候不会因为之前事务的IX锁而阻塞。

6.2 Innodb中行锁的具体实现

Innodb存储引擎有四种行锁的实现，分别是：

这四种锁实现都是针对索引列的，也就是锁住的对象要么是索引记录(行锁record lock)，要么是索引记录的间隙(gap lock)，要么是索引记录的间隙+索引记录(next-key lock)，每一张表都至少有一个索引（如果表本身没有任何索引，Innodb会有一个隐藏列来做为主键）所以这三种锁在任何innodb表上都会用到。

这四种锁是Innodb中行锁的具体实现，这四种锁每种又有排他和共享两种模式，所以总共有8种锁模式：

注意以上gap锁、next-key锁、插入意向锁都是可重复读的隔离级别下才有的锁，所以在谈论锁的时候一定要知道当前的隔离级别，抛开隔离级别谈论锁是没有意义的。

mysql8中，我们可以通过performance_schema.data_locks这张系统表来查询当前的加锁情况，这张表有几个重要的字段：

字段名 说明
engine_transaction_id 事务id
object_name 锁定的对象名，即哪张表
lock_type 锁类型，RECORD代表行锁，TABLE代表表锁
lock_mode 锁的模式，也就是上面我们列举的八种模式
lock_status 锁的状态，GRANTED代表已经授予锁，WAITING表示正在等待锁
lock_data 事务锁定的记录的主键值，如果是表锁，则这个值为null

lock_data种有一个特殊的值：supremum pseudo-record，这个值代表最后一个索引值到无穷大，也就是(n，+∞]，这种情况下锁定的范围非常大，大部分情况都是锁全表。

当然这张表还有一些其它字段，通过这些字段就可以看到当前各个事务加锁的情况。还有一种方式就是通过show engine innodb status语句来查看，但是因为混入其它信息不如查询系统表直观。

6.3 延迟加锁机制（隐式锁）

如果有朋友在看了上面的描述之后亲自做过实验，会发现有些加锁的情况和描述的不太一样，比如原表种已经有了1和5两条记录，开启两个事务，一个事务插入2一个事务插入3，按道理应该产生两个插入意向锁，但是真实情况是根本没有加行锁，也没有加插入意向锁，只加了两个表级的IX意向锁：

延迟加锁机制展示

这其实是因为Innodb为了降低锁的开销，采用了延迟加锁的机制，也叫做隐式锁，也就是说当有事务对某条记录进行修改的时候，会先判断这一行记录上是否有隐式锁，实际上就是判断这行记录上是否有活动的事务(每一行都有一个隐藏的trx_id字段用于记录正在进行的事务id，如果不为空，就说明有活动事务，也就是有隐式锁)，如果有活动的事务，就需要将原有记录的隐式锁转换为显示锁，同时自己的锁也会创建出来，然后根据锁是否兼容来决定是阻塞还是继续执行。简单来说只有显示加锁语句(select ... for update/select ... in share mode/update/delete)或者可能存在冲突的时候才会真正加锁。

6.4 可重复读隔离级别下的加锁规则

知道了各种锁模式和延迟加锁机制，就可以推导加锁规则了，以下列举可重复读隔离级别下的部分加锁规则，可能会使用到锁的操作主要是insert/update/delete和当前读，按这些操作来进行分析：

6.4.1 普通查询语句

如果是普通查询语句，采用快照读的方式，不会加锁，也不会被阻塞
如图，表test有两个字段：id和col1，id是主键(无自增)，col1上没有索引，在事务1中用一个简单的方式锁定全表，在事务2中执行普通查询，发现普通查询没有被阻塞，查询加锁情况，发现事务2没有加锁：

普通查询_快照读

注意上图中lock_mode是X代表加的是排他锁模式next-key锁。supremum pseudo-record代表的是(5,+∞]的左开右闭区间。

6.4.2 当前读，查询用到索引且为等值查询

如果是当前读，并且用的是等值查询，查询用到的是唯一索引或者主键索引，那么会在主键或者唯一索引上加行锁(record key)，如果是辅助索引则在辅助索引上加nextKey锁，在聚集索引上加行锁，然后在第一个不满足条件的索引值上加gap锁，锁是共享模式还是排他模式视sql语句而定

6.4.3 当前读，查询用到索引且为范围查询

如果是当前都并且是范围查询，不管唯一还是辅助索引都在每一个满足条件的索引值上加nextKey锁，并在相应的聚集索引上加record锁，然后在第一个不满足条件的索引值上加nextKey锁，锁是共享模式还是排他模式视sql语句而定。

6.4.4 insert语句

// TODO 待补充
insert语句只有在有冲突时才会创建锁，有冲突时在插入成功前会在(insertVal，nextVal)加 插入意向锁，插入成功后会在insertVal上加行锁。

6.4.5 update语句

// TODO 待补充
update的目标值同insert，update成功前会在 (targetVal, nextVal) 加插入意向锁，更新成功后会在 targetVal上加record锁。例如update i=8 where i=11那targetVal i=8 上会先有(8, 10)的插入意向锁，之后会有[8]的record锁。

6.4.6 insert/update/delete/当前读没有用到索引的情况

如果insert/update/delete/当前读没有用上索引，则对全表记录的主键索引上加nextKey锁，包括无穷值也加上nextKey锁。实际上就是整表锁定，锁是共享模式还是排他模式视sql语句而定。
那些是没有用到索引的情况呢？举一些例子：

  1. -- test表的name字段上面没有索引
  2. select * from test where name = 'Tom' for update；
  3. select * from test where name = 'Tom' lock in share mode；
  4. insert into test2 select * from test where name = 'Tom';
  5. update test set name = 'Jeery' where name = 'Tom'；
  6. delete from test where name = 'Tom';

分析一条sql的加锁情况的时候先看使用了什么存储引擎，再看当前是什么隔离级别，最后看执行的sql语句是否会显示加锁，是否可能存在冲突，锁分析是复杂的，最好的方式就是从系统表中直接查询当前事务的加锁情况。

添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注