MySQL 一条查询语句是如何执行的?
MySQL的架构
MySQL的架构可以大致分为 Server层 和 存储引擎层。 如下图所示:
Server 层主要负责客户端请求的接收、解析、执行以及结果的返回。它位于MySQL系统的上层。Server 层包括连接器、查询缓存、分析器、优化器、执行器,以及所有的内置函数,并且所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
存储引擎层则负责底层的数据存储、检索、事务处理等任务。每种存储引擎提供不同的存储方式和特性,适用于不同的业务场景。比如,常见的存储引擎有InnoDB、MyISAM、Memory。其中,最常用的就是InnoDB了。
一般来说,当你建表的时候,如果没有指定存储引擎,那么默认情况下就是使用InnoDB。
一条查询语句的执行过程
那么,当我们在执行一条查询语句的时候,具体是怎么执行的呢?我们就来从MySQL的架构层面,来具体分析一下。
比如我要执行这条查询语句:
连接器
连接器是MySQL的第一道关卡,它的作用是管理客户端与MySQL服务器之间的连接。 连接器负责接收客户端发来的请求、处理会话管理、进行身份验证、权限检查等工作。
当客户端发起查询请求时,连接器首先接收到客户端的连接请求。此时,客户端会使用MySQL的连接协议(通常是TCP/IP)向MySQL服务器发送请求。
连接器接收到连接请求后,就会建立连接。之后,连接器会进行用户身份验证。这一步骤是通过检查用户名和密码来验证客户端是否具有连接MySQL服务器的权限。数据库内部的用户信息存储在 mysql.user 表中,包括每个用户的用户名、密码、权限等。
如果用户名和密码正确,连接器允许该连接继续执行。
设在客户端输入的用户名是user1,密码是password1。
如果用户名不存在,就会出现如下提示:
如果密码错误,就会出现如下提示:
身份验证通过,连接器会继续检查你是否有执行SQL查询的权限,而且后续的所有操作都是基于你的权限来进行的。
当连接建立之后,如果你后续没有任何的操作,那么这个连接就处于空闲状态,你可以使用 show processlist来查看。
在连接建立但没有进行任何操作时,执行 show processlist 会显示连接处于 Sleep 状态,这意味着连接处于空闲状态,等待后续的查询操作。Time 列表示连接空闲的时间,Info 列为空,因为没有正在执行的SQL语句。
如果过了太长时间,你依然没有操作,那么这个连接就会自动断开。这个时间是由参数 wait_timeout 控制的,默认值是 8 小时。
当连接断开之后,你再连接数据库,就需要重新建立连接。
数据库中的连接分为两种,分别是长连接和短连接。
- 长连接是指客户端与数据库建立连接后,连接会保持打开状态,可以在多个操作中被复用,不会在每次操作后关闭。
- 短连接是指客户端与数据库建立连接后,执行完一个操作或一组操作后立即关闭连接。每次执行数据库操作时,都会重新建立连接,操作完成后即断开连接。
因为建立连接的过程很复杂,所以我们最好使用长连接。 但是如果连接不再使用而保持空闲状态,可能会导致数据库资源浪费。
那么如何解决这个问题呢?有两个措施:
- 定期断开长连接。
- 如果你用的是 MySQL 5.7 或更新版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源。
查询缓存
连接建立完成之后,就可以执行我们的select语句了,然后就到了第二阶段:查询缓存。
MySQL 提供了查询缓存功能,查询缓存会保存上一次执行的查询结果,下一次如果相同的查询被执行,且缓存未过期,MySQL 就可以直接返回缓存中的数据,而不需要去执行相同的查询。但是,如果没有查到缓存,MySQL就会继续往下执行,如果查询到了结果,它就会把这个查询结果缓存起来。
但是,大多数情况下,是不建议使用缓存的。
因为, 查询缓存需要维护缓存的数据一致性。当数据发生变化时,缓存会被清空或重新计算,导致缓存命中率降低。比如,我们更新了一张表,那么这张表中的所有缓存就会全部被清空。
分析器
分析器的动作主要有两个:词法分析和语法分析。
词法分析是将查询语句的原始字符流转换成有意义的词法单元(token)。每个 SQL 查询中的关键词、表名、字段名、操作符等都会被识别为一个个的词法单元。
在这个查询中,词法分析会把它分解为以下几个词法单元:
SELECT、name、age、FROM、users、WHERE、age、>、30。
做完词法分析之后,就会进行语法分析。’
语法分析是对词法单元序列进行检查,确定它们是否符合 SQL 的语法规则。SQL 查询的语法通常遵循一定的语法结构,例如 SELECT 语句后面必须跟表名、字段名,WHERE 子句后面必须有条件表达式等。
比如,如果在 SELECT 语句中忘记了 FROM 子句:
语法分析器会报告以下错误:
总之,分析器的作用就是告诉MySQL,我们想要做什么。
优化器
而优化器的作用,就是告诉MySQL,应该怎么做。优化器主要任务是 生成最优的查询执行计划,以确保 SQL 查询可以高效地执行。
比如,我们如果想要查询这个语句:
如果查询中有常量的计算,优化器会预先计算出结果,而不是每次查询时都计算。
再比如,我们想要查询这个语句:
假设 users 表的 age 列上有索引,优化器可能会选择该索引来加速查询。如果没有索引,优化器会选择全表扫描。
另外, 如果查询涉及多个表,优化器会评估不同的连接方式,选择最优的连接顺序和方法。在此例中,由于只有一个表,连接优化不会起作用。
总之,在 优化器阶段,MySQL 的优化器通过对查询的多种执行计划进行评估,选择一个最优的执行方式。
执行器
在 MySQL 查询执行的最后阶段,即 执行器阶段,优化器已经选择了最佳的执行计划,执行器负责 实际执行查询,将查询的逻辑转化为实际操作,并从数据库中提取、处理数据,最终返回查询结果。
执行器阶段的具体操作是这样的:
- 访问存储引擎: 执行器会根据优化器选择的执行计划,发起对存储引擎的请求。
- 扫描表数据: 执行器根据执行计划决定扫描哪一部分数据。
- 执行 JOIN 操作: 如果查询涉及多个表的连接,执行器会根据优化器选择连接方式。
- 排序操作: 如果查询中包含了 ORDER BY 子句,执行器会根据查询计划中的指示,对查询结果进行排序。
- 聚合操作: 如果查询包含了聚合函数(如 COUNT()、SUM()、AVG()、GROUP BY 等),执行器会进行聚合计算。
我们继续用之前的查询语句作为例子:
- 扫描表数据: 执行器会根据优化器选择的执行计划,首先访问 users 表。如果 age 列上有索引,执行器会选择索引扫描;如果没有合适的索引,它会执行全表扫描。
- 条件过滤: 执行器会在扫描到每一行数据时,根据 WHERE age > 30 过滤掉不符合条件的记录。只有满足条件的数据行会被保留。
- 排序操作: 执行器会根据 ORDER BY age 对过滤后的数据进行排序。如果数据量较小且有合适的索引,执行器可能会利用索引直接返回排序后的数据。如果没有索引,执行器会将查询结果加载到内存中并进行排序。
- 返回结果: 最终,执行器将满足 WHERE 条件和排序条件的数据行(name 和 age 列)返回给客户端。
小结
我们从MySQL的架构层面,从头到尾捋了一遍一条查询语句的执行逻辑。如果有某个环节没有听懂,也没有关系,以后的章节我也会讲到的。