[大数据]MySQL执行计划explain

MySQL explain介绍

MySQL提供了一个explain命令，可以用于对select语句的执行计划进行分析，并详细的输出分析结果。

通过explain命令，可以深入了解到MySQL的优化器，还可以获得很多被优化器考虑到的访问策略的细节以及运行sql语句时哪种策略预计会被优化器采用。

MySQL explain参数

1. id

select的序列号列，表示查询中操作表的顺序。

• id越大，优先级越高（先执行）
• id相同，执行顺序从上到下
• id列为null表示为结果集，不需要使用这个语句来查询

MySQL将select 查询分为简单查询和复杂查询。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。

• 简单子查询

  EXPLAIN SELECT (SELECT id FROM timed_task LIMIT 1) FROM article;
  

  

• 派生表（from语句中的子查询）

  EXPLAIN SELECT * FROM  (SELECT id FROM timed_task LIMIT 1) a;
  

  

• union查询

  EXPLAIN SELECT * FROM timed_task UNION ALL SELECT * FROM timed_task;
  

  

2. select_type

查询类型。主要用于区别普通查询、联合查询（union、union all）、子查询等复杂查询。

select_type取值范围：

• simple：简单查询。查询不包含子查询和union
• primary：复杂查询中最外层的select（最后执行的select）
• derived：派生查询。包含在from 子句中的子查询（MySQL会将结果存放在一个临时表中，也称为派生表）
• subquery：包含在select中的子查询（非from子句中子查询）
• dependent subquery：相比于subquery，子查询的结果受到外层的影响的为dependent subquery
• union：在union中的第二个和随后的select
• dependent union：和union一样，出现在union或者union all中，但是这个查询要受到外部查询的影响
• union result：union连接的多表查询，第一个查询是primary，后面的是union, 结果集是union result

3. table

查询的表名。

• 如果查询使用了别名，那么这里显示的就是别名
• 如果不涉及对数据表的操作，那么这里就是null
• 如果格式为<derived N>，表示这是一个临时表。N就是执行计划的id，表示结果来自这个查询
• 如果格式为<union n,m>，表示这是一个临时表。表示来自union查询id为n、m的结果集

4. partitions

查询将匹配记录的分区。 对于非分区表，该值为 NULL。

5. type

查询所使用的访问类型。表示MySQL如何查找表中的行。

type值的从优到差顺序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

type的值分析

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

  例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

  EXPLAIN SELECT MAX(id) FROM timed_task;
  

• system：表中只有一行数据或者是空表（貌似MySQL5.7版本后不会有该值了）。

• const：使用唯一索引或者主键，返回记录一定是一条的等值where条件时，通常type是const。

• eq_ref：连接字段为主键或者唯一索引，此类型通常出现于多表的join查询，表示对于前表的每一个结果，都对应后表的唯一一条结果。并且查询的比较是=操作，查询效率比较高。

• ref：ref的三种情况：

  1. 非主键或者唯一键的等值查询
  2. join连接字段是非主键或者唯一键
  3. 最左前缀索引匹配

• ref_or_null：和ref类似，增加了null值判断

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

• index：辅助索引，减少回表次数,因为要查询的索引都在一颗索引树上。

• index_merge：索引合并，表示查询使用了两个或者以上的索引数量，常见于and或者or查询匹配上了多个不同索引的字段

• unique_subquery：子查询，返回唯一值

• index_subquery：子查询，返回值可能有重复。

• ALL：全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

• fulltext：全文检索索引。

6. possible_keys

可能使用的索引。

7. key

实际使用的索引。

• 可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
• 如果没有使用索引，则该列是 NULL。
• 当select_type为index_merge时，key列可能有多个索引，其它时候这里只会有一个。

8. key_len

索引里使用的字节数。

当索引为组合索引时，通过这个值可以算出具体使用了索引中的哪些列。

例如：组合索引index_column1_column2，其中column1和column2都为bigint（8个字节）

• 如果key_len=8，说明只是使用了索引中的column1。
• 如果key_len=16，说明只是使用了索引中的column1和column2。

MySQL数据类型的字节长度

特殊：如果字段允许为 NULL，需要1个字节记录是否为 NULL。

9. ref

关联的字段。如果是连接查询，则会显示关联的字段。

• 可以为Null
• 如果是使用的常数等值查询，这里会显示const
• 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
• 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能会显示func

10. rows

执行计划估算的扫描行数，不一定是精确值。（innodb不是精确值，myisam是精确值，主要是因为innodb使用了mvcc）。

11. filtered

存储引擎返回的数据在server层过滤后，剩下的满足的记录数量的占比（百分比）。

12. Extra

不适合在其他列中显示但十分重要的额外信息。常见的值有：Using index、Using temporary、Using filesort、Using where、Using where、Using join buffer、impossible where、select tables optimized away、distinct

• Using index
  相应的select操作中使用了覆盖索引(covering index)，避免访问了表的数据行，效率不错。

  • 如果同时出现using where，表明索引被用来执行索引键值的查找
  • 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

  覆盖索引：
  查询列（包括查询条件）只用从索引中就能够取得，不必读取数据行。

  换句话说就是：查询列要被所建的索引覆盖，MySQL可以利用索引返回查询列，而不必根据索引再次读取数据文件。
  举例说明：

  • 表user有一组合索引index_name_age（name、age两字段）
  • 查询SQL：select name from user where age=20

  该查询就使用了覆盖索引。

• Using filesort
  MySQL对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

  排序的时候最好遵循所建索引的顺序与个数否则就可能会出现using filesort

• Using temporary
  MySQL使了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

  group by一定要遵循所建索引的顺序。

• Using where
  表明使用了where过滤。

• Using join buffer
  表明使用了连接缓存。

• impossible where
  where子句的值总是false，不能用来获取任何元组。

• select tables optimized away
  在没有GROUPBY子句的情况下，基于索引优化min/max操作，或者对于MyIsam存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

• Distinct
  优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

MySQL 查询的执行流程

可参考文章：MySQL查询的执行流程