【MySQL逻辑架构】一条SQL查询语句到底是如何执行的？

Sma2tArt
156 阅读8分钟

当我们输入下面这一条查询语句的时候，会得到表table中满足条件field=value的所有数据。那么，这条语句在MySQL内部的执行过程是怎样的呢？那么请跟随本文逐步拆解各个过程，让你对MySQL的逻辑架构从入门到入库。

SELECT * FROM table WHERE field = value;
  • 注意：下文逻辑架构若不特意说明，默认指MySQL的逻辑架构。

逻辑架构概述

先上两张图，大家感受一下SQL语句在MySQL的各个功能模块中的执行过程。

简图

image-20230205143833874.png

详细图

image-20230205143913135.png

如图所示，MySQL逻辑架构大致分为三层：1.连接层；2.Server层；3.存储引擎层。其中Server层包括连接器、查询缓存、分析器、优化器、执行器等。

简要描述一下提交SQL语句后，MySQL的执行流程

  1. 客户端先与服务器建立TCP连接，建立成功后， MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。然后从连接池中分配给一个线程专门与这个客户端的交互，完成相应的请求。
  2. 然后先去缓存中查询，如果缓存中没有，则会进入到解析器。
  3. 解析器会对 SQL 语句进行词法分析、语法分析，生成语法树，然后进入到优化器。
  4. 优化器对查询的sql进行优化，生成一个执行计划，交给执行器去执行。
  5. 执行器调用存储引擎中的API，对数据进行存储和提取，并且将结果进行缓存【缓存在MySQL8.0后废弃】。

逻辑架构详解

简化为三层结构：

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

连接层

连接层负责跟客户端建立连接、获取权限、维持和管理连接。

我们在交互模式下输入最常见的mysql -u[user] -p[password]连接命令后，会与MySQL服务端建立连接。经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。

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

服务层

1）查询缓存

之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到分析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能，主要原因如下：

  • 两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。因此 MySQL 的查询缓存命中率不高。

查询缓存中的缓存数据是在查询执行引擎返回查询结果的阶段设置的，但不是所有的查询都会被缓存，如下情况是不会被缓存的：

  • 查询语句中如果包含一些不确定的数据时，例如查询语句中包含：NOW()、CURRENT_DATE()等。因为每次执行这类带了不确定数据的查询所返回结果可能是不同的；
  • 如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、 information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存；

缓存失效

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

MySQL5.7查看缓存是否可用：【默认是可用的】

mysql> show variables like '%have_query_cache%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

我们可以修改MySQL的配置文件：/etc/my.cnf，添加配置项：query_cache_type=0;

  • 0代表不使用缓存
  • 1代表使用缓存
  • 2代表按需使用

或者执行如下语句，但不是永久生效的【重启后会重置，具体相关内容读者可去查阅关于系统变量的内容】

set global query_cache_type = 0;

0.MySQL5.7查看缓存是否开启：

mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| OFF                 |
+--------------------+
1 row in set (0.00 sec)

1.配置文件中开启查询缓存

在 /etc/my.cnf 中新增一行：

query_cache_type=1

2.重启mysql服务

mysql> systemctl restart mysqld

3.开启查询计划

mysql> set profiling=1;

4.执行两次相同查询语句

mysql> select * from t_student;
mysql> select * from t_student;

5.查看profiles

显示执行计划，查看程序的执行步骤：

mysql> show profiles;   # 显示最近的几次查询
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00116425 | select * from t_student |
|        2 | 0.00014550 | select * from t_student |
+----------+------------+-------------------------+

6.指定Query_ID查询执行计划，即程序的执行步骤

image-20230207143954372.png

image-20230207144051981.png

执行编号2时，比执行编号1时少了很多信息，从截图中可以看出查询语句直接从缓存中获取数据。

2）分析器

在分析器中对 SQL 语句进行词法分析、语法分析。

词法分析： 你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

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

select department_id,job_id,avg(salary) from employees group by department_id;

若SQL语句正确，生成下面这样一个语法树

image-20230205152539591.png

如果语法有问题，会收到错误信息

mysql> select * from 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 'fromuser where id=1' at line 1

3）优化器

经过分析器处理之后，得到了语法树。这个时候MySQL已经明确知道自己要做什么了，但是在开始执行之前还会对sql进行优化，确定SQL语句的最优执行路径，生成一个执行计划。

优化内容： 重写查询、决定表的读写顺序、选择合适的索引等。

案例：

select * from t1 join t2 using(ID) where t1.name='lilei' and t2.name='mysql';
  • 方案1：可以先从表 t1 里面取出 name='lilei'的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 name的值是否等于 'mysql'。
  • 方案2：可以先从表 t2 里面取出 name='mysql' 的记录的 ID 值，再根据 ID 值关联到 t，再判断 t 里面name的值是否等于 lilei。 这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

4）执行器

执行器在执行sql语句之前，需要判断该用户是否具备操作这张表的权限。如果没有，就会返回权限错误。如果具备权限，就执行 SQL查询并返回结果

执行器的执行流程如下：

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

至此，这个语句就执行完成了。

存储引擎层

存储引擎层负责数据的存储和提取。存储引擎是插件式的，支持InnoDB、MyISAM、Memory等多种存储引擎，MySQL也提供了一些第三方的存储引擎，这种插件式的结构设计，使得不同的公司可以根据自己的需求选择不同的引擎。

MySQL5.7支持的存储引擎如下所示

image-20230205154128020.png

让我们来看一下t_student表的信息

mysql> show table status like 't_student' \G
*************************** 1. row ***************************
           Name: t_student  #表名字
         Engine: InnoDB  #存储引擎类型
        Version: 10
     Row_format: Compact   #行格式
           Rows: 8
 Avg_row_length: 2048      #平均每行记录的字节数
    Data_length: 16384     #表中数据的大小[字节]
Max_data_length: 0
   Index_length: 16384     #索引大小
      Data_free: 8388608
 Auto_increment: 26        #下一个自增的值
    Create_time: 2022-03-18 11:03:04
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci    #字符集
       Checksum: NULL               #校验和
 Create_options:
        Comment:                    
1 row in set (0.01 sec)

关于存储引擎的详细内容，后续会进行介绍。

avatar
文章
阅读
粉丝