【MySQL】Explain字段的解释

原创

原来是咔咔 2019-08-06 16:40:36 ©著作权

文章标签 MySQL 子查询 mysql 字段全文索引 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者原来是咔咔的原创作品，请联系作者获取转载授权，否则将追究法律责任

Explain命令是查看查询优化器是如何决定执行查询的主要方法。这个功能有局限性，并不总会说出真相，但它的输出是可以获取的最好信息，值得花时间去了解，因为可以学习到查询是如何执行的。学会解释explain将会帮助你了解MySQL优化器是如何工作的。

Explain误区跟不足

在执行Explain时MySQL不会执行查询，这是一个误区。事实上查询中包括子查询，那么MySQL实际上会执行子查询，将其结果放在一个临时表中，然后完成外层的查询优化。

虽然我们调用explain可以分析sql语句，但要意识到分析出来的结果也是一个近似结果，别无其他。

explain不会告诉你触发器，存储过程如何影响查询
他不会告诉你mysql在查询执行中所做的特定优化
他不会显示关于查询的执行计划的所有信息
他并不区分具有相同名字的事物。例如，他对内存排序和临时文件都使用filesort，并且对于磁盘上和内存中的临时表都显示using temporary
可能会误导。例如，他会对一个有着很小limit的查询显示全索引扫描

explain列的解释

【MySQL】Explain字段的解释_字段

id列

这一列总是包含一个编号，标识select所属的行。如果在语句当中没有子查询活联合查询，那么就只会有唯一的select，于是每一行在这个列中都将显示一个1。否则，内层的select语句一般会顺序编号，对应于其在原始语句的位置

【MySQL】Explain字段的解释_子查询_02

select_type列

这一列显示了sql语句是简单的查询还是复杂的查询。

简单查询显示的simple，如果有子查询或者联合查询则是primary

SUBQUERY

包含在SELECT列表中的子查询中的SELECT（换句话说，不在FROM子句中）标记为SUBQUERY。

DERIVED

DERIVED值用来表示包含在FROM子句的子查询中的SELECT， MySQL会递归执行并将结果放到一个临时表中。服务器内部称其“派生表”，因为该临时表是从子查询中派生来的。

UNION

在UNION中的第二个和随后的SELECT被标记为UNION。第一个SELECT被标记就好像它以部分外查询来执行。这就是之前的例子中在UNION中的第一个SELECT显示为PRIMARY的原因。如果UNION被 FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED。

UNION RESULT

用来从UNION的匿名临时表检索结果的SELECT被标记为UNION RESULT。

除了这些值，SUBQUERY和UNION还可以被标记为DEPENDENT和 UNCACHEABLE。DEPENDENT意味着SELECT依赖于外层查询中发现的数据；UNCACHEABLE意味着SELECT中的某些特性阻止结果被缓存于一个Item_cache中。（Item_cache未被文档记载；它与查询缓存不是一回事，尽管它可以被一些相同类型的构件否定，例如RAND()函数。）

table列

显示这一步所访问的数据库中的表的名称。

type列

这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型依次为：

system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL

system
系统表，表中只有一行数据
const
读常量，最多只会有一条记录匹配，由于是常量，实际上只需要读一次。
eq_ref
最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问
ref
对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或索引不是主键或唯一索引时，使用ref类型（也就是说，此联接能够匹配多行记录）。
ref可用于使用’=‘或’<=>'操作符作比较的索引列。
fulltext
使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
ref_or_null

跟ref类型类似，只是增加了null值的比较。实际用的不多。

eg.
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

index_merge
表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range
unique_subquery
用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。
该类型替换了下面形式的IN子查询的ref：
value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery
子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引
range
索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
index
索引全表扫描，把索引从头到尾扫一遍
all
全表扫描,性能最差。