EXPLAIN

1.EXPLAIN命令调用

mysql> EXPLAIN select 1;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
      filterd: NULL
        Extra: No tables used

EXPLAIN有两个主要变种

  • EXPLAIN EXTENDED: 告诉服务器"逆向编译"执行计划为一个SELECT语句.可以通过紧接其后运行SHOW WARNINGS看到这个生成的语句
  • EXPLAIN PARTITIONS: 会显示查询将访问的分区,如果查询是基于分区表的话.

2.EXPLAIN的相关限制

  • 更不不会告诉你触犯器,存储过程或UDF会如何影响查询
  • 并不支持存储过程,尽管可以手动抽取查询并单独地对其进行EXPLAIN操作
  • 不会告诉MySQL在查询执行中所做的特定优化
  • 不会显示关于查询的执行计划的所有信息
  • 不区分具有相同名字的事物.例如,它对内存排序和临时文件都是用'filesort',并且对于磁盘上和内存中的临时表都显示using temporary
  • 只能解释SELECT查询,并不会对存储过程调用和INSERT,UPDATE,DELETE或其他语句做解释(Mysql5.6 允许解释非SELECT查询)

3.EXPLAIN中列

3.1 id列

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

SELECT查询分类

  • 简单查询
  • 复杂查询
    • 简单字查询
    • 所谓的派生表(在FROM子句中的子查询)
    • UNION查询

3.2 select_type 列

这一列显示了对应行时简单还是复杂查询

标记类型

  • SIMPLE 表示此查询不包含 UNION 查询或子查询
  • PRIMARY 表示此查询是最外层的查询(包含子查询时)
  • UNION 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION,UNION 中的第二个或后面的查询语句,取决于外面的查询
  • UNION RESULT,UNION 的结果
  • SUBQUERY 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT,取决于外面的查询. 即子查询依赖于外层查询的结果

3.3 table列

这一列显示了对应行正在访问哪个表. 在通常情况下,它相当明了: 它就是那个表,或是该表的别名. 可以在这一列中从上往下观察MySQL的关联优化器为查询选择的关联顺序

MySQL的执行计划: 总是从一个表开始一直嵌套循环,回溯完成所有的表关联,是一颗左侧深度优先的树.

3.4 type列

关联类型

  • ALL: 表示全表扫描,通常意味着MySQL必须扫描整张表.这个类型的查询是性能最差的查询之一. 通常来说,我们的查询不应该出现 ALL 类型的查询,因为这样的查询在数据量大的情况下,对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询,那么一般来说可以对相应的字段添加索引来避免.
  • index: 表示全索引扫描(full index scan),和全表扫描一样. 只不过 ALL 类型是全表扫描,而 index 类型扫描表时按照索引次序进行而不是行. 它的主要优点是避免了排序;最大的确定是要程度按索引次序读取整个表的开销. 如果在Extra中看到Using index,说明MySQL整张使用覆盖索引,只扫描索引数据,比按索引次序全表扫描的开销要少很多.
  • range: 表示使用索引范围查询,通过索引字段范围获取表中部分数据记录.比全索引扫描好一些,因为它用不着遍历全部索引. 这个类型通常出现在 =,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,IN() 操作中.当 type 是 range 时,那么 EXPLAIN 输出的 ref 字段为 NULL,并且 key_len 字段是此次查询中使用到的索引的最长的那个.
  • ref: 这是一种索引访问(有时也叫做索引查找),它返回所有匹配某个单个值的行.然而,它可能会找到多个符合条件的行,因此,它是查找和扫描的混合体.此类索引访问只有当使用费唯一性索引或者唯一性索引的非唯一性前缀时才会发生. 把它叫做ref是因为索引要跟某个参考值(这个参考值或者是一个常数,或者是来自多表查询前一个表里的结果值)相比较.此类型通常出现在多表的 join 查询,针对于非唯一或非主键索引,或者是使用了 最左前缀 规则索引的查询.
  • eq_ref: 使用这种索引查找,MySQL知道最多只返回一条符合条件的记录.这种访问方法可以在MySQL使用主键或者唯一性索引查找时看到,它会将他们与某个参考值做比较. MySQL对于这类访问类型的优化做的非常好,因为它无须估计匹配行的范围或在找到匹配行后再继续查找. 此类型通常出现在多表的 join 查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =,查询效率较高.
  • const: 针对主键或唯一索引的等值查询扫描,最多只返回一行数据. const 查询速度非常快,因为它仅仅读取一次即可.
  • system: 表中只有一条数据. 这个类型是特殊的 const 类型.
  • NULL: 这种访问方式意味着MySQL能在优化节点分解查询语句,在执行阶段甚至用不着再访问表或者索引.例如: 从一个索引列中选取最小值可以通过单独查找索引来完成.

type类型的性能比较 通常来说,不同的 type 类型的性能关系如下: ALL < index < range ~ index_merge < ref < eq_ref < const < system ALL 类型因为是全表扫描,因此在相同的查询条件下,它是速度最慢的. 而 index 类型的查询虽然不是全表扫描,但是它扫描了所有的索引,因此比 ALL 类型的稍快. 后面的几种类型都是利用了索引来查询数据,因此可以过滤部分或大部分数据,因此查询效率就比较高了.

3.5 possible_keys

possible_keys 表示 MySQL 在查询时,能够使用到的索引. 注意,即使有些索引在 possible_keys 中出现,但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引,由 key 字段决定.

3.6 key

此字段是 MySQL 在当前查询时所真正使用到的索引.如果该索引没有出现在possible_key列中,那么MySQL选用它是处于另外的原因--例如选择了一个覆盖索引,哪怕没有WHERE字句.

3.7 key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用,或只有最左部分字段被使用到

key_len 的计算规则如下: 字符串

  • char(n): n 字节长度
  • varchar(n): 如果是 utf8 编码,则是3n+2字节; 如果是 utf8mb4 编码,则是 4n+2 字节.

数值类型:

  • TINYINT: 1字节
  • SMALLINT: 2字节
  • MEDIUMINT: 3字节
  • INT: 4字节
  • BIGINT: 8字节

时间类型

  • DATE: 3字节
  • TIMESTAMP: 4字节
  • DATETIME: 8字节

字段属性: NULL 属性占用一个字节. 如果一个字段是 NOT NULL 的,则没有此属性.

3.8 ref列

这一列显示了之前的表在key列记录的索引中查找值所用的列或常量.

3.9 row列

这一列是MySQL估计为了找到所需的行而要读取的行数.这个数字是内嵌循环关联计划里的循环数目.

3.10 filtered列

MySQL 5.1中新加进去. 这一列显示的是针对表里符合某个条件(WHERE子句或连接条件)的记录数的百分比所做的一个悲观估算.如果吧rows列和这个百分比相乘,就能看到MySQL估算它将和查询计划里前一个表关联的行数.

3.11 Extra列

这一列包含的是不适合在其他列显示的额外信息.

常见的最重要的值

  • Using index: 表示MySQL将使用覆盖索引,表示查询在索引树中就可查找所需数据,不用扫描表数据文件,往往说明性能不错
  • Using where: 意味着MySQL服务器将在存储引擎检索行后再进行过滤. 有时出现就是一个暗示: 查询可受益于不同的索引
  • Using temporary: 查询有使用临时表,一般出现于排序,分组和多表 join 的情况,查询效率不高,建议优化.
  • Using filesort: 当Extra中有Using filesort时,表示MySQL需额外的排序操作,不能通过索引顺序达到排序效果. 一般有Using filesort,都建议优化去掉,因为这样的查询CPU资源消耗大.
  • Range checked for each record(index map:N): 这个值意味着没有好用的索引, 新的索引将在连接的每一行上重新估算.N是显示在possible_key类中索引的位图,并且是冗余的

results matching ""

    No results matching ""