MySQL（二）Explain工具分析SQL执行计划

一、引言

　　使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析我们的查询语句有哪些可优化的地方，这样有助于我们优化原有的SQL，在执行SQL时也能做到心里有底。

　　在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

　　PS：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。

二、Explain工具

-- 使用 explain 工具来分析后面的select语句执行计划
explain select * from t_actor;

-- 连着上一句一起使用，展示MySQL优化我们的SQL后执行的语句【PS：该结果拿出来不一定是标准的SQL语句，不一定能直接运行】
show warnings;

三、Explain结果集字段含义分析

id列

　　定义：id列的编号是 select 的序列号，有几个 select 就有几个id。

　　PS：id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type列

　　定义：select_type 表示对应行是简单还是复杂的查询。

　　分类：

• simple ：简单查询。查询不包含子查询和union
• primary ：复杂查询中最外层的 select
• subquery ：包含在 select 中的子查询（不在 from 子句中）
• derived ：包含在 from 子句中的子查询。
• union ：在 union 中的第二个和随后的 select

　　PS: from关键字后面的查询称之为衍生查询，MySQL会将结果存放在一个临时表中，称为衍生表或派生表

　　举个例子：

table列

　　定义：表示 explain 的一行正在访问哪个表。

　　当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。【如上面的查询截图】

type列

　　定义：表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

　　优>>差：system > const > eq_ref > ref > range > index > ALL

　　PS：一般来说，SQL语句得保证查询达到range级别，最好达到ref。

null

　　🎈mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

const, system

　　🎈mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。

　　PS：dual就是mysql的一张空表，sql语句中比如select 1 from dual其实大意就是查询1这个常量，至于from dual其实是为了符合sql语法而已.

eq_ref

　　🎈primary key或unique key索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。【简单理解：主键连接主键查询】

ref

　　🎈相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

range

　　🎈范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

index

　　🎈扫描全索引就能拿到结果。

　　PS：一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，二级索引一般比较小，所以这种通常比ALL快一些。

ALL

　　🎈全表扫描，扫描聚簇索引的所有叶子节点。

possible_keys列

　　定义：显示查询可能使用哪些索引来查找。

　　PS：explain时可能出现possible_keys有列，而key显示NULL的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

　　如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果。

key列

　　定义：显示mysql实际采用哪个索引来优化对该表的访问。

　　PS：如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len列

　　定义：显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

ref列

　　定义：显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

rows列

　　定义：mysql估计要读取并检测的行数。

　　PS：这个不是结果集里的行数。

extra列

　　定义：展示MySQL的额外信息。

Using index

　　🎈使用覆盖索引。

　　覆盖索引：结果集可以通过一个索引【一般是辅助/二级索引】快速定位到，且不用回表查询的，这种查询方式我们可以说是用上了覆盖索引。

　　PS：覆盖索引并不是某一种具体的索引类型。

Using where

　　🎈使用 where 语句来处理结果，并且查询的列未被索引覆盖。

Using index condition

　　🎈查询的列不完全被索引覆盖，where条件中是一个前导列的范围。

Using temporary

　　🎈MySQL需要创建一张临时表来处理查询。

　　常见处理方式：创建索引

　　PS：MySQL若没有用上索引的distinct语句会创建临时表

Using filesort

　　🎈排序场景没有用到索引，数据较小时从内存排序，否则需要在磁盘完成排序。

　　常见处理方式：创建索引

Select tables optimized away

　　🎈使用某些聚合函数（比如 max、min）来访问存在索引的某个字段，且只用从索引就能获取到我们需要的结果。

四、小问答

Q：为啥MySQL在选择索引时，会优先选择二级索引？

　　在mysql中，如果查询数据时发现通过一级索引和二级索引都能拿到我们需要的数据时，会优先选用二级索引，因为二级索引比较小，而主键索引会包含整个表的所有信息，相对比较大，查询耗费的资源会比较高。