MySQL执行计划详解

已于 2022-07-28 09:17:48 修改
阅读量134 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库
于 2022-02-09 09:12:21 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_63164811/article/details/122827253
版权

MySQL执行计划详解


在日常开发的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，来达到加快SQL语句的执行效率的目的。可以使用【Explain语法】来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的，也使开发人员知道该如何优化SQL。

官方文档地址：

https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

数据库表结构：

DROP TABLE
IF
	EXISTS dept;
CREATE TABLE dept (
	DEPTNO INT ( 2 ) NOT NULL,
	DNAME VARCHAR ( 14 ) DEFAULT NULL,
	LOC VARCHAR ( 13 ) DEFAULT NULL,
	PRIMARY KEY ( DEPTNO ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;-- Records of dept
INSERT INTO dept
VALUES
	( '10', 'ACCOUNTING', 'NEW YORK' );
INSERT INTO dept
VALUES
	( '20', 'RESEARCH', 'DALLAS' );
INSERT INTO dept
VALUES
	( '30', 'SALES', 'CHICAGO' );
INSERT INTO dept
VALUES
	( '40', 'OPERATIONS', 'BOSTON' );-- Table structure for emp
DROP TABLE
IF
	EXISTS emp;
CREATE TABLE emp (
	EMPNO INT ( 4 ) NOT NULL,
	ENAME VARCHAR ( 10 ) DEFAULT NULL,
	JOB VARCHAR ( 9 ) DEFAULT NULL,
	MGR INT ( 4 ) DEFAULT NULL,
	HIREDATE date DEFAULT NULL,
	SAL INT ( 7 ) DEFAULT NULL,
	COMM INT ( 7 ) DEFAULT NULL,
	DEPTNO INT ( 2 ) DEFAULT NULL,
	PRIMARY KEY ( EMPNO ),
	KEY FK_DEPTNO ( DEPTNO ),
	CONSTRAINT FK_DEPTNO FOREIGN KEY ( DEPTNO ) REFERENCES dept ( DEPTNO ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO emp
VALUES
	( '7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', NULL, '20' );
INSERT INTO emp
VALUES
	( '7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30' );
INSERT INTO emp
VALUES
	( '7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30' );
INSERT INTO emp
VALUES
	( '7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', NULL, '20' );
INSERT INTO emp
VALUES
	( '7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30' );
INSERT INTO emp
VALUES
	( '7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', NULL, '30' );
INSERT INTO emp
VALUES
	( '7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', NULL, '10' );
INSERT INTO emp
VALUES
	( '7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', NULL, '20' );
INSERT INTO emp
VALUES
	( '7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000', NULL, '10' );
INSERT INTO emp
VALUES
	( '7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30' );
INSERT INTO emp
VALUES
	( '7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', NULL, '20' );
INSERT INTO emp
VALUES
	( '7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', NULL, '30' );
INSERT INTO emp
VALUES
	( '7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', NULL, '20' );
INSERT INTO emp
VALUES
	( '7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', NULL, '10' );

执行计划中包含的信息

ColumnMeaning
idSELECT查询的序列号
select_typeSELECT类型
table表名
partitions匹配的分区
type连接类型
possible_keys可能使用的索引
key实际使用的索引
key_len使用索引的长度
ref显示索引的哪一列被使用了，如果可能的话，是一个常数
rows扫描行的数量
filtered返回结果的行占需要读到的行(rows列的值)的百分比
extra执行情况的说明和描述

执行计划中主要信息详解

id

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

EXPLAIN SELECT
	* 
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno;

在这里插入图片描述

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询。

TypeMeaning
SIMPLE简单SELECT（不使用 UNION或子查询）
PRIMARY主查询，即外层的查询
UNIONUNION 中的第二个或者后面的查询语句
DEPENDENT UNION表示union或union all联合而成的单位查询受外部影响
UNION RESULTUNION的结果
SUBQUERY在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERY在SELECT或WHERE列表中包含了子查询,子查询基于外层IN
DERIVED在FROM列表中包含的子查询被标记为DERIVED
UNCACHEABLE SUBQUERY表示使用子查询的结果不能被缓存
UNCACHEABLE UNION表示union的查询结果不能被缓存
--sample:简单的查询，不包含子查询和union
explain select * from emp;

在这里插入图片描述

--union:若第二个select出现在union之后，则被标记为union
--union result:从union表获取结果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

在这里插入图片描述

--primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
--dependent subquery:subquery的子查询要受到外部表查询的影响
--dependent union:跟union类似，
--此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e 
where e.empno  in 
( select empno from emp where deptno = 10 
union 
select empno from emp where sal >2000)

在这里插入图片描述

--subquery:在select或者where列表中包含子查询
explain select * from emp where sal > (select avg(sal) from emp) ;

在这里插入图片描述

--UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
 explain select * from emp 
 where empno = (select empno from emp where deptno=@@sort_buffer_size);

在这里插入图片描述

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集

  1. 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
  2. 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
  3. 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：

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

一般情况下，得保证查询至少达到range级别，最好能达到ref

--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;

在这里插入图片描述

--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
explain  select empno from emp;

在这里插入图片描述

--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
explain select * from emp where empno between 7000 and 7500;

在这里插入图片描述

--ref：使用了非唯一性索引进行数据的查找
 create index idx_3 on emp(deptno);
 explain select * from emp e where e.deptno = '12';

在这里插入图片描述

--const：这个表至多有一个匹配行，
explain select * from emp where empno = 7369;

在这里插入图片描述

possible_keys

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

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

在这里插入图片描述

key

​ 实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

在这里插入图片描述

key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

在这里插入图片描述

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

在这里插入图片描述

rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

explain select * from emp;

在这里插入图片描述

extra

包含额外的信息。

--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;

在这里插入图片描述


--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;

在这里插入图片描述


--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。
--如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，
--表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;

在这里插入图片描述

她又在丛中笑
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MYSQL执行计划详解.pdf
04-21
执行计划的ID1. id相同，执行顺序由上至下 id不同，如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
mysql执行计划
qq_30391051的博客
09-11 761
MySQL 也已经使用的多年，这么些年陆续碰到一些sql 优化的需求，每次都是查看sql 执行计划，关于其中参数常常忘记，现作此篇记录 执行计划的各项参数含义，与追梦路上诸君共勉。先记录这么多，后续在补充把。
Mysql执行计划
你的指尖有改变世界的力量
10-21 306
学习MySQL执行计划的笔记。
MySQL执行计划range
最新发布
weixin_36185970的博客
07-07 20
实现MySQL执行计划range 介绍 在MySQL中，执行计划是指MySQL数据库优化器根据表结构、索引等信息估算出的查询执行的步骤和顺序。其中range是一种常见的执行计划类型，通常用于范围查询。本文将教你如何实现MySQL执行计划range。 流程图 执行计划range实现流程#render_12_301984767...
MySQL执行计划全面解析
Lxn2zh的博客
01-19 499
using index 表示相应的select行使用了覆盖索引(覆盖索引为查询结果为索引列，不必读取数据行)，防止访问数据行，速度提升。索引中使用的字节数，在不损失精度的情况下，长度越短越好(为索引值的最大可能长度，并非索引实际长度，是根据表结构计算得出，而非数据)，对于确认索引的有效性以及多列索引中用到的列的数目很重要。using join buffer 在使用join进行链表查询的时候，如果表的连接条件没有用到索引，需要有一个缓冲区来存储中间结果，需要添加索引进行优化。
MYSQL 范围查询 执行计划有时为range有时为all问题
qq_41713884的博客
05-10 1070
ggg仅仅为key索引，d为primary索引； EXPLAIN SELECT * FROM ggg WHERE d>1 #range d为primary ​ 实际上，只有当条件列的索引为primary和unique时才是range，key和fulltext依旧是all EXPLAIN SELECT * FROM ggg WHERE ggg>1 #all ggg为key ​ 所以说啊，避免范围查询不是没道理的，对了一半。（本次测试MYSQL为5.7.33） 还有啊，网上那些发文章谈执.
mysql 执行计划详解
04-17
MySQL执行计划是数据库管理员和开发人员优化SQL查询性能的关键工具。它揭示了MySQL如何解析查询，确定数据检索的顺序，以及使用哪些索引。在本文中，我们将深入探讨MySQL执行计划，了解其重要性，如何生成，以及如何...
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执行计划PPT
04-03
mysql执行计划详解，PPT格式
MySQL执行计划的深入分析
09-09
MySQL执行计划是数据库管理员和开发人员用来理解和优化SQL查询性能的关键工具。它是数据库解析和执行SQL语句的蓝图，提供有关数据库如何执行查询的详细信息。执行计划可以帮助我们理解SQL语句的执行流程，包括数据的...
MySQL高级 之 explain执行计划详解
热门推荐
wuseyukui的专栏
05-09 8万+
使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。explain执行计划包含的信息其中最重要的字段为：id、type、key、rows、Extra各字段详解idselect查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序 三种情况： 1、id相同：执行顺序由上至下 2、id不同：
多表查询
sjwl0526的博客
09-09 1530
多表查询 概述 基础语法： select ​ 列名列表 from ​ 表名列表 where… 多表查询的分类 内连接查询： 隐式内查询： 使用where消除无用的数据 显式内查询 外链接查询： 子查询：
对Explain理解
微风吹付博客
09-23 268
EXPLAIN简介 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 通过explain我们可以获得以下信息： 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用 每张表有多少行被优化器查询 使用方法：explain + sql语句。 包含的字段如下 执行计划各字段含义 2.1 id id相同，执行顺序由上至下 id不同，如果是子查询，id的序号会递增，id值越大优先级越.
MYSQL 执行计划
speciality的专栏
01-25 1432
explain + SQL语句； 其中比较重要的是type，mysql性能比较重要的标志，性能从低到高依次：all<index<range<index_merge<ref_or_null <ref <system/const ALL:全表扫描，数据表从头到尾找一遍（一般未命中索引，都是会执行全表扫描） 特别的：如果有limit，则找到之后就不再继续往下扫描 index：全索引扫描，对索引从头到尾找一遍 range：对索引列进行范围查找 index_merge:
SQL执行计划参数详细说明
卡洛斯利伯
01-20 1170
执行计划包含的信息简述 字段 描述 id 执行计划id select_type 查询的类型 table 查询的哪张表 type 访问类型 possible_keys 可能会用到的索引 key 实际使用的索引 key_len 索引中使用的字节数 ref 显示索引的哪一列被使用了 rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取...
MySQL执行计划
dbtrd733355的博客
08-31 313
MySQL执行计划 使用EXPLAIN优化查询 EXPLAIN输出格式 扩展EXPLAIN输出格式 EXPLAIN 语法 {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] ...
MySQL的执行计划详解（Explain）
samker的博客
01-05 1万+
MySQL的执行计划详解（Explain）
mysql的执行顺序和执行计划(一)
码农崛起
08-02 1745
执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数。
三面蚂蚁金服成功拿到offer，帮你解决90%的问题！
m0_57472099的博客
07-14 407
一面（一个半小时） 首先自我介绍 了解Web层开发？数据库索引了解么？聚簇索引，非聚簇索引？索引分类？ 了解数据库都由哪些引擎？分别有什么区别和使用场景？ 了解分布式？高可用？如何保证节点集群的同步？Nginx了解过么？ 什么是事务，数据库的隔离级别，Mysql默认的隔离级别。 JVM的内存模型，GC算法 非递归实现青蛙跳台阶（非递归斐波拉契数列） 判断链表是否有环，若有则返回环的入口节点 对象的重写和重载？ 设计模式里面，单例模式？实现单例模式的双重校验。 ep
mysql 执行计划 详解
08-04
在优化阶段，MySQL查询优化器会生成一个执行计划，该计划告诉MySQL执行SQL语句的具体步骤，包括表的连接方式、扫描方式、过滤条件等。[1]你可以通过使用EXPLAIN关键字或MySQL Workbench来查看执行计划。优化查询效率...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值