MySQL基础 | Michael Lu’s Blog

type

status

date

slug

summary

连接器

第一步，就是连接到数据库，接待的其实就是连接器。

mysql -h localhost -P 3306 -u root -p

输入完这个命令之后，就需要输入密码。

连接命令中的mysql是一个客户端工具，用来和服务端建立连接的。密码不对就直接access denied，客户端结束执行；正确的话，连接器查到权限，判断逻辑。

连接完成后，如果后续没有动作，连接是处于空闲状态的。

客户端默认8小时没有动静，会自动断开由wait_timeout控制。收到一个lost connection之后要继续就要重新连接。

数据库里面

长连接：连接成功后，如果客户端持续请求，则用一直使用同一个连接

短连接：每次执行完很少的几次查询就断开来，下次查询再建一个新的

长连接的问题：

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

如何解决：

1.定期断开或者执行一个占用内存的大查询后，断开连接，之后要查询再重新连

查询缓存

连接建立了之后，就可以执行select语句，就会来到查询缓存的步骤。

之前执行过的语句和结果会以key-value形式缓存在内存里，key是查询语句，value是结果，如果查询能在缓存找到，value直接给客户端。

为什么查询缓存不好？

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

分析器

如果没有命中缓存，就要开始执行语句了，mysql需要先对语句做解析。

分析器先做词法分析，要知道字符串代表什么，是什么。

1.MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串"T"识别成"表名 T"，把字符串"ID"识别成"列 ID"。

2.做完了这些识别以后，就要做"语法分析"。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

优化器

到这里mysql就知道你要做什么，但开始执行前，也要先经过优化器处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的 join：

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。

也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了

执行器

开始执行的时候，先判断有没有执行的权限，没有就会报错。

mysql> select * from T where ID=10;

有权限，就打开表继续执行

比如

1.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；

2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

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

比如：

mysql> create table T(ID int primary key, c int);

mysql> update T set c=c+1 where ID=2;

更新的流程也是走一遍这个流程的。

更新会把缓存都清掉；

然后分析器通过词法和语法分析知道这是update；

优化器决定用ID这个索引；

和查询流程不同的是，更新涉及到两个日志模块，redo log 和 binlog

redo log

先想象有这样一个场景：

如果有人要赊账或者还账的话，掌柜一般有两种做法：

一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；另一种做法是先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。

在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。首先，你得找到这个人的赊账总额那条记录。

密密麻麻几十页，掌柜要找到那个名字，可能还得带上老花镜慢慢找，找到之后再拿出算盘计算，最后再将结果写回到账本上。这整个过程想想都麻烦。相比之下，还是先在粉板上记一下方便。你想想，如果掌柜没有粉板的帮助，每次记账都得翻账本，效率是不是低得让人难以忍受？

同样，在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率

粉板和账本配合的过程就是mysql的WAL技术, write-ahead logging。就是先写日志，再写磁盘。

具体来说，就是有一条记录要更新的时候，innoDB会先把记录写到redo log（粉板），并且更新内存，这个时候就算完成了。 InnoDB会在适当的时候，将这个操作记录更新到硬盘里。

然后假设某天赊账特别多，粉版写满了，那怎么办？掌柜只能暂停一下，把粉板上的一部分记录更新到账本。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。

如果 write pos 追到checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

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

要理解 crash-safe 这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

binlog

MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的粉板 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

为什么有两份日志？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力

区别：

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

然后再回到刚刚的例子：

mysql> update T set c=c+1 where ID=2;

浅色框表示是在 InnoDB 内部执行，深色框执行器

1.执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2.执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3.引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4.执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

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

最后这里：将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

为什么需要两阶段提交？

用反证法：如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

mysql> update T set c=c+1 where ID=2;

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

1.先写redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。(redo log是粉板肯定有物理的数据)

因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。（相当于主库c=1, 从库c=0）

（执行完这个c 得从0+1=1） binlog没了，就没有这个逻辑的更新语句了。

2.先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。（主库c=0, 从库c=1，不一致）

事务隔离

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

用例子来理解：

只有T表，一列，其中一行的value为1

mysql> create table T(c int) engine=InnoDB; insert into T(c) values(1);

四种隔离级别：

1.如果隔离级别是 read uncommitted,那么V1就是2.虽然B还没有提交事务，但结果已经被A看到了。所以V2,V3都是2.

2.如果隔离级别是读已提交，那么V1是1,V2是2事务B的更新要在提交后才能被A看到，所以V3是2.

3.如果是可重复读，则V1,V2是1，v3是2.v2之所是1，因为要遵循事务在执行期间看到的数据前后必须是一致的。（A事务）

4.若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

Oracle 数据库的默认隔离级别其实就是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交”。

mysql> show variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

事务隔离的实现

这里以可重复读来看

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

即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

为什么不使用长事务：

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

举个例子：

比如，在某个时刻（今天上午9:00）开启了一个事务A（对于可重复读隔离级别，此时一个视图read-view A也创建了），这是一个很长的事务……

事务A在今天上午9:20的时候，查询了一个记录R1的一个字段f1的值为1……

今天上午9:25的时候，一个事务B（随之而来的read-view B）也被开启了，它更新了R1.f1的值为2（同时也创建了一个由2到1的回滚日志），这是一个短事务，事务随后就被commit了。

今天上午9:30的时候，一个事务C（随之而来的read-view C）也被开启了，它更新了R1.f1的值为3（同时也创建了一个由3到2的回滚日志），这是一个短事务，事务随后就被commit了。

……

到了下午3:00了，长事务A还没有commit，为了保证事务在执行期间看到的数据在前后必须是一致的，那些老的事务视图、回滚日志就必须存在了，这就占用了大量的存储空间。

源于此，我们应该尽量不要使用长事务。

索引

索引的常见模型

1.hashmap

User2 和 User4 根据身份证号算出来的值都是 N，但没关系，后面还跟了一个链表。假设，这时候你要查 ID_card_n2 对应的名字是什么，处理步骤就是：首先，将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2。

图中四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。

如果你现在要找身份证号在[ID_card_X, ID_card_Y]这个区间的所有用户，就必须全部扫描一遍了。所以，哈希表这种结构适用于只有等值查询的场景

不适用场景

哈希索引不适用于以下场景：

范围查询：哈希索引无法直接支持范围查询（如 >、<、BETWEEN 等），因为哈希值不具备有序性。

排序：哈希索引无法直接支持排序操作，因为哈希值是无序的。

模糊查询：哈希索引无法支持模糊查询（如 LIKE），因为哈希值是基于精确值计算的。

有序数组

这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候如果你要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O(log(N))。

同时很显然，这个索引结构支持范围查询。你要查身份证号在[ID_card_X, ID_card_Y]区间的 User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大于 ID_card_X 的第一个 User），然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证号，退出循环。

但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎

二叉搜索树

bst:

左子树小于父节点，右子树大于父节点

UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))。

为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。

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

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

InnoDB的索引模型

InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

非叶子节点：存储索引键值和指向子节点的指针。

叶子节点：存储表中所有列的完整数据行，按照主键的顺序排列。

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

假设有这个表

R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。

在 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+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程

索引维护

分析一下哪些场景下应该使用自增主键，而哪些场景下不应该。

自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。除了考虑性能外，我们还可以从存储空间的角度来看。

假设我们有两个选择：

自增主键：使用 INT 类型，占用 4 字节。

身份证号主键：使用 VARCHAR(18) 类型，占用 18 字节（假设身份证号是18位）。

如果使用身份证号做主键，每个二级索引的叶子节点需要存储主键值，因此每个叶子节点会多占用 18 字节。而如果使用自增主键，每个叶子节点只占用 4 字节

有没有什么场景适合用业务字段直接做主键而不用自增主键呢的呢？还是有的。比如，有些业务的场景需求是这样的：只有一个索引；该索引必须是唯一索引。你一定看出来了，这就是典型的 KV 场景。

比如：

开发一个在线商城的购物车系统。每个用户都有一个购物车，购物车中存储了用户选择的商品及其数量。

CREATE TABLE user_carts ( user_id VARCHAR(50) PRIMARY KEY, -- 业务字段做主键 cart_items JSON );

优势分析

简单性

单一索引：表中只有一个索引，即主键索引 user_id。

唯一性：每个 user_id 都是唯一的，确保每个用户只有一个购物车。

高效查询

快速查找：由于 user_id 是主键，查询某个用户的购物车信息非常高效，时间复杂度为 O(1)。

无需额外索引：不需要为 cart_items 创建额外的索引，简化了表结构和查询逻辑。

灵活性

动态数据结构：cart_items 存储为 JSON 格式，可以灵活地存储不同用户的购物车内容，无需预定义字段。

易于扩展：如果需要添加新的购物车属性（如优惠券信息），可以直接在 JSON 中添加，无需修改表结构。

如果我执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

假设数据是这样的：

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

k 索引树上找到 k=3 的记录，取得 ID = 300；

再到 ID 索引树查到 ID=300 对应的 R3；

在 k 索引树取下一个值 k=5，取得 ID=500；

再回到 ID 索引树查到 ID=500 对应的 R4；

在 k 索引树取下一个值 k=6，不满足条件，循环结束。在这个过程中，回到主键索引树搜索的过程，我们称为回表。

可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引

在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，R3~R5（对应的索引 k 上的记录项），但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。

最左前缀原则

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

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

单字段查询的问题：

然而，对于仅根据 id_card 进行的查询 WHERE id_card = 'some_id_card'，联合索引 (id_card, name) 是无法被使用的。因为联合索引的结构是先按 id_card 排序，再按 name 排序，数据库无法直接利用 (id_card, name) 索引来加速仅包含 id_card 的查询

所以应该维护两个索引

索引下推

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

现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

可以看这个比较：

无索引下推执行

有索引下推执行

在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

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

全局锁

MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

所以为什么备份需要加锁？

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

先备份余额表，用户购买，然后备份用户课程表

如果用可重复读呢？

是可以的，但是如果用的是MYISAM不支持事务的引擎，那就需要加锁了。

表级锁

表锁的语法是 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（metadata lock)。

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

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

session a 和 session b都可以拿到读锁，

c在等（MDL 写锁：MDL 写锁是排他的，一旦一个会话持有 MDL 写锁，其他会话无论是读锁还是写锁都会被阻塞。）

然后d也拿不到

在 MySQL 中，MDL 锁的优先级如下：

MDL 写锁 的优先级高于 MDL 读锁。

一旦有会话在等待 MDL 写锁，所有后续的 MDL 读锁请求都会被阻塞，直到 MDL 写锁请求被满足。

阻塞链

在这个例子中，Session C 被阻塞是因为它需要获取 MDL 写锁，而 Session A 和 Session B 的 MDL 读锁还没有释放。由于 Session C 被阻塞，所有后续的 MDL 读锁请求（如 Session D）也会被阻塞，因为它们无法获取 MDL 读锁

行锁

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

两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放！

举例子：

假设你有一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。这个业务需要完成以下操作：

从顾客 A 账户余额中扣除电影票价。

给影院 B 的账户余额增加这张电影票价。

记录一条交易日志。

为了保证交易的原子性，这三个操作需要放在一个事务中。

事务设计

为了最大程度地减少锁等待时间，你可以将更新影院账户余额的操作（语句 2）放在事务的最后。例如，事务的顺序可以是：

记录交易日志（语句 3）。

从顾客 A 账户余额中扣除电影票价（语句 1）。

给影院 B 的账户余额增加这张电影票价（语句 2）。

因为可能有客户c要往影院B买门票，所以这个是最影响并发度的，就放在最后面。

死锁

事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

第一种就不太合理：太长了，线上服务等待太久；太短了，可能只是锁简单等待，所以一般是第二种。

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

视图

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。

另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

快照在mvcc里如何工作？

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

注意，这个快照是基于整库的。这时，你会说这看上去不太现实啊。如果一个库有 100G，那么我启动一个事务，MySQL 就要拷贝 100G 的数据出来，这个过程得多慢啊。

可是，我平时的事务执行起来很快啊。实际上，我们并不需要拷贝出这 100G 的数据

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

每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。

同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

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

语句更新会生成 undo log（回滚日志）吗？那么，undo log 在哪呢？实际上，图 2 中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

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

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

如果落在黄色部分，那就包括两种情况

a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；

b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

继续用上面的例子解释就是说：

当一个事务的低水位（low watermark）是18时意味着：

这个事务只能看到事务ID小于18的数据版本

在图中，V4的事务ID是25 > 18，所以不可见

系统需要往前找到第一个事务ID小于18的版本

回溯过程：

从V4开始看（事务ID=25），因为25 > 18，所以不可见

通过U3向前找到V3（事务ID=17）

因为17 < 18，所以V3是对该事务可见的版本

因此这个事务看到的值是k=11

这就像时间旅行一样：低水位18的事务只能看到发生在"时间点18"之前的变化，所以它看到的是事务17所做的更新（k=11）。

分析下事务 A 的语句返回的结果，为什么是 k=1。

做如下假设：事务 A 开始前，系统里面只有一个活跃事务 ID 是 99；

事务 A、B、C 的版本号分别是 100、101、102，且当前系统里只有这四个事务；

三个事务开始前，(1,1）这一行数据的 row trx_id 是 90。这样，事务 A 的视图数组就是[99,100], 事务 B 的视图数组是[99,100,101], 事务 C 的视图数组[99,100,101,102]。

事务 A 查询语句的读数据流程是这样的：

找到 (1,3) 的时候，判断出 row trx_id=101，比高水位大，处于红色区域，不可见；

接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见；

再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，

有三种情况：

版本未提交，不可见；

版本已提交，但是是在视图创建后提交的，不可见；

版本已提交，而且是在视图创建前提交的，可见。

现在，我们用这个规则来判断图 4 中的查询结果，事务 A 的查询语句的视图数组是在事务 A 启动的时候生成的，这时候：(1,3) 还没提交，属于情况 1，不可见；

(1,2) 虽然提交了，但是是在视图数组创建之后提交的，属于情况 2，不可见；

(1,1) 是在视图数组创建之前提交的，可见。

更新逻辑

如果事务 B 在更新之前查询一次数据，这个查询返回的 k 的值确实是 1。但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务 C 的更新就丢失了。

因此，事务 B 此时的 set k=k+1 是在（1,2）的基础上进行的操作。所以，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。因此，在更新的时候，当前读拿到的数据是 (1,2)，更新后生成了新版本的数据 (1,3)，这个新版本的 row trx_id 是 101。

所以，在执行事务 B 查询语句的时候，一看自己的版本号是 101，最新数据的版本号也是 101，是自己的更新，可以直接使用，所以查询得到的 k 的值是 3。

普通查询是在看历史快照（一致性读）

更新操作必须在"实时"状态下进行（当前读）

如果变成这个，那么：

在这个情况下的事件顺序是：

事务C'(102)先执行update，生成新版本(1,2)

但C'还未提交，所以仍然持有这个版本的写锁

事务B(101)要执行update，需要先做当前读

关键点分析：

版本生成：

虽然事务C'还未提交

但新版本(1,2)已经生成

这个版本是当前最新版本

事务B的当前读特性：

必须读取最新版本（这里是1,2）

当前读需要加锁（获取共享锁或排他锁）

不能读取历史版本(1,1)

两阶段锁协议的作用：

事务C'持有(1,2)版本的写锁

在C'提交或回滚前不会释放这个锁

事务B需要等待这个锁释放

显示为"lock wait"状态

如果是read committed的隔离级别：

所以说

每次查询都会创建新的视图数组

事务A在执行这个查询时才创建视图数组

会看到在查询时已经提交的事务所做的修改

所以对于事务A的这次查询：

创建视图数组时，版本(1,2)已经提交（事务C已提交）

版本(1,3)还未提交（事务B未提交）

因此能看到版本(1,2)，看不到版本(1,3)

所以a：k=2. b:k=3

主要区别：

可重复读（RR）：

视图数组在事务开始时创建一次

后续所有查询复用这个视图数组

保证了事务内多次查询结果一致

读已提交（RC）：

每次查询都创建新的视图数组

能看到查询时刻之前已提交的修改

同一事务内多次查询可能看到不同结果

这就像是：

RR：事务开始时拍一张快照，始终看这张快照

RC：每次查询都重新拍快照，能看到最新已提交的变化