MySQL入门（1）：查询和更新的内部实现

摘要

在MySQL中，简单的CURD是很容易上手的。

但是，理解CURD的背后发生了什么，却是一件特别困难的事情。

在这一篇的内容中，我将简单介绍一下MySQL的架构是什么样的，分别有什么样的功能。然后再简单介绍一下在我们执行简单的查询和更新指令的时候，背后到底发生了什么。

1 MySQL结构

在这一小节中，我会先简单的介绍一下各个部分的功能。随后，将在第二、第三节中详细介绍。

先来看一张图：

简单的来讲一讲：

1.1 连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

在客户端输入了账号密码之后，如果此时账号密码验证通过，连接器将会和客户端建立一条TCP连接。这个连接将会在长时间无请求后被连接器自动断开（默认是8小时）。

此外，在连接建立后，如果管理员修改了这个账户的权限，也不会对当前的连接有任何的影响，当前连接所拥有的权限还是之前未修改前的权限。

1.2 分析器

分析器有两个功能：词法分析、语法分析。

对于一个 SQL 语句，分析器首先进行词法分析，对sql语句进行拆分，识别出各个字符串代表的含义。

然后就是语法分析，分析器根据定义的语法规则判断sql语句是否满足 MySQL 语法。

所以，如果我们看到You have an error in your SQL syntax这么一段话，就可以知道这个错误是由分析器返回的。

1.3 缓存

这里的缓存会保存之前的sql查询语句和结果。你可以理解为这是一个map：key是查询的sql语句，value是查询的结果。

并且，在官方手册中，有这么一句话：

Queries must be exactly the same (byte for byte) to be seen as identical.

也就是说，查询语句必须得和之前完全一致，每一个字节都一样，大小写敏感，甚至不能多一个空格。

但是，这里的缓存是非常容易失效的。为了保证查询的幂等性，当某一张表有数据更新后，这个表的缓存也将失效。

所以，对于更新压力大的数据库来说，查询缓存的命中率会非常低。建议只在读多写少的数据库开启缓存。

但是，在MySQL8.0以后，已经删除了缓存功能。

1.4 优化器

查询优化器的任务是发现执行SQL查询的最佳方案。大多数查询优化器，包括MySQL的查询优化器，总或多或少地在所有可能的查询评估方案中搜索最佳方案。

简单来说，优化器就是寻找一个最快能够查询到数据的策略。

1.5 执行器

在通过了上述的过程后，Server层已经解析出了需要处理的数据是什么，应该怎么做。

随后会进行权限的判断，如果当前的连接拥有目标表的权限，则会调用存储引擎开放的接口，处理需要处理的数据。

到这里MySQL的基本架构就讲完了。但是因为我省略了大部分的细节，只讲了这么一小部分，可能会导致你的疑问增加了。

不过没关系，我们接着往下看，用实际的例子来解释这里的每一部分，可能会更容易理解。

2 查询

我们从这么一条sql讲起：

select * from T where ID = 1；

2.1 查找缓存

首先，会调用分析器，进行词法分析。

此时，词法分析发现这条sql语句是以select开头的，并且在这条语句中没有任何不确定的数据，所以会去缓存中查找是否保存了这条语句的结果作为缓存。

但是关于上面的说法，有我个人推测的部分。我没有在官方文档中找到MySQL是何时查找缓存的，到底是在分析器之前还是分析器之后。

但是在《高性能MySQL》这本书中提到了 “通过检查sql语句是否以select” 开头，所以我推测查找缓存是需要先经过简单的词法分析的。

只有经过了词法分析分析，MySQL才能知道这段语句是否是select语句，也能知道这条语句中有无一些不确定的数据（如当前时间等）。

2.2 缓存未命中

此时，如果缓存未命中，则继续使用分析器进行语法分析。然后，根据这颗语法树，来判断这条sql语句是否符合MySQL语法的。

注意，关于词法分析和语法分析，如果你感兴趣的话，可以看一看编译原理相关的内容。

然后来到了优化器。优化器就是在有多种查找方式的时候，自行选择一个更好的查询方式。

例如，如果此时sql语句里面有多个索引，会选择一个合适的索引；又或者在关联查询的时候，选择一个更好的方案。

这一部分的内容我想在以后的文章中介绍，这里我想重点讲讲下面的内容，关于MySQL中数据的结构。

2.3 数据的结构

在我们利用最后一步的执行器去进行数据的读取和写入的时候，其实是调用了MySQL中的存储引擎进行数据的读写和写入。

回到我们的例子，我们要找的是在表T中ID为1的数据。但是，存储引擎并不会返回这么一条具体的数据，他返回的是包含这条数据的数据页。

这里我补充一点点知识：

数据库使用页管理，和我们操作系统是一样的。因为我们现在的机器是冯诺依曼结构的，这是是一种将程序指令存储器和数据存储器合并在一起的存储器结构。

在这种结构中，具有一个特性，叫局部性原理。

• 时间局部性（Temporal Locality）：如果一个信息项正在被访问，那么在近期它很可能还会被再次访问。程序循环、堆栈等是产生时间局部性的原因。
• 空间局部性（Spatial Locality）：在最近的将来将用到的信息很可能与正在使用的信息在空间地址上是临近的。
• 顺序局部性（Order Locality）：在典型程序中，除转移类指令外，大部分指令是顺序进行的。顺序执行和非顺序执行的比例大致是5:1。此外，对大型数组访问也是顺序的。指令的顺序执行、数组的连续存放等是产生顺序局部性的原因。

简单的来解释就是如果一行数据被读取了或者一条指令被执行了，那么很大概率接下来CPU会继续读取或执行这个地址或者这个地址后面的数据和指令。

在MySQL中也是一样的，如果一次性读取一个页，那么可能在接下来的读写中所操作的数据也在这个数据页内，这样可以使得磁盘IO的次数更少。

回到我们刚刚说的内容，至于引擎是如何找到这个页的，我想在后面索引相关的文章中再详细解释。这里我们先简单的理解为引擎能够快速的找到这一行数据所在的页，然后这一页返回给执行器。

此时，这一页数据还会被保存在内存中。在之后还需要用到这些数据的时候，将会直接在内存中进行处理，并且MySQL的内存空间中可以存放很多个这样的数据页。也就是说，这个时候无论是查找还是修改，都可以在内存中进行，而不需要每次都进行磁盘IO。

最后，会在合适的时候将这一页数据写回磁盘。至于是在什么时候如何写回磁盘的，我们接着往下看。

3 更新

在说完了如何查找数据之后，我们已经知道了一行数据是如何以页的形式保存在内存中了。我们现在要解决的问题是：

• update语句是如何执行
• 如何将执行后的新数据持久化在磁盘中

这是一个很有意思的问题，我们来假设两种情境：

假设MySQL在更新之后只更新内存中的数据就返回，然后再某一时刻进行IO将数据页持久化。这样所有操作都是在内存中，可以想象此时的MySQL性能是特别高的。但是，如果在更新完内存又还没有进行持久化的这段时间，MySQL宕机了，那么我们的数据就丢失了。

再来看另外一种情况：每次MySQL将内存中的页更新好后，立刻进行IO，只有数据落盘后才返回。此时我们可以保证数据一定是正确的。但是，每一次的操作，都要进行IO，此时MySQL的效率变得非常低。

所以我们来看看MySQL是如何做到保证性能的情况下，还保证数据不丢的。

现在回到这条语句：

update T set a = a + 1 where ID = 0;

假设这条sql语句是正确的，存在名为ID，a的列在表T中，且存在ID为0的数据。

此时经过连接器，分析器，分析器发现这是一条update语句，于是继续语法分析，优化器，执行器。执行器判断有权限，然后开表，引擎找到了包含了ID为0这行数据的数据页，将这一页数据保存在内存中。

你可以发现，update语句，同样也走了这么一遍流程。

然后重点来了，我们要介绍一下MySQL是如何保证数据一致性的。

3.1 重做日志

这里要介绍一个很重要的日志模块，称为todo log（重做日志）。

注意，重做日志是InnoDB引擎特有的。

重做日志在更新数据的时候，会记录在哪个数据页更新了什么数据，并且只要成功的在重做日志记录了这次更新，不需要将内存中的数据页写回磁盘，就可以认为这次更新已经完成了。

MySQL里有一个名词，叫WAL技术，WAL的全称是Write-Ahead-Logging，它的关键点就是先写日志，再写磁盘，也就是说只要保证了日志的落盘，数据就一定正确。此时只要保存了日志，就算此时MySQL宕机了，没有将数据页写回磁盘，也可以在之后利用日志进行恢复。

但是，InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB。固定大小也就造成了一个问题，redo log是会被写满的。

所以，InnoDB采取了循环写的方式。注意看，这里有两个指针。write_pos表示当前写的位置，只要有记录更新了，write_pos就会往后移动。而check_point表示检查点，只要InnoDB将check_point指向的修改记录更新到了磁盘中，check_point将会往后移动。

换句话说，拿我们刚刚的update T set a = a + 1 where ID = 0;举例，如果我们把这一行数据所在的内存页更新好了，并且写入了todo log中，此时将返回修改成功的提示。然后在todo log中表现为记录了在某一个内存页的更新记录。

注意，此时在磁盘中，数据a未改变，在内存中，a改为了a+1，在todo log中记录了这个内存页的更新记录，write_pos往后移动。

此时，如果要把check_point往后移，那么他就应该把记录中这个内存页的更新持久化到磁盘中，也就是说要把a+1写回磁盘，此时无论是磁盘还是内存，a的数据都是a+1。只有成功的写回了磁盘，check_point才可以往后移动。这个设计，使得todo log是可以无限重复使用的。

那么问题来了，我们现在只是知道了write_pos会在数据更新之后往后移动，那么check_point会在什么时候移动呢？

这里涉及到了innodb_io_capacity这个参数，这个参数会告诉InnoDB你的磁盘读写速度怎么样，然后由他来控制check_point的移动。至于如何调优，我想在以后的文章中来介绍，在本文你就理解为，他会按照一定的速度，不断推进。

然后问题又来了，如果此时数据库有大量的更新操作，而check_point推进的速度又是恒定的，那么write_pos不断往前推进，就一定会写满。这种情况是InnoDB要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都会被堵住。如果你从监控上看，这时候更新数会跌为0。至于如何避免这种情况，我想等到调优的时候再来聊，这里我们只是知道会有这么一种情况。

除此之外还有一种情况我想聊一聊，同样是大量的更新操作。我们在前面已经提到过了，所有的操作都会在内存中完成，也就是说如果此时我要操作的数据，他们分布到了不同的数据页中，那么此时内存中就存储了非常多的数据页。这个时候，内存可能不足了。

我们这里补充一个概念，干净页和脏页。干净页指的是从磁盘读到内存中，没有被修改过，你可以理解为只被查询而没有被更新过的数据页。而脏页是和磁盘中数据不一样的数据页，他被修改过。如果此时有大量的查询或更新操作，那么就需要有大量的内存空间，而此时内存空间已经有各种各样的数据页了。那么我们应该怎么办呢？

• 如果还有空闲空间，则直接将需要的数据页读取并存到空间空间内。
• 如果没有空闲空间了，则淘汰最近最少使用的干净页，也就是说把这个干净页的空间给用了。
• 如果连干净页也没有了，那么需要淘汰最近最少使用的脏页。要怎么淘汰呢，把脏页写回磁盘，也就是说更新脏页的数据，使他变成了干净页。

然后问题又双叕来了，如果此时我们因为内存空间不足而将这个脏页写回了磁盘，但是对这个脏页的更新却记录在了redo log的不同位置，那么在redo log需要更新这个页的时候，怎么办呢？我们需不需要在刷新脏页的时候，在redo log中也把对应的记录删掉或者怎么样呢？

这个问题我希望你能思考一下，如果有了这个疑问我想你就理解了上面我说的关于redo log和脏页的问题了。答案是在更新脏页的时候，是不需要修改redo log的。redo log在check_point往前推进的时候，如果发现这个页已经被刷回磁盘了，将会跳过这条记录。

3.2 归档日志

说了这么多重做日志，我们再来聊聊归档日志。

有几个原因，redo log是循环使用的，也就是说新数据一定会覆盖旧数据，我们没办法拿他来恢复太长时间的记录。

第二个原因是因为redo log是InnoDB引擎特有的，在别的引擎中，就没有重做日志了。

所以在这里我们聊聊引擎层必有的归档日志binlog。

归档日志是追加写的，在一个文件写满后就会切换到下一个文件继续写，会记录每一条语句更改了什么内容。

也就是说，在进行故障恢复的时候，可以使用binlog一条一条的恢复记录。

那我们要怎么保证binlog一定能保证数据一致性呢，我们来聊聊MySQL中的两阶段提交。

还是以update T set a = a + 1 where ID = 0;为例：

解释一下：一直到更新内存中的数据页，在上面都已经提到过了。然后是将数据页的更新写入redo log中。

注意，这里写的redo log，并不是写入了redo log的文件中，而是写入了名为redo log的buffer中，也就是说此时并没有使用磁盘IO，不会造成性能的降低。

然后，进入了名为prepare的阶段。

然后，写入bin log。注意，这里说的写入bin log，也同样没有持久化，也是写入了buffer中。

只有当这两者都写入成功了，才会到提交事务的阶段。

然后，有两个参数很重要。

这两个参数决定了是否等待直到将redo log和bin log持久化之后再返回。

sync_binlog和innodb_flush_log_at_trx_commit。

先说说innodb_flush_log_at_trx_commit：

• 当设置参数为1时，（默认为1），表示事务提交时必须调用一次 fsync 操作，最安全的配置，保障持久性。
• 当设置参数为2时，则在事务提交时只做 write 操作，只保证将redo log buffer写到系统的页面缓存中，不进行fsync操作，因此如果MySQL数据库宕机时，不会丢失事务，但操作系统宕机则可能丢失事务。
• 当设置参数为0时，表示事务提交时不进行写入redo log操作，这个操作仅在master thread 中完成，而在master thread中每1秒进行一次重做日志的fsync操作，因此实例 crash 最多丢失1秒钟内的事务。（master thread是负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性）。

也就是说，如果我们设置为了1，在最后提交的时候，会调用fsync等待redo log持久化，才返回。

再说说sync_binlog：

• sync_binlog=0的时候，表示每次提交事务都只write，不fsync。
• sync_binlog=1的时候，表示每次提交事务都会执行fsync。
• sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。但如果宕机了可能会丢失最后的N条语句。
  也就是说，如果我们设置为了1，最后提交的时候会和上面说到的一样，等待系统的fsync。

那么，我们为什么需要两阶段提交来保证数据的一致性呢？

我们假设现在写完了redo log，进入了prepare阶段，但是还没有写bin log，此时数据库宕机，那么重启后事务会回滚，不影响数据。

再做一个假设，我们已经写完了bin log，宕机了，再重启后MySQL会判断redo log是否已经有了commit标识，如果有，则提交；否则的话，去判断bin log是否完整，如果是完整的，则提交，否则回滚。

那么，如果我们没有将阶段提交，会怎么样呢？

假设我们先提交redo log，再提交bin log，此时逻辑和两阶段提交一样，但是没有了两次验证。那么如果我们在redo log提交完了宕机了，那么我们重启后，可以根据redo log来恢复数据。但是因为我们在bin log中没有更新，所以在未来如果使用bin log进行恢复，或者同步从库的时候，将会导致数据不一致。（主从同步问题在以后的文章解释）

再做一个假设，先提交bin log，再提交redo log。那么在恢复的时候这个数据是没有被更新的，但是在未来使用bin log的时候，会发现这里的数据不一致。

所以说，两阶段提交是为了保证这两个日志是可以一致的。

写在最后

首先，谢谢你能看到这里。

希望这篇文章能够给你带来帮助，让你对MySQL的了解可以加深一些。当然了，文章篇幅有限，作者水平也有限，文章中很多地方的细节没有展开讲。很多知识点会在今后的文章中不断进行补充。另外，如果你发现了作者不对的地方，还请不吝指正，谢谢你！

其次，要特别感谢雄哥，给了我很多的帮助！另外，也特别感谢丁奇老师，我是以《MySQL实战45讲》作为主线进行学习的。

PS：如果有其他的问题，也可以在公众号找到作者。并且，所有文章第一时间会在公众号更新，欢迎来找作者玩~

MySQL 入门（1）：查询和更新的内部实现