MySql逻辑架构及查询过程

桃扇骨 2022-11-05 08:57 220阅读 0赞

**目录**

1.MySQL逻辑架构图

1.1 连接层

1.2 服务层

1.3 引擎层

1.4 存储层

2.MySQL查询过程

2.1 客户端/服务端通信协议

2.2 查询缓存

2.3 语法解析和预处理

2.4 查询优化

2.5 查询执行引擎

2.6 返回结果给客户端

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

## **1.MySQL逻辑架构图** ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NzU2Njgy_size_16_color_FFFFFF_t_70][]

##      1.1 连接层 ##

**Connectors：**最上层是一些客户端和连接服务。

##      1.2 服务层 ##

**Management Serveices & Utilities：** 系统管理和控制工具

** Connection Pool： **每一个客户端发起一个新的请求都由服务器端的线程处理工具负责接收客户端的请求并开辟一个新的内存空间，在服务器端的内存中生成一个新的线程，当每一个用户连接到服务器端的时候就会在进程地址空间里生成一个新的线程用于响应客户端请求，用户发起的查询请求都在线程空间内运行， 结果也在此线程内缓存并返回给服务器端。线程的重用和销毁都是由线程处理管理器实现的。

** ****SQL Interface： **接受用户的SQL命令，并且返回用户需要查询的结果。

** ****Parser：**解析器，将SQL语句进行语义和语法分析，按照不同的操作类型进行分类后做出针对性的转发到后续步骤。

** ****Optimizer：**查询优化器，SQL语句在执行前MySql会使用查询优化器对查询语句进行优化，根据客户端请求的query语句和数据库中的一些统计信息，在一系列算法的基础上进行分析后得出一个最优的策略，告诉后面的程序如何取得这个query语句的结果。

** Cache和Buffer：**查询缓存，主要功能是将客户端提交给MySQL的select请求的返回结果集缓存到内存中，该查询语句所查询的中的数据或结构发生任何变化后， MySQL会使该查询语句的缓存失效。在读写比例非常高的应用系统中， 查询缓存对性能的提高是非常显著的，当然也增加了对内存的消耗。

##      1.3 引擎层 ##

** Pluggable Storage Engines：**存储引擎接口真正的负责了MySQL中数据的存储和获取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，MySQL支持存储引擎的可插拔，这样我们可以根据自己的实际需要进行选取。

##      1.4 存储层 ##

**数据存储层：**主要将数据存储在运行于裸设备的文件系统上，并完成于存储引擎的交互。

## 2.**MySQL查询过程** ##

MySQL查询优化实质说白了就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行，从而达到不同的业务目标需要实现的效果。下图展示了MySQL的查询过程。

![SouthEast][]

##      2.1 客户端/服务端通信协议 ##

MySQL客户端/服务端通信协议是"**半双工**"的：**在任意时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。**一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以**当查询语句很长的时候，需要设置max\_allowed\_packet参数**。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

与之相反的是，**服务器响应给用户的数据通常会很多**，由多个数据包组成。但是**当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送**。因而在实际开发中，**尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯**，这也是查询中尽量避免使用SELECT \*以及加上LIMIT限制的原因之一。

##      2.2 查询缓存 ##

在解析一个查询语句前，如果**查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据**。**如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行**。

MySQL将缓存存放在一个引用表（类似于HashMap的数据结构）中，通过哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以**两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中**。

**如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存**。比如函数NOW()或者CURRENT\_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT\_USER或者CONNECION\_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

**MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效**。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外。如果查询结果被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。基于此，我们知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

1）多个小表代替一个大表（注意不要过度设计）

2）批量插入代替循环单条插入，降低磁盘IO次数

3）合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适

4）可以通过SQL\_CACHE和SQL\_NO\_CACHE来控制某个查询语句是否需要进行缓存

##      2.3 **语法解析和预处理** ##

MySQL通过关键字将SQL语句进行解析并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析，比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法，比如检查要查询的数据表和数据列是否存在等等。

##      2.4 **查询优化** ##

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。**多数情况下一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划**。MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last\_query\_cost的值来得到其计算当前查询的成本。 **有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样，我们希望执行时间尽可能短，但MySQL只选择它认为成本小的，但成本小有的时候并不是我们的预期。**

##      2.5 **查询执行引擎** ##

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，**MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等**。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

##      **2.6 ****返回结果给客户端** ##

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。**结果集返回客户端是一个增量且逐步返回的过程**。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，**结果集中的每一行都会以一个满足客户端/服务器通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送**。

# #

本文总结自：[https://www.bilibili.com/video/BV12b411K7Zu?p=189][https_www.bilibili.com_video_BV12b411K7Zu_p_189]

借鉴自：[https://www.cnblogs.com/andy6/p/5789254.html][https_www.cnblogs.com_andy6_p_5789254.html]

[https://zhuanlan.zhihu.com/p/105049719][https_zhuanlan.zhihu.com_p_105049719]

[https://blog.csdn.net/fuzhongmin05/article/details/70904190][https_blog.csdn.net_fuzhongmin05_article_details_70904190]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NzU2Njgy_size_16_color_FFFFFF_t_70]: /images/20221023/e5ccfad1be084961b5a2055e45c946bc.png
[SouthEast]: /images/20221023/2f0b2d8fdfbb4d5c9ea7c0c2e2c5e3e1.png
[https_www.bilibili.com_video_BV12b411K7Zu_p_189]: https://www.bilibili.com/video/BV12b411K7Zu?p=189
[https_www.cnblogs.com_andy6_p_5789254.html]: https://www.cnblogs.com/andy6/p/5789254.html
[https_zhuanlan.zhihu.com_p_105049719]: https://zhuanlan.zhihu.com/p/105049719
[https_blog.csdn.net_fuzhongmin05_article_details_70904190]: https://blog.csdn.net/fuzhongmin05/article/details/70904190