[大数据]Mysql数据查询优化——sql执行顺序&执行计划

目录

sql语句的执行顺序

缓存

?分析器

?优化器

执行器

Sql执行顺序

执行计划

explain

sql语句的执行顺序

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

连接器

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

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

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

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

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

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

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

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

4. ）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窗口使用这个自动分行

?

?分析器

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

?优化器

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

执行器

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

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

SHOW ENGINES;

?

Sql执行顺序

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

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;

?执行计划结果如下

字段说明

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;

?

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

table:explain 的一行正在访问哪个表。当 from 子句中有子查询时，table列是?<derivenN>?格式，表示当前查询依赖 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;

(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;

?(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

?

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

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

EXPLAIN SELECT * FROM tb_user WHERE id <=95908

?

?(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;

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

EXPLAIN SELECT * FROM tb_user;

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

EXPLAIN SELECT MAX(id) FROM tb_user; 

?

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;

?

?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