【Mysql45讲】基础知识点

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

MySQL可以分为Server层和存储引擎层两部分。

• Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务
  功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在
  这一层实现，比如存储过程、触发器、视图等。
• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、
  Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了
  默认存储引擎。

连接器

主要是连接客户端，在每次登陆的时候更新你的登录权限

查询缓存

从缓存中查找，k-v对都是保存在连接器中。不建议使用这个方法，因为命中率很低。在8.0版本中直接删除了查询缓存

分析器

分析sql语句的语法是否正确，是否表存在对应的内容。

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

执行器

首先判断有无权限打开，然后调用引擎提供的接口去执行查询。

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

分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用ID这个索引。然后，执行器负责具体执行，找到这一行，然后更新。除此以外还有重要的概念是更新两个备份日志：redo log 和 binlog

日志的出现是为了防止每次都直接往磁盘写入会造成太大的IO压力。因此采用备忘的形式，一段时间存一次。WAL技术（Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。

redo log（重做日志）

redo log是Innodb引擎特有的，是物理存储日志，是循环写入的。在Innodb中，redo log的大小是固定的，有两个指针标识一个是write pos，一个是checkpos。标识了当前写的位置和未进行备份的位置，如果write追上（或者说套圈更好理解）了checkpos，系统就擦除部分内容。（也就是保存一段时间的记忆）

redo log的意义

redo log的背后其实是WAL（Write-ahead-log）。是一种将磁盘的随机写改为顺序写的操作。redo log落盘的时机一般有四个，首先是每个事务commit的时候，都会进行落盘。当redo log的使用达到了redo log buffer size的一半的时候也会进行落盘。其余的，系统每1s会后台执行一次持久化，以及数据库关闭时候也会落盘。

binlog（归档日志）

binlog是Sever层持有的，是逻辑存储日志，是追加写入的。写满以后会切换到下一个，不会覆盖。

更新流程中的两阶段提交

两阶段提交保证了事务的一致性。不论mysql什么时刻crash，最终是commit还是rollback完全取决于MySQL能不能判断出binlog和redolog在逻辑上是否达成了一致。只要逻辑上达成了一致就可以commit，否则只能rollback。也就是说我们就看是不是redo log和binlog一致了。

为什么两阶段提交好，这个思想的本质是什么

因为最大程度降低了网络危险期，本质是分布式理论中的XA事务（分布式事务）

1.准备阶段：事务协调者(事务管理器)给每个参与者(资源管理器)发送Prepare消息，每个参与者要么直接返回失败(如权限验证失败)，要么在本地执行事务，写本地的redo和undo日志，但不提交，到达一种“万事俱备，只欠东风”的状态。(关于每一个参与者在准备阶段具体做了什么目前我还没有参考到确切的资料，但是有一点非常确定：参与者在准备阶段完成了几乎所有正式提交的动作，有的材料上说是进行了“试探性的提交”，只保留了最后一步耗时非常短暂的正式提交操作给第二阶段执行。)

2.提交阶段：如果协调者收到了参与者的失败消息或者超时，直接给每个参与者发送回滚(Rollback)消息；否则，发送提交(Commit)消息；参与者根据协调者的指令执行提交或者回滚操作，释放所有事务处理过程中使用的锁资源。(注意:必须在最后阶段释放锁资源)

将提交分成两阶段进行的目的很明确，就是尽可能晚地提交事务，让事务在提交前尽可能地完成所有能完成的工作，这样，最后的提交阶段将是一个耗时极短的微小操作，这种操作在一个分布式系统中失败的概率是非常小的，也就是所谓的“网络通讯危险期”非常的短暂，这是两阶段提交确保分布式事务原子性的关键所在。（唯一理论上两阶段提交出现问题的情况是当协调者发出提交指令后当机并出现磁盘故障等永久性错误，导致事务不可追踪和恢复）

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

事务的隔离其实是mysql中很大的一个问题，因为还会进而设计到锁，MVCC，版本快照等问题。这里算是一个粗浅的入门。

事务的特点，隔离问题以及隔离级别

事务的四大特点ACID，原子性，一致性，隔离性，持久性。原子性是说事务要不全部完成，要不全部失败回滚。一致性是数据库从一个一致性状态转移到另外一个一致性状态，也就是说如果某个动作失败了数据库可以自动回滚。隔离性指，多个事务之间不会相互干扰。持久性指事务提交以后将被数据库永久保存再次读取不会发生改变。

mysql中的隔离级别可以分为，读未提交（read uncommitted）,读提交（read commited）、可重复读（repeatable read）和串行化（serializable ）。

mysql中容易出现的隔离问题包括，修改丢失，脏读（dirty read）、不可重复读（nonrepeatable read）、幻读（phantom read）。

• 读未提交是隔离性遭到了破坏，事务中还没提交的修改也会被其他正在进行的事务读取到，也就是脏读。
• 读提交是只能看到其他事务已经提交的修改，但是可能出现不可重复读的问题。也就是一次事务中多次查询得到的结果不一样。
• 可重复读 mysql的默认级别，每次看到在事务开始时候都是拿到了表的一张快照，可以重复查询。但是仍然存在幻读风险。
• 串行化 最安全，效率最低，通过加锁的方式实现了更高的安全性。

Innodb引擎在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻影读。

多版本控制（MVCC）会在读提交和可重复读中也被使用。数据库中表的每一行都记录了一个版本，select时只拿出早于自己的版本。

04.5 | 深入浅出索引

参考文献

为什么是B+树

• 哈希表，直接查找快速，但是不适用于范围查找。
• BST二叉搜索树，不适用于自增主键，会退变成链表。
• 红黑树是可以自动平衡的，但是对于自增问题还是无法保证右倾的趋势。效率不是很高
• AVL树的平衡是更为严格的，但是由于AVL和红黑树都是二叉的，效率也不太高。我们希望可以多叉树更好。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块

• B树 是一种多叉结构，并且平衡的。分叉越多，效率越高。 IO 读一个数据和读 100 个数据消耗的时间基本一致。其实满足了我们的要求。但是在范围查找时候依然是要依次进行。
• B+树 是对于B树的一个升级，区分开了普通节点和叶子节点。每一个普通字节点都会出现在叶字节点中。并且在叶子节点中用了链表进行连接，方便读取叶子节点中的数据。是我们的最佳选择

B 树和 B+树有什么不同呢？

第一，B 树一个节点里存的是数据，而 B+树存储的是索引，所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。

第二，B+树的叶子节点是数据阶段用了一个链表串联起来，便于范围查找。

通过 B 树和 B+树的对比我们看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率

Innodb中的聚集索引和辅助索引

Innodb中主索引是聚集索引，也就是索引和数据是在一起的，位于.ibd文件中；而辅助索引是单独的。主键索引中普通节点都是位置信息，而叶节点是数据。而辅助索引中，叶节点是主键索引中主键的key，需要再到主键索引中去读取相应的数据。

之所以设计成这样是为了节省空间。相比起非聚集索引的mylsam，查询辅助索引时候性能略差。

最后再总结一下什么时候需要给你的表里的字段加索引吧：

1. 较频繁的作为查询条件的字段应该创建索引；
2. 唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件；
3. 更新非常频繁的字段不适合创建索引。

联合索引与最左前缀原则

在mysql中是可以构建联合索引的，但是需要注意联合索引也是有先后顺序的，可以理解为在多叉树上的查找还是按照联合索引的第一个来，在后续的叶节点中则是可以参考后一个索引进行查找。

• 覆盖索引
  如果我们正好有一个a的辅助索引，主键是b，我们的目标是 select b from T where a between 3 and 5，这样的话我们其实可以直接借助辅助索引就可以完成全部查找。完全不需要回表。因为辅助索引的叶节点的内容就是a。这就称为索引覆盖。

• 最左前缀原则
  如果我们有一个（a,b）的联合索引。我们优先查找可以完成 select * from tuser where a like ‘张%’ and b=10 and ismale=1;的操作。因为辅助联合索引中是按照a进行排列的，然后是b。这符合我们的查找逻辑。

• 索引下推
  可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

06、7 | 全局锁，表锁与行锁

锁的类型

mysql中的锁，可以分为全局锁，表锁和行锁。部分引擎不支持行锁，但是Innodb是支持行锁。

全局锁一般用于备份，尤其是对于不支持事务的引擎。对于Innodb引擎，我们一般不进行使用，因为我们可以MVCC得到一个版本快照进行备份。

对于表锁是很多引擎的最细锁，可以分为普通表锁lock tables … read/write，和元数据锁MDL（metadata lock)。保证读写的正确性。MDL会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新。

行锁是Innodb的特色，也是Innodb流行的原因。在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释
放，而是要等到事务结束时才释放。解决不可重复读，别人无法事务执行期间进行修改。

三级封锁协议：1. 对于事务T写加X锁，事务完成释放（解决修改丢失） 2. 对于读加S锁，完成读立刻释放 （解决脏读） 3. 对于读加X锁，完成事务以后释放。（解决不可重复读）

两段锁协议： 是指所有的事务必须分两个阶段对数据项加锁和解锁。即事务分两个阶段，第一个阶段是获得封锁。事务可以获得任何数据项上的任何类型的锁，但是不能释放；第二阶段是释放封锁，事务可以释放任何数据项上的任何类型的锁，但不能申请。

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

Innodb中的行锁

首先行锁可以分为共享锁S（读锁）和排他锁X（写锁）。更细的层次上还可以分为两者意向锁。执行逻辑是，如果你希望执行行锁，那么首先需要对更大的级别，如表，页等加上意向锁。意向锁之间都是可以兼容的。然后再对具体的行加行锁。其实意向锁不会组织除全表扫描意外的请求。

一致性非锁定读和一致性锁定读

一致性非锁定读：是在不需要锁的前提下。利用快照完成查询。对于可重复读的模式下，快照是在事务发起阶段创建的。对于读提交，是在可以看到事务发起之后已提交的结果的。

一致性锁定读：是借助锁实现，其他的事务无法得到这一行的相关锁。

但是对于事务中的增删改业务，都是需要先读再改，这个读只能是借助行锁的一致性锁定读。如果其他事务正在使用，当前事务就会被阻塞。

锁的性能问题

同时我们不能忽视可能出现的锁问题。

死锁：是两个事务相互持有对方需要的锁，且都在等待彼此都不释放自己的所持有的锁。这种现象会消耗大量的系统资源。解决办法一般有两个，一个是设置一个死锁等待时间每隔，如50s，如果依然无法得到锁，就释放自己的锁，一会重试。另外就是死锁检测，发现死锁以后主动回滚死锁链条中的某个事务，让其他事务执行。当然死锁检测也是消耗资源的，因此我们需要合理的控制并发度。采取一些别的手段去预防死锁。（Hashmap中的多个count加和的思路）

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

当前读与快照读

在mysql中存在当前读与快照读的区别。对于简单的读取操作，是在事务开始的阶段就获得事务当前的快照，也就说只能看到事务请求之前已经提交的内容。查询也是可重复的。但是对于增删改操作，是在命令进行前获取当前的快照，进行更新。
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

同时在事务开始时候看无法看到正在活跃的事务提交的内容的。

如何生成的版本快照呢？

通过undo log实现的。在undo log中存在一些回退操作，可以使得数据库得到在对应的row trx_id的历史记录。这里row trx_id在可重复读中是事务发起时候的任务号。对于读提交就是在指令前已经提交的内容。

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

什么是幻读？幻读与不可重复读有什么区别？

幻读也是一种在mysql中会出现的奇特现象，具体的表现每次查询得到的结果会发生增加。尤其注意区分幻读和不可重复读之间的区别。不可重复读是的重点是修改，每次查询到的内容修改都是不一样的。但是对于幻读来说，重点在于增删，可能上次查询的行数只有一个，这次就出现了两个。

比如在读提交的页面中，我们A事务查询id=3 的内容，只查询出来一个，但是我们在事务B中可以再插入一个id=3的东西。这样再A事务再次查询的时候就会发现两个。出现了幻读。

幻读的情况与不可重复读现象的解决方案也不一样。因为对于后者，我们只需要加一个行锁，保证别的事务不进行修改即可。但是对于幻读的场景，可能开始时候不存在这个行，我们也就无法加锁。

什么是间隙锁？

间隙锁+行锁，称为next-key lock

Innodb引擎为了在可重复读场景下解决这个幻读问题，引入了一个新的锁，叫间隙锁。这个加锁的实体是一个区间范围。比如我们是select id = 3。而表中的数据还有1，3，5，7。间隙锁就是加在（$-\infty$, 1] (1, 3] (3, 5] (5, 7] (7, $\infty$]上。保证在区间内也是不可以被操作的。间隙锁之间不存在排他的，这个性质也可能导致死锁。

可重复读隔离下的间隙锁加锁条件

两个原则两个优化一个bug

原则1： 加锁的最小单位是nextkey-lock，前开后闭
原则2：只有访问到的对象才会进行加锁。（因此在普通索引上查询不会再主键索引上加间隙锁，如果是覆盖索引，不会加任何的锁）

优化1：对于等值查询，会向右遍历且遍历到第一个不满足的情况下时，nextkey-gap会退变为纯间隙锁。
优化2：对于等值查询，且给唯一索引加锁时候，nextkey-gap会退化为行锁

一个bug：对于唯一索引的范围查询，会向右遍历到第一个不满足的情况。

如果是limit的话，不会加最后一个间隙锁。