MySQL 执行计划使用详解

最新推荐文章于 2022-03-10 15:41:51 发布
最新推荐文章于 2022-03-10 15:41:51 发布
阅读量239 收藏
点赞数
文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_63550462/article/details/121063715
版权

执行计划是什么？

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是 如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

前提介绍：文中所有案例 mysql 版本为 5.7.23

执行计划帮助我们完成什么事情？

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

怎么使用执行计划？

  • expain + SQL 语句
  • 执行计划包含信息

image.png

执行计划包含信息解释

id

select 查询的序列号，包含一组数字， 表示查询中执行 select 子句或操作表的顺序

use oemp;

#测试表1
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#测试表2
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#测试表3
CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#id 相同
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id
and t3.other_column = '';

#id 不同
explain select t2.* from t2 where id = (select id from t1 where id = 
(select t3.id from t3 where t3.other_column = ''));

#id 相同和不同同时存在
explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 
where s1.id = t2.id;

包含三种情况： id 相同，id 不同，id 相同和 id 不同同时存在。

id 相同

id 相同，执行结果从上而下

  • 运行结果

image.png

id 不同

id不同如果是自查询，id 的序号会递增，id 值越大，优先级越高，越先被执行

  • 运行结果

image.png

id 相同和 id 不同时存在

id 如果相同，可以认为是一组的，从上往下执行； 在所有组中，id 值越大，优先级越高，越先被执行； 衍生 = DERIVED

  • 执行结果

image.png

derived_merge 是 Mysql5.7 引入的，会试图将 Derived Table (派生表，from 后的自查询) 视图引用，公用表达式（Common table expressions） 与外层查询进行合并。 MySQL 5.7 不在兼容的实现方式，可以通过调整 optimizer_switch 来加以规避

set optimizer_switch='derived_merge=off';

说白了，如果设置为 on 那么就不会出现 derived_merge 行 结果如下：

image.png

select_type

包括范围： simple. primary，subquery, derived, union, union result 查询类型主要是用于区别普通查询，联合查询，子查询等复杂的查询

  • simple，简单的select 语句，查询中不包含自查询或者 union
  • primary, 查询若包含任何复杂的子部分，最外层查询则被标记为primary
  • subquery， 在 select 或 where 列表中包含子查询
  • derived，在 from 列表中包含自查询被标记为 derived （衍生）MySQL 会递归执行这些自查询，把结果放在临时表中。
  • union，若第二个 select 出现在 union 之后，则被标记为 union. 若 union 包含在 from 子句子查询中，外层 select 将别标记为 derived
  • union result, 从 union 表中获取结果的 select

table

  • 这行数据是关于那种表的

type

类型： all , index , range, ref, eq_ref, const, system ,null type 显示的是防卫类型，是较为重要的一个指标，结果从好到坏依次是： system > count > eq_ref > range > index > all

​sytem > const > eq_ref > ref > fulltext > ref_or_null > index_merge >> unique_subquery > index_subquery > range > index > ALL

system

表只有一行记录（等于系统表），这是 const 类型的特列， 平时不会出现，这个也可以忽略不计

count

explain select * from (select * from t1 where id =1) d1;

表示通过索引一次就找到了， const 用于比较 primary key 或者 unique 索引。 因为只匹配一行数据，所以很快如将主键置于where 列表中， MySQL 就能将该查询转换为一个常量。

image.png

eq_ref

explain select * from t1, t2 where t1.id = t2.id

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描. 查询案例：

image.png

ref

# tb_emp ddl
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ;

#员工表添加年龄列
alter table tb_emp add column `age` int(11) default null after `name`;

#添加复合索引
create index idx_emp_name_age on tb_emp(`name`, `age`);

explain select * from tb_emp where `name` = 'z3';

非唯一性索引扫描， 返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独的行，然而，它可能会找到多个符合个条件的行，所以它应该属于查找和扫描的混合体

image.png

range

explain select * from t1 where id between 1 and 3;

explain select * from t1 where id in (1, 2, 3);

只检索给定范围内的行，使用一个索引来选择行。key 列显示使用了哪个索引 一般就是你在 where 语句中出现了 between、<、>、in 等的查询 这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某个点，而结束于另一个点，不用全表扫描 案例结果：

image.png

index

explain select id from t1;

Full Index Scan ， index 于 ALL的却别 ，index 类型只遍历索引树， 这通常比 ALL 快， 因为索引文件通常比数据文件小。（也就是说虽然 all 和 index 都是读全表，但是index 是从索引中读取的， 而 all 是从硬盘中读取的 ） 查询结果：

image.png

all

explain select * from t1;

Full Table Scan 将遍历全表找到匹配的行

image.png

备注：一般来说，得以保证查询至少达到 rang 级别， 最好能达到 ref。 ​

possible_keys

显示可能应用在这张表中的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用. ​

key

实际使用的索引，如果为NULL，则没有使用索引 查询中若使用了覆盖索引，则该索引仅出现在KEY列表中

explain select col1, col2  from t1;

create index idx_col1_col2 on t1(col1, col2);

explain select col1, col2  from t1;

案例一（加索引之前）

image.png

案例二（加索引之后）

image.png

key_len

desc t1; 
explain select * from t1 where col1 = 'ab';
explain select * from t1 where col1 = 'ab' and col2 = 'bc';

表示索引中使用的字节数，可通过该列计算查询中的使用的索引的长度，在不损失精确性的情况下，长度越短越好 key_len 显示的只为索引字段的最大可能长度，** 并非实际使用长度**。即 key_len e是更具表定义计算而得，不是通过表内检索出的。 查询结果：

image.png

总结：条件越多，付出的代价越大，key_len 的长度也就越大，建议在一定条件的情况下，key_len 越短，效率越高。 ​

Rows

根据表统计信息及索引选用情况， 大致估算出找到所需的记录所需读取的行数

image.png

filtered

Extra

包含不适合其他列中显示但十分重要的额外信息 id, select_type, table, type , possible_keys, key, key_len, ref, rows, Extra

1. Using filesort

文件排序

2. Using temporary

explain select col2 from t1 where col1 in ('ab', 'ac', 'as') group by col2 \G;

explain select col2 from t1 where col1 in ('ab', 'ac', 'as') 
group by col1, col2, col3 \G;

使用了临时表保存中间结果， MySQL 在对查询结果排序时使用临时表。 常见于排序 order by 和分组查询 group by 。 例子：

image.png

3. Using index

explain select col2 from  t1 where col1=100;

explain select col1, col2 from  t1;

表示相应的 select 操作使用了覆盖索引 (Covering Index), 避免了访问表的数据行，效率不错～ 如果同时出现 using where , 表示索引被用来执行索引键值的查找； 如果没有同时出现 using where , 表明索引引用来读取数据而非执行查找动作。 例子：

image.png

覆盖索引 （Covering Index）

  • 覆盖索引 （Covering Index）, 一说为索引覆盖
  • 理解方式一：就是 select 的数据列只用从索引中就能取得，不必读取数据行， MySQL 可以利用你索引返回 select 列表的字段， 而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖
  • 理解方式二：索引是高效找到的行的一个方法， 但是一般数据库也能使用索引找到一个列的数据， 因此它不必读取整个行，毕竟索引叶子节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据， 那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
  • 注意： 1. 如果要使用覆盖索引，一定要注意 select 列表汇总只取出需要的列，不可 select * ； 2. 因为如果将所有字段一起做索引将会导致索引文件过大，查询性能下降。

4. Using Where

表明使用了 where 过滤

5. using join buffer

使用了链接缓存

6. impossible where

explain select * from t1 where 1=2;

where 子句的值总是 false , 不能用来获取任何元组

image.png

7. select tbale optimized away

在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUT（*） 操作不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

8. distinct

优化 distinct 操作 在找到第一匹配的元祖后立即停止找相同值的动作。 ​

举个例子

例子描述：

explain select d1.name, (select id from t3) d2 from 
(select id, name from t1 where other_column = '') d1 
union 
(select name, id from t2);

查询结果：

image.png

案例解析：

  • 第一行 （执行顺序4）：id 列为1 ， 表示 union 的第一个 select , select_type 的 primary 表表示该查询为外层查询， table
  • 列被标记为 , 表示查询结果来自一个衍生表，其中 derived3 中的 3 代表查询衍生自第三个 select 查询， 即 id 为 3 的 select [select d1.name ... ]
  • 第二行（执行顺序为2）：id 为 3 ，是整个查询中第三个 select 的一部分， 因查询包含在from 中， 所以为derived 。 【select id, name from where other_column = ''】
  • 第三行（执行顺序为3）：select 列表中的子查询 select_type 为 subquery , 为整个查询中的第二个 select . [select id from t3]
  • 第四行（执行顺序为1）：select_type 为 union ， 说明第四个 select 是 unin 里的第二个 select , 最先执行 【select name ,id from t2】
  • 第五行（执行顺序为5）：代表 union 的临时表中读取行的阶段， table 列的 <union , 1, 4> 表示用第一个 和第四个 select 结果进行union 操作 。 【两个结果 union 操作】
咸猪手呆呆
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MYSQL执行计划详解.pdf
04-21
执行计划的ID1. id相同，执行顺序由上至下 id不同，如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
MySQL执行计划的深入分析
09-09
MySQL执行计划是数据库管理员和开发人员用来理解和优化SQL查询性能的关键工具。它是数据库解析和执行SQL语句的蓝图，提供有关数据库如何执行查询的详细信息。执行计划可以帮助我们理解SQL语句的执行流程，包括数据的...
执行计划的使用(EXPLAIN)
moonfly2004
03-12 301
对于sql执行的小量高低。我们可以通过执行计划的信息基本上可以进行分析查看该SQL语句执行的时间。连接顺序及浪费的数据库资源等信息，从而判断该SQL语句执行的效率如何，下面就简单的介绍一下执行计划的使用 　　2. Explain使用 　　Oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择（搜索）路径以及索引是如何被...
【mysql】分析mysql执行计划
summer
09-20 238
分析Mysql执行计划使用EXPLAIN优化查询EXPLAIN输出格式idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra优化建议 使用EXPLAIN优化查询 EXPLAIN后可接 SELECT， DELETE， INSERT， REPLACE，和 UPDATE语句。如下： explain sele...
【MySQL】SQL执行计划分析
热门推荐
多多说happy的博客
01-09 2万+
MySQL EXPLAIN命令是查询性能优化不可缺少的一部分，该文主要讲解explain命令的使用及相关参数说明。
MySql中如何使用 explain 查询 SQL 的执行计划
xiaoyaGrace的博客
03-02 120
explain命令是查看查询优化器如何决定执行查询的主要方法。 这个功能有局限性，并不总会说出真相，但它的输出是可以获取的最好信息，值得花时间去了解，因为可以学习到查询是如何执行的。 1、什么是MySQL执行计划 要对执行计划有个比较好的理解，需要先对MySQL的基础结构及查询基本原理有简单的了解。 MySQL本身的功能架构分为三个部分，分别是 应用层、逻辑层...
如何做一个可执行计划
aaa333qwe的博客
04-01 2039
你知道最严谨的德国人新年计划的执行率有多高么？实话告诉你，基本上能坚持自己计划三个月的不超过50%。这个数字在我看来其实一点也不惊讶，放在以前的我身上可能一个月都无法坚持。原因当然有很多，懒、拖延症等等。 　　不过更多可能是自己的计划充斥着虚拟的的想法，比如想要赚更多钱，想要自己每天努力学习等等。这些看起来和大部分人的想法都差不多，其实越是这种比较空的目标越是难以实现的，因为它缺乏了衡量是否
mysql执行计划id为空（UNION关键字）详解
09-09
MySQL的执行计划是数据库优化的重要工具，它可以帮助我们理解SQL语句如何被数据库引擎解析和执行。当我们在SQL语句中使用`UNION`关键字时，执行计划可能会出现一些特殊的特性，尤其是`id`字段为空的情况。在本篇中，...
mysql 执行计划详解
04-17
NULL 博文链接：https://xq0804200134.iteye.com/blog/1826023
mysql 索引与执行计划
10-21
目录 1. 索引与执行计划 1 1.1. 索引入门 1 1.1.1. 索引是什么 1 1.1.2. 索引得分类 3 1.1.3. 基础语法 3 1.2. 执行计划 3 1.2.1. 什么是执行计划 3 1.2.2. 执行计划的作用 3 ...1.2.4. 执行计划详解 4
执行计划怎么出来 MySQL获取_查看mysql执行计划
weixin_32235191的博客
02-12 71
select * from message_message where id in(select message_id frommessage_message_tags where messagetag_id=59885) and (category=9 orcategory=1)order by sum(like_count,favorite_count) desc limit 15;这条SQ...
执行计划执行步骤原则
congtang0640的博客
10-31 442
执行计划查看原则： Operation这一列的步骤是个树，ID=0的为树根节点，一个根节点可以有多个子节点，展开是由上到下，从左到右，表示oracle读取SQL的过程，而真正SQL执行的先后顺序是从父节点开始找，有子节点...
mysql 开启计划_开启MySQL计划事件
weixin_31545945的博客
02-01 220
1、首先查看计划事件是否开启:[root@leekwen ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 10164Server version: 5.1.69 Source distributionCopyr...
mysql的sql执行计划详解（非常有用）
JackLiu16的博客
03-11 4341
引言：实际项目开发中，由于我们不知道实际查询的时候数据库里发生了什么事情，数据库软件是怎样扫描表、怎样使用索引的，因此，我们能感知到的就只有sql语句运行的时间，在数据规模不大时，查询是瞬间的，因此，在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增大，如千万、亿的时候，我们运行同样的sql语句时却发现迟迟没有结果，这个时候才知道数据规模已经限制了我们查询...
ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=1 这句话是什么意思呢？
u010882595的专栏
07-31 1万+
ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=1 这句话是什么意思呢？ 新手对于Mysql创建数据表的相关疑问，例如： CREATE TABLE `message` (   `id` tinyint(1) NOT NULL auto_increment,   `user` varchar(25) NOT NULL,   `t
MySQL中ENGINE=InnoDB、AUTO_INCREMENT的意思
lip86的专栏
09-20 4812
CREATE TABLE `hui_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户表id', `username` varchar(50) NOT NULL COMMENT '用户名', `password` varchar(50) NOT NULL COMMENT '用户密码，MD5加密', `email` va...
什么是执行计划？ 怎么用？
Aikun7777777的博客
03-30 2961
什么是执行计划？ 怎么用？ 加粗样式 执行计划是一条查询语句在oracle中的执行过程或者访问路径的描述。 在sql窗口执行完一条select 语句后按F5即可查看刚刚执行的这条查询语句的执行计划。 执行计划的常用列字段解释： 基数（rows）：Oracle估计的当前操作的返回结果集行数 字节（bytes）：执行该步骤后返回的字节数 耗费（cost）、cpu耗费：oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入） 时间（time）：oracle估计的当前操作
Mysql执行计划-看这一篇就够了
GM_115的博客
03-10 7464
Mysql执行计划
mysql 执行计划 详解
最新发布
08-04
在优化阶段，MySQL查询优化器会生成一个执行计划，该计划告诉MySQL执行SQL语句的具体步骤，包括表的连接方式、扫描方式、过滤条件等。[1]你可以通过使用EXPLAIN关键字或MySQL Workbench来查看执行计划。优化查询效率...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值