MySQL-逻辑架构

MySQL-逻辑架构

1.逻辑架构剖析

1.1服务器处理客户端请求

MySQL属于典型的C/S架构，客户端进程发送请求，服务端进程处理请求。处理的基本流程如下。

MySQL的逻辑架构如下：

Connectors：与服务端程序连接的程序。

Manager Services & Utilties：基础服务组件；

Connection Pool：提供多个客户端和服务端交互的线程并进行管理；

SQL Interface：接收SQL指令，返回查询结果；

Parser：语法分析，语义分析，生成语法树；

Optimizer：核心组件，对SQL进行优化；

Caches & Buffers：查询缓存，以键值对方式缓存查询结果；

Pluggable Storage Engines：存储和组织数据的方式；

File System ：文件系统；

查询数据的流程：

Connectors-> Connection Pool ->SQL Interface->Cache & Buffers->Parser->Optimizer->Pluggable Storage Engines->File System-> Cache & Buffers -> SQL Interface

1.2连接层

访问MySQL服务前，首先需要建立TCP连接，并对客户端传输的账号密码进行身份认证、权限获取。

1. 用户名或密码错误：拒绝连接数据库。
2. 认证通过，根据权限表查询用户权限，后面的权限判断逻辑，都依赖于该查询结果。

MySQL服务器可以和多个客户端进行连接，为了避免无限制创建TCP连接将系统资源消耗尽。MySQL中提供TCP连接池限制连接数。采用长连接的方式复用TCP连接。

TCP收到请求后，需要线程对请求进行处理，因此还需要有线程池，执行后续流程。

1.3服务层

提供了大部分核心服务功能，并完成缓存的查询，该层会将SQL语句解析查询并创建相应的内部解析树，并进行优化。

1.4引擎层

负责对数据的存储和提取，MySQL服务器通过API与引擎进行通信并查询结果。

不同的存储引擎实现的功能不同，可以根据具体的需求，选择存储引擎。

2.SQL的执行流程

2.1查询缓存

如果在查询缓存中发现一条和当前查询完全相同的SQL语句，就会将结果直接返回给客户端；如果没有则进入解析器阶段。但是查询缓存效率往往不高，因此在MySQL之后抛弃了该功能。

 -- 查询是否开启缓存
 show global  variables like '%query_cache_type%';
-- 0（OFF）：关闭查询缓存 1：开启缓存（ON）  2：按需使用（DEMAND）
SET GLOBAL query_cache_type=1;
-- 显示指定使用查询缓存
SELECT SQL_CACHE * FROM t1 where id = 1;
-- 查看缓存的状态
SHOW STATUS LIKE '%Qcache%';
1
2
3
4
5
6
7
8

2.2解析器

分析器先做“词法分析”，MySQL需要识别里面的字符串分别是什么，代表什么（什么是关键字、列名、表名等）。然后做词法分析，根据词法分析的结果，语法分析器会根据语法规则，判断输入的SQL语句是否满足语法要求，如果检查出语句不对，会返回"You hava an error in your SQL syntax"的错误提醒。

2.3优化器

一条语句查询可以有很多种执行方式，返回的结果都相同，但是时间不一定相同，因此优化器会对SQL进行优化，在优化器中会确定SQL语句的执行路径，比如是根据全表索引，还是根据索引检索等。

物理查询优化：通过索引和表连接方式等技术来进行优化。

逻辑查询优化：通过SQL等价变换提升查询效率。

2.4执行器

执行之前需要判断用户是否具备权限，如果没有，则返回权限错误。如果具备权限，就执行SQL查询并返回结果。如果设置了查询缓存，还会将查询结果进行缓存。

2.5预处理器

当我们频繁执行SQL时，可能一些SQL语句长的都很相似。例如下面两条SQL语句。

SELECT * FROM user WHERE id = 1;
SELECT * FROM user WHERE id = 2;
1
2

如果每一次都要对这种类似的语句进行解析会比较浪费时间，因此可以采用预编译的方式将会发送动态变化的值用占位符进行代替，将SQL语句模板化，通常称这类语句叫Prepared Statements。

-- 创建预处理SQL语句,名字可以自定义
prepare select_product from 'select * from product where id = ?';
-- 设置一个变量
set @id = 1;
-- 执行预处理SQL语句
 execute select_product using @id;
-- 根据名字删除预处理语句
drop prepare select _product;
1
2
3
4
5
6
7
8

2.6MySQL的执行原理

MySQL中的profiling参数，标记了当前是否开启记录SQL语句的执行信息。

select @@profiling;
-- 0代表OFF，1代表ON
set profiling = 1;
-- 查看已经执行的SQL语句情况
show profiles;
-- 查看指定行的sql语句执行情况
show profile for query 序号;
1
2
3
4
5
6
7

可以通过查询SQL语句的执行情况，检查SQL语句的性能如何。