来自灵魂的拷问——知道什么是SQL执行计划吗？

时间  2020-09-05 标签 来自 灵魂 拷问 知道 什么 sql 执行 计划

在这里插入图片描述

面试官说：工做这么久了，应该知道sql执行计划吧，讲讲Sql的执行计划吧！
看了看面试官手臂上纹的大花臂和一串看不懂的韩文，吞了吞口水，暗示本身镇定点，整理了一下思绪缓缓的对面试官说：我不会
面试官：。。。。，回去等通知吧
我：%^&%$!@#html

1、前言

当咱们工做到了必定的年限以后，一些应该掌握的知识点，咱们是必须须要去了解的，好比今天面试官问的SQL执行计划
当咱们执行一条SQL的时候，能够直接对应的结果，可是你并不晓得，它会经历多深远黑暗的隧道，经过链接器、查询缓存、分析器、优化器、执行器重重筛选，才有可能展现到咱们面前，有时候当你等待N长时间，可是展示的倒是 timeout，这个时候想砸电脑的心都有了，不过当你看了今天的SQL执行计划后，你不再用砸电脑了，看懂了这篇文章你就会知道这都不是事，让咱们一块儿来揭晓这里面的奥妙java

在实际的应用场景中，为了知道优化SQL语句的执行，须要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。
一般会使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。mysql

官网地址： https://dev.mysql.com/doc/refman/5.5/en/explain-output.htmlweb

首先咱们来下面的一条sql语句，其中会有id、select_type 、table 等等这些列，这些就是咱们执行计划中所包含的信息，咱们要弄明白的就是这些列是用来干吗的，以及每一个列可能存在多少个值。
explain select * from emp;
在这里插入图片描述面试

2、执行计划中包含的信息

列(Column) 含义(Meaning)
id The SELECT identifier（每一个select子句的标识id）
select_type The SELECT type（select语句的类型）
table The table for the output row（当前表名）
partitions The matching partitions （显示查询将访问的分区，若是你的查询是基于分区表）
type The join type（当前表内访问方式）
possible_keys The possible indexes to choose（可能使用到的索引）
key The index actually chosen（通过优化器评估最终使用的索引）
key_len The length of the chosen key （使用到的索引长度）
ref The columns compared to the index（引用到的上一个表的列）
rows Estimate of rows to be examined （要获得最终记录索要扫描通过的记录数）
filtered Percentage of rows filtered by table condition（存储引擎返回的数据在server层过滤后，剩下知足查询的记录数量的比例）
extra Additional information （额外的信息说明） 
贴心的小农已经把sql复制出来了，只须要放到数据库中执行便可，方便简单快捷 

														 ——牧小农

建表语句：算法

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `DEPTNO` int NOT NULL,
  `DNAME` varchar(14) DEFAULT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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');

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` int NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int DEFAULT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY `idx_job` (`JOB`),
  KEY `jdx_mgr` (`MGR`),
  KEY `jdx_3` (`DEPTNO`),
  KEY `idx_3` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-02-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-01-05', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-09-06', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');

DROP TABLE IF EXISTS `emp2`;
CREATE TABLE `emp2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `empno` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `emp2` VALUES ('1', '111');
INSERT INTO `emp2` VALUES ('2', '222');

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `GRADE` int NOT NULL,
  `LOSAL` double DEFAULT NULL,
  `HISAL` double DEFAULT NULL,
  PRIMARY KEY (`GRADE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '9999');

DROP TABLE IF EXISTS `t_job`;
CREATE TABLE `t_job` (
  `id` int NOT NULL AUTO_INCREMENT,
  `job` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `j` (`job`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.1 id

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

id号分为三种状况：数据库

​ 一、若是id相同，那么执行顺序从上到下缓存

-- 左关联
explain select * from emp e left  join dept d on e.deptno = d.deptno;
-- 右关联
explain select * from emp e right join dept d on e.deptno = d.deptno;

经过left join 和 right join 验证；id同样（注意执行计划的table列），left join 先扫描e表，再扫描d表；right join 先扫描d表，再扫描e表
在这里插入图片描述ide

​ 二、若是id不一样，若是是子查询，id的序号会递增，id值越大优先级越高，越先被执行

explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

在下面的表中回先查询 id 为 2的数据 也就是咱们的 d表（注意：咱们能够看到d表中select_type为 SUBQUERY 也就是子查询的 意思），而后根据d表中的deptno去查询 e表中的数据
在这里插入图片描述

​ 三、id相同和不一样的，同时存在：相同的能够认为是一组，从上往下顺序执行，在全部组中，id值越大，优先级越高，越先执行

explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

在这里先从id为2的执行，若是id是同样的，就按照顺序执行
在这里插入图片描述

2.2 select_type

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

select_type 值 含义(Meaning）
SIMPLE 简单的查询不包含 UNION 和 subqueries
PRIMARY 查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
UNION 若第二个select出如今union以后，则被标记为union
DEPENDENT UNION 跟union相似，此处的depentent表示union或union all联合而成的结果会受外部表影响
UNION RESULT 从union表获取结果的select
SUBQUERY 在select或者where列表中包含子查询
DEPENDENT SUBQUERY subquery的子查询要受到外部表查询的影响
DERIVED from子句中出现的子查询，也叫作派生类
UNCACHEABLE SUBQUERY 表示使用子查询的结果不能被缓存
UNCACHEABLE UNION 表示union的查询结果不能被缓存 
--simple:简单的查询，不包含子查询和union
explain select * from emp;

在这里插入图片描述

--primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

在这里插入图片描述

--union:若第二个select出如今union以后，则被标记为union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

在这里插入图片描述

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

在这里插入图片描述

--union result:从union表获取结果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

在这里插入图片描述

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

在这里插入图片描述

--dependent subquery:subquery的子查询要受到外部表查询的影响
explain select * from emp e where e.deptno = (select distinct deptno from dept where deptno = e.deptno);

在这里插入图片描述

--DERIVED: from子句中出现的子查询，也叫作派生类，
explain select * from (select ename staname,mgr from emp where ename = 'W' union select ename,mgr from emp where ename = 'E') a;

在这里插入图片描述

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

在这里插入图片描述

--uncacheable union:表示union的查询结果不能被缓存
explain select * from emp where exists (select 1 from dept where  emp.deptno = dept.deptno union select 1 from dept where  deptno = 10);

在这里插入图片描述

2.3 table

对应行正在访问哪个表，表名或者别名，多是临时表或者union合并结果集
一、若是是具体的表名，则代表从实际的物理表中获取数据，也能够是表的别名
explain select * from emp where sal > (select avg(sal) from emp) ;
在这里插入图片描述

​ 二、表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
explain select * from (select ename staname,mgr from emp where ename = 'WARD' union select ename staname,mgr from emp where ename = 'SMITH') a;

derived2 ： 代表咱们须要从衍生表2中取数据
在这里插入图片描述

​ 三、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id
explain select * from emp where deptno = 10 union select * from emp where sal >2000;
在这里插入图片描述

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

在这里插入图片描述

--index_subquery：利用索引来关联子查询，再也不扫描全表
explain select * from emp where emp.job in (select job from t_job);
--unique_subquery:该链接类型相似与index_subquery,使用的是惟一索引
 explain select * from emp e where e.deptno in (select distinct deptno from dept);
--ref_or_null：对于某个字段即须要关联条件，也须要null值的状况下，查询优化器会选择这种访问方式
explain select * from emp e where  e.mgr is null or e.mgr=7369;

在这里插入图片描述

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

在这里插入图片描述

--eq_ref ：使用惟一性索引进行数据查找
explain select * from emp,emp2 where emp.empno = emp2.empno;

在这里插入图片描述

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

在这里插入图片描述

--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

2.5 possible_keys

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

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

在这里插入图片描述

2.6 key

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

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

在这里插入图片描述

2.7 key_len

表示索引中使用的字节数，能够经过key_len计算查询中使用的索引长度，在不损失精度的状况下长度越短越好。
一、通常地，key_len 等于索引列类型字节长度，例如int类型为4 bytes，bigint为8 bytes；
二、若是是字符串类型，还须要同时考虑字符集因素，例如utf8字符集1个字符占3个字节，gbk字符集1个字符占2个字节
三、若该列类型定义时容许NULL，其key_len还须要再加 1 bytes
四、若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不容许整列建立索引，若是建立部分索引也被视为动态列类型），其key_len还须要再加 2 bytes
字符集会影响索引长度、数据的存储空间，为列选择合适的字符集；变长字段须要额外的2个字节，固定长度字段不须要额外的字节。而null都须要1个字节的额外空间，因此之前有个说法：索引字段最好不要为NULL，由于NULL让统计更加复杂，而且须要额外一个字节的存储空间。

-- key_len的长度计算公式：

-- varchar(len)变长字段且容许NULL      : len*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
-- varchar(len)变长字段且不容许NULL    : len*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)

-- char(len)固定字段且容许NULL         : len*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)
-- char(len)固定字段且不容许NULL       : len*(Character Set：utf8=3,gbk=2,latin1=1)
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

在这里插入图片描述

2.8 ref

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

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

在这里插入图片描述

2.9 rows

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

explain select * from emp;

在这里插入图片描述

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

在这里插入图片描述

--using where:使用where进行条件过滤
explain select * from emp2 where empno = 1;

在这里插入图片描述

三 总结

到这里执行计划就讲完了，sql的执行计划并非很难，主要是记住每一个列表明的意思和如何进行优化，这个是须要大量的训练和实操实现的， 有兴趣的小伙伴能够自行去试试，仍是颇有趣的，本文只是简单介绍一下MySQL执行计划，想全面深刻了解MySQL,可优先阅读MySQL官方手册，你们加油~

联系我们 沪ICP备2021010478号-7