Sea-Whales 树深时见鹿,海蓝时见鲸~
MySQL数据查询优化&SQL执行顺序&执行计划&SQ 执行顺序优先级
2025-09-05
MySQL
00
请注意，本文编写于 180 天前，最后修改于 180 天前，其中某些信息可能已经过时。

目录

缓存
 分析器
 优化器
执行器
Sql 执行顺序
explain

原文地址 blog.csdn.net

sql 语句的执行顺序

Mysql 的执行流程图如下 图片来自网络

Mysql 的执行流程图

目录

sql 语句的执行顺序

缓存

分析器

优化器

执行器

Sql 执行顺序

执行计划

explain

连接器

连接器就是起到连接的作用，主要职责有

1、验证请求用户的账户和密码是否正确。

2、用于客户端的通信。Mysql 的 TCP 协议是一个半双工通信模式因此在某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据, 而不能同时进行。

  • 单工： 数据传输只允许在一个方向上的传输，单向传输，只能一方来发送数据，另一方来接收数据并发送。例如：遥控器。
  • 半双工：数据传输允许两个方向上的传输，但是同一时间内，只可以有一方发送或接受消息。例如：打电话
  • 全双工：同时可进行双向传输。例如：websocket、Http2.0。

3、如果账号密码验证通过，会在 mysql 自带的权限表中验证当前用户权限。mysql 库中有 4 个控制权限的表，分别为 user 表，db 表，tables_priv 表，columns_priv 表。

  1. ）user 表存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例。

2.png

  1. ）db 表存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库。% 同样表示所有的主机可连。

3.png

  1. ）Tables_priv 表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表。

  2. ）Columns_priv 表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段。

验证过程如下：

  • 先从 user 表中的 Host,User,Password 这 3 个字段中判断连接的 ip、用户名、密码是否存在，存在则通过验证。
  • 通过身份认证后，进行权限分配，按照 user、db、table、 columns 依次判断，如果 user 表中全局变量都是 Y 则不再进行下面的判断，否则一步一步判断权限。
  • 如果在任何一个过程中权限验证不通过, 都会报错。

权限设置请参考 Mysql 权限设置_熟透的蜗牛的博客 - CSDN 博客

缓存

mysql 的缓存主要的作用是为了提升查询的效率，缓存以 key 和 value 的哈希表形式存储，key 是具体的 sql 语句，value 是结果的集合。如果无法命中缓存, 就继续走到分析器, 如果命中缓存就直接返回给客户端 。不过需要注意的是在 mysql 的 8.0 版本以后，缓存被官方删除掉了。之所以删除掉, 是因为查询缓存的失效非常频繁, 如果在一个写多读少的环境中, 缓存会频繁的新增和失效。

需要注意 ：缓存和哈希自适性索引的区别，自适性哈希是通过哈希表实现的，它是数据库自身创建的不能人为的创建和删除。通过一下 sql 可以查看。

SHOW ENGINE INNODB STATUS ;
 
SHOW ENGINE INNODB STATUS \G; #cmd窗口使用这个自动分行

4.png

 分析器

分析器的主要作用是将客户端发过来的 sql 语句进行分析，这将包括预处理与解析过程，在这个阶段会解析 sql 语句的语义，并进行关键词（select、update、delete、where、order by、group by 等等）和非关键词进行提取、解析，并组成一个解析树。另外在此过程还会对 sql 语法进行分析，除此之外还会校验表是否存在，表中的字段值是否存在。下面是一个解析树

5.png

 优化器

能够进入到优化器阶段表示 sql 是符合 mysql 的标准语义规则的并且可以执行的，此阶段主要是进行 sql 语句的优化，会根据执行计划进行最优的选择, 匹配合适的索引, 选择最佳的执行方案。如 MRR（Multi-Range Read 多范围读取）优化，ICP(Index Condition Pushdown 索引下推) 优化，是否选择使用索引，选择使用主键索引还是其他索引等。

执行器

在执行器的阶段, 此时会调用存储引擎的 API,API 会调用存储引擎。下面罗列几个存储引擎。

存储引擎存储限制事务索引锁数据压缩外键支持
Myisam256T-B+tree、fulltext(全文索引)表锁支持-
Innodb64T支持B+tree、自适性 hash、fulltext(全文索引 mysql5.6 开始)表锁、行锁-支持
Memory有-B+tree、hash（默认）表锁--
Archive无-行锁支持-
Maria支持B+tree、fulltext(全文索引)行锁支持-

**存储引擎是基于表的，而不是数据库。**使用下面 sql 可以查看 mysql 支持的存储引擎

SHOW ENGINES;

6.png

Sql 执行顺序

实际上 sql 语句并不是按照我们写的 sql 的顺序从左到右依次执行的，它是按照如下顺序执行的。

7.png

  1. from 第一步就是选择出 from 关键词后面跟的表, 这也是 sql 执行的第一步: 表示要从数据库中执行哪张表。
  2. join on join 是表示要关联的表，on 是连接的条件。通过 from 和 join on 选择出需要执行的数据库表 t1 和 t2 产生笛卡尔积, 生成 t1 和 t2 合并的临时中间表 Temp1。on: 确定表的绑定关系, 通过 on 产生临时中间表 Temp2。
  3. where where 表示筛选, 根据 where 后面的条件进行过滤, 按照指定的字段的值 (如果有 and 连接符会进行联合筛选) 从临时中间表 Temp2 中筛选需要的数据, 注意如果在此阶段找不到数据，会直接返回客户端, 不会往下进行. 这个过程会生成一个临时中间表 Temp3。注意在 where 中不可以使用聚合函数，聚合函数主要是 (min**、max、count、sum 等函数)。**
  4. group by group by 是进行分组，对 where 条件过滤后的临时表 Temp3 按照固定的字段进行分组, 产生临时中间表 Temp4，这个过程只是数据的顺序发生改变, 而数据总量不会变化, 表中的数据以组的形式存在。
  5. having 对临时中间表 Temp4 进行聚合，然后产生中间表 Temp5，在此阶段可以使用 select 中的别名。
  6. select 对分组聚合完的表挑选出需要查询的数据, 如果为 * 会解析为所有数据, 此时会产生中间表 Temp6。
  7. distinct distinct 对所有的数据进行去重, 此时如果有 min、max 函数会执行字段函数计算，然后产生临时表 Temp7。
  8. order by 会根据 Temp7 进行顺序排列或者逆序排列，然后插入临时中间表 Temp8，这个过程比较耗费资源**。**
  9. limit limit 对中间表 Temp8 进行分页, 产生临时中间表 Temp9, 返回给客户端。

实际上这个过程也并不是绝对这样的，中间 mysql 会有部分的优化以达到最佳的优化效果，比如在 select 筛选出找到的数据集。

执行计划

下面所有的 sql 语句是在 Mysql 8.0.27 版本上执行的。两张表，表结构为

CREATE TABLE `tb_salary`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` int NULL DEFAULT NULL COMMENT '用户id',
  `salary` decimal(10, 2) NOT NULL COMMENT '工资',
  `salary_time` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '薪资月份',
  `create_time` datetime NULL DEFAULT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '薪资表' ROW_FORMAT = Dynamic;
 
 
CREATE TABLE `tb_user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名字',
  `age` int NULL DEFAULT NULL,
  `position` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '职务',
  `address` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',
  `create_time` datetime NULL DEFAULT NULL,
  `update_time` datetime NULL DEFAULT NULL,
  `delete_flag` tinyint NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 100 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

explain

执行如下 sql

EXPLAIN SELECT t2.*,( SELECT 1 FROM tb_user WHERE id = 100000 ) 
FROM
	tb_user t1
	LEFT JOIN tb_salary t2 ON t1.id = t2.user_id 
WHERE
	t2.id IS NOT NULL;

 执行计划结果如下

8.png

字段说明

**id:**id 列的编号是 select 的序列号，有几个 select 就有几个 id，并且 id 的顺序是按 select 出现的顺序增长的。id 列越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行。

select: 查询类型

(1) SIMPLE(简单 SELECT，不使用 UNION 或子查询等)。

(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的 select 被标记为 PRIMARY)。

(3) UNION(UNION 中的第二个或后面的 SELECT 语句)。

(4) DEPENDENT UNION(UNION 中的第二个或后面的 SELECT 语句，取决于外面的查询)。

(5) UNION RESULT(UNION 的结果，union 语句中第二个 select 开始后面所有 select)。

(6) SUBQUERY(子查询中的第一个 SELECT，结果不依赖于外部查询)。

(7) DEPENDENT SUBQUERY(子查询中的第一个 SELECT，依赖于外部查询)。

(8) DERIVED(派生表的 SELECT, FROM 子句的子查询)。

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)。

派生查询例子

#派生表的优化合并，默认是开启的，需要手动关闭。
set session optimizer_switch='derived_merge=off';

EXPLAIN SELECT
	( SELECT id FROM tb_salary WHERE id = 1 ) 
FROM
	( SELECT * FROM tb_user WHERE id = 95885 ) t1;

  9.png

 其中 table 中的 3 指向的是 id 列的值。

**table:**explain 的一行正在访问哪个表。当 from 子句中有子查询时，table 列是  格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为 < union1,2>，1 和 2 表示参与 union 的 select 行 id。

partitions: 查询是基于分区表的话，会显示查询将访问的分区，mysql5.6 版本之后才有。

type: 这一列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据行记录的大概范围。 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL 一般来说，得保证查询达到 range 级别，最好达到 ref。

(1) const, system：mysql 能对查询的某部分进行优化并将其转化成一个常量。用于 primary key 或 唯一索引的所有列与常数比较时，所以表最多有一个匹配行，读取 1 次，速度比较快。system 是 const 的特例，表里只有一条元组匹配时为 system。

EXPLAIN SELECT
	( SELECT id FROM tb_salary WHERE id = 1 ) 
FROM
	( SELECT * FROM tb_user WHERE id = 95885 ) t1;

10.png

(2) eq_ref: 使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用 primary key 或者 unique key 作为关联条件。

EXPLAIN SELECT
	t2.*
FROM
	tb_user t1
	LEFT JOIN tb_salary t2 ON t1.id = t2.user_id 
WHERE
	t2.id IS NOT NULL;

11.png

 (3) ref: 与 eq_ref 类似，只是不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

EXPLAIN SELECT
	t2.*
FROM
	tb_user t1
	LEFT JOIN tb_salary t2 ON t1.id = t2.user_id 
WHERE
	t2.salary=10500.00

12.png

 其中 salary 字段上有普通索引。

(4) range : 范围扫描通常出现在 in()、 between 、>、<、 >= 、<= 等操作中。使用一个索引来检索给定范围的行。

EXPLAIN SELECT * FROM tb_user WHERE id <=95908

13.png

(5) index  : 只遍历索引树就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接 对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般会使用覆盖索引，二级索引一般比较小，所以这 种通常比 ALL 快。

首先对上面的表创建索引

-- 创建索引
CREATE INDEX idx_name_age_position ON tb_user(name,age,position);
-- 前15个字符创建索引
ALTER TABLE tb_user ADD INDEX address (address(15));
-- 创建聚簇索引
CREATE CLUSTERED INDEX 索引名 ON 表名(字段名);
-- 删除索引
DROP INDEX idx_name_age_position ON tb_user;
-- 删除索引
ALTER TABLE tb_user DROP INDEX idx_name_age_position;
-- 删除主键索引
ALTER TABLE tb_user DROP PRIMARY KEY;

EXPLAIN SELECT name,age,position FROM tb_user;

14.png

 （6）All： 即全表扫描，扫描你的聚集索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

EXPLAIN SELECT * FROM tb_user;

15.png

（7）null : mysql 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

EXPLAIN SELECT MAX(id) FROM tb_user;

16.png

possible_keys: 可能用到的索引。

key: 实际用到的索引，实际情况中有可能 possible_keys 有值，而实际执行时候 key 没有值，这种情况下可能是 mysql 优化器觉得全表扫描比使用索引查询效率要高，而没有使用索引。如果强制 mysql 使用索引，则可以使用 force index(索引名称) 来实现。

EXPLAIN SELECT
	* 
FROM
	tb_salary FORCE INDEX ( salary ) 
WHERE
	salary = 15000;

key_len: 这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
key_len 计算规则如下：

字符串，char(n) 和 varchar(n)，5.0.3 以后版本中，n 均代表字符数，而不是字节数，如果是 utf-8, 一个数字或字母占 1 个字节，一 个汉字占 3 个字节。
char(n)：如果存汉字长度就是 3n 字节, 固定长度。
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的 2 字节用来存储字符串长度，因为 varchar 是变长字符串。
数值类型
tinyint：1 字节
smallint：2 字节
int：4 字节
bigint：8 字节
时间类型
date：3 字节
timestamp：4 字节
datetime：8 字节

如果字段允许为 NULL，需要 1 字节记录是否为 NULL。索引最大长度是 768 字节，当字符串过长时，mysql 会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

EXPLAIN SELECT name,age,position FROM tb_user;

17.png

 name 为 varchar(20) 允许空值 (1 个字节)   20*3+2+1=63

age 为 int  允许空值 4+1=5

position 为 varchar(50)  50*3+2+1=153

则 63+5+153=221

ref: 这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名, null。

rows: 预估数据行数，并不一定等于查询的返回结果。

filtered: 符合某条件的记录数百分比。

Extra:

Using where——表示 MySQL 将对存储引擎层提取的结果进行过滤，过滤条件字段无索引， Using where 本身其实和是否使用索引无关。

Using index——表示使用覆盖索引，查询的字段在覆盖索引中就可以获取到。

Using index condition——在 5.6 版本后加入的新特性（Index Condition Pushdown) 后面具体说明。

Using filesort——表示没有使用索引的排序。

参考 ：

https://www.cnblogs.com/wyq178/p/11576065.html

https://blog.csdn.net/admin522043032/article/details/121037081

本文作者:sea-whales

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！