SQL 查询语句是如何执行的

# Web 开发

大家好！这是我的第一篇文章。

在本文中，我将介绍如何执行一条sql查询语句

下面是MySQL架构图：

MySQL architecture diagram

总体来说，MySQl可以分为两部分：服务器层和存储引擎层。

服务器层包括连接器、查询缓存、解析器、优化器、执行器等，包含 MySQL 的大部分核心服务功能以及所有内置函数（例如日期、时间、数学和加密函数）。所有跨存储引擎的功能（例如存储过程、触发器和视图）都在此层实现。

存储引擎层负责数据的存储和检索。其架构为插件式，支持InnoDB、MyISAM、Memory等多种存储引擎。从MySQL 5.5.5开始，InnoDB成为MySQL的默认存储引擎。

您可以在创建表时通过使用带有“engine=memory”的“create table”语句来指定内存引擎。

**不同的存储引擎共享同一个Server层**

连接器

第一步是连接数据库，需要用到连接器，连接器负责与客户端建立连接，获取权限以及维护和管理连接，连接命令为：

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

该命令用于与服务器建立连接，完成经典的TCP握手后，连接器会使用提供商的用户名和密码来验证您的身份。

  • 如果用户名或密码不正确，您将收到因用户错误而拒绝访问的消息，并且客户端程序将终止。
  • 如果身份验证成功，连接器将从权限表中检索当前帐户的权限。此连接期间的所有权限检查都依赖于此初始检索。

    • 这意味着一旦成功建立连接，管理员对用户权限所做的任何更改都不会影响现有连接的权限。只有新连接才会使用更新后的权限设置。

    连接建立之后，若没有后续动作，连接将进入空闲状态，可以使用`show processlist`命令查看：

    processlist

    如果客户端长时间不活动，连接器将自动断开连接。持续时间由 `wait_timeout` 参数控制，默认为 8 小时。

    如果连接终止，客户端发送请求，则会收到错误消息：“查询期间与 MySQL 服务器失去连接”。要继续，您需要重新连接，然后执行请求。

    在数据库中，**长连接**是指客户端在成功连接后，对连续的请求保持同一个连接。短连接是指在进行几次查询后断开连接，并在后续查询时重新连接。

    由于连接过程很复杂，建议在开发过程中尽量减少连接的创建，即尽可能使用持久连接。

    但是使用长连接时，MySQL 的内存占用可能会大幅增加，因为执行过程中使用的临时内存是在连接对象内部管理的，只有当连接终止时才会释放这些资源。如果长连接不断积累，可能会导致内存占用过大，从而导致系统强制终止 MySQL（OOM），从而导致意外重启。

    **解决方案**：

  • 定期断开持久连接。使用连接一段时间或执行消耗过多内存的查询后，断开连接并重新连接以进行后续查询。
  • 如果您使用的是 MySQL 5.7 或更高版本，您可以在执行资源密集型操作后使用 mysql_reset_connection 重新初始化连接资源。此过程不需要重新连接或重新认证，而是将连接重置为刚创建的状态。

    • 查询缓存

    注意：从 MySQL 8.0 开始，查询缓存功能已被完全删除，因为**它的缺点大于它的优点**。

    当 MySQL 收到一个查询请求时，它首先会检查查询缓存，看这个查询是否之前被执行过。之前执行过的查询和它们的结果会以键值对的形式缓存在内存中。键是查询语句，值是结果。如果在查询缓存中找到该键，则直接将值返回给客户端。

    如果在查询缓存中找不到该查询，则该过程继续。

    **为什么查询缓存弊大于利？**

    查询缓存失效现象发生得非常频繁，任何对表的更新都会清除与该表相关的所有查询缓存，导致缓存命中率非常低，除非该表是静态配置表。

    MySQL 提供了一种“按需”使用查询缓存的方法。通过将参数“query_cache_type”设置为 DEMAND，SQL 语句将默认不使用查询缓存。要使用查询缓存，可以显式指定“SQL_CACHE”：

    select SQL_CACHE * from T where ID=10;

    解析器

    如果查询缓存未命中，则开始语句执行过程。MySQL 首先需要了解要做什么，因此它会解析 SQL 语句。

    解析器首先进行词法分析，输入的 SQL 语句由字符串和空格组成，MySQL 会对其进行分析，识别出各个部分代表什么，例如 `select` 被识别为查询语句，`T` 被识别为 `表名`，`ID` 被识别为 `列`。

    词法分析之后，接着进行语法分析，语法分析器根据词法分析的结果，判断SQL语句是否符合MySQL的语法规则。

    如果有语法错误，则会显示类似“您的 SQL 语法有错误”的错误消息。例如，在以下查询中，select 关键字拼写错误：

    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 知道您要做什么。接下来，优化器决定如何做。

    当一个表有多个索引时，优化器决定使用哪个索引；当一个查询涉及多个表时，优化器决定表连接的顺序。例如，在以下查询中：

    select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

    查询可以从 t1 或 t2 中检索值开始。两种方法都会产生相同的逻辑结果，但它们的性能可能不同。优化器的作用是选择最有效的计划。

    优化阶段结束后，流程进入执行器。

    执行者

    执行器开始执行查询。

    执行前先检查当前连接是否有权限查询该表，如果没有权限则返回权限不足的错误（从查询缓存返回结果时也会进行权限检查）。

    如果授予权限，则打开表并继续执行。在此过程中，执行器根据表的引擎定义与存储引擎进行交互。

    例如，假设表T在ID列上没有索引，执行器的执行流程如下：

  • 调用InnoDB引擎接口获取表的第一行，检查ID值是否为10，如果不是，则跳过；是，则添加到结果集中。
  • 调用引擎接口来获取“下一行”，重复相同的逻辑，直到检查完所有行。
  • 执行器将累积的结果集返回给客户端。

    • 至此，查询完成。

    对于索引表，该过程涉及使用引擎的预定义方法迭代地获取“第一个匹配的行”和“下一个匹配的行”。

    在慢查询日志中，rows_examined 字段表示查询执行过程中扫描的行数。每次执行器调用引擎检索数据行时，该值都会累积。

    在某些情况下，对执行器的一次调用可能涉及在引擎内部扫描多行。因此，**引擎扫描的行数不一定等于 rows_examined**。

    结尾

    感谢您的阅读！希望文章能够对您有所帮助。