mysql-执行计划详解

Explain

Explain 有两个变种

• explain extended

  会在explain的基础上额外提供一些查询优化的信息。紧随其后通过show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有一个filtered列

• explain partitions

  相比 explain 多了一个partitions字段，如果查询是基于分区表的话，会显示查询将访问的分区

限制

• EXPLAIN无法分析触发器、存储过程或UDF会如何影响查询
• 不支持存储过程，尽管可以手动抽取SQL并单独对其进行explain分析
• 不会分析MySQL在查询执行中所做的特定优化
• 不会显示关于查询计划的所有信息
• 不区分具有相同名称的事物。例如，它对内存排序和临时文件都使用“filesort”，并且对磁盘上和内存上的临时表都显示”Using Temporary“
• 可能会误导

输出列说明

输出列	说明
id	编号是select的序列号，而且id的顺序是按照select出现的顺序增长的
select_type	表示对应行的查询是简单还是复杂查询
table	表示explain的这一行正在访问哪个表
partitions	查询将从中匹配记录的分区
type	表示关联类型或者访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围
possible_key	显示查询可能使用哪些索引来查找
key	显示MySQL实际使用那个索引来优化对该表的访问，如果没有索引，则该列是NULL
key_len	显示了mysql在索引里的字节数，通过这个值可以计算出具体使用了索引中的那些列
ref	显示了在key列记录中的索引中，表查找中的值所用到的列或常量
rows	MySQL估计要读取并检测的行数，并不是结果集里的行数
filtered	指示按表条件过滤的表行的估计百分比。最大值为100，这表示未过滤行。值从100减小表示过滤量增加。 rows显示了检查的估计行数，rows× filtered显示了与下表连接的行数
Extra	展示的是额外信息

select_type

输出项	说明
SIMPLE	简单查询。查询不包含子查询和union
PRIMARY	复杂查询中最外层的select
UNION RESULT	在union临时表检索结果的select
DEPENDENT UNION	UNION 取决于外部查询
SUBQUERY	包含在select中的子查询（不在from子句中）
DEPENDENT SUBQUERY	子查询取决于外部查询
DERIVED	包含在from中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表
DEPENDENT DERIVED	派生表依赖于另一个表
MATERIALIZED	物化子查询
UNION	在union中的第二个和随后的select
UNCACHEABLE SUBQUERY	子查询，其结果无法缓存，必须针对外部查询的每一行重新进行评估
UNCACHEABLE UNION	属于不可缓存子查询的中的第二个或更高版本的选择

table列

• <union*M*,*N*>：该行是指具有和id值的行 的 *M*并集 N
• <derived*N*>：该行是指用于与该行的派生表结果id的值 N,派生表可能来自（例如）FROM子句中的子查询
• <subquery*N*>：该行是指该行的物化子查询的结果，其id 值为*N*

type

依次从最优到最差为：system > const > eq_ref > ref > range > index > ALL
一般来说得保证查询达到 range 级别，最好达到 ref

输出项	说明
NULL	意味着MySQL能够在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或索引
system	该表只有一行（=系统表）。这是const联接类型的特例
const	该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。 const表非常快，因为它们只能读取一次
eq_ref	可以用于使用=运算符进行比较的索引列 。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式
ref	索引访问，它返回匹配某个单值的行，可能会找到多个符合条件的行
ref_or_null	ref之上的一个变体，它意味着MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目
index_merge	此联接类型指示使用索引合并优化。在这种情况下，key输出行中的列包含使用的索引列表，并key_len包含使用的索引的最长键部分的列表
unique_subquery	只是一个索引查找函数，它完全替代了子查询以提高效率
index_subquery	此连接类型类似于 unique_subquery。它代替IN子查询，适用于子查询中的非唯一索引
range	范围扫描，即有限制的索引扫描，它开始于索引的某一点，返回匹配这个值域的行
index	扫描全表索引，这通常比ALL快一些（index是从索引中读取的，而all是从硬盘中读取的）
ALL	全表扫描

index

扫描表时按照索引次序进行而不是行。它的主要优点是避免了排序；最大的缺点是要承担按索引次序读取整个表的开销

key_len

key_len 计算规则如下：

• 字符串
  • char(n)： n字节长度
  • varchar(n)：2字节存储字节长度，如果是utf-8，则长度为3*n+2
• 数值类型
  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节
• 时间类型
  • date：3字节
  • timestamp：4字节
  • datetime：8字节
• 如果允许字段为NULL，需要1字节记录是否为NULL
  索引最大长度是768字节，当字符串过长时，MySQL会做一个类似于左前缀索引的处理，将前半部分的字符提取出来做索引

Extra列

输出项	说明
Using index	表示MySQL将使用覆盖索引，以避免访问表
Using where	MySQL将在存储引擎检索后再进行过滤
Using temporary	MySQL在对查询结果排序时会使用临时表
Using filesort	MySQL会对结果使用一个外部索引排序，而不是按照索引次序从表里读取行
Range checked for each record（index map：N）	没有好用的索引，新的索引将在连接的每一行上重新估算。N是显示在prossible_keys列中索引的位图，并且是冗余的