mysql一条sql语句详细执行过程

数据中穿行
5 阅读6分钟

下面详细拆解MySQL 中一条 SQL 语句的完整执行过程，以最常用的 InnoDB 存储引擎为例，结合其架构分层，呈现从客户端发起请求到返回结果的所有步骤。

整体流程概览

客户端请求 → 连接管理 → 查询缓存(8.0移除) → 解析器 → 预处理器 → 优化器 → 执行器 → 存储引擎 → 返回结果

详细步骤拆解

假设我们执行一条查询：SELECT * FROM users WHERE id = 1;

阶段 1：连接与认证

  1. 客户端连接：应用程序通过 TCP/IP 或 Unix Socket 向 MySQL 服务器发起连接请求。

  2. 连接器处理：

    • 验证用户名、密码、主机权限。
    • 在 mysql.user表中检查账户信息。
    • 如果验证通过，建立连接并分配一个线程（从线程池获取或创建新线程）。
    • 关键点：此时获取的权限将用于整个连接生命周期。即使管理员修改了用户权限，已存在的连接也不会受影响，除非重新连接。

阶段 2：查询缓存（MySQL 8.0 已移除）

  • 注意：在 MySQL 8.0 之前存在此步骤，8.0 及之后版本完全移除了查询缓存。

  • 如果是 8.0 之前的版本：

    1. 将查询语句作为 key 在缓存中查找。
    2. 如果命中，直接返回结果，跳过后续所有步骤。
    3. 如果未命中，继续后续处理，执行完成后将结果存入缓存。

阶段 3：解析与预处理

  1. 解析器：

    • 词法分析：将 SQL 语句拆解为“单词”（token）。如 SELECT、*、FROM、users、WHERE、id、=、1。
    • 语法分析：根据 MySQL 语法规则，将这些 token 组合成一棵解析树。此时会检查基本语法错误，如关键字拼写错误、缺少括号等。

  2. 预处理器：

    • 语义检查：检查解析树中表和列是否存在，验证列名和数据类型是否有效。
    • 权限检查：检查用户对相关表是否有执行权限。
    • 视图展开：如果查询涉及视图，会将视图定义展开为底层表查询。
    • 查询重写：进行一些简单的语义优化，如常量表达式计算（WHERE 1=1会被优化掉）。

阶段 4：查询优化

优化器是 MySQL 的“大脑” ，负责将解析树转化为最高效的执行计划。

  1. 逻辑优化：

    • 移除不必要的操作，如 SELECT *优化为实际列名。
    • 合并多个查询块。
    • 对条件进行化简、重排序，使过滤更早执行。

  2. 物理优化与代价估算：

    • 索引选择：优化器会考虑所有可能的索引使用方式：

      • 主键索引
      • 二级索引
      • 全表扫描

    • 连接顺序：对于多表连接，决定表的连接顺序（动态规划或贪心算法）。

    • 访问方法：决定如何从表中读取数据（ref, range, index, all 等）。

    • 代价估算：

      • 查询 information_schema获取表的统计信息（行数、索引区分度、直方图等）。
      • 估算每种执行计划的 IO 成本（读取数据页数）和 CPU 成本（处理行数）。
      • 选择总成本最低的计划。

  3. 生成执行计划：

    • 最终产生一个可执行的查询计划，通常以树形结构表示。
    • 可以通过 EXPLAIN命令查看优化器选择的计划。

阶段 5：查询执行

执行器​ 是优化器与存储引擎之间的桥梁，负责按计划逐步执行。

  1. 初始化：准备执行环境，打开相关表，获取表结构元数据。

  2. 调用存储引擎 API：

    • 执行器不直接操作数据，而是通过定义的存储引擎接口调用 InnoDB 的功能。
    • 对于我们的示例查询，执行器会调用“读取满足条件的第一行记录”的接口。

阶段 6：存储引擎处理

InnoDB 引擎内部的具体操作：

  1. 缓冲池查找：

    • InnoDB 首先在缓冲池中查找 id=1对应的数据页。
    • 如果找到（缓存命中），直接读取内存中的数据。

  2. 磁盘读取：

    • 如果缓冲池未命中，InnoDB 会：

      a. 从磁盘的 .ibd表空间文件中读取包含该行的数据页到缓冲池。

      b. 如果存在Change Buffer​ 中缓存的二级索引更新，会在此刻合并。

  3. 索引查找（如果 id 是主键）：

    • InnoDB 使用 B+ 树主键索引查找：

      1. 从根节点开始，通过比较键值找到对应的子节点。
      2. 沿着 B+ 树的非叶子节点层层向下，直到找到对应的叶子节点。
      3. 在叶子节点中，由于是聚集索引，直接包含完整的行数据。

  4. 行数据读取：

    • 从索引叶子节点读取整行数据。
    • 如果查询使用了覆盖索引，则直接从索引中获取所需列，无需回表。

  5. 事务与并发控制：

    • MVCC 可见性检查：根据当前事务的隔离级别和 Read View，判断该行数据的版本对当前事务是否可见。
    • 如果使用可重复读（RR）隔离级别，会检查行的 DB_TRX_ID和事务的 Read View。
    • 锁检查：如果是更新操作，会尝试获取相应的行锁。

  6. 返回数据：将满足条件的行数据（经过可见性过滤后）返回给执行器。

阶段 7：结果返回

  1. 执行器处理：执行器接收存储引擎返回的数据行，可能会做进一步处理（如排序、聚合等）。

  2. 结果集构建：将数据放入结果集中。

  3. 返回客户端：

    • 通过连接线程将结果发送回客户端。
    • 如果是网络传输，会将结果序列化为 MySQL 协议格式的数据包。
    • 客户端接收并解析这些数据包，呈现给用户。

针对 UPDATE 语句的额外步骤

如果是 UPDATE users SET name='张三' WHERE id=1;，除了上述步骤外，还有：

  1. 写 Undo Log：修改前，将旧数据写入 Undo Log，用于回滚和 MVCC。

  2. 修改缓冲池：在内存中修改数据页，标记为脏页。

  3. 写 Redo Log Buffer：将物理修改写入 Redo Log Buffer。

  4. 事务提交：用户执行 COMMIT 时：

    • Redo Log Buffer 刷入磁盘的 Redo Log 文件（保证持久性）。
    • 写入 Binlog（用于主从复制）。
    • 标记事务为提交状态。

  5. 后台刷脏：Checkpoint 或后台线程将脏页异步刷回磁盘。

可视化执行流程图

客户端请求
    ↓
连接器（认证/权限）
    ↓
解析器（词法/语法分析 → 解析树）
    ↓
预处理器（语义检查/权限/视图展开）
    ↓
优化器（基于代价选择执行计划）
    ↓
执行器
    ↓
存储引擎 API
    ↓
InnoDB 引擎
├─ 缓冲池查找
├─ 磁盘读取（如未命中）
├─ 索引遍历（B+树）
├─ MVCC 可见性检查
├─ 锁检查（如需要）
└─ 返回行数据
    ↓
执行器（构建结果集）
    ↓
返回客户端

性能关键点

  1. 连接管理：避免频繁创建连接，使用连接池。
  2. 解析与优化：复杂查询的解析优化可能耗时，可通过预编译语句（Prepared Statement）减少重复解析。
  3. 缓冲池命中率：决定 IO 性能的关键，应足够大以容纳热点数据。
  4. 索引选择：不合适的索引会导致全表扫描，性能急剧下降。
  5. Redo Log 写入：事务提交的瓶颈所在，组提交可优化。

这个完整流程展示了 MySQL 如何将一个简单的 SQL 语句转化为底层的存储操作，每个环节都有复杂的设计考量，共同保证了数据库的正确性、一致性和高性能。

avatar
文章
阅读
粉丝