查询sql的执行过程

最新推荐文章于 2022-08-17 07:43:21 发布

戎·码一生

最新推荐文章于 2022-08-17 07:43:21 发布

阅读量1.9k

点赞数 2

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/gogokongyin/article/details/51788250

版权

数据库专栏收录该内容

11 篇文章 1 订阅

订阅专栏

前言

简单的查询一行数据，数据库需要经过那些加工处理才能得到我们需要的数据呢？

1 Innodb的逻辑关系架构图

数据库增查改删，我们平时用的很熟悉，执行一个sql语句，然后返回一个结果，但是这个过程到底是什么样的呢，下面来简单看一下其中的过程，首先了解一下mysql的模块架构图如下：

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

Server层：包括 连接器／查询缓存／分析器／优化器／执行器等，涵盖MySql的大多核心服务功能，以及所有的内置函数「日期，时间，数学和加密函数」等，所有跨存储引擎的功能都在这一层实现，比如存储过程／触发器／试图等。从图中可以看到，不同的存储引擎公用一个Server层，也就是从连接器到执行器的部分。

存储引擎层： 负责数据的存储和读取。其架构模式是插件式的，支持InnoDB/MyISAM/Memory等多个存储引擎。现在最常用的是存储引擎室InnoDB,它从MySQL5.5版本开始成为默认引擎。不同的存储引擎表数据的存储格式不一样，支持的功能也不一样。我们在创建表create table时 ，如果不指定引擎类型，默认使用的就是InnoDB.我们也可以通过指定存储引擎来选择引擎，比如在create table的时候采用engine=memory，来指定使用内存引擎创建表。

Server 层包括：连接器；分析器；优化器；执行器；主要作用为：“连接、鉴权、计算”；
存储引擎：仅仅只是读写和存储数据；给我id，我给你数据；

2 查询sql的执行过程

2.1 连接准备阶段

要进行数据库的查询，第一步首先是需要连接数据库，然后才能查询。

2.1.1 连接器

要使用数据库，首先我们要连接上数据库，这个时候我们就需要使用连接器。连接器负责跟客户端建立连接／获取链接／维持和管理连接。我们一般使用如下命令：

mysql -h$ip -P$port -u$user -p

输入完命令，我们需要在交互框里输入密码，这里不建议直接用命令行模式直接将密码写在-p后面，可能导致密码的泄漏，尤其是生产服务器。连接命令中的mysql是客户端工具，用来跟服务端建立连接。在完成 tcp握手后，连接器就开始认证你的身份，这个时候用的就是输入的用户名和密码。

⚠️如果用户名和密码不对，我们经常会收到一个“Access deied for user”的错误，然后客户端的程序执行结束。

如果密码和用户名通过验证，连接器会到权限表里查询改用户拥有的权限。连接阶段只是获取了权限信息，真正进行查询和操作的时候才会判断“有没有操作这个表的权限”。拿到用户信息和权限信息。之后，这个连接里设计到的权限判断都将依赖于此时读到的权限。这也就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在的连接权限。修改完成后，只有重新新建连接才会使用新的权限设置。

连接完成后，我们可以通过如下命令来查看连接的状态：

show processlist;

其中通过command这个命令来查看该连接是在做增删改查，还是处于空闲sleep状态；

如果太长时间没动静，连接器就会自动将其断开。这个时间是由参数 wait_timeout来控制， 默认是8小时。

⚠️当连接断开时，我们经常会收到“Lost connection to MySql server during query.”这时候你要继续，就需要重连，然后再执行请求了。

2.1.2 数据库里面的长连接和短连接：

* 长连接：是指连接成功后，如果客户端持续有请求，则一直使用同一个连接；
* 短连接：是指每执行几次命令后就会断开连接，下次查询再新建一个；

建立连接的过程比较耗时和复杂，所以尽量使用长连接。但在使用长连接后，你会发现内存长的特别快，这是因为Mysql在执行过程中临时使用的内存管理师管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以长期积累下来，可能会导致内存占用量过大，可能被系统强行杀掉OOM，从现象上来看就时mysql异常重启了。

长连接导致内存增长解决的方案一般有两个：

1、定期的断开连接。使用一段时间后，或者程序里面判断执行过一个占用内存大的查询后，断开连接，之后再重新查询连接；
2、对于mysql5.7以后的版本，我们可以通过 mysql_reset_connnection来重新初始化资源。这个过程不需要重连喝重新做权限验证，但是会将连接恢复到刚刚创建完的状态，释放掉积累的内存空间。

2.1.3 查询缓存

完成第一步的建立连接，如果开启了缓存就直接查询缓存了，所以这步暂且放入连接阶段里了，不算正式的查询：查询缓存。

Mysql拿到一个查询请求后，先到缓存里查询该命令是否执行过，因为之前执行过的命令会以key-value的形式存储在内存中(当然这个是可以设置的)，key是查询的语句，value是查询的结果。如果查询能够直接在这个缓存中找到这个key，这个value就会被直接返回给客户端。如果不存在，这个命令就会继续执行，当然其结果将会被保存在缓存中。可以看到，缓存大大提高了查询效率，省去了后面的分析-优化-执行的阶段。

但是查询缓存失效十分频繁，只要有对一个表有更新，表上所有的查询缓存都会被清空。所以对于频繁更新的数据库来说不适合开启查询缓存，因为每一次更新将导致缓存全部无效。但是对于查询频繁，修改少的静态表可以采取查询缓存的方式，例如系统配置表。当然了，我们也可以采用“按需使用”的方式，使用 query_cache_type来设置是否开启缓存查询，或者使用SQL_CACHE来显示指定：

select SQL_CACHE * from USER_TABLE where ID = 6;

缓存小结：

查询缓存[可设置关闭]，缓存命中返回数据时对权限校验；如果缓存没有继续执行， 只要有更新就情况缓存：配置表适合用缓存；
- show variables like “query_cache_type”=off，默认关闭状态，不推荐使用；
- 8.0的缓存模块已经被删除了（ 因为使用条件比较苛刻，必须完全一样，稍有不同就会清空删除数据，效率低）；
如果需要缓存：
- 使用本地缓存map；
- 使用分布式缓存redis；

2.2 sql准备阶段

2.2.1 分析器

如果没有命中缓存，就需要开始执行语句了，首先mysql需要知道你要做什么，因此需要对sql做解析。

2.2.2 词法分析

分析器线会做“词法分析”。因为我们输入的是由多个字符串和空格组成的一条sql语句，mysql需要识别出里面的字符串分别是什么，代表什么。MySql从你输入的select这个关键字识别出来，这是一个查询语句。它也要把字符串 “USER_TABLE”识别成”表名TABLE”, 把字符串id识别成“列ID”.

2.2.3 语法分析

做完这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析会根据语法规则，判断你输入的这个sql语句是否满足mysql语法。如果你输入的sql不对，⚠️这个时候我们就会遇到我们经常看到的一个错误：“You hava an error in your SQL syntax. ”的错误提醒。一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。

2.3 优化器阶段

优化器

经过了分析器，Mysql就知道你要做什么了，接下来mysql需要知道如何最优完成？

在开始执行之前，还要进行优化器的处理。优化器是在表里有多个索引的时候，决定用那个索引；或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序，具体过程后面进行详细的分析，下面简单介绍一下：

比如你执行下面的这样的语句，查找用户123的成功的订单。假如这个语句是执行两个表的join：

select * from USER_TABLE join ORDER_TABLE  where USER_TABLE.id=123 and ORDER_TABLE.statu=1;

上面的语句执行：

方案一：可以先从USER_TABLE中找出id=123的用户，然后再根据id关联到ORDER_TABLE找到状态为1的成功的订单；
方案二：也可以先找到状态为1的成功的订单，然后再根据id匹配123的用户；

虽然最后执行的逻辑结果一样，但是很明显id是有索引的，statu一般很少加索引。另外id=123的用户一般很少，但是如果先执行statu=1的语句，其返回的结果会非常多，所以这就产生了查询成本和收益的选择方案，而优化器就是为了决定选择那种方案而生的。

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

2.4 执行器阶段

执行器

MySql通过分析器知道了你要做什么，通过优化器知道了该如何最优的完成，于是就进入了执行阶段：

开始执行的时候需要判断一下你对这个表USER_TABLE有没有执行查询权限，如果没有，就会返回没有权限的错误，因为有些存储过程是跳过了预处理阶段的权限验证。另外如果是查询缓存，如果命中缓存，在返回结果的时候，做权限验证。查询也会在优化器之前调用precheck来验证权限。

select * from USER_TABLE where ID=6;

如果没有权限则会报如下错误：

ERROR 5345 (5356767): SELECT command denied to user ‘king’@‘localhost' for table ‘USER_TABLE’

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表引擎的定义，去使用这个引擎提供的接口。

select * from USER_TABLE where ID=6 and name ="张三";

字段ID有索引，而name没有索引，那么直接通过索引数据结构找到id=6的行记录，然后返回到server层，判断name的值是否是”张三“，如果是将这一行的值返回给客户端；

select * from ORDER_TABLE where statu=1;

对于上面的sql来说没有索引，执行器的执行流程就是普通的查找过程：

(1)、调用InnoDB引擎接口取这个表的第一行，判断statu的值是不是1，如果不是则跳过，如果是就将这行的记录存储在结果集中；

(2)、调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行；

(3)、执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端；

在我们执行sql前我们也可以通过explain来看一下执行计划：

explain select * from ORDER_TABLE where statu=1;

看当前的sql是否走了索引，扫描了多少行的记录，做到心中有数，否则对于高并发的线上引起的慢查询也不是好玩儿的。比如rows_examined字段，记录了语句执行过程中扫描了多少行记录。

3 问题思考

问题一： 查询“select * from TABLE where names = "张三" ”; 时返回 “Unknown column ‘names’ in ‘where clause”错是那个阶段呢？“you hava an error in your SQLsynax" 呢?

答：是分析器的词法分析阶段，后面是语义分析阶段。这两个阶段mysql会将表名和字段名解析成具体的mysql能识别的关键字及相应的语法分析。

问题二： 为什么对数据库权限的检查不在优化器之前执行？或者是连接阶段检查？

答：因为有些时候sql的执行不仅仅是表面一些执行命令，还包括一些触发器，这些操作只有在执行阶段才确定，所以分析器、连接器和优化器是无能为力的。

问题三： 长连接会导致内存的增加，这些是由于什么产生？为什么不提前释放？

答：排序、变量和大查询等都会占用内存；因为是长连接所以需要复用，提前释放反而影响性能；

问题四： mysql中connetion_timeout 和 wait_timeout的区别？

connection_timeout:连接过程中的等待时间；
wait_time:是指连接完成后，使用过程中等待的时间;

问题五： mysql中表权限验证是什么阶段执行？

答：连接器阶段 “取”权限；执行器“用”权限。

4 小结

Mysql查询三步曲：

1，连接阶段
- 连接器：连接管理模块，接收请求；连接进程和用户模块，通过，连接线程和客户端对接
2，准备优化阶段
- 查询缓存 Query Cache，【mysql8.0以后的版本已经将缓存查询去掉了。】；
- 分析器，内建解析树，对其 语法检查，先from，再on，再join，再where......；检查权限，生成新的解析树， 语义检查（没有字段k在这里）等
- 优化器，将前面解析树转换成执行计划，并进行评估最优
- 执行器，获取锁，打开表，通过meta数据，获取数据
3，执行及返回结果
- 存储引擎：真正存储数据，提供数据的读写接口api；
- 返回数据给连接进程和用户模块，然后清理，重新等待新的请求