MySQL 一条 SQL 的执行全流程

客户端（如 Java 应用、MySQL CLI、Navicat 等）通过 MySQL 协议与 Server 建立 TCP 连接。默认端口 3306，支持 UNIX Socket（本机）和命名管道（Windows）。

连接时需要提供：主机地址、端口、用户名、密码。MySQL 支持多种认证插件：mysql_native_password、caching_sha2_password 等。

连接器（Connector）是 Server 层的第一个组件，负责：

① 身份认证：校验用户名和密码，查询 mysql.user 权限表。若认证失败返回 Access denied

② 权限获取：认证成功后，从权限表中读取该用户的全局权限。后续若有 USE database，还会读取库级权限；访问具体表时读取表级权限

③ 分配线程：从线程池中分配一个线程（或新建线程）来处理该连接的所有请求。MySQL 采用每个连接一个线程（one-thread-per-connection）模型

④ 空闲管理：连接空闲超过 wait_timeout（默认 8 小时）后自动断开。可通过 show processlist 查看当前所有连接

查询缓存以SQL 文本的哈希值为 Key，查询结果集为 Value，缓存在内存中。

匹配条件极其严格：SQL 必须完全一致（包括空格、大小写），且涉及的表没有发生任何数据变更。

缓存失效代价很高——任何一个表的任何数据变更，都会清空该表的所有查询缓存。在高写入场景下，缓存命中率极低，反而成为性能瓶颈。

⚠️ 已废弃 MySQL 8.0 已完全移除查询缓存功能。替代方案：使用 Redis 等外部缓存系统。

词法分析器（Lexer/Scanner）将 SQL 字符串拆解为一系列 Token：

例如 SELECT name FROM employee WHERE age > 25 会被拆为：

SELECT name FROM employee WHERE age > 25

每个 Token 有类型标识：SELECT 是保留字，name 是标识符，25 是数字字面量，> 是运算符。

如果 SQL 中包含无法识别的字符，在此阶段就会报错：You have an error in your SQL syntax。

语法分析器（Parser）根据 MySQL 预定义的语法规则（BNF 文法），将 Token 序列构建成一棵 抽象语法树（AST，Abstract Syntax Tree）。

AST 的节点类型包括：Select_stmt、Expr、Table_ref、Where_clause、Order_by 等，完整表达了 SQL 的语法结构。

此阶段还会做基本的语法合法性检查：比如 SELECT 缺少 FROM 子句（在某些上下文中）、表达式不完整、缺少右括号等。

💡 注意 语法分析只管语法对不对，不管语义（表存不存在、字段存不存在）。

预处理器对 AST 进行语义分析：

① 表/列存在性检查：查询数据字典（information_schema），验证 SQL 中引用的表、字段、视图是否存在。不存在则报错：Table 'xxx' doesn't exist

② 权限检查：验证当前用户对涉及的表是否有 SELECT 权限。无权限则报错：SELECT command denied

③ 语义合理性：检查 WHERE、GROUP BY 等子句是否合法，列别名是否能被正确解析

④ 隐式类型转换：标记需要隐式转换的表达式，为后续优化提供信息

优化器是 MySQL 的核心大脑，负责将逻辑执行计划转化为物理执行计划。它基于代价模型（Cost-Based Optimizer，CBO），选择代价最低的执行路径。

优化器的主要工作：

可通过 EXPLAIN / EXPLAIN ANALYZE 查看优化器最终选择的执行计划。MySQL 8.0 引入了直方图（Histogram），为优化器提供更精确的数据分布统计。

优化器输出的是一个执行计划，它是一棵由物理操作符组成的树。常见操作符包括：

seq_scan 全表扫描  index_scan 索引扫描  ref 索引查找  range 范围扫描  nested_loop 嵌套循环连接

执行计划决定了数据访问路径、JOIN 策略、排序方式、临时表使用等所有执行细节。

执行器是 Server 层与存储引擎层之间的桥梁。它按照执行计划，通过 Handler API 逐行（或批量）调用存储引擎获取数据。

执行器的工作流程（以我们的 JOIN 查询为例）：

① 先从 department 表查询 dept_name = 'engineering' 的记录（调用 handler::index_read）

② 拿到 dept_id 后，再从 employee 表中查找匹配记录（调用 handler::index_read 或 handler::rnd_next）

③ 对每条满足条件的记录，执行器做进一步的 WHERE 条件过滤（有些条件无法利用索引）

④ 按 age DESC 排序，取前 10 条，返回结果集

💡 Server 层过滤 有些 WHERE 条件无法下推到存储引擎，需要执行器在 Server 层做二次过滤。这就是为什么有时候看到 Using where（执行器过滤）。

以 InnoDB 为例，数据读取流程：

① 查找 Buffer Pool：InnoDB 维护了一个大的内存缓冲池（innodb_buffer_pool_size，通常设为物理内存的 60%-80%），存储数据页和索引页

② 缓存命中：如果请求的数据页已在 Buffer Pool 中，直接从内存读取，速度极快

③ 缓存未命中：如果不在 Buffer Pool 中，触发页面读取请求。InnoDB 通过预读（Read-Ahead）机制，一次读取相邻的多个页面到 Buffer Pool

④ LRU 淘汰：Buffer Pool 满时，基于改进的 LRU 算法（区分 Young 区和 Old 区，防止全表扫描污染缓存）淘汰冷页面

如果是索引扫描，InnoDB 通过 B+Tree 结构查找：

① 从 B+Tree 根节点开始，逐层比较键值，定位到叶子节点。B+Tree 一般 3-4 层即可存储千万级数据（因为每个非叶子节点可存储数百个指针）

② 在叶子节点层面，通过二分查找定位到第一条匹配记录

③ 聚簇索引（Clustered Index）：叶子节点直接存储完整数据行，查找一次即可

④ 二级索引（Secondary Index）：叶子节点存储主键值，需要回表（Bookmark Lookup）到聚簇索引再查一次完整行数据

索引下推 MySQL 5.6+ 的 ICP（Index Condition Pushdown）允许将部分 WHERE 条件下推到存储引擎层过滤，减少回表次数。

当 Buffer Pool 未命中，需要读取磁盘数据文件（.ibd）：

① 磁盘读取：操作系统将数据页从磁盘读入 InnoDB Buffer Pool。InnoDB 的页大小默认 16KB

② 后台线程：InnoDB 有多个后台线程负责异步 I/O（innodb_read_io_threads）、脏页刷新（Page Cleaner Thread）、redo log 刷盘（Log Thread）等

写操作额外保障（本次查询为 SELECT，但写操作涉及）：

• Redo Log：WAL（Write-Ahead Logging），先写日志再写磁盘，保证持久性（Durability）

• Undo Log：记录数据修改前的值，保证原子性（Atomicity）和 MVCC

• Change Buffer：缓存二级索引的修改，减少随机 I/O

• Doublewrite Buffer：防止页撕裂（partial write），每次写数据页先写到双写缓冲区

执行器将处理好的结果集，通过客户端连接逐行（或按包）发送回去：

① 增量返回：MySQL 不是等所有数据都查完了才返回，而是查到一部分就发送一部分。这对客户端体验和内存占用都很友好

② 结果集格式：按 MySQL 协议封装为文本或二进制结果集包（取决于是否使用 prepared statement）

③ 状态更新：发送完毕后附带一个 OK 包，包含 affected_rows、warnings 等状态信息

④ 连接保持：连接不会自动关闭，进入空闲状态，等待下一条 SQL。直到客户端断开或超时

💡 长连接 vs 短连接 短连接每次都要重新走步骤 1-3（握手+认证），开销大。生产环境建议使用数据库连接池复用连接。