MySQL SQL 执行全过程原理

很多人学 MySQL 时只知道：

text

SQL 先解析，再优化，再执行。

这句话太粗。真正要能学懂、面试能讲清楚、线上能排查，需要把一条 SQL 从客户端发出到 InnoDB 读页返回结果的每一步都拆开。

这一页解决这些问题：

客户端连接到 MySQL 后发生了什么。
Server 层解析、预处理、优化、执行分别做什么。
优化器为什么可能不用你建的索引。
执行器怎么调用 InnoDB。
InnoDB 怎么从 Buffer Pool 或磁盘读页。
为什么 limit 20 不代表只扫描 20 行。
一条慢 SQL 到底可能慢在哪个环节。

总流程

mermaid

flowchart TD
    A["客户端发送 SQL"] --> B["连接层"]
    B --> C["权限和连接状态检查"]
    C --> D["解析器"]
    D --> E["预处理器"]
    E --> F["优化器"]
    F --> G["执行器"]
    G --> H["存储引擎接口"]
    H --> I["InnoDB 索引和数据页"]
    I --> J["返回行给执行器"]
    J --> K["返回结果给客户端"]

一句话理解：

Server 层决定 SQL 怎么执行，InnoDB 负责真正按页和索引读取数据。

第一步：连接层做什么

客户端连接 MySQL 通常基于 TCP。

mermaid

flowchart TD
    A["客户端发起连接"] --> B["MySQL 接收连接"]
    B --> C["认证用户名和密码"]
    C --> D["检查账号权限"]
    D --> E["分配连接线程或线程资源"]
    E --> F["连接进入可执行 SQL 状态"]

连接层负责：

事情	说明
建立连接	客户端和 MySQL Server 建立通信
认证	校验用户名、密码、来源主机
权限	判断账号能访问哪些库、表、列
连接状态	保存当前数据库、事务状态、隔离级别等

如果连接层出问题，可能表现为：

连接数满。
应用连接池耗尽。
权限不足。
认证失败。
网络超时。

这类问题不是 SQL 索引能解决的。

第二步：解析器做什么

解析器把 SQL 文本变成 MySQL 能理解的语法结构。

例如：

sql

select id, username
from users
where status = 1
order by created_at desc
limit 20;

解析器会识别：

这是 select。
查询字段是 id、username。
来源表是 users。
过滤条件是 status = 1。
排序是 created_at desc。
限制返回 20 行。

如果语法错，会在这一层报错：

sql

select from users;

解析器只判断语法，不判断这条 SQL 是否高效。

第三步：预处理器做什么

预处理器会检查语义：

检查	示例
表是否存在	`users` 表有没有
列是否存在	`username` 字段有没有
字段是否歧义	多表 Join 中同名字段是否明确
权限是否足够	当前用户能不能查这些字段

例如：

sql

select not_exist_column
from users;

语法可能没问题，但预处理会发现字段不存在。

第四步：优化器做什么

优化器不是简单地“有索引就用索引”。它会生成多个候选计划，并估算成本。

mermaid

flowchart TD
    A["SQL 语义明确"] --> B["找可用索引"]
    B --> C["估算每种访问路径成本"]
    C --> D["估算扫描行数和回表成本"]
    D --> E["考虑排序和临时表成本"]
    E --> F["选择成本最低的执行计划"]

优化器会考虑：

全表扫描。
走哪个索引。
是否回表。
Join 顺序。
是否需要 filesort。
是否使用临时表。
扫描行数预计是多少。

为什么有索引也不用

假设表：

sql

create table orders (
  id bigint primary key auto_increment,
  user_id bigint not null,
  status tinyint not null,
  amount decimal(10, 2) not null,
  created_at datetime not null,
  key idx_status (status)
) engine = InnoDB;

查询：

sql

select *
from orders
where status = 1;

如果 status = 1 占全表 90%，优化器可能不用 idx_status。

为什么？

mermaid

flowchart TD
    A["走 status 索引"] --> B["扫描大量二级索引记录"]
    B --> C["每条记录回表取整行"]
    C --> D["大量随机访问聚簇索引"]
    E["全表扫描"] --> F["顺序扫描聚簇索引页"]
    D --> G{"哪个成本低"}
    F --> G

如果返回大部分数据，走二级索引再大量回表，可能比全表扫描更慢。

所以“有索引不用”不是 MySQL 傻，而是它估算走索引不划算。

第五步：执行器做什么

优化器选好计划后，执行器按计划执行。

执行器不直接操作磁盘，它通过存储引擎接口调用 InnoDB。

mermaid

flowchart TD
    A["执行器拿到执行计划"] --> B["调用 InnoDB 接口"]
    B --> C["读取第一条符合条件的记录"]
    C --> D["Server 层判断剩余条件"]
    D --> E{"是否满足"}
    E -- "是" --> F["放入结果集"]
    E -- "否" --> G["丢弃"]
    F --> H{"是否满足 limit"}
    G --> I["读取下一条"]
    H -- "否" --> I
    H -- "是" --> J["返回客户端"]

注意：有些条件可以下推给 InnoDB，有些需要 Server 层继续判断。

第六步：InnoDB 怎么读数据

InnoDB 以页为单位读写，默认页大小通常是 16KB。

主键查询：

sql

select *
from users
where id = 1001;

过程：

mermaid

flowchart TD
    A["执行器请求 id=1001"] --> B["InnoDB 从聚簇索引根页开始"]
    B --> C["定位内部页"]
    C --> D["定位叶子页"]
    D --> E{"叶子页在 Buffer Pool 吗"}
    E -- "是" --> F["直接读内存页"]
    E -- "否" --> G["从磁盘读页到 Buffer Pool"]
    G --> F
    F --> H["从页中找到完整行"]

关键点：

InnoDB 不是一行一行从磁盘读。
它先读页。
页在 Buffer Pool 中就读内存。
页不在 Buffer Pool 中才读磁盘。

二级索引查询为什么可能回表

查询：

sql

select id, username, age
from users
where username = 'tom';

如果有索引：

sql

create index idx_username on users(username);

二级索引叶子节点保存的是：

text

username + 主键 id

如果查询字段 age 不在二级索引里，就要回表。

mermaid

flowchart TD
    A["查 idx_username"] --> B["找到 username=tom"]
    B --> C["拿到主键 id"]
    C --> D["回到聚簇索引查 id"]
    D --> E["读取完整行"]
    E --> F["返回 id / username / age"]

回表少量没问题，大量回表就可能慢。

`limit 20` 为什么不代表只扫描 20 行

查询：

sql

select id, order_no
from orders
where status = 1
order by created_at desc
limit 20;

如果没有合适索引，MySQL 可能要：

找出大量 status = 1 的行。
按 created_at 排序。
取前 20 行。

mermaid

flowchart TD
    A["where status=1"] --> B["扫描大量候选行"]
    B --> C["按 created_at 排序"]
    C --> D["取前 20 行"]

这就是为什么 limit 写得小，也可能很慢。

如果有联合索引：

sql

create index idx_status_created
on orders(status, created_at);

MySQL 可以更接近按索引顺序取数据。

filesort 是什么

Using filesort 不一定表示一定落磁盘，它表示 MySQL 需要额外排序，而不是直接利用索引顺序。

排序可能发生在内存，也可能因为数据太大使用磁盘临时文件。

mermaid

flowchart TD
    A["候选行"] --> B{"索引顺序能满足 order by 吗"}
    B -- "能" --> C["按索引顺序返回"]
    B -- "不能" --> D["额外排序 filesort"]
    D --> E{"内存够吗"}
    E -- "够" --> F["内存排序"]
    E -- "不够" --> G["磁盘临时文件辅助排序"]

一条查询 SQL 的完整例子

表：

sql

create table orders (
  id bigint primary key auto_increment,
  order_no varchar(64) not null,
  user_id bigint not null,
  status tinyint not null,
  amount decimal(10, 2) not null,
  created_at datetime not null,
  unique key uk_order_no(order_no),
  key idx_user_created(user_id, created_at)
) engine = InnoDB default charset = utf8mb4;

SQL：

sql

select id, order_no, amount, created_at
from orders
where user_id = 1001
order by created_at desc
limit 20;

完整过程：

mermaid

flowchart TD
    A["客户端发送 SQL"] --> B["连接层检查连接和权限"]
    B --> C["解析 SQL 结构"]
    C --> D["预处理检查 orders 和字段"]
    D --> E["优化器选择 idx_user_created"]
    E --> F["执行器调用 InnoDB"]
    F --> G["InnoDB 定位 user_id=1001 的索引范围"]
    G --> H["按 created_at 顺序扫描叶子节点"]
    H --> I["需要 amount，可能回表读取完整行"]
    I --> J["收集 20 行"]
    J --> K["返回客户端"]

为什么索引 (user_id, created_at) 合适：

user_id 是等值条件，可以先定位用户范围。
created_at 用于排序，可以利用索引顺序。
limit 20 可以尽快停止扫描。

慢 SQL 可能慢在哪

环节	问题	现象
连接层	连接池满、连接数满	应用拿不到连接
解析预处理	SQL 复杂、权限错误	报错或解析耗时
优化器	统计信息不准、选错索引	执行计划不合理
执行器	filesort、temporary	`Extra` 出现对应信息
InnoDB	扫描页多、回表多	`rows` 高、IO 高
锁	等待其他事务	单独执行快，并发慢
网络和应用	返回数据太多	DB 执行不慢但接口慢

排查顺序

mermaid

flowchart TD
    A["发现 SQL 慢"] --> B["拿到原 SQL 和参数"]
    B --> C["EXPLAIN 看计划"]
    C --> D["看 type / key / rows / Extra"]
    D --> E{"扫描是否过多"}
    E -- "是" --> F["检查索引和 SQL 写法"]
    E -- "否" --> G{"是否排序或临时表"}
    G -- "是" --> H["优化 order by / group by / 索引"]
    G -- "否" --> I{"是否锁等待"}
    I -- "是" --> J["查事务和锁"]
    I -- "否" --> K["查网络 / 应用 / 返回行数"]

商业场景：订单列表为什么忽然变慢

背景：

sql

select id, order_no, amount, created_at
from orders
where user_id = 1001
order by created_at desc
limit 20;

如果没有 (user_id, created_at)，只有 user_id 索引，可能发生：

用 user_id 找到该用户所有订单。
回表读取大量订单。
再按 created_at 排序。
取前 20。

用户订单少时不慢，用户订单多后突然变慢。

优化：

sql

create index idx_user_created
on orders(user_id, created_at);

优化后：

直接定位用户订单范围。
按时间顺序扫描。
扫够 20 条就停止。

面试标准回答

text

一条 MySQL 查询会先经过连接层做连接、认证和权限检查，然后 Server 层解析 SQL、预处理检查表和字段，再由优化器根据统计信息和成本选择执行计划，执行器按计划调用存储引擎接口。InnoDB 会根据索引从 B+Tree 根页定位到叶子页，优先从 Buffer Pool 读页，页不在内存才读磁盘。二级索引叶子保存索引列和主键值，如果查询字段不在索引中，需要回到聚簇索引查完整行，也就是回表。limit 不代表只扫描这么多行，如果没有合适索引，MySQL 可能先扫描、过滤、排序大量数据，再取少量返回。排查慢 SQL 要看 EXPLAIN 的 type、key、rows、Extra，并结合锁等待、返回行数和应用侧耗时分析。

JDK 8/11/17/21 LTS演进

线程池

MySQL SQL 执行全过程原理 ​

总流程 ​

第一步：连接层做什么 ​

第二步：解析器做什么 ​

第三步：预处理器做什么 ​

第四步：优化器做什么 ​

为什么有索引也不用 ​

第五步：执行器做什么 ​

第六步：InnoDB 怎么读数据 ​

二级索引查询为什么可能回表 ​

limit 20 为什么不代表只扫描 20 行 ​

filesort 是什么 ​

一条查询 SQL 的完整例子 ​

慢 SQL 可能慢在哪 ​

排查顺序 ​

商业场景：订单列表为什么忽然变慢 ​

面试标准回答 ​

MySQL SQL 执行全过程原理

总流程

第一步：连接层做什么

第二步：解析器做什么

第三步：预处理器做什么

第四步：优化器做什么

为什么有索引也不用

第五步：执行器做什么

第六步：InnoDB 怎么读数据

二级索引查询为什么可能回表

`limit 20` 为什么不代表只扫描 20 行

filesort 是什么

一条查询 SQL 的完整例子

慢 SQL 可能慢在哪

排查顺序

商业场景：订单列表为什么忽然变慢

面试标准回答