三亩地 三亩地SAN MU DI · CODE DIARY
交流投稿
ARTICLE DETAIL

日记详情

真实记录编程学习的某一天，欢迎挑你感兴趣的翻一翻。

首页/学习日记/日记详情

一条 SQL 语句在 MySQL 中的执行过程详解

发布时间：2026/8/6 3:07:11 三亩地 编程学习日记
一条 SQL 语句在 MySQL 中的执行过程详解

面试题考点分析：

  • 是否清晰理解 MySQL 整体架构及各组件分工（连接器、分析器、优化器、执行器、存储引擎）。
  • 能否有条理地描述 SQL 语句从客户端发送到返回结果的完整链路。
  • 对查询缓存、词法语法分析、权限校验、执行计划生成等关键环节的细节掌握程度。
  • 是否关注存储引擎层的差异以及执行器与存储引擎的交互方式。
  • 能否结合执行过程给出实际开发中的 SQL 优化与问题排查思路。

一、标准回答

一条 SQL 语句在 MySQL 中的执行过程可以概括为连接、解析、优化、执行、返回五个阶段。客户端发起连接后，MySQL 的连接器负责验证身份和管理连接；通过SQL 接口进入分析器，对语句进行词法和语法分析，构建解析树；然后优化器根据统计信息和成本模型生成最优执行计划；最后执行器按照执行计划调用存储引擎 API读取或写入数据，并将结果返回给客户端。整个过程体现了分层解耦的架构思想，不同存储引擎（如 InnoDB、MyISAM）可以共用上层的解析与优化能力。

二、核心原理

要深入理解 SQL 执行过程，需要先了解 MySQL 的整体架构。MySQL 可以分为Server 层和存储引擎层，Server 层负责连接管理、SQL 解析、权限校验、优化、执行以及与存储引擎的交互；存储引擎层负责数据的实际存储与提取，是插件式的。

2.1 连接器

连接器负责与客户端建立 TCP 连接、进行身份认证、从权限表中获取用户权限，并管理连接的生命周期。如果连接长时间没有活动（默认为 8 小时），连接器会将其断开，后续请求会收到Lost connection错误。

2.2 查询缓存（已废弃）

在 MySQL 8.0 之前，SQL 语句到达后会先查询缓存，如果命中则直接返回结果，避免后续的解析与执行。但由于缓存命中率低、失效频繁（表的任何更新都会清空相关缓存），且在高并发场景下容易成为性能瓶颈，因此在 8.0 版本被正式移除。

2.3 分析器

分析器首先进行词法分析，将 SQL 文本拆解为关键字、表名、字段名等 Token；然后进行语法分析，根据 MySQL 定义的语法规则检查 SQL 语句是否合法，构建AST（抽象语法树）。如果语法错误，会直接抛出You have an error in your SQL syntax。

语法分析之后还有预处理器，负责检查表或列是否存在、进行名称解析和权限校验，为后续优化器准备好语义上下文。

2.4 优化器

优化器是 SQL 性能的关键角色。它根据表的统计信息、索引情况和查询条件，按照成本模型评估多种可能的执行计划（例如选择哪个索引、多表连接的顺序），并选出成本最低的方案。优化器可以进行的优化包括：

  • 选择合适的索引或全表扫描；
  • 决定多表连接顺序（Nested-Loop Join、Hash Join 等）；
  • 重写子查询（子查询物化、半连接优化等）；
  • 条件下推（Index Condition Pushdown、Derived Merge 等）。

优化器的决策可以通过EXPLAIN命令查看，是日常 SQL 调优的核心工具。

2.5 执行器与存储引擎

执行器拿到最终的执行计划后，开始逐条执行。对于SELECT语句，执行器会先进行权限校验，然后逐行读取数据；对于UPDATE语句，则会定位到具体行进行更新并写日志。所有数据的读取和写入都是通过调用存储引擎 API完成的，执行器本身不管理物理存储。这种设计使得 MySQL 可以灵活切换存储引擎，例如将查询密集型应用使用 InnoDB，而某些归档场景使用 MyISAM。

三、应用场景

3.1 日常开发场景

  • 慢查询分析：通过EXPLAIN查看执行计划，了解优化器的选择，发现全表扫描、未使用索引等问题。
  • 索引设计：理解优化器如何使用索引，有助于合理设计复合索引、覆盖索引。
  • 连接池配置：了解连接器的长连接和短连接特性，用于 JDBC 连接池参数调优。

3.2 企业级真实场景

  • 分库分表后的路由优化：在 ShardingSphere 等中间件中，SQL 解析后需要根据分片键改写 SQL，与 MySQL 的分析器原理类似。
  • 数据库中间件开发：自研数据库代理（如 ProxySQL、MaxScale）必须模拟 MySQL 的 SQL 解析、执行计划生成流程。
  • SQL 审核平台：通过分析 SQL 的执行计划，在上线前自动检测潜在的性能风险，防止拖垮数据库。

四、使用方式

下面以 Java 为例，演示通过 JDBC 执行一条简单的查询语句，并说明其与 MySQL 内部执行过程的对应关系。

import java.sql.*; public class MySQLExecutionDemo { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=UTC"; String user = "root"; String password = "your_password"; // 1. JDBC 驱动注册（JDBC 4.0 后可省略） // 2. 建立连接 —— 对应连接器 try (Connection conn = DriverManager.getConnection(url, user, password); // 3. 创建 Statement —— 准备好发送 SQL Statement stmt = conn.createStatement()) { String sql = "SELECT id, name FROM users WHERE status = 1 ORDER BY create_time DESC LIMIT 10"; System.out.println("发送 SQL -> " + sql); // 4. 执行查询 —— SQL 经过分析器、优化器，由执行器通过存储引擎获取数据 try (ResultSet rs = stmt.executeQuery(sql)) { // 5. 处理结果集 while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); System.out.println("ID: " + id + ", Name: " + name); } } } catch (SQLException e) { e.printStackTrace(); } } }

执行流程及注意事项

  • 建立连接：DriverManager.getConnection()触发 TCP 三次握手，MySQL 连接器进行身份认证并分配线程，对应流程中的连接器阶段。
  • 发送 SQL：stmt.executeQuery()将 SQL 字符串发送给 MySQL Server，进入分析器和优化器。
  • 结果获取：ResultSet.next()每次调用都可能触发网络 I/O 或从客户端缓存中读取行数据，具体由 JDBC 驱动实现（如 MySQL Connector/J 默认将结果集全部读入内存）。
  • 注意事项：务必使用try-with-resources确保 Connection、Statement、ResultSet 关闭；生产环境推荐使用连接池（如 HikariCP）复用连接，减少连接器频繁创建销毁的开销。

五、扩展延伸

5.1 与其他数据库对比

与 PostgreSQL 相比，MySQL 的优化器基于成本模型相对简单，而 PostgreSQL 采用基于规则的优化器与成本模型结合，并支持更多的查询重写规则。但 MySQL 8.0 引入了 Hash Join、窗口函数等特性，优化能力大幅提升。在存储引擎层，MySQL 的插件式架构允许按表选择引擎，具有更高的灵活性。

5.2 不同存储引擎的执行差异

存储引擎查询执行差异事务支持
InnoDB支持 MVCC、事务、行锁；执行器通过聚簇索引读取数据，非主键索引回表支持
MyISAM不支持事务，表锁；执行器直接使用表共享或独占锁读取不支持
Memory数据存储在内存，Hash 索引，重启后数据丢失，执行速度极快不支持

5.3 实际开发注意事项

  • 连接管理：长连接会占用 MySQL 内存，建议定期断开长连接或使用连接池的 TestOnBorrow 等机制。
  • SQL 书写：尽量使用参数化查询，避免 SQL 注入，同时也能减少 SQL 硬解析的开销（MySQL 的 PreparedStatement 在服务器端会预编译）。
  • 权限最小化：连接器获取的权限是在连接时确定的，若修改了用户权限，已有连接不会立即生效，需要重新连接。

六、面试追问

追问一：为什么 MySQL 8.0 移除了查询缓存？

回答思路：查询缓存的失效粒度是表级别的，任何对表的写操作都会使其相关缓存全部失效。在高并发写入场景下，缓存的失效和重建会带来额外的锁竞争，反而降低吞吐量。此外，缓存命中率在动态数据上极低，对只读场景的收益有限，反而增加了维护成本。8.0 移除后，推荐使用 Redis 等外部缓存或 Proxy 层缓存。

追问二：优化器总是能选择最优的执行计划吗？如果选错了怎么办？

回答思路：不一定。优化器依赖统计信息，如果统计信息不准确（例如表频繁增删导致直方图失效）或查询过于复杂，可能导致错误的索引选择或连接顺序。可以通过ANALYZE TABLE更新统计信息，或使用FORCE INDEX、STRAIGHT_JOIN、optimizer_switch参数干预执行计划。生产环境下还会结合EXPLAIN JSON和OPTIMIZER_TRACE详细排查。

追问三：一条 UPDATE 语句的执行过程和 SELECT 有何不同？

回答思路：基本的前期阶段（连接器、分析器、优化器）相同，但在执行器阶段，UPDATE 需要定位到具体行并获取行锁（InnoDB），然后更新数据并写入undo log和redo log，最后提交事务并释放锁。而 SELECT 在默认隔离级别下直接进行一致性读，不加锁。从整体流程上看，UPDATE 涉及更多的日志和事务操作，对磁盘 I/O 和锁管理的要求更高。

编程学习 学习日记 实战经验
← 返回列表