MYSQL 性能分析器 EXPLAIN 用法实例分析

原作者: [db:作者] 来自: [db:来源] 收藏邀请

本文实例讲述了MYSQL 性能分析器 EXPLAIN 用法。分享给大家供大家参考，具体如下：

使用方法：

EXPLAIN SELECT * FROM user;

环境和数据准备

-- 查看 MySQL 版本
SELECT VERSION();
 
-- MySQL 提供什么存储引擎
SHOW ENGINES;
 
-- 查看默认存储引擎
SHOW VARIABLES LIKE '%storage_engine%';

输出结果：

id:输出的是整数，用来标识整个 SQL 的执行顺序。id 如果相同，从上往下依次执行id不同；id 值越大，执行优先级越高，越先被执行；如果行引用其他行的并集结果，则该值可以为NULL

select_type:[查询类型]

SIMPLE：简单的 SELECT 查询，没有 UNION 或者子查询，包括单表查询或者多表 JOIN 查询

PRIMARY：最外层的 select 查询，常见于子查询或 UNION 查询，最外层的查询被标识为 PRIMARY

UNION：UNION 操作的第二个或之后的 SELECT，不依赖于外部查询的结果集（外部查询指的就是 PRIMARY 对应的 SELECT）

DEPENDENT UNION：UNION 操作的第二个或之后的 SELECT，依赖于外部查询的结果集

UNION RESULT：UNION 的结果（如果是 UNION ALL 则无此结果）

SUBQUERY：子查询中的第一个 SELECT 查询，不依赖于外部查询的结果集

DEPENDENT SUBQUERY：子查询中的第一个select查询，依赖于外部查询的结

DERIVED：派生表（临时表），常见于 FROM 子句中有子查询的情况

注意：MySQL5.7 中对 Derived table 做了一个新特性，该特性允许将符合条件的 Derived table 中的子表与父查询的表合并进行直接JOIN，从而简化简化了执行计划，同时也提高了执行效率；默认情况下，MySQL5.7 中这个特性是开启的，所以默认情况下，上面的 SQL 的执行计划应该是这样的

MATERIALIZED：被物化的子查询，MySQL5.6 引入的一种新的 select_type，主要是优化 FROM 或 IN 子句中的子查询，更多详情请查看：Optimizing Subqueries with Materialization

UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被缓存，每次都需要计算

UNCACHEABLE UNION：类似于 UNCACHEABLE SUBQUERY，只是出现在 UNION 操作中

SIMPLLE、PRIMARY、SUBQUERY、DERIVED 这 4 个在实际工作中碰到的会比较多，看得懂这 4 个就行了，至于其他的，碰到了再去查资料就好了

table:显示了对应行正在访问哪个表(有别名就显示别名)，还会有 <union2,3> 、 <subquery2> 、 <derived2> （这里的 2,3、2、2 指的是 id 列的值）类似的值

partitions:查询进行匹配的分区，对于非分区表，该值为NULL。大多数情况下用不到分区，所以这一列我们无需关注

type:

关联类型或者访问类型，它指明了 MySQL 决定如何查找表中符合条件的行，这是我们判断查询是否高效的重要依据,完整介绍请看：explain-join-types

system:该表只有一行（=系统表），是 const 类型的特例

const:确定只有一行匹配的时候，mysql 优化器会在查询前读取它并且只读取一次，速度非常快。用于 primary key 或 unique 索引中有常亮值比较的情形

eq_ref:对于每个来自于前面的表的行，从该表最多只返回一条符合条件的记录。当连接使用的索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时使用，非常高效

ref:索引访问，也称索引查找，它返回所有匹配某个单个值的行。此类型通常出现在多表的 JOIN 查询, 针对于非 UNIQUE 或非 PRIMARY KEY, 或者是使用了最左前缀规则索引的查询，换句话说，如果 JOIN 不能基于关键字选择单个行的话，则使用ref

fulltext:当使用全文索引时会用到，这种索引一般用不到，会用专门的搜索服务(solr、elasticsearch等)来替代

ref_or_null:类似ref，但是添加了可以专门搜索 NULL 的行

这个是有前提条件的，前提为 weapon 列有索引，且 weapon 列存在 NULL

index_merge:该访问类型使用了索引合并优化方法

这个同样也是有条件的， id 列和 weapon 列都有单列索引。如果出现 index_merge，并且这类 SQL 后期使用较频繁，可以考虑把单列索引换为组合索引，这样效率更高

unique_subquery:类似于两表连接中被驱动表的 eq_ref 访问方式，unique_subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键或者唯一索引进行等值匹配时，则会使用 unique_subquery

index_subquery:index_subquery 与 unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引

range:使用索引来检索给定范围的行，当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比较关键字列时，则会使用 rang,前提是必须基于索引，也就是 id 上必须有索引

index:当我们可以使用索引覆盖，但需要扫描全部的索引记录时，则会使用 index；进行统计时非常常见

ALL:我们熟悉的全表扫描

possible_keys:展示在这个 SQL 中，可能用到的索引有哪些，但不一定在查询时使用。若为空则表示没有可以使用的索引，此时可以通过检查 WHERE 语句看是否可以引用某些列或者新建索引来提高性能

key:展示这个 SQL 实际使用的索引，如果没有选择索引，则此列为null，要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者I GNORE INDEX

key_len:展示 MySQL 决定使用的键长度（字节数）。如果 key 是 NULL，则长度为 NULL。在不损失精确性的情况下，长度越短越好

ref:展示的是与索引列作等值匹配的东东是个啥，比如只是一个常数或者是某个列。它显示的列的名字（或const），此列多数时候为 Null

rows:展示的是 mysql 解析器认为执行此 SQL 时预计需要扫描的行数。此数值为一个预估值，不是具体值，通常比实际值小

filtered:展示的是返回结果的行数所占需要读到的行（rows 的值）的比例，当然是越小越好啦