MySQL实战45讲总结

不论是先实践再搞清楚原理去解释，还是先明白原理再通过实践去验证，都不失为一种好的学习方法，因人而异。但是，怎么证明自己是不是真的把原理弄清楚了呢？答案是说出来、写出来。
如果有人请教你某个知识点，那真是太好了，一定要跟他讲明白。不要觉得这是在浪费时间。因为这样做，一来可以帮你验证自己确实搞懂了这个知识点；二来可以提升自己的技术表达能力，毕竟你终究要面临和这样的三类人讲清楚原理的情况，即：老板、晋升答辩的评委、新工作的面试官。

最近疫情很严重，封起来闲着没事看完了《MySQL实战45讲》，上面是书里最后一章提到的一句话。看完这本书对数据库的体系脉络又梳理了一遍，但总感觉空空的，很多东西心里默念的时候是清楚的，但讲出来总是磕磕盼盼，带着非常多口语，比如“你懂吧”、“那个这个”等，说到底还是没有转化为自己的东西，遂决定写这一篇博客，把每一讲的东西用自己的话复述一遍。

---

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

一条SQL语句，从客户端发出去的那一刻，它首先是和服务端serve层的连接器建立连接，也就是我们背的经典八股——TCP三次握手的过程。

在建立连接后，为了更快的得到查询结果，在内存中通常会有缓存，这里的缓存是key-vaule结构存在内存中的，key为sql的查询语句，value为查询的结果。如果查询的语句命中了查询缓存，则直接返回给客户端value的数据。

如果没有命中，则继续执行sql查询语句。服务端的serve层会对这一条语句进行解析、优化，随后操作引擎，比如InnoDB，查询结果。在InnoDB中，数据是按页为单位进行管理的，为了能够更快的查询，引擎层中也是有缓存的，通过LRU链表淘汰旧页面。

在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。处于 old 区域的数据页，每次被访问的时候要判断这个数据页在 LRU 链表中存在的时间是否超过了 1 秒，超过了就把它移动到链表头部；如果短于 1 秒，位置保持不变。

需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。这是因为频繁的查询会使缓存失效，只能说食之无味，弃之可惜。

---

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

更新语句的执行流程和查询是差不多的，这一讲主要提了两个东西binlog和redolog。其中binlog属于serve层，redolog属于引擎层，由于历史原因，MySQL自带的MyISAM引擎没有支持崩溃恢复，所以使用了InooDB的redolog。

说说采用日志的意义吧，如果InnoDB是以页为单位进行刷盘的，如果更新一条语句就刷盘显然太浪费了，另外如果在一个事务中，SQL语句涉及到的页面可能并不相邻，会造成随机IO，而redolog是顺序写入磁盘的。

再说说binlog，在binlog中记录的是原始语句，binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志；而redolog记录的是在某行做了什么修改，采用的是循环写，redolog 并没有记录数据页的完整数据，在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到bufferpool中，然后让 redolog 更新内存内容，数据也在bufferpool中变成脏页。

为了两个log日志逻辑一致，使用两阶段提交来保证在这个问题。redolog的写入分为prepare和commit两个阶段，在这两个阶段中间则是写入binlog的过程。

那么崩溃的时候怎么恢复呢，如果到了redolog的commit阶段，则说明日志是完整的，如果redolog经过prepare阶段，但binlog并不完整则说明日志不完整，反之binlog完整则日志完整，可以进行日志恢复。

如何判断binlog是否完整：
1、statement 格式的 binlog，最后会有 COMMIT；
2、row 格式的 binlog，最后会有一个 XID event

这里如果binlog完整就可以进行日志恢复是考虑到主从复制的时候，binlog已经发到从库中，为了保证数据一致，便采用了这个策略。

---

23 MySQL是怎么保证数据不丢的？

23讲介绍了binlog和redolog的写入机制，和第二讲的内容紧密相关，所以我这里先总结23讲的内容。

从学习操作系统开始，咱就被告知内存的读写速度比硬盘更快，为了避免大量的磁盘的IO，在内存中引入了cache即缓存。

我们的binlog在写入的时候，也是先写入到binlog cache，然后再把binlog cache 写入到binlog文件中，系统给每个线程分配了一块内存作为binlog cache，不过所有线程共用一个binlog文件。一个事务必须一次性写入同一个binlog中，如果内存空间不够了，会暂存到磁盘中。

而redolog自然也有对应的redolog buffer，但是我们的日志一直在内存中也不是办法，总得找个时间把它刷入到磁盘咱心里才够踏实。

redo日志刷盘时机：
1、redolog buffer空间不足时，《MySQL是怎样运行的》这本书上提到了当日志量超过buffer容量的50%时就会刷盘。《MySQL45讲》中提到如果超过50%容量时，有个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache；
2、脏页刷盘时会把脏页对应的日志刷到盘中；
3、后台有一个线程专门负责这个事，同1中的情况如果此时有个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。

在第二讲中提到了redolog是循环写，既然是循环写那么总会覆盖掉开头的日志文件，所以需要把redolog刷到磁盘中，碰到崩溃恢复的时候，通过日志恢复即可

为了达到这个目的，我们需要一个全局变量记录哪些日志已经被写入了磁盘，也就是checkpoint。

这里要先引入日志逻辑序列号（log sequence number，LSN）的概念，InnoDB规定LSN的初始值是8704（不知道为啥不是从0开始，一个人刚诞生是0岁不是很合理吗- _ -），前面提到，我们的日志不是一条一条写入的，而是先写入buffer，buffer中是一个一个的block，每一组日志插入这个block中，根据日志占用的字节数b，直接加在LSN上（8704+n）。我们给设定一个checkpoint_lsn变量，表示能够覆盖的日志总量。

好，现在我们知道了LSN的概念，在bufferpool中每修改一个页面就会对应一个脏页的产生，脏页对应的控制块加入flush链表（链表中每个控制块按照第一次修改的时间排序），在每一个控制块中会记录第一次修改前对应脏页时的LSN值，记为oldest_modification；以及记录修改结束后对应的LSN值，记为newest_modification。如果后续再对已经在flush链表中的脏页进行修改，直接更新最新的newest_modification即可。

现在我们有了flush链表中最早脏页的oldest_modification，那么LSN凡是小于该值的日志都是已经被刷盘的，再把该值赋给checkpoint_lsn。

---

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

说到事务，那肯定逃不过经典八股，事务的四大特性,ACID，即原子性、一致性、隔离性、持久性。关于这四大特性网上有很多解释，这里不多做介绍。

在事务并发执行的过程中，可能会出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

提一下，不可重复读的重点是修改，修改了未提交事务的数据：同样的条件，你读取过的数据，再次读取出来发现值不一样了；幻读的重点在于写入（insert，delete）符合之前搜索条件的一些记录：同样的条件，第 1 次和第 2 次读出来的记录数不一样。

不可重复读强调的是数据是否被改变了，幻读强调数据是否存在，幻读后数据的条数不一样了。

为了解决这些问题，就有了“隔离级别”的概念，那么八股他又来了。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

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

写到这里的时候我在想，读未提交这个隔离级别三种情况都解决不了，那它到底有啥作用。在网上找到一个场景，插入一个非常大的文件，如果事务一直没有提交，为了知道插入多少数据了，可以将隔离级别设置读未提交。

那么这四种隔离级别是怎么实现的呢，对于InnoDB来说，记录的修改都会记录一条undo日志，如果一条记录多次修改，通过行记录中的roll_pointer属性将这些undo日志串联起来，链表的头结点对应记录的最新值，这样一个链表我们叫做版本链，而这种机制也就是多版本并发控制（multi-version 从currency control， MVCC）.

回到刚才提的问题，读未提交的实现直接读版本链中最新的值即可；串行化的实现通过加锁来进行访问；至于读提交和可重复度的实现都需要满足只能读到已提交事务的记录，这里的关键点就在于知道版本链的各个版本对于当前事务是否可见，InnoDB通过一致性视图（ReadView）解决了这个问题。

ReadView在生成的时候会记录一个属性creator_trx_id，即生成该视图的事务的事务id。在ReadView中还记录了当前活跃事务（即未提交）ID列表，列表里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

有了ReadView后，如果某个版本的事务id等于creator_trx_id，则说明这是当前事务自己修改过的记录，那么该版本对当前事务可见；如果某个版本的事务id小于低水位，则说明这是已经提交过的事务的记录，那么该版本对当前事务可见；

如果某个版本的事务id在低水位和高水位之间，则需要先看它是否在列表中，如果在说明生成视图时该事务仍然活跃，还不可见；如果某个版本的事务大于等于高水位，说明这是将来启动的事务生成的，也是不可见的。

写到这里，我又有疑问了，读提交和可重复读都是只能读已提交事务的记录，为什么可重复读能解决不可重复读，但是读提交不能避免不可重复读。这是因为读提交每次读取数据前都生成一个新的ReadView，但是可重复读只有在第一次读取数据时生成一个ReadView。

可是可重复读为什么会出现幻读呢，这是因为更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。加锁的 SELECT，或者对数据进行增删改都会进行当前读。

---

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

先说说第6讲，因为可重复读涉及一个通过间隙锁解决幻读的问题，我想先总结一下和锁有关的知识。

全局锁

从锁的粒度的出发，由粗到细，先谈一下全局锁。某个会话加上全局锁后，其他会话对这个库做出增删改操作都会被拒绝，整个库处于只读状态的时候。

使用全局锁，则要执行这条命：flush tables with read lock

释放全局锁，则要执行这条命令：unlock tables

一个全局锁加上去，这个库就只归我啦，那这个东西有什么应用场景呢，答案是全库备份的时候。不过这样的话，会严重影响正常业务的进行，如果数据库引擎支持事务并且支持可重复读的隔离级别，那么可以利用第5讲说的readview进行备份。像MyISAM 这种不支持事务的引擎，在备份数据库时就只能使用全局锁的方法。

表锁

表级别的锁有这几种：

• 表锁；
• 元数据锁（MDL）;
• AUTO-INC 锁

表锁

使用表锁，执行以下命令：
//表级别的共享锁（S锁），也就是读锁；
lock tables t read;

//表级别的独占锁（X锁），也就是写锁；
lock tables tt wirte;

释放表锁，执行这条命令：unlock tables

其实这里还有一个表级别的锁，叫做意向共享锁（IS锁）和意向独占锁（IX锁）。出现这个概念的原因是在加表锁的时候，我们无法确定表中的哪里记录加了锁或者是有没有加锁（行锁），为了避免遍历，引入了这个概念。

当给某条记录加X锁时，需要加上表级别的IX锁；
当给某条记录加S锁时，需要加上表级别的IS锁。

元数据锁（MDL）

MDL 不需要显式使用，在访问一个表的时候会被自动加上。对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

读锁之间是共享的，这意味着多个线程对表进行CRUD是不会阻塞的；但某个线程对表结构进行更改时，有另一个线程想进行CRUD操作，读写之间是互斥的。

AUTO-INC 锁

在为某个字段声明 AUTO_INCREMENT属性时，之后可以在插入数据时，可以不指定该字段的值，数据库会自动给该字段赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

在插入数据时，会加一个表级别的 AUTO-INC 锁，插入语句执行完成时释放锁资源。所以但是， AUTO-INC 锁对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

---

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

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

是的，我又跳着总结了，并且一次总结了两讲，丁奇大大的这个专栏和出版书不一样，没有严格按照知识点顺序讲解下来，有想系统学习的朋友还是建议买一本书来看看。

我在第6讲总结开头说可重复读涉及一个通过间隙锁解决幻读的问题，那么可重复读为什么会出现幻读，InooDB将其设置为默认隔离级别，那他们又是怎么解决幻读的。

第一个问题：可重复读为什么会出现幻读
对语句进行锁定读select * from t where id=1 for update;，select * from t where id=1 lock in share mode;和增删改操作的时候会出现当前读，即读取当前最新的数据，更新数据都是先读后写的，所以读也是当前读。这个当前读就是出现幻读的罪魁祸首了。

第二个问题：InnoDB是怎么解决的
答案是通过对记录加行锁，默认加next-key lock，next-key lock是由间隙锁和记录锁组成的，左开右闭。InnoDB的行锁是是加在索引上的， 如果SQL语句未命中索引，则走全表扫描，表上每条记录都会上锁，导致并发能力下降，增大死锁的概率，因此查询尽量命中索引。

加锁规则

• 唯一索引等值查询：当索引项存在时，next-key lock 退化为记录锁；当索引项不存在时，默认 next-key lock，访问到不满足条件的第一个值后next-key lock退化成间隙锁
• 唯一索引范围查询：默认 next-key lock，会访问到不满足条件的第一个值为止
• 非唯一索引等值查询：默认next-key lock ，索引项存在/不存在都是向右遍历访问到不满足等值条件的时候，next-key lock 退化为间隙锁。
• 非唯一索引范围查询：默认 next-key lock，向右访问到不满足条件的第一个值为止

记录锁之间会存在竞争，但是间隙锁之间并不冲突，跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作。

最后InnoDB只是在很大程度上避免了幻读，比如如果先对一条记录进行快照读，在这期间另一个事务对这条记录进行了更改，之后先前的事务再进行当前读就会出现幻读。

---

待续……