殷龙飞的博客

MySQL 硬核解析专题 - 第二篇：SQL 执行的幕后故事

 数据库/MySQL  1893  4 分钟

上一章我们聊了 MySQL 的架构，知道它像一个分工明确的工厂。今天我们聚焦一条 SQL 语句的“旅程”，比如 SELECT * FROM users WHERE age > 18; 是怎么从你敲下回车，到屏幕吐出数据的。这背后藏着解析、优化和执行的硬核细节，搞懂这些，你就能写出更高效的 SQL。

一、SQL 执行的全流程概览

MySQL 处理一条 SQL 大致经历以下几个阶段：

  1. 连接与接收：客户端发送 SQL，连接层接管。
  2. 解析与预处理：服务层把 SQL 拆解成可执行的指令。
  3. 查询优化：优化器决定执行路径。
  4. 执行与存储引擎交互：交给存储引擎去拿数据。
  5. 结果返回：数据加工后送回客户端。

下面我们逐一拆解每个阶段，看看 MySQL 是怎么“思考”和“干活”的。

二、详细拆解 SQL 执行过程

  1. 连接与接收：SQL 的起点

    • 发生了什么：你通过客户端（比如命令行、JDBC）发送 SQL，MySQL 的连接层接收请求，校验权限后分配一个线程。
    • 细节：如果是高并发场景，线程池会派上用场，避免每次新建线程的开销。如果连接失败，你可能看到 Access denied 或 Too many connections。
    • 硬核点：可以用 SHOW PROCESSLIST; 查看当前连接和正在执行的 SQL，State 列会显示线程的状态，比如 Sending data。

  2. 解析与预处理：从字符串到指令

    • 词法解析：MySQL 把 SQL 拆成一个个 token。比如 SELECT * FROM users 被拆成 SELECT、*、FROM、users。
    • 语法解析：生成抽象语法树（AST），检查语法是否合法。如果写错了，比如 SELEC * FROM users，会抛出 You have an error in your SQL syntax。
    • 语义检查：确保表名、列名存在，权限足够。比如 users 表不存在，就会报 Table doesn't exist。
    • 硬核点：MySQL 的解析器基于 C 写的词法分析工具（如 lex 和 yacc），效率极高，但不支持太复杂的语法（比如嵌套过深的子查询可能会卡住）。

  3. 查询优化：大脑的决策时刻

    • 核心任务：优化器分析 SQL，生成最优的执行计划。
    • 怎么优化：
      • 选择索引：比如 WHERE age > 18，如果 age 有索引，优化器可能会用它。
      • 重写查询：把子查询改成 JOIN，或者合并条件。
      • 估算成本：MySQL 会评估每种执行路径的“代价”（Cost），包括 IO 和 CPU 开销，选择代价最低的。
    • 举个例子：SELECT * FROM users WHERE age > 18 AND name = 'Tom';
      • 可能路径 1：用 age 索引扫一部分，再过滤 name。
      • 可能路径 2：用 name 索引扫少量数据，再过滤 age。
      • 优化器会根据统计信息（比如索引的基数）决定走哪条路。
    • 硬核点：用 EXPLAIN 查看执行计划，关注 type（访问类型，如 ref 或 range）、key（用的索引）和 rows（预计扫描行数）。

  4. 执行与存储引擎交互：干活的时间

    • 查询缓存（历史遗留）：MySQL 8.0 之前，如果启用了查询缓存（query_cache_type=1），会先查缓存。缓存命中就直接返回。但 8.0 后废弃了，因为它在高并发下锁竞争严重。
    • 存储引擎执行：优化器选好计划后，交给存储引擎（比如 InnoDB）。如果是 SELECT，引擎从缓冲池或磁盘取数据；如果是 INSERT/UPDATE，会写数据和日志。
    • 细节：InnoDB 会先查缓冲池，命中就直接读，没命中就从磁盘加载到缓冲池。范围查询会顺着 B+树的叶子节点链表走。
    • 硬核点：可以用 SHOW PROFILE;（需要开启）看执行耗时，或者 SHOW ENGINE INNODB STATUS; 查锁和缓冲池状态。

  5. 结果返回：旅程的终点

    • 加工数据：服务层把存储引擎返回的原始数据整理好，比如排序（ORDER BY）、分组（GROUP BY）。
    • 发送客户端：通过网络协议（TCP 或 Socket）把结果发回去。如果数据量大，分批发送，避免内存爆炸。
    • 硬核点：可以用 LIMIT 控制返回行数，或者加大 net_buffer_length（默认 16KB）提升大结果集的传输效率。

三、慢查询的“罪魁祸首”在哪里？

SQL 执行慢，可能卡在哪个环节？

  • 解析慢：SQL 太复杂（嵌套子查询、大量 JOIN），解析器顶不住。
  • 优化慢：表数据量大，统计信息不准，优化器选错计划。
  • 执行慢：没索引走全表扫描，或者缓冲池太小，频繁读磁盘。

解决办法：

  • 用 EXPLAIN 定位问题，比如 type=ALL 表示全表扫描，得加索引。
  • 检查 slow_query_log，找出耗时超过阈值（默认 10 秒）的 SQL。

四、实战演练

试试这条 SQL：

1

SELECT name, age FROM users WHERE age > 18 ORDER BY age LIMIT 10;
  1. 跑 EXPLAIN：
    • 如果 age 有索引，key 可能是 idx_age，type 是 range。
    • 如果没索引，type 会是 ALL，性能堪忧。
  2. 开慢查询日志：
    • SET GLOBAL slow_query_log = 'ON';
    • SET GLOBAL long_query_time = 1; （单位秒）
    • 执行后查日志，看这条 SQL 是否上榜。

五、从执行看优化方向

  • 索引：加快存储引擎的查找。
  • 缓存：缓冲池调大，减少 IO。
  • SQL 精简：少用 *，避免不必要的排序和分组。

结语

这一篇我们追踪了一条 SQL 的完整生命周期，从连接到返回，摸清了 MySQL 的“工作节奏”。搞懂这些，你就能预判 SQL 的性能瓶颈，甚至跟 DBA 聊优化时更有底气。下一章我们会聊 性能优化的硬核技巧，从索引到配置，给你一套实战组合拳。

updatedupdated2025-03-312025-03-31

相关文章：