【MySQL 系列】MySQL 架构篇

在我们开始了解 MySQL 核心功能之前，首先我们需要站在一个全局的视角，来看 SQL 是如何运作执行的。通过这种方式，我们可以在头脑中构建出一幅 MySQL 各组件之间的协同工作方式，有助于我们加深对 MySQL 服务器的理解。

---

---

1、MySQL 的逻辑架构

MySQL 的架构共分为两层：Server 层和存储引擎层

Server 层：负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接池，执行器、优化器、解析器、预处理器、查询缓存等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现；

存储引擎层：负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的。

---

2、SELECT 语句执行原理

2.1、连接器

当我们通过客户端访问 MySQL 服务器前，要做的第一步就是需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的

连接的过程需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的。

TCP 网络连接建立成功后，服务端与客户端之间会建立一个 session 会话，紧接着会对登录的用户名和密码进行效验，首先会查询自身的用户表信息，判断输入的用户名是否存在，如果存在则会判断输入的密码是否正确。密码正确后，会从连接池中分配一条空闲线程维护当前客户端的连接；如果没有空闲线程，则会创建一条新的工作线程。之后线程会查询用户所拥有的权限，并对其授权，后续 SQL 执行时，都会先判断是否具备相应的权限。

空闲连接在超过最大空闲时长（wait_timeout）之后，连接器会自动将它断开。

一个处于空闲状态的连接被服务端主动断开后，客户端并不会马上知道，等到客户端在发起下一个请求的时候，才会收到报错。

2.2、连接池

Connection Pool，是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对池中的连接进行申请、使用、释放。主要是为了复用线程、管理线程以及限制最大连接数。

当一个客户端尝试与 MySQL 建立连接时，MySQL 内部都会派发一条线程负责处理该客户端接下来的所有工作。

线程的频繁创建和销毁都会耗费大量资源，通过复用线程的方式，不仅能减少开销，还能避免内存溢出等问题。

数据库连接池可以设置最小连接数和最大连接数：

• 最小连接数：是连接池一直保持的数据库连接，如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费；
• 最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中。

2.3、查询缓存

如果查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句的哈希值，value 为 SQL 语句查询的结果。

如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

查询缓存往往弊大于利，因为只要有对表的更新，就会导致表上的所有查询缓存被清空。所以，MySQL8.0 版本直接将查询缓存删掉了。

这里说的查询缓存是 server 层的，也就是 MySQL8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer poll。

2.4、解析 SQL

在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由解析器来完成。解析器可以将输入的 SQL 语句转换为计算机可以理解的形式（语法树，Syntax Tree）。

解析器会做如下两件事情：

• 词法解析：MySQL 会根据输入的字符串识别出关键字出来，构建出 SQL 语法树；
• 语法解析：根据词法分析的结果，语法分析器会根据语法规则，判断输入的 SQL 语句是否满足语法规则。

语法树大致结构如下：

当词法分析和语法分析出错时，分析器会抛出异常。比如语法结构出错、出现了无法识别的字符等。

表或者字段不存在，并不是在分析器里做的，而是在预处理阶段完成。

2.5、执行 SQL

每条 SQL 语句主要可以分为以下这三个阶段：① prepare ，预处理阶段；② optimize ，优化阶段；③ execute ，执行阶段。

预处理器：检查 SQL 查询语句中的表或者字段是否存在；将 select * 中的 * 符号，扩展为表上的所有字段；

优化器：化器会根据语法树制定多个执行计划，然后确定最优的执行计划。

• 在表里存在多个索引的时候，决定使用哪个索引；
• 在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器：判断用户权限，然后根据执行计划执行 SQL 语句。

2.6、SELECT 查询过程

总结一下一条查询 SQL 语句的执行流程：

1. 客户端通过连接器连接 MySQL 服务；
2. 连接成功后向 SQL 接口发送 SQL 语句请求；
3. SQL 接口接收到 SQL 查询语句会先去缓存查询，如果命中返回给客户端，否则交给解析器；
4. 解析器在拿到 SQL 语句后会判断语法是否正确，正确会生成 SQL 语法树交给优化器，否则报错给客户端；
5. 优化器会根据 SQL 语法树生成一个最优的执行计划交给执行器执行；
6. 执行器拿到执行计划调用存储引擎来获取数据响应给客户端；
7. 完成！！！

---

3、UPDATE 语句执行原理

在数据库里面，我们说的 update 操作其实包括了更新、插入和删除。如果大家有看过 MyBatis 的源码，应该知道 Executor 里面也只有 doQuery() 和 doUpdate() 的方法，没有 doDelete() 和 doInsert()。

3.1、缓冲池

首先，InnnoDB 的数据都是放在磁盘上的，InnoDB 操作数据有一个最小的逻辑单位，叫做页（索引页和数据页）。我们对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了。InnoDB 使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫 Buffer Pool.

下一次读取相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。

修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB 里面有专门的后台线程把 BufferPool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

BufferPool 是 InnoDB 里面非常重要的一个结构，它的内部又分成几块区域。这里我们趁机到官网来认识一下 InnoDB 的内存结构和磁盘结构。

3.2、InnoDB 内存结构和磁盘结构

BufferPool 主要分为3个部分：Buffer Pool、Change Buffer、AdaptiveHash Index，另外还有一个（redo）logbuffer。

3.2.1、BufferPool

BufferPool 缓存的是页面信息，包括数据页、索引页。查看服务器状态，里面有很多跟 BufferPool 相关的信息：

SHOW STATUS LIKE '%innodb_buffer_pool%';

这些状态都可以在官网查到详细的含义，用搜索功能。

BufferPool 默认大小是 128M（134217728字节），可以调整。查看参数（系统变量）：

SHOW VARIABLES like' %innodb_buffer_pool%';

这些参数都可以在官网查到详细的含义，用搜索功能。

内存的缓冲池写满了怎么办？InnoDB 用 LRU 算法来管理缓冲池（链表实现，不是传统的 LRU，分成了Younf 和 Old），经过淘汰的数据就是热点数据。

内存缓冲区对于提升读写性能有很大的作用。思考一个问题：当需要更新一个数据页时，如果数据页在 BufferPool 中存在，那么就直接更新好了。否则的话就需要从磁盘加载到内存，再对内存的数据页进行操作。也就是说，如果没有命中缓冲池，至少要产生一次磁盘 IO，有没有优化的方式呢？

3.2.2、ChangeBuffer

如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）的执行速度。

这一块区域就是 ChangeBuffer。5.5 之前叫 InsertBuffer 插入缓冲，现在也能支持 Delete 和 Update。

最后把 ChangeBuffer 记录到数据页的操作叫做 merge。什么时候发生 merge？有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库 shutdown、redolog 写满时触发。

如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 ChangeBuffer（写缓冲）。写多读少的业务，调大这个值：

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

代表 ChangeBuffer 占 BufferPool 的比例，默认 25%。

3.2.3、Log Buffer

思考一个问题：如果 BufferPool 里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据丢失。如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用。

为了避免这个问题，InnoDB 把所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作（实现 crash-safe）——用它来实现事务的持久性。

这个文件就是磁盘的 Redo Log（叫做重做日志），对应于 /var/lib/mysql/ 目录下的 ib_logfile0 和 ib_logfile1，每个 48M。

这种日志和磁盘配合的整个过程 ，其实就是 MySQL 里的 WAL 技术（Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。

show variables like 'innodb_log%';

问题：同样是写磁盘，为什么不直接写到 db file 里面去？为什么先写日志再写磁盘？

我们先来了解一下随机 I/O 和顺序 I/O 的概念：磁盘的最小组成单元是扇区，通常是 512 个字节。操作系统和磁盘打交道，读写磁盘，最小的单位是块 Block。

如果我们所需要的数据是随机分散在不同页的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，依次进行此过程直到找完所有数据，这个就是随机 IO，读取数据速度较慢。

假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序 IO。

刷盘（将内存中的数据写入磁盘）是随机 I/O，而记录日志是顺序 I/O，顺序 I/O 效率更高。因此先把修改写入日志，可以延迟刷盘时机，进而提升系统吞吐。

当然 Redo Log 也不是每一次都直接写入磁盘，在 Buffer Pool 里面有一块内存区域（Log Buffer）专门用来保存即将要写入日志文件的数据，认 16M，它一样可以节省磁盘 IO.

需要注意：Redo Log 的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自 bufferpool。Redo Log 写入磁盘，不是写入数据文件。那么，Log Buffer 什么时候写入 log file？在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush 就是把操作系统缓冲区写入到磁盘。

Redo Log 的特点：

1. Redo Log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有；
2. 不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志；
3. Redo Log 的大小是固定的，前面的内容会被覆盖。

除了 Redo Log之外，还有一个跟修改有关的日志，叫做 Undo Log（撤销日志或回滚日志），记录了事务发生之前的数据状态，分为 insert Undo Log 和 update Undo Log。如果修改数据时出现异常，可以用 Undo Log 来实现回滚操作（保持原子性）。

3.3、UPDATE 更新过程

有了 Redo Log 和 Undo Log，我们来总结一下一个 Update 操作的流程。

UPDATE user set name = 'lizhengi' where id=1;

1. 在执行前需要：① 连接器连接数据库；② 分析器通过词法分析和语法分析知道这是一条更新语句；③ 优化器决定要使用的索引等；④ 执行器负责具体的执行过程；

2. 事务开始，从内存（buffer poll）或磁盘（data file）取到包含这条数据的数据页，返回给 Server 的执行器；

3. Server 的执行器修改数据页的这一行数据的值为 lizhengi；

4. 记录 name=lisa（原值）到 Undo Log；

5. 记录 name=lizhengi 到 Redo Log；

6. 调用存储引擎接口，记录数据页到 buffer pool（修改 name= lizhengi）；

7. 事务提交。