MySQL explain 详解

MySQL explain 详解

explain 是什么？

explain是用来查看SQL执行计划的。

可以干什么？

• 确定查询语句中表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 查询语句中表之间的引用
• 每张表有多少行被优化器查询

如何使用？

语法： explain + SQL语句
示例：

explain select * from security_user t where t.id = 1

查询结果如图：

下面会对查询结果中的字段属性进行详解。

explain 结果中字段详解

• id
  这个字段是用来确定查询语句执行的优先级的。
  这个值会有三种情况：
  
  • id值相同：这种情况意味着查询语句按照explain结果中的id自上而下执行
  • id值不相同：这种情况下，id值会自递增，id值越大，explain结果中的相应sql语句被执行的优先级越高，越先被执行。这通常会在子查询中出现
  • id值存在相同的和不同的值：这种情况下，id值越大，优先级越高，越先被执行，那么，对于id值相同的结果，mysql会按照explain结果中的id自上而下执行。

• select_type:

  • SIMPLE：简单的select查询，查询语句中不包含子查询或者union语句
  • PRIMARY：查询中若包含若干子查询或者嵌套查询，那么最外层的查询将被标记为PRIMARY.
  • SUBQUERY：在select或where语句中包含子查询
  • DERIVED：在from列表中包含的子查询将被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，将结果放丰临时表中。
    示例：
  • UNION：若第二个select出现在union之后，则被标记为union; 若union包含在from子句中的子查询，外层将被标记为：DERIVED
  • UNION RESULT：从union表中获取结果的select
    注意： 查询类型主要是为了区分普通查询、联合查询、子查询等复杂查询语句

• table
  显示查询语句结果来自于哪一张表

• type

  • all：Full Table Scan, 全表扫描，将遍历全表找到匹配的行
  • index：Full Index Scan, index 与 all的区别在于index只遍历索引树。这通常比all效率高，因为索引文件通常比数据文件小。（也就是说尽管all和index都是读取全表，但是index从索引中读取，all从硬盘中读取）
  • range：仅检索给定范围的行，使用一个索引来选择行。key列显示查询语句使用了哪些索引。这种情况一般出现在where条件语句中包含between…and.., <、>、in等的范围查询。这种范围检索给全表检索性能要好，因为它只需要开始于索引的某一点，结束于另一点，不需要全表扫描。
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也属于一种索引访问。它可能返回多个符合条件的行，所以它可能是查询和值班表扫描的混合体。
  • equ_ref：唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配。常用于主键或者唯一索引的扫描。
  • const：表示通过一次索引就找到了，const用于比较primary key或unique索引。因为只匹配一行数据，所以很快将主键置于where列表中，MySQL就会将该查询转换成一个常量。
  • system：表中只有一行记录（相当于系统表），这是const的特例，一般不会现。
  • null
    type显示的是访问类型，是查看性能较为重要的指标，性能值从最优到最差的顺序是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
    通常情况下，一条查询语句最起码需要保证达到range级别，最好可以达到ref级别。
    日常工作中，经常遇到的type类型如下：
    system > const > eq_ref > range > index > all

• possible_keys：显示可能应用到这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

• keys：实际使用到的索引。如果为null,则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
• key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的是大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值
• rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所所需要读取的行数
• extra：包含不适合在其它列中显示但十分重要的额外信息
  extra有以下几种情况：
  
  • Using filesort: 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为”文件排序”
  • Using temporary:使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by.
  • Using index: 表示相应的select操作中使用覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where, 表明索引被用来执行索引键值的查询;如果没有同时出现using where， 表明索引用来读取数据而非执行查找动作。
  • using where: 表明使用where过滤
  • using join buffer: 表明使用连接缓存
  • impossible where: where子句的值总是false，不能用来获取任何元素
  • select tables optimized away: 在没有group by 子句的情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
  • distinct: 优化distinct操作，在找到第一匹配的元素后即停止找同样值的动作。