# 一条 sql 了解 MYSQL 的架构设计

### 1 前言

最近我从cnaaa.com购买了云服务器。

对于一个服务端开发来说 MYSQL 可能是他使用最熟悉的数据库工具，然而，大部分的 Java 工程师对 MySQL 的了解和掌握程度，大致就停留在这么一个阶段：它可以建库、建表、建索引，然后就是对里面的数据进行增删改查，语句性能有点差？没关系，在表里建几个索引或者调整一下查询逻辑就可以了，一条 sql，MYSQL 是如何处理的，为我们做了什么，完全是个黑盒。本文主要通过 sql 执行的过程打破这样一个黑盒的认知，来了解 MYSQL 的逻辑架构。

MYSQL 的逻辑架构可分为 3 层：应用层、服务层、存储引擎层。其中存储引擎是 MYSQL 最有特色的地方，MySQL 区别于其他数据库的最重要特点是其插件式的表存储引擎，本文也将着重聊聊最常用的 innoDB 存储引擎的架构设计原理，假设现有如下 sql：

update users set name=’zhangsan’ where id = 10

作为一个 java 服务端工程师，见到这样一个 sql，本能的脑海中立刻就浮现出如下信息：

- 一个表名为 users 的表
- 有两个字段 id、name，id 是主键
- 把 users 表里的 id=10 的这个用户名修改为 “zhangsan”

那么 MYSQL 是如何处理这样一个 sql 呢？带着这个问题，我们来看一下 MYSQL 是如何通过一个个组件来处理这个 sql，来了解 MYSQL 的整体架构

### 2 应用层

#### 2.1 连接线程处理

当 MYSQL 面对上面的 sql，首先应该做什么呢？是如何解析？如何选择索引？如何提交事务？当然不是，首先应该解决的是怎么把 sql 语句传给它。大家都知道，如果我们要访问数据库，那么，首先就需要和数据库建立连接，那么这个连接由谁来建呢，答案就是 MYSQL 驱动，下面这段 maven 配置大家应该都很熟悉

![img](https://pic.imgdb.cn/item/632c035916f2c2beb1129c36.png)

java 程序就是通过这个驱动包来与数据库建立网络连接。
下图示意：

![img](https://pic.imgdb.cn/item/632c035916f2c2beb1129c3a.png)

从图中可以看到这样一个场景：java 程序很多个线程并发请求执行上述 sql，我们都知道数据库连接是非常占用资源的，尤其是在高并发的情况下，如果每次都去建立数据库连接就会有性能问题，也会影响一个应用程序的延展性，针对这个问题，连接池出现了。
下图示意：

![img](https://pic.imgdb.cn/item/632c035916f2c2beb1129c42.png)

从图中可见网络连接交由线程 3 监听和读取 sql 请求，至此 MYSQL 已经收到我们的请求，当然 MYSQL 在建立连接时还做了用户鉴权，鉴权依据是：用户名，客户端主机地址和用户密码；在获取连接后，处理请求时还会做 sql 请求的安全校验，根据用户的权限判断用户是否可以执行这条 sql。

### 3 服务层

#### 3.1 SQL 接口

从上图中我们知道线程 3 负责监听并读取 sql，拿到这个 sql 之后，如何执行是一项极其复杂的任务，所以 MYSQL 提供了 SQL 接口这么一个组件，线程 3 会将 sql 转交给 SQL 接口来执行如下图：

![img](https://pic.imgdb.cn/item/632c035916f2c2beb1129c4a.png)

SQL 接口具体处理功能有：DDL、DML、存储过程、视图、触发器等。

#### 3.2 SQL 解析器

接着问题来了，SQL 接口如何执行本文 sql 呢？，数据库怎么理解本文这个 sql 呢？相信懂 sql 语法的人立马就能知道什么意思，但是 MYSQL 是个系统不是人，它无法直接理解 sql 的意思，这个时候关键的组件出场了，SQL 解析器的作用主要就是是解析 sql 语句，最终生成语法树，比如本文 sql 就可以拆解成如下几个部分：

1. 需要从 users 表里更新数据
2. 需要更新 id 字段是 10 的那行数据
3. 需要把这行数据的 name 字段的值改为 “zhangsan”

![img](https://pic.imgdb.cn/item/632c035916f2c2beb1129c57.png)

#### 3.3 SQL 优化器

当通过 SQL 解析器理解了 sql 语句要干什么之后，该如何实现呢，以本文的更新语句为例，我们可以有以下两种实现方式：

1. 直接定位到 users 表中 id 字段等于 10 的一行数据，然后查出这行数据数据，然后设置 name 字段为 “zhangsan”；
2. 也可以通过更新 name 字段索引的方式在 name 索引上遍历 id 等于 10 的索引值，然后设置 name 字段为 “zhangsan”。

上面两种途径都能实现最终结果，显然第一种路径更好一些，所以，SQL 优化器就是从众多实现路径中选则一条最优的路径出来，也就是我们常说的执行计划。

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129d5e.png)

#### 3.4 执行器

通过 SQL 优化器我们得到一套执行计划，那么，这个计划怎么执行呢？这个时候就不得不提 MYSQL 存储引擎，我们都知道 MySQL 和其他关系型数据库不一样的地方在于它的弹性以及可以通过插件形式提供不同种类的存储引擎，类似 java 接口的多实现，MYSQL 肯定会有一套标准的存储引擎接口，而执行器就是按照执行计划一步一步的调用存储引擎接口完成 sql 执行而已，如下图：

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129d63.png)

上图专门将 binlog 标出来是为了和下文 innodb 存储引擎的 undo log、redo log 做区分，强调 binlog 是 server 层的日志，后续 binlog 和 redo log 的两阶段方式完成事务的提交会再次提到。

#### 3.5 查询缓存

MYSQL 服务层为追求高效也引入了 QUERY BUFFER 这个组件，但是这个组件比较鸡肋，缓存不仅需要 sql 全字匹配命中，而且对基础表的任何修改都会导致这些表的所有缓存失效，既不符合现在用户变量的开发模式，大部分时候也不高效。MYSQL 从 5.7 开始不推荐使用默认关闭，8.0 中不再支持，详细原因如下图：

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129d70.png)

截图来源 MYSQL 开发者专区文档：https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/

### 4 存储引擎层

#### 4.1 概述

上文执行器拿到执行计划后，调用存储引擎的接口来完成 sql 的执行，那么存储引擎如何帮助我们去访问、操作内存以及磁盘上的数据呢？我们都知道 MYSQL 的存储引擎有很多，实现方式各一，下面让我们继续通过上文的 sql 来初步了解我们常用的 Innodb 存储引擎的核心原理和架构设计

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129d80.png)

重温一下本文 sql：

update users set name=’zhangsan’ where id = 10 —- 历史 name = ‘lisi’

#### 4.2 缓冲池（buffer pool）

InnoDB 存储引擎中有一个非常重要的放在内存里的组件，就是缓冲池（Buffer Pool），这里面会缓存很多的数据，以便于以后在查询的时候，万一你要是内存缓冲池里有数据，就可以不用去查磁盘了，如下图：

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129d88.png)

缓冲池（buffer pool）在 Innodb 中的地位类似于我们现在系统设计中 redis 的地位，在 Innodb 中引入这一组件的就是为了高效的存取，我们都知道 MYSQL 查询数据很快，究其原因不止是索引查询，深层次的原因就是所有的增删改查都是在 buffer pool 这块内存上操作的，相比于操作磁盘，效率不言自明。

4.2.1 数据页、缓存页和脏页

还是拿我们的 sql 举例，更新 id=10 的这条记录，难道从磁盘里只拉取 id=10 数据进入内存中吗？很明显不是，毕竟加入内存的记录不止这一张表，而且单表每行记录也不一样，内存管理会非常困难的，所以，MYSQL 对数据抽象出来的一个叫数据页的逻辑概念，每页固定大小默认 16KB，可以存多条数据，并且 buffer pool 里的存储结构和数据页一致，这样内存管理就会简单的多，数据页注册元数据后加载进内存后就是缓存页。

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129e23.png)

从图中可以看到在缓存页在 sql 更新完还未刷回硬盘时数据和磁盘中的数据页是不一致的，这个时候我们称这种缓存页为脏页。至于后续脏页如何落盘暂时不提。

4.2.2 元数据

从上图我们看到 buffer pool 中除了缓存页，还多了一个元数据内存结构，这个可以简单的理解为登记，比如因为疫情外地人回家过年会被当地政府进行登记，记录从哪来、到哪去等信息，便于管理，buffer pool 也是这样做的；但是元数据可不止记录缓存页的磁盘地址和内存地址这么简单，buffer pool 核心原理都是通过元数据来实现的

4.2.3 free 链表

buffer pool 在 MYSQL 初始化的时候，就根据配置在内存中申请了一块连续的空间，申请过后就按数据页的大小和元数据的大小进行合理的划分出很多个连续的、空的缓存页，当需要查询数据的时候就会从磁盘读入数据页放入到缓存页当中，但是由于脏页的存在，数据还未刷盘不能使用，那么数据页加载进哪个缓存页就是个问题。为了解决哪些缓存页是空闲的，MYSQL 团队为 Buffer pool 设计了一个 free 链表，它是一个双向链表的数据结构，这个 free 链表里每个节点就是一个空闲的缓存页的元数据块地址，也就是说只要一个缓存页是空闲的，那么他的元数据块就会放入这个 free 链表中，这样加载数据页是只需要从 free 链表中找空闲的缓存页即可。

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129e33.png)

从图中即可看出链表的大致结构，那么现在我们要更新 users 表中 id=10 的记录，首先要知道 id=10 这条记录的数据页有没有在缓存页当中，然后在决定是否是加载数据页还是直接使用缓存页，所以，buffer pool 里还有左下角这种 hash 表，用表空间 + 数据页号作为 key，缓存页地址为 value，可以快速判断数据页是否被缓存。

4.2.4 flush 链表

本文 sql 执行更新后，这样就导致内存中的数据和磁盘上的数据不一致，这就表明这个缓存页是脏页，脏页是需要刷新到磁盘文件的。但是不可能所有缓存页都刷回磁盘，比如有的缓存页可能只是查询的时候用到了，没有别更新过，所以数据库就引入 flush 链表，flush 链表和 free 链表的实现方式一样，都是在元数据中增加两个指针做成双向链表，用来标记链表上的都是脏页，需要刷回磁盘，后续 IO 线程异步刷盘就是将 flush 链表的数据刷盘，然后把缓存页移除 flush 链表，加入 free 链表当中。

4.2.5 LRU 链表

随着不停的把磁盘上的数据页加载到空闲的缓存页里去，free 链表中空闲的缓存页越来越少，如果 free 链表空了，这时候就无法从磁盘加载数据页了，这时候就需要淘汰掉一些缓存页，首先想到的就是把修改过的缓存页刷新回磁盘上，然后清空这个缓存页

具体选择哪个缓存页进行清空呢，数据库引入 LRU 链表，结构和 free 链表基本一致，最近访问的缓存页都会被移动到 LRU 链表的头部，这样尾部的就是少访问的数据，但是这样的 LRU 有个问题，就是 MYSQL 的预读机制，会把不常访问或者不访问的数据连带着加载到内存，这样就把这一部分也放在了 LRU 头结点上，很明显不合理，同理，全表扫描也有这个问题。

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129e4a.png)

从上面可以看出，如果此时需要淘汰缓存页，就可能把热点数据提前淘汰掉。对于这种不合理的 LRU 算法 MYSQL 基于冷热数据分离的方法对 LRU 算法进行如下优化：LRU 链表被拆分为两个部分，一部分热数据，一部分冷数据，数据页第一次加载到缓存的时候是放在冷数据表头，在 1s 后再次访问这个缓存页，就很有可能是热数据，就会把它挪到热数据表头区域，这样设计防止了刚加载就访问造成的假热现象。

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129e59.png)

冷热区域缓存页移动规则如下：

- 冷数据 -> 热数据
  冷数据区的缓存页是在 1s 后再被访问到就移动到热数据区的链表头部
- 热数据 -> 冷数据
  能留在热数据区域的缓存页，证明都是缓存命中率比较高的，会经常被访问到。如果每个缓存页被访问都移动到链表头部，那这个操作将会非常的频繁。所以 InnoDB 存储引擎做了一个优化，只有在热数据区域的后 3/4 的缓存页被访问了，才会移动到链表头部；如果是热数据区域的前 1/4 的缓存页被访问到，它是不会被移动到链表头部去的。这样尽可能的减少链表中节点的移动了

4.2.6 小结

现在我们了解了更新数据会先把数据加载进 buffer pool 在进行，了解 buffer pool 是如何通过冷热数据分离的机制优化 LRU 链表，为系统设计中缓存过期淘汰策略提供的新的解决思路。既然，数据更新是把数据载入 buffer pool 中修改，那么更新完缓存页之后数据库是如何保证事务提交、如何保证数据页和缓存页数据一致的呢

#### 4.3 undo log

说到事务就不得不提事务是如何回滚的，innodb 是引入了 undo log 的日志组件来实现事务回滚的，以本文 sql 为例，在数据加载进缓存页后，修改之前，会将执行的 sql 取反保存在 undo log 中，逻辑类似 sql：

update users set name=’lisi’ where id = 10

当然如果是 insert 语句与之对应的就是 delete 语句，delete 语句也就对应的 insert 语句，这也就明白为什么 delete 的数据是可以回滚，而 truncate 数据之后无法回滚的根本原因，在于 truncate 无法生成 undo log。

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129e63.png)

上图是本问 sql 执行的大致步骤，至于加入 buffer pool 这块上文已经详细了解过了，就不在赘述。从图中可以看出因为 log 直接刷盘比较损耗性能，所以引入 log buffer 进行缓存，然后在通过异步的方式把数据刷入磁盘既然数据更新之前的数据记录下来并成功刷入磁盘，则事务的回滚就不难实现了。

当然 undo log 除了提供回滚功能，还为多版本并发控制（MVCC）提供了实现基础，实现了 MYSQL 的非阻塞读写，提高了系统的并发性。本文也不再深入

#### 4.4 redo log

下面来了解一下 innodb 是如何保证 buffer pool 缓存的数据一致性问题，数据更新值内存后并不会立即刷新至磁盘数据页，而是一致以脏页的形式保存在 buffer pool 当中，这样做有两个原因会导致效率很差，一个是内存向磁盘写数据本身效率就慢，另一个就是随机 IO 会写磁盘的时间上附加上很多磁头寻址的时间，所以立即刷数据页效率很低。

Innodb 是如何规避上述问题的呢，正常情况下，异步刷盘就已经可以解决了刷磁盘慢的问题，但是，假如 MYSQL 系统崩溃、宕机，这时候脏页还未及时刷盘，那么缓存页期间所有改动数据岂不是丢了，所以，Innodb 引入了另一个组件 redo log，专门记录数据被缓存期间做过的修改记录，然后立即写入 redo log 磁盘文件，相比于缓存页刷盘，redo log 刷盘的数据了小多了，并且写 redo log 是顺序 IO，而缓存页刷盘是随机 IO。下图示意：

![img](https://pic.imgdb.cn/item/632c035b16f2c2beb112a052.png)

这样当数据库异常宕机时，即使缓存页丢失数据也不会丢失，因为 redo log 已经落盘，数据库重启的时候会更近 redo log 把磁盘上历史的数据页重新载入内存，重新按 redo log 的修改记录操作一遍就能将缓存页中的数据恢复至宕机前的状态。

如果系统宕机时，redo log 还没落盘数据岂不是丢了，对，这种情况下数据会丢，这种 redo log 丢数据分两中情况：

第一种情况，MYSQL 有三种刷盘策略，通过 innodb_flush_log_at_trx_commit 参数进行配置

1. 配置为 0：事务提交的时候不会把 redolog buffer 里的数据立即刷入磁盘，此时如果宕机则会导致已提交的数据修改丢失；
2. 配置为 1：则是事务提交的时候必须把 redolog buffer 里的数据刷入磁盘，以保证事务提交后操作数据日志不丢；
3. 配置为 2：则表示只是把数据交给操作系统进行刷盘，操作系统刷没刷成功则不管，理论上操作系统刷盘是先要经过 os cache 内存缓存的，就是说数据会先在 os chache 里没有真正的落盘，这种模式下也可能导致数据丢失

![img](https://pic.imgdb.cn/item/632c035b16f2c2beb112a05c.png)

这第一种情况如果产生丢数据，是真的丢失，所以，如果对数据库丢失数据零容忍，建议配置策略为 1

第二种情况，就是未写 commit 标记日志的情况，即下图第 9 步丢失的情况，但是这种情况系统认为事务提交失败，所以丢失了并不影响数据一致性。

![img](https://pic.imgdb.cn/item/632c035b16f2c2beb112a068.png)

图中 7、8、9 三个步骤是事务提交 commit 的时候才做的（本文只用一个 sql 来讲解，默认事务自动提交），redo log 记录更新记录之后，执行器会把修改记录写在 server 层的 binlog 当中，很明显这是两个文件，如果出现上述宕机等异常情况，这两个文件的数据一致性是不能保证的，所以，为了保证两个文件的数据一致性，innodb 会在 binlog 写完之后在 redo log 中补上一个 commit 标记告诉 redo log 事务成功。事务执行成功后操作 redo log 刷入磁盘，至此本文 sql 执行成功。

### 5 总结

通过一条 update 的 sql 的更新流程，清晰的看到 MYSQL 的整体架构设计，对 Innodb 存储引擎的几大核心组件如何相互协作、配合以实现高效的数据库系统有了更清晰的认识；核心组件 buffer pool 的冷热数据分离的缓存淘汰机制也为以后系统的架构设计提供了新的解决思路。

ITPub博客

一条 sql 了解 MYSQL 的架构设计

最新文章