面试官:说说一条查询SQL的执行过程"/>
面试官:说说一条查询SQL的执行过程
一条查询语句是如何执行的?
- MySQL 的架构分层
- MySQL组件定义
- 连接器
- 长链接的问题
- 查询缓存
- 分析器
- 词法分析
- 语法分析
- 优化器
- 执行器
- 总结
本文和大家一起讨论查询语句在 MySQL 中的执行过程。
为了理解这个问题,我们先从 MySQL 的架构说起,对于 MySQL 来说,虽然经历了多个版本迭代,但每次的迭代,都是基于 MySQL 基架的。
下面我们开始分析下 MySQL 的架构体系。
MySQL 的架构分层
MySQL 大致可以分为 3 层架构,如下图:
第一层客户端,主要负责与 MySQL Server 层建立连接,发送查询请求以及接受响应的结果集。
第二层是 Server 层,主要包括连接器、查询缓存、分析器、优化器、执行器等。这些组件包含了 MySQL 的大部分主要功能,例如平时使用最多的存储过程、触发器、视图都在这一层中。还有一个通用的日志模块 binlog
。
第三层则是存储引擎了,主要负责数据的存储和提取。MySQL 支持多个存储引擎,例如:InnoDB
、MyISAM
、Memory
等。现在最常用的存储引擎是 InnoDB
,它从MySQL 5.5.5 版本开始成为了 MySQL 的默认存储引擎,重要的是 InnoDB
引擎包含了自带的日志模块 redo log
,这个我们在后面会详细讲述。
通过 MySQL 的架构分层,我们可以清晰的了解到一个 SQL 大致的执行过程。
- 首先客户端发送请求到服务端,跟连接器建立连接
- 服务端先看下查询缓存是否命中,命中就直接返回,否则继续往下执行。
- 接着来到分析器,进行词法分析、语法分析,一些系统关键字校验,校验语法是否合规等等。
- 然后优化器进行 SQL 优化,比如怎么选择索引之类,然后生成执行计划。
- 最后执行器调用存储引擎 API 查询数据,返回结果。
这就是一个很概括性的 SQL 执行过程,接下来,具体到每个步骤详细说明一下。
MySQL组件定义
连接器
客户端通过连接器访问 Server 层。连接器主要负责身份认证和权限鉴别的工作。即校验账户密码,权限。
- 如果用户名或密码不对,就会收到一个
Access denied for user
的错误提示,然后客户端程序结束执行。 - 如果用户名密码认证通过,连接器会到权限表里面查出登录用户所拥有的权限。之后,这个连接里面的权限判断,都将依赖于此时读到的权限。
连接建立完成之后,如果没有后续的动作,这个连接就处于空闲状态,我们可以用 show processlist
命令查看。
上图中的 Command 列显示为 Sleep
的这一行,就表示现在系统里面有一个空闲连接。
如果客户端太长时间没动静,连接器就会自动将连接断开。这个时间是由参数 wait_timeout
控制的,默认值是 8 小时。
mysql> show variables like 'wait_timeout';
如果在连接被断开之后,客户端再次发送请求的话,就会收到一个错误提醒: Lost connection to MySQL server during query
。这时候如果要继续,就需要重连,然后再执行请求了。
数据库将连接分为:长链接和短连接。
- 长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。
- 短连接则是指每次执行完 SQL 的请求操作就断开连接,如果再有请求会重新建立连接。
因为频繁建立连接,消耗系统的资源,所以建议在使用中尽量减少建立连接的动作,也就是尽量使用长链接。
但是如果全部使用长链接也会出现问题。
长链接的问题
如果全部使用长链接,我们会发现,有些时候 MySQL 占用内存涨得特别快。
这是因为 MySQL 在执行过程中,临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。
所以如果长链接累积下来,导致内存占用太大,被系统强行杀掉(OOM),从现象看就是 MySQL 异常重启了。
我们该如何解决这个问题呢?
- 定期断开长连接。每隔一段时间或者执行一个占用内存的大查询以后断开连接,从而释放内存,当查询的时候再重新创建连接。
- 使用 MySQL 5.7 或更高的版本,通过执行
mysql_reset_connection
来重新初始化连接。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。 - 使用连接池的方式,将短连接变为长连接。
查询缓存
MySQL 拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。
之前执行过的语句及其结果会以 key-value
对的形式,被直接缓存在内存中。key 是查询的语句,value 是查询的结果。
如果客户端发送的查询请求能够直接在这个缓存中找到 key,那么这个 value 就会被直接返回给客户端。
如果查询命中缓存,MySQL 不需要执行后面的复杂操作,就可以直接返回结果,这个效率会很高。
但是,MySQL 查询不建议使用缓存:因为查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。
如果针对某张表进行更新,针对这张表的查询缓存就会被清空。如果这张表不断地被更新、查询,那么查询缓存会频繁地失效,获取查询缓存也失去了意义。
MySQL 8.0 版本直接将查询缓存的整块功能删掉了。
分析器
如果没有命中查询缓存,就要开始真正执行语句了。SQL 请求会进入分析器。
分析器是用来分辨SQL语句的执行目的,其执行过程大致分为两步:
- 词法分析
- 语法分析
词法分析
第一步,分析器先会做词法分析。主要负责从 SQL 语句中提取关键字,比如:查询的表,字段名,查询条件等等。
词法分析阶段是从 information schema
里面获得表的结构信息的。
MySQL 从 select
这个关键字识别出来,这是一个查询语句。它也要把字符串 T
识别成 表名 T
,把字符串 ID
识别成 列 ID
。
做完了这些识别以后,就要做语法分析。
语法分析
通过语法分析,判断输入的SQL 语句是否满足 MySQL 语法。
如果输入的语句不对,就会收到 You have an error in your SQL syntax
的错误提示,比如下面这个语句 select
少打了开头的字母 s
:
mysql> elect * from t 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 'elect * from t where ID=1' at line 1
优化器
经过了分析器,MySQL 知道语句是合法的,现在要由优化器将其转化成执行计划。
一条查询 SQL 可以有 N 种执行方式,最后返回的结果都是相同的。优化器的作用是找到其中最好的执行计划。
比如执行下面这样的语句,这个语句是执行两个表的 join:
mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
- 既可以先从表 t1 里面取出 c=10 的记录的 ID 值,再根据 ID 值关联到表 t2,再判断 t2 里面 d 的值是否等于 20。
- 也可以先从表 t2 里面取出 d=20 的记录的 ID 值,再根据 ID 值关联到 t1,再判断 t1 里面 c 的值是否等于 10。
这两种执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案。
一条 SQL 语句可能有不同的执行逻辑(或者顺执行顺序),而优化器就是选择最优的执行顺序。
优化器阶段完成后,这个语句的执行方案就确定下来了,然后进入执行器阶段。
执行器
MySQL 通过分析器知道了客户端要做什么,通过优化器知道了该怎么做,于是就进入了执行器阶段,开始执行语句。
但在执行之前,要先判断一下请求用户对这个表 T 有没有执行查询的权限,如果没有,就会返回没有权限的错误,如下所示 (在工程实现上,如果命中查询缓存,会在查询缓存返回结果的时候,做权限验证。查询也会在优化器之前调用 precheck
验证权限):
mysql> select * from T where ID=10;ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。
所以到了执行的时候才会进入到数据库引擎,然后执行器也是通过调用数据库引擎的API来进行数据操作的。也因此数据库引擎才会是插件形式的。
比如我们这个例子中的表 T 中,ID 字段没有索引,那么执行器的执行流程是这样的:
- 调用 InnoDB 引擎接口取这个表的第一行,判断 ID 值是不是 10,如果不是则跳过,如果是则将这行存在结果集中;
- 调用引擎接口取「下一行」,重复相同地判断逻辑,直到取到这个表的最后一行。
- 执行器将上述遍历过程中所有「满足条件的行」组成的记录集作为「结果集」返回给客户端。
至此,这个语句就执行完成了。
对于有索引的表,执行的逻辑也差不多。第一次调用的是「取满足条件的第一行」这个接口,之后循环取「满足条件的下一行」这个接口,这些接口都是引擎中已经定义好的。
总结
本文以 SQL 查询语句的执行过程作为切入点,介绍了查询请求的执行流程。
MySQL 的架构分为 MySQL Server 层和 MySQL 存储引擎层。Server 层包含了 5 大组件,分别是:连接器、查询缓存、分析器、优化器、执行器。
更多推荐
面试官:说说一条查询SQL的执行过程
发布评论