本文是我学习极客时间专栏《MySQL实战45讲》的学习笔记

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

查询语句执行流程

image

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

更新语句执行流程

更新流程也要走查询流程那一套，更新表数据时，会清除该表所有缓存，所以不建议开启查询缓存。
与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）。

redo log（重做日志）

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面。

binlog（归档日志）

这binlog和redo log有以下4点不同：

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

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

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

4.redo log是记录这个页 “做了什么改动”;Binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

更新语句记录日志流程图
mysql> update T set c=c+1 where ID=2;

image

关于 redo log 和 binlog 的持久化设置

innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

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

事务的隔离级别

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的，MySQL 原生的 MyISAM 引擎不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。

读已提交是指，一个事务提交之后，它做的变更才会被其他事务看到。

可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

例子：

image

读未提交：V1=2，V2=2，V3=2

读已提交：V1=1，V2=2，V3=2

可重复读：V1=1，V2=1，V3=2

串行化：V1=1，V2=1，V3=2

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

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

“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的；

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

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

04 05 | 深入浅出索引

索引

索引种类

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

所以说想获取到整行数据，根据主键索引查询可直接得到整行数据，而使用非主键索引需先检索到主键，然后根据主键进行回表得到整行数据。也就是说，基于非主键索引的查询需要多扫描一棵索引树。

覆盖索引

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

最左匹配原则（最左前缀原则）

1.简单说下什么是最左匹配原则
顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

2.最左匹配原则的原理
最左匹配原则都是针对联合索引来说的，所以我们有必要了解一下联合索引的原理。了解了联合索引，那么为什么会有最左匹配原则这种说法也就理解了。

我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。
例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的

image

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

索引下推

现在有 (name,age) 这个联合索引，执行以下语句
select * from tuser where name like '张%' and age=10 and ismale=1;
虽然在这个语句只能使用到name的索引，但是mysql 5.6 引入了索引下推优化， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

全局锁

对整个数据库实例加锁。

MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)

这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。

使用场景：全库逻辑备份。

风险：

1.如果在主库备份，在备份期间不能更新，业务停摆

2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟

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

一致性读是好，但是前提是引擎要支持这个隔离级别。

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

1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。

2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

MySQL里面表级锁有两种，一种是表锁，一种是元数据所(meta data lock,MDL)

表锁的语法是:lock tables ... read/write

可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

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

MDL的作用：保证读写的正确性。

在对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。

MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

07 | 行锁功过：怎么减少行锁对性能的影响？

行锁

两阶段锁

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

所以如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，减少持有锁的时间。

死锁和死锁检测

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

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

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

08 | 事务到底是隔离的还是不隔离的？

一致性视图

先来看个题目,可重复读隔离级别下,k的初始值为1,执行如下语句后,事务A和事务B查询到的k值分别是多少?

注:

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

2. InnoDB的autocommit=1的时候(默认为1),事务C未显示的使用begin/commit，表示这个 update 语句本身就是一个事务，语句完成的时候会自动提交.

   
   image

这组事务执行完后,事务A查询到的k=1,事务B查询到的k=3.是不是感到了疑惑？

在MySQL中有两种视图的概念:

一种是view,是虚拟表,现在一般都不会去使用view。

另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

判断规则：

1.版本未提交，不可见；

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

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

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

5.自己的更新总是可见的。

对于事务B来说，创建视图时，k=1，但是执行更新语句时，属于规则4当前读k=2，k值更新后为3，查询时属于规则5，所以查询语句的k=3；

对于事务A来说，事务C的修改属于情况2，不可见，事务B的修改属于情况1，不可见，A查询到的k值为创建视图时的k值1。

09 | 普通索引和唯一索引，应该怎么选择？

普通索引和唯一索引应该怎么选？

场景

假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，所以，你一定会考虑在 id_card 字段上建索引。由于身份证号字段比较大，且插入时可能无序，我不建议你把身份证号当做主键，那么现在你有两个选择，要么给 id_card 字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。现在我要问你的是，从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？

查询过程

1.对于普通索引来说，查找到满足条件的第一个记录后，会需要查找下一个记录，判断条件不满足然后停止。

2.对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。对于整型字段，一个数据页可以放近千个key，所以对于普通索引来说，需要查找下一个记录大概率上是在内存中进行的，我们计算平均性能差异时，可以认为这个操作成本可以忽略不计。

更新过程

为了说明普通索引和唯一索引对更新语句性能的影响这个问题，需要先了解一下 change buffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

那么，什么条件下可以使用 change buffer 呢？对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。所以必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

索引选择和实践

普通索引和唯一索引应该怎么选择？。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。

而在其他情况下，change buffer 都能提升更新性能。在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

20 | 幻读是什么，幻读有什么问题？

幻读是什么

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

这里，我需要对“幻读”做一个说明：

1.在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

2.幻读仅专指“新插入的行”。

因为新加的记录不受之前的行锁限制，所以即使把所有的记录都加上锁，还是阻止不了新插入的记录，这也是为什么“幻读”会被单独拿出来解决的原因。

如何解决幻读

引入间隙锁，间隙锁和行锁一起被称为next-key lock，间隙锁在可重复读隔离级别下才有效

21 | 为什么我只改一行的语句，锁这么多？

加锁规则，包含了两个“原则”、两个“优化”和一个“bug”

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间，如(5,10]。

原则 2：查找过程中访问到的对象会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。