MySQL实战45讲-阅读笔记

---

SQL查询语句的执行

执行流程如下

具体拆解如下，

1. 连接器

• 长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。使用长连接可能会导致MySQL占用内存快速上涨，原因是执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放。解决方案有两个
  • 定期断开长连接
  • MySQL 5.7或更新版本可以执行mysql_reset_connection来重新初始化连接资源

2. 查询缓存

• 除非是静态表，否则不建议开启查询缓存。表只要有一次更新操作，表关联所有缓存都失效。MySQL 8.0及以上缓存功能已被废弃

3. 分析器：依次进行如下分析，不符合词法或语法则抛出异常

• ①词法分析：解析关键字和字段名
• ②语法分析：判断是否符合MySQL语法

4. 优化器：决定SQL的执行顺序
5. 执行器：表权限校验，调用查询引擎接口根据索引（如果有）查询数据

---

SQL更新语句的执行

执行流程和查询语句的流程一样，如下

redo log和binlog

除此以外，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）

redo log
- InnoDB引擎特有的日志，可以保证crash-safe。
- 物理日志，记录的是“在某个数据页上做了什么修改”。
- 循环写的，空间固定会用完。如下所示，write pos是当前记录的位置，一边写一边后移，checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos和checkpoint之间的是还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示redolog满了不能再执行新的更新，得停下来先擦掉一些记录写入binlog，把checkpoint推进一下。

binlog
- MySQL的Server层实现的，所有引擎都可以使用，用于归档。
- 逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1”。
- 可以追加写入的。binlog文件写到一定大小后会切换到下一个，不会覆盖以前的日志。

update语句的执行流程

update语句的执行流程如下，图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的。redo log的写入拆成了两个步骤：prepare和commit，也就是”两阶段提交”。

---

🔥事务的隔离级别

隔离性与隔离级别

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

级别	结果
读未提交	则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被A看到了。因此，V2、V3也都是2
读提交	则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以， V3的值也是2
可重复读	则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的
串行化	则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看， V1、V2值是1，V3的值是2

🔥事务隔离的实现

事务隔离具体是怎么实现的，这里以“可重复读”为例展开说明一下。

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

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

回滚日志不会一直保留，在不需要的时候才删除。什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的read-view的时候。所以不建议用长事务，长事务意味着系统里面会存在很老的事务视图，因此回滚日志会占用大量存储资源，还占用锁资源，也可能拖垮整个库。

---

深入浅出索引

索引的数据结构

哈希表：适用于只有等值查询的场景

有序数组：在等值查询和范围查询场景中的性能就都非常优秀。但是插入效率较低，只适用于静态存储引擎

搜索树：使用N叉搜索树

• 为什么不使用二叉搜索树：索引数据存储在磁盘上，二叉搜索树节点只存储一个数据，树比较高，一次查询需要更多次的数据块读取

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;

主键索引：也叫聚簇索引，叶子节点存的是整行数据
普通索引：也叫二级索引，非聚簇索引，叶子节点内容是主键的值

Q：基于主键索引和普通索引的查询有什么区别？
A：主键查询方式，则只需要搜索主键这棵B+树。普通索引查询方式，则需要先搜索普通索引树，得到叶节点上的主键值，再到主键索引树搜索一次。这个过程称为回表

索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。

• 如果插入一个更大的值，只需要在最后面插入一个新记录。
• 如果在中间插入一个值，处理比较麻烦，需要逻辑上挪动后面的数据，空出位置。
• 而更糟的情况是，如果数据所在的数据页已经满了，根据B+树的算法，需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂，插入性能因此会受到影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

  Q：哪些场景下应该使用自增主键，而哪些场景下不应该?
  A： 自增主键的插入数据模式，正符合递增插入的场景。每次插入一条新记录都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。其次，在考虑主键字段的选择时，由于普通索引的叶节点存储的是主键，因此主键越小普通索引占用的空间就更小。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

覆盖索引

在普通索引上，叶节点上存储了相关字段和主键的数据，如果查询的是这部分字段可以避免回表查询，提高查询效率，减少树的搜索次数，显著提升查询性能

最左前缀原则

B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

Q：在建立联合索引的时候，如何安排索引内的字段顺序？
A： 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。如果既有联合查询，又有基于a、b各自的查询呢？这时候需要同时维护类似(a,b)、(b) 这两个索引。此时，我们要考虑的原则就是空间，比如上有两个字段name、age，name字段是比age字段大的 ，那我就建议你创建一个（name,age)的联合索引和一个(age)的单字段索引。

索引下推

举例，比如表存在name、age的联合索引，在执行如下语句时，

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

• 如果没有索引下推，会回表依次查询记录的age、ismale是否符合要求
• 如果存在索引下推（MySQL5.6引入），会先根据索引上的数据对age进行过滤，再回表查询记录的ismale是否符合要求，这样的回表次数会更少

🔥锁机制

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

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。

如果库下的表都是InnoDB表，在使用逻辑备份工具mysqldump时可以使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

表级锁

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（metadata lock）。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

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

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

Q：为什么修改表结构操作可能出现问题？
A： 如果表正在被读，一些session因此获取了读锁，在进行修改表结构操作获取写锁的时候就会阻塞住，此时如果再发生其他申请读锁的session，这些session也会全部被阻塞住。如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session再请求的话，这个库的线程很快就会爆满。

Q：如何安全地给小表加字段？
A： 首先我们要解决长事务，事务不提交，就会一直占着MDL锁。在MySQL的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。如果要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，该怎么做呢？为alter table操作增加超时

🔥行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，这就会影响到业务并发度。InnoDB是支持行锁的，这也是MyISAM被InnoDB替代的重要原因之一。

🔥两阶段锁协议

在下面的操作序列中，事务B的update语句执行时会是什么现象呢？假设字段id是表t的主键。

结果取决于事务A在执行完两条update语句后，持有哪些锁，以及在什么时候释放。

实际上事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。因此事务A持有的两个记录的行锁，都是在commit的时候才释放的。

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

知道了这个设定，对我们使用事务有什么帮助呢？那就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。这样操作可以最大程度地减少事务之间的锁等待，提升并发度。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。如下图所示，事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。 事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。

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

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

在InnoDB中，innodb_lock_wait_timeout的默认值是50s，对于在线服务来说一般是无法接受的。但是不能把这个值设得很小，因为正常的锁等待也有可能被错误地释放。所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，但是每个新的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的，会消耗大量的CPU资源。

怎么解决由这种热点行更新，导致的死锁检测耗费大量CPU资源的问题呢？

• 如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是大部分业务设计时不会把死锁当做一个严重错误，遇到异常业务重试就完了，如果锁等待超时的话对业务来说是有损的。
• 控制并发度，在数据库服务端对于相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。如果数据库层面没有团队支持，可以考虑通过将一行改成逻辑上的多行来减少锁冲突。

🔥事务隔离级别的原理

在第3章中曾经提到过，如果是可重复读隔离级别，事务T启动的时候会创建一个视图read-view，之后事务T执行期间，即使有其他事务修改了数据，事务T看到的仍然跟在启动时看到的一样。

但是，在第4章中，关于行锁的描述时又提到，一个事务要更新一行，如果刚好有另外一个事务拥有这一行的行锁，它又会被锁住，进入等待状态。问题是，既然进入了等待状态，那么等到这个事务自己获取到行锁要更新数据的时候，它读到的值又是什么呢？

以如下这个表为例，

mysql> 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 这个命令。在本笔记中，如果没有特别说明，都是默认autocommit=1。

令人惊讶地，结果是事务B查到的k的值是3，而事务A查到的k的值是1，具体原因会在后面介绍。

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

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

🔥MVCC是如何提供“快照”的

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是怎么定义快照的？按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见

在实现上， 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或者3(a)的情况，而对它来说，这些新的数据版本是不存在的，所以这个事务的快照，就是“静态”的了。

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

继续看一下图1中的三个事务，分析下事务A的语句返回的结果，为什么是k=1。

这里，我们不妨做如下假设：

1. 事务A开始前，系统里面只有一个活跃事务ID是99；
2. 事务A、B、C的版本号分别是100、101、102，且当前系统里只有这四个事务；
3. 三个事务开始前，(1,1)这一行数据的row trx_id是90。

这样，事务A的视图数组就是[99,100], 事务B的视图数组是[99,100,101], 事务C的视图数组是[99,100,101,102]。

为了简化分析，我先把其他干扰语句去掉，只画出跟事务A查询逻辑有关的操作：

从图中可以看到，第一个有效更新是事务C，把数据从(1,1)改成了(1,2)。这时候，这个数据的最新版本的row trx_id是102，而90这个版本已经成为了历史版本。

第二个有效更新是事务B，把数据从(1,2)改成了(1,3)。这时候，这个数据的最新版本（即row trx_id）是101，而102又成为了历史版本。

你可能注意到了，在事务A查询的时候，其实事务B还没有提交，但是它生成的(1,3)这个版本已经变成当前版本了。但这个版本对事务A必须是不可见的，否则就变成脏读了。

好，现在事务A要来读数据了，它的视图数组是[99,100]。当然了，读数据都是从当前版本读起的。所以，事务A查询语句的读数据流程是这样的：

1. 找到(1,3)的时候，判断出row trx_id=101，比高水位大，处于红色区域，不可见；
2. 接着，找到上一个历史版本，一看row trx_id=102，比高水位大，处于红色区域，不可见；
3. 再往前找，终于找到了(1,1)，它的row trx_id=90，比低水位小，处于绿色区域，可见。

这样执行下来，虽然期间这一行数据被修改过，但是事务A不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。这个判断规则是从代码逻辑直接转译过来的，但是正如你所见，用于人肉分析可见性很麻烦。

所以翻译一下。一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

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

对于可重复读的隔离级别，记住这个规则就可以来分析了。

🔥更新逻辑

事务B的update语句，如果按照一致性读，好像结果不对？如下图，事务B的视图数组是先生成的，之后事务C才提交，不是应该看不见(1,2)吗，怎么能算出(1,3)来的？

是的，如果事务B在更新之前查询一次数据，这个查询返回的k的值确实是1。但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务C的更新就丢失了。因此，事务B此时的set k=k+1是在（1,2）的基础上进行的操作。

所以，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

因此，在更新的时候，当前读拿到的数据是(1,2)，更新后生成了新版本的数据(1,3)，这个新版本的row trx_id是101。

其实，除了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;

再进一步，假设事务C不是马上提交的，而是变成了下面的事务C’（如下右图所示），会怎么样呢？

事务C’的不同是，更新后并没有马上提交，在它提交前，事务B的更新语句先发起了。前面说过了，虽然事务C’还没提交，但是(1,2)这个版本也已经生成了，并且是当前的最新版本。那么，事务B的更新语句会怎么处理呢？

这时候，我们在上一篇文章中提到的“两阶段锁协议”就要上场了。事务C’没提交，也就是说(1,2)这个版本上的写锁还没释放。而事务B是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务C’释放这个锁，才能继续它的当前读。如下图所示

综上所述，事务的可重复读的能力是怎么实现的？

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

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