数据库面试题 — 一条SQL查询语句是如何执行的？

Cyan_RA9
80 阅读5分钟

Author : Cyan_RA9
Source : 【卡码笔记】网站
Question : 一条SQL查询语句是如何执行的？

【简要回答】

  1. 连接阶段：由服务器端的连接器组件负责，在客户端与 MySQL 服务器之间建立连接，并验证用户权限。
  2. 查询缓存检查阶段（MySQL 8.0 前）：检查是否命中缓存，若有完全相同且有效的查询结果可以直接返回。
  3. 解析与预处理阶段：解析 SQL 语法，检查语法是否正确，并生成抽象语法树，然后，预处理器进行一些语义检查，验证表和字段是否存在。
  4. 执行计划生成与优化阶段：基于统计信息和成本模型，考虑多种执行方案，并选择最优执行计划（如索引选择、JOIN 顺序）。
  5. 执行阶段：根据选择的执行计划，调用存储引擎接口，并按执行计划读取数据并处理（排序、聚合等）。
  6. 数据存储和检索阶段（以 InnoDB 为例）（如 InnoDB）：负责从磁盘或内存读取数据，返回给执行器。
  7. 结果返回阶段：执行器进行必要的处理（如过滤、排序）后，通过服务器进程/线程将结果集返回客户端，可能分批次传输。

【详细回答】

  1. 连接阶段：
    • 当我们在客户端（如命令行工具、应用程序）输入并执行一条 SQL 查询时，首先需要与数据库服务器建立一个网络连接。
    • 这个过程包括 TCP/IP 协议的三次握手，以及数据库层面的认证，比如验证用户名和密码。
    • 连接成功后，服务器会为这个连接分配一个独立的线程来处理后续的请求。
  2. 查询缓存检查阶段（MySQL 8.0 前）：
    • 服务器接收到 SQL 语句后，会先检查 查询缓存。这是一个位于内存中的区域，存储了之前执行过的查询语句及其结果。
    • 如果当前查询与缓存中的某个查询完全一致（包括 SQL 语句本身、连接的数据库、客户端的协议版本等），并且缓存仍然有效（比如涉及的表没有被修改），那么服务器会直接从缓存中返回结果，无需执行后续的解析、优化和执行过程。
    • 需要注意的是，在 MySQL 8.0 及更高版本中，查询缓存功能已经被移除。 这是因为在并发写入场景下，查询缓存的维护成本很高，容易成为性能瓶颈。因此，在现代数据库系统中，通常不再依赖查询缓存进行优化。
  3. 解析与预处理阶段：
    • 如果查询缓存未命中，服务器会将 SQL 查询语句发送给解析器。解析器会对 SQL 语句进行词法分析（将语句分解成一个个词法单元，如关键字、标识符、操作符等） 和 语法分析（根据 SQL 语法规则检查语句是否合法，生成一个抽象语法树 AST）。
    • 如果语法有错误，解析器会直接返回错误信息。如果语法没问题，预处理器根据抽象语法树，进一步检查 SQL 语句的合法性，例如，检查表名、字段名是否存在，是否有权限执行该查询等，它还会进行一些语义上的检查和转换。
  4. 执行计划生成与优化阶段：
    • 优化器的目标是找到执行查询的最优执行计划。它会考虑多种可能的执行方式，并评估它们的成本（如 I/O 次数、CPU 消耗等）。
    • 优化器会利用统计信息（如表的大小、索引的选择性等）来做出决策。
    • 常见的优化策略包括：
      ① 选择合适的索引。
      ② 决定表的连接顺序。
      ③ 选择合适的连接算法（如嵌套循环连接、哈希连接、合并排序连接）。
      ④ 改写查询语句，使其更高效。
    • 最终，优化器会生成一个最优的执行计划（Execution Plan），它描述了如何执行查询的步骤。
  5. 执行阶段：
    • 执行器根据优化器生成的执行计划，调用存储引擎的接口来执行查询。
    • 执行器会按照执行计划的步骤，从存储引擎获取数据，进行过滤、排序、连接等操作。例如，如果执行计划指示使用某个索引进行查找，执行器就会调用存储引擎的索引查找接口。
  6. 数据存储和检索阶段（以 InnoDB 为例）：
    • 存储引擎是数据库系统中负责数据存储和检索的核心组件。不同的存储引擎有不同的特点和优势（如 InnoDB、MyISAM 等）。
    • 执行器通过存储引擎的 API 来访问和操作数据文件。
    • 存储引擎负责数据的读取、写入、更新、删除以及事务管理、锁机制等。
  7. 结果返回阶段：
    • 执行器将最终的查询结果返回给服务器进程/线程，该进程/线程负责将其通过网络发送回客户端。。
    • 客户端应用程序接收到结果后，可以进行进一步的处理和展示。

【知识拓展】

  1. MySQL执行一条SQL查询语句的流程示意图（以MySQL8.0为例），如下所示：
    SQL_execution_order.jpg
  2. 面试官可能的追问1—为什么 MySQL 8.0 移除了查询缓存？
    • 答：缓存失效频繁（表更新即失效），维护成本高且命中率低。现代优化器能生成更高效执行计划，且应用层缓存（如 Redis）更灵活。
  3. 面试官可能的追问2—优化器如何决定使用某个索引？
    • 答：基于索引的选择性（区分度）、统计信息（如索引的基数cardinality）、查询条件、以及预估的I/O和CPU成本。可通过 EXPLAIN 查看 possible_keys 和 key 字段。
  4. 面试官可能的追问3—如何分析一条慢 SQL 的执行瓶颈？
    • 答：
      ① 使用 EXPLAIN 查看执行计划（索引使用、扫描行数）。
      ② 开启慢查询日志（slow_query_log）捕获耗时操作。
      ③ 检查锁竞争（SHOW ENGINE INNODB STATUS）。
avatar
文章
阅读
粉丝