MySQL进阶 - 第5章 逻辑架构

---

上篇：第四章、配置文件与系统变量
下篇：第六章、存储引擎

本文内容主要源于：bilibili-尚硅谷-MySQL高级篇

一、逻辑架构的剖析

---

1.1 服务器处理客户端请求

MySQL 是典型的 C/S 架构，即 Client/Server 架构，服务器程序使用的是 mysqld。

不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一段文本（SQL）语句，服务器进程处理后再向客户端进程发送一段文本（处理结果）。

那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？这里以查询请求为例展示：

具体流程（针对于 MySQL-5.7）：

在 MySQL-8.0 版本中 缓存 被移除了

---

1.2 客户端连接器

客户端连接器（Connectors）指的是不同语言中与 SQL 的交互，MySQL 首先是一个网络程序，在 TCP 之上定义了自己的应用层协议，所以要使用 MySQL，可以编写代码跟 MySQL 建立 TCP 连接，之后按照其定义好的协议进行交互或者比较方便的办法是调用 SDK，比如 Native C API、JDBC、PHP 等各个语言 MySQL Connector，或者通过 ODBC，但通过 SDK 来访问 MySQL 本质上还是在 TCP 连接上通过 MySQL 协议跟 MySQL 进行交互。

---

1.3 MySQL Server 的三层架构

简化为三层结构：

• ① 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端
• ② SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关
• ③ 存储引擎层：与数据库文件打交道，负责数据的存储和读取

---

1.3.1 第一层：连接层

系统（客户端）访问 MySQL 服务器前做的第一件事就是建立 TCP 连接

经过三次握手建立连接成功后，MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取

• 用户名或密码不对，会收到一个 Access denied for user 错误，客户端程序执行结束
• 用户名密码认证通过后，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑都将依赖于此时读到的权限

一个系统只会和 MySQL 服务器建立一个连接吗？只能有一个系统和 MySQL 服务器建立连接吗？

当然不是，多个系统都可以和 MySQL 服务器建立连接，每个系统建立的连接肯定不止一个，所以为了解决 TCP 无限创建与 TCP 频繁创建销毁带来的资源耗尽、性能下降问题。MySQL 服务器有专门的 TCP 连接池 限制连接数。

采用 长连接模式复用 TCP 连接来解决上述问题。

TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互，所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

所以 连接管理 的职责就是负责管理连接、账号认证和获取权限信息。

---

1.3.2 第二层：服务层

第二层架构主要完成大多数的核心服务功能，如 SQL 接口，并完成 缓存的查询，SQL 的分析和优化及部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如过程、函数等。

在该层，服务器会 解析查询 并创建相应的内部 解析树，并对其完成相应的 优化，如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作

如果是 SELECT 语句，服务器还会 查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

• SQL Interface：SQL 接口
  • 接收用户的 SQL 命令，并且返回用户需要查询的结果，比如 SELECT ... FROM 就是调用 SQL Interface
  • MySQL 支持 DML（数据操作语言），DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种 SQL 语言接口
• Parser：解析器
  • 在解析器中对 SQL 语句进行语法分析、语义分析，将 SQL 语句分解成数据结构、并将这个结构传递到后续步骤，以后 SQL 语句的传递和处理就是基于这个结构。如果在分解构成中遇到错误，那么说明这个 SQL 语句是不合理的
  • 在 SQL 命令传递到解析器的时候会被解析器验证和解析，并为其创建 语法树，并根据数据字典丰富查询语法树，会 验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL 还会对 SQL 查询进行语法上的优化，进行查询重写
• Optimizer：查询优化器
  • SQL 语句在语法解析之后，查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划
  • 这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户
  • 它使用 选取-投影-连接 策略进行查询，例如：SELECT id,name FROM student WHERE gender = '女';，这个 SELECT 查询会根据 WHERE 语句进行 选取，而不是将表全部查询出来以后再进行 gender 过滤，根据 id 和 name 进行属性 投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件 连接 起来生成最终查询结构
• Cache & Buffers：查询缓存组件
  • MySQL 内部维持着一些 Cache 和 Buffer，比如 Query Cache 用来缓存一条 SELECT 语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端
  • 这个缓存机制是由一系列小缓存组成的，比如表缓存、记录缓存、key 缓存、权限缓存等
  • 这个查询缓存可以在 不同客户端之间共享
  • 从 MySQL-5.7.20 开始，不推荐使用查询缓存，并在 MySQL-8.0 中删除

---

1.3.3 第三层：存储引擎层

和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎 架构将查询处理和其它的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。同时开源的 MySQL 还允许 开发人员设置自己的存储引擎。

这种高效的模块化架构为那些希望专门针对特定应用程序需求（例如数据仓库、事务处理或可用性情况）的人提供了巨大的好处，同时享受使用一组独立于任何接口和服务的优势存储引擎。

插件式存储引擎层（Storage Engines）真正的负责了 MySQL 中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样可以根据自己的实际需要进行选取。

可以用以下命令查看 MySQL 所支持的存储引擎

SHOW ENGINES;

MySQL 8.0 默认支持的存储引擎如下：

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

所有的数据、数据库、表的定义，表的每一行的内容、索引，都是存在 文件系统 上，以 文件 的方式存在的，并完成与存储引擎的交互。当然有些存储引擎比如 InnoDB，也支持不使用文件系统直接管理该设备，但现代文件系统的实现使得这样做没有必要了，在文件系统之下，可以使用本地磁盘，可以使用 DAS、NAS、SAN 等各种存储系统。

---

二、SQL 执行流程

---

2.1 MySQL 中的 SQL 执行流程

MySQL 的查询流程：

• ① 查询缓存
• ② 解析器
• ③ 优化器
• ④ 执行器

1. 查询缓存：Server如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL 8.0 之后就抛弃了这个功能。

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句，之前执行过的语句及其结果可能会以 key-value 键值对的形式被直接缓存在内存中，key 是查询语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中，所以，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

但是在大多数情况下查询缓存就是个鸡肋，因为查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果，需要说明的是，在 MySQL 中的查询缓存，不说缓存查询计划，而是查询对应的结果。这就意味着查询匹配的 鲁棒性大大降低，只有 相同的查询操作才会命中查询缓存。两个查询请求在任何字符上的不同（例如：空格、注释、大小写等），都会导致缓存不会命中，因此 MySQL 查询缓存命中率不高。

比如：

SELECT id,name FROM t_user where id = 1;

# 即使该 SQL 只比上一条 SQL 多一个空格，也会未命中缓存
SELECT id, name FROM t_user where id = 1;

同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表、如 mysql、information_schema、performance_schema 数据库中的表，那个请求就不会被缓存。以某些系统函数为例，可能同样的函数的两次调用会产生不一样的结果，比如说函数 NOW()，每次调用都会产生最新的当前时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的。

此外，既然是缓存，那就有它 缓存失效的时候。MySQL 的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了 INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE 或 DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除，对于 更新压力大的数据库 来说，查询缓存的命中率会非常低。

总之，因为查询缓存往往弊大于利，查询缓存的失效非常频繁

一般建议在静态表里使用查询缓存，什么叫 静态表 呢？就是一般极少更新的表。比如，一个系统配置表、字典表、这张表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种 按需使用 的方式，可以将 my.cnf 参数 query_cache_type 设置成 DEMAND，代表当 SQL 语句中有 SQL_CACHE 关键词时才缓存，比如：

# query_cache_type 有 3 个值：0 表示关闭查询缓存 OFF，1 代表开启，2 表示 DEMAND
query_cache_type=2

这样对于默认的 SQL 语句都不使用查询缓存，而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显示指定，像下面这个语句一样：

SELECT SQL_CACHE * FROM table_name WHERE ...;

不使用查询缓存也可以写作

SELECT SQL_NO_CACHE * FROM table_name WHERE ...;

查看当前 MySQL 实例是否开启缓存机制

# MySQL-5.7 中，在默认情况下 MySQL 也是关闭的
mysql>SHOW GLOBAL VARIABLES LIKE '%query_cache_type%';
+---------------------------+---------+
| Variable_name             | Vaule   |
+---------------------------+---------+
| Query_cache_type          | OFF     |
+--------------------+----------------+
1 rows in set (0.00 sec)

# MySQL-8.0 中不存在该参数
mysql> SHOW GLOBAL VARIABLES LIKE '%query_cache_type%';
Empty set (0.00 sec)

监控查询缓存命中率：

mysql> SHOW STATUS LIKE '%Qcache%';
+---------------------------+---------+
| Variable_name             | Vaule   |
+---------------------------+---------+
| Qcahce_free_blocks        | 1       |
| Qcahce_free_memory        | 1031832 |
| Qcahce_hits               | 0       |
| Qcahce_inserts            | 0       |
| Qcahce_lowmem_prunes      | 0       |
| Qcahce_not_cached         | 1280    |
| Qcahce_queries_incahce    | 0       |
| Qcahce_total_blocks       | 1       |
+--------------------+----------------+
9 rows in set (0.00 sec)

运行结果分析：

• Qcahce_free_blocks：表示查询缓存中还有多少个剩余的 blocks，如果该值显示较大，则说明查询缓存中的 内存碎片 过多了，可能在一定的时间进行整理
• Qcahce_free_memory：查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA 可以根据实际情况做出调整
• Qcahce_hits：表示有 多少次命中缓存，主要可以通过该值来验证查询缓存的效果，数字越大，缓存效果越理想
• Qcahce_inserts：表示 多少次未命中然后加入，意思是新来的 SQL 请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果 insert 到查询缓存中，这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想，当然系统刚启动后，查询缓存是空的，也很正常
• Qcahce_lowmem_prunes：该参数记录有 多少条查询因为内存不足而被移除 出查询缓存，通过这个值，用户可以适当的调整缓存大小
• Qcahce_not_cached：表示因为 query_cache_type 的设置而没有被缓存的查询数量
• Qcahce_queries_incahce：当前缓存中 缓存的查询数量
• Qcahce_total_blocks：当前缓存的 block 数量

2. 解析器： 在解析器中对 SQL 语句进行语法分析、语义分析。

如果没有命中查询缓存，就要开始真正执行语句了，首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句进行解析，SQL 语句的分析分为 词法分析 和 语法分析。

分析器先做 词法分析，你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

MySQL 从你输入的 select 这个关键字就能判断这是一个查询语句，并把所需要查询的表、列都识别出来

接着要做 语法分析，根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否 满足 MySQL 语法。

如果你的语句不对，就会提示 You have an error in your SQL syntax 的错误提醒，比如下面这个语句将 from 写成 fro

mysql> select * fro t_user where id = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fro t_user where id = 1' at line 1

如果 SQL 语句正确，则会生成一个这样的语法树：

下图是 SQL 语法分析的过程步骤：

至此解析器的工作任务也算基本圆满了，接下来进入到优化器

3. 优化器： 在优化器中会确定 SQL 语句的执行路径，比如是根据 全表检索，还是根据 索引检索 等

经过了解析器，MySQL 就知道你要做什么，在开始执行之前，还要先经过优化器的处理，一条查询可以有很多种执行方式，最后都返回相同的结果，优化器的作用就是找到这其中最好的执行计划。

比如：优化器是在表里面有多个索引的时候，决定使用哪个索引，或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序，还有表达式简化，子查询转为连接，外连接转为内连接等

举例：如下语句是执行两个表的 join：

select * from test1 join test2 using(ID)
where test1.name = 'duojiala' and test2.name = '泰酷啦';

• 方案一：可以先从表 test1 里面取出 name = 'duojiala' 的记录 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name 的值是否等于 泰酷啦
• 方案二：可以先从表 test2 里面取出 name = '泰酷啦' 的记录 ID 值，再根据 ID 值关联到表 test1，再判断 test1 里面 name 的值是否等于 duojiala

这两种执行方式的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案，优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

在查询优化器种，可以分为 逻辑查询 优化阶段和 物理查询 优化阶段

逻辑查询优化就是通过改变 SQL 语句的内容来使得 SQL 查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对 SQL 语句进行 等价变换，对查询进行 重写，而查询重写的数学基础就是关系代数，对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行外连接消除、嵌套连接消除等

物理查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划，在这个阶段里，对于单表和多表连接的操作，需要高效地 使用索引，提升查询效率。

4. 执行器：

截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划，于是就进入了 执行器阶段

在执行之前需要判断该用户是否 具备权限。如果没有，就会返回权限错误，如果具备权限，就执行 SQL 查询并返回结果，在 MySQL-8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

select * from test where id = 1;

如果有权限，就打开表继续执行，打开表的时候，执行器就会根据表的引擎定义，调用存储引擎 API 对表进行的读写，存储引擎 API 只是抽象接口，下面还有个 存储引擎层，具体实现还是看表选择的存储引擎。

比如：表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

• 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 1，如果不是则跳过，如果是则将这行存在结果集中
• 调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行
• 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

至此，这个语句就执行完成了，对于有索引的表，执行的逻辑也差不多

SQL 语句在 MySQL 中的执行流程是：SQL语句 —> 查询缓存 —> 解析器 —> 优化器 —> 执行器。

---

2.2 SQL 执行原理

---

2.2.1 查询资源使用情况

不同的 DBMS 其 SQL 的执行原理是相通的，只是在不同的软件中，各有各的实现路径。

一条 SQL 语句会经历不同的模块，可以用 show profile 命令查看在不同模块中 SQL 执行所使用的资源（时间）。

MySQL 默认情况下是不会开启 profiling 的，可以通过以下命令确认是否开启计划：

select @@profiling;

show variables like '%profiling%';

示例：

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

profiling 为 0 或者 OFF，表示关闭

如果想让 MySQL 收集在 SQL 执行时所使用的资源情况，则需要把 profiling 打开，即设置为 1；

mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

show profile 基本语法：

show profile [type [, type] ...] [for Query_ID] [LIMIT row_count [OFFSET offset]]

type 可设置的值有：

• ALL：显示所有参数的开销信息
• BLOCK IO：显示 IO 的相关开销
• CONTEXT SWITCHES：上下文切换相关开销
• CPU：显示 CPU 相关开销信息
• IPC：显示发送和接收相关开销信息
• MEMORY：显示内存相关开销信息
• PAGE FALUTS：显示页面错误相关开销信息
• SOURCE：显示 Source_function，Source_file，Source_line 相关的开销信息
• SWAPS：显示交换次数相关的开销信息

展示所有 SQL 语句的简要耗时情况：

show profiles;

示例：

mysql> show profiles;
+----------+------------+----------------------------+
| Query_ID | Duration   | Query                      |
+----------+------------+----------------------------+
|        1 | 0.00030500 | select * from t_access_log |
|        2 | 0.00027300 | select * from t_access_log |
+----------+------------+----------------------------+
2 rows in set, 1 warning (0.00 sec)

查询最近一条 SQL 的详细耗时信息

show profile;

示例：

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000066 |
| Executing hook on transaction  | 0.000004 |
| starting                       | 0.000008 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000032 |
| init                           | 0.000006 |
| System lock                    | 0.000009 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000013 |
| preparing                      | 0.000015 |
| executing                      | 0.000068 |
| end                            | 0.000003 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000007 |
| closing tables                 | 0.000007 |
| freeing items                  | 0.000012 |
| cleaning up                    | 0.000010 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

这里默认查询的就是 Query_ID = 2 的这条 SQL

当然也可以查询指定 SQL 的执行过程，语法如下：

show profile for query Query_ID;

示例：

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000064 |
| Executing hook on transaction  | 0.000006 |
| starting                       | 0.000008 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000031 |
| init                           | 0.000005 |
| System lock                    | 0.000010 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000014 |
| preparing                      | 0.000015 |
| executing                      | 0.000070 |
| end                            | 0.000016 |
| query end                      | 0.000004 |
| waiting for handler commit     | 0.000008 |
| closing tables                 | 0.000007 |
| freeing items                  | 0.000020 |
| cleaning up                    | 0.000020 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

如果想要查询 CPU、io 阻塞等参数情况，可以写作：

# 查看指定 SQL 的 CPU、io 阻塞等参数情况 
mysql> show profile cpu,block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000064 | 0.000062 |   0.000000 |            0 |             0 |
| Executing hook on transaction  | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
| starting                       | 0.000008 | 0.000008 |   0.000000 |            0 |             0 |
| checking permissions           | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.000031 | 0.000021 |   0.000009 |            0 |             0 |
| init                           | 0.000005 | 0.000003 |   0.000002 |            0 |             0 |
| System lock                    | 0.000010 | 0.000006 |   0.000004 |            0 |             0 |
| optimizing                     | 0.000004