一条sql了解MYSQL的架构设计-京东云开发者社区

### 1 前言

对于一个服务端开发来说 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配置大家应该都很熟悉

![](//img1.jcloudcs.com/developer.jdcloud.com/492d1cee-2bda-4df4-9f16-e7d6dc84070c20220428150149.png)

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

![](//img1.jcloudcs.com/developer.jdcloud.com/42d8bf3c-0f6d-406f-a12b-7ce65466b2e120220428150201.png)

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

![](//img1.jcloudcs.com/developer.jdcloud.com/e44aa781-ed03-48a0-a24e-c14892bdfa1920220428150221.png)

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

### 3 服务层

#### 3.1 SQL 接口

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

![](//img1.jcloudcs.com/developer.jdcloud.com/f2c0314a-5269-4178-8d64-73fa1b6cd42e20220428150255.png)

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

#### 3.2 SQL解析器

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

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

![](//img1.jcloudcs.com/developer.jdcloud.com/f8d237a6-16e3-4d93-be21-29a476c8c3ac20220428150344.png)

#### 3.3 SQL优化器

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

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

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

![](//img1.jcloudcs.com/developer.jdcloud.com/efeed4ad-159f-4c60-9701-50ab20f9452d20220428150430.png)

#### 3.4 执行器

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

![](//img1.jcloudcs.com/developer.jdcloud.com/99f68439-0915-4959-83f0-6275f6ed854920220428150449.png)

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

#### 3.5 查询缓存

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

![](//img1.jcloudcs.com/developer.jdcloud.com/da9d25d7-267b-4cd9-910b-5ddbe82b0ddd20220428150521.png)

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

### 4 存储引擎层

#### 4.1 概述

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

![](//img1.jcloudcs.com/developer.jdcloud.com/deccd89e-cdec-4d61-9113-73be9d73dc8a20220428150719.png)

重温一下本文sql：

update users set name='zhangsan' where id = 10 ---历史name = 'lisi'

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

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

![](//img1.jcloudcs.com/developer.jdcloud.com/46aca08f-6c02-4c7b-bde0-cd0df38277dd20220428151742.png)

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

##### 4.2.1 数据页、缓存页和脏页

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

![](//img1.jcloudcs.com/developer.jdcloud.com/46c2cfbe-53b7-411c-b000-700e76eb987120220428152409.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链表中找空闲的缓存页即可。

![](//img1.jcloudcs.com/developer.jdcloud.com/de480c32-c143-46df-87ee-e4ca55f2ade920220428152451.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头结点上，很明显不合理,同理，全表扫描也有这个问题。

![](//img1.jcloudcs.com/developer.jdcloud.com/a7221e71-3630-4c3f-9e5b-e8b11f4f7f1b20220428152603.png)

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

![](//img1.jcloudcs.com/developer.jdcloud.com/1e8e0514-f21b-404c-a6ec-e7317e72daad20220428152622.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。

![](//img1.jcloudcs.com/developer.jdcloud.com/a3139de9-b3f6-4f56-8060-dbe2749fd67920220428152653.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。下图示意：

![](//img1.jcloudcs.com/developer.jdcloud.com/05622328-12c9-4df4-a871-a4eb6bed48a620220428152749.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里没有真正的落盘，这种模式下也可能导致数据丢失

![](//img1.jcloudcs.com/developer.jdcloud.com/f50b458d-4d41-4f1b-bef4-d1d41a973b7620220428152830.png)

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

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

![](//img1.jcloudcs.com/developer.jdcloud.com/cf836583-4313-4679-8c09-c6049e83029920220428152846.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的冷热数据分离的缓存淘汰机制也为以后系统的架构设计提供了新的解决思路。

------------

###### 自猿其说Tech-JDL京东物流技术与数据智能部
###### 作者：陆庆林