跳转到内容
logo logo 周行ITJava全栈/大模型应用开发

MySQL架构

一条 SQL 语句的执行全流程

💡

这是腾讯等大厂面试的高频题：执行 SELECT * FROM product WHERE id = 1; 中间到底发生了什么？本文从 MySQL 架构分层的视角，完整拆解一条 SQL 语句从客户端到存储引擎的全链路执行过程。

一、MySQL 架构分层

1.1 整体架构图

MySQL 的架构分为两大层：

┌─────────────────────────────────────────────────────┐
│                    客户端                            │
│               mysql -u root -p                       │
└─────────────────┬───────────────────────────────────┘
                  │ TCP 连接
┌─────────────────▼───────────────────────────────────┐
│                   Server 层                          │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐          │
│  │  连接器   │→│  解析器   │→│  优化器   │→ 执行器   │
│  │Connector │  │ Parser   │  │Optimizer │ Executor  │
│  └──────────┘  └──────────┘  └──────────┘          │
│       ↑                         ↓                   │
│  ┌──────────┐              ┌──────────┐             │
│  │ 查询缓存  │              │ 内置函数   │             │
│  │(8.0废弃)│              │ 日期/数学..│             │
│  └──────────┘              └──────────┘             │
└─────────────────┬───────────────────────────────────┘
                  │ 调用存储引擎接口
┌─────────────────▼───────────────────────────────────┐
│                存储引擎层                             │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐          │
│  │  InnoDB  │  │ MyISAM   │  │  Memory  │          │
│  │ (默认)    │  │          │  │          │          │
│  └──────────┘  └──────────┘  └──────────┘          │
│       ↑                                              │
│  B+Tree 索引 / 数据页 / Buffer Pool / Redo Log       │
└─────────────────────────────────────────────────────┘
📌

Server 层：负责连接管理、SQL 解析、优化、执行。所有跨存储引擎的功能都在这里实现（如存储过程、触发器、视图）。 存储引擎层：负责数据的存储和读取。采用插件式架构，支持 InnoDB、MyISAM、Memory 等。MySQL 5.5 后 InnoDB 成为默认存储引擎。

1.2 Server 层核心模块

模块

定位

核心职责

连接器

入口

建立连接、权限验证、连接管理

查询缓存（8.0 废弃）

缓存层

缓存查询结果，命中直接返回

解析器

语法层

词法分析 → 语法分析 → 生成语法树

预处理器

语义层

检查表/字段是否存在，展开 *

优化器

决策层

选择索引、决定 JOIN 顺序、生成执行计划

执行器

交互层

调用存储引擎接口，逐行获取数据

内置函数

工具层

日期、数学、字符串等通用函数

二、完整执行流程

以 SELECT * FROM product WHERE id = 1; 为例。

2.1 第一步：建立连接

mysql -u root -p

连接的过程使用 TCP 三次握手：

  1. 客户端 → 服务端：SYN
  2. 服务端 → 客户端：SYN + ACK
  3. 客户端 → 服务端：ACK

TCP 连接建立后，连接器进行身份认证：

  • 验证用户名和密码
  • 查询权限表，缓存该连接的权限信息
-- 查看当前连接数
SHOW PROCESSLIST;

-- 查看最大连接数（默认 151）
SHOW VARIABLES LIKE 'max_connections';

-- 设置最大连接数
SET GLOBAL max_connections = 500;

连接管理细节

参数

默认值

说明

max_connections

151

最大连接数

wait_timeout

28800（8小时）

非交互连接空闲超时自动断开

interactive_timeout

28800（8小时）

交互连接空闲超时
💡

连接数过多怎么办？ 连接是昂贵的资源（每个连接分配线程内存）。连接数耗尽 → 新连接报错 "Too many connections"。解决方案：增大 max_connections、使用连接池、及时释放空闲连接。

长连接 vs 短连接

类型

特点

推荐场景

长连接

持续使用同一连接，减少 TCP 握手开销

频繁查询的业务

短连接

每次查询完就断开

低频查询
💡

长连接的缺点是 MySQL 临时使用的内存只有在连接断开时才释放。长时间累积可能导致 OOM。解决方案：定期断开长连接（如每执行一个大的操作后 mysql_reset_connection 重置连接状态）。

2.2 查询缓存（MySQL 8.0 已废弃）

在 MySQL 8.0 之前：

-- 查询缓存是否开启
SHOW VARIABLES LIKE 'query_cache_type';

工作原理：

  1. SQL 语句作为 key，查询结果作为 value 存入缓存
  2. 下次相同 SQL 直接返回缓存结果（不经过解析/优化/执行）
  3. 但是：只要表有任何更新操作（INSERT/UPDATE/DELETE），这张表的所有缓存全部失效
❌

为什么废弃？ 对于频繁更新的表，缓存命中率极低（刚缓存就被清掉）。缓存的维护开销反而拖慢性能。MySQL 8.0 直接移除了查询缓存模块。现在的替代方案是应用层缓存（Redis）。

2.3 第二步：解析 SQL

2.3.1 词法分析（Lexical Analysis）

解析器首先将 SQL 字符串拆解为一个个 Token（词法单元）：

SELECT * FROM product WHERE id = 1;

拆解结果：

SELECT  → 关键字（keyword）
*       → 通配符
FROM    → 关键字（keyword）
product → 标识符（表名）
WHERE   → 关键字（keyword）
id      → 标识符（字段名）
=       → 运算符
1       → 数值字面量
;       → 结束符

2.3.2 语法分析（Syntax Analysis）

词法分析完成后，语法解析器根据 MySQL 的语法规则判断输入的 SQL 语句是否合法。如果 SQL 语法有误，在这个阶段就会报错：

ERROR 1064 (42000): You have an error in your SQL syntax...

如果语法正确，解析器会构建一棵 SQL 语法树（AST，抽象语法树）：

                    SELECT
                   /      \
              列列表      表名&条件
                |            |
                *          WHERE
                         /       \
                       id         =
                                  |
                            table: product
                           /          \
                      column: id    value: 1
📌

对于这样一棵语法树，使用后序遍历（左 → 右 → 中），可以将叶子节点的值按照语法规则重新组合为一条完整的 SQL 语句。这是理解 SQL 解析的核心。

2.4 第三步：执行 SQL

执行阶段分为三个子步骤：预处理 → 优化 → 执行。

2.4.1 预处理阶段（Preprocessor）

检查项

说明

表是否存在

检查 product 表是否在数据库中

字段是否存在

检查 id 字段是否在 product 表中

展开 *

将 SELECT * 扩展为表中的所有列 (id, name, price, ...)

如果表或字段不存在，此阶段直接报错。

2.4.2 优化阶段（Optimizer）

优化器负责确定执行计划——从多个可能的执行方案中选择代价最小的一个。

-- 查看优化器选择的执行计划
EXPLAIN SELECT * FROM product WHERE id = 1;

输出的关键字段：

字段

含义

id

查询序号

select_type

查询类型（SIMPLE / PRIMARY / SUBQUERY 等）

type

访问类型（const > eq_ref > ref > range > index > ALL）

possible_keys

可能使用的索引

key

实际使用的索引

rows

预估扫描行数

Extra

额外信息（Using index / Using filesort / Using temporary）

优化器如何选择索引？

对于 SELECT * FROM product WHERE id = 1;，假设 id 是主键：

  • 主键索引（聚簇索引）：B+Tree 叶子节点存储的是完整的行数据，查询成本：一次树搜索
  • 如果还有普通索引，优化器会基于查询成本来选——哪个索引预估扫描行数更少，就选哪个
📌

成本计算：优化器根据统计信息（innodb_stats_persistent）估算每个索引需要扫描的行数，选择代价最小的索引。主键索引查一行通常是最优选择（const 级别）。

  • 主键索引（聚簇索引）：B+Tree 叶子节点存储的是完整的行数据，查询成本：一次树搜索即可定位
  • 普通索引（二级索引）：B+Tree 叶子节点存储的是主键 ID，查完还需要回表到主键索引再查一次
📌

优化器的选择逻辑：主键索引虽然叶子节点更大（存了整行），但等值查询时树高度更低，定位更快。优化器综合考虑 I/O 成本和 CPU 成本来选择最优索引。用 EXPLAIN 可以看到优化器最终的选择。

2.4.3 执行阶段（Executor）

执行器拿着优化器生成的执行计划，开始与存储引擎层交互。

SELECT * FROM product WHERE id = 1;

执行流程：

执行器                         存储引擎(InnoDB)
  |                                  |
  |--"打开 product 表"--------------->|  获取表元数据
  |                                  |
  |--"读取 id=1 的第一行"------------>|  在 B+Tree 中定位 id=1
  |                                  |  返回行数据
  |<--返回行数据----------------------|
  |                                  |
  |--"满足条件了吗？"（如果有WHERE）--|
  |                                  |
  |--"还有下一行吗？"---------------->|
  |<--"没有了"-----------------------|
  |                                  |
  返回结果集给客户端

执行器的三种常见查询方式

方式

说明

EXPLAIN type

主键等值查询

B+Tree 精确匹配一行

const

主键/索引范围查询

B+Tree 叶子节点链表支持范围扫描

range

全表扫描

无索引可用，遍历所有数据页

ALL

2.5 索引下推（Index Condition Pushdown / ICP）

📌

索引下推是 MySQL 5.6 引入的重要优化，将 Server 层的一部分过滤逻辑下推到存储引擎层，减少回表次数，显著提升查询效率。

问题场景

假设有联合索引 (age, reward)，执行：

SELECT * FROM user WHERE age > 20 AND reward = 1000;

对于联合索引，遇到 &gt; 或 &lt; 这种范围查询后，后面的索引列会失效。

MySQL 5.6 之前（没有 ICP）

1. 存储引擎定位到 age > 20 的第一条记录
2. 拿到该记录的主键 id
3. ↓ 回表查询（到主键索引查完整行）
4. 将完整行返回给 Server 层
5. Server 层判断 reward 是否等于 1000
6. 如果不等，丢弃，回到步骤 1 继续

问题：每条 age > 20 的记录都要回表！

MySQL 5.6 之后（有 ICP）

1. 存储引擎定位到 age > 20 的第一条记录
2. ↓ 先在存储引擎层判断 reward 是否等于 1000
3. 如果 reward = 1000 → 才回表查询主键索引
4. 如果 reward ≠ 1000 → 直接跳过，不回表！

优势：大量不符合 reward=1000 的记录无需回表！
✅

ICP 的核心价值：将 Server 层的过滤条件交给存储引擎执行，在索引层面就过滤掉不满足条件的记录，减少了大量无意义的回表操作。这是"把计算推近数据"思想的体现。

查看是否使用 ICP

EXPLAIN SELECT * FROM user WHERE age > 20 AND reward = 1000;

Extra 列中出现 Using index condition 说明使用了索引下推。

三、执行完成后

3.1 结果集返回

SQL 执行完成后，MySQL 将结果集以字节流的形式通过网络返回给客户端。如果结果集很大，MySQL 采用流式返回（边读边发），不会一次性把所有数据加载到内存。

3.2 完整流程图（总览）

客户端发起连接（TCP 三次握手）
       │
       ▼
  ┌─────────┐
  │  连接器  │  身份认证 + 权限检查
  └────┬────┘
       │
       ▼
  ┌─────────┐
  │  解析器  │  词法分析(Token) → 语法分析(语法树)
  └────┬────┘
       │
       ▼
  ┌─────────┐
  │ 预处理器  │  检查表/字段是否存在，展开 *
  └────┬────┘
       │
       ▼
  ┌─────────┐
  │  优化器  │  选择索引、确定执行计划
  └────┬────┘
       │
       ▼
  ┌─────────┐
  │  执行器  │  调用存储引擎接口读取数据
  └────┬────┘
       │
       ▼
  ┌─────────────┐
  │  存储引擎层  │  InnoDB → B+Tree → Buffer Pool → 数据页
  └────┬────────┘
       │
       ▼
  结果集返回给客户端（字节流）

四、面试要点总结

⭐

面试被问到"一条 SQL 语句的执行流程"，回答框架：

  1. 连接器：TCP 握手 → 身份认证 → 权限缓存
  2. 解析器：词法分析（生成 Token）→ 语法分析（生成语法树）
  3. 预处理器：检查表和字段是否存在 → 展开 *
  4. 优化器：选择索引 → 生成执行计划（EXPLAIN 可验证）
  5. 执行器：调用存储引擎接口，逐行读取数据
  6. 存储引擎：InnoDB 通过 B+Tree 定位数据页，Buffer Pool 缓存
  7. 返回结果：字节流返回给客户端

加分项：提到查询缓存（8.0 废弃及原因）、索引下推（ICP，MySQL 5.6+）、优化器的成本计算模型

📌

关键数据结构：

  • B+Tree：InnoDB 的默认索引结构，叶子节点形成有序链表，支持范围查询
  • Buffer Pool：内存缓冲区，缓存数据页和索引页，减少磁盘 IO
  • Redo Log：重做日志，保证事务持久性（crash-safe）
  • Undo Log：回滚日志，保证事务原子性 + MVCC 多版本控制
✅

一句话总结：MySQL 的 Server 层负责"理解 SQL 并决定怎么做"，存储引擎层负责"实际存取数据"。两者通过标准接口解耦，使得 MySQL 可以灵活切换底层存储引擎。理解这个分层架构是掌握 MySQL 的关键。