计算机基础

Appearance

执行一条 select 语句，期间发生了什么？ ​

查询语句执行流程

可以看到，MySQL 的架构共分为两层：Server 层和存储引擎层，

  • Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
  • 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始，InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

好了，现在我们对 Server 层和存储引擎层有了一个简单认识，接下来，就详细说一条 SQL 查询语句的执行流程，依次看看每一个功能模块的作用。

第一步：连接器 ​

  • TCP三次握手（MySQL基于TCP进行传输）
  • 握手成功后，验证用户名、密码
  • 验证通过后，获取并保存本次权限（连接过程中不变，新建立的连接使用最新的权限设置）

wait_timeout 参数控制最大空闲连接时间，超过后连接器会自动将它断开，默认值是 8 小时。这个时候请求会报错：“ERROR 2013 (HY000): Lost connection to MySQL server during query”，不操作不会感知

max_connections控制最大连接数，超过后继续连接会报错“Too many connections”

MySQL 的连接也有短连接和长连接的概念，一般是推荐使用长连接减少建立连接和断开连接的过程。

但长连接会因为在查询过程中会创建临时表，导致内存占用过高（断开会清理）。可以选择

  • 定期断开连接
  • 客户端主动重置连接。MySQL 5.7 的 mysql_reset_connection() 函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

第二步：查询缓存 ​

对查询语句进行 Hash 计算得到一个 Hash 值。SQL 完全一致 Hash 值才会一样。得到 Hash 值之后，通过该 Hash 值到查询缓存中匹配该查询的结果。（基本不用）

。MySQL 8.0 已删除该模块；

第三步：解析 SQL ​

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

第四步：执行 SQL ​

预处理器 ​

我们先来说说预处理阶段做了什么事情。

  • 检查 SQL 查询语句中的表或者字段是否存在；
  • 将 select * 中的 * 符号，扩展为表上的所有列；

优化器 ​

  • 1、确定使用哪个索引

执行器 ​

根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端

执行器查询的过程是一个 while 循环。

如何获取执行计划？ ​

执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化会后，具体的执行方式。

MySQL 为我们提供了 EXPLAIN 命令，来获取执行计划的相关信息。

需要注意的是，EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句，使用起来非常简单，语法如下：

sql
EXPLAIN + SELECT 查询语句；

我们简单来看下一条查询语句的执行计划：

sql
mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | NULL | 331143 |   100.00 | Using where |
|  2 | SUBQUERY    | dept_emp | NULL       | index | PRIMARY,dept_no | PRIMARY | 16      | NULL | 331143 |   100.00 | Using index |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

可以看到，执行计划结果中共有 12 列，各列代表的含义总结如下表：

列名含义
idSELECT 查询的序列标识符
select_typeSELECT 关键字对应的查询类型
table用到的表名
partitions匹配的分区，对于未分区的表，值为 NULL
type表的访问方法
possible_keys可能用到的索引
key实际用到的索引
key_len所选索引的长度
ref当使用索引等值查询时，与索引作比较的列或常量
rows预计要读取的行数
filtered按表条件过滤后，留存的记录数的百分比
Extra附加信息

如何分析 EXPLAIN 结果？ ​

为了分析 EXPLAIN 语句的执行结果，我们需要搞懂执行计划中的重要字段。

id ​

标识整个查询中 SELELCT 语句的顺序。

id 如果相同，从上往下依次执行。id 不同，id 值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为 NULL。

select_type ​

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

  • SIMPLE：简单查询，不包含 UNION 或者子查询。
  • PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
  • SUBQUERY：子查询中的第一个 SELECT。
  • UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
  • DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
  • UNION RESULT：UNION 查询的结果。

table ​

查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

  • <unionM,N> : 本行引用了 id 为 M 和 N 的行的 UNION 结果；
  • <derivedN> : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
  • <subqueryN> : 本行引用了 id 为 N 的表所产生的的物化子查询结果。

type（重要） ​

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

  • system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
  • const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
  • eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
  • ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
  • index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
  • range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
  • index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
  • ALL：全表扫描。

possible_keys ​

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

key（重要） ​

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

key_len ​

key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

rows ​

rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra（重要） ​

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

  • Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
  • Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
  • Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
  • Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
  • Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
  • Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

正在精进

Copyright © 2025 菜鸟