MySQL | 提交一条查询SQL背后发生了什么？

数据与人 2021-09-15

319

点击上方"数据与人", 右上角选择“设为星标”

分享干货，共同成长！

一、提交一条查询SQL背后发生了什么

在传统关系型数据库中，SQL处理器的组件主要包括以下几种：

• Query Parsing

负责进行词法和语法分析,把程序从人类可读的格式(即SQL)转化成机器高可读的格式。

• Query Rewrite

即通常我们说的"Logical Optimizer"或基于规则的优化器(Rule-Based Optimizer,即RBO)。

常见操作有:视图展开，简化常量运算表达式，谓词逻辑的重写，语义的优化等。

• Query Optimizer

即通常我们所说的"Physical Optimizer"，负责把内部查询表达转化成一个高效的查询计划，指导DBMS如何去取表，如何进行排序，如何Join。

• Query Executor
查询执行器，负责执行具体的查询计划，从存储引擎中获取数据并且对数据应用查询计划得到结果。

不管是传统的关系型数据库，还是非关系型数据库，SQL的解析和生成执行计划过程都是大同小异的。

二、一条 SQL 的执行过程。

以MySQL为例。

首先有一个 test 表，表里有一个 id 字段，执行下面这条查询语句：

select * from test where id = 1000;
复制

返回结果为：

+------+-------+---------+---------------------+
| id   | name  | Stu_ID  |  create_time        |
+------+-------+---------+---------------------+
|1000 | 刘皇叔 | 2020001 | 2020-08-10 22:29:08 |
+------+-------+---------+---------------------+
复制


复制

来看一下 SQL 语句在 MySQL 的各个模块中的执行过程。

MySQL 分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、执行器等，以及所有的内置函数（如日期、时间、数学和加密函数等）和跨存储引擎的功能（如存储过程、触发器、视图）。

存储引擎层负责数据的存储和提取。

1、连接器（Connector）

首先要建立与 MySQL 的连接，用于查询SQL语句。

连接命令为：

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

连接到MySQL之后，连接器会首先判断当前用户拥有的权限，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

连接建立后，即使管理员对这个用户的权限做了修改，也不会影响已经存在连接的权限，修改完后，只有再新建的连接才会使用新的权限设置。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态。

客户端如果太长时间没动静，连接器就会自动将它断开；这个时间是由参数 wait_timeout 控制的，默认值是8小时。

2、长连接和短连接

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，尽量使用长连接。

但是全部使用长连接后，有时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。

这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

可以考虑以下两种方案解决此类问题：

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
MySQL 5.7 以上版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。

3、查询缓存（Query Cache）

在建立连接后，就要开始执行 select 语句了。

拿到查询请求后，会先查询缓存。执行过的语句及其结果会以 key-value 对的形式保存在一定的内存区域中。

key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个value 就会被直接返回给客户端，这一过程称为软解析。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，会提升效率。

4、分析器（Analyzer）

如果查询缓存未命中，就要开始执行语句了。首先需要对 SQL 语句进行硬解析。

分析器先会做词法分析。分析SQL 语句的组成成分。

从你输入的 select 这个关键字识别出来，这是查询语句。它也要把字符串 test 识别成表名，把字符串 id 识别成列名。之后就要做语法分析。根据词法分析的结果，语法分析器会根据语法规则，判断输入的 SQL 语句是否满足语法。

5、优化器（Optimizer）

经过分析器的词法分析和语法分析后，还要经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

不同的执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

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

6、执行器（Actuator）

MySQL 通过分析器知道了要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表 test 有没有执行查询的权限，如果没有，就会返回没有权限的错误。

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

我们这个例子中的表 test 中，id 字段没有索引，那么执行器的执行流程是这样的：

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

对于有索引的表，第一次调用的是取满足条件的第一行这个接口，之后循环取满足条件的下一行这个接口。

最终把查询结果返回客户端。

三、结语

了解数据库背后SQL的查询整个流程，不仅能让数据库使用者更清晰地认识到如何编写最优化的SQL，也能够让数据库内核开发者加深对数据库体系结构的理解，提高开发效率。

本文并没有涉及到太深入的技术细节，，只是从宏观角度给读者介绍了执行SQL背后到底发生了什么。

感谢各位的阅读，文章的目的是分享对知识的理解，若文中出现明显纰漏也欢迎指出，我们一起在探讨中学习。

觉得本文有用，请转发、点赞或点击“在看”
聚焦技术与人文，分享干货，共同成长
更多内容请关注“数据与人”

mysql

文章转载自数据与人，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL | 提交一条查询SQL背后发生了什么？

一、提交一条查询SQL背后发生了什么

1、连接器（Connector）

2、长连接和短连接

3、查询缓存（Query Cache）

4、分析器（Analyzer）

5、优化器（Optimizer）

6、执行器（Actuator）

三、结语

评论

相关阅读

MySQL | ​提交一条查询SQL背后发生了什么？

一、提交一条查询SQL背后发生了什么

1、连接器（Connector）

2、长连接和短连接

3、查询缓存（Query Cache）

4、分析器（Analyzer）

5、优化器（Optimizer）

6、执行器（Actuator）

三、结语

评论

相关阅读

MySQL | 提交一条查询SQL背后发生了什么？