01 基础架构：一条 SQl 查询语句是如何执行的？

最新推荐文章于 2024-09-17 09:10:57 发布

翊无忧

最新推荐文章于 2024-09-17 09:10:57 发布

阅读量108

点赞数

分类专栏： MySQL 实战45讲学习笔记文章标签： mysql

本文链接：https://blog.csdn.net/wmt0501/article/details/124122489

版权

MySQL 实战45讲学习笔记专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.1、Mysql 基本逻辑架构示意图

在这里插入图片描述

1.1.1 Mysql 分层

大体来说，Mysql 可以分为两层：Server 层，存储引擎层。
Server 层

组成成分：连接器，查询缓存，分析器，优化器，执行器等
功能：蕴含了 Mysql 大多数的核心服务功能，以及所有的内置函数（如日期，时间，数学和加密函数等），所有的跨存储引擎的功能都在这一层实现，比如存储过程，触发器，视图等。
存储引擎层
负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB，MyISAM，Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，该存储引擎是从 MySQl 5.5.5 版本开始成为了默认的存储引擎。也就是说，在 Mysql 中创建表时如果未指定存储引擎，那么默认使用的时 InnoDB。不过可以在 create table 后面使用 engine=memory/(别的存储引擎)来指定，不同的存储引擎其表数据的存储方式不同，支持的功能也不同。
不同的存储引擎共用同一个 Server 层。

1.2、连接器

1.2.1功能

1.负责与客户端建立连接，获取权限，维护和管理连接。
2.连接命令
- mysql -h [ip] -P[port] -u[user] -p
- 可以选择在 -p后面添加密码，注意此处不要空格，否则将空格也视为密码的一部分，不过不建议使用这种方式登录，这样的方式输入密码容易导致密码泄露，而是在输入 -p 之后换行在输入密码，此时是看不见输入的密码的，用来保护密码。
3.身份认证和权限验证
- 连接命令中的 Mysql 是客户端工具，用来跟服务器建立连接，之后再经历客户端和服务器经典的三次 TCP 握手之后，连接器就开始认证输入的用户身份，如果用户名或者密码不正确，那么就会收到一个 “ Access denied for user ”（用户访问被拒绝）的错误提示，然后客户端程序结束执行；如果用户名和密码认证通过，连接器会到权限表中获取到该用户所拥有的权限，之后的权限判断逻辑都将依赖于此时读取到的权限，再这个连接还未断开期间，即使使用管理员账号对该用户账号的权限进行修改也不会影响到已经建立起连接的用户权限，只会对之后的该用户的连接的权限进行更新。
4.超长时间未操作断开连接
- 如果建立连接后，没有后续操作，那么该连接中的 Command(命令) 列为 Sleep，表明这是系统中的空闲连接。如果客户端长时间没有任何操作，超过了其参数 wait_timeout 设置的值（默认是8小时），那么连接就会自动断开。如果连接自动断开后，客户端再次发送请求时会收到 “ Lost connection to Mysql server during query ” （查询期间与服务器连接中断）的错误提示，这时候需要重新与服务器建立连接才能执行客户端发送的执行请求。
5.长短连接利弊及其解决方案
- 数据库中，长连接是指连接成功后，如果客户端持续有执行请求，则一致使用同一个连接；短连接是指每次执行少量几次查询就断开连接，下次查询需要重新与服务器建立连接。由于连接连接的过程较为复杂且耗时，因此要减少建立连接的次数，尽量使用长连接。
- 如果全部使用长连接，可能会出现 Mysql 占用内存增长过快，其原因是因为 Mysql 再执行过程中临时使用的内存是管理在连接对象里面的，然而这些资源只有在连接断开的时候才会释放，如果长连接累积下来，可能会导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 Mysql 异常重启。
- 解决方案
  - 1.定期断开长连接，使用一段时间后，或者程序里面判断执行过一个占用内存较大的查询后，则断开连接，之后需进行执行查询请求则需要重新建立连接。
  - 2.如果使用的是 Mysql5.7 以及更新的版本时，可以在执行一个占用内存较大的查询操作后，通过执行 mysql_reset_connection 来重新的初始化连接资源，该过程不需要重新建立连接，也不需要重新进行权限验证，即可将连接恢复到最初建立连接的状态。

1.3、查询缓存

连接建立完成后，就可以执行 select 语句了，执行逻辑就会来到第二步：查询缓存。
在查询缓存的查询步骤
a. Mysql 拿到一个查询请求之后，会先到查询缓存中查看，之前是不是执行过该查询语句，而查询缓存中会以 key-value 的形式保存已经查询过的查询语句的结果，其中 key 保存的是 SQL 查询语句，而 value 保存的是查询结果。如果能在查询缓存中能找到要执行的 SQL 查询语句在查询缓存中所对应的 key，那么之后直接将其对应的 value 返回即可。如果没有在查询缓存中查找到查询 SQL 语句对应的 key，那么就继续后面的执行阶段，最后将其执行的 SQL 作为 key，其查询结果作为 value 的形式保存在查询缓存中。可以很明显的看出如果查询命中的话，那么其查询的效率会提高很多。
但是大多数情况下建议不要使用查询缓存，因为查询缓存弊大于利。
- 查询缓存失效非常的频繁，常常会花费大量的时间和系统资源将查询后的结果缓存在查询缓存中，可以一旦查询缓存中保存的查询语句对应的表做了更新，那么这个对应的查询缓存就会因为失效而从查询缓存中清空。对于更新较为频繁的数据库而言，查询缓存的命中率非常低，除非是一张静态表（系统配置表），因为长时间不会进行更新，才比较适用于查询缓存。
- 在 Mysql 中可以根据需求选择性的使用查询缓存，将参数 query_cache_type(查询缓存类型) 设置为 DEMAND(demand需求)，这样默认的 SQL 语句不使用查询缓存，对于有需求使用查询缓存的可以在 SQL 语句中可以使用 SQL_CACHE 显式的指定：select SQL_CACHE * from T where ID=1;
- 需要注意的是：在 Mysql8.0 版本直接将查询缓存这整个功能块给删除了，没有查询缓存该功能了。

1.4、分析器

如果 SQL 查询语句没有命中查询缓存，那么就会继续后面的执行步骤，首先进入的是分析器，分析器的作用的需要分析要执行的 SQL 语句要做什么。会对其 SQL 语句进行词法分析，语法分析两步。
词法分析
a. 分析器首先进行词法分析，SQL 语句是一个个的字符串和空格组成，词法分析出整个 SQL 语句中出现的各个字符串的含义代表什么.
b. 例如：select * from T where ID=1；select 表示这是个查询语句，T 表示的是 table 表名，ID 表示的表中的 ID 字段。
语法分析
a. 做完词法分析之后继续进行语法分析，根据词法分析的结果，语法分析器会根据语法规则判断该 SQL 语句是否满足 SQL 语法。如果不满足则会返回 “ You have an error in your SQL syntax ”你的 SQL 语句存在语法错误。例如 select 写成了 elect。

1.5、优化器

经过了分析器之后， Mysql 就知道执行的 SQL 要做什么，在开始执行之前，的首先经过优化器选择最佳的执行方式。优化器的作用是在表中存在多个索引的时候来决定使用哪个索引；或者在一条 SQL 语句中存在多表关联（join）的时候，决定各个表的连接顺序。
例如：select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
a. 既可以先从表 t1 里面先取出 c=10 的记录，再根据 ID 值关联到表 t2，在判断 t2 里面的 d 字段的值是否等于 20。
b. 也可以先从表 t2 里面先取出 d=20 的记录，再根据 ID 值关联到表 t1，在判断 t1 里面的 c 字段的值是否等于 10。
c. 两种方式的执行结果是一样的，都是根据其 t1，t2 相同的查询条件取并集，但是由于连接表的顺序不同其执行的效率会有所不同，而优化器的作用就是决定选择最佳的连接顺序使其 SQL 语句的执行效率最高。

1.6、执行器

Mysql 经过了分析器知道了 SQL 要干什么，在经过了优化器知道了怎么执行 SQL 语句，之后就进入执行器阶段，开始执行 SQL 语句。
开始执行 SQL语句前首先判断于服务器建立连接的用户是否有权限对该表执行查询的权限，如果没有就返回没有权限的错误，例如：表 T 拒绝该用户的命令
如果有权限，就会打开表继续执行，打开表的时候会根据该表定义的时候 engine 指定的或者默认的引擎，去使用该引擎提供的接口。执行流程：
a. 例如：select * from T where ID=1; 假如该 SQL 查询语句中对应的表 T 中，ID 字段没有索引，那么执行流程如下：
b. 调用 InnoDB 引擎接口取出表 T 的第一条记录，判断 ID 的值是不是等于 10，如果不是则跳过，如果是就将该条记录保存到结果集中
c. 调用引擎接口取表 T 的下一条记录，重复相同的判断，直至遍历完表 T 中的所有记录。
d. 执行器将上面遍历后的满足条件的记录集作为结果集返回给客户端。至此这条 SQL 语句执行完毕。
对于有索引的表，执行的逻辑差不多，都是调用引擎中相同的接口来一条条的向下根据条件筛选出满足条件的记录保存起来最后作为结果集返回给客户端
在数据库中的慢查询日志中可到 rows_examined (查询行数) 字段，该字段表示对应的 SQL 语句执行过程中扫描了多少行记录，这个值在执行器每次调用引擎获取数据行的时候累加的。但是在某些场景，执行器只调用了一次引擎中的接口，但是引擎内部则扫描了多行，因此器 rows_examined 中的记录数不一定和真实扫描的行数一致。