mysql执行计划explain

我就是我 2022-12-17 08:49 121阅读 0赞

一、介绍：explain

执行计划的几个属性：

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

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

*  SIMPLE: 简单的 select 查询,不使用 union 及子查询
 *  PRIMARY: 最外层的 select 查询
 *  UNION: UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集
 *   DERIVED: 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。
 *  UNION RESULT：UNION的结果
 *  SUBQUERY：子查询中的第一个SELECT
 *  DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
 *   DERIVED：导出表的SELECT(FROM子句的子查询)

取值如下：

![70][]

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

1.  NULL：MySQL不访问任何表或索引，直接返回结果
2.  system:  表仅有一行(=系统表)。这是 const 连接类型的一个特例。
3.  const:  const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。
4.  eq\_ref: 从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
5.  ref:  连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。 叫做 ref 是因为索引要 跟某个参考值相比较。这个参考值或者是一个常数,或者是来自一个表里的多表查询的 结果值。
6.  ref\_or\_null:  如同 ref, 但是 MySQL 必须在初次查找的结果里找出 null 条目,然后进行二次查找。
7.  index\_merge: 说明索引合并优化被使用了。
8.  unique\_subquery:  在某些 IN 查询中使用此种类型,而不是常规的 ref:valueIN (SELECT primary\_key FROM single\_table WHERE some\_expr)
9.  index\_subquery:  在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与unique\_subquery 类似,但是查询的是非唯一 性索引
10. range:  只检索给定范围的行,使用一个索引来选择 行。key列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range（**一般该查询是最低忍受范围，一下两个就是慢的**）。
11. index:  全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。
12. all: 最坏的情况,从头到尾全表扫描（**出现了说明需要优化了**）。

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

![70 1][]

4、table ：输出数据行所在的表的名称

![70 2][]

5、possible\_keys ：顾名思义,指出MySQL能使用哪些索引来优化查询，如果为空,说明没有可用的索引。查询所涉及的列上的索引都会被列出，但不一定会被使用，算是个提示作用！

6、key：实际从 possible\_key 选择使用的索引。 如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT语句中使用 USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制 MYSQL 忽略索引。

7、key\_len ：表示索引字段的最大可能长度，KEY\_LEN的长度由字段定义计算而来，并非数据的实际长度，当 key 字段的值为 null时，索引的长度就是 null。在不损失精确性的情况 下,长度越短越好。注意，key\_len的值可以告诉你在联合索引中 MySQL 会真正使用了哪些索引。

8、ref：连接匹配条件,如果走主键索引的话,该值为: const, 全表扫描的话,为null值，表示哪些列或常量被用于查找索引列上的值。该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。 ref还可以用于检索字段使用=操作符来比较的时候。以下的几个例子中，MySQL将使用 ref 来处理ref\_table，和eq\_ref的区别是-用到的索引是否唯一性。

9、rows （关键）：

扫描行数,也就是说，需要扫描多少行,才能获取目标行数,一般情况下会大于返回行数。通常情况下,rows越小,效率越高, 大部分SQL优化，都是在减少这个值的大小。注意: 理想情况下扫描的行数与实际返回行数理论上是一致的,但这种情况及其少,如关联查询,扫描的行数就会比返回行数大大增加)。

10、Extra ：这个属性非常重要,该属性中包括执行SQL时的真实情况信息,如上面所属,使用到的是”using where”，表示使用where筛选得到的值,常用的有:“Using temporary”: 使用临时表 “using filesort”: 使用文件排序。

Distinct：在select部分使用了distinc关键字。MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。  
    Using filesort：排序时无法使用到索引时，就会出现这个。  
    Using temporary：表示使用了临时表存储中间结果。例如：ORDER BY或GROUP BY中的字段都来自左连接非驱动表（第二张表），就会创建一个临时表了

Using index：索引覆盖。直接通过索引就可以获取查询的数据。  
    Using where：**需要回行。**在查找使用索引的情况下，需要回表去查询所需的数据  
    Using sort\_union、Using union、Using intersect：这些函数说明如何为index\_merge联接类型合并索引扫描。  
    通过相乘EXPLAIN输出的rows列的所有值，可以粗略地知道MySQL必须检查多少行以执行查询。

**结论：**

1）出现了Using temporary，一般临时表排序，需要优化

2）出现了Using filesort，排序时无法使用到索引时，就会出现这个

3）rows过多，或者几乎是全表的记录数

4）key 是 (NULL)

5）possible\_keys 出现过多（待选）索引

![70 3][]

二、单表查询举例：

1、准备工作：如现有表：

CREATE TABLE \`test\` (  
  \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
  \`name\` varchar(255) DEFAULT NULL COMMENT '名称',  
  \`age\` int(11) DEFAULT NULL COMMENT '年龄',  
  \`year\` int(11) DEFAULT NULL,  
  \`month\` int(11) DEFAULT NULL,  
  PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

![20201023100237954.png][]

2、查询：

![20201023100358544.png][]

下面查看此查询语句的执行计划：

![20201023100434406.png][]

3、给name加上索引：

ALTER TABLE \`test\`  
ADD INDEX \`index\_name\` (\`name\`) ;

再看下执行计划：

![20201023101344728.png][]

只扫描了一行，性能比较高。

三、连接查询举例：

课程表  
create table Course(  
c\_id int PRIMARY KEY,  
name varchar(10))  
数据200条-------------------------  
  
学生表:  
create table Student(  
id int PRIMARY KEY,  
name varchar(10))  
数据80000条-----------------------  
  
学生成绩表SC  
CREATE table SC(  
    sc\_id int PRIMARY KEY,  
    s\_id int,  
    c\_id int,  
    score int)  
数据80w条--------------------------

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 )

![70 4][]

发现没有用到索引，type全是ALL，先给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)。

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

![70 5][]

接下来再次优化：这次我们用连接查询！（先删除索引）  
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  
再看执行计划：

![70 6][]

发现一个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执行计划：

![70 7][]

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

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

[70]: /images/20221122/8e22876abd354d06b0241577878aa135.png
[70 1]: /images/20221122/96a2f6303cef4042927de6379ad41624.png
[70 2]: /images/20221122/576fb9150447410f8aa2b7cdbaa7ac2a.png
[70 3]: /images/20221122/b592297ffc314a7e903d281f7c8d2958.png
[20201023100237954.png]: /images/20221122/2ed16cb7507a4ad7b1d266acd8a01196.png
[20201023100358544.png]: /images/20221122/f16f0549dd854adb8a1b72490cc19fb1.png
[20201023100434406.png]: /images/20221122/f53600aa4617482f8e9a54af2aa75ff8.png
[20201023101344728.png]: /images/20221122/f5b338474ce74837b19f9e6eeecc82f9.png
[70 4]: /images/20221122/23fbe795db8b4a6bb3fadb6b0a1f9a99.png
[70 5]: /images/20221122/d3ee766b2e7543a68f04b42286c515b0.png
[70 6]: /images/20221122/5ac1ae8db2b04a80b142aee3543b7a13.png
[70 7]: /images/20221122/7ce2434d2f524c778a92e91f35b88186.png