SQL执行计划学习

背景：

        实际项目开发中，由于我们不知道实际查询的时候数据库里发生了什么事情，数据库软件是怎样扫描表、怎样使用索引的，因此，我们能感知到的就只有sql语句运行的时间，在数据规模不大时，查询是瞬间的，因此，在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增大，如千万、亿的时候，我们运行同样的sql语句时却发现迟迟没有结果，这个时候才知道数据规模已经限制了我们查询的速度。所以，查询优化和索引也就显得很重要了。

引出的问题：

        当我们在查询前能否预先估计查询究竟要涉及多少行、使用哪些索引、运行时间呢？
答案是可以的，mysql提供了相应的功能和语法来实现该功能。那就是sql执行计划！

关键字：explain

什么是Sql执行计划？

        执行计划，简单的来说，是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。在MySQL使用 explain 关键字来查看SQL的执行计划。（这里我的理解是，存储引擎在执行sql的时候，把一条sql分解，列出来每一步需要干什么，并按照步骤依次执行，这样我们就能看出来哪个步骤耽误了时间，当然这也是接下来要讲的重点！）

如何查看sql执行计划？

1. 查询SELECT * from employee WHERE `name` ='蒋峰1';

2. 查看上述语句的执行计划 （加关键字explain）

EXPLAIN SELECT * from employee WHERE `name` ='蒋峰1';

读懂执行计划

通过上面，我们知道了什么是执行计划,也看到了执行计划到底是什么东西,现在我们来具体了解一下,MySQL执行计划中,每个属性代表的是什么意思?

我们一一来介绍,并说明每个属性有哪些可选值,以及每个可选值的意思。

id

表示查询中select操作表的顺序,按顺序从大到依次执行（不是表中的自增主键！）
id值相同执行顺序从上到下。

id值不同时id值大的先执行。

select_type

这一列显示了对应行是简单还是复杂SELECT.取值如下:SIMPLE值意味着查询不包括子查询和UNION。查询有任何复杂的子部分，则最外层标记为PRIMARY.取值如下：

type 

该属性表示访问类型,有很多种访问类型。
最常见的其中包括以下几种: ALL(全表扫描), index(索引扫描),range(范围扫描),ref (非唯一索引扫描),eq_ref(唯一索引扫描,),(const)常数引用, 访问速度依次由慢到快。
其中 : range(范围)常见于 between and …, 大于 and 小于这种情况。

提示 : 慢SQL是否走索引,走了什么索引,也就可以通过该属性查看了。

table 

输出数据行所在的表的名称

possible_keys 

顾名思义,指出MySQL能使用哪些索引来优化查询，查询所涉及的列上的索引都会被列出，但不一定会被使用，算是个提示作用！

key 

显示MySQL实际使用的索引,其中就包括主键索引(PRIMARY),或者自建索引的名字。

如果没有可用的索引，则显示为NULL

key_len 

表示索引字段的最大可能长度，KEY_LEN的长度由字段定义计算而来，并非数据的实际长度，

当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中 MySQL 会真正使用了哪些索引。

ref 

连接匹配条件,如果走主键索引的话,该值为: const, 全表扫描的话,为null值
表示哪些列或常量被用于查找索引列上的值

该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。 ref还可以用于检索字段使用=操作符来比较的时候。以下的几个例子中，MySQL将使用 ref 来处理ref_table，和eq_ref的区别是-用到的索引是否唯一性

rows （关键）

扫描行数,也就是说，需要扫描多少行,才能获取目标行数,一般情况下会大于返回行数。通常情况下,rows越小,效率越高, 也就有大部分SQL优化，都是在减少这个值的大小。

注意: 理想情况下扫描的行数与实际返回行数理论上是一致的,但这种情况及其少,如关联查询,扫描的行数就会比返回行数大大增加)

Extra 

这个属性非常重要,该属性中包括执行SQL时的真实情况信息,如上面所属,使用到的是”using where”，表示使用where筛选得到的值,常用的有:

“Using temporary”: 使用临时表 “using filesort”: 使用文件排序

对一开始的sql进行改造

看到这里,我们应该已经发现,在第一步中,我们的这条SQL
SELECT * from employee WHERE `name` ='蒋峰1';
是没有走索引的,而且还是全表扫描,在数据量少的情况下,问题还不会特别突出,如果数据量比较大,这可是个会造成生产事故的慢查询哦，现在我们改造一下,将name字段添加上索引,
# 添加索引
alter table employee add index idx_name(name);

看看它的执行计划是怎样的。

你看,现在已经走idx_name索引了,其type从All(全表扫描)到ref(非唯一索引了)，别看就只有这一点点小区别,在大数据量的时候,可是会起大作用的哦。

再举一个栗子

课程表
create table Course(
c_id int PRIMARY KEY,
name varchar(10))
数据100条-------------------------100条（自己模拟）

学生表:
create table Student(
id int PRIMARY KEY,
name varchar(10))
数据70000条-----------------------400条

学生成绩表SC
CREATE table SC(
    sc_id int PRIMARY KEY,
    s_id int,
    c_id int,
    score int)
数据70w条--------------------------30w条

查询目的：

查找语文考100分的考生

查询语句：

select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
执行时间：30248.271s（29.648s）
为什么这么慢，先来查看下查询计划：
EXPLAIN 
select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

发现没有用到索引，type全是ALL，那么首先想到的就是建立一个索引，建立索引的字段当然是在where条件的字段。
先给sc表的c_id和score建个索引
CREATE index sc_c_id_index on SC(c_id);

CREATE index sc_score_index on SC(score);

再次执行上述查询语句，时间为: 1.054s(2.428s)
快了3w多倍，大大缩短了查询时间，看来索引能极大程度的提高查询效率，建索引很有必要，很多时候都忘记建
索引了，数据量小的的时候压根没感觉，大数据量感觉贼爽。

但是2s的时间还是太长了，还能进行优化吗，仔细看执行计划：

接下来再次优化：这次我们用连接查询！（先删除索引）
alter table SC drop index sc_c_id_index;
alter table SC drop index sc_score_index;
SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100

再次执行上述查询语句，时间为: 0.088s

EXPLAIN SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100
再看执行计划：

发现一个ALL，extra字段中显示where
所以我们尝试加索引：
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

再次执行上条sql：
SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100
再次执行上述查询语句，时间为: 0.010s

再看sql执行计划：

总结：
1.mysql嵌套子查询效率确实比较低
2.可以将其优化成连接查询
3.连接表时，可以先用where条件对表进行过滤，然后做表连接
（虽然mysql会对连表语句做优化）
4.建立合适的索引，必要时建立多列联合索引
5.当然我们最主要的是要学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要

索引优化
上面讲到子查询的优化，以及如何建立索引，而且在多个字段索引时，分别对字段建立了单个索引
后面发现其实建立联合索引效率会更高，尤其是在数据量较大，单个列区分度不高的情况下。
create index sc_c_id_score_index on SC(c_id,score);
时间为: 0.008s(由于数据量有限，效果不明显，数据大的时候效率更高)

能写sql 只是程序员的基本功，能写出性能优异的SQL是优秀程序员的必备技能

什么是Mysql的执行计划

要对执行计划有个比较好的理解，需要先对MySQL的基础结构及查询基本原理有简单的了解。

一条SQL如何执行？大概过程

MySQL本身的功能架构分为三个部分，分别是 应用层、逻辑层、物理层，不只是MySQL ，其他大多数数据库产品都是按这种架构来进行划分的。

• 应用层，主要负责与客户端进行交互，建立链接，记住链接状态，返回数据，响应请求，这一层是和客户端打交道的。

• 逻辑层，主要负责查询处理、事务管理等其他数据库功能处理，以查询为例。

  • 首先接受到查询sql之后，数据库会立即分配一个线程对其进行处理；
  • 第一步查询处理器会对SQL查询进行优化，优化后会生成执行计划；
  • 然后交由计划执行器来执行。
  • 计划执行器需要访问更底层的事务管理器，存储管理器来操作数据，他们各自的分工各有不同；
  • 最终通过调用物理层的文件获取到查询结构信息，将最终结果响应给应用层。

• 物理层，实际物理磁盘上存储的文件，主要分为数据文件，日志文件

通过上面的描述，生成执行计划是执行一条SQL必不可少的步骤，一条SQL性能的好坏，可以通过查看执行计划很直观的看出来，执行计划提供了各种查询类型与级别，方面我们进行查看以及为作为性能分析的依据。

所谓的执行计划，就是mysql如何执行一条Sql语句，包括sql查询的顺序、是否使用索引、以及使用索引信息的等等。

如何分析执行计划，来帮助我们写出更好的Sql

MySQL为我们提供了 explain 关键字来直观的查看一条SQL的执行计划。

//1. 查询table_name
select * from table_name where name="explain";

//2. 查看上述语句的执行计划
explain select * from table_name where name="explain";

执行查看上述2语句后,我们可以得出以下执行计划结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	table_name		ALL					1	Using where

上面这个执行计划给到的信息是: 这个结果通过一个简单的语句全表扫描,共扫描1行,使用where条件在t_base_user表中筛选出的。

---

要想看懂执行计划，就要明白每一个参数的含义

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra

id

有一组数字组成。表示一个查询中各个子查询的执行顺序;

• id相同执行顺序由上至下。

• id不同，id值越大优先级越高，越先被执行。

• id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

select_type

每个子查询的查询类型，一些常见的查询类型。

id	select_type	description
1	SIMPLE	不包含任何子查询或union等查询
2	PRIMARY	包含子查询最外层查询就显示为 PRIMARY
3	SUBQUERY	在select或 where字句中包含的查询
4	DERIVED	from字句中包含的查询
5	UNION	出现在union后的查询语句中
6	UNION RESULT	从UNION中获取结果集，例如上文的第三个例子

table

表示该语句查询的表

partitions

表分区、表创建的时候可以指定通过那个列进行表分区。 ex：

create table tmp (
    id int unsigned not null AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;

type

访问类型

• ALL 扫描全表数据
• index 遍历索引
• range 索引范围查找
• index_subquery 在子查询中使用 ref
• unique_subquery 在子查询中使用 eq_ref
• ref_or_null对Null进行索引的优化的 ref
• fulltext 使用全文索引
• ref 使用非唯一索引查找数据
• eq_ref 在join查询中使用RIMARY KEY or UNIQUE NOT NULL索引关联。
• const 使用主键或者唯一索引，且匹配的结果只有一条记录。
• system const 连接类型的特例，查询的表为系统表。

possible_keys

查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。可能使用的索引，注意不一定会使用。

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

Tips:查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中

key_length

索引长度 char()、varchar()索引长度的计算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)

其他类型索引长度的计算公式: ex:

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '',
  `age` int(11),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

name 索引长度为： 编码为utf8mb4,列长为128,不允许为NULL,字段类型为varchar(128)。key_length = 128 * 4 + 0 + 2 = 514;

age 索引长度：int类型占4位，允许null,索引长度为5。

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

扫描行数,也就是说，需要扫描多少行,采能获取目标行数,一般情况下会大于返回行数。通常情况下,rows越小,效率越高,也就有大部分SQL优化，都是在减少这个值的大小。

Tips: 理想情况下扫描的行数与实际返回行数理论上是一致的,但这种情况及其少,如关联查询,扫描的行数就会比返回行数大大增加)

extra

extra的信息非常丰富，常见的有：

• 1.Using index 使用覆盖索引
• 2.Using where 使用了用where子句来过滤结果集
• 3.Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
• 4.Using temporary 使用了临时表

参考blog → 彻底读懂Mysql执行计划

如图：选中SQL点击右键