基础

基础架构：一条SQL查询语句是如何执行的

下面是 MySQL 基础架构示意图

总体上，MySQL 可以分为 Server 层和存储引擎层两部分

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，包含了 MySQL 绝大部分核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有的跨存储引擎的功能都在这一层进行实现，比如存储过程、触发器、视图等

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。从 MySQL 5.5.5 开始 InnoDB 称为了 MySQL 的默认存储引擎

可以在使用create table语句中使用engine=memory，来指定使用内存引擎创建表

不同的存储引擎共用一个 Server 层

连接器

第一步，先连接到这个数据库上，这时候需要使用连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接，连接命令为：

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

连接命令是用来跟服务端建立连接的。在完成经典的 TCP 握手之后，连接器就要开始用输入的用户名跟密码认证你的身份

• 如果用户名或者密码不对，就会收到一个Access denied for user的错误，然后客户端程序结束
• 如果用户名密码认证通过，连接器会到权限表里面查出当前账号拥有的权限，之后这个连接里面的权限判断逻辑，都依赖于此时读到的权限

这就意味着，一个用户成功建立连接后，即使管理员对这个用户的权限做了修改，也不会影响已经存在连接的权限。只有新的连接才会使用新的权限设置

连接完成后，如果没有后续动作，这个连接就处于空闲状态，使用show processlist命令查看

客户端如何太长时间没有操作，连接器会自动断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时

如果连接断开后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query，如果要继续，就需要重连，然后再执行请求

数据库里面有一种长连接，指的是连接成功后，客户端如果有连续的请求，则一直使用同一个连接。短连接是指每次执行完很少的几次查询后就断开连接，下次查询再重新建立一个

建立连接的过程比较复杂，建议平时开发中尽量减少建立连接的操作，也就是尽量使用长连接

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

解决措施：

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

查询缓存

注意：MySQL 8.0 开始，查询缓存功能已经彻底删除了，因为查询缓存弊大于利

MySQL 拿到一个查询请求，会先到查询缓存中查看，之前是否执行过这条语句。之前执行过的语句及其查询结果会以 key-value 的形式，直接缓存在内存里。key 是查询的语句，value 是查询的结果。如果能在查询缓存中找到 key，那么 value 会直接返回给客户端

如果语句不在查询缓存中，会继续走后面的流程

为什么查询缓存弊大于利？

查询缓存失效的非常频繁，只要对一个表有更新，这个表上面的所有查询缓存都会被清空。这就导致缓存的命中率非常低，除非是一张静态配置表 MySQL 提供了“按需使用”的方式来使用查询缓存。将参数query_cache_type设置成 DEMAND，这样默认的 SQL 语句不会使用查询缓存，如果有语句需要使用的时候，可以用SQL_CACHE显式指定

select SQL_CACHE * from T where ID=10;

分析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道要做什么，因此需要对 SQL 语句做解析

分析器先会做“词法分析”。输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么 MySQL 从输入的select这个关键字识别出来，这是一个查询语句。它也要把字符串T识别成表名 T，把字符串ID识别成列 ID 做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断输入的这个 SQL 语句是否满足 MySQL 语法 如果语句不对，就会收到You have an error in your SQL syntax的错误提醒，比如下面这个语句 select 少打了开头的字母“s”

mysql> elect * from t where ID=1;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

优化器

分析器让 MySQL 知道了你要做什么，然后需要经过优化器处理

优化器是表中有多个索引的时候，决定使用哪个索引，或者在一个语句有多表连接（join）的时候，决定各个表的连接顺序。比如下面语句

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

可以先从表 t1 中取值，也可以从 t2 中取值，两种方式的逻辑效果是一样的，但是执行效率会不同，而优化器的作用就是决定选择哪一种方案 优化器阶段结束后，就会进入执行器阶段

执行器

执行器开始执行语句

但是在执行之前，要先判断一下当前连接有没有对表执行查询的权限，如果没有，就会返回没有权限的错误（如果查询的时候命中缓存了，在查询缓存返回结果时，也会做权限校验）

如果有权限，就打开表继续执行，打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

比如这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了

对于有索引的表，第一次调取“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的 在数据库的慢查询日志中能看到 rows_examined 字段，表示这个语句在执行过程中扫描了多少行，这个值是在执行器每次调用引擎获取数据行的时候累加的

在有些场景下，执行器调用一次，在引擎内部扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的

日志系统：一条SQL更新语句是如何执行的

更新流程除了查询流程中的内容外还涉及到了两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）

redo log

在 MySQL 中，如果每一次更新操作都写入磁盘，在磁盘中找到对应的数据然后更新，整个过程的 IO 成本、查找成本是非常高的

为了解决这个问题 MySQL 引入了 WAL 技术，全称是 Write-Ahead Logging，关键点是先写日志，后写磁盘

当有一条记录需要更新时，InnoDB 引擎会先把记录写到 redo log 中，并更新内存，这个时候更新就算完成了；InnoDB 引擎在合适的时候，讲这个操作记录更新到磁盘中，这个更新一般是系统比较空闲时做

InnoDB 的 redo log 是有固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么 redo log 总共可以记录 4GB 的操作 注意：redo log 是循环写的

write pos 是当前记录的位置，一边写一边向后移，写到第 3 号文件末尾后就回到 0 号文件开头

checkpoint 是要擦除的位置，也是往后推移循环往复的，擦除记录之前要把记录更新到数据文件

如果 wirte pos 追上 checkpoint，表示 redo log 写满了，这时候不能再执行新的更新操作，必须先停下来擦掉一些记录，把 checkpoint 推进一些

有了 redo log，InnoDB 可以保证即使数据库发生异常重启，之前提交的记录不丢失，这个能力称为：crash-safe

binlog

MySQL 整体来看其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事；还有一块是引擎层，负责存储相关的事宜 redo log 是 InnoDB 特有的日志，binlog 是 Server 层的日志

最开始时候 MySQL 没有 InnoDB 引擎，默认是 MyISAM，InnoDB 是以插件的方式引入 MySQL的 MyISAM 引擎没有 crash-safe 能力，binlog 日志只能用于归档，所以 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 文件写到一定大小后会切换到下一个写，并不会覆盖之前的日志

执行 update 语句时的内部流程：

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎用树搜索找到这一行。如果 ID=2 这一行的数据在内存中，就直接返回给执行器；否则，需要先从磁盘中读取到内存，然后返回
2. 执行器拿到引擎返回的行数据，把这个值加上 1，得到新的一行数据，再调用引擎接口写入这行新数据
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 中，此时 redo log 处于 prepare 状态，然后告知执行器执行完成了，随时可以提交事务
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘
5. 执行器调用引擎提供的事务提交接口，引擎把刚写入的 redo log 改成提交（commit）状态，更新完成

执行步骤如下：

最后三步是经典的”两阶段提交

两阶段提交

两阶段提交是为了让两份日志之间逻辑一致

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。看看这两种方式会有什么问题

仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

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，与原库的值不同

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致

事务隔离：为什么你改了我还看不见？

事务就是要保证一组数据库操作，要么全部成功，要么全部失败

MySQL 中，事务是在存储引擎层实现的，MySQL 的原生引擎 MySIAM 不支持事务

隔离性和隔离级别

当数据库上有多个事务同时执行时，就可能出现脏读、不可重复读、幻读，为了解决这些问题，就出现了”隔离级别“的概念

隔离的越彻底，效率就会越慢。很多时候都需要在二者之间找一个平衡点

SQL 标准的隔离级别包括：

• 读未提交（read uncommitted）：一个事务还没提交时，它做的变更能被其他事务看到
• 读提交（read committed）：一个事务提交后，它的变更才会被其他事务看到
• 可重复的（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
• 可串行化（serializable）：对于同一行记录，”写“会加”写锁“，”读“会加”读锁“。当出现读写锁冲突时，后访问的事务必须等前一个事务执行完成，才能继续执行

在实现上，数据库会创建一个视图，访问的时候以视图的逻辑结果为准

在可重复读隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都使用这个视图

在读未提交隔离级别下，直接返回记录上的最新值，没有视图概念

在串行化隔离级别下，直接用加锁的方式来避免并行访问

事务隔离的实现

这里着重展开说明可重复读

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作，记录上的最新值，通过回滚操作，都可以得到前一个状态的值 假设一个值从 1 被顺序设置成了 2、3、4，在回滚日志里就会有如下记录

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view

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

回滚日志什么时候删除呢？在不需要的时候删除。系统会判断，当没有事务需要用到这些回滚日志的时候，回滚日志会被删除

为什么尽量不要使用长事务？

长事务意味着系统中会存在很老的事务视图，由于这些事务随时可能访问数据库中的任何数据，所以事务提交之前，数据库中它可能用到的回滚记录都必须保留，这会导致占用大量存储空间

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有 200GB 的库。最终只好为了清理回滚段，重建整个库

事务的启动方式

1. 显示启动事务语句，begin或start transaction。提交语句是commit，回滚语句是rollback
2. set autocommit=0，这个命令会把当前线程的自动提交关掉。意味着如果你执行一个 select 语句，这个事务就启动，而且不会自动提交。这个事务持续存在到你主动执行 commit 或 rollback 语句，或断开连接

有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务

有时候会纠结“多一次交互”的问题。对于一个需要频繁使用事务的业务，第二种方式每个事务在开始时都不需要主动执行一次 “begin”，减少了语句的交互次数。建议使用 commit work and chain 语法

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中

可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(), trx_started)) > 60;

深入浅出索引

索引的出现就是为了提高查询效率，就像书的目录一样

索引的常见模型

索引的常见模型有三种：哈希表、有序数据和搜索树

哈希表

是一种以键值对（key-value）形式存储数据的数据结构，只需要输入待查找的 key，就能立即找到对应的值

哈希的思路是：把值放在数组中，通过一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置

但是，在换算过程中，可能会出现多个 key 是同一值的情况，处理这种情况的一个方法就是，拉出一个链表

哈希这种结构只适用于等值查询的场景，不适用于范围查询

有序数组

有序数组在范围查询和等值查询场景中性能非常优秀，但是有序数组索引在更新数据时非常麻烦，在中间插入一个值需要挪动后面所有的值，所以有序数组索引只适用于静态存储引擎

二叉搜索树

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子

查询的时间复杂度是 O(log(N))，为了保持这棵树是平衡二叉树，更新的时间复杂度也是O(log(N))

树可以有二叉，也可以有多叉。二叉搜索树的搜索效率最高，但是实际上大多数的数据库存储并不使用二叉搜索树，因为索引不止存在于内存，还要写到磁盘上

一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间

为了让一个查询尽量少的读取磁盘，必须让查询过程访问尽可能少的数据块，所以要使用 N 叉树

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了

InnoDB的索引模型

在 InnoDB 引擎中，表都是按照主键顺序以索引的顺序存放的，这种存储方式称为索引组织表，InnoDB 引擎使用 B+ 索引模型，所以数据都是存储在 B+ 树中的每一个索引在 InnoDB 中对应一棵 B+ 树

现在有一张表

create table T(
  id int primary key, 
  k int not null, 
  name varchar(16),
  index (k)
)engine=InnoDB;

两棵树的示意图如下：

根据叶子节点的内容，索引类型分为主键索引和非主键索引

• 主键索引的叶子节点存的是整行的数据。在 InnoDB 中，主键索引也被称为聚簇索引（clustered index）
• 非主键索引的叶子节点内容是主键的值。在 InnoDB 中，非主键索引也被称为二级索引（secondary index）

基于主键索引和普通索引的查询有什么区别？

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树
• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表

也就是说，基于非主键索引的查询需要多扫描一颗索引树，因此，日常中应该尽量使用主键查询

索引维护

B+ 树为了维护索引有序性，在插入新值的时候会做必要的维护，以上图为例，如果插入新的行 ID 值为 700，只需要在 R5 后面插入一个新记录，如果插入的 ID 值为 400，需要逻辑上挪动后面的数据，空出位置

如果 R5 所在的数据页已经满了，根据 B+ 树的算法，需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂，分裂的时候性能会收到影响

除了性能，页分裂还影响数据页的利用率。原本放到一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%

当相邻的两个页由于删除了数据，利用率很低之后，会将数据页做合并

下面来讨论一个问题：

哪些场景下使用自增主键，哪些场景下不使用

自增主键是自增列上定义的主键，建表语句中这么定义：not null primary key auto_increment

插入新记录时可以不指定 ID 的值，系统会取当前 ID 最大值加 1 作为下一条记录的 ID 值，这样正好符合递增插入的场景，每次插入一条记录都是追加操作，不涉及到挪动其他记录，也就不会出现页分类

从存储空间的方面看：

假设表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节

主键的长度越小，普通索引的叶子节点越小，普通索引占用的空间就越小

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择

KV 场景适合用业务字段直接做主键

表的初始化语句如下：

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

索引如下：

若执行下面查询语句，需要执行几次树的搜索操作，会扫描多少行

select * from T where k between 3 and 5

执行流程如下：

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300
2. 再回到 ID 索引树查 ID=300 对应的 R3
3. 在 k 索引树取下一个值 k=5，取得 ID=500
4. 再回到 ID 索引树查到 ID=500 对应的 R4
5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束

在这个过程中，回到主键索引树搜索的过程，称为回表，这个查询过程中读了 k 索引树的 3 条记录，回表了两次

覆盖索引

如果执行的语句是

select ID from T where k between 3 and 5

这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经覆盖了我们的查询需求，称为覆盖索引

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

最左前缀法则

每种查询都设计一个索引未免太过浪费了，对于 B+ 树这种索引结构，可以使用最左前缀法则来定位记录

以下面（name，age）联合索引来举例

当查询的是姓名第一个字是“张”的人，SQL 语句为：

where name like '张 %'

这时，依旧能够使用上面的联合索引，查找第一个符合条件的记录是 ID 3，然后向后遍历，直到不满足条件为止

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

建立复合索引时的原则：

1. 在建立联合索引时应根据索引的复用能力，安排索引内的字段顺序，如果通过调整顺序可以少维护一个索引，那么这个顺序往往是需要优先考虑采用的
2. 第二个原则就是空间，比如上面这个表，name 字段比 age 字段大，所以建议创建一个(name, age)联合索引和一个 age 单字段索引

索引下推

通过最左前缀可以在索引中定位记录，那些不符合最左前缀的部分，会怎么样呢？

以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

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

索引下推流程如下：

InnoDB 在索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过，在这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次

全局锁和表锁

根据加锁的范围，MySQL 里面的锁可以大致分为全局锁、表级锁和行锁三类

全局锁

全局锁就是对整个数据库实例加锁，MySQL 提供了一个加全局读锁的方法，命令是：

Flush tables with read lock (FTWRL)

当需要让整个库出于只读状态时，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（增删改）、数据定义语句（创建表、修改表结构等）和更新类事务的提交语句

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

为什么备份数据库要加锁呢？

如果不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的

官方自带的逻辑备份工具是 mysqldump，当 mysqldump 使用参数-single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图，由于 MVCC 的支持，这个过程中数据是可以正常更新的

那为什么还需要 FTWRL 呢？一致性读是很好，但前提是引擎要支持这个隔离级别，对于 MyISAM 这种不支持事务引擎，如果备份过程中有更新，只能取到最新的数据，那么就破坏了备份的一致性

所以，single-transaction方法只适用于所有的表使用事务引擎的库，如果有的表使用了不支持事务的引擎，那么只能通过 FTWRL 方法来备份

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？

1. 在有些系统中，readonly 的值会被用来做其他的逻辑，比如用来判断一个库是主库还是备库，因此，修改 global 变量的方式影响很大
2. 在异常处理机制上有差异。如果执行 FTWRL 命令后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态，而将整个库设置为 readonly 后，如果客户端发生异常，则数据库会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高

表级锁

MySQL 中表级别的锁有两种，一种是表锁，一种是元数据锁（Meta Data Lock，MDL）

表锁的语法是

lock tables ... read/write

与 FTWRL 类似，可以使用 unlock tables 主动释放锁，也可以在客户端断开连接时自动释放

注意：lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象 例如： 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表

在没出现更细粒度的锁的时候，表锁是最常用的处理并发的方式，而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 来控制并发，毕竟锁住整个表的影响太大了

另一类表级的锁是 MDL。MDL 不需要显式使用，在访问一个表的时候会被自动加上

MDL 的作用是，保证读写的正确性

如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的

因此，MySQL 5.5 引入了 MDL，当对一个表做增删改查操作时，加 MDL 读锁，要对表做结构变更操作的时候，加 MDL 写锁

• 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
• 读写锁之间、写锁之间是互斥的，用来保证变更表结果的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

给一个小表加一个字段，导致整个库挂了

• 可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行
• 之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞
• 如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了
• 如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满
• 事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放

如何安全的给小表加字段

首先要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务

但考虑一下这个场景。如果要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而不得不加个字段，该怎么做呢？

这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程

行锁

行锁就是针对数据表中行记录的锁，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新

两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立即释放，而是等到事务结束时才释放，这个就是两阶段锁协议 如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

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

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

也就是说，要完成这个交易，需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，怎样安排这三个语句在事务中的顺序呢？

试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据

根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度

死锁和死锁监测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁 例如下面：

这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的

所以，正常情况下还是要采用第二种策略，即：主动死锁检测，而且innodb_deadlock_detect的默认值本身就是 on

主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的（每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁）

如果是上面说到的所有事务都要更新同一行的场景呢？

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，就会看到 CPU 利用率很高，但是每秒却执行不了几个事务

怎么解决这种热点行更新导致的性能问题？

• 如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身就有一定风险，因为业务设计的时候一般不会把死锁当作一个严重错误，毕竟出现死锁了就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能出现大量的超时，这是业务有损的
• 控制并发度。比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。在客户端做并发控制。但是，这个方法不太可行，因为客户端很多。一个应用，有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000

因此，这个并发控制要做在数据库服务端。如果有中间件，可以考虑在中间件实现；如果团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了

也可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗

这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理

事务到底是隔离还是不隔离的

下面有一个初始化表语句：

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);

下面有三个事务在这个表上执行：

begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果想要马上启动一个事务，可以使用start transaction with consistent snapshot这个命令

结果为：事务 B 查到的 k 的值是 3，而事务 A 查到的 k 的值是 1

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

• 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view...，查询方法与表一样
• 另一个是 InnoDB 在 实现 MVCC 时用到的一致性读视图，即consistent read view，用于支持 RC（Read Commit，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现

他没有物理结构，作用是定义事务执行期间用来定义“我能看到什么数据”

“快照”在MVCC里是怎么工作的

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”

注意，这个快照是基于整个库的

如果一个库有 100G，快照是怎么做到拷贝这么快的呢？实际上，并不需要拷贝出这 100G 的数据

快照是如何实现的

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

而每行数据也都是有多个版本的，每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务 ID，记为 row_trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它

也就是说，数据表中的一行记录，其实可能有多个版本（row），每个版本有自己的 row_trx_id

下面就是一个记录被多个事务连续更新后的状态

图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25

图中三个虚线箭头就是 undo log；而 V1，V2，V3 并不是物理上真是存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的

那么，InnoDB 是怎么定义那个“100G”的快照的呢？

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果，但是之后，这个事务执行期间，其他事务的更新对它不可见 因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”

在实现上，InnoDB 为每个事务构造了一个数组，用来保存这个事务启动的瞬间，当前正在“活跃”的所有事务 ID。“活跃”是指，启动了但还没提交 数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）

而数据版本的可见性规则，就是基于数据的 row_trx_id 和这个一致性视图的对比结果得到的

这个视图数组把所有的 row_trx_id 分成了几种不同的情况

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况 a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见 b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见

比如，对于图 2 中的数据来说，如果有一个事务，它的低水位是 18，那么当它访问这一行数据时，就会从 V4 通过 U3 计算出 V3，所以在它看来，这一行的值是 1

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力

更新逻辑

要去更新数据的时候，就不能再在历史版本上更新了，否则事务 C 的更新就丢失了。因此，事务 B 此时的 set k=k+1 是在（1,2）的基础上进行的操作

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）

除了 update 语句外，select 语句如果加锁，也是当前读

所以，如果把事务 A 的查询语句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以读到版本号是 101 的数据，返回的 k 的值是 3。下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）

select k from t where id=1 lock in share mode;
select k from t where id=1 for update;

事务的可重复读的能力是怎么实现的？

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待

而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

• 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图
• 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图