MySQL 45讲笔记

本文摘自极客时间，丁奇老师的《MySQL45讲》，文章生动形象，深入浅出的讲解了MySQL的整个系统与一些底层实现。笔者花了120大洋购买的该课程，学习后发现收益良多，为加深自己理解与怕自己忘记因此梳理出本笔记。

笔记中主体内容摘自丁老师的《MySQL45讲》，部分补充内容为笔者自己的理解和极客时间上其他同学记录的一些笔记，笔者认为这些笔记很有价值，因此一并摘录。

鉴于45讲的内容很多，所以选择将笔记拆为一部分一部分的，这是第一部分。

基础篇

1. 一条查询语句是如何执行的

img

大体来说MySQL分为两层：Server层和存储引擎层。

Server层包括连接器，分析器，优化器和执行器等涵盖MySQL大多数和核心功能与内置的一些函数，所有跨存储引擎的功能都在这一层实现，包括存储过程、视图、触发器等。而存储引擎则负责数据的存储和提取，其架构模式是插件式的，支持InnoDB，MyISAM，Memory等多个存储引擎。

存储引擎与MySQL或者说MySQL Server层的关系类似于：MySQL Server定义了一些接口，比如如何存储这些数据，如何提取这个数据，而不同的存储引擎会有各自不同的实现，或者说这有些像操作系统内核与驱动的关系，操作系统定义了一些接口，如open，read，write等，硬件的驱动程序负责实现这些接口，操作系统内核调用驱动的实现接口以此来实现完整的功能。

通过上图我们也可以看到，一条查询语句会先经过Server层的连接器，分析器，优化器和执行器，最后再由执行器调用存储引擎。

1.1 连接器

连接器顾名思义，用于客户端和服务端的连接。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

如果熟悉MySQL Client操作的同学，一般都知道如下命令：

mysql -h$ip -P$port -u%user -p

这就是代表一个MySQL客户端连接服务器的连接请求，在回车里我们可以输入密码。

在完成TCP的三次握手后，MySQL开始校验你的用户名和密码，如果用户名或密码不对，会收到一个Access denied for user的错误，然后客户端程序退出。

否则，连接器会根据当前连接查看你所拥有的权限，之后这个连接里的权限判断逻辑均依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

连接建立完成我们就可以正常的执行MySQL指令，但如果客户端长时间不操作，连接器会断开这个连接，在MySQL中这个参数是由wait_timeout控制的，默认是8h。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

如果连接断开，则连接器会自动断开连接，客户端可以重新发起重连请求。

长连接和短连接是网络连接中两个比较典型的概念和场景，对于数据库而言，长连接就是连接成功后，如果客户端未断开连接，则一直使用同一个连接，短连接则是指客户端每次执行完少量的指令后就断开连接，下次再请求就再创建连接。我们可以类比HTTP协议的演变，在HTTP1.0中每次HTTP请求都是一个单独的TCP连接，也即发起一次HTTP请求后就会断开连接，下次再发送请求就新建连接再发送。而HTTP1.1中，允许短连接的建立，即多次HTTP请求可以复用一个TCP连接，多次使用后TCP连接断开。而HTTP2.0中支持长连接。

短连接的问题在于频繁的创建和销毁连接是耗时的行为，因此对于数据库的连接尽量减少连接的频繁建立，也即更建议使用长连接。

但长连接也有其问题：在连接全部使用长连接后MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

因此对于连接我们可以采用如下策略：

  • 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
  • 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

另外对于后端开发同学，会知道一个东西叫数据库连接池，数据库连接池都帮我们解决了上述问题，拿常用的druid连接池举例，我们可以配置连接数量，空闲连接数量，每隔一段时间检测空闲连接，然后关闭多余的空闲连接等。

1.2 查询缓存

建立连接完成，就可以执行你写的select语句（我们这里说的是一条语句的查询过程）。此时会来到第二个阶段，查询缓存，缓存的查询很简单，在MySQL中会有一个散列表，key为你的SQL语句，而value是该语句的执行结果，这一信息会放入MySQL的内存中。因此MySQL只需拿到你的SQL语句，向表里做个查询，如果查到代表结果在缓存中此时将value直接返回，否则会继续执行后续的流程。

这样看起来似乎非常高效，因为一旦命中就直接返回，无需执行额外的操作。而且它听起来类似于我们常见的Redis与MySQL配合使用的场景，Redis充当缓存，存放MySQL查询结果，查询时先从Redis查询，如果命中直接返回不经过MySQL。但大多数场景下我们是不建议用MySQL的查询缓存的，因为查询操作很难命中。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。

MySQL下查询缓存是默认关闭的，这由query_cache_type参数控制。

mysql> show variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)

不过MySQL提供给了我们按需使用的场景：你可以使用SQL_CACHE来显示指定使用缓存。

mysql> select SQL_CACHE * from T where ID=10；

另外需要注意的是在MySQL8.0版本已经彻底删除了查询缓存的整块功能（因为太鸡肋了，很难命中）。

1.3 分析器

在查询缓存未命中后，开始SQL语句的正式执行阶段，我们知道SQL语句是一种编程语言，MySQL要想知道当前SQL是什么意思就需要做词法分析和语法分析，而分析器就是做这个的。

分析器会先做词法分析，所谓词法分析就是将你输入的SQL语句内的每个字符串代表什么意思分析出来，如：

select * from T where id = 123

此时分析器会识别出select关键字，代表这是一条查询语句，然后将T识别为表名，将id识别为列名。

做完这些识别后，MySQL会再进行语法分析，语法分析器主要是分析当前语法的规则，你输入的SQL语句必须是满足MySQL语法的，如果语法错误会收到You have an error in your SQL syntax的错误提醒。

另外分析器还会检查你当前请求的表或字段是否存在，如果不存在也会返回错误。

1.4 优化器

优化器是MySQL中很核心的一个功能，我们的查询语句在执行前需要经过优化器的处理，这其中包括在多个索引的查询下应该用哪个索引，多表联合的时候哪个表作为主表等。另外，我们在使用Explain查看执行计划时，看到的也是优化器优化后的实际执行情况，关于优化器的优化方案和MySQL中对查询代价的评估我们后续再说。在优化器完成后，这个语句的执行方案就定了下来，此时也就可以进行执行阶段了。

1.5 执行器

我们通过前面的流程知道了一条SQL语句要做什么以及如何做，现在就开始实际的执行了。

开始执行前是对权限的校验，判断当前用户是否具有执行当前表查询的权限，如果没有会返回没有权限的错误。在连接器一节中我们讲了在连接的时候会查询用户权限，而查询到的权限就会在这里用到。

如果具有权限，执行器就会打开表进行执行，通过判断当前表的存储引擎类型来调用对应存储引擎的接口执行SQL语句，比如：

select * from T where id = 10;

假设我们没有对T表的id字段建索引，且当前T表采用InnoDB存储引擎，那么执行器查询流程为：

  1. 调用InnoDB存储引擎接口，查询表T的第一行记录，判断当前记录id是否为10，如果是将该行信息存储在结果集中，如果不是就跳过。
  2. 调用InnoDB存储引擎接口，查询下一行，重复相同判断逻辑，直到调用完表T的最后一行
  3. 执行器将上述遍历过程中所有满足条件的行组成记录集返回给客户端。

对于有索引的查询逻辑也差不多，只不过由取表的第一行记录改为取表中满足条件的第一行记录，之后循环读取满足条件的下一条记录，而这些接口都是存储引擎实现的。

对MySQL有了解的同学会知道数据库的慢查询日志中有一个rows_examined字段，其表示当前语句的执行过程扫描了多少行，这个值就是执行器在调用存储引擎获取到的数据行时累加的。但这个值并不等于存储引擎实际扫描的行数，这是因为执行器调用一次，存储引擎内部可能会扫描多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

另外通过这一节我们可以看到优化器生成执行计划，这个执行计划决定了执行器会以怎样的方式执行，会采用存储引擎中哪个接口来执行。

2. 一条更新语句是如何执行的

假设我们需要执行如下SQL语句：

update T set value = value+1 where id = 2;

MySQL执行这条更新语句的过程与查询相同，即：

img

执行更新前依然需要连接上数据库，然后由于是修改操作，因此需要从查询缓存中清空表T的所有缓存结果（这也是为什么不推荐用查询缓存），接下来是词法分析判断这条语句想做什么，优化器给出优化的执行方案，执行器去执行当前SQL语句。

但更新流程与查询不一样的是，更新流程会涉及两个重要的日志模块：redo log（重做日志）和binlog（归档日志）。首先说一下redo log是InnoDB层实现和使用的，而binlog是MySQL Server层实现和使用的。

2.1 redo log

我们先来说redo log是什么以及解决了什么问题。

上面我们说了redo log是存储引擎InnoDB实现的，而存储引擎是干什么的？实际存储和查询数据的。因此存储引擎需要十分高效的更改与查询方案，查询的高效我们都知道是索引，更改的高效呢？

假设当前有很多很多条的更改请求，请求的十分频繁，如果InnoDB每收到一条就更新这条数据并持久化到磁盘，这会带来什么影响？我们都知道InnoDB的存取单位是页（不知道的也没关系，后面会讲），每当有一条更新来的时候都需要从磁盘中找到对应的页，更新完后再写入磁盘，那么这里的每次IO都是随机IO。

随机IO的性能是远比顺序IO低的（这是因为顺序IO是不需要寻址的，但随机IO需要寻址），这首先会导致InnoDB在处理更新语句时会耗时很久，无法短时间处理大量的更新请求。其次由于一次随机IO会更耗时，那么极有可能在一次IO处理过程中服务器宕机，这会一定程度造成数据的丢失。

InnoDB面对这种情况的处理方案也很简单，就是先用顺序IO来代替随机IO，怎么代替？这就是redo log的作用。

redo log用于记录当前更新操作，也即对更新操作的一个日志记录。当有一条记录需要更新的时候，InnoDB 引擎会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

看到了没有，当有更新来的时候，InnoDB是不立马执行更新的，只是记录了一条日志到redo log，并将更新信息放到内存（用于查询）。这样就代表更新完了，实际去磁盘上找对应页的对应记录是后续有时间或恰当的时间点才去做的事，做的方法也很简单，就是看redo log内有哪些修改，对应的修改磁盘信息就好了。

这一技术就是我们经常提及的WAL技术，即Write-Ahead Logging，一条更新会先写日志随后再写磁盘。

另外redo log是通过写追加（实际是循环写）的形式记录的，所谓写追加就是一直向后追加写入，不会来回跳到不同的地方写。在MySQL启动时，InnoDB存储引擎会向操作系统申请一块连续的磁盘空间，这个空间就是放redo log的。因此这就保证了redo log是顺序IO。

采用这种方式会带来哪些好处？

首先顺序IO替代随机IO，更新操作会快了很多，并且我们可以将多次对一个页的修改统一一次写入磁盘，提高了更新的性能，其次写一条日志比数据直接写入磁盘快很多，极大的避免了在更新时服务器宕机的可能。这里很多同学可能会有个问题：磁盘的更新是慢于redo log的，那如果此时服务器宕机怎么办？很简单，我们每次将redo log信息写入磁盘都会标记一下，服务器再重启的时候，我们先看下redo log上次写入磁盘的位置，然后从这个位置往后都是还未写入磁盘的，那么写入就好了，这样数据就不会丢失，这一能力有个专业的词叫crash-safe。

这里很多同学可能会有另一个问题：redo log是写追加的形式记录，那如果服务器运行很长，那redo log岂不是会很大，在文件很大的时候无论是写入还是重启后读取redo log恢复数据都会很耗时。

实际上，redo log不是无限长的，它是固定大小。比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么redo log总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写。

img

redo log会有两个指针，一个是write pos 是当前记录的位置，一边写一边后移，当写到文件末尾时就回到文件的开头。另一个是checkpoint ，代表的是当前redo log与磁盘同步的位置，checkpoint - write pos间的数据代表均未同步，不可覆盖，而 write pos - checkpoint 间的数据代表均已同步，可以覆盖。如果checkpoint追上write pos代表数据均已同步，反过来如果write pos追上checkpoint，代表redo log满了，写不下了，需要先进行一下同步，释放点空间才能写。

另外再重申一遍redo log是InnoDB实现的！！！InnoDB实现的！！！它不是MySQL的固有功能，其它存储引擎是不具有这个功能的。

2.2 binlog

binlog是MySQL的日志，无论你用什么存储引擎都会有binlog。很多同学可能会问，为什么MySQL会有两个日志，以及这两个日志有什么不同？最早的时候MySQL是没有InnoDB的，当时MySQL自带的存储引擎是MyISAM，但MyISAM并不具有crash-safe的能力，为此InnoDB使用redo log来实现了crash-safe能力。

总结下，这两种日志有以下三点不同

  1. redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

下面我们再来看下binlog是何时记录，以及redo log与binlog是如何配合的：

我们以之前的更新语句为例来看下执行器和InnoDB存储引擎间执行的流程：

update T set value = value+1 where id = 2;
  1. 执行器先找引擎取 id=2 这一行。ID 是主键，引擎直接用B+树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

    img

这里很多同学可能对第3-5步有疑问，为什么redo log多了一个状态，prepare和commit状态是什么意思？

这就是两阶段提交，两阶段提交主要是为了保证redo log与binlog的一致性。

我们将上述的3-5流程简单梳理如下：

  1. InnoDB执行完更改，记录一条redo log状态是prepare
  2. 执行器记录binlog
  3. InnoDB将刚才的redo log改为commit。

这样设计核心目的是为了保证binlog与redo log的一致性，这样如果在某个步骤crash了，恢复的时候binlog与redo log不会不一致造成冲突。那么问题来了，为什么这样两阶段提交可以保证日志的一致性？

我们假设数据库的crash会发生在任何阶段，如果发生在1-2间，即记录了redo log状态是prepare但binlog记录失败，那么redo log将数据同步到磁盘的时候会查看到这条记录是prepare，就会找这条记录对应的binlog（这里补充一下，在redo log记录为prepare时同时会记录一个XID，XID可以简单理解为当前binlog的ID，这样可以更快的以及唯一的找到一条binlog），此时binlog是不存在这条信息的，那么InnoDB就不会将这条记录同步到磁盘，这代表数据commit失败，执行回滚。

假设crash 发生在2-3之间，即记录了redo log状态是prepare，且binlog记录成功。那么redo log将数据同步到磁盘的时候会查看到这条记录是prepare，就会找这条记录对应的binlog，此时binlog是存在的，那么InnoDB会将这条redo log状态改为commit，然后将这条记录同步到磁盘。

如果crash发生在3之后，即记录了redo log状态是commit，且binlog记录成功。那么InnoDB会将这条redo log的修改同步到磁盘。

看明白了吗？通过两段提交保证了binlog与redo log的一致性，binlog里没有的redo log也就不需要同步到磁盘，binlog有的，redo log就需要同步到磁盘。

这里补充说明下在3步骤之后发生crash与[checkpoint,write pos]的区别，我们之前说了[write pos,checkpoint]之间的存储都是可覆盖的，这代表这期间的redo log记录都已经同步到了磁盘的实际数据库数据页的更新。而[checkpoint,write pos]的内容则是已经记录了redo log但还未同步到磁盘的，这期间的数据就分为三种，也就是上面说的三个情况：

  1. redo log prepare但binlog不存在
  2. redo log prepare且binlog存在
  3. redo log commit

数据库发生crash后，根据redo log进行数据恢复时其实就是恢复[checkpoint,write pos]间的数据（还未同步到磁盘的），上述情况1 不需要恢复，2和3需要恢复，所谓恢复就是将redo log记录同步到磁盘中实际数据库数据页的更新。

第三种情况redo log commit与[write pos,checkpoint]间的记录不同，前者是已经完成事务但还未同步数据页的，后者是已经同步了的。

如果不两阶段提交，也即如果binlog与redo log不同步会怎么样？如果不两阶段提交，那么要么redo log先写，要么binlog先写：

  1. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
  2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同

这里又会带来另外两个问题：

  1. 既然redo log自己就能crash safe，那完全可以不需要binlog，binlog有什么用呢？

    解答：如果你是在单机MySQL下（并且你不需要数据的备份和恢复），确实没问题，甚至你都可以把binlog关掉，MySQL是支持binlog关闭的。但如果是主从的MySQL（或需要用binlog恢复数据），我们知道主从间数据的同步通过binlog，主服务器通过发binlog给从服务器来保证数据的同步（为什么不用redo log？因为redo log是InnoDB自己的，并非所有存储引擎都有redo log，但都会有binlog）。因此多机下就需要binlog，且如果我们的binlog与redo log不同步，比如即使binlog不存在也会同步更新到磁盘，那么由于binlog不存在，也即从服务器没有收到binlog，从服务器是没有这条更新的，但主服务器redo log是存在的，如果根据redo log恢复，此时redo log与binlog不一致，那么会造成主从数据的不一致性。

  2. 既然binlog与redo log是一致的，那恢复的时候直接用binlog不就可以了，为什么用redo log来恢复？

    因为实际干活的人是存储引擎，到底这个数据有没有同步到磁盘（后续将redo log与磁盘同步我们统一称为刷盘），binlog是不知道的。拿上述两阶段提交举例子：先是记录redo状态prepare，然后记录binlog，再是将redo改为commit。如果以binlog作为恢复的判断依据，那如果在记录完binlog但未将redo改为commit时发生crash，此时表面上看这条记录已经执行成功了，但实际根本还没刷到磁盘的，执行成功是存储引擎告诉它的成功，到底是不是真的成功只有存储引擎自己知道（譬如InnoDB并不会真的修改磁盘，只是记录下日志就返回成功了），因此binlog不能作为数据恢复的依据。

最后我们再来说一个数据备份和恢复的场景：假设你们公司的MySQL服务器崩了，并且磁盘也坏了，你想恢复数据库到原来的样子。或者你们公司的数据库没有坏，但你想将数据库状态恢复成今天早上8:30的样子，那应该如何做？

首先你数据库的binlog是需要开启并需要做备份的，备份频率取决于系统的重要性，备份可以保证数据库服务器如果挂了并且磁盘坏了，binlog不会也跟着丢失。其次找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；然后，从备份的时间点开始，将之后备份的 binlog 依次取出来，重放到你想数据恢复的那个时刻。

3. 事务隔离

3.1 事务隔离级别

谈到事务我们都知道ACID四大特性，其中I指的就是隔离性，而根据隔离的不同程度又出现了隔离级别的概念，有些基础的同学肯定知道数据库的四大隔离级别与其相应的问题：

  • 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
  • 读已提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
  • 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  • 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

上面四个等级的隔离性越来越高，效率越来越低。

我们将从一个例子解释这些隔离级别及其问题：

假设现在有两个事务A、B

img

  • 如果当前隔离级别是读未提交，那么V1的值就是2，此时事务B是还未提交事务的，但已经被事务A读到，此时如果事务B回滚或后续又做了其他操作，那很有可能我们读到的是脏数据，因此读未提交会出现脏读的现象。

  • 如果当前隔离级别是读已提交，则V1是1，V2和V3是2。这里由于事务A读取V2是在事务B提交以后做的，因此我们读到的是已经提交的数据，此时V2是2，对于V3已经是另一个事务了（MySQL规定如果未开启事务则每一个语句都是单独的事务），所以V3也会读到事务B提交的结果。

    但这里依然会出现一个问题：在事务A的生命周期内共查询了两次值，但这两次的结果却不一样（V1 = 1，V2 = 2），也即一个事务下重复读取一个数据可能每次读到的结果不同，因此读已提交带来的问题是不可重复读。

  • 如果当前隔离级别是可重复读，则V1是1，V2是1，V3是2。这是因为可重复读要求事务在执行期间看到的数据前后必须是一致的。
  • 如果当前隔离级别是是串行化，则在事务 B 执行“将 1 改成 2”的时候，会被锁住，直到事务 A 提交后，事务 B 才可以继续执行（所谓锁住就是事务B会一直阻塞直到事务A提交完成才能再操作。这是因为读写锁的原因，读锁允许并发的读，写锁是独占锁，只允许一个事务在写，并且当读的时候是不允许其他事务写的，如果现在有其他事务在读，得需要等到读完才能写。因此事务A会在查询值得时候会得到读锁，而事务B在获得值也会获得读锁，读锁可以共享，但事务B在修改值得时候需要申请写锁，但此时事务A还是持有读锁得，需要事务A事务提交才能释放读锁，因此事务B会锁住，直到事务A提交才能执行写入操作）。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

对于不同的隔离级别，InnoDB的实现也不一样，首先读已提交和可重复读，数据库会创建一个视图。我们访问数据的时候以视图的结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的（注：不知是否是原作者笔误，一致性视图应该是在执行第一条查询语句时建立，而非事务启动时建立，除非强制要求。），整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的（这里也是有些问题，不知是否是原作者笔误，其实并非所有SQL都会建立一致性视图，比如查询语句才会建立，更新语句就不需要。另外，对于读已提交，视图是每执行一个SQL语句就会创建一个视图，因此如果在执行本条SQL语句之前有别的事务提交，那么创建视图的时候就可以看到别的事务的提交，而对于可重复读，其视图是在事务启动的时候就创建好了，这个视图会一直用到事务结束，也即事务执行期间所有的SQL都用这个一开始创建的视图，那么自然不会读到别的事务的提交）。至于“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念。而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

这里需要补充说明一下上面说的视图并非指我们经常熟知的为方便查询的连结两个表的虚拟表，这个视图是一致性视图：

在 MySQL 里，有两个“视图”的概念：

  • 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
  • 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

另外对于Oracle数据库其默认隔离级别是读提交，而对于MySQL其默认隔离级别是可重复读。

很多人在使用事务的时候，很难界定读提交和可重复读，那什么时候要用可重复读呢？

假设你在管理一个个人银行账户表。一个表存了账户余额，一个表存了账单明细。到了月底你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便。

看到了吗，当你希望在执行一个事务的时候，当前正在修改的事情不要影响到你，那么你最好用可重复读，当在可重复读的级别下，事务一旦开始数据库就好像静止了，所有事情都是事务一开始时那样子的。否则如果你没有这种需求，则可以使用读提交。

可重复读还有一个使用比较频繁的场景就是数据备份。有时我们想将每个表都select出来存成文件，为了保证备份的数据都是同一个逻辑时间点的内容（因为select每个表的操作会很耗时，有可能select一开始与select期间的数据不是一个时间点上的数据，我们很多时候想要同一时间点的数据库数据，有些类似于这个时间点上数据库的快照），这时候就可以在可重复读级别下开启事务。MySQL官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。但这里也需要注意的是只有支持可重复读级别的存储引擎表才可以那么做，比如MyISAM就不支持。

3.2 隔离级别的实现

我们以比较复杂的可重复读隔离级别来说下隔离级别的实现：

我们之前说过，隔离级别下会在事务开始（实际是查询的时候）的时候创建一个视图，对于不同的事务会有不同的视图，在每个视图下同一条记录的值也可能不尽相同，那对于同一条记录是如何保证不同视图可以看到不同的结果呢？

在 InnoDB中，实际上每条记录在更新的时候都会同时记录一条回滚操作（所谓回滚操作就是逆操作，也即通过什么样的操作可以从当前版本得到上一个版本的信息）。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录：

img

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制，也就是大名鼎鼎的MVCC。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

这里需要补充几点：

  1. 这个所谓的回滚日志就是有名的undo log（至此我们已经说了redo log，binlog和undo log ）。不同的事务可以通过undo log来实现数据的回滚，保证可重复读下的事务隔离性，另外对于需要回滚的数据，也可以通过逆操作来实现回滚。
  2. 对于一条记录的变更，我们需要用一条日志记录这个变更，这是redo log。同时还需要一条日志记录与变更相反的逆过程这是undo log。

这里 很多同学可能会有一些疑问：

  1. 这里的多版本记录有些类似于版本控制软件（如Git）对一次commit的管理，回滚也有些类似于版本管理里的版本回退，可以这样理解吗？

    解答：这样对比可以能让你对MVCC理解更深，但实际两者是有些不同的。在Git里，版本的回退是直接通过指针跳转来实现的（指针指向哪个版本，当前就是哪个版本），而MySQL是通过逆操作实现的。也即Git中每个版本都是独立的，Git可以通过修改指针的指向来代替回退，因此Git可以直接指向某一个很久以前的版本（直接指明版本号即可），这时这个回滚是不需要一个版本一个版本往回倒的，但MySQL如果想退回更早的版本，就需要一个版本接一个版本的指向逆操作。

  2. 我们之前说可重复读级别下，事务一开始的时候视图就创建好了，那既然一开始就创建好了视图，数据应该都是已经拿到固定的了，为什么还需要MVCC以及逆操作来获得以前版本数据呢？

    解答：一致性视图有些类似于当前数据库的快照，指的是创建视图的那一瞬间当前数据库内的信息，但实际上MySQL不可能真的创建那么一个快照，因此一致性视图只是逻辑概念，并非真的存在。我们之前知道每条数据都存在多个版本，这种说法也是有歧义的，准确来说数据库内的每条数据都只有一个版本，但MySQL提供了可以追溯版本的方案，就是执行undolog逆操作。因此一致性视图的创建其实就是对当前版本的一个结果记录，当我们需要查询某条数据时，会从这条数据的最新版本往回倒，一致逆操作到当前版本（或低于当前的版本），每条需要用到的数据都可以那么做，这样看起来就像创建了一个快照。

    换句话说，一致性视图或者快照是不真实存在的，是个逻辑概念，它是通过undolog加版本综合作用得出来的。

  3. redo log是固定长度的，也即会记录日志的时候会进行擦除，undo log也会吗

    解答：是的undo log也不能一直保留，数据库会在不需要时候进行删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。那什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候（也即没有更早的视图时，也就不会有人回滚的时候用到它了，另外一个事务提交的时候，视图也就消失了）。因此我们也可以得出一个结论：尽量不要使用长事务，如果使用长事务，则意味着这个事务会存活的很久，那么也就代表着在这个事务提交前，所有会用到的回滚段都不能删除，事务越久积累的回滚段也就越久，这会导致undo log的存储占用大量空间。

关于undolog和MVCC更多内容可以看下第7章。

4. 索引

MySQL索引的知识比较多，它需要读者对数据结构有着较深的理解，由于篇幅有限且笔者能力欠缺，此处的索引不从基本的B+树讲起，也不从MySQL的页结构讲起，我们假设读者是熟悉和了解这一信息的，知道InnoDB下索引的具体实现（不了解的可以阅读《MySQL是怎样运行的》4-6章）。

本章主要想讲一下联合索引以及几个概念，这些概念对于我们优化MySQL有着很大的作用。

  1. 覆盖索引

    为了故事开展顺利，我们先建一张表，并插入几条数据

    create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

    现在我们有两个索引ID和k，也就代表有两棵B+树：

    img

    如果此时我们要执行一条查询语句：

    select * from T where k between 3 and 5

    这条语句会执行几次树的搜索操作，以及会扫描多少行？其分析流程如下：

    • 先从索引为k的B+树上查询k = 3的记录，取得ID = 300
    • 回表，从索引为ID的B+树上查询ID = 300的记录，得到记录R3
    • 沿着k = 3的记录向后搜索，取下一个值 k = 5，满足条件，ID = 500
    • 回表，从索引为ID的B+树上查询ID = 500的记录，得到记录R4
    • 沿着k = 5的记录向后搜索，取下一个值 k = 6，不满足条件结束搜索

    从上述流程我们知道对于k索引树，进行了一次树的搜索，扫描了3行记录，同时我们还进行了两次回表，也即对于ID索引树进行了二次树的搜索。

    上例中，由于我们要查询的字段在聚簇索引上，所以必须回到上面来查询，但回表是一个很耗时的操作。

    现在假设我们执行

    select ID from T where k between 3 and 5

    那么在k索引树上就包含了ID信息，就不需要回表，对于这种某个查询索引树覆盖了我们的查询需求不需要回表的情况我们称为覆盖索引。

    由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

    有了上述基础后我们再来讲另一个例子：

    CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

    现在假设你们公司要做居民信息的统计，需要居民的身份证号，姓名等信息。

    首先我们知道居民身份证号是唯一的，我们往往也有一些需要通过身份证号做查询的场景，因此就给身份证号建一个索引。

    假设现在我们有一个高频率的请求，要求根据居民身份证号查询他的名字，即：

    select name from tuser where id_card like '???'

    我们知道查询的时候会走id_card这个索引树，但这棵树上并没有name信息，因此就需要执行回表，而回表是耗时的操作，那能不能避免回表呢？

    我们可以建立联合索引(id_card,name)，此时这棵联合索引树上就包含了name这一信息，满足了覆盖索引，无需回表，这就是通过覆盖索引优化的典型场景。

  2. 最左前缀原则

    熟悉联合索引的同学会知道联合索引先根据第一个字段排序，如果第一个字段有相同的，就按照第二个字段排序，注意，这里仅仅有相同的第一个字段情况下，才会根据第二个字段排序。

    另外B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录，比如你建立了一个名字的索引，那么当执行：

    select * from tuser where name like '张%';

    此时这条查询语句也会走你的名字索引。

    因此不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

    有了这个概念后，我们再来看之前说的联合索引(id_card,name)，有了这个索引后，其实我们完全可以删了id_card索引，因为id_card其实是唯一索引。另外补充一下：创建索引会增加写操作的时间，因为写操作会带来对B+树的维护。但我们从id_card索引改为(id_card,name)索引时，由于id_card是唯一索引，因此理论上id_card与(id_card,name)维护成本是相近的，在写维护成本相近的情况下，(id_card,name)还解决了根据id_card查询name的回表问题，因此性能更高（如果id_card不唯一，那么维护成本联合索引更高，因为name的变动可能也会修改索引树）。

    最左前缀的好处是如果我们也需要根据身份证号查询年龄或性别，但这些查询又不是高频，那么完全可以走(id_card,name)索引树，查询出ID后再回表查具体信息，而无需建立(id_card,age)，(id_card,ismale)等索引。这也就是最左前缀带来的索引复用能力。

    另外联合索引的顺序是一个很关键的内容，顺序选择的第一原则是：如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

    如果有下面情况，联合索引(a,b)，既有对a,b的联合查询，又有对a,b的单独查询，在查询b时无法使用(a,b)这个联合索引，因此往往就需要单独建立一个索引b，在这种情况下考虑的原则是空间原则，将字段占用较小的那个设为b。比如上例中的(name,age)索引，首先会有个(name,age)的B+树，此时我们单独再为age建索引，那么就是(name,age)与age两棵树。但如果改变顺序，改为(age,name)与name索引树，由于一般情况下name所占空间大于age所占空间，因此后者空间占用比前者多，这里我们考虑的就是空间原则。

  3. 索引下推

    还拿上面的居民信息统计表举例，假设执行如下SQL语句：

    select * from tuser where name like '张%' and age = 10 and ismale = 1;

    根据前面的最左前缀原则我们知道这条SQL语句会走(name,age)索引，它会先找到第一个满足名字开头是张的记录，现在有了一个新的问题，找到这条记录后是回表，在表里判断 age = 10 and simale = 1还是先在(name,age)索引树中判断 age = 10再回表呢？

    在MySQL5.6之前，就是我们说的第一种情况，根据 like '张%'得到ID，在回表后判断age和ismale字段。

    但在MySQL5.6及以后版本引入了索引下推：索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

    还一种经典的使用场景，如联合索引(a,b,c)，根据a,c来查询时，会先根据最左前缀按a查询，对在联合索引树上查到的记录进行索引下推，判断c的条件是否成立。索引下推的目的就是过滤不必要的回表操作，提升查询的性能。

5. 全局锁和表级锁

在介绍锁之前先介绍两个概念：DDL和DML。

所谓DML就是数据操作语言(Data Manipulation Language)，对当前数据库内的所有数据操作就属于DML，比如select，update，insert操作等。

而DDL是数据定义语言(Data Definition Language)，对当前数据库内对象的操作属于DDL，比如create创建表，alter更新表结构等。

另外再介绍一个概念读写锁：

很多时候对于资源的访问我们往往可以分为读写访问，为保证数据库的安全性往往就会使用读写锁，读写锁保证了读锁是共享锁，多个线程可以一起读取资源，写锁是独占锁，只能一个线程一个线程的写入，同时当读的时候是不能写入的，当写的时候，其他线程也是不能读取的（自己线程可以读取）。释放读锁获取写锁称为锁升级，释放写锁获得读锁称为锁降级。

MySQL数据库在设计初期的时候就考虑了并发问题，为处理并发保证数据的安全性，最常见的就是加锁。

加锁的颗粒度不同，锁住的范围也不同，在MySQL下大致可以分为全局锁，表级锁和行锁三类。顾名思义，全局锁就是对当前数据库实例整个加锁，而表级锁是对某张表（或表信息）加锁，行锁自然是对一行加锁。本章我们先讲下全局锁和表级锁，下章再说行锁。

5.1 全局锁

当使用全局锁时，MySQL将只允许对当前整个库做读操作，所有的写操作都会被阻塞（包括DML和DDL的写操作，比如数据的增删改和建表，更新表结构）。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。在unlocks table或客户端断开连接时全局锁会被释放。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

在备份时加锁往往是考虑到想在备份的一瞬间将数据库的信息固定住（因为没有写操作来更改数据库了），这样备份的时候得到的库信息就是某个逻辑时间点的信息，视图是一致的。如果不加锁，查询出来整个库的时间可能会比较耗时，这样往往会出现库存表减了，但是余额表没变（或者余额表变了，库存表没变）的现象。

如果了解我们之前说的事务隔离级别，就会想到可以使用可重复读的隔离级别下开启一个事务来select操作备份数据。由于可重复读下的视图是一致的，不会读到事务执行过程中别的事务提交的信息，因此也是一个逻辑时间点的信息。另外，MySQL官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的（MySQL默认隔离级别就是可重复读）。

明显可以看到使用single-transaction比FTWRL要好很多，那为什么不直接用single-transaction呢？因为single-transaction这个功能需要存储引擎支持这个隔离级别，对于MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。

5.2 表级锁

MySQL下有两种表级锁：表锁和元数据锁(meta data lock, MDL)。

  1. 表锁

    表锁的作用域在某张表，我们可以加锁限制某张表的读写，表锁是读写锁，读写锁的特性我们已经在本章开头讲了。

    表锁的语法是 lock tables … read/write。如lock tables t1 read, t2 write，这样我们当前线程就读t1上了读锁，对t2上了写锁，这时其他线程就无法对t1执行写操作（因为读的时候不能写），也无法对t2执行读写操作（写的时候别的线程不能读写）。自己线程同样也只能对t1执行读操作，不能对t1做写操作，也即表锁不支持锁升级。

  2. 元数据锁

    什么是表的元数据？其实就是表的那些结构信息，比如这张表有哪些属性，每个属性又是什么样等。我们知道DML语句只修改表内的行记录不修改表结构即元数据，但DDL语句是修改表元数据的，同时我们还需要知道的是在进行DML的时候是不允许表结构修改的（保证读写的正确性），因此这就需要一个对元数据的锁，即MDL(metadata lock)，这个锁也是读写锁。元数据锁不需要显示使用，执行一个DML语句时会自动的加读锁，执行一个DDL语句时会自动的加写锁，在事务执行完成才会统一释放锁。

    我们知道读写锁的读锁是共享锁，因此在没有DDL语句执行时，DML语句可以并发的执行。但如果有DDL语句的执行，DML语句就需要等DDL语句执行完提交事务释放锁再执行。同样DDL与DDL之间也是互斥串行的。

    另外这里说一个实战时的问题：

    img

    上述例子中，由于 Session A与Session B是先于SessionC启动，需要的是MDL读锁，因此会正常执行不会阻塞，SessionC由于需要MDL写锁，但此时SessionA和SessionB的事务还未完成，读锁还未释放就需要挂起等待。那后来的Session D呢？也会被阻塞。

    很多同学可能会很意外，觉得SessionD是申请读锁啊，现在没有人在写，读锁是可以被申请的啊，为什么会阻塞？其实不然，如果熟悉读写锁结构的同学会知道，为了保证线程的阻塞和唤醒，一般锁的设计都会有个队列，这个队列内就是装目前阻塞的线程，Session C由于执行DDL需要获得写锁，但获取不了因此挂起，此时队列内就有了一个挂起的线程Session C，这时Session D来了，MySQL的读写锁规定写锁优先级高于读锁，因此Session D一看队列里还有因为要申请写锁而挂起的线程，那我优先级比别人低，我肯定也得挂起，等人家用完写锁我再用读锁吧。这其实也很好理解，如果想获得读锁的线程不判断当前队列内是否有因为要获得写锁而挂起的线程，而是直接获取读锁，很可能造成锁饥饿。比如SessionD此时不关心Session C而是直接获得读锁，那么SessionC还需要等后到的SessionD用完读锁才可以获得写锁，但如果SessionD快用完的时候又来了Session E，Session F，SessionG...，它们都直接获取读锁，那很可能Session C一直获取不上写锁（插队的太多了）。

    了解了上面一个情况后我们想一下，如果后续又跟来了很多很多查询语句，且客户端还加了查询失败重试的机制，那么自然这些查询都会被阻塞且这个数据库的线程会很快爆满。

    因此为了修改表结构的安全，我们首先要解决长事务，对于未提交的事务会一直占着MDL锁，这样可以保证，DDL语句的执行不会等待太久，自然也不会导致DDL之后申请的DML语句等待太久。

    其次在执行DDL语句里最好加一个等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃，后续再重试即可。

6. 行锁

MySQL的行锁是存储引擎自己单独实现的，我们熟知的InnoDB是支持行锁的，但MyISAM是不支持的。因此 我们对于行锁的讨论都是在说InnoDB存储引擎。

行锁的好处是锁的颗粒度更细，这样锁的冲突更小并发度也就更高。

本章对于行锁的讲解主要是两个知识点：两阶段锁、死锁和死锁检测。

6.1 两阶段锁

所谓两阶段锁是指锁的获取和释放是在两个不同的阶段。InnoDB中，行锁是在需要的时候才加上的，而锁的释放是统一在事务结束后释放的。所谓需要时就是只有语句在执行时才加上的，而并非事务开始就加上。举个例子如下：

时间事务A事务B
T1begin;
T2update t set k=k+1 where id=1;
T3update t set k=k+1 where id=2;
T4 begin;
T5 update t set k=k+2 where id = 1;
T6commit;

我们假设表t的主键是id。

上述流程中，事务A会在T2时刻获得id=2这一行的锁，在T3时刻获得id=2的行锁，事务B在T5时刻想获得id=1的行锁，但已经被事务A在T2时刻获得了，因此只能等待锁释放。事务A会在T6时刻才释放锁，因为锁的释放是在事务结束后统一释放的，因此事务B只能在T6之后才可以执行更新语句。

两阶段锁对我们实际SQL优化有着有很重要的指导意义：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。我们以一个例子说明：

假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：

  1. 从顾客 A 账户余额中扣除电影票价；
  2. 给影院 B 的账户余额增加这张电影票价；
  3. 记录一条交易日志。

为保证这个操作的原子性我们需要将这三条语句写在一个事务中，那怎么安排这三条语句的顺序呢？

上述三条语句中，多个事务一起执行时，冲突的是语句2，因为不同的人都需要给同一个影院B转账，影院B的账户余额是同一条记录。因此我们应该将语句2放在最后，比如按照3,1,2的顺序，让大家都需要想要的资源放在最后获取，避免它被一个线程占用太久。

6.2 死锁和死锁检测

6.2.1 死锁

了解了两阶段锁后敏锐的同学就会发现，两阶段锁会带来死锁现象，比如：

时间事务A事务B
T1begin;begin;
T2update t set k=k+1 where id=1;update t set k=k+1 where id=2;
T3update t set k=k+1 where id=2;update t set k=k+1 where id=1

上述事务A会在T2时刻获得id=1的行锁，事务B会在T2时刻获得id=2的行锁。这样T3时刻事务A再想获得id=2的行锁就得等待，同理T3时刻事务B想获得id=1的行锁也得等待。

Innodb对于死锁有两种解决方案：超时和死锁检测。

超时就是如果事务A因为未能立马获得锁需要等待，就等待一个最大等待时长，这个时长由参数innodb_lock_wait_timeout来决定，默认为50S。可以看到超时并非一种比较好的方案，比如一个SQL的执行要等待50S这是不能允许的，但反过来如果将超时时间设得比较短如1S这样会出现很多误伤，基于此我们往往选择第二种方案来解决死锁：死锁检测。

死锁检测的开启是通过innodb_deadlock_detect参数控制，默认为true，也即默认是开启的（MySQL5.7.15及以上版本）。

6.2.2 死锁检测

每个事务一旦检测到自己无法立马获得锁就会进行死锁检测。

死锁检测的方案如下：事务一旦检测到自己想要获得的锁已经被其他事务获取了，会先查看自己目前持有的锁，再遍历所有因这个行（资源）而挂起的事务，看看这些里的每个事务是否也因为别的

todo 死锁检测

part2-innodb引擎的死锁&死锁检测机制 - 掘金 (juejin.cn)

7. 再说事务隔离与undolog

本章的知识点会很多，我们会将前面讲的很多知识点串起来，比如undolog，可重复读和行锁。

先从一个例子说起

CREATE TABLE `t` ( 
    `id` int(11) NOT NULL, 
    `k` int(11) DEFAULT NULL, 
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

上面我们创建了一张表并插入了两条数据：

现在基于这张表和这两条记录，我们创建三个事务：

时间事务A事务B事务C
T1start transaction with consistent snapshot
T2 start transaction with consistent snapshot
T3 update t set k = k+1 where id = 1;
T4 update t set k = k+1 where id = 1;
select k from t where id = 1;
T5select k from t where id = 1;
commit;
T6 commit;

首先需要说下start transaction with consistent snapshot指令：

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。另外对于第一种方式，一致性视图是在执行第一条查询语句创建的，而第二条语句的一致性视图是在这个时候创建的。

虽然事务C没有显示的声明begin/commit，但MySQL规定未显示声明的每一条语句都是一个单独的事务。

了解了这些后我们就需要提出一个问题：上述事务A的查询结果是多少？事务B的查询结果又是多少？

答案是事务A的查询结果是1，事务B的查询结果是3。是不是很诧异，别着急，我们这章就来解释这个情况，在这章你会对一致性视图理解的更深，也很更加清晰行锁在事务间的作用。同时也会学到一些新的概念，比如当前读（current read）。

7.1 一致性视图

我们在第三章的时候其实已经引入了一致性视图的概念，这里我们再介绍下。

在 MySQL 里，有两个“视图”的概念：

  • 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
  • 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

拿RR隔离级别举例，在事务创建一开始的时候，就对当前数据库拍了个快照，也即我们就有了一个当前事务创建时这个时间点下的数据库内容，后续我们所有的查询语句都是基于这个快照的。比如事务A在T1时刻开启了事务并创建了快照，此时这一瞬间的id = 1的这行记录还是(1,1)，因此查询回的k值自然也是1，至于后面的各种更新修改与我又有何干系呢？当我建立快照的一瞬间，数据库对我而言就静止了，我所有查询都是这个快照的结果的，后续再怎么折腾我都是不关心也“看不见”的。

这个快照就是一致性视图。说到这你可能就会疑惑了，拍个快照？如果我数据库现在有100GB的数据，你这个快照不也100GB了，每个事务都有个快照，那也不可能啊。确实不可能，那我们就不得不说说这个“快照”是个什么东西了。

7.2 一致性视图的本质

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

熟悉InnoDB行格式的同学应该知道，每条InnoDB的行记录都会有一个row trx_id的属性，这个属性记录的是修改当前行记录的事务的transaction id。

还拿上面的例子来说，事务A，B，C开启事务，事务id严格递增，我们假设事务A的事务id是100，B是101，C是102。当事务C执行update t set k = k+1 where id = 1;此时id=1的这一行就是被事务C修改的，那么这一行的row trx_id的值就是102。也即行记录的row trx_id属性记录了这行是被哪个事务修改的。

我们又知道，InnoDB的每条记录都存在多个版本，而所谓版本其实就是事务id。每个事务我们都可以理解为一个版本号，比如：

img

上图中一行记录存在多个版本，每个版本都代表是被之前的一个事务修改过后的状态。

我们又知道，上图中的虚线其实就是undolog，因此我们可以得出一个结论：

在InnoDB中，一条记录其实就只有一个版本，是最新版本。只不过InnoDB提供了可以追溯到之前版本的方法，而所谓的版本其实就是事务id，每个事务可以根据自己的事务id，通过追溯找到与自己事务id对应的版本的数据记录（或更早版本的）。追溯的方法就是undolog，执行undoLog逆向操作。

而所谓的可重复读或者后续更新不可见，其实就是通过版本+undolog找到属于自己版本的数据而已。

还拿上图举例，事务X在V2和V3版本之间建立了一致性视图，这时候事务X说：在我建立视图之前提交的数据我都是认的，在我建立视图之后提交的数据我都“看不见”。事务X要想获得k的值，会先得到当前行记录，我们之前说了其实只有一条行记录，是最新的行记录。事务X拿到后一看，k=22，但版本是V4，这个版本太高了，我不认，我得回退。因此它拿着V4版本的k=22的值开始通过undolog回退，退一步到V3版本的k=11，但V3也是在事务X建立视图后创建的，V3也不认，那再往后，就退回到V2，k=10，V2版本是在事务X视图建立前提交的，这时候就认了，此时事务X查询回来的k的值就是10。

这个过程中，事务X真的看不见最新版本的值吗？当然看的见，实际上大家都看得见，因为大家想读这行的记录只能读到V4版本的信息，但读到了又怎么样，又用不了，那怎么办？往回倒呗，根据undolog记录往回倒，一直倒到能用的版本，这样看起来就像后面的V3或V4版本对事务X不可见一样。

同样的道理，如果事务X这时候还没结束，但这条行记录又被更新了几个版本，V5，V6，V7等等。事务X此时要查询，那这些更新跟它有关系吗？不能说毫无关系，因为虽然事务X只想要V2版本的值，但也得从最新版往回倒。只不过由于更新的版本多了，得多往回倒几次才能到V2。

7.3 视图数组

我们现在已经知道了什么是一致性视图，以及为什么可重复读隔离级别下后续的更改是不可见的。我们可以总结下：每个事务都有个事务id，事务id是严格递增的。所谓事务id也就是版本，每个事务在建立一致性视图的时候都要求在自己视图建立前的提交是可见的，在自己视图建立后的更改是不可见的。但InnoDB中其实只记录了最新版本的行记录，如果想得到之前版本的数据，就需要根据最新版本信息加上undolog回退。

但现在有个问题：每个事务在建立一致性视图的时候，如何判读自己是哪个版本呢？或者说在一条行记录的诸多版本中对自己可见的版本是哪个版本呢？

这需要讲到一致性视图的创建：InnoDB 会为要创建一致性视图的事务构造一个数组，这个数组用来保存一致性视图启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。另外这个数组叫视图数组。

上面这段信息非常关键，我们假设现在有一个事务X，它的事务id是100，当它要创建一致性视图的时候，获得的的视图数组如下：[95,98,102,104]。

我知道你可能会很诧异，比如为什么视图数组会存在比当前事务小的事务id，以及为什么视图数组不连续等。在解释这些以前我们需要先清楚事务id和一致性视图创建的时间。

事务id是在执行第一条InnoDB语句的时候创建，比如：

时间事务X
T1start transaction;
T2update t set k = k+1 where id = 1;

上述中事务X的事务id是在T2时刻创建的，而非T1时刻。

一致性视图是在事务需要一致性视图的时候才创建的，所谓需要一致性视图就是执行查询语句的才会创建，比如：

时间事务X
T1start transaction;
T2update set t k=k+1 where id = 1;
T3select k from t where id = 1;

上述事务X的一致性视图其实是在T3时刻创建的，而非T2时刻。

了解了这些以后我们再来解释刚才的问题：

首先T2时刻，事务X申请事务id，事务id是100，在T2~T3时刻执行update set t k=k+1 where id = 1;语句，但是在执行当前语句期间，又立马进来了很多事务，比如事务101~105。

到了T3时刻，事务X开始创建一致性视图，但此时事务101,103,105已经执行完成（已经提交），不再属于活跃状态。因此目前活跃的事务是102,104。但同样的，在事务X执行前，还存在一些更早的事务，比如事务95,98，它们比事务X更早，但执行的比较久，T3时刻它们依然存在，因此此时的视图数组就是[95,98,102,104]。

那这个时候对事务X可见的版本是哪些呢？哪些又是不可见的呢？首选我们根据视图数组划分出高水位和低水位，低水位就是视图数组中最小的事务id，高水位是视图数组中最大的事务id+1。那么我们的低水位就是95，高水位是105。很明显比95低的事务id其实都已经完成并提交了，这些自然是可见的。而大于等于 105的事务id都还未创建，都是未来的事务，自然是不可见的，那么95~104之间的事务id呢？这里面分为两种情况，一种是已经提交了的事务，比如97,101等，这些因为在视图创建前已经提交，自然是可见的。还一种是未提交（活跃）的事务，比如95,98,102,104。这些因为还未提交，为避免回滚出现脏读自然是不可见的。因此我们可以概括为：高水位线以后的事务+视图数组中的事务都是不可见的，其余事务的修改都是可见的。

还有一条：一个 事务自己更新的数据对自己可见的。比如事务X修改了k的值，那么它在同一个事务中查询的时候是可以查询到自己的修改结果的。

因此所谓一致性视图“快照”的创建，其实就是创建了这样一个视图数组而已，视图数组是非常小的，因此我们可以实现秒级别的创建快照能力。

创建了视图数组，事务就知道了哪些数据版本是对自己可见的，哪些是不可见的，这样访问一条数据时，由于数据本身和undo log上带了transaction id，那么就可以找到自己可见的那个transation id对应的版本的数据。

另外通过上面的分析我们可以总结出修改是否可见的判断标准：

  1. 版本未提交，不可见；
  2. 版本已提交，但是是在视图创建后提交的，不可见；
  3. 版本已提交，而且是在视图创建前提交的，可见。

7.4 更新逻辑

我们再将本章一开始时的那个例子拿过来：

时间事务A事务B事务C
T1start transaction with consistent snapshot
T2 start transaction with consistent snapshot
T3 update t set k = k+1 where id = 1;
T4 update t set k = k+1 where id = 1;
select k from t where id = 1;
T5select k from t where id = 1;
commit;
T6 commit;

了解了一致性视图后，就不难知道事务A查询的结果是1，因为虽然事务B和事务C都修改了k的值，但它们都是在事务A创建一致性视图后修改的，均不可见。

那现在又有了新的问题，为什么事务B的查询结果是3，而不是2。根据前面讲的内容，事务B的一致性视图也是建立在事务C的修改之前，事务C的修改应该对事务B不可见，这样事务B应该是1+1等于2才对啊，为什么是3？这是因为更新的逻辑与读取是不同的。

当一条事务要去更新一条记录时，是不可能在历史版本上更新的，只会在当前最新的数据上更新。

事务与事务之间的隔离不应该影响最终数据的落地。就是说事务C先更新了数据，而后事务B也更新了同一份数据，以数据库的眼光来看这份数据的变动就是事务C的更新跟着事务B的更新，必须延续在一块而不能分开。 所以事务的更新必须是基于当前最新值来执行的，而读则是基于其视图，即可重复读的隔离，真的只是读层面的隔离。 在这个例子中，就是因为事务C的更新在前，事务B的更新必须延续事务C的结果，所以只能读取当前值再更新。而事务本身的更新是能被看到的，所以事务B再查询就只能是得到当前最新值。

这里我们就需要讲一个新的概念，当前读（current read）：事务更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

因此事务C在T3时刻更新k值为2后，T4时刻事务B获得当前读的值是2，然后将2更新为3，并将这条记录的row trx_id设为自己的事务id，那么在T4时刻事务B再执行查询语句的时候发现最新记录的row trx_id是自己的事务id，我们前面说过自己的更新对自己是可见的，因此查询回来的就是3。

但如果现在我们修改一下之前的例子：

时间事务A事务B事务C
T1start transaction with consistent snapshot
T2 start transaction with consistent snapshot
T3 start transaction with consistent snapshot
update t set k = k+1 where id = 1;
T4 update t set k = k+1 where id = 1;
select k from t where id = 1;
T5select k from t where id = 1;
commit;		 commit;
T6 commit;

我们将事务C的修改提交延迟到了T5时刻，现在再来想想会发生什么？

事务A会的执行不受影响，依然是会在T5时刻查询回来结果是1。事务C在T3时刻开启事务准备修改id=1的一行记录，在修改记录前必须要获取行记录的锁，因此T3时刻事务C获取行锁执行修改操作。事务B在T4时刻准备执行修改操作，同样需要获得行锁，但此时行锁已经被事务C持有，事务C要等到T5时刻才能提交释放锁。因此事务B会在T4时刻由于无法获得行锁而挂起等待，直到T5时刻以后获得行锁才能开始执行。

这样我们就将行锁与当前读和一致性读串了起来。

对于不同隔离级别的事务我们可以总结如下：

  • 读未提交

    这个级别下没有版本的概念，所有 更改都是一个版本，别人的更改你可以立马读到，因此读操作是没有版本和加锁的，但写操作应该依然是走行锁串行修改的。

  • 可重复读

    这个级别下引入了版本的概念，读的安全性是依靠版本实现的。读的时候依然没有锁，但是通过版本可以推算出自己应该读到的什么时候什么样子的数据。

    写的时候依然是行锁，但是写的数据基础是当前读，而非一致性视图下的版本数据。

  • 读已提交

    与可重复读的实现一样，区别就是可重复读在一个事务中只创建一次一致性视图，后续所有的读都是基于这个一致性视图，但读已提交是在一个事务中每有一次读都会重新创建一致性视图

  • 串行

    这个级别下读也上了锁，由于上锁自然也不需要版本了。读上的是读锁，修改上的是写锁，都是基于行级别的。读锁之间可以共享，并发的读，写锁是互斥的，只能串行修改。读的时候不能写，写的时候别人不能读。

最后修改：2022 年 12 月 18 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏