由一条select语句的执行流程，了解MySQL的内部结构！

MySQL架构

下图是MySQL的整体架构，主要分为两层，Server层和存储引擎。

MySQL架构图

Server层：负责建立连接、分析和执行 SQL

包含核心功能模块：连接器、查询缓存（8.0版本移除）、解析器、预处理器、优化器、执行器、内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（存储过程、触发器、视图）。

存储引擎：负责数据的存储和提取

支持多种存储引擎，不同存储引擎共用同一个Server层，默认存储引擎为InnoDB。

连接器

使用MySQL之前需要先进行连接（登录）MySQL服务，然后才能执行SQL语句，连接语句如下：

1
2
3
4
# -h 指定 MySQL 服务得 IP 地址，如果是连接本地的 MySQL服务，可以不用这个参数；
# -u 指定用户名，管理员角色名为 root；
# -p 指定密码，如果命令行中不填写密码（为了密码安全，建议不要在命令行写密码），就需要在交互对话里面输入密码
mysql -h$ip -u$user -p

连接过程需要经过TCP三次握手，连接之前确保启动MySQL服务，使用正确的用户名和密码，连接成功如下

image-20260104102048666

连接成功后连接器就会获取该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读到的权限进行权限逻辑的判断。所以，如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

查看当前MySQL服务被多少个客户端连接：show processlist

image-20260104103233284

空闲连接

MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。

使用的是 kill connection + id 命令手动断开空闲连接。

MySQL 的连接数限制

MySQL 服务支持的最大连接数由 max_connections 参数控制，默认是 151 。

短连接和长连接

1
2
3
4
5
6
7
8
9
10
11
12
// 短连接
连接 mysql 服务（TCP 三次握手）
执行sql
断开 mysql 服务（TCP 四次挥手）

// 长连接
连接 mysql 服务（TCP 三次握手）
执行sql
执行sql
执行sql
....
断开 mysql 服务（TCP 四次挥手）

长连接可以减少建立连接和断开连接的过程，所以一般是推荐使用长连接

长连接后可能会占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。

总结连接器流程：

1、与客户端进行 TCP 三次握手建立连接；

2、校验客户端的用户名和密码，如果用户名或密码不对，则会报错；

3、如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；

查询缓存

连接器得工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。

在8.0之前的版本中，如果SQL是select语句，MySQL会先进行查询缓存，如果命中则直接返回，未命中则继续执行。

对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。

解析器

在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析。

词法分析：

把字符串切成 token，MySQL 会根据你输入的字符串识别出关键字

语法分析：

根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，如果没问题就会构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

image-20260104155407094

执行SQL流程

预处理器

检查 SQL 查询语句中的表或者字段是否存在；

将 select * 中的 * 符号，扩展为表上的所有列；

预处理器负责把解析器产出的语法结构“落到真实的库表对象上”，让一条语句从“语法正确”变成“语义可执行”。它会解析并确认语句里引用的库、表、列等对象是否存在且可访问（比如表不存在会在这一阶段直接报错），同时会把 select * 这类模糊表达展开成明确的列清单，必要时也会完成与权限、类型相关的基础校验。

它的作用是尽早发现“对象/语义层面”的问题并把查询规格化，为后续生成执行计划提供准确输入。

优化器

经过预处理阶段后，还需要为 SQL 查询语句先制定一个执行计划。

优化器负责为这条 SQL 选择一条成本更低的执行计划，核心就是在多种可行访问路径之间做取舍，例如决定走主键索引、二级索引还是全表扫描，以及能否利用覆盖索引来避免回表、能否使用索引下推来减少不必要的回表与记录传递等。它会基于统计信息与代价模型评估不同方案的开销，最终确定执行过程中“怎么访问数据、以什么顺序和方式取数”的策略；

它的作用是把同一个逻辑查询变成尽可能高效的物理执行方案，EXPLAIN 里看到的 key/type/Extra 等信息本质上就是优化器决策的外化结果。

执行器

经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」完成的。在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端。

负责按优化器给定的执行计划真正把查询跑起来，并在执行过程中以记录为单位与存储引擎交互：该走索引就调用引擎的索引读取接口定位第一条或下一条记录，该全表扫就让引擎顺序返回记录，然后在 Server 层对返回的记录做条件判断与表达式计算，满足条件的就逐步发送给客户端，直到读完或满足停止条件为止。在索引下推场景下，执行器会把部分原本由 Server 层做的过滤条件交给引擎在索引层先筛掉，从而减少回表次数与数据搬运。它的作用是把“计划”转化为具体的读、判、发动作，协调 Server 层与存储引擎完成整条语句的实际执行。

三种方式：

主键索引查询

1
select * from product where id = 1;

以上面的SQL语句为例，这条查询语句的查询条件用到了主键索引，而且是等值查询，同时主键 id 是唯一，不会有 id 相同的记录，所以优化器决定选用访问类型为 const 进行查询，也就是使用主键索引查询一条记录，那么执行器与存储引擎的执行流程是这样的：

1、执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为 InnoDB 引擎索引查询的接口，把条件 id = 1 交给存储引擎，让存储引擎定位符合条件的第一条记录。

2、存储引擎通过主键索引的 B+ 树结构定位到 id = 1的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；

3、执行器从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，如果不符合则跳过该记录。

4、执行器查询的过程是一个 while 循环，所以还会再查一次，但是这次因为不是第一次查询了，所以会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为一个永远返回 - 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了。

全表扫描

1
select * from product where name = 'iphone';

以上面的SQL语句为例，这条查询语句的查询条件没有用到索引，所以优化器决定选用访问类型为 ALL 进行查询，也就是全表扫描的方式查询，那么这时执行器与存储引擎的执行流程是这样的：

1、执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 all，这个函数指针被指向为 InnoDB 引擎全扫描的接口，让存储引擎读取表中的第一条记录；

执行器会判断读到的这条记录的 name 是不是 iphone，如果不是则跳过；如果是则将记录发给客户的（是的没错，Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，是因为客户端是等查询语句查询完成后，才会显示出所有的记录）。

3、执行器查询的过程是一个 while 循环，所以还会再查一次，会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 all，read_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；

4、一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器（Server层） 返回了读取完毕的信息；

5、执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。

索引下推

mysql 5.6 推出的查询优化策略。

索引下推能够减少 二级索引 在查询时的回表操作，提高查询效率，因为它将 server 层部分负责的事交给了存储引擎层去处理。

1
2
3
4
5
有一个字段如下的表，且对 age 和 reward 字段建立了联合索引。
 id    name     age      reward
 
 有如下的查询语句
 select * from t_user where age > 20 and reward = 100000;

联合索引当遇到范围查询 (>、<) 就会停止匹配，也就是 age 字段能用到联合索引，但是 reward 字段则无法利用到索引。

不使用索引下推时（mysql 5.6版本之前），执行器与存储引擎的执行流程如下：

1、Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；

2、存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；

3、Server 层在判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；

4、接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给 Server 层；

5、如此往复，直到存储引擎把表中的所有记录读完。

可以看到，没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给 Server，接着 Server 再判断该记录的 reward 是否等于 100000。

而使用索引下推后，判断记录的 reward 是否等于 100000 的工作交给了存储引擎层，过程如下 ：

1、Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；

存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。

3、Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。

4、如此往复，直到存储引擎把表中的所有记录读完。

可以看到，使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。

总结

执行一条 SQL 查询语句，期间发生了什么？

  • 连接器：建立连接，管理连接、校验用户身份；
  • 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
  • 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
  • 执行 SQL：执行 SQL 共有三个阶段：
    • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
    • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
    • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；