MySQL 中一条 SQL 查询语句的执行过程

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
全局流量管理 GTM,标准版 1个月
简介: `SELECT id FROM table_a where id = 10` 这条 SQL 从执行到最后结果返回你知道都经历了哪些步骤么?

MySQL 中一条 SQL 查询语句的执行过程

SELECT id FROM table_a where id = 10 这条 SQL 从执行到最后结果返回你知道都经历了哪些步骤么?

今天我们就来详细了解一下 mysql 的一个架构。

img

连接器

你会先连接到这个数据库上,这时候接待你的就是连接器。

连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的:

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

输完命令之后,你就需要在交互对话里面输入密码。当然密码也可以直接跟在 -p 后面写在命令行中,但这样可能会导致你的密码泄露。

在完成经典的 TCP 握手后,连接器就要开始认证你的身份,这个时候用的就是你输入的用户名和密码。

如果用户名或密码不对,你就会收到一个"Access denied for user"的错误,然后客户端程序结束执行。

如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限。

之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。这就意味着,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限。修改完成后,只有再新建的连接才会使用新的权限设置。

连接完成后,如果你没有后续的动作,这个连接就处于空闲状态,你可以在show processlist 命令中看到它。文本中这个图是 show processlist 的结果,其中的 Command 列显示为“Sleep”的这一行,就表示现在系统里面有一个空闲连接。

image-20230803094020754

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

数据库里面,长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。建立连接的过程通常是比较复杂的,所以我建议你在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。但是全部使用长连接后,你可能会发现,有些时候 MySQL 占用内存涨得特别快,这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是 MySQL 异常重启了。

缓存机制

为了提高查询性能,MySQL引入了缓存机制。查询缓存(Query Cache)可以缓存查询结果,如果之后有相同的查询,MySQL可以直接返回缓存中的结果,而无需执行相同的查询操作。

MySQL 拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式,被直接缓存在内存中。key 是查询的语句,value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key,那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果会被存入查询缓存中。你可以看到,如果查询命中缓存,MySQL 不需要执行后面的复杂操作,就可以直接返回结果,这个效率会很高。

然而,查询缓存并不是适用于所有情况的,它可能因为数据的变动而导致缓存的命中率降低,因此在某些情况下需要谨慎使用。

查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来,还没使用呢,就被一个更新全清空了。对于更新压力大的数据库来说,查询缓存的命中率会非常低。除非你的业务就是有一张静态表,很长时间才会更新一次。比如,一个系统配置表,那这张表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND,这样对于默认的 SQL 语句都不使用查询缓存。

注意: MySQL 8.0 版本直接将查询缓存的整块功能删掉了,也就是说 8.0 开始彻底没有这个功能了。

查询解析和优化

MySQL的查询执行过程从客户端发送查询请求开始。当应用程序发送一条SQL查询语句到MySQL服务器时,首先由查询解析器(Parser)负责解析该语句。

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句,MySQL 需要识别出里面的字符串分别是什么,代表什么。MySQL 从你输入的select这个关键字识别出来,这是一个查询语句。它也要把字符串table_a识别成“表名 table_a”,把字符串id识别成“列 id”。做完了这些识别以后,就要做“语法分析”。根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果你的语句不对,就会收到“You have an error in your SQL syntax”的错误提醒

查询解析器会将SQL查询语句转换为内部的数据结构,即查询解析树。接下来,优化器(Optimizer)登场,它的任务是对查询解析树进行优化,选择最优的执行计划。优化器根据索引、表的大小、统计信息等因素来评估不同的执行计划,并选择最优的执行策略。

优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。比如你执行下面这样的语句,这个语句是执行两个表的 join:

select * from t1 inner join t2 on t1.id = t2.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。这两种执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案。

执行计划生成

优化器选择了最优的执行计划后,会生成一个执行计划(Execution Plan)。执行计划是一组用于执行查询的操作指令,包括数据的读取、过滤、排序等操作。执行计划以树形结构表示,每个节点对应一个操作。这些节点构成了一个完整的执行计划,指导数据库引擎执行查询操作。

查询执行引擎

查询执行引擎(Execution Engine)是MySQL查询执行过程中的核心组件。它根据优化器生成的执行计划执行查询操作。查询执行引擎负责读取数据、执行过滤和排序等操作,并将结果返回给客户端。在执行查询时,查询执行引擎会与存储引擎进行交互,请求存储引擎提供数据。

开始执行的时候,要先判断一下你对这个表 table_a 有没有执行查询的权限,如果没有,就会返回没有权限的错误,如下所示 (在工程实现上,如果命中查询缓存,会在查询缓存返回结果的时候,做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

mysql> select * from table_a where id=10;
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'table_a'

如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。比如我们这个例子中的表 table_a 中,id 字段没有索引,那么执行器的执行流程是这样的:调用 InnoDB 引擎接口取这个表的第一行,判断 id 值是不是 10,如果不是则跳过,如果是则将这行存在结果集中;调用引擎接口取“下一行”,重复相同的判断逻辑,直到取到这个表的最后一行。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

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

存储引擎

MySQL支持多种存储引擎,如InnoDB、MyISAM等。存储引擎负责实际的数据存储和检索操作。在执行查询时,存储引擎会根据执行计划从磁盘中读取数据块,并将数据返回给查询执行引擎。不同的存储引擎有不同的特性和适用场景,选择合适的存储引擎对于数据库性能至关重要。

结果返回与客户端处理

最后,查询执行引擎从存储引擎获取到数据后,将数据处理成结果集并返回给客户端。客户端接收到查询结果后,可以根据需要对结果进行处理和展示。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
7天前
|
SQL 监控 关系型数据库
SQL语句当前及历史信息查询-performance schema的使用
本文介绍了如何使用MySQL的Performance Schema来获取SQL语句的当前和历史执行信息。Performance Schema默认在MySQL 8.0中启用,可以通过查询相关表来获取详细的SQL执行信息,包括当前执行的SQL、历史执行记录和统计汇总信息,从而快速定位和解决性能瓶颈。
|
14天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
46 9
|
19天前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
68 10
|
13天前
|
SQL 关系型数据库 MySQL
|
16天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
44 3
|
21天前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
|
20天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
94 1
|
22天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
42 1
|
21天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
49 0
下一篇
无影云桌面